Checking units propagate correctly

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: 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 modeller 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 modeller 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 modeller 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 round 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 FREE MONTE CARLO SIMULATION SOFTWARE

For Microsoft Excel

Download your free copy of ModelRisk Basic today. Professional quality risk modeling software and no catches

Download Tamara Basic now 