Cost reconciliation in construction projects

Introduction

Construction projects involve millions (and sometimes billions) in spending on expenses that range from people to material and equipment. While reconciling costs is a common-place activity, we find that many finance, accounting and commercial teams in construction projects lose precious time every month trying to complete their reconciliation activities.

This is not unique to any construction project and as we’ll see that the issues are the same everywhere. Project teams have been reconciling expenses in the same way for decades, making it difficult to even imagine a better way of doing things!

The good news is that there are better ways of reconciling expenses in projects and in this post we aim to guide you through it.

What is cost/expense reconciliation?

Cost Reconciliation Workflow-3

Cost reconciliation is the process of checking records to ensure that the project’s expenses match ongoing activity in the project. Usually, the records are checked using three-way matching which is a payment verification technique to ensure that the contractor's costs are valid. This technique entails comparing the submitted cost against two other pieces of evidence. Depending on the cost, this can take the form of checking and comparing receipts, invoices, time cards and transaction history.

Expense Source Check 1 Check 2
Labour Payroll Timesheet 3rd party source (eg. Gate)
Material and Equipment Invoice Delivery Note Purchase Order and requisition
Subcontract Bill

Progress reports

Inspection Requests

 

If you were the only person managing the project, it would be relatively easy to have confidence in the spending. But in projects that is rarely the case, with many different items spent on behalf of the project. The reconciliation process allows organisations to have confidence that their spending was needed, approved and necessary evidence has been provided.

The specifics of reconciling costs vary from one item to another and there may be differences from one project or organisation to another. For instance, for labour costs, we’d be looking to compare the incurred cost against work performed, typically recorded in a timesheet, and against an independent third-party source that shows that the worker was physically present. The third-party source can be as basic as a casual timekeeper or in the form of gate data. Even more futuristically, wearables can be utilised. Regardless of the specifics, ultimately, each incurred cost is compared and checked against a form of evidence.

 

Challenges in reconciliation processes

Over the last year, we’ve worked with over 20 organisations across the supply chain that have to perform reconciliation continually. In reality, while the actual work involved in reconciliation should be relatively simple, it quickly becomes a nightmare for all those involved. Regardless of whether the organisation uses an ERP, accounting system, project management system or even an excel spreadsheet, the issues tend to be similar.

Number and variety of data sources

Cost reconciliation involves exchanging cost-related information between multiple parties on a construction project. Typically, there are different cost types (like rented equipment, purchased materials, trade labour, etc.) and multiple involved stakeholders (such as the owner, PMO, consultant, main contractors, subcontractors, etc.). Consequently,  one would expect the presence of numerous sources of information with multiple unique formats like:

  • Timecards - Site team
  • Payroll  - Accounting team
  • Gate Data - Clerk of works
  • Invoices
  • Delivery Notes
  • Approvals - Management

This means that the controller has to spend hours collecting the information from these different teams. The number and variety of data sources depend on the size of the project and the number of involved companies.

One of our clients is currently developing a mega venue with more than 50 active subcontractors performing different jobs on the site. The client has created three cost buckets that subcontractors report costs to. These cost buckets are:

  1. Labour
  2. Material, equipment and services
  3. Other

How would you estimate the number of different data sources related to cost reconciliation involved in this project?

 

By the nature of those three buckets, it is more likely that the subcontractor will provide different formats for each bucket. So, in a simple calculation, the number of different data sources in this project would be close to the number of contractors multiplied by the number of cost buckets which is 150.

 

In reality though, this number is much higher!

 

The reason is that this calculation assumes that the involved companies will provide the same uniform format for any cost type in those three cost buckets throughout the lifecycle of the project. Also, we have ignored the systems that the client has put in place to track and monitor the costs of the project.

 

Can only be reconciled by people (PDF)

