• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Onware

Collaborative Content Administration Software

  • About
    • About Onware
    • Careers
  • Business Solutions
    • Tableau
    • Microsoft Power BI
    • Custom Software Development
    • Download Brochure
  • Contract Administration
    • Features
    • Integrations
    • Compare Us
    • User Guide
  • Case Studies
    • Case Studies
    • News and Updates
  • Contact
  • Login

Consolidated Income Statement

Using Tableau to create a combined income statement from multiple business units operating in both US and Canada

All, Case Studies, Onware Business Solution Case Studies
Financials, Tableau

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)

  • Gross Profit Margin
  • Revenue and Gross Margin Summary
  • Financial Income Statement Branch
  • Financial Income Statement
  • financial income statement
  • Account Detail Financial Statement
Previous Post: « Clareview Recreation Center and Clareview Library

Onware proves to be an invaluable tool in Contract Administration Management

Next Post: Time and Materials Project Invoicing

Creating a series of Time and Materials (T&M) project reports to help improve efficiency and effectiveness of preparing and validating preliminary invoices

»

Primary Sidebar

Onware Edmonton Case Studies

Case Studies

• Onware Business Solutions

• Collaborative Contract Administration Software

Collaborative Contract Administration Software Clients

Sector

• Commercial

• Education

• Healthcare

• Tourism, Culture, & Sport

• Transportation

Edmonton Constract Administration Case Study Clients

Clients

• City of Edmonton

• Clark Builders

• DIALOG

• START (formerly HFKS)

• IBI Group

• Kasian

Edmonton Contract News and Updates

News & Updates

See What Onware Can Do For You
Contact Us Today for a Quote or to Schedule a Demo →

Contract Administration | Custom Software | Microsoft Power BI | Tableau

Support

+1 (780) 488-9273 | Toll Free : +1 (855) 781-9273
Suite 201 3230 Parsons Road NW Edmonton, Alberta, Canada
T6N 1M2

Onware © 2021 · Call Us Toll Free: 1 (855) 781-9273 · Privacy · Website by Plaid