This is a little story about the likely dangers of not using named cells!
Here in New Zealand we have a Goods and Services Tax (GST) on the supply of most goods and services. It is generally charged at a rate of 12.5% (see http://www.ird.govt.nz/gst/). Many other countries and states have similar value added taxes or sales taxes. There must be millions of spreadsheets in the world today with calculations incorporating tax rates in some form. Are these spreadsheets likely to be set up so that changes in these tax rates are easy to accommodate?
For example, consider the NZ case. In the past the GST rate was 10%. What if the NZ government decides to return to this rate? Or more likely, what if they put it up to 15% or 17.5% as is being talked about at present? Would spreadsheet models, referring to the GST rate, especially those that are used time and time again, be easy to modify? Or would it become a nightmare situation?
Well that very much depends on how the tax rate is referenced. There are several possible strategies. We shall explore these next using an example where we find the tax component of a payment that included tax. In the scenario shown here, to keep things simple, we’ve range named cell B4 as Payment and we’ll use that in each formula.
Three different correct formulas for the same calculation
In the first case, as shown above, the tax rate is stored in cell B2, and it has been range named GST_Rate. Formulas, in any sheet in the workbook, can now refer to this cell using its name. Our calculation of the tax component of Payment in B5 has the formula = Payment*GST_Rate/(1+GST_rate).
We think this is the best formula, for reasons we shall discuss later, but it isn’t the simplest.
If we didn’t use a range name the same calculation could be written, using a fixed cell (also known as absolute) reference, as = Payment*$B$2/(1+$B$2). Range names not only make formulas easier to understand but they also make it much less likely that cells will be incorrectly referenced. For a start when using a range name it is obvious that you are referencing the correct cell. (A common situation occurs when a formula referring to a cell containing a tax rate or discount rate is set up in the first cell of a column, checked, and then copied down the rest of the column. If a fixed cell reference isn’t used, as in our formula above, then only the first calculation will be correct! This is a very easy mistake to make and it won’t happen if a range name is used because it will automatically use a fixed reference.)
We hope you are now convinced that the first version, with the range named tax cell, is better than the second formula with the fixed reference.
For this calculation you might well be tempted to use the very much simpler formula, = Payment*1/9, shown here.This formula doesn’t refer to a cell containing a tax rate but puts the rate directly into the formula. We call this “hard-wiring” the constant into the formula.
Is it a problem if tax rates are “hard wired” into formulas? Isn’t it better to have the simpler formula?
Recently a student showed us a spreadsheet model, set up by someone from a very reputable, international accounting firm. The model projected the finances of a NZ enterprise over the next couple of years. By our standards it was a complex spreadsheet even though it contained only two sheets. The first sheet had nearly 500 rows and we searched and found 169 formulas in the first sheet referring to the current GST rate. (How many didn’t we find, we wonder?) The next sheet in the workbook, with the next year’s budget was a bit more compact but it still had many formulas requiring the tax rate.
In every single formula we examined we found that the tax rate was directly built into the formula. In the first sheet formulas used five different constants which all related to the GST rate of 12.5% (or 1/8). These were:
0.125
1.125
0.88888889
8/9
1/9
Mostly a whole row had the same formula copied across a range of columns. However in some rows there were different formulas in different columns, e.g. formulas referring to 0.888888888888889 and 8/9 in adjacent cells. It is hard to imagine why they were like that. Some rows had formulas that contained multiple references to the GST rate, e.g. =((… +…-…)*0.125)+( … +…)/9. One particular row had a mixture of formulas containing the constant 1.125 and others cells had data values of 0 rather than formulas.
How could these formulas have been made much more robust in the first place?
When the spreadsheet was created then the 12.5% (which can of course be written as 0.125 or 1/8) should have been entered into a cell and given a suitable range name such as GST_Rate. The constants listed above should then have been replaced in formulas by the following expressions:
| Value |
Equivalent Expression |
| 0.125 or 1/8 |
GST_Rate |
| 1.125 |
1+GST_Rate |
| 1/9 |
GST_Rate/(1+GST_Rate) |
| 0.88888889 or 8/9 |
1/(1+GST_Rate) |
Yes it does take a bit to figure these out! And the formula =((… +…-…)*
0.125)+( … +…)/9 would now become the more complex formula
=((… +…-…)
GST_Rate)+( … +…)/
GST_Rate/(1+GST_Rate)
If the formulas use the expressions rather than the constants they will be much more robust. If a different tax rate becomes applicable, other than that currently used in the spreadsheet, then just one cell will need to be changed. All formulas referring to this cell will then automatically use the new value. (Yes, we do realise that some formulas might have to refer to the new rate and some to the previous rate but it would still be much easier to fix than if each formula contained a constant related to the tax rate!)
If you want to change an existing spreadsheet how easy is it to find all the hard-wired constants?
If you know what you’re looking for then you can use the find and replace options found in the Edit menu (or Home | Editing if using Excel 2007). In this particular example it is necessary to use a different search for each of the five constants used. Can you guarantee that you will locate and correctly update all formulas using these constants? If the spreadsheet is as complex as this one then I doubt it. Prevention would have been a much better strategy than cure!
So what is the key point here?
It is better to take the time and effort to create a spreadsheet with formulas referring to named cells containing constants than to enter these constants directly into formulas.