Over a million developers have joined DZone.

Tips and Tricks for Using BI to Explore Financial Data

Learn some tips for using BI tools for data exploration on financial data and visualizing this data.

· Big Data Zone

Read this eGuide to discover the fundamental differences between iPaaS and dPaaS and how the innovative approach of dPaaS gets to the heart of today’s most pressing integration problems, brought to you in partnership with Liaison.

One of the main advantages modern business intelligence brings to the table is the ability to perform exploratory data analysis – i.e., testing new hypothesis leading to further data collection and analyses, beyond merely examining the present state of affairs. How does this apply to the realm of financial data analytics?

From Spreadsheets to Data Exploration

Spreadsheets have become almost synonymous with financial reporting, and can be found in many finance departments and services across the world for as the go-to tool for practically all reporting – financial statements such as balance sheets, income statements, and cash flow statements are all prone to arrive in lengthy Excel spreadsheets.

While spreadsheets can offer a reasonable way to summarize large amounts of information, they are much less useful when it comes to exploratory analysis. The purpose of a spreadsheet is to condense a large amount of data into a summarized format. However, a spreadsheet usually lacks analysis of trends, and usually has no method of highlighting issue. A balance sheet is written to satisfy accounting and tax requirements, not for data exploration.

In contrast, the purpose of visual analytics is to add trending analysis and contextual clues to a large set of data. Once the data is visualized, it can be used to display trends, make judgments, and implement action plans much quicker. Let’s look at some ways to get started with this type of analysis and reporting:

Understand Lead-to-Cash by Finding Connected Data

If the company has never mapped its “lead to cash” process, now is a great time to do so. The “lead to cash” process covers all steps involved with all departments of the company to convert a marketing lead into a paying customer. This process should be determined to ensure the smoothest transitions between marketing, sales, operations, and finance. By understanding this process, financial analysts may discover some trends from other departments affecting finance.

One exploratory report might reveal increasing service tickets are a leading indicator of cash flow issues. If customers are receiving poor quality goods and services, they may stop paying their outstanding invoices, or cease being a customer at all. In financial terms, this means lower revenues, lower cash flow, increased Accounts Receivable (AR) balances, increased Days Sales Outstanding (DSO), and increased calls for collections. By considering the connection between finance and the other departments within the company, financial analysts are able to highlight issues more effectively, and enable management to be more proactive in solving these issues.

Focus on Visual Management

Visual management is a concept meant to improve communication, highlight issues, and set expectations in a working environment. A consistent report across all levels of the company, from front-line technicians to executive management, clearly communicates current performance and future goals. Although this concept was born in manufacturing engineering, visual management has many applications within finance departments. We’ll discuss a few best practices in visual management as they apply to creating exploratory financial dashboards and reports.

Create a Scoreboard for Employee Tasks

Creating a “scoreboard” of employee tasks within your finance BI tool is a great first step in visual management. A scoreboard communicates to workers they stand in relation to their goals, and communicates to management if a particular team is falling behind. In a finance department, the scoreboard might contain the following information:


  • AR billed vs. collected in current month as a bar graph. A difference in bar height might highlight customer management issues, invoicing issues, or payment processing issues.
  • AP received vs. paid in current month as a bar graph. A difference in bar height might highlight vendor management issues, or a cash flow disruption.
  • Customer service ticket activity vs. AR collections as a multiple line graph. An increase in customer service tickets might be a leading indicator of future collections or churn issues, creating a negative impact on revenues and cash flow.


  • Revenue vs. budget as a bar graph. Rather than using a spreadsheet column to display difference, bar heights quickly highlight whether revenues are meeting expectations.
  • Net profit by month as a line graph. Add a color gradient to the line, displaying green for positive values, and red for negative values.
  • Marketing expenses vs. Sales activity as a multiple line graph. If the marketing campaigns are successful, an increase in marketing expenses should be a leading indicator of future increased sales.

Highlight Issues

Highlighting issues is a significant advantage of using visual management. Once issues are highlighted, technicians and their direct supervisors can immediately begin to investigate root causes and take corrective actions. Rather than having a mindset of “out of sight, out of mind,” empowering technicians with information and decision-making power can enable employees to be more proactive and more satisfied.

Within the finance department, highlighting issues might look like:

  • Outstanding AR balances by customer as a bubble graph. The largest bubbles quickly draw attention to the largest collection items. Differences in bubble size also helps to put each customer in context of all customers with outstanding AR balances. Adding a temperature gradient to the bubbles might also highlight issues further. With this information, an AR clerk can prioritize their collections activity on the largest problems.
  • Percentage of timesheet submissions as a pie chart. Without disciplined monitoring of timesheet submissions, finance won’t have all the information required to complete payroll correctly or on time. Employees might receive late or incomplete paychecks as a result. If frequent issues with timesheet submission arise, finance can take corrective action to send email reminders.
  • Revenue by customer vs. profit by customer as a scatter plot. Trends in customer size and profit margin will quickly appear. If profits are linear or exponential, the business is managing its ability to scale in a healthy manner. If profits are scattered, however, this might highlight inconsistent operations processes with each customer. Financial management can work with operations to gain more information, and improve processes to create more consistent results.

Visualization Tips: Use Contextual Clues to Maximize Visual Impact

Since, as we have mentioned, exploratory analysis is facilitated by data visualization, it’s important to build an effective one. Here are some pointers to creating effective dashboards for finance professionals:

Color – Use color to denote different categories in the data. A legend on the right side of the graph should be added to describe each color’s meaning. Color can also add contextual clues, such as using conditional formatting to communicate judgment (green is good, red is bad), or color gradient to help represent the range of judgment (most items are some shade of green, only a select few are dark red). This type of context is used frequently in income statements, with black representing positive values, and red representing negative values.

Shape – The shape of the data point can also denote different categories in the data. The two can be combined to clearly delineate different data series and their meanings. Shapes of data points are most commonly used in line graphs and bubble graphs. A revenue by month by product graph, with each product as a different shape, is an excellent use of shape context.

Area – The size of the area of a data point should be relative to its importance. Using a variable area size on data points will help visually highlight the most important data, and draw attention away from unimportant data. Bubble graphs and treemap graphs are excellent uses of area as context.

Map – If the data has a geographic element, such as country, state / province, city, or zip code, place the data on a geographic map to best represent the data. Combine the map with color to add more contextual data clues.

These tips and tricks are really just the tip of the iceberg for the types of insights you can reach when using business intelligence to prepare, analyze and visualize financial data. To learn more, check out 4 Building Blocks of Great CFO Dashboards or this free analyst report on "How Financial Services can Tap Into Insights with Self-Service BI."

Discover the unprecedented possibilities and challenges, created by today’s fast paced data climate and why your current integration solution is not enough, brought to you in partnership with Liaison

excel,bi,visualization,big data

Published at DZone with permission of Eran Levy, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}