The wide acceptance of the PDF format as a means to transfer cost-related information in the construction industry means that the cost data found in those files can only be reconciled by people. With the amount and variety of information involved in typical construction projects, it usually takes days for a whole team to reconcile the costs of a large-scale construction project from those PDF files. This amounts to a lot of extra costs which the owner/contractor has to spend in order to reconcile the costs being spent on the project/s.

For a contractor based in the US to reconcile their costs, they have to prepare an 800-page PDF file consisting of all the costs in a particular month. This is done by manually sorting through hundreds of hard copies of invoices and matching them with their corresponding purchase orders.

 

Not only does the main accountant get stressed towards the end of the month, but she also pauses or lags behind on most of her other tasks, just to prepare the needed documentation for cost reconciliation. Since she is an avid tech user, she has prepared some Excel spreadsheets that assist her. Those helped to decrease some of the manual work needed for documentation. Unfortunately, this still wasn’t enough to relieve the major part of the heavy work.

 

After that, she submits this 800-page PDF file to another team which in turn spends around a week sifting through the different pages and cross-referencing with other data sources on the construction to make sure that the submitted costs are reconciled.

 

Can you guess how much this contractor spends on this laborious work?

 

Remember, this is only one of the contractors we have dealt with. There are probably thousands who operate in a similar way.

 

How much is the total time and cost spent by the whole construction industry doing manual cost reconciliation?

 

Poor information quality/formatting

Not only is the availability of information crucial for cost reconciliation, but the quality of information is as important. Generally speaking, information quality could mean the availability of key information, similarity of the same information in different sources, traceability and uniqueness. Below are a few examples of how information can go wrong without proper information checking:

Item Example
Availability of key information

• The payroll file has missing Employee Numbers 

• The invoice has a missing invoice date

The similarity of information in different data sources

• Purchase order and invoice have different descriptions of the materials

• Equipment rental agreement and invoice have different names for equipment

Traceability

• Someone from your company has submitted a purchase order but you don’t know who or for what reason

Uniqueness

• Two invoices from the same vendor have the same invoice number but different items

• Two employees are reported in payroll with the same Employee Number

 

Let’s suppose you were reconciling the cost of material purchased at your construction site. On one of the invoices, the item description says the invoice is for some “reinforcement bars”. The invoice has no clear indication of the date those materials were delivered to the site, why they were ordered or where they were installed. Neither does it have any indication of the quantity and specifications of those materials. It just mentions that the material costs a certain amount of money. Moreover, there is no purchase order or delivery receipt attached with the invoice!

 

How do you evaluate the quality of the information in this invoice?

 

As a cost controller on-site, would you accept this cost?

 

Clearly, the answer is no.

 

This is one of many examples where a document that is supposed to be used for cost reconciliation does not meet the minimum requirements of information quality. There are many more examples like this: missing badge numbers in gate logs, missing rental rates for equipment on-site in rental agreements, etc.

The issue with quality is not just about missing data; it extends beyond that to include incorrect and misplaced data as well. For instance, the accountant might key in the wrong value for the number of hours the labour spent on site for this week. Or, someone might copy and paste the wrong information in Excel and print that invoice form and bill it to the client.

 

Poor feedback/visibility

We have discussed the challenges related to the number and variety of data sources involved in cost reconciliation, how cost reconciliation documents can only be reconciled by people,  and how poor information quality and format might be. Now, we have arrived at feedback and visibility, which are two byproducts or after-effects of cost reconciliation. Most of us have heard the phrase “garbage in, garbage out”. With the three previous challenges of cost reconciliation in mind, imagine how hard it can be to produce reliable feedback and visibility.

A subcontractor works for a general contractor on a large construction project. Every month, the general contractor requires the subcontractor to submit a payment application consisting of a series of invoices, delivery tickets and labour timesheets.

 

The leading cost controller who works for this subcontractor has told us that once they submit the monthly payment application, they are in the dark and have no clue about the acceptance of their application until they receive payment in their bank account. Early that morning, she rushes into her office to calculate the ratio between what she was paid and what she was billed for in the payment application. This gives her the acceptance percentage and inversely shows how much was rejected and withheld by the client.

 

