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.