[Skip to content]

Sign up for our daily newsletter
The Actuary The magazine of the Institute & Faculty of Actuaries
.

5 tips for better spreadsheets

We are all familiar with one or more Microsoft products and probably use them nearly every day, but how many of us are really proficient and know more than half of the features? Probably only experts and developers. Here, I concentrate on common errors using Microsoft Excel and demonstrate how using a few simple rules will rapidly improve model development.

My experience from running applied Excel workshops over several years and dealing with client projects is that most users achieve the required standard to finish the job and do not have the time or inclination to develop their skills further. The spread of Excel in the last 15 years since the initial introduction of Microsoft Office means that there are more than 400 million worldwide users. Forrester Research estimates 50-80% of enterprises still rely on spreadsheets for critical applications, such as financial reporting, budgeting and forecasting. It is perhaps not surprising then that there are many examples of spreadsheet failures resulting either in monetary loss or reduced efficiency (see www.eusprig.org for further details).

Excel users who produce ‘models’ for others to use rather than ‘spreadsheets’ for personal use need to understand the fundamentals of model design and how to avoid basic errors. While the importance of a spreadsheet depends on the amount of money passed through the model or its strategic value, the same rules apply regardless. In this article, I put forward five basic design points and errors in spreadsheets and how to avoid them.

1. Logical flow
Many models appear to take information from all angles and work backwards and forwards as if they have emerged sporadically rather than having been subject to planning. Concentrating on what resulting information is needed often helps to determine how to move from inputs through calculations to outputs. One question that I ask clients is: “What five to ten outputs are needed from the model?” rather than: “What kind of model do you want?”

Modelling should not be complicated and one analogy is ‘like a book’: an ideal model should have a cover, table of contents, executive summary, workings, answers, summaries and backing sheets, such as explanations and history sheets. Even if this is not possible due to time constraints, information should flow from left to right across the tabs and down the individual sheets in a logical manner. Information should come from previous pages, not future pages. As others will need to understand the output, all the sheets should be formatted in a similar manner and set up for printing. This is much simpler if all users start with a basic application template with specimen schedules set up as examples (see Figure 1).

2. Mixed inputs, calculations and outputs
Some models appear to emerge as the product of different users’ input over time rather than as a result of a defined development. I have worked on models with multiple and differing timelines or a bewildering set of input areas. One simple test is to ask: “Can I understand the model structure in less than five minutes?”

While there are many theories on model design, most developers agree that different areas of a model should perform different tasks. Figure 2 shows some disorganised mixed code from a live spreadsheet presented to a clearing bank each month. It would be easier to understand the information flow from inputs through calculations to outputs if it were more logically presented in sections. Similarly, it is easier to check and audit a model progressively in coded sections.

3. No marked control area
All models should have a defined control or input area in one place with all the required inputs. Some models require inputs on different sheets and do not mark them with a simple colour scheme. If a colleague needs to use the model, they cannot be sure that they have entered all the required data. Unless formulas can be shown quickly as the most basic audit method, it will be unclear where formulas or constants are located.

One way to solve this issue is to set up simple styles for inputs, calculations, certain number formats and outputs and apply these styles to cells as the model is built. If the formatting scheme is published somewhere on the model, users can see exactly where the inputs are located. Note that the example in Figure 1 has a formatted control area in the top left for all the inputs.

4. Complexity
There are no prizes for complexity and long formulas. While it may be clever to write long nested IF statements or array formulas, these are difficult for users to understand. The use of long formulas should be limited to two lines and formulas broken down into shorter calculations. One advantage of spreadsheets, unlike executed code, is that you should be able to review how an answer emerges in stages. Trying to provide complete anticipated loss or options formulas in single cells is compact but does not aid understanding or assist with step-by-step auditing. IF logic statements can often be more clearly stated using MIN, MAX or LOOKUP functions.

5. Formula errors
Complex ill-defined models are likely to maximise formula errors but two practices serve to make spreadsheets even more errorprone. The first is the use of mixed formulas such as =C12/4 or SUM(C5:C10) + 50 where formulas and constants are combined in the same cell. If the first were an annual nominal interest rate divided by four to form a quarterly rate, this is static since no user could easily change the periodicity of the rate.

The second example appears to be a ‘fix’ and only a cell-by-cell examination would explain why 50 had been added randomly to the result. In addition, no reliable sensitivity analysis is possible if there is no dependable route from inputs to outputs. Therefore, constants should be in the input or control area and there should be no mixed formulas (examples of which can be seen in Figure 3).

Secondly, users often write inconsistent formulas in blocks of code. The general rule is that the same formula should be located across or down a block of code. So, it is wrong to use one formula from January to March, a different formula in April and then go back to the same formula in May to December. This is confusing for users and auditors alike, especially if it’s, as it often is, undocumented.

Spreadsheets need to be taken seriously and care should be taken in their construction and use. This article is not exhaustive in the types of errors found in client models. Nevertheless, these methods should help to write less erroneous spreadsheets.

_____________________________________________________________

Alastair Day is the author of Mastering Financial Modelling, Mastering Risk Modelling and Mastering Financial Mathematics in Excel published by the Financial Times, together with a number of other books