Breaking Excel – how one company liberated its Board!

Two customer meetings last week bought up the very same issue that I have been tackling for some time now.  Both were running queries on a database in SQL and the result set from then query was output into a csv file.

Once the output was generated, the respective analysts then reformatted the csv file and started to manipulate the spreadsheet by creating pivot tables.  After making sense of and making sure they had the right data, they started to build charts and various reports to slim the data down, to send to various consumers that needed the information to make decisions.  In some cases, this took almost a day to get this much needed information extracted, manipulated and reformatted.

Once the consumers received the charts and various slimmed down reports from the overall raw data set, guess what happened?  Yep!  They asked for more and more changes, to be applied to the original outputs, which then took more time for the analysts to be taken away from their own day jobs to run these queries. How long did the analyst get consumed doing this type of work – 2 – 3 days a week!

I knew where this was headed and it reminded me of a customer that had a similar situation where we cut out the middle man so to speak (the middle man could go about their daily job more efficiently), and improve processes and procedures by taking a very manual process and automating it.

Reporting by Excel an example

Here is how a company managed their data and reporting process using Excel, before we implemented a business intelligence solution:

Day 1

A reminder email typically goes out (at the beginning of the month) to operational managers, finance managers, sales managers and HR managers stating that they need to send in their data to a central reporting analyst on a specific date, for the analyst to start assembling the monthly reporting pack for the Board and CEO.

Day 2

Each of the managers then sends a request to the IT department to extract the data from the source (Oracle CRM, Sage etc.) and they need it in a specific format as they need to copy and paste the data into a target template file.  Now while doing so, they may have found some errors in the data or need some more extracts so they must go around the houses until they have the optimal data set and are happy.  The process takes a few days – to extract, get the data right, find some anomalies, correct it in the source system and start over.

Day 4

The source department (Operations, Sales etc.) pass the Excel template onto the analyst, who then starts assembling / merging all their data into the target Board / CEO scorecard for the four areas mentioned. The analyst will then have to manipulate the data from each department or go back to the department as something doesn’t look right. A few more days’ pass…

Day 6

All the data that was queried is now back in the required format to the reporting analyst. The analyst must redo the master report, check all the figures manually, make sure all the formulas are working, and then starts to prep all the charts so that there is some form of pictorial view the Board & CEO can see.

Day 8

The HR department have found something wrong in the data that they originally provided the analyst, and have sent a new batch of data that needs to replace the original data. The analyst now needs to redo that data in all related spreadsheets.  This won’t be the only department to do so and a flurry of activity typically comes in from others that have made mistakes.  Formulas need to be checked and double checked, results need to be tally and wow this is becoming quite strenuous just reading it!

Day 10

The analyst now recreates the Board / CEO scorecard, and redoes the charts etc.  Phew that’s all over until next month!

Day 11

The longest day! The analyst is up against it and working all manner of hours to ensure that the report is completed for the Board / CEO meeting.  The analyst sends the entire report which is a series of 20 tabs in one or multiple Excel files. There is also a report that needs to be put together by the CEOs PA that will take blocks of information along with the charts highlighted by the reporting analyst and copied and pasted into PowerPoint.

Day 12

The meeting now happens where the Board & C-level executives gather to go through the report and see what is happening in the business. All the reports have been printed (mostly in A3 very small and the rest in PowerPoint), and everyone including the analyst is in the meeting.

The CEO and the CFO are looking at the number “13” in column 165 cell AA and ask the question – how did we get that number for that part of the business. The CEO looks at the HR director, the HR director looks at the analyst and the reporting analyst must go back into the data in their laptop and start to fumble around for an explanation of the number 13. Someone then makes a guess that it means x and there is a huge sigh of relief from the analyst – phew!  No one knows whether it’s the right explanation – just glad that someone can offer a view!

Day 13

There are a lot of queries about numbers from the executive meeting and the analyst now has to go and find what they mean, by speaking to the various departments and making sure for the next round of meetings they can explain the number 13 !

This is not a made-up situation, nor is it a joke for the analyst and the C-Suite. This is how reporting occurs in many organisations.

Do you want to be liberated?

In the above situation, it was a fairly simple exercise to put a business case together with benefits and yearly savings of c£400,000.  Once the business case was approved, we implemented a centralised database, brought all the data together from the other systems, cleansed data where needed and put a visual analytics layer on top of the data as the presentation layer to consumers.  We also embedded data governance principles into the business that ensured data going into systems was of the right quality (instead of the adage “garbage in, garbage out”), and making sure there were clear owners of the data.

The main benefits this provided to the user community were:

