Startup Financial Models: Best Practices in Spreadsheet Design




(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.


For some more advanced material on spreadsheet design, check out “Best Practice Spreadsheet Modelling Standards” from the Spreadsheet Standards Review Board(SSRB).



Photo credit notionscapital.


Thank you to interns Franklin Bi, Wei Deng, and intern Rodi Blokh for their help in researching and writing this blog post.


David Teten originally posted this article on his blog.

NOW WATCH: Money & Markets videos

Want to read a more in-depth view on the trends influencing Australian business and the global economy? BI / Research is designed to help executives and industry leaders understand the major challenges and opportunities for industry, technology, strategy and the economy in the future. Sign up for free at