If you study physics one of the first things you learn to do is a "dimensional analysis' of formulae. For example, there exists an equation relating initial (u) and final (v) speeds to the distance (s) over which a body has constant acceleration (a):
v2 = u2 + 2*a*s
The dimensions involved are length L (in meters, for example) and time T (in seconds, for example). Distance has units L, speed has units L/T, and acceleration has units L/T2. Replacing the elements in the above formulae with their dimensions gives:
| {\bigg(\frac{L}{T}\bigg)}^2 = {\bigg(\frac{L}{T}\bigg)}^2 + \bigg(\frac{L}{T^2}\bigg) * L |
You can see that the left and right side of the equation have the same units and that when we add two things together, they have the same units too (so we are not adding "apples and oranges'). In a spreadsheet model we can use the same logic to help make sure our model is constructed properly. It is good practice to label cells containing a number or formula with some explanation of what that value represents, but including units makes the logic of the model even clearer. For example, noting the currency when there are more than one in your model, or if it is a rate then note the denominator, e.g. "US$/ticket', or "cases/outbreak'. Then checking that the units flow through the model using dimensional analysis will often reveal errors.
Checking that the same units are used for a dimension (length, mass, etc) is also important. We commonly come across two problems in this category in our auditing activities that are easily avoided.
Fractions
The first is the use of a fraction, where the modeler might label a cell: "Interest rate (%) and then write a value like "6.5'. Of course to apply that interest rate, s/he will have to remember to divide by 100 to get to a percentage, and we've found that this is sometimes forgotten. Better by far, in our view, is to label a cell "Interest rate' and input the value "6.5%' which will show on-screen as 6.5% but be interpreted by Excel as 0.065 and can therefore be used directly.
Thousands, millions, etc
In large investment analyses, for example, one is often dealing with very large numbers, so the modeler finds it more convenient to use units of thousands or millions. This would not present a problem if the entire spreadsheet used the same units, but very commonly there will be certain elements that do not: for example, cost/unit or price/unit for a manufacturer or retailer of high volume products. The danger is that in summary calculations that evaluate cash-flow streams, the modeler may forget to divide by 1,000 or 1,000,000 in keeping with other currency cells.
Our preference is that the model is kept in the same units throughout, so we would use the base currency unit for example. Admittedly this can be tricky if you're converting from values you know in thousands or millions: we can easily get all those zeros mixed up. A convenient way to get around this is to type numbers into Excel using the engineering format. For example:
For 123 thousand type 123E3
For 326.5 thousand type 326.5E3
For 100 million type 100E6
Of course, now your spreadsheet is full of cells with huge numbers you can't read, so we also advise that you use special number formatting. The file Financial Formats offers a number of formats you can use so that a cell with the value 105,000, for example, will appear on-screen as £105k. You need only copy and paste cells from this file into your spreadsheet, or use Excel's Format Painter tool to transfer the formats.
The links to the Financial Formats software specific models are provided here:
