Wil-fred’s Most Excel-lent Adventure

Published 27th July 2016

Right from starting school, I have been fascinated by numbers and this took me on an educational route that involved studying maths and economics at various levels right up to degree level.  So when I entered the world of work, this meant that I performed a variety of roles where the word ‘analyst’ was in the job title as I would get to apply my advanced numeracy in a business context.

Working as an analyst, I quickly became highly proficient in the use of Excel and gained a reputation as an Excel expert.  As a wide-eyed twentysomething, I loved the feeling of making the impossible possible either through the crafty use of a formula, the marvellous use of a macro or the inspired use of a pivot table.  Quickly, within the various organisations I worked within, I’d become the go-to person that could use Excel to prune a thorny business problem.  Tasks that would take someone a day, I could reduce down to thirty minutes.  I’d quite often get a burst of satisfaction as I found a quick, efficient and accurate way of solving a problem that was quite often more rewarding than the praise I’d get from astounded colleagues. 

However, the danger with being effective is that the spreadsheets that you developed would get pushed beyond their limits.  In one organisation where I worked, I redesigned a spreadsheet that was used to keep track of a credit control process.  Previously, this spreadsheet had only had seven hundred rows of data representing the seven hundred unpaid debts.  However, after I redesigned the spreadsheet, it became more effective.  The management of that department were able to monitor pipeline progress and target their staff on how much cash was being collected.  However, this spreadsheet became a victim of its own success and, at one point, had four users of the spreadsheet simultaneously using it and eight thousand lines of data.  This is quite often a problem that I found frustrating with Excel that while it can handle millions of rows of data, it doesn’t do it that fast and there are dangers that when you have huge files that are being edited by multiple users that the spreadsheet is liable to either run slow or become corrupt.  I thought to myself, “There must be a better way.” 

In another role, I quite often had to cleanse data from various legacy systems.  Yet again, it is satisfying finding a clever Excel formula or function that cleanses the data ready for analysis but then what happens if the formulae you use don’t cover the full range of data.  How frustrating was it when the files you were manipulating were huge?  I would try and come up with robust solutions that tried to anticipate future issues.  I wrote instructions and documentation for various contingencies but quite often it felt like walking a tightrope while balancing an egg on a spoon to accurately deliver timely information. Yet again, I thought to myself, “There must be a better way.” 

And in a lot of business situations, it’s not just Excel that lets you down but the organisational issues that you have around spreadsheets being so prevalent in business.  I have helped co-ordinate budgeting and capital expenditure approval procedures before and a lot of staff time is wasted.  You would not have a clear audit trail of what changes had been made and by whom and a lot of time was spent trying to maintain records of what approvals had been granted, what feedback had been given and what was left outstanding.  And yet again, I was left thinking to myself, ”.. there must be a better way. “

Luckily, in the last couple of years, I have become aware of IBM’s TM1.  In the next blog post, I’ll explain how both my colleagues and I have helped various companies around the UK solve the common business problems that used to frustrate me.