Showing data to business stakeholders in long-winded tables and with lots of numbers is a no-go. You will not find many finance professionals that have not yet understood that point. Similarly, most finance professionals know that we must develop insights through analytics to get a seat at the table. However, doing this does not come without challenges…
That is because we are rarely able to take data straight from the ERP system and throw into the visualization tool.
Most often we must extract the data to Excel or another tool to arrange it neatly and analyze it to understand what insights can be drawn from it.
These manual interventions bring about several challenges.
– They increase the risk of making errors.
– They make it difficult to track data back to the source.
– They complicate having a qualified dialogue about what happened and why it happened.
– They slow down the data-to-insights process that makes executives wait too long for their numbers.
In this article, we will look at ways to tackle each of these challenges. We will also point to a desired future state when it comes to using the data created in the ERP or other operational systems.
PROPER DATA AND MODEL GOVERNANCE IS WHAT YOU NEED
Ideally, all data is correct and usable from the source but even in that case it is rarely ready to be shown right away. That is why we must process it through other tools. We do that to develop insights through analytics and to visualize it in an appealing and easy to understand way. However, as described, it does not come without challenges. Let us look at how to tackle each of them.
The increase of risk of errors
The challenge in most cases is that data is extracted in free form to Excel allowing the user to make any modification (s)he desires. With every additional modification the risk increases. Hence, to prevent this a data model should be built to ensure data is always processed in the same way. The model should be documented and agreed with stakeholders. In case true ad hoc analysis is needed this should be clearly stated. This ensures alignment with stakeholders that the risk of errors is higher.
The difficulty to track data to the source
For every single number you show to your stakeholders you must be able to state what the source is. In addition, you should be able to show what processing has been done to it to arrive at its current form. If you are looking at inventory numbers for instance, you should be able to go to the warehouse and point at the item in stock. One way to ensure you can do this is to create a simple flow- or process chart that shows the flow of data from sources to uses.
The complications of having a dialogue about the numbers
It must be transparent to all stakeholders where numbers come from and what processing has been done to them. The easiest way to do this is by having an audit trail from aggregated numbers to the source. If all you have is flat data in Excel (or even PDF) you cannot do this. In this case each sheet must be documented with a link where clicking on it will lead the user to the source data. Doing this makes your dialogue with stakeholders about what and why something happened straightforward.
The slow data-to-insights process
Your stakeholders want their numbers as soon as possible to be relevant for decision-making. However, every process step you take to prepare the data will slow down the process. The best way to go around this is to build automatic interfaces between systems. If all you have is Excel this is clearly a challenge although not impossible if you build macros that can replicate your manual steps.
Regardless of how you choose to solve these challenges it is crucial that you are using a proper data model and adhere to a strict governance around using it. This might feel limiting to the flexibility on how you can use the data. However, as highlighted if you clearly distinguish between standard and ad hoc analysis you can at least set the right expectations with your stakeholders.
Is excel the right tool for you?
To truly enjoy the upside of doing proper analytics and data visualization you must re-evaluate if you are using the right tools. As you can see above using standard functions in Excel will in most cases come with many challenges.
With Power Pivot and Power Query you can do many types of analysis to better connect to and visualize data from other sources. However, the ability to slice and dice and double click on numbers to better understand the details is more sophisticated in BI systems.
With Power BI or similar tools, you can now better visualize the numbers and the same tools can be connected directly to the source as well.
(In Konsolidator® customers have access to Konsolidator Konnect where they can connect their numbers directly to Excel or BI systems. This enables them to use the different systems for what they are good for – making documentation and working papers in Excel and present and analyze in a BI system. And they can easily track their numbers down to the original source).
Hence, now is probably a good time to consider what tool(s) could enhance your capabilities in the areas of data visualization and analytics. Same time these tools would likely help you tackle the challenges highlighted in this article. Go explore what is possible and escape your current perils of trying to do too many calculations in Excel and manual interventions. Find tools that allow you to focus on the important things such as discussing how to improve business performance.
What are you waiting for?
Author:
Martin Birch, Customer Success Manager at Konsolidator
