Building an Excel Model

aat comment

Excel Models are representations of real-world financial scenarios.

This could be anything from a calculation to estimate the costs for an event you’re attending, to a spreadsheet with macros that forecasts financial performance.

Here’s a basic example of a spreadsheet intended to work out the cost of hiring staff for a party:

excel

There are many advantages to building the calculation in Excel rather than on paper or with a standard calculator. You can more readily keep track of your decision rationale (as well as share it) and it’s easy to repeat the calculations with different input assumptions.

A model is easier to build and understand if it is broken down into the following components:

excel-2

This approach treats the model as a ‘sausage machine’: inputs and assumptions at one end, Excel formulae applying calculations in the middle, and results churned out on the right. The arrows will be functions choosing data from the inputs to use in the calculations and presenting them in the results.

To some extent it is up to you what you call input data and what you entitle assumptions, but input data are generally independently sourced (e.g. the raw feed from your company’s sales database) and assumptions require some consideration (e.g. the rate of growth in sales of a new product over the next few years).

Models can be pretty complex, but there are some simple principles to bear in mind:

Plan ahead – Map models out first before putting them into practice. Ask yourself what inputs, assumptions and calculations need to be included and what results need to be displayed.

Keep it simple – Think about how accurate the output from the model needs to be, and which inputs might cause uncertainty. Choose the simplest formula for any given section and break down any complex steps into simpler ones – this will make mistakes easier to spot and less likely to happen.

Horses for courses – Consider alternative ways of performing each calculation and their pros and cons. Then choose the most appropriate for the task.

Build in checks – These will help you highlight errors in your model so you can fix them. A simple sense check as you input data will quickly highlight issues with the model.

Future proof by automation – By linking your model to your input data, the model will be able to update itself automatically when something changes and save you precious time.

Presentation – Make sure your spreadsheet layout matches the analysis steps and use consistent formatting so it’s clear what each section relates to. It’s also a good idea to include a dedicated results worksheet for clarity.

Alan Gurney is AAT Comment’s Excel tips writer.

Related articles