This example portrays a subcontractor that has no visibility on their acceptance rate before their payment is received. So the contractor has to wait around 30 to 45 days (average pay time on this project) to get an idea about their payment application acceptance status. Not only is there an absence of visibility on the acceptance rate, but also absence of clarity on rejected invoices, which makes it nearly impossible to fight for the rest of the bill.

 

How many contractors around the world would benefit from lowering the payment time and having more visibility and quality feedback on the payment applications?

 

Prone to Error

As mentioned in the above sections, the PDF file has dominated the cost submittal landscape. Subsequently, this has put a lot of dependency on people when it comes to reconciling costs. We completely agree that some tasks are better performed by people than by machines. Yet, most numerical calculations and comparisons are much more effectively processed by a machine than by a person, especially considering the magnitude and breadth of information in cost reconciliation. That being said, in the current status quo, there are a lot of pitfalls for humans while reconciling costs in a construction project. Even with the help of Excel, which has a huge range of formulas, one minor error can have a domino effect on thousands of values in cells.

We’re working with a subcontractor to extract information from their labour weekly reports, which bill the number of hours and costs for each employee on-site, into a standardized table. The subcontractor has not set up a proper accounting system. Thus, all cost reports are prepared in Excel.

 

Due to copying and pasting some of the cells in the Excel sheet, some of the employees are not updated with their correct Employee Number. This causes the same employee number to be reported multiple times but with different names.

 

This issue, if not detected by Morta, could have been missed by the cost controller and would have most probably been rejected down the line.

 

How can databases help in cost reconciliation?

Bring your cost reconciliation data to a single hub

Databases allow you to store information from any existing systems on your construction project. For the tech geeks here, this can be done either via API or via converters which extract data from CSV/Excel files exported from your systems.

Morta is currently used to connect several systems together on a large-scale construction site in the US. It imports the gate logs from a gating system which is installed on-site in several locations. Morta pulls the data from the gating system every 15 minutes and stores it in a secure location making sure that the data isn’t duplicated while importing.

 

Also, Morta imports site reports for labour, materials and equipment found on a second system. This is automatic, without any user intervention every 30 minutes. So, the data from these two systems is kept almost up to date.

 

Moreover, Morta is used to import all the deliveries and inventory found on-site from a third system into three different tables which are also securely stored. Those records are imported daily.

 

Morta indirectly connects to tens of other systems. In practice, this means that rather than having access to the original system,  we received updates via Excel/CSV files and updated the tables on Morta accordingly. Due to the nature of this workflow, the owner has requested it to be manual, whereby users manually upload the Excel file into Morta and then it takes care of the rest. However, fully automated data extractions via Excel/CSV could be set up as well.

 

In doing this the client has connected all necessary systems and achieved proper data organization, linking and also prepared for the next step

 

Validate data quality

Data quality is the most important factor when it comes to generating accurate cost reconciliation results. As demonstrated in previous sections of this blog post, having poor data quality and format quality often leads to inconclusive results and intensive hours of rework to correct and reconcile the costs.

However, using databases, since the tables provide a structured way to store the cost data, it is very intuitive and natural to move into validating data quality.

Payroll sheets are one of the most used kinds of cost documents in labour cost reconciliation. They typically display the hours worked by an employee throughout the day or a week along with the pay rate of that employee and the total cost.

 

On one of our projects, there are more than 10 subcontractors who submit Excel/CSV files containing thousands of labour hours and costs. In a perfect world, their data would always be correct and complete. But this is rarely the case.

 

For example, Morta checks for duplicates within the data and finds several instances of those duplicates in the payroll sheets. Morta also checks for missing key data like the employee name and the dates.

 

Can you imagine validating these items manually even with the help of Excel for different files with different formats EVERY SINGLE WEEK?

 

By the way, I have tried, it was horrible.

 

