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.