Financial models – This is the Way

We work with a lot of early stage, and not so early stage, companies to help them build financial models. And we can save you a lot of reading now with our guiding principle – just build a simple model.

The world is awash with advanced finance and accounting software. We can model anything. And with so many software companies generating immense amounts of data about everything, there is a strong temptation to build complex models with all the bells, whistles and macros. Resist this temptation.

Our guiding principle in building a financial model is that models should not give answers. If you build a 12-tab model with hundreds of rows on each one, there is the a very human tendency to want that model to give you a simple, clear answer, but this is a false certainty. Models do not give answers, they show constraints and measure trade-offs. Financial models should be seen as Thinking Aids, they help management teams make decisions, but they are just one tool among many.

Below we outline some guidelines for creating powerful, but simple models.

Step One: think about who will use the model. Models are communications tools, and everyone building such a tool should keep their audience in mind. Every department inside a company should have its own models – the sales team should have a detailed sales funnel and pipeline tracker, the operations teams should have waterfalls and Gantt charts to their hearts’ content. In this post, we are going to look at the basic Income Statement which the management team should use as a focus for their decision making. And to be clear, this is a cash model, used to measure the company’s life, let the auditors build the GAAP model. We are going to call this model an Income Statement, but really it is a Cash Burn model.

Step Two: set up the model with just a few tabs. This model should generally have less than five tabs. We usually build models in Excel, because we are hard-core Excel cultists, but if you are going to have multiple people working in the model, use Google sheets. You lose a lot of functions that you were never going to need, and gain really good sharing capabilities.

Below is a list of basic model etiquette. Feel free to modify to suit your tastes, but you should really follow these exactly. This is the Way:

  • Do not set up a separate tab for assumptions, we are not investment bankers seeking a single value of the company. Assumptions are variables, and you want to be able to immediately see the impact of changing those variables on other parts of the model.
  • Use text color. Assumptions in one color (so long as it is blue), formulas in another (black), only use red text if you are trying to scare people.
  • Be consistent with negative numbers (we prefer parentheses).
  • Sparingly (never) make use of cell shading, no one remembers what all the colors mean.
  • Never hide rows or columns.
  • Break complicated formulas into multiple cells, so you can play around with all the assumptions separately. (Seriously, cells are free, use a lot of them.)
  • Prominently label units of measurement.
  • Link the tabs and cells. If your model requires something to be Cut & Paste when you use it, you are doing it wrong.
  • Build the model out monthly – annual numbers lack granularity, quarterly models are for public companies, no one really intuits quarters.

The goal is to make the model readable. We find that good coders are the best at this because they understand the importance of other people being able to read your code/model. Accountants less so, because they prioritize comprhensive detail.

Step Two: start with the biggest swing factor in your business. For pre-revenue companies, this is headcount. All companies should dedicate one tab of the model to headcount forecasts. This is likely the most manual part of the model, as you need to break out individual salaries, or at least salaries by department. But it is also likely the biggest expense, by far. Be sure to include a gross-up for taxes and benefits, these add up to big numbers. We also find it helps to factor in costs of onboarding new employees.

For companies with revenues, add a tab for revenue. This is a place which often trips up companies. This is the Management Model, not the Sales Model. The sales and business development teams should have their own tool for tracking prospects and customers. Use the output of that model for the management model. If that is not available, think about sales in a semi-granular sense. This model should not track every customer (unless you have less than ten customers), but should bucket revenue by price tiers. This can be large-, mid- and small-sized deals, or it can be Free, Premium and Pro customers. Factor in number of customers, prices and do not forget about churn. This tab serves two purposes, it should both capture revenue and it should also serve as a driver for the cost of sales. The goal is to have enough detail to be able to calculate those two elements, without having so much detail that discussions bog down into a Sales Pipeline review every time you open the model.

A final note on marketing expense. If your company is spending a lot on growth and customer acquisition that should also merit its own tab. Again, the goal is to capture high level, monthly trends. This should tie into the revenue model where those links can be clearly established, and should largely focus on the variable costs.

Step Three: Assemble the P&L. Admittedly, for many companies this may be mostly L without so much P for a while, but this tab will be the company’s Income Statement – again, on a cash basis, not an accounting basis.

We already have the two biggest line items for this model – revenue and employee costs. Then add in the other costs including cost of goods sold, rent, marketing, cloud services (which if they are large enough, and tied to user growth should merit their own tab), lawyers, patents and other corporate services. This likely captures most of the company’s costs. If the company has some large upfront items (e.g. inventory if you are building hardware, tape outs if you are building semis) add them to this tab. Do not factor in deprecation or amortization, this is a cash model.

Step Four: add everything up to get Operating Income/(Loss). If you are paying taxes or interest, deduct those. (But if you have those, you may be reading the wrong blog.) This cell is your cash burn, which for a start-up is the Layer 0 of all KPIs.

However, we are not done. Next take your current bank balance, subtract the Cash Burn amount, add any funding amounts you expect to get, and this gives you your projected End of Month cash balance. In the next month, set this as your Beginning of Month Cash Balance, and repeat these steps. You now know exactly when the cash runs out. Time to start fundraising.

Step Five: now that we have the basic information we can start peeling off parts for display to different audiences. Our working models have various “Display” tabs showing the numbers cut in different ways for the Board, for the Exec Team meeting, for formatted Copy & Paste into pitch decks. You can also now add all the bells and whistles you want – formats, sharing, VB Macros, Pivot Tables, drop-down menus – but put all those toys on new tabs, leave the core model pristine.

The power of building a model this way is that the management team can move things around and see how they impact the burn rate. Hire more sales people adds costs but accelerates revenue. Have you been debating marketing spend for six weeks? Does the amount actually change the month you run out of cash or is it really rounding error? Are you spending a lot on rent? There is an alternative to that, and we know a lot of companies that extended their runway by six months by abandoning unused offices. All this and more become starkly clear with this kind of model.

The list goes on, but the point should be clear. Building a model this way allows clear insight into how the parts of the business interact and, in turn, how that affects the survivability of the company.

Credit: Disney / Lucasfilm Ltd.

Leave a Reply