VAT calculations

There's been an interesting discussion over at Smurf on Spreadsheets about the consequences of the UK government's temporary VAT rate reduction. For the benefit of non-UK readers, VAT is basically the british sales tax (it differs a little in implementation from a simple sales tax). It is currently 17.5%, but as a reaction to the financial crisis, it will be reduced to 15% from 1 December 2008 until 31 January 2010 inclusive. Whether this makes sense as a matter of economic policy is, of course, highly contentious. But this is a technical blog so I'll stick to its effect on spreadsheets :-)

The problem is, of course, that there are thousands of spreadsheets throughout the UK using formulae like =A1 * 1.175 to calculate gross prices from net. Finding and fixing all of these is like a miniature version of fixing y2k bugs, worsened by the fact that this rate change was announced with just one week's notice (which makes sense, as otherwise spending could have frozen up until the new rate came in, but is still a pain for people who have to implement the change).

Now, I don't think there's any way that this could have been avoided entirely. Spreadsheets frequently start out has a scratchpad, where someone is just running some numbers and intends to throw the sheet away. Sometimes these scratchpads are valuable enough to be kept, and sometimes when that happens, magic numbers like 1.175 aren't extracted into a named cell called vatRate. They should be, of course, but they won't always be. So there are a lot of crappy spreadsheets out there.

If some spreadsheets are always going to be broken, the interesting question is: what happens with the well-designed spreadsheets? The ones where there's a named vatRate cell which just needs to be updated? Well, unfortunately, they're going to be broken too. There are two problems:

• Sheets where there are different items happening at different times. Imagine you have two transactions, one taking place on 28 November, and one on 1 December. The VAT rate for the first is 17.5%, and for the second it is 15%. So if you change the value in the vatRate cell, you'll get the wrong answer for one or the other. So now you need oldVatRate and newVatRate named cells -- but then, what happens when the new rate comes in? Or if the government decide to keep tweaking it up and down, as the economic situation changes?
• What if you don't update all of your spreadsheets? For the first few days of December, you can check each one as you open it for any named vatRate cells, and if you're careful you'll probably get them all. But what about other files that the files you open depend on? Have they been updated?

How to fix the mess? Or, at least, how could you write spreadsheets to guard against this kind of problem? Obviously, as someone who runs a company producing a souped-up Python-based spreadsheet, I was hoping that there was some kind of useful trick to handle this in our product -- and after a bit of thought, I realised there is.

In Resolver One you can create a single spreadsheet to represent your VAT calculations, and then use it like a function from other sheets. So you can create a spreadsheet that, given a date and a VAT type (some products get a reduced VAT rate, or are entirely exempt -- for example, there's no VAT on groceries), will work out the VAT rate. You can then use this in your other spreadsheets, and so changes like this can be updated in one place, and will be picked up by every other sheet you have.

This is probably the kind of thing where it's better to see an example rather that read about it, so if you want to check out how it works, the Resolver Exchange has a file containing a VAT calculator spreadsheet and a spreadsheet that uses it to work out some prices. If you download and unpack it, you can open them up and see how it works. (I've saved them both under a Creative Commons license, so if you don't have Resolver One, you can take a look at them using the free non-commercial version.)

(Oh, and if you're coming from Excel and are wondering where the formulae are in the second sheet, they're column-level -- click on the column headers to see them.)

Here's what I'd really like to know -- is there a way to do this in Excel without having to encode all of the information about VAT rates into some kind of add-in?