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
Figure 1. Job Cost and Labour Summary Report (numbers are fictitious).
Time and Materials (T&M) contracts are an arrangement for construction or another type of development in which the employer agrees to pay the contractor based on the time spent by the contractor and subcontractor’s employees to perform work, and for materials, used in the construction. It is generally used for projects in which it is impossible to get an accurate cost estimate of the project, or if the scope is undefined and changing often. Because of the nature of these contracts, all of the charges are passed onto the owner, it means that the owner wants to see a detailed record of the billings.
Our client, a Colorado construction company with extensive experience in building complex projects, is involved with 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 will generate a T&M detailed preliminary invoice for our client to review and then send to their client along with their monthly invoices.
The preliminary invoice is a record of all the transactions for the job for the period of time since the previous invoice. In order to provide all of the required detail for the transactions, it must be cross-referenced with different sources of data. For example, a job report may show only rental invoice number and invoice amount, therefore, to find information about the rental, one would need to find the corresponding rental invoice based on the number. Consequently, 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 the Project Managers to review, and finally the owner to approve and pay the invoice.
With Tableau, we were able to create summary 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. Not only could this be automated to generate reports on an ongoing basis, but also automatic data refresh meant bringing 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
Challenges and Objectives
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.
Figure 2. 200-300 black and white pages of data
Create a series of 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
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 were able to map 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 our client's internal Tableau Server and were consumed 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.
By significantly reducing the number of pages and time to produce the preliminary invoice and decreasing manual cross-referencing, it resulted it 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.
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.