A sad tale about spreadsheet templates

  • Tagged with:
  • Errors,
  • Sumwise Application,
  • templates,

Spreadsheet templates hold such promise. A good template can save you hours of time you would otherwise spend creating your own spreadsheet, and gives you the benefit of someone else’s expertise. The sad fact though is that spreadsheet templates are fundamentally ‘broken’, and the concept of a good template is completely illusory.

Act 1: A sad tale.

Yesterday I needed a simple cash flow forecast model. Instead of firing up Excel or Google Docs and starting from scratch, I decided to look online first. So, I aimed my browser at the Google Docs template site and searched for a “cash flow forecast” spreadsheet. I got just 1 result! Undeterred, I clicked the button to preview this model and thought it was pretty close to what I needed—I could just add a few more rows, rename some things, and save myself some time and effort.

But after playing around with this cash flow forecast for a few minutes to understand how it worked I discovered that a key formula cell had an error—and not just a minor error—but an error that unless fixed would render the entire model useless. Now I’m not the typical spreadsheet template user. As someone whose career has been built around spreadsheets I almost always build my own models. From the ground up! Every time! The people this template is aimed at have limited spreadsheet skills, and I suspect they would not have picked up this mistake.

The Opening Bank Balance for April had a hard coded value instead of a formula

After fixing this error I started cleaning up this model so that I could use it. It wasn’t obvious which cells contained inputs and which contained formulas. This is a key point, as zeroing or clearing the contents of formula cells would clearly be a problem. Ideally the formula cells would be somehow locked or read only, but this feature does not exist. Next I needed to add some rows to the cash payments section, but I got a little nervous that I was going to mess up the structure of the model. It took me a while to figure out exactly which formulas could be safely copied and pasted to the new rows to get them to work properly. All in all, something that you would think would be fairly simple turned out to be fairly risky and perhaps not worth the effort.

I won’t bore you by listing all the other templates sites I’ve visited; but there are quite a few. The story is the same everywhere you go though. Even templates that don’t have errors (how would you know though?) generally have little documentation, arcane instructions to ensure you don’t mess up, weird and wonderful formatting (this is not a good thing), and are poorly constructed.

Act 2: Why is this so?

Spreadsheet vendors like Microsoft and Google seem to assume that everyone should be a spreadsheet author. This is the 1:1 model where the person who builds the model is the person who uses the model. This is fine if everyone is a skilled spreadsheet developer and knows what they want and how to build it. But, most people I’ve met who build spreadsheets admit that they’re not that good at building spreadsheets. But they still do, and they rely on the results of their models to make important decisions.

In any case, why should it be that spreadsheets are intended to be used only by the person who built them? Why shouldn’t the world work in way where the most skilled spreadsheet developers do the building and the users do the using. The current state of spreadsheet development is the technology equivalent of subsistence farming, where everyone, from junior analysts to CEOs, is out there tapping away at their keyboards building their own spreadsheet models from scratch, and just for themselves. This is so inefficient, and such a poor allocation of resources.

I’ve thought about this issue a lot and have come to the conclusion that it’s due to two main reasons, one of which actually drives the other. (So perhaps that’s really one main reason?) The first reason is related to technology. Spreadsheets like Excel, Open Office, Zoho, and Google Docs—whether they run on the desktop or online—do not lend themselves to distribution and use by people other than the model’s builder. There’s just too much that can go wrong when you give your precious creation to someone else to use. Too much to explain to ensure they use it properly, and too much effort involved in using techniques like data validation, conditional formatting, locked ranges, intermediate calculation worksheets, and VBA macro code to ensure nothing goes wrong when others get their paws on your precious model.

The second reason is that expert modelers have no incentive to build great template models for others. There is no good way to charge users for all the skill and effort you’ve put into a spreadsheet template. Even sites that do charge for Excel templates know that they can’t really protect against people emailing these spreadsheets around to their friends and colleagues, or even worse, having their models copied or reverse engineered and submitted to an alternate template site for a lower price.

The current state of spreadsheet development is the technology equivalent of subsistence farming, where everyone, from junior analysts to CEOs, is out there tapping away at their keyboards building their own spreadsheet models from scratch, and just for themselves.

The bottom line is that spreadsheet applications like Excel, Google Docs and others were just never designed to deal with the issue of people using other people’s spreadsheets. And that may be fine, but it means that spreadsheet templates will never be a serious endeavour. Skilled modelers do not have the technology at their disposal, or financial incentive, to put serious effort into building spreadsheet templates for others.

Act 3: A glimmer of hope!

We at Sumwise are starting to address this problem though. A little while ago we realized that models built using Sumwise were more robust, more extensible, and easier to use than traditional spreadsheet equivalents. Sumwise’s combination of features and user interface make it a much better software platform for building and distributing spreadsheet templates. Also, as a web application, users never need to be given the ‘source code’ so to speak, and so copy prevention, licensing and the like become possible.

We’ve started putting our templates (a.k.a. RUMs) on our site. Take a look and let us know what you think.

No comments yet

Be the first

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

Order free vps for forex and keep calm.