From Richard Block: I agree with your first comments 100 percent. Hard-coding numbers and formulas into cells is a formula for disaster.

You also bring up a good suggestion for when spreadsheets are used to receive and report monthly data. Downloading monthly data from another system is common. What isn't common is downloading the data in a consistent format, or storing it in a separate, well-designed tab within the spreadsheet. (Say you want a tab with 12 months of data so reporting and analysis can be performed.) Most ERP systems allow you to do this. For users at smaller companies, when downloading monthly from QuickBooks to Excel, for example, a new tab within the the existing spreadsheet automatically will be created. Once downloaded, the monthly data can be copied into a monthly column, so by the end of the year all 12 months of data can exist in one tab. If all the data is now formatted consistently, then using Excel's lookup features (vertical or horizontal lookups) can be an efficient way to report or analyze monthly trends.

To CFO.com:

The worst overall use of a spreadsheet I have seen is when a new college graduate is asked to add two numbers together. Instead of using a ten-key, they spend three minutes on the task using a spreadsheet. —Phil Medler

From Richard Block: Phil, I laughed out loud when I read your comment. At least the new grad knew that you could add two numbers within a spreadsheet. I once asked a new director of marketing for a budget. A week later, after no budget was submitted, she reluctantly admitted that the delay was caused by her not knowing how to use a spreadsheet. Feeling a bit guilty, I created a template for her, labeling the columns in months and quarters, and the rows with specific marketing expenditure categories. When another week went by without a budget submission, I went to find the cause of that delay. I observed her typing numbers into the January, February, and March columns, and then using a calculator to add up the first quarter results in the Q1 column. When I explained how the spreadsheet could do this, she looked at me as if I had performed a great magic trick.

Final Note from Shahid Ansari: In my class, I just finished teaching a case in which my students had to come up with a new production plan, and to try several other what-if assumptions, including labor mix, increase in quality, and decrease in inventory. All were graduate students who work for major U.S. corporations and have at least 12 years of work experience.

It became painfully obvious that the team members I asked to lead the discussion had followed all the worst spreadsheet practices in our first article. They had numbers in the formulae; they had fonts and labels that were hard to follow; there were no data sheet links to allow what-ifs, and on and on.

I stopped the class and asked everyone to go the CFO.com site and read the article.

Obviously, there is something about spreadsheets that tempts everyone to be lazy. And that suggests one more tip for spreadsheet users: To go fast you must go slow.

The time spent organizing the data and assumptions pays off when nonfinancial managers ask for the profit implications of changing the labor mix, or reducing inventory, or increasing receivable turnover.

• « previous
• 1
• 2
• 3