• Technology
  • CFO.com | US

Sloppier Spreadsheets: How Bad Can They Get?

Readers weigh in again on Excel sins and solutions. Tips include totaling at the top, while traps like poor version control can even threaten to force restatements.

Tip: An Alternative to the Negative Sign

To CFO.com:
I loved the major improvements Richard Block made to the before-and-after worksheet. But I prefer to format any negative numbers or percents into brackets, instead of showing that little bitty negative sign before the number.

I believe the brackets are much easier to see, and highlight something that is “bad news,” and possibly should be addressed. While I’m on the subject of percents, I also would have formatted the percent calculations to be carried out at least one decimal place. In any case, with or without the decimal place, I would have written the formula so that the final result is either rounded up or down using the “round” function. —Thomas Mroz

From Richard Block: Thomas, I agree with your comments. I too prefer to format negative numbers using brackets instead of the negative sign, so I’m sorry that I didn’t do that in my previous example. Brackets are much easier to see. When I am formatting numbers in a difference column, such as “Actual vs. Budget,” I use the B/[W] or F/[U] to ensure that the reader knows that brackets (or parentheses) are going to indicate a negative or unfavorable difference. While I use the round function often, I sometimes set it to one decimal point when calculating percentages as it sometimes takes tenths of a percent to highlight a difference that matters.

Trap: Dressed Up, but Nowhere to Go

To CFO.com:
My pet peeve is receiving a spreadsheet that is all dressed up — i.e., formatted correctly with macros, formulas tied together nicely, etc. — yet the one who produced it cannot produce the data behind the spreadsheet. Without the data such a spreadsheet is nothing but window dressing for bad data. I see it happen all the time. They believe a spreadsheet that looks and performs well compensates for questionable or no data. No matter how easy to use, read, interrupt a spreadsheet is, if the core assumptions and data cannot be provided, then it is just a pretty trash can, GIGO. —Rod Ferrara

From Janice Bell: This reminds me of an experience years ago when I was on a strategic planning committee, making decisions that would impact operations of a college for years to come. Committee members were provided “data” in a spreadsheet with nice charts that showed program ratings offered by students, alumni, employers, and faculty. The faculty ratings were quite surprising given my knowledge as a “person on the street.” When I asked to see the data behind the chart I was provided raw survey forms, untabulated in any way. I tried to compare the raw data to the spreadsheet inputs, and found that the spreadsheet data was in summary and not the raw data I’d been provided. I then tried to recreate the summary data and found that the data in the spreadsheet had not been created from the raw data after all; it had been changed by a small group who didn’t like the original results. This was truly a case of GIGO, but everyone had been very impressed because of the “authority” of the spreadsheet.

Discuss

Your email address will not be published. Required fields are marked *