Building a good financial model isn’t easy. Many companies spend countless hours trying to get their Excel-built model just right. The purpose of building a model is two-fold: first it lets potential investors know that you have made an attempt to project the potential performance of your business, understand what the cost drivers are and (hopefully) identify key risks in the business model. Secondly, it helps you link strategic thinking to the nitty gritty of making enough money each month to pay your bills.
I’m pitched on potential investments regularly. Beyond looking at the big picture, I also take a look at the details of the company’s financial models. What are their assumptions? Are they realistic? Do the patterns in costs and revenues make sense given the typical progression of a business and given the industry? Through this process I see some mistakes over and over again. These mistakes are “tells” which let me know that the founders either haven’t thought everything through or they have prepared the model hastily and may have other problems.
Want to read more about building a financial model? Check these out:
Before sending your model to anybody (investor or not), make sure that these “tells” are resolved.
First tell: Accounts receivable and accounts payable are ignored
Including A/R and A/P in an Excel model does add an additional layer of complexity. You have to adjust for the difference between when you make the sale or incur the expense and when the cash actually comes in or goes out. This means that your cash flow statement and your balance sheet need to take this into account.
The most correct way to model it would be to include in your assumptions sheet your standard payment terms. It could an A/R assumption of 60 days and an A/P assumption of 45 days. This assumption should then filter through to your monthly projections.
If you are in a crunch for time and don’t want to build this layer of complexity into your model simply indicate in your assumptions sheet that you are assuming that your A/R and A/P will both settle in the same period. That way you won’t need to adjust for the timing difference between your financial statements. It also sends the message that you didn’t forget about this; instead you simply used a somewhat unrealistic assumption which you can always update later.
Second tell: Your income taxes aren’t calculated correctly
Most models for early stage or startup companies show significant losses in the first several years. Let’s assume that your company is projected to lose $500,000 in Year One, $250,000 in Year Two and make $500,000 in Year Three. What I see in many models is that the income taxes owed in year one are $0 and are also $0 in Year Two. That’s fine so far. But then in Year Three the model takes the $250,000 in profits and multiples it by a weighted average tax rate of 35 percent generating a tax liability of $87,500. WRONG! You have to take into account your net operating loss carryforwards. This means that in Year Three, the correct tax liability will be $0.
The correct way to do this is to include a separate spreadsheet which tracks NOLs and calculates the proper tax liability. If you would like to see how this is done, send me an e-mail at firstname.lastname@example.org and I’ll send you a sample tax liability calculation sheet that you can plug into your model.
Third tell: Sales forecasts are calculated using the top down approach
It’s a good idea to express your projected revenue forecasts as a percentage of the total market. This provides a compelling case if you have a clear path for capturing significant share. It also ensures that you aren’t deluding yourself if your model shows that you are going to capture, say, 98 percent of a market.
But you shouldn’t calculate your sales projections using a percentage of market approach. It’s not realistic. If your model shows that your revenues are derived from capturing 2 percent market share in Year one and 4 percent market share in Year Two and so on, it’s not credible. Sales should be calculated from a bottom-up approach. This means calculating it based on your sales process and cycle. How many sales people will you have? How many leads will they contact each month? What percentage will close the deal? This is what you need to model using credible assumptions.
It’s always a good idea to have a finance person take a look at your model before your circulate it. Even if you have to pay something for their time, the learning you’ll gain and the potential grilling you’ll avoid are worth it.