An important part of the development of a model is a review of the model – either by a colleague or a more formal external review.

An important aspect of this is incorporating deliberate errors in the model. These errors are recorded, and the model version with these errors is clearly marked as such and not used for analysis. This provides two key benefits:

• By finding all of the deliberate errors, the reviewer will demonstrate that they have carried out the review thoroughly and that it is likely that the genuine errors are discovered; and
• Selecting the deliberate errors in the model provides an opportunity for reflection on model logic and assumptions.

The deliberate errors selected should reflect those which are likely to be included in the development model:

• Formatting – that all cells comply with formatting rules.
• Formula errors – referring to incorrect cells or ranges, using inappropriate calculations (e.g. adding rather than multiplying).
• Logic errors – where the formula does not calculate the desired figure – for example multiplying by the incorrect currency.
• Deviations from best practice – including hard coded values, repeating inputs etc.

Finally, before you submit a model for review, do a thorough review of the model yourself.

EconomicsLAB provides model review services. If you would like to discuss this, or any other issues in analytics and economics, please contact adam@economicslab.com.au.

I am in two minds about named ranges.

Named ranges allow a formula to capture the logic of the calculation in an easy to understand way – illustrating the developers thought process and assumptions.

=Cost_Price*(1+MarkUp)

While this helps logic reviews, it does make it more difficult to review accuracy. When using named ranges, reviewers need to both (i) identify which cells are being referred to by the named ranges, and (ii) ensure they contain the appropriate value. This introduces an additional step in the review. Further complications can arise when ranges have similar names, ranges appearing in multiple worksheets.

The biggest difficulty with named ranges, however, is the vast number of redundant ranges which seem to accumulate. Incrementing versions of the model over time or the course of development, copying between workbooks, all seem to cause this problem. The impact of this is slower performance, increased file size, and decreased stability of the model.

EconomicsLAB’s First Aid Kit is an Excel add-in which addresses this issue, and other common Excel problems. If you would like further information or would like us to tailor a solution to your needs, please contact adam@economicslab.com.au.

One the biggest lessons I have learnt in Excel is that it is a two dimensional tool – rows and columns. At a stretch, we can do three (rows, colums and sheets). However, a lot of the time, we call on it to operate in a more complicated scenarios.

I was once tasked with reviewing a model which calculated the balances of four accounts for around fifty sites. In the short-term, the calculations were done on a six monthly basis. That moved to annual later in the period.

The resulting model was complicated, and very hard to extend. There were errors in the output, but it was difficult to find and correct them. The sites were listed along the rows, with the account balances and dates listed in the columns, with some initial assumptions in the early columns. This had several problems. It was very hard to follow the logic of the model – formula for each period referred to a mixture of the assumptions and previous period. When the model switched from sig monthly to annual, the formulae were inconsistent.

Rather than remedying the model, it was easier to redevelop the model. A better structure was a table with the assumptions on a separate sheet. The calculation/output sheet looked up these results, using a drop down box to select the site. Account balances ran across the columns, with the periods running down the side. Because the layout and calculations were now much simpler, we moved to a monthly basis of calculation, with sums to provide six monthly/annual totals. Charts became easy to include, and were great for finding errors in the assumptions.

By removing one dimension, the result was a more user friendly, accurate, and extendable model.

If you require any analysis of spreadsheets, including model reviews, development of add-ins or macros, contact adam@economicslab.com.au.