REanalyze This!: Spreadsheet

Showing posts with label Spreadsheet. Show all posts

Thursday, April 5, 2012

Rob Ende Guest Blogging at INFORMS Business Analytics Conference

I am very excited that I've been selected as a guest blogger for the INFORMS 2012 Conference on Business Analytics & Operations Research.

The conference takes place April 15-17 in Huntington Beach, CA, and is the leading venue where world-class organizations showcase the strategic use of business analytics and O.R.

The conference blog can be found here, and the blog will replace the conference home page once the conference begins. I will also mirror my posts right here on REanalyze This!

In addition to taking in some great presentations on the success of Business Analytics in areas such as Supply Chain and Forecasting, I will be co-facilitating a discussion group on best practices in spreadsheet modeling:

Garbage In, Gospel Out? Join Rob Ende, CEO of REanalyze Inc. and SPRIG President Rick Carter, CEO of Equation, in a discussion of tips and tricks on spreadsheet design, development and auditing to prevent errors, reduce risk and ensure quality results.

Please check back for conference updates starting April 15. 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.

Wednesday, January 4, 2012

What’s Lurking in your Spreadsheets? (Part 2)

Note: This series of posts is not intended to be a “How To” guide. My purpose is to highlight some common issues and mistakes in spreadsheet development and how to avoid them and the resulting risk to your business. Even if you don’t develop spreadsheet models yourself, hopefully this will help you know what to look for and questions to ask those who DO develop spreadsheets for your business. If you would like more detailed “how to” instructions and examples on any of these topics, I refer you to a good Excel reference book like the Microsoft Excel 2010 Bible by John Walkenbach.

In a previous post I discussed the prevalence of poor spreadsheet modeling practice in most organizations, and the resulting risk to the business. In that post, I covered one very common mistake – using fixed or hard-coded values or constants in formulas.

This time I would like to cover another common type of spreadsheet error: incorrect cell references or ranges. You are probably familiar with Relative and Fixed references (even if you didn’t know what they were called).

“A1” is a Relative reference referring to Column A, Row 1. If you copy or drag a formula to the right, the A1 becomes B1, C1, etc. If you copy or drag it down, it becomes A2, A3, etc. In other words, the reference is “relative” to whatever cell you’re in.
“$A$1” is a Fixed reference. No matter where you copy or drag it, it remains $A$1 and always refers to the same cell. In other words, the reference is “fixed” no matter what cell you’re in. And of course you can fix just the row (e.g., A$1) or just the column (e.g., $A1).

Each type of reference is appropriate in different circumstances. The problem is that it’s easy to use the wrong type in a formula and not realize it. Then when you copy and paste the formula somewhere else, you may end up referring to incorrect cells and getting incorrect results. Things may appear OK but the calculations may be totally wrong!

It’s critical to review all the formulas in a spreadsheet model to make sure the correct types of references are being used. You can also use Excel’s built-in auditing features, e.g., Trace Precedents, to look for mistakes.

Here’s another neat trick: Press Ctrl and the accent key (it looks like ` and is next to the “1” key on the top left of your keyboard). This turns on the Formula View which shows the formulas in every cell on your worksheet. This is a great way to scan your formulas for errors, especially incorrect references.

Perhaps the best way to prevent incorrect references is to use Range Names. To name a cell or a range of cells, just select the cell(s), click in the Name box which can be found toward the upper left corner of the window, type in a name and hit Enter. Then, whenever you refer to that cell or cells in a formula, you can use the Range Name instead of entering the cell reference. Here’s an example, first with a normal reference to cell A2, and second where cell A2 has been named “Margin”.

a. = SUM(B3:B6)*A2

b. = SUM(Q4_Sales)*Margin

Why is (b) so much better than (a)? Three main reasons.

First, when you’re writing a formula you don’t need to remember that the margin is in cell A2; you just type “Margin”.
Second, you don’t have to worry about using the wrong type of cell reference. If you write a formula using “A2” (a Relative reference) instead of “$A$2” (a Fixed reference) and then you drag or copy it to other cells, you will end up with the wrong values. If you write a formula using “Margin”, you can drag or copy at will with no concerns.
Third, it’s much easier to understand what a formula is doing. In example (b), it’s easy to see that you are multiplying Sales by Margin. Not so in example (a).

In future posts I’ll continue to point out other problems to look for in spreadsheet models, along with some best practices to reduce the risk to your business.

Meanwhile, I’ll close with the same question from last time: 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

_________________________________________________________________

Tuesday, December 6, 2011

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

_________________________________________________________________