Finance Tip of the Bi-Week

When in doubt, create a spreadsheet or write things down.  It is much better to put calculations into a spreadsheet or on paper when making financial decisions.  I often listen to clients ramble off numbers in their head about projects they would like to take on, and how they are going to make a tonne of money only to find themselves losing on the project because they didn’t write anything down.  (Poor planning)

I prefer to use Excel (spreadsheets) because the numbers are concrete and visual.  I can save the information if I want to add to it later, it saves a lot of time regarding calculations and on top of that, it is more accurate and you can go back and see if you made a mistake in a calculation.

Finally it saves on meeting time.  When you’re in a board room discussing a financial matter and everyone is giving their opinions and going in circles, nothing cuts meeting time down more than hooking your projector up to your laptop. I find this shortens meeting time because creating spreadsheet that everyone can see puts everyone’s ideas that people have in their head onto a screen and once the idea has been said it no longer has to be resaid, and resaid, and resaid, and resaid over and over and over and over again.

In summary, I love spreadsheets.

Side note: Flip Charts also work very well for cutting time short.

You Can Solve All Problems with Excel Part 2 – “The Not-So Obvious” A Personal Problem

In my previous post I posted about my love of Excel and we looked at JoAnna who was considering taking her favourite hobby of greeting card making and making it a small side business. Today I want to look at another type of problem that Excel can help you solve.

Personal Decisions

In my post One of My Faviourite Hobbies, I touched on the MBTI personality types and mentioned that people make personal decisions based on how they think or how they feel.  Often our feelings can get in the way of making objective decisions. In this next example I’m going to teach you how to create a nifty tool called the “weighted average decisionator” and how we can use Excel to create this useful tool.

Linda is considering the decision of where she wants to go on holidays. She has narrowed her decisions to the following two options.

1.       Drive to Florida to visit with family for a 2 weeks or,

2.      Take a 1 week cruise with friends from Quebec to Boston via the St. Lawrence River

Both vacations are so different that it is hard for her to decide which one she prefers more.  She has strong feelings about both.  She hasn’t visited her Uncle Sam in Florida and he is getting older in years.  Linda also loves to shop and Florida has great outlet stores.  But on the other hand (she had warts J) she has always wanted to take a cruise and she has been saving up for a year now, but only has enough money to take the 1 week cruise.

Step 1 in making this decision objectively will be to come up with a set of criteria in which to make her decision. She has come up with the following 5 criteria that are most important to this decision.

1.       Ability to Relax

2.      Shopping Value

3.      Family Visiting

4.      Time with Friends

5.      Memories

Step 2 involves giving the 5 criteria numeric weights to their importance.  Here is how Linda decided the weights for her criteria.

1)      Ability to Relax                                                              (Weight 30 Points)

a)     Linda has had a very stressful year at work and really needs a good relaxing break so she has weighted this criterion very high.

2)     Shopping Value                                                  (Weight 10 Points)

a)     Linda loves to shop, but has been shopping a lot recently and is not in need of anything in particular so she has given it a lower point value.

3)     Family Visiting                                                (Weight 25 Points)

a)     The thought of visiting Uncle Sam is very important to Linda so she weighted it high.

4)     Time with Friends                                          (Weight 20 Points)

a)     Linda loves her friends, but feels that in comparison to visiting with Uncle Sam it should not be as important.

5)     Memories                                                         (Weight 15 Points)

a)     Linda loves to take pictures and loves creating scrapbooks of each of her vacations.

At this point we have enough information to set up our Excel spreadsheet.  Here’s how I would set it up.

Notice that to make things simple I set up the spreadsheet to make each criterion a score out of 10.  I have found that when comparing different decisions keeping the score out of 10 for each criterion makes the comparison process easier.

The final result shows that Linda should go to Boston because the Boston score was 66 while Florida only received a score of 60.

You may say, “why not just handwrite this problem instead of using Excel it’s an easy enough problem” I would agree, but I would argue that using Excel lets you change your weights and scores at the click of a button instead of rewriting the whole decision every time you want to change your weights or scores.  Let Excel do the work for you!  It’s a great tool and in this case reveals that the Boston trip will be the better trip in the long run. 

Personal Problem Solved!

 

You Can Solve All Problems with Excel Part 1 – “The Obvious” A Financial Problem

I have a saying that I tell all my clients. “You can solve all problems in life with Excel”.

I love Excel and spreadsheets. I’ve solved many problems using Excel. One of the first things I do when I start working with any new client is analyze their lineup of products that they sell. You’d be surprised how many small business owners sell things either at, below cost, or at such a small margin that they can’t make any money.

To alleviate this problem I like to use Excel to break up their product into four categories.

1. Direct Labour / unit

2. Direct Materials / unit

3. Overhead / unit

4. Desired Profit / unit

By breaking up a sellable item into its 4 categories of costs per unit you can find out exactly how much something costs; not what you think it costs in your head.

Let’s use an example of JoAnna who wants to sell homemade cards for a profit.  She expects that she can sell them for $7.00 per card.  Here is a breakdown of her homemade cards by using the four categories and Excel.

Category 1 Direct Labour / Unit

After some research it was found that it takes JoAnna 2 hours to make 3 identical birthday cards.

In Excel I would do the following:

Direct Labour

 

 

 

 

 

 

 

Category 2 Direct Materials / Unit

After some research it was found that to make 3 birthday cards it takes the following list of supplies. – 5 sheets of construction paper of various colours – 6 Ribbons – 3 Envelopes – 3 Blank Cards – Various small supplies

An issue arises when trying to calculate the cost per unit.  Curry’s, the craft store that JoAnna buys her supplies from doesn’t sell any of these items in single units.  Here’s where Excel come in.  I would use Excel to calculate the per unit cost. 

In Excel I would do the following:

 

 

 

 

 

 

 

Category 3 Overhead / Unit: 

In JoAnna’s case, this is a side business and she has no overhead other than the various supplies like glue, stickers and other small items that she uses.  She figures she probably spends about $100.00 per year on all her small supplies.  She expects to sell 200 cards per year through a local retailer who loves her homemade cards.  Therefore we can divide $100.00 by 200 cards to give us an amount of $0.50 per card.

Category 4 Desired Profit / Unit

To figure out this category JoAnna needs to calculate her total cost of the first three categories together.

We can see that her cost per card is $8.57.  She is now put in a dilemma because based on her research into the greeting card market she knows that standard birthday cards sell for $7.00 per card.  She has a few options:

1.       She could decide that getting into the card business is not for her.

2.       She could market her cards as a premium greeting card and sell them for more than $8.57.

3.       She could sell them for $7.00 but accept that she could never pay someone to do the work and that she would be working for less than minimum wage to sell her cards

4.       She could find a new way to make cards that would take less time.

5.       She could try to find new suppliers that have cheaper direct material prices or buy in bulk.

6.       She could have her friend who lives in China where labour and supplies are cheaper, make the cards, and ship them to Canada.

7.       She could get together with her Grandmother and have fun making cards and get much of the labour for free.

There are many options she could take at this point to make her business more profitable, but had she not used Excel to calculate her total cost per unit she would have been losing $1.57 per card she sold. ($8.57 – $7.00 = -$1.57)

In this case if she ever chose to hire someone to make cards at even minimum wage the more cards she sold the more money she would lose but if she makes the birthday cards herself she will make $5.25 per card ($7.00 Selling Price minus 1.75 Direct Materials = $5.25 Labour/Personal Profit)

I don’t think that JoAnna would want to quit her day job but at $5.25 per card she may have fun turning her hobby into a fun way to make a little extra money and at the same time learn some Excel skills.