Spreadsheets are a laboratory: a place to test business hypotheses



Kenny Whitelaw-Jones


17 Feb 2015




At F1F9, we think of spreadsheets as a laboratory: a place for analysts to test business hypotheses.

The purpose of the spreadsheet financial model is to understand something about a specific business situation: should we invest? Should we change our capital structure? Should we undertake a project? Can we afford to hire more staff? By how much do we need to cut costs?

Since models exist to provide answers to these questions, the model should be built with the questions in mind: output oriented modelling. Somebody in the business asks a question. The analyst responds with: “we’ll need to model it.”

To model it, you first need to understand what it is that you are modelling.

In order to understand what it is that you are modelling, consider what makes it risky. Here are some classic risks that might be relevant:

– This deal is a one off – there are no precedents;

– It is has huge political significance;

– It involves numerous key stakeholders – and how they work together is not yet clear;

– Nobody trusts the demand forecasts;

– There are ten thousand lines of raw data to accommodate;

– Cost estimates have a margin of error of + / – 30 per cent;

– The financing structure is not yet clear; and

– The terms under which revenue is generated are complex.

Risks might fall under different categories. So project risks are risks that apply once the deal has been signed. We have seen project risks classified under seven main headings: construction; design; technology; demand; relevant cost; third party revenues and residual value.

Procurement risks apply up to deal close. These include the risk of approvals not being granted, overruns in budget estimates and financing not being available on favourable terms.

Modelling risk deserves its own category and we encourage the modelling team to undertake their own assessment.

Conceptual modelling

We have taught analysts from hundreds of companies to build financial models. Financial modelling proficiency requires skills that take time to acquire, develop and perfect.

Sometimes the difficulties students have are related to how to build their model.

More often, difficulties relate to what it is they need to model.

Once the modeller understands how a particular area of a business works commercially, it is much more straightforward to create a spreadsheet model.

We recommend that students begin with a sound conceptual understanding of what it is they must model. If they are confident that they understand how an area of the business works, they may press on and open up Excel. If not, they should spend the time required up front to acquire that understanding.

Conceptual understanding may be acquired by talking to people within the business who do understand the business situation, or by creating flow diagrams that set out the commercial and financial ingredients of the business.

It is helpful to start with the line item being modelled—for example “Revenue”—and to keep asking the question “what drives that?” until you cannot go any further. The sample flow chart below shows what this might be like.

conceptual model

Download the Dirty Dozen ebook

Kenny Whitelaw-Jones
Kenny Whitelaw-Jones is no longer with F1F9 but we really like this blog so we've kept it.