Follow me
You are here: Home / Startup / Financial Analysis / Startup Financial Models: Best Practices in Spreadsheet Design

Startup Financial Models: Best Practices in Spreadsheet Design

spacer

(This is the first of three blog posts on financial modeling 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 analyzing 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 analyze 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.

Atomize.

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.

Standardize.

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 standardize elements 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:

–  Color-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 maximize 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.

Summarize.

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 Track.com intern Rodi Blokh for their help in researching and writing this blog post.


Related tags: Financial Analysis

Subscribe to my newsletter

about david teten

David Teten is a Partner with ff Venture Capital. [more info]

new to teten.com?

  • Where are the Deals? How VCs Identify the Next Generation of Startups
  • How to Prepare For and Run a Startup Board Meeting
  • Asset Management Is A Bizarre Industry Ripe For Disruption
  • ff Venture Capital Is First VC Fund To Raise Capital Under The JOBS Act
  • How to Think Like a Hacker, Even if You Can’t Code
  • Parkour and Entrepreneurs
  • A Closer Look At The Quality Of Angel Returns Data
  • How VCs Can Accelerate Portfolio Company Returns
  • Reinventing the Office: How to Lose Fat and Increase Productivity at Work
  • Founder Institute Startup Fundraising Hacks

other popular posts

  • Template Startup Financial Model
  • Startup Financial Models: Best Practices in Spreadsheet Design
  • Peter Drucker on the Seven Sources of Systematic Innovation
  • How to Write a Memo That People Will Actually Read
  • Why We Chose Salesforce CRM Over Zoho
  • Lose Weight and Get Strong Without Leaving Your House
  • SeekingAlpha.com: How to increase your consulting revenue and your profile
  • The Ultimate Office for Athletes and People Seeking a Healthier Lifestyle
  • Why Are Venture Capitalists (76% White Men) Ignoring The Future?
  • Please Don’t Pitch A Venture Capitalist Without This Checklist
gipoco.com is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.