Finance & Economics

Delivering a Cohesive Financial Dashboard

Financial dashboards can be tricky.

Most analysts have the information they need but the problem is there’s no uniformity in the sources. You might have data that’s formatted differently or from some other software. And all these disparate sources need to be merged together somehow to create a final report. Let me give you some examples from my own experience:

So, there’s a CAPEX chief financial analyst for Company XYZ and he has the responsibility to generate the CAPEX reports every month, which takes about a week to gather and QC the data before even starting to build the report. Now, there’s another financial analyst in this same office who will occasionally build the reports, but that means the structure of the information isn’t always consistent. Some columns are pulled from SAP while other areas are created in Excel. Then, there could be several iterations of the report because invoices for previous months may require adjustments. The chief analyst wishes there was a quicker way to include adjustments and show historical monthly data for comparison in his reports.

And then there’s the OPEX financial analyst for Company XYZ whose responsibility is to generate the OPEX reports each month. This whole process takes about 1-2 weeks, depending on when the data becomes available. There are two separate SAP codes used to pull the OPEX data because charges can be against cost elements and cost centers. This data is then merged in Excel and then used in the monthly report. This analyst wishes there was a way to build code in SAP to pull the OPEX data as one set since all columns are the same.

Finally, there’s a finance manager for Company XYZ who reviews the CAPEX and OPEX reports monthly. The manager thinks the information is useful, but the report formats are not always consistent so it takes some time to digest what is presented. The manager would like to be able to compare the CAPEX and OPEX expenses on a monthly basis and see how actual costs compare to the field cost estimates.
Usually, the first step in designing a solution for this type of challenge is identifying the problems in the processes.

For both financial analysts, mentioned above,
  • separate roles result in separate tables of CAPEX and OPEX data that require a lot of time to curate and format prior to building their monthly reports
  • cost adjustments are not easily accounted for
  • historical sums are not easily adjusted and reported
For the finance manager,
  • report formats are different and take time to understand content
  • although useful data is presented separately, more value would come from also being able to analyze the overview of CAPEX vs OPEX
  • there is no way to evaluate actual cost data against estimates

For this team, the solution was to build a cohesive financial dashboard that would integrate their CAPEX and OPEX data for reporting. The dashboard would have standard visualizations that could quickly provide answers for what the finance manager was trying to understand, even allowing a look at the previous months’ performance. Some of the requirements to consider included:

  1. Ability to view CAPEX and OPEX separately on a monthly basis
  2. Ability to view CAPEX and OPEX together
  3. Ability to integrate Field Cost Estimates from a different data source to compare to CAPEX
  4. Ability to quickly update data to current month reporting, including adjustments for previous months

After evaluation of the client’s available data, I quickly realized there was a need to manipulate columns and merge tables to prepare the data in a proper format for cohesive reporting. Using TIBCO’s Spotfire was the best application choice for this solution because of its powerful engine for processing large amounts of data quickly, ease of data integration and column matching across data sources, and user-friendly data updating capability.

To address the requirements, I used many of Spotfire’s out-of-the-box features:

  • Filtering schemes were used to isolate analysis per tab, allowing separately configured visualizations for CAPEX and OPEX.
  • Add Rows & Columns feature was an easy way to merge the data tables and still identify the source of each data row.
  • Data Column Matching was an excellent tool for visualizing Field Cost Estimate vs. CAPEX data on the same chart without having to merge the data from separate sources
  • Since the central data source was not ready to be linked to in Spotfire, I designed the solution to have a template style that allows each financial analyst to easily replace their data table in Spotfire each month as part of the process for generating their reports. As the tables are replaced, adjustments in the CAPEX and OPEX data and the most recent month’s data are captured and applied to all visualizations and calculations throughout the dashboard.
  • The linked-to-source feature in Spotfire also allows Field Cost Estimates to be up-to-date each time the dashboard is opened for review.

Now, the process for updating the data is much faster for the analysts and the finance manager can open one dashboard to review reports.

If having disparate data sources is a problem you are familiar with, try using Spotfire like I did. Start by evaluating the problem, identifying the data columns and data tables that need to be merged, and use Spotfire’s out-of-the-box features. A simple template demonstrating how to work with multiple data sources in the same visualization can be found here.

Leave a Reply

Your email address will not be published. Required fields are marked *