Dimensional Analysis and Accounting

The road to wisdom? Well, it's plain and simple to express. Err and err and err again, but less and less and less.

Figure 1: Accounting Must Be Accurate to Run
the Business Properly (Photo:
www.business.rutgers.edu).

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 of thousands of dollars on one contract. This error caused that company great hardship.

My son is an accountant who provides taxation and accounting online services to individuals and firms. 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. I realized how important the knowledge of accounting practices could be for accountants or CPA businesses who are looking to buy accounting firm at a wholesome level as it would help their business focus on minute details such as these. Coming back to the point, some of the results which the spreadsheet generated 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. Many businesses use dedicated software like lease accounting software to deal with this aspect of their finances in order to improve their workflow and other monetary expenses in regards to their venture. The importance of such an application or even something as integrated as a Top staff expense management software is not something that can be undermined. These applications prove beneficial to the business owners, but I, as an individual, work at it differently. 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:

• N_ThisYear is the number of tons produced this year
• N_LastYear is the number of tons produced last year
• E_LastYear is the number of megawatts consumed last year
• E_ThisYear is the number of megawatts consumed this year
• C_ThisYear is the total electrical expense this year
• C_LastYear 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

Figure 2: Dimensional Analysis Reveals a Spreadsheet Error.

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.

Figure 3: Quick Fix for the Formulas.

This was the first time my son had used dimensional analysis for an accounting problem and it made quick work of the issue.