Spreadsheet Worst Practices

I’ve long contended that, behind all the hype about Source To Pay systems and SRM packages and Flex interfaces and eAuction software, Excel remains one of the top 3 software tools for buyers. (The other 2 being Outlook and Google).

So I enjoyed reading this article on CFO.com all about spreadsheet worst practices and how to avoid them. Here’s how the article starts:

There’s little doubt that electronic spreadsheets are the most widely-used financial software application. But they are also the most-abused.

The article clearly struck a chord with CFO’s readership, as they published a follow up with readers’ views.

The CFO article is directed mainly at those who use Excel for number crunching, analysis and what-if planning. So the practices in the article will be of most interest to buyers who use Excel for analysis. But there are also some nuggets that you can pull out of the articles, even if you only ever use Excel for issuing RFQ templates.

The practices CFO highlights:

1. Poor segregation of data. Some people use Excel just as a super calculator. So if you look into a cell you might find the formula “=300000*1.50+158000*1.46+250000*1.20*0.95”. While it might make sense to the person doing the calculation at the time that we are looking at the total forecast spend for three different parts (300000 units at $1.50, 158000 at $1.46 and 250000 at $1.20 less a 5% discount), a formula as bare as this is not going to help explain the data 3 months down the line
2. Poor documentation of assumptions. The last part in my example formula is 250000*1.20*0.95. You could read this as 250000 parts at $1.20 with a discount of 5%. But why the discount? Does the discount always apply? Or is it some volume discount based on ordering over 200000 items?
3. Poor documentation of constraints. Don’t put one complex formula in a cell. Remember in your maths exams when you were always told to show your workings? Same applies in Excel. Better to use multiple, intermediate calculations to show how you are getting to the final result.
4. Difficulties in making changes. If we decided that we wanted to change the forecast volume for part B to 180000 then it’s not immediately straightforward to know where to update the spreadsheet
5. Now it’s here, Now it’s not. The ability to change one value in a spreadsheet and have all the relevant values re-calculated is very powerful. But it’s also easy to lose track of where you were before you started your what-if scenarios. CFO.com’s recommendation is to use different worksheets for different scenarios, with one master worksheet to summarise and compare the results of your different scenarios.
6. Presentation Ready. It’s not hard to set your spreadsheets up for printing – with headers, footers, page sizing, repeating columns and rows. But it’s often overlooked, to the annoyance of the people you are emailing your spreadsheet to.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s