Blog

Building A Financial Model For Your Small Business (Part I)

Posted by John Boudreau on May 24, 2017 11:49:44 AM

A financial model is a critical tool for helping you make business decisions.

In this blog post, I am going to define how to build a simple financial model for your small business and why doing so can help you improve performance and make better decisions.

What Is A Small  Business Financial Model?

A financial model is your business in black in white. It is the financial skeleton of your business.

According to Wikipedia - "Financial modeling is the task of building an abstract representation (a model) of a real world financial situation. This is a mathematical model designed to represent (a simplified version of) the performance of a business..."

A good financial model does the following:

  1. Details the products you plan to sell and the revenue generated from those products.
  2. Details your cost of goods sold or the costs incurred when you sell those products.
  3. Details who you think you will need to hire, what they will do and what you will pay them.
  4. Details how much you will spend on sales and marketing.
  5. Details how much you will spend on general and administrative costs.
  6. Details if your business will make a profit in any given month.

Let's go through elements 1-3 in this blog post and why they are important to your business.

Tracks The Products You Plan To Sell And The Revenue Generated From Each

The first thing a financial model must do is to detail each line of revenue in your business. We'll take a simple doughnut business (who doesn't love doughnuts?) for example. Let's say, your doughnut business sells jelly, chocolate cake and glazed donuts. Those are your products.

Your model should project how many of each doughnut you plan on selling each month. Try to break down your product into discrete units (individual doughnuts in this case). If you sell websites, the discrete unit will be (1) one website. If you sell your time (like a lawyer or an accountant), the number of hours is your discrete unit. If you sell boats, (1) one boat would be the discrete unit.

Units sold doughnuts.jpg

 

You can see in our example, we plan on selling 800 chocolate cake donuts per month.

For you business, project out how many of each product you plan on selling for each month. You may have seasonality in your business. You may sell more of "X" product in the winter versus the summer.

You may say, "I have no idea how many of "X" I am going to sell". I hear that often, but this is actually not true. Using data from previous months or years, you do have some idea. Do your best projecting what you think you will sell. One big part of financial modeling or budgeting is learning the numbers that underline your business. Make a best guess, then enter the actuals when they occur.

Convert The Units To Revenue

Next, convert the units you think you are going to sell to revenue by multiplying the number of units by the average sales price of each product. Again, you should know the price you sell things for. If you don't know your pricing, you may have an even bigger problem.

In an Excel spreadsheet, create an "assumptions" tab which allows you to change the assumptions that are the drivers for the model.

 

assumptions.jpg
 

You can link separate tabs of your model to your assumptions tab. This allows you to make one change (for example, increasing the sales price for chocolate cake donuts) and see how that affects your model.

You can see below, that we have now projected the revenue for three products, just using an "assumptions" sheet and a "bookings and revenue" sheet.

revenue model.jpg

 

I like to highlight the cells that can be edited to make it easier for the user. For example, say you plan on selling more donuts in the summer. You can change the number of units sold. In the example below, we increased the number of units sold in the summer and you can see the effect on revenue.

revenue summer.jpg

 

Granted, this is a very simple example, but one that can be applied to your business.

As each month passes, you could enter your actuals (how much you really sold) and have an increasingly improved picture behind the sales of your business.

I've found that many business owners think they are selling more of one product than they really are. Having a financial model can help you get a true picture of what's going on in sales and help you plan for seasonal downturns.

Next, a good financial model details your cost of goods sold or the costs incurred when you sell those products.

Your business' COGS (cost of goods sold) is a critical component of your business model. If you can think of your business as a machine, your customers’ cash comes into the machine and hopefully cash comes out at the end in the form of profits.

Your COGS are in inescapable. These are the costs which are incurred just to build or deliver your product. The lower these costs are, the more efficient your business "machine” will be. Having a handle on these costs is critical for improving the profitability of your business.

 

COGS.jpg

 

To really understand the efficiency of your business, you should calculate your Gross Margin for each product line.

Gross Margin = (Revenue - COGS)/Revenue and is expressed as a percentage. In the example above, the Gross Margin is 60%. ($10-$4)/$10 = 0.60 or 60%. The higher the Gross Margin, the better.

For example, you may have one product that generates a lot of sales but has a low Gross Margin. You may consider focusing your efforts on those products with a higher Gross Margin.

Using our doughnut example from early, let's calculate the COGS for each product by defining the COGS.

cogs and gross margin.jpg

 

You can see that "Chocolate Cake" doughnuts have a better Gross Margin (59.6%) than "Jelly" doughnuts (55.6%).

Lastly, a good financial model details who you think you will need to hire, what they will do and what you will pay them.

A good model helps to map out who and when you will hire people. Headcount (people) is usually your biggest expense, so mapping this out can drive real results.

We've updated the model now to only include two employees. One to make the doughnuts and one in "admin" who is focused on all the administrative functions of the business and to work the register (sales).

 

headcount doughnut business.jpg

 

The model is built to calculate the monthly salary of each employee and their associated taxes and benefits. What if you'd like to hire another person? Just add them to the model and see the effect.

The headcount currently only has two categories of employees - COGS and Admin, but you can add more like "marketing", "sales" or "R&D".

We've had to increase the number of doughnuts sold to cover the cost of this full time employee. We've also broken down the COGS into materials (flour, sugar etc) and labor. You can see that the projected annual revenue is $212,000. You can also see that our Gross Margin has dropped as we've pulled in full time labor to make the doughnuts. You can see the potential impact hiring another person to make the doughnuts could have on your gross margin. Having a tool like this could help you make the right decision.

updated with cogs labor.jpg

 

I won't go into more detail here, but I think you get the picture. Just building a simple model like this can help you gain more knowledge about your business and the underlying drivers.

If you'd like to view the model live you can access the Google sheet here.

In the next blog post, we'll take this model further by adding:

  • Details how much you will spend on sales and marketing
  • Details how much you will spend on general and administrative costs
  • Details if your business will make a profit in any given month

In the meantime, if you'd like help building a financial model for your business just contact us. We'd be happy to help.

 

Topics: budgeting, gross margin, small business financial models, COGS