(This is the first of three blog posts on financial modelling for startups.)
While some entrepreneurs may think that financial models are just a formality that needs completing in order to placate straight-laced venture capital types, the reality is that models can be essential tools for creating and analysing the roadmap an entrepreneurial venture should follow. As soon as you’ve figured out the general area in which you’re building a business, you should have at least a simple model.
I know some people will ask, “Did Mark Zuckerberg build a financial model when launching Facebook?” The answer is, almost certainly not. But then again, very few of us will cofound a billion-dollar company which gets massive traction on day 1. A lot of entrepreneurs waste time and energy on companies which they don’t understand are destined to fail; one of the reasons for that is that they haven’t done any basic financial analysis. In particular, if you talk with VCs, they’re going to want to at least get comfort that you know the value of the key variables that drive your business, e.g., advertising rates.
A well-designed financial model has a clear purpose, flows intuitively from inputs to final outputs, is well documented, and is easy to use and read. (There are obvious parallels with how to write code.)
Given those objectives, I’ve compiled a list of the best practices in spreadsheet design. I’ve tried to summarize the most useful and easily achievable steps, so that even casual users can start making their spreadsheets more valuable without delving into macros or VBA programming.
Purpose of a Model
A financial model should be a virtual re-creation of the actual business. A good model should have the ability to test assumptions in order to analyse the impact on future financial performance, including growth rates, operating margins, product lines/individual segments, and refinancings/recapitalizations. The purpose of the model should directly influence how you think about its design and functionality.
An Effective Model
The effective model should be realistic and be backed by reasonable, defensible assumptions and projected performance. It should have adaptable and dynamic schedules and should be easy-to-follow. An effective model should be modular and have a logical cascade, so that anyone can audit the drivers.
It’s sometimes tempting to drive towards a final answer by hard-coding numbers inside a formula, but in reality, doing so destroys a lot of the potential value in your model and prevents it from transforming into a tool (which every good model should aspire to be). Therefore, always atomize your formulas.Extract all the inputs from each formula — those should be the only hard-coded elements in your model.
The test of a model’s quality can be determined by how easily it can be used or recycled by another person. Therefore, the more you’re able to standardizeelements of the spreadsheet design, the more likely it is to stand up under a variety of use cases. Consistency of a model will make your model more understandable and easier to construct. Make sure the model is easy to follow for yourself as well as for others.
The standard design elements I use:
– colour-code your inputs as blue, formula cells as black; references from another schedule or worksheet as green and warnings to another user/links to another model as red.
– Use exact figures. Excel can easily round for you according to your specified format.
– Never input the same number twice – let Excel flow and be dynamic.
– Don’t embed inputs in formals; instead, break out inputs into separate line items.
– In tables, try to structure them so that you have only one formula per row or column, so that a single formula can be copied across or down en masse. This constraint may take some thought to work around, but it’s so worth it when you need to make changes and know that all you need to do is copy straight across.
– If you’re using multiple worksheets, use each column for the same purpose, e.g., Columns D-G in all the tabs are equal to the next four calendar quarters.
– Include as many Checksums as possible. Sometimes the idiot against whom you need to idiot-proof your model is, in fact, you.
– Add cell comments to “leave a paper trail” and to highlight where you have follow-up questions. However, I dislike using the ‘comment’ feature, because these comments don’t print well and are often overlooked and accidentally deleted. Instead, i’ll set up a column where people can see my comments.
– Calculation settings should be set to: (1) “Automatic except tables” (if “Manual” is checked); (2) “Iteration” should be checked (default settings typically fine: “maximum iterations” = 100, “maximum change” = 0.001). A circular model will not calculate if iterations are unchecked.
– If possible, stress test each of your assumptions
– To maximise efficiency, you must learn the basic keyboard shortcuts. A sure sign of an inefficient worker is someone who uses the mouse heavily when in Excel or Word, instead of using keyboard shotrcuts.
– Make formatting consistent and nice – sloppy formatting will certainly give the impression that your analytics are also sloppy.
Finally, create a summary sheet that contains key statistics, assumptions, and outputs from the model. Include a documentation section that notes who developed the model, who last edited it, key assumptions, and which version is currently being used.
Photo credit notionscapital.