What’s Lurking in your Spreadsheets?

What’s wrong with this formula from a spreadsheet model?

=A2*.18

Seems simple enough.  Imagine you’re calculating an annual inventory holding cost.  Cell A2 contains the inventory value in dollars, and you estimate the annual holding cost percentage at 18%.  Multiply them together and you’ve got the correct answer, right?

Technically, yes.  But…

• What if the holding cost percentage changes next year?  Will you or an analyst or whoever uses this spreadsheet remember to change the formula?

• What if different items have different holding costs?  For example, obsolete items might require a higher value due to the risk of write-offs.

• What if someone passes this spreadsheet along to a colleague working on a different product line, where the holding cost percentage should be 25% instead of 18%?

Any time a parameter like this is fixed or “hard-coded” in a formula, rather than in an input cell, you are much more likely to get burned in one of these situations.  If you are lucky, this is only an informational report.  But what if it’s in a financial model that you use to set operating budgets?  Or to calculate someone’s bonus?

In this case, the solution is pretty simple.  Just put the holding cost percentage into an input cell (or maybe a range of input cells for different products).  Highlight the cell with shading and borders for good measure.  That way anyone who uses the model can easily see the value and change it if necessary.  Even better, give the input cell a name like “HoldingCost” and then you can put it into a formula anywhere in your spreadsheet without having to remember what cell it’s in.

This may seem ridiculously easy, and it is.  But you would be amazed at how prevalent poor practices like these are in otherwise very sophisticated companies.  Virtually every business uses Excel spreadsheets to a lesser or greater extent.  And no matter where I’ve been in my career as an analyst, an executive and a consultant – small and large companies, different functional areas, various industries – I have seen poor modeling practices and outright mistakes in almost every spreadsheet I’ve come across.   In fact, some people have remarked that I have a special talent for finding this stuff!  Some are obvious formula errors.  Others, a result that “just doesn’t look right” based on what I know about the business (note:  the published research on spreadsheet errors is a little scary: See this literature review from Powell, Baker & Lawson or this research article from Panko).

When you get right down to it, spreadsheets are software that perform some function or process for your business.  But unlike other types of software, which are usually developed by trained software developers with a controlled process, just about anyone can create or modify a spreadsheet.  Think about all the rigor and quality control that go into typical software development in your company.  Then think about whether your spreadsheets are getting a comparable level of attention.  If not, you could be introducing a lot of uncertainty and risk into your business.

In upcoming posts I’ll point out some other problems to look for in your spreadsheet models, along with some best practices to reduce the risk to your business.  You may consider starting with a "No fixed values" or "No hard-coded constants" rule for your spreadsheet developers.

In the meantime, here’s something to think about.   

Do you run critical parts of your business on spreadsheets?  If so, have you thought about where and how they are being developed, and what sorts of errors may be lurking inside?

Until next time…

Rob

_________________________________________________________________

Rob Ende is Founder and President of REanalyze Inc., a Supply Chain, Inventory Management and Analytics consultancy based on Long Island, New York.  Rob can be reached at 631-807-2339 or rende@reanalyzeinc.com.

© 2011 REanalyze.Inc.. All Rights Reserved.