Simplicity

The most important feature your model needs to have is simplicity. No matter how complex the problem, the structure of a spreadsheet is perfect for that problem to be broken down into components, solved at this level, and these solutions to be rolled together into an answer to the problem.

The practical implementation of this means:

• Keep your formulae short. Spread more complicated calculations over a number of cells.
• Use worksheets to break your work into sections. A financial model might have a separate tab for revenue calculations and expense calculations. As complexity increases, these calculations may be further divided.
• If you are performing a calculation using values from a different sheet, bring those values into the current sheet, and then perform the calculation. That not only reduces the size of the formula, but makes it much easier to see what values are being used in the calculation.

Complications make your model harder to audit, maintain and understand.

 

Unique cells

One of the most important design principals in Excel is avoiding unique cells. These are cells which we can’t predict the contents of based on the information surrounding them. These cells should be avoided at best, or highlighted and explained at a minimum. This section will describe how they arise and how to manage them.

• A transition from raw data to a formula.
• A formula changes along a row or down a column.
• Deviation from the purpose of the sheet.

Introducing Formula

A spreadsheet contains a row of data. However, at some point it changes from a hard coded value to a formula – perhaps repeating the last data point, calculates an average, or applies a growth rate. The reason this has occurred is likely that the model required data beyond the inputs available, necessitating an assumption about what happens next. For example, exchange rate forecasts span five years, and after that we apply that last forecast for the remainder of your model.

This is relatively common, and should be managed by highlighting the cell where the formula is introduced and including commentary about the model.

Changing Formula

This is typically a sign of a model in distress. A row/column contains a complicated formula – think nested =IF() statements. It has been modified over many periods to overcome the occasional quirk with the data. Under time pressure, we add another condition until we get that cell right. We continue this process as circumstances change.

The solution to this situation is to review the logic of your formula. Consider simplifying and spreading over a number of columns or rows. Are the other ways to approach the problem which a more transparent and auditable? Investigate these issues.

Changing Sheet Design

Almost all of the data on a sheet is time series. However, a few assumptions are for all periods. These assumptions are in the column which is meant to hold first year data. This means that a user who is examining these cells may be confused – do they only apply to year 1? If so, what happens in other years?

This situation arises during development of the model, where we are inserting assumptions, and it may be easier to group assumptions by topic – put everything associated with revenue on the same sheet. However, now we have a fully functioning model, it is time to create the necessary sheets and adjust the layout to reflect best practice modelling.

Incorrect numbers

A financial model aims to calculate an after tax return, and is labelled appropriately. However, due to an error in the formula, it actually calculates a pre-tax return. Or a user mistakenly entered a pre-tax number where post tax is required.

These cases are more difficult to detect than the above examples, which can be identified by visual inspection alone. In the case of user input, it may be almost impossible to detect without reviewing the source data and a good understanding of the business being modelled.

The solution in this case is the use of alerts, or built-in sense checks. For example, an alert may be raised where the post-tax return is higher than the pre-tax return.

Conclusion

This will hopefully give you some ideas for how to develop stronger models which can continue to be useful into the future.