Excel is not always the answer

Whilst this may seem like quite a controversial statement, particularly to accountants and finance departments, please bear with me.

Firstly, by way of reassurance, I am a massive fan of Excel and have been actively using it for over 20 years on both Mac and PC.  It is an amazingly flexible and canvas-like framework for all manner of business and personal activities, ranging from fuel consumption monitors, through complex financial planners and even interactive games.  With determination you can do almost anything in it (imagination and skill withstanding).

Now the note of caution - whilst Excel can be used for almost anything that's not to say it should be.

Throughout my professional career I have seen all manner of Excel-based solutions to things varying from the mundane to the ridiculous (mainly the mundane, sadly).  Whilst many of them were legitimate uses and "did the job", a number  of them followed one of several possible scenarios:-

  • one or more operators following a list of Commandments every week / month-end / quarter to produce something that they don't really understand and subsequently "massage" to get the information they need
  • a horrendous labyrinth of inter-linked Excel Workbooks saved across desktops and network drives (usually "H:" drives, for some reason) with a laundry-list of broken VLOOKUPS and circular references
  • the swan-song of a (no-longer-with-us) "Excel Expert" who also dabbled in Visual Basic - the users of these spreadsheets tend to click "Cancel" on any errors that pop up and work around the functionality rather than with it
  • a giant workbook that takes considerable time to open, considerable time to populate with a host of system exports, and after several days of hard graft produces Management Information (MI) for the departmental decision-makers
  • a technicolour yawn of a series of spreadsheets that someone (presumably a fan of Piet Mondrian's early work) has tried to add a layer of intuition to using a somewhat poorly judged colour palette

Although people can work with all the above scenarios, they shouldn't have to.  The main rationale behind this being that IT / Technology is supposed to make everyones' lives easier and "a happy worker is a productive worker".

So what would be a suitable alternative?

Well, for starters, anything deemed as "Management Information" or "Business Intelligence" should be:-

  1. Easy to find
  2. Easy to generate
  3. Easy to interpret
  4. Useful
  5. Secure

The first of these points, easy to find, is fairly simple to address through judicious use of shortcuts, logical presentation and training.

The second, easy to generate relates to both the process of generation and the time taken to generate.  If high to medium frequency MI is either complex to generate or taking significant time to prepare, this must be addressed.  As long as the logic driving the MI can be explained and extrapolated from a businesses source system(s), then the process can be streamlined through the judicious use of data definition languages, programming and presentation frameworks.

As mentioned above, presentation frameworks (typically in the format of Reporting frameworks, such as Microsoft SSRS, Crystal Reports and other tools) are an ideal candidate for generating easy to interpret MI.  Excel does indeed "excel" at this as well, through appropriate use of tools such as Pivot Tables, Slicers, Graphs and even PowerPivot (where a business has a compatible Sharepoint installation).  Of course "easy to interpret" closely related to the target audience.

Technically all MI should be useful but what is meant here is that the MI produced is useful, for example - a Productivity or Aged Debtors report is by definition "useful" but if it's presented in a format that needs dual monitors or an A1 printer to fit it all in then it's not particularly easy to share.  "Useful" in this sense means that the data provides a suitable summary level of detail for the intended purpose, with an option to drill down into further detail as necessary.  This granular approach to data presentation means that an overall trend can be established and the cause of any anomalies can be zoomed in on for further consideration.  Some reporting frameworks also allow for automated generation and delivery (subscriptions) - which actually covers points 1, 2, 4 and 5.  All you then have to worry about is whether it's easy to interpret!

Information security is highly important in today's connected workplace - MI relating to staff pay levels and performance, for example, need to be restricted appropriately (although there are exceptions).  Whilst you can password protect an Excel workbook, this is not always infallible.  Better to control access using something a bit more robust like, for example, Windows logins or Groups or alternatively tapping into the same security framework that is already used in a core system.

At Donkeylogic we take all the above points into consideration when planning solutions for our clients and we quite honestly take great pride in building robust and scalable solutions.  We are not only BI consultants, we are also system designers and graphic designers so we can build almost anything from the ground up and make it look appealing.

We are more than happy to engage in a no-obligation chat, so please Contact Us if you would like to find out how we can help.