The financial forecasting model rubric

Here is a guide to the model.

First, consider the rows of the spreadsheet. They are organized into sections. Rows 2-4 detail assumptions and about customers. Rows 6-112 detail assumptions about personnel in various job categories, which for many tech startups generate most of the expenditures. Rows 14-36 show the opening and closing cash balances at the beginning and end of a quarter. Rows 16-20 detail cash that comes in from customers, grants, and tax credits. Rows 22-33 detail cash that flows out for employees and for expenses such as marketing and rent. Row 29 is fringe benefits which are paid on top of salaries.

Net cash flow, the difference between cash in and out, appears on row 35. Revenue is shown on 39-41. Fixed and variable costs are shown on lines 42-48, leading to a calculation of profit or loss on line 49. The elements of a balance sheet, including assets and liabilities, appear on lines 51-60.

Next, consider the columns of the spreadsheet. Column A lists the rows; detailed explanations appear on the far right of the spreadsheet in column V. Constants that drive some of the calculations are in columns B and C.  Column D describes what happens in Quarter 0, as the startup is founded. The first 12 quarters (3 years) appear in columns E-P. Annual summaries of revenue and profit (loss) appear in columns R-T.

Here are the most important regions in such a forecasting model for a startup.

1. All constants specifically input as values are highlighted in yellow. All computed values appear on a white background. The formulas doing the computation may be seen by accessing the spreadsheet.

2. Monitoring cash balance on rows 15 and 36 is critical to ensuring you do not run out of cash.

3. Equity infusions on line 17 must be timed to finance product development and marketing. The model shows 5 tranches totaling $3.1 million. Ideally, more funds would be raised more quickly, allowing much more rapid investment in sales and marketing.

4. The numbers of people hired (rows 7-12) and what you are paying them (rows 23-28) is typically the dominant cost component in the early days of a startup. Sweat equity and stock options must be used to reduce the cash drain. This can be seen as small salaries for principals.

5. Row 3 shows the number of B2B partner customers and row 4 the number of B2C customers. The cost to each B2B customer is set at $500 per quarter, the cost to each B2C customer at $50 per quarter. One row can be eliminated if you do not plan both kinds of business models. It is very difficult to project how quickly you can add customers after your product launch, so this is one of the most fragile parts of the model. In the model shown on the following pages, we assume that the product has been built with sweat equity prior to product launch.

6. The model shows relatively light sales and marketing expenses until quarter 7.  Only in Q4 is there a full-time business development person, joined by a second person in Q7. Other marketing expenditures also start to grow significantly in Q7 after the first substantial infusion of investment ($800K).

7. The model assumes that providing good support is part of the DNA of the company and is a source of competitive advantage. Premium support for B2C customers is priced at $75 per quarter. Market considerations need to be used to set product and support prices (Chapter 5). Design/support/training costs are estimated by assuming an occasional contract designer, and support personnel at .1 person per B2B customer and .002 person per B2C customer. These numbers will have the be adjusted based on experience.

Note that the model does not include a calculation for taxes (the firm does not achieve profitability until the fourth year) but there is an allowance for the Canadian government reimbursing part of the firm’s R&D costs under the SR&ED program (row 19).

Forecasting models are not static entities. They must be tuned month by month to reflect a growing understanding of the financial dynamics of the business. To best understand the model, as you adapt it to your startup and using it as a management tool, you may want to try some of the following exercises:

• Simplify the model so it just represents a startup that is purely B2C with some premium support revenue.

• Expand the model so that the firm sells turnkey systems, which you can do by adding in a new expense line for hardware to be shipped.

• Add a training component of the business, which will require new personnel rows and a revenue row.

Also, try using the model to answer typical questions for which such models are useful. Here are three good questions:

1. You discover at the end of Q0 that development has encountered problems and will be two quarters late in producing a usable product. Modify the model, and especially the revenue forecasts, to predict how much additional investment you need and how soon you will need it.

2. Although you are concerned that adding more developers will make the late project even later, your VP Development believes that adding two consultants that she knows can reduce the delay in product ship date to one month. Modify the model to predict how much additional investment you need and when.

3. By the end of Q3, you realize that you have underestimated the marketing expense required to realize your sales forecasts. Modify the model to predict how much additional investment you need and when.