With Tableau, we created summary data reports that provided a breakdown of each transaction to a high degree of detail – saving both Project Accountants and Project Managers time, effort, and administrative burden. We automated the eCMS reporting process to generate Tableau reports on an ongoing basis and perform automatic data refreshes that would bring the latest information into the reports as soon as it was posted. Furthermore, we included a lookup of archived data, making it easier to locate historical transactions.
For construction or other types of development, Time and Materials (T&M) contracts are an arrangement where the employer pays the contractor based on the time spent by the contractor and subcontractor’s employees to perform work, and for materials used in the construction. T&M contracts are generally used for projects in which it is impossible to get an accurate cost estimate , or if the scope is undefined and changing often. Because of these contracts’ nature, all of the charges are passed onto the owner, who would then want to see a detailed record of the billings.
Our client is a construction company with extensive experience in building complex projects and projects of all sizes. As a result, they require the ability to produce extensive T&M reports with detailed billing information to track costs and provide visibility to their project owners.
Our client uses eCMS, a popular construction ERP software created by Computer Guidance Corporation and used throughout North America. Out of the box, the ERP system generates a T&M detailed preliminary invoice for review that then gets sent to clients with their monthly invoices. This preliminary invoice is a record of all the transactions for the job as of the previous invoice. In order to provide all of the required detail for the transactions, different sources of data must be cross-referenced. For example, a job report may show only rental invoice number and invoice amount; therefore, to find information about the rental, one would use the number to find the corresponding rental invoice. This resulted in reading through potentially hundreds of pages in order to fully understand the billing information. Not only is this a burden for Project Accountants to compile all the required details for the invoice, but also for Project Managers to review the invoice, and finally for the owner to approve and pay the invoice.
Our client is a construction company with unwavering commitment to customers and industry partners. They ensure safety and satisfaction is top-of-mind, and handle large, complex projects and small, community-focused efforts with expertise and passion.
Founded in 1967, our client builds projects of all sizes – from more than $300 million to less than $50,000 – and across different uses — from healthcare to education, advanced technology to hospitality, mixed-use/office to sports and recreation.
They are headquartered in Colorado in Colorado Springs but also operate in Denver and Vail. In addition, they have branches in Jackson, Wyoming and Oklahoma City, Oklahoma.
Challenges and Objectives
- As our client uses eCMS as their construction ERP, Onware used Tableau to connect directly to eCMS’ database, so an intimate knowledge of the data structure, as well as the transaction flow was required.
- Preliminary invoices from eCMS are black and white text-based reports that list out pages of transactions. In order to find the details of the transactional item, it is required to cross reference with other reports. In addition, the preliminary invoice report and its level of detail is only available in eCMS prior to the invoice being issued.
- To create the required reports to minimize manual-cross referencing, we needed to combine and map six data sources in our Tableau workbooks:
- T&M Payroll transactions (eCMS).
- T&M Job Cost Detail transactions (eCMS).
- Employee (HR) tables including job role assignments and hourly rate information (eCMS).
- Rental equipment list containing details on the equipment and its hourly rates (rental system database).
- Rental equipment invoices (rental system database).
- Credit Card Statements to get vendor and description details (CSV files provided by the card issuer).
- Because invoicing is a time-sensitive process, costs needed to be updated frequently as new time and material expenditures are charged to the project and adjustments were made prior to billing.
- Create a series of Time and Materials (T&M) reports for our client to simplify the process of compiling and validating preliminary invoices.
- Include additional information such as amounts, quantities, regular/overtime hours worked, days used, rates, and resource details to minimize manual cross-referencing for easier verification and accounting.
- Increase ease of validation for preliminary invoices for quicker turnover to produce final invoices.
- Incorporate ability to view and reference historical cost information for higher visibility and transparency.
How we Helped Create Time and Materials Reports
Using Tableau, we connected to our client’s eCMS database (via ODBC), rental system database (via SQL Server), and credit card statements which were provided via CSV files and added monthly. We mapped together employee and labour codes to details and rates, as well as equipment and material codes to details and rates. As a result, we created multiple summary reports including labour, equipment/materials, and other job costs. These listed out information for each expenditure, such as total amount, rates, hours worked, days utilized, and additional details. Due to limitations of the ODBC data connection, we were unable to provide real-time results, but data connections were set to update every 15 minutes to ensure that data presented in the Tableau reports were as up to date as possible.
The dashboards we built were deployed to the client’s internal Tableau Server and were used primarily by the Project Accountants. Additional Tableau licenses were provided to Project Managers to be able to view the reports as well. The project owners were sent PDF exports of the reports generated by Tableau. These reports gave a succinct T&M Invoice that consisted of tens rather than the hundreds of pages that were previously being pulled from eCMS.
Results: An Easy to Understand and Interactive Time and Materials Report
By significantly reducing the number of pages and time to produce the preliminary invoice and decreasing manual cross-referencing, eCMS Tableau dashboards resulted in better reporting for project owners and managers. Not only were there fewer pages to read through, but also, more details on individual charges.
With Tableau, we automated the process to compile the T&M reports with the necessary transactional details. As a result, it took considerably less time and effort to regenerate new reports when needed. In the past, project accountants were required to manually refresh and recompile the data from multiple sources and export the information into a report to view the latest costs. However, using Tableau Server we were able to provide a single location for all the cost data, and Project Accountants were ensured the reports contained the latest cost information.
Finally, we implemented the ability to quickly lookup historical transactions by line or invoice. This improved clarity and transparency by allowing the project manager to see past project data for comparisons or reference.
Questions About BI Software, Data Analytics, or Data Visualizations?
At Onware, we have over extensive knowledge and experience building Time and Materials (T&M) Tableau reports 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.
Enjoy this post?
Visit our website for more content including additional case studies, news and events, and tips and tricks to elevate your visual analytics. Follow our social media for updates in software, BI, and data.
Time and Materials (T&M) Gallery
(all values are randomly generated)