• Automation – no need to ask the IT department to extract data and then analysts having to spend time manipulating ad nauseam.
• Self-service – consumers can access their data anytime, anywhere and literally on any device. More flexible and no pain.
• Trends & Insights – being able to see the trends in the business and specific insights into how operations is performing and how specific interventions applied have impacted the bottom line – seeing a direct correlation from the two sources instantly.
• Interacting with data – as above users can query data that is near real-time, not waiting days to get at the data. As you saw from the example, data in the excel spreadsheet was already out of date as it was for a fixed period (or of a static nature).  In this case automating and visualising the data increase the frequency, timeliness and quality of the data being able to see hourly and daily trends and not having to wait till the end of the period.
• Root cause analysis – static reports will typically inform users when things go wrong for example it might conclude that sales are down in a particular region. It won’t be able to provide you with the “Why” the sales are down and the ultimate reason for it and make a comparison as to why other regions might be performing better.  Visualisation tools can provide a level of depth that static reports cannot do, and ultimately allows the users to dig deeper to make better decisions.
• Telling a story through the data – visualisation tools can provide different views of data. For example, heat maps provided this customer with a view of their customers’ segments in different colours and drew out more questions about why specific customers were outperforming others.  Which ones were sitting dormant and were going to churn etc.  Questions asked by the Board & C-Suite were quicker to draw out and provide a better response by the analyst.

I could list many more benefits that were “front-end” related.  There were of course many benefits that were “back-end” related.  Such as:

• Better business intelligence – wider access to better data not just for the Board & CEO, but for different departments to start interrogating their performance to a different level than ever achieved before.
• Increased query and system performance – greater speed for users to access their data, and for the company to store larger volumes of data for analytical purposes, rather than depending on operational systems.
• One source of the truth – data is available from all the multiple disparate sources in the business and consolidated into one place – the database. There is no need to have duplicate data stored in different silos, it can commonly be stored, retrieved and defined in standards ways so that data across the business conforms to specific definitions.  Hence the adage “one source of the truth”.
• Timely access to data – there is no need to depend on someone to access the data and bring it together for you. The data load is automated from all the sources so that it is “extracted (from source systems), transformed (manipulated) and loaded” into the database.  Commonly this can occur every fifteen, thirty, half a day, or at daily intervals – so that different users that consume data can use it for their own analytical purposes.  For instance, in operations they may need it every fifteen minutes to spot trends and bottlenecks in their processes, or marketing may need to see how well a campaign performed historically, therefore, not needing the data instantly.  Different use cases for different departments.
• Improved data quality and consistency – when moving data from all those disparate siloed systems into a centralised database, it allows for the reformatting of data or putting into a common format to be consumed. A use case that springs to mind is typically the “Single Customer View”.  To get customer data from many different systems and ensure that all the data on a customer’s name, address, phone numbers, account numbers etc. all point to the right individual so that you can get the truest picture of that person.
• Historical business intelligence – companies do collect inordinate amounts of data these days and in most cases, don’t know what to do with it! Operational systems aren’t catered to store historical data.  I’ve just been at a conference where the head of data management from Specsavers had to convince the CEO and the board that they are in fact a data company – if you think about it – everyone is a data company!  So, the advantage of having all this data for a company like Specsavers provides them with a backward view, and some historical trends that might tell them something about the past, and if they use predictive analytics will almost definitely tell them something about the future.  Historical insights in their case can provide medical benefits too looking at the deterioration of eyes of certain demographics in certain locations over time!  Pretty awesome!
• Higher return on investment – simply put if you are messing around and attempting to get data from this or that system every month and churning that data using very manual processes it’s going to cost a lot of money. If you think it’s not then you are kidding yourself!  If you put it all your data in one place, and have the automation in the background to support the data being refreshed, then your costs will dramatically reduce!  It’s a no brainer and with new cloud technologies becoming easier and more secure to use, as well as cheaper, companies must start to wake up and smell those coffee beans…as I am smelling in this café right now!

It’s time to quit!

If you are still using manual processes to get insights and consuming countless hours of employees who could be doing value based work, then it’s time to stop!  Data is too precious to keep wrangling with it, invest the time in gaining an advantage over your competitors and becoming a data-driven business.  The companies that are competing on analytics are winning and those who aren’t are being disrupted.  Do you want to be one of those organisations that sits back and watches your market share decrease because it took too long to manually get insights out of your systems and data?  There are many companies out there that are going to be hot on your heels, you only need to look at Airbnb for that – the largest provider of hotel rooms in the world, only because hotels didn’t become data-driven quickly enough!

The two companies, I spoke about at the beginning are moving towards a more data-driven focus and repositioning their strategies.  Don’t let Excel be your reporting tool for complex insights, it won’t be able to stretch that far.  Yes, it can be used for very simple pivot charts etc., no doubt I will have the naysayers who rely on Excel tell me different.  So be it!  Be sensible about your business and think about your data strategy and not sweating your poor analyst for insights that will give you insights that are days or months old!