Time and Materials Project Invoicing
Creating eCMS Tableau reports to help improve efficiency and effectiveness of preparing and validating preliminary invoices
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.
Introduction
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. The use of T&M contracts occur when it is difficult to accurately estimate project costs, or if the scope is undefined. Due to the nature of these contracts, the owner takes on the charges. As a result, seeing the transactional details of bills is very important.
Our client is a construction company with extensive experience in building complex projects and projects of all sizes. Consequently, they require extensive T&M reports with detailed billing information to track costs and provide visibility to their project owners.This client uses eCMS, a popular construction ERP software created by Computer Guidance Corporation and used throughout North America. This software, can generate preliminary T&M invoices for clients to review.
The preliminary invoice is a record of all the transactions for the job as of the previous invoice. It may require some cross-referencing to find all details of transactions. 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. This a burden for Project Accountants to compile all the required details for the invoice. Moreover, it creates administrative difficulties for Project Managers that review the invoice, and for owners that approve and pay the invoice.
Client Profile
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 $50 million to $300 million. Their projects are also diverse – from healthcare to education, advanced technology to hospitality, mixed-use/office to sports and recreation.
Our client has headquarters 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
Challenges
- There needs to be strong knowledge of the eCMS and the transaction flow.
- Preliminary invoices from eCMS are black and white text-based reports that list out pages of transactions. Consequently, it requires cross-referencing of various reports to find the details of the transactional item. In addition, the invoice report and its detail is only available in eCMS prior to the invoice being issued.
- Six data sources need to be brought together to minimize manual cross referencing in Tableau:
- 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).
- Invoicing is a time-sensitive process. Therefore, we require frequent cost and material expenditure updates charged to the project.
Challenges
- 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. Unfortunately, we are unable to provide real-time results because of limitations of the ODBC data connection. However, we set up refreshes in the data connections for every 15 minutes to ensure that data presented in the Tableau reports were as up to date as possible.
Deployment of the dashboards are to the client’s internal Tableau Server. Project accountants and project managers are the main end-users of these dashboards. Moreover, Tableau generates PDF exports and emails them project owners. These reports give a succinct T&M Invoice that significantly reduces page count and are much easier to interpret.
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
(Image values are randomly generated)
