Consolidated Income Statement
How we helped created a combined income statement from multiple business units within an organization operating companies in both US and Canada
Figure 1. Consolidated Income Statement in Tableau
Our client, a construction engineering and plant maintenance service provider, has companies in Canada and across the US. In the past, they were required to export their trial balance every month for each company into Microsoft Excel and manually map the accounts and costs to create an Income Statement. This process resulted in significant amounts of data manipulation and validation, which is not only time consuming, but also only provided a snapshot of data for a given time period.
Through Tableau, we helped our client automate the process, and also provided them with real-time interactive analytics and self-service for executives. In addition, we were able to provide filtered numbers for individual branches and their managers.
Challenges and Objectives
Client has seven operating companies - six in the US and one in Canada.
Each company has different account and are sub-divided into branches, with a total of 19 branches split amongst the companies
In the ERP database, the companies are set up as individual silos, each with its own chart of accounts.
There are foreign exchange considerations - one of the companies are located in Canada, and the executive wanted to see everything consistent in US dollars.
Month-to-Month, Year-over-Year, and budget comparisons were required as well, thus, we had to include multiple data sources.
The client requested the ability to navigate from the Income Statement and drill down to the transaction level. The row count for all the transactions were well over one million.
Prepare the high-level consolidated income statement.
Provide different levels of detail on the income statement, and allow filtering by branch/company.
Include month-to-month, year-over-year and budget comparisons.
How We Helped
Initially the requirement was to provide an income statement, but as we started working through things, the requirements started growing and getting more detailed.
Before, getting to the end result, we tried multiple solutions with Tableau. First, we used Excel for mapping the transactional tables; however, there were too many rows and Tableau’s blending of data was not working well. In addition, it took minutes to refresh the views when we applied filtering, as a result, it was not very usable.
In the second attempt, we tried to source the monthly transaction roll up tables provided by the ERP; however, this gave us account level summaries rather than transaction level detail. Blending was still used to get the previous year and budget data. Once again, it took too long to refresh the views - we aim to have refreshed take less than eight seconds.
Figure 2. We want views to refresh in less than 8 seconds
Our third attempt used a combination of a custom database query against the ERP to get the data we were looking for. This was combined with an Excel mapping of the chart of accounts to where they would appear on the Income Statement. Collaboration with our client was essential in this step to understand what was required from the data source. Previously, they had generated their budgets using Excel, but we worked with the ERP’s built-in budgeting so that both the budget data and the transactional data was in the same location. Our custom database query sourced data from: General Ledger Actual Amounts (roll up by each month), Prior Year General Ledger (again, roll up by each month), and the budget. In addition, we joined that with the Excel file containing the mapping of the account number to the income statement, and also one that contains historical foreign exchange rates – which helps convert CAD to USD.
Figure 3. Left joining multiple data sources to create our income statement in Tableau
In the end, we created a series of financial reports used for board reporting and forecasting.
Branch level reporting with data source level filters, thus, branches can only see their data
Drill down from high-level income statement to account-level summaries to individual transactions
Visualizations for key performance indicator (KPI) tracking that included additional data sources (i.e. time sheets, budget re-forecast)
Process improvements included:
Automated data import/preparation to Tableau Online via Tableau Bridge
Less Excel manipulation since the views are aligned with the desired end-result
Real-time results to Branch Managers instead of having to wait for a manual process to complete
Self-service for Executives allowing them to get the details, if required, without having to go to employees
Call us and speak to one of our data experts and we will answer any questions you may have. We want to help you unleash your data through visual analytics as well, we offer 'proof of concept' packages to connect your data to our set of starter dashboards.
Visit our website for more content including additional case studies, news and events, and tips and tricks to elevate your visual analytics.