Why Excel Should Not Be Used for Business Reporting


This is the distillation of a thought process that has come to fruition over the years, when the realisation started to sink in that not only is there no plan to move away from Excel spreadsheets for business reporting, the great white hope at the time appeared to rest on Power Pivots to enable everyone to create reports for themselves, in the mistaken belief that this will address all the things that are wrong with current attempts to use Excel spreadsheets for business reporting. A copy of the presentation can be found here, as well as on my LinkedIn account.

Why Excel Should Not Be Used for Business Reporting – 7 March 2014

Hidden Waste

  • Maintenance of spreadsheets is time-consuming
  • How many people do you know who spend at least one day a week, every week, updating spreadsheets for reports ?
  • Too much time spent on recording data leaves little time to do do something about the findings
  • There is never a moment that a spreadsheet can be fully automated and does not require someone to carry it along
  • Each spreadsheet report should show how many manhours it took to compile, in order to reveal the hidden cost

Private Copy

  • By its very nature, every spreadsheet is a localised copy
  • Version control is problematic, especially when individuals “improve” their copy
  • Sharing can be done through email attachments, SharePoint or Lotus Notes databases, but remains problematic
  • Collaborative work, where different people need to add their portion to a common spreadsheet, is clumsy

Data Trail

  • A data trail is virtually impossible to establish, mainly because of frequent manual interventions
  • In a worst case scenario data get transferred from database to spreadsheet and back several times before it ends up in the final report version
  • Because the data layer is inextricably linked to the display layer, new reporting requirements results in rearranging of existing data into a new format to serve the needs of the new report
  • Ultimately this can lead to the nightmare of the “sophisticated” spreadsheet, where only the owner or a few initiated people know how it all links up

Bad Habits

  • Manual input without error checking leaves spreadsheet prone to mistakes
  • Encourages the bad habit of copy-and-pasting as standard
  • Worst of all, when solving problems, it narrows people’s mindset to what and how Excel can solve things (“If all you have is a hammer, everything looks like a nail” — Abraham Maslow)

The Cost of Spreadsheets and Formal BI

The cost of centralised BI and spreadsheets compared

Low lifetime of report or analysis:

  • centralised BI high start-up costs, lead times sometimes months, many needs unfulfilled
  • spreadsheets offer same day turnaround on emerging needs

High lifetime of report or analysis:

  • ongoing spreadsheet maintenance and updates – labour intensive, error prone, not secure
  • low maintenance costs – autorefreshed, secure, robust

Power Pivot – Will It Help ?

  • Hidden waste – doubtful
  • Private copy – doubtful
  • Collaboration – doubtful
  • Version control – doubtful
  • Data trail – probably
  • Data / display layer – possibly
  • Error-prone manual entry – probably
  • Copy-and-paste – no
  • Sophisticated spreadsheet – makes it worse
  • Narrowed mindset – no

Main Drawbacks of Power Pivot

  • Only full loads are allowed, “Append” to an existing data set is not an option
  • Power Pivot is not a data cleanser, meaning that the data set needs to be prepared prior to loading
  • Since Power Pivot runs in memory, you will need huge RAM to run against all your enterprise data
  • Power Pivot for SharePoint requires Enterprise license and SQL Server 2008R2 EE license; the cost (which is very high) may affect the SharePoint implementations of Power Pivot
Advertisements

2 thoughts on “Why Excel Should Not Be Used for Business Reporting”

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