Ten Common Financial Model SNAFUs
I plan to publish a series of posts on mistakes I frequently see in financial forecasts (or pro-formas, or financial projections, or whatever you want to call them).
I’ve segregated the common mistakes into ten broad and sometimes overlapping categories. Today, I’ll describe these categories, and future posts will go deeper into each category, discussing ways to identify and correct typical errors within the category.
Here are my ten categories of financial model errors:
- Big Picture & Scope Errors. Your model should be designed so that the level of detail is appropriate for your audience and your desired outcome. The level of detail that your CFO requires to project weekly cash flow for the coming quarter is very different from the level of detail you might need for your first meeting with a potential investor.
- Business Model Errors. Most business models are fairly straightforward, but others can be very tricky, especially if they involve some combination of advertising, subscriptions, licensing fees, and partner revenue sharing. Furthermore, industry practices change. If you’re building a revenue forecast for a mobile app based on a business model that was commonplace just a few years ago, for example, you’re probably missing many of today’s realities.
- Assumption Errors. Assumptions are the inputs into a model, and what you show your audience is the output. Thus, the model is only as good as its inputs. Some assumptions, such as those related to product pricing or customer adoption and growth rates, can have a tremendous impact on your overall revenue forecast. And bad expense assumptions will misstate profitability. We all know that none of us can predict the future, but you can still do a lot to come up with assumptions that are reasonable. Don’t try to be “conservative” or “aggressive” – try to be justifiable.
- Reality Check Errors. Very few companies have achieved a billion dollars in revenue within five years of starting. Nobody will believe you if you claim that your company is going to be the next Google. Trust me. Also, benchmark key metrics in your forecast against industry norms. If you’re projecting revenue per employee at $500,000 and your industry norm is $250,000, your audience probably won’t believe you.
- Financial Errors. Some things are hard to accomplish accurately if you build a model using a spreadsheet. For example, accurately accounting for COGS, customer churn, bad debt and returns, depreciation, tax loss carryforwards, accounts payable and receivable, and interest income and expense can all get tricky. Often, something even more basic gets overlooked: for example, balance sheets that don’t balance (or if they do, only because usually either cash or retained earnings is used as a “plug” to make it balance). You need a firm grasp of accounting to get this right.
- Structural Errors. A business is essentially a collection of cause-and-effect relationships. Every dollar of revenue is caused by some precipitating event, like the efforts of a salesperson or the impact of a marketing campaign. Operating expenses are the consequence of headcount, among other things. The levels of assets and liabilities are the consequence of prior operating, investing, and financing activities. When these structural relationships are not modeled appropriately, the projection quickly becomes a meaningless collection of independent guesses.
- Computational Errors. These are straight-up formula errors. Sometimes they’re easy to find and fix; other times, they can be very elusive. The better organized your model is, the easier it will be to avoid errors.
- Errors of Omission. Businesses are complex, and it’s easy to overlook important things while modeling them. Sometimes it’s something fairly simple, like forgetting to include the cost of travel or utilities or something relatively small like that. Other times, it can be more consequential: for example, some entrepreneurs project rapidly growing revenue, but omit most of the marketing effort (and expense) necessary to make that growth happen.
- Presentation Errors. Financial models need to be understandable and useful to their target audiences. If they have to spend a lot of time wading through a messy financial model trying to figure out what you’re saying, you’ve failed. Keep it neat, and use descriptive labels, variable names, color codes, table headers, and other visual and semantic cues to help your user focus on the business, rather than on the model.
- Errors of Expectation. The main value of a financial model is helping you and your audience focus on the right assumptions and structural relationships. It’s a tool for understanding how the pieces fit together. You can’t predict the future, so there’s no point in striving for perfection. Make sure you and your audience both understand the purpose and limitations of the model.
Bonus: Human Error. We all make bone-headed mistakes. Sometimes we transpose numbers, or enter dollars in a cell that calls for Euros (or units), or slip a decimal point. Hopefully, you or a colleague will catch the mistake before long, and if you don’t, the magnitude of the error is immaterial.
I plan to do a deep dive into at least one category a week. If you have any other areas of financial modeling errors you’d like me to explore, please let me know in the Comments section below.