Every organization, almost every person working, has used Excel at one point in time. For many, what started in Excel as a simple collection tool was, in fact, the first step towards computed data analysis. Further, self-learning while using Excel was easy due to number of online training materials available.
Eventually, graduating from a novice user, you started using formulas, creating charts and analyzing your data. And, there is where a problem begins emerging; you decided to master the art of Excel because it is interesting and easy to learn. Copy pasting a formula from a forum didn’t seem that difficult and you implemented those for your business.
So, where’s the problem here? It lies in the belief that the formulas and charts you create are correct, when you believe the numbers derived and then use them to devise strategy for your organization. In fact, JP Morgan Chase had to create a 129-page report explaining how a copy and paste error, used across multiple dashboards in Excel, resulted in a $6B loss.
I don’t deny that you cannot learn through online materials. Many do extremely well through this method, but what about those who are unaware they don’t. Did you ever get those formulas evaluated by someone? Did you ever get an outside opinion on using the right visualizations for your data? Did you think of hiring a business analyst to manage data and reports for your organization?
Even if your answer to most of these questions is yes, I have seen executives taking matters in their own hand simply because they think and believe what they are doing is right.
Recently while working with an organization, I was consulting on converting Excel reports to a robust BI Tool, ClicData. From scoping to implementation, I came across several scenarios which made me realize how easy it is for other companies to make simple mistakes. In this article, we’ll explore the top 3 scenarios in Excel to be aware of to ensure you aren’t making mistakes.
1. Collecting Data
Executive summary reports contain data from multiple departments and members. As was the case for this client, the Excel report handed over to me was a sheet with data collected from multiple users and system. My first question was, “How are you collating this information into a single sheet; is it automated and extracted directly from the source?”
Not surprisingly, the answer was, “No. We have multiple sheets with data delivered from different departments and units. We have one person assigned to copy data from multiple sheets into this Excel file, and then this file is provided to our VP for analysis.”
There is an issue here that needs to be addressed, even if the person responsible to enter the data from multiple sheets to a single sheet is accurate and precise 99.99% of time. What about that 0.01 % time when that person copies wrong information?
Why introduce steps for a database that exists and can be used directly?
2. Using Formulas in Excel Is Great, Are You Sure They Are Right?
After changing the way source data was populated, I started working on generating appropriate visualizations from the data gathered. I created financial summary charts in ClicData and passed them over to the client for evaluation. The next day, I receive a call from the client who was worried. She said, “Shree, your numbers in ClicData are wrong. We should be at a positive margin this month and your dashboards has a negative margin, which is not correct.”
I was concerned. How could I make a simple mistake in calculating Gross Margin? I started debugging the issue and couldn’t find anything wrong with the formula I was using. I asked the client what the right number was and why he thought he had the correct gross margin?
The answer was as expected. We checked our Excel spreadsheet, and the margin should be positive, whereas your margin is negative
After looking at the Excel, here’s a simplified version on how their data was setup:
[table id=6 /]
What’s wrong with the above calculation?
Sub-Total Revenue 1 was included in calculating Total Revenue because the client used the formula SUM (Row 2 : Row 6). This inflated their revenue numbers, and hence, showed a positive margin instead of negative.
A simple mistake can easily be made using SUM and multiple rows without realizing you could be including your subtotals in the calculations again.
3. Simple Fundamentals: Are You Using the Right Logic?
Like the above case, the client questioned me on not having the right numbers for average revenue generated per service hour. I looked at how the client was calculating their average number. Below is a description on how the approaches varied:
[table id=7 /]
How would you calculate overall average for 4 months?
What the client was doing:
(20+15+25+20)/4 = $20
And what I was doing:
(2000+3000+2500+4000)/(100+200+100+200) = 11500/600 = $19.16
In this case, a simple mistake was made on understanding how averages should be calculated for a single month, versus a group or collection of numbers put together.
Even if you are an expert in Excel and it’s not your domain expertise, I would recommend getting your formulas verified by an expert. Do not introduce unnecessary steps in a process when not required. Finally, move to a BI system as soon as possible. Excel is great tool to get started with, but only a BI system will allow to bring your sources together, handover the report building function to a specialist and help you be self-sufficient for analysis.
Give ClicData a try with your own data
Connect all your data, blend it together and publish interactive, fully automated dashboards to your team or clients.
All of it by EOD.
