03 8400 4500
mail@standardledger.co

Standard Ledger Blog

Spreadsheet design has many elements.

How to build a good financial model spreadsheet

Good spreadsheet design makes your financial model easier to use. It starts with why you need it, and who you’re building it for. Read on to find out how you can make your financial model useful.

If you are just after a simple startup financial model template, you can download the simple Excel startup financial model template here.

In our previous blog, we provided an introductory overview of our simple startup financial model template. In this article you will find some of the thought processes that we go through with clients in building financial models.

Building financial models starts with spreadsheet design

We’ll start our building process by talking about spreadsheet design.

Let’s ask two important questions: Why are doing this? Who will be using this spreadsheet?

All financial models are wrong … but some are useful

Yep, you heard right! So why do we bother?

As Hugh Macleod who draws these wonderful cartoons over at Gapingvoid puts it, every model is wrong… but some are useful.

This art depicts the words "every model is wrong, but some are useless".

This art is by Hugh McLeod at Gaping Void

 

I wanted to get this out of the way right upfront. Don’t get tempted to fall into the ‘analysis paralysis’ trap: A place where you are attempting to build a perfect model. There is no such thing.

Financial models are a best attempt to put together a consistent, financial representation of the planned performance of the business.

Spend sufficient time to be satisfied that you’ve modeled what you believe is the most likely outcome for your startup. Then go ahead and deliver on it.

First, a note: Your plan drives your numbers

Your plan drives your numbers. The process of building a model often starts with figuring out what your plan is. Once you’ve done that, you put a number in a cell in a spreadsheet.

If the act of building a financial model forces you to think about whether you do, in fact, know what it takes to build your actual business – and you fall short in answering this – then it’s also served its purpose. Even if you don’t end up with a model.

Building a #financial model forces you to think about what it takes to build your business.… Click To Tweet

Once you have managed to pull together the spreadsheet design, and populated it, you might then figure out that it doesn’t work financially. Then you can use this knowledge to go back and adjust the plan, which drives the next iteration of your model. By the end of the modeling process you’ve hopefully been able to see (and demonstrate) that your business is sustainable.

And that you’ve figured out what financial and other resources you’ll need to succeed.

Remember, your investors will want to see the model

A sound financial model is critical when presenting to financiers, whether they’re a bank or a venture capital investor. Your financial model demonstrates that you’ve thought through how you’re going to deliver on your vision. Remember, as with any piece of work you produce, it’s always good to understand who your audience is (this is Marketing 101).

A financial model is part of your supporting material in helping an investor make a decision on whether to invest in your startup. While there may be some high level numbers (and especially graphs) included in your pitch deck, you won’t be going through spreadsheets when you present to investors.

ST 377_Financial Modeling_1-08

Get your model done before you pitch, not after

Make sure that you have your model ready to go as soon as you walk out the door from your pitch. If you’ve got a potential investor interested, you don’t want them waiting a moment longer than necessary for any information they need. I’ve seen investments fail because an entrepreneur took three weeks to ‘clean up’ (ie rebuild) a financial model when asked for that level of supporting information from a VC.

Knowing that your model is going to end up in the hands of an investor, you want to make sure that it is easy for them to understand.

With me so far? Now we come to the elements of spreadsheet design.

Elements of good spreadsheet design

Here are four key points to remember when working on your financial model spreadsheet design.

1. Ensure your model reflects your plan.

Since the financial model is ‘merely’ in a financial incarnation of the actual business, you should include modeling elements that match your business. If you’re going to use a Salesforce field, include those costs (and revenue assumptions). If you’re aiming to drive your SaaS business through Adwords or Facebook advertising and A/B tested online conversions, then build that; and so on. I’ve even seen some people cut-and-paste business model canvases or build decision-tree flowcharts into models

2. Ensure your assumptions are easily found and understood.

Every model should have at least one assumptions tab where all the key assumptions can be found. Never, ever bury hard-coded numbers in the depths of a formula in a call 2 ‘pages’ down.  I’ll say it again – never.  If an investor ‘happens’ to find one of these, it’ll make them question what else they haven’t found.  They won’t want to go looking, and you’ll have lost your credibility.  On the other hand, I’ve seen investors disagree with the actual percent, say, used in an assumption (from their experience), but totally agree with the modeling methodology … and then go on and invest.

3. Ensure your assumptions are verifiable.

If you’ve made assumptions about growth rates, can you back them up? If you’re a brand new startup without a history, you should try and obtain the best possible industry metrics as a starting point for considering how you think your own startup will perform. At the bottom of this article you will find links to SaaS benchmarks, Crunchbase and the US SE, all of which can be useful initially.  If you’ve been going for a while, you should use your own historical information to guide future growth rate assumptions.  Remember that you are trying to demonstrate to yourself and investors that your assumptions are considered, and, on the face of it, are believable.

4. Include bottom up detail (within reason).

This applies to both your assumptions as well as the model as a whole.  For assumptions, take a little bit of time to spell out what’s behind, for example, an incremental per-employee cost.  For the bigger line-items such as advertising spend over time, while you could simply assume $50,000 per year, it’s far better to itemise your actual advertising plans (online, offline, trade shows etc.) and the corresponding costs wherever possible.

Formatting matters in your spreadsheet design!

It might sound trite, but from experience I can tell you this stuff matters. What you’re trying to do is present information in as readily a digestible format as possible.

Investors are time poor. If you give them any excuse to say ‘too hard’, you’re likely to lose them.  So, do take the extra time to make sure that your model is visually appealing, easy to understand and easy to navigate.  By doing so, you’re making sure there is one less barrier stopping them from saying yes to an investment.

Selective colour coding is useful

Given how busy the ‘sea of numbers’ can look in a spreadsheet, you want to make it easy for creators (you) and readers (investors) to be able to distinguish between different elements easily.

Many modellers, myself included, use the following spreadsheet design convention: Blue text is for an enterable cell, while black is used for derived formulas.  This is also a useful visual reminder that your assumptions should be (largely) blue and other tabs (largely) black.

Other methods of highlighting include cell background colours or font size or boldness to draw attention to different items. For example, you might want to distinguish between key metrics/units and dollar amounts).  Remember that we’re trying to build a largely functional model – so don’t overdo it. We’re after useable function, not beauty.

A picture tells 1,000 words – so use graphs

Make good use of graphs, built right into your spreadsheet.  This is particularly powerful if you also layer in actual data showing the traction you’ve already had, and the expected (ie modeled) continuation of this going forward. Some startups even give potential investors login access to their key metric dashboards for the ultimate in disclosure – all of which helps investors to be confident that the startup has nothing to hide.

But if you don’t want to do all of this yourself, you can just download our simple financial model template for free.

 

Coming up next

Our next blog will look at the revenue section of a financial model. If you haven’t already, sign up to our mailing list here to receive the next instalment.

Remember:  You can download the simple Excel startup financial model template here. We’ll finish up this series of posts by introducing the more comprehensive Excel startup financial model template that you can use when you’re ready for it.

Other resources

If you’re serious about your spreadsheet design, you may also want to check out the serious folk over at the Spreadsheet Standards Review Board (SSRB) and their Best Practice Spreadsheet Modeling Standards.

If you’re a SaaS startup you really want to check out all of these:

Other useful links: