What happens when you use spreadsheets for KPIs and dashboards?
Do you know about the risks? What are the alternatives?
Author Alex Orrantia
- KPI, Dashboard, Business Analytics
What are KPIs?
KPIs are more than just numbers you report out on a specific period - they enable you to understand the performance and overall health of your business so that you can make decisions in your execution in order to achieve your goals, think of them as the pieces of data your car shows you on the dashboard in front of you.
KPIs are one of the main things that your business relies on as it scales, some examples of this are “sales this quarter, jobs delivered, billable hours, units sold”, these metrics may be by person, by team, the whole company, etc.
The set of KPIs you use will depend on what you want to track. They will help you estimate where your business is going, how is it performing, are there any areas that need immediate attention?
Who is this article for?
Maybe you have been in business for a number of years, your business model is proven, as a CEO of your you may be at the stage where instead of being so much on top of putting out fires and at the line of fire with customers and operations you start focusing more on business strategy, marketing to drum up sales, focusing on hiring the right people to continue growing.
You may be aware of what these KPIs are and you know what each one of these numbers mean to your business. So if you are using tools like spreadsheets to track these KPIs you may be putting your business at risk. The rest of this article tells you why and what to do about it.
Data accuracy in KPIs
Data accuracy is of utmost importance, because, in order for you to make the right business decisions your data needs to be reliable. There’s nothing worse than relying on a broken car’s dashboard, taking a trip, only to find out in the middle of nowhere you did not have enough gas in the tank.
In the beginning you could be manually pulling all of these business data that multiple apps, people or third party services hand out to you periodically. You pour all of this data and crunch some numbers to get an insight into where your business stands. This is fine in the early beginning, but one critical aspect about these data points is “how often do they get updated?” and “how often do they get synchronized to your master spreadsheet?
As your business grows you may appoint a team to build this dashboard using a spreadsheet, but the problem comes when they skip a few days of updating, enter the wrong data, or someone else starts updating it, and suddenly it's now about training someone new which creates a new burden on your team.
A related aspect to this is human error on data entry.
Data on time / automation
KPIs are as good as their refresh rate, and what I mean by that is when making business decisions you know it's best to take decisions on fresh data. One good example is sales. If you have arrived at a conclusion that a healthy number of sales per sales executive is 30 sales per month, when you take a glance at that dashboard you want to make sure the information on the screen is up to date and you are not looking at last month’s data before taking any decisions. KPIs must reflect reality at any given point in time.
Removing the human element from updating your KPIs as much as you can is where the magic is.
This takes me to the topic of automation. In order for you to have your full attention on keeping your car on the road, stop at a red light, decide for one route less traffic congested over the other you want to only wear the driver’s hat, take decisions on what the car’s dashboard and mirrors tell you and not attempt to be a mechanic.
Information sources for KPIs
With an automated platform you can pull all the information that your third party apps provide – your billing software, in house databases, crm, project management suite, calendars – examples are google sheets, trello, google drive, stripe, salesforce, quickbooks, a local database (i.e. basically any app with an open API)
Calculate once – enjoy forever
With spreadsheets you are at a greater risk of miscalculations, in fact there are studies that show that as high as 88% of spreadsheets out there contain errors. When writing checks this could be a tremendous failure ( imagine extra zeros in a cell) When you have software making the calculations your risk is much lower, because those formulas are well thought of once and then deployed.
Business intelligence from dashboards comes from not just pulling data from multiple sources, but by crossing all of that information and finding new insight.
A huge benefit of dashboards is that you can apply filters, search, options, date ranges to the data and arrive at new insights without the risk of breaking formulas, shifting cells, etc. – this is not readily available with spreadsheets.
What are the options when building dashboards?
There are multiple options out in the market that will help you getting started with creating dashboards, they come in many shapes and sizes, here is a short list of available options:
- Microsoft Power Bi
- Google Data Studio
What are the more advanced options?
We can help you setup any of the solutions above for a very solid starting point. When it comes down to a more comprehensive set of features that is custom tailored to the needs of your organization we can migrate any of the options above, or start on a clean slate with a custom built app that encompasses a dashboard and includes options only available when building from scratch.