From the Not Just Numbers blog:
A major news story in the UK in recent weeks has been the embarrassing U-turn of the government on their award of the West Coast rail franchise to First Group, following complaints from the incumbent, Richard Branson’s Virgin.
The problems appear to have been caused by unrealistic assumptions on rates of inflation and passenger growth.
This got me thinking about the use of assumptions and variables in Excel spreadsheets generally.
Obviously, any assumptions should be clearly visible and I would suggest (if space allows) above the forecast (or whatever the main content of the spreadsheet is).
I’m sure we all know this to be good practice but it doesn’t mean we always do it!
A second, slightly more subtle, rule that I always (try to) follow helps avoid so many of these types of errors.
Make these assumptions an active part of the spreadsheet
What I mean by this is instead of entering the text, say, “Inflation is assumed to be at the rate of 2%”, enter the name of the variable in one column and the value in another, e.g.
Then wherever inflation is used in the spreadsheet, always reference this cell (B3 in the example above). This way you always know that what you have stated in the assumptions is what your spreadsheet is using.
You can refer to this cell in two ways, either:
1. Directly, i.e. $B$3 – Note the dollar signs to ensure that if you copy your formula elsewhere in the spreadsheet, it still references the cell, or;
2. As a named range, i.e. name cell B3 Inflation and refer to the range Inflation in your formula.
The second approach makes the formula easier to understand, but the first is usually quicker.
Another significant benefit of this approach is when these rates need to change. I come across so many spreadsheets that calculate VAT (a UK sales tax – currently 20%) on the value in cell A2 as =A2*0.2. When the rate changes, all of the calculations need to be checked, where if the VAT rate had been entered in a cell as above, it would be as simple as changing the rate in that cell!
Simple tips, I know, but many a complex spreadsheet has been brought down by not following them – just ask the Department for Transport!
If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.