Fortunately, with the help of Morta, everything is done automatically. This greatly reduced the hours and costs spent by the clients and subcontractors in checking the payroll sheets data quality and preserves the data in proper quality making it ready for reconciliation any time as well as any future analysis.

 

 

Connect your data

Prior to reconciling the different cost components, it is necessary to connect those components and join them in one table. In labour cost reconciliation, for example, the reported hours for each employee are fetched from payroll, timesheet and gate logs and placed side by side. Instead of having to look at three different places, combining the information in one table gives the user direct visibility on which records report a higher number of hours in payroll than in timesheets and in gate logs.

Connecting data however is not always as simple as it may sound since more often than not, two systems might refer to the same employee in two different ways. For example, an employee might be referred to by her internal employee number in the subcontractor’s payroll system but by a badge number in the gate logs. Moreover, different systems might display the same information in different formats. For example, the vendor’s invoicing system might display dates in YYYY-MM-DD format, but the client’s accounting system might display dates in another format. So, to properly connect cost data together, a thorough analysis must be done on all the key data fields.

 

Automate your reconciliation

After making sure of data quality and properly connecting multiple data sources together, we are ready for the third and final step: Automation.

Automation can take many forms depending on the application which is required. In regards to cost reconciliation, the major part of automation is data comparisons and calculations and less about workflows. As mentioned in the Prone to Error section of this blog post, automated checks can do all the heavy lifting when it comes to comparing the labour hours to gate logs, or when comparing the invoice items to the ones on the purchase order, or comparing the current billed equipment costs to the rental rates. Again, for the geeks here, this can be done using any programming language which can connect to the database, such as Python or javascript.

Morta is reconciling costs related to labour on a construction project in the US. In this project, there are three sources of labour cost information: payroll, site timesheets and gate logs.

 

The rules involved in this reconciliation are listed below:

 

Rule 1: If payroll hours > gate hours - 15 minutes a day (tolerance set by the client on-site), the billed hours are not accepted by the system and marked as an exception

 

Rule 2: If payroll hours > timesheet hours, the billed hours are not accepted by the system and marked as an exception

 

Rule 3: If the hours have already been reported previously, the hours are also not accepted and they are marked as duplicates.

If all the rules are satisfied, the record is automatically accepted by the system.

 

 

Secure collaboration platform

The Morta platform is able to engage all involved parties in one single hub. For example, a subcontractor might be adding some evidence for missing material costs details on one side while a package manager is adding her feedback on that same record from another side. That being said, you might be worried that the subcontractor can change the package manager’s feedback. However, Morta can be configured with a role/position/company/contract-based security that can either allow or forbid a user from seeing or changing certain parts of a table.

Labour Cost Submittal & Reconciliation in Morta

Labour Cost Approval in Morta

Benefits of Implementing Cost Reconciliation on Morta

As we have discussed in this blog post, there are various challenges in cost reconciliation on construction projects:

  1. There are numerous and various data sources most of which are PDF files
  2. These files require people to compare the values manually between different files
  3. This usually causes data quality issues
  4. Due to the data quality issues, the results of the reconciliation may be flawed

After implementing cost reconciliation on Morta for more than 15 contractors, it was evident that data quality and visibility on cost information has greatly improved and that cost controllers spent less time preparing and analyzing their cost information. Below is a chart that estimates how much of each cost type was automated:

Benefits using Morta

We noticed that trade labour, management labour and services had the highest percentage of automation which was about 80% to 85% whereas equipment and materials required more human intervention than the others. This was not a surprising finding since the material and equipment cost information was mostly paper-based and relied on descriptions instead of proper coding of materials and equipment. However, if the material and equipment information had been set up with proper coding, we expect that we can automate 80% to 90% of material and equipment cost automatically.

Next Steps

As shown in some of the above examples, Morta provides a platform to bring in your cost information, validate that information, connect the different data sources together and automate the reconciliation process by following a set of rules, all within a secure and collaborative environment.

If you are interested to know more about cost reconciliation and other applications on Morta, please contact me or schedule a demo.

Leave a Comment