The road to wisdom? Well, it's plain and simple to express. Err and err and err again, but less and less and less.
It has been reported that 90% of spreadsheets have serious errors (source). These errors occasionally are large enough that they have international implications (e.g. London Whale). In my small part of the world, one small company I worked at had an error in a cost estimation spreadsheet that cost it hundreds thousands of dollars on one contract. This error caused that company great hardship.
My son is an accountant and he recently told me about a spreadsheet that had no documentation as to how it was supposed to work, but it clearly was not working properly. Some of the results it was generating made no sense and the problems could be seen using dimensional analysis. In fact, he compared his spreadsheet debugging with the stoichiometry he learned when he was in chemistry class. He was correct – dimensional analysis and stoichiometry are closely related.
I use Excel spreadsheets for much of my financial work and, like all software, it is very difficult to ensure that the results produced by spreadsheets are correct. My standard approach is to first perform a dimensional analysis of the spreadsheet formulas to ensure unit consistency. My son also used dimensional analysis to immediately expose a couple of problems with this spreadsheet.
The spreadsheet calculates (among other things), the year-over-year electrical cost changes (called variances) due to different factors. Figure 2 shows my simplified version – the variances should all be in $s. The parameters involved are:
- NThisYear is the number of tons produced this year
- NLastYear is the number of tons produced last year
- ELastYear is the number of megawatts consumed last year
- EThisYear is the number of megawatts consumed this year
- CThisYear is the total electrical expense this year
- CLastYear is the total electrical expense last year
- ΔVolume is the cost variance attributable to changes in production volume
- ΔEfficiency is the cost variance attributable to changes in electrical efficiency
The fix was simple and is shown in Figure 3. It turns out that the error was in the same fraction for both variances. The spreadsheet author had flipped the numerator and denominator of the megawatts per ton term.
This was the first time my son had used dimensional analysis for an accounting problem and it made quick work of the issue.