Lies, Damn Lies and Spreadsheets

I’m sure most of you have heard the phrase lies, damn lies and statistics, which bemoans the fact that statistics can be created and presented to say anything you want them to say.

Talking with colleagues and clients recently, it struck me that in most cases, we face the same challenge with spreadsheets. Let me explain.

In most organisations, the pricing team will pull data from the ERP system, either directly or via a Business Intelligence tool, and will use spreadsheets to interrogate, run calculations, create scenarios and finally decide on a new pricing table. All fine so far. Except …

There are usually a number of analysts who are involved and often, different people are preparing material each time a review is done. Everyone who uses a particular spreadsheet always has a better idea about how to create a calculation or present a view or capture some additional nugget of information. That means that after two or three review cycles the spreadsheet is completely different from the one that you started with.

If the pricing activity is spread across multiple countries or business units, the problem is compounded; and the idea that you can simply take the spreadsheets and consolidate them into a single view is quickly dashed as you find the individual changes made by the different users make consolidation a real challenge.

Of course, another challenge for spreadsheets is the lack of governance. Just imagine the sequence. Scenarios in a spreadsheet are circulated by email, and a manager or managers send an email confirming which version that they are approving. The pricing team load the price into the ERP. What happens 12 months from now when the auditor asks …. “The ERP shows that Fred loaded the prices and that Joan approved the system change. Who approved the new price in the first place?” Could you find that email exchange easily?

Coupled with the fact that a spreadsheet is only as good as the formula and links you build into it or the initial data that you started with, you can see why I have concerns about running such important data through a spreadsheet, and then feeding it back into the ERP.

More and more organisations are realising that using spreadsheets to conduct analysis, create possible scenarios and share information represents challenges in terms of version control, managing approvals and meeting audit requirements. Implementing the newly agreed price in the ERP system effectively and quickly can also be a challenge when uploading from spreadsheets. As the processes and the practice of pricing matures within a business more sophisticated tooling is becoming much more necessary to handle the growing mass of data that our businesses generate every trading day. Analysis by spreadsheet just takes too long.

If you recognise these challenges and want to find a better, more consistent and auditable way to crunch the numbers and keep on top of your pricing challenges, do get in touch to arrange an initial discussion with one of our consultants.

Barry Edney – December 2021

Without data, you’re just another person with an opinion.

W Edwards Demming