Through Tableau, we helped Thorpe automate the financial income statement process, and also provided them with real-time interactive analytics and self-service for executives. With enhanced data governance, we can define access permissions and manage the data available for individual branches and their managers.
Thorpe, 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 a financial 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. Using Tableau, we automated and connected to real-time data to build an interactive and detailed financial income statement.
Client Profile
Thorpe is a leading multi-craft industrial service provider with engineering and manufacturing capabilities for non-metallic corrosion-resistant equipment and materials, insulation services, high temperature systems, and industrial flooring and resurfacing systems for a variety of industrial plants.
Thorpe’s strength is the combined capability of coordinating technical and engineering requirements, self-manufacturing many of their solutions, and completing installations with experienced field crews. The result is better communication, shorter projects duration, lower life cycle cost, and enhanced safety with improved reliability for maintenance, retrofit and capital projects. They are based in Houston, Texas.
Challenges and Objectives
Challenges
- Thorpe has seven operating companies – six in the US and one in Canada.
- Each company has a different account and are sub-divided into branches – 19 in total.
- 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.
- Thorpe 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.
Objectives
- Prepare the high-level consolidated income statement with comparisons
- 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 Create an Income Statement
Initially the requirement was to provide a financial 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 long to refresh the views.
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 Thorpe 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.
Results: An Interactive Income Statement in Real-Time
In the end, we created a series of financial reports used for board reporting and forecasting.
These included:
- Enhanced Data Governance: Branch level reporting with data source level filters, thus, branches can only see their data
- Interactive Dashboards and reporting: Drill down from high-level income statement to account-level summaries to individual transactions
- Visual Analytics: Visualizations for key performance indicator (KPI) tracking that included additional data sources (i.e. time sheets, budget re-forecast)
Process improvements included:
- Time saving and increased reporting efficiency: Automated data import/preparation to Tableau Online via Tableau Bridge
- Single Source of Truth straight from the data source: Less Excel manipulation since the views are aligned with the desired end-result
- Real-time analytics for proactive rather than reactive decision-making: Real-time results to Branch Managers instead of having to wait for a manual process to complete
- Self-Service Analytics and less reliance on others: Self-service for Executives allowing them to get the details, if required, without having to go to employees
Questions?
At Onware we have over extensive knowledge and experience building financial statements for both large and small organizations. Whether it be with architects, construction owners, general contractors, engineers, government entities or services providers, we can design a custom tailored solution that is built for your organization.
Call us and speak to one of our data experts and we will answer any questions you may have. Trust Onware to help you unleash your data through visual analytics; 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.
Consolidated Income Gallery
(all values are randomly generated)
