Monthly Archives: November 2008

VAT calculations

28 November 2008

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?

Ironclad 0.7 released

27 November 2008

Excellent news from my friend and colleague William — he’s released version 0.7 of our Ironclad project, a library that allows you to use the useful C extensions that have been written for CPython (Python’s reference implementation) from within IronPython (Microsoft’s version for .NET – the version we use at Resolver Systems).

William has many caveats about how far there still is to go, but this new release is tantalisingly close to being ready for alpha testing. Huge chunks of numpy, the numerical Python library for doing difficult maths with large data sets, now work. This is fantastic stuff — close enough that we’re now seriously considering having it as an option (with an explicit note that it’s not ready for production use) in the next release of Resolver One — or at least the one after that.

[Update] The redoubtable Michael Foord, another friend and colleague, has written a much better and more detailed post about this release on the IronPython URLs blog.

Resolver One plug

26 November 2008

A quick plug: there’s only one day left to get Resolver One at the old price!

As of midnight (GMT) tomorrow, the discounted price for Resolver One 1.3 will come to an end, and the price will rise from $199 to $399. If you want to get your copy at the old price, you should buy now

Do one thing and do it well

20 November 2008

It’s all change at Resolver Systems.

When we founded the company at the end of 2005, Robert, Patrick and I wanted to produce a new kind of spreadsheet. It would be something that would be bought by individuals within a company, using their company credit cards, and would gain market share slowly but surely, by making its owners more productive than their peers.

Based on this plan, we raised money, started putting together the team, and worked hard to get a good first cut of the software ready for our first milestone — a meeting of the advisory board.

At that meeting our advisory board looked at what we had, looked at the spreadsheet market (with its dominance by Microsoft Excel), and told us that our plan couldn’t work. The real money in the spreadsheet market was in the big-ticket sales, they explained. Sure, we could sell a souped-up spreadsheet for programmers and make a few bucks, but it we could produce something… I hesitate to say enterprise-grade, but that was the implication — well, then we could start looking at making real money.

We weren’t entirely convinced, but these were older and wiser people than us; people who had made their fortunes in the (enterprise) software world. So we tried to work out how to best do this. There were various iterations of the plan, but the aim was always the same: to produce a high-end version with a high price tag for the top end of the market, in particular for the financial markets, and a another version for everyone else.

It took a while, with constant adjustments of what should go into each version, but this summer we finally cracked it. On our website we would offer a retail version of the software (and a free version for not-for-profit use), but the bulk of our marketing, and all of our in-person sales effort, would go into identifying large finance companies with specific spreadsheet problems, for whom we would provide services around our core platform as a way of giving them a high-end product — with a high-end price tag. In my wilder moments, I found myself thinking that we could open-source the platform and get our revenues entirely from these services, or from tools built around the platform — like 37signals have with Ruby on Rails.

What could go wrong? After all, our only dependency was on the market for highly priced software for financial companies.

Right.

In October, our top-end customers started cutting back their requirements for new copies of the software, and at our weekly sales meetings, the “potential blocking factors” column in the status spreadsheet started sprouting worrying new entries like: “Client being acquired by a non-bankrupt competitor, all expenditure on hold. End user still keen.”

It was clearly time to re-think.

I don’t know who it was that said it, but I remember reading once advice saying something like: “Imagine what you would do if your company was in real trouble. What would you do to bring the company back from the brink? Now, for each of those things you would do then — why aren’t you doing them now?”

To put it another way, the prospect of impending demise sharpens the mind wonderfully. We’ve come to realise that since that original meeting, we’ve been trying to be two companies; an enterprise services vendor, selling software based on our own platform, and a vendor of a new kind of spreadsheet. We’ve been putting our management and sales effort into the former, but we have the DNA of the latter.

(At a developer interview we’re much more likely to ask someone how they might go about writing an algorithm to topologically sort a dependency graph than how they might calculate the value of a CDS. That’s a free hint to anyone thinking of applying for a job.)

Our problem wasn’t that we’d followed our advisory board’s advice and they were wrong (though it would be easy for us to see it that way). If we’d followed their advice entirely and given up our dreams of producing a new kind of spreadsheet, we could probably have produced a fairly decent humdrum kind of financial software services company, which would be in trouble now, but with good management might have been able to weather the storm. It wouldn’t have been the company we planned for, and we probably would have had to be replaced as a management team at some point, but it could have worked as a company. If, on the other hand, we’d rejected their advice outright and gone ahead with the regular commercial product, with appropriate marketing, we could have been a significantly bigger force in the spreadsheet market by now — as one of our users put it in an email, we could have got further in “rescuing the world from VBA” (a worthy cause if ever there was one).

The net result of this is that we’re simplifying; we’re going whole-heartedly for our original plan, and dropping the enterprisey nonsense; like a Unix shell command, we’ll do one thing, and do it well. Naturally, we’ll keep to our existing commitments to our enterprise clients, but from this release, Resolver Systems is a company producing a spreadsheet product. It’s a souped up spreadsheet for people who don’t want to make costly mistakes. For people who want to be able to see and control the steps taking place as part of the spreadsheet’s calculations. For people who want to know that if they add a new row or column, they don’t need to remember to change umpteen formulae elsewhere.

It’s called Resolver One, and we released version 1.3 this week. Let’s see if we can rescue the world from VBA.

[UPDATE] Coincidentally, Dharmesh Shah at OnStartups just made a post arguing for precisely the opposite strategy :-)

[UPDATE] Elsewhere, Laeeth suggested that the “do now what you would do if you were in trouble” might have been Andy Grove of Intel, who said something like “if we were fired and replaced by new management, what would be the first changes to strategy they would make?”

Book meme

13 November 2008

Via the unofficial planet python, specifically Grig Gheorghiu

  • Grab the nearest book.
  • Open it to page 56.
  • Find the fifth sentence.
  • Post the text of the sentence in your journal along with these instructions.
  • Don’t dig for your favorite book, the cool book, or the intellectual one: pick the CLOSEST.

Here’s mine, from A Vertical Empire by C. N. Hill (a history of the british space programme):

“All this wrangling cannot have helped the progress at Saunders Roe, or the prospect of trying to sell it to the German Air Force.”

Meh. Not terribly exciting. I should have gone for the cool book :-)