Spreadsheets are powerful tools. In business, they’ve become indispensable. But, they come with huge risks. Let’s briefly touch on a few.

Systems on spreadsheets … not usually a good idea

Around 30 years ago I took over responsibility for a NYC-based asset manager’s systems. Portfolio accounting systems back then were very basic. The one we had lacked quite a bit that’s often taken for granted today, including rates of return processing and client billing.

It was clear given the number of accounts we were managing (over 2,000) that we needed to automate the billing calculation process. Our comptroller, who was hired around the same time as I was, pushed for us to do it on Lotus 1-2-3 (which was much more popular then than Excel). I ignored her, and had it automated. Yes, we could have done it very quickly in a spreadsheet. But, it would have been limited in capabilities, manually intensive, difficult to control and manage. Instead, we built a system that interfaced with our trading system, and provided us with many more capabilities than a spreadsheet ever would have.

When it came time for us to calculate returns, we built a system. After spending nearly five years in the army, I have become a fan of acronyms, something the military is known for. Our return system was called TWIPS: time-weighted investment performance system. One of our programmers claimed that since she worked on it during the day, she must be a “day-twipper”! Get it? Day twipper? You know, like “day tripper”? The Beatles? Oh, well … was funnier back then.

The appeal of spreadsheets includes its flexibility and ease in storing data and calculating information. But, for systems you’ve got to be careful.

The main shortcomings of spreadsheets

As noted above, spreadsheets are manually intensive: they don’t run on their own. We typically need to interface with them. They’re also error prone. Not only is it quite easy to make typos, it’s very easy to make other errors that may be quite difficult to find, especially when the spreadsheet becomes quite large.

Shortly after I left the army I spent a few years programming. Back in the mid-1970s, the trend was to develop software using “structured programming.” The alternative was often referred to as “spaghetti code,” because the program’s logic had the process go all over the place, meaning that you could arrive at a certain point from various places, and trying to really understand the logic could be impossible, which made maintenance extremely difficult. Structured code forced some discipline into the process.

Many spreadsheet systems are filled with such difficult to follow logic that can be quite a challenge to understand. I’ve seen some very huge spreadsheet-based systems, that employ complex macros, where different parts of the spreadsheet tie to other parts.

I was doing an operations review for a client that had a spreadsheet-based performance system a few years ago. The system wasn’t documented, involved a lot of macros, and was virtually impossible to maintain. The developer had long ago left the company, meaning that whenever changes were needed, a great deal of care was needed. My recommendation was to replace it with a programmed system; ideally, a software package.

Controlling spreadsheet-based systems

One thing I learned while I was doing my review was that another member of the performance team was busy building a replacement system: also on a spreadsheet. Whether this was actually authorized or approved wasn’t clear to me, but it wasn’t solving anything, and gave visibility to yet another problem: control!

Programmed systems, be they software packages or in-house developed, typically require a great deal of formality in order to implement and maintain: lots of paperwork, design and code reviews, change management, testing, documentation, etc.

Sadly, the same typically doesn’t apply to spreadsheets, that can rise up anywhere within an organization, and quite quickly become relied upon “systems” for the firm’s operation. Too many firms do not have any process to gain approval to introduce them or maintain them. This can be a huge problem.

Why would we think that a new spreadsheet-based system will be better than the one we already have?

When spreadsheet systems are needed?

To me, spreadsheets are ideal for ad hoc work. They’re also fantastic for testing, running prototypes, evaluating methods to employ. But, in general, they’re not ideal for systems.

But, there are times when they work, and I’ll cite a few examples.

Budget limitations: if your budget is such that you can’t justify the expense for a software package and don’t have the in-house programming skills to build a true system, spreadsheets can often work.

For years, when teaching about the Global Investment Performance Standards (GIPS(R)), I mention that there will be great appeal to using a spreadsheet for your composite(s). You’ll hear a small voice in your head telling you “use Excel; it’s easy; it’s fast; you’ll save money; you’ll look like a champ!” I say that this is the “voice of the devil.” Be careful. Too many firms went down this path only to regret it later on.

But, if you don’t have the money, a spreadsheet will work: in fact, we’ll give you one!

Unique needs: if there isn’t a software package available and you don’t have an IT team, you may need to use a spreadsheet. Consider a case where the model you want to use for fixed income attribution doesn’t exist in any packages. Then perhaps building it in Excel is the best approach.

Temporary: if it’s for temporary use, then Excel can be ideal. But, be careful that temporary doesn’t become permanent and long-term.

Spreadsheet systems: making it work!

If a spreadsheet is going to work, you’ll want to treat it like any other system, though. Make sure you document what it does. Make sure you do testing on it. Make sure you limit access and have controls built around it. Understand how the data will be entered: if manual, what checks will be used to ensure it’s correct.

Years ago I was responsible for the IT (although we called it “systems”) department for a broker/dealer. We had around 25 keypunch operators (I know I’m dating myself). This was how our traders trades got into our trading system (which resided on a Stratus computer: a “fault-tolerant” mid-range computer that no longer exists). We didn’t assume that our operators keyed in 100% accurately: we also had around four “verifiers” who verified everything that was entered: i.e., the work was done twice! And yes, errors were caught.

What will you employ to avoid errors in data entry?

As noted above, programmed systems typically have a formal process around changes: what change control will you have for your spreadsheets?

I love spreadsheets (I actually favored Lotus over Excel, but have, after a couple decades use, have grown to like Excel, too). And, I use them daily.

I use them for research and support for articles I write. In a recent series of tests I made some cell errors that resulted in nonsensical results. It wasn’t until I painstakingly went through my cells did I find my goof. I’m sure you’ve “been there, too”!

I use them when conducting GIPS verifications. We have one that identifies what we are to test for, and I use one for each engagement. I use them for testing (returns, standard deviation, etc.). But, I have to exercise care to make sure I’m using them correctly: I make entry errors, too! Sometimes my cells get screwed up. But, they can work quite well.

Using spreadsheets? Great! They’re cheap, powerful, and easy to use. Just be careful if they morph into systems, where better alternatives exist.