Analyzing estimated vs. actual costs using Sage Estimating is a common discussion topic with many (most) of our clients. Who doesn’t want visibility into the accuracy of their estimates?
Our recommended approach to capturing actual or as-built costs is to use a cost management tool, such as Oracle Primavera Unifier, EcoSys, or other leading cost management solutions. There are plenty to choose from.
The Sage Estimating estimate can easily be transferred electronically to the cost management system of choice to serve as the initial budget for the project. Then the cost engineer or project manager tracks actuals (costs, quantities, hours, etc.) in the cost management system. The cost management system supports comparing the original budget vs. actuals and tracking changes (e.g. change orders) that will impact the final actual costs.
In our experience, the estimate will be built up in greater detail than the cost management budget. Thus, the comparison of the original budget to actual costs will be at more of a summary level than the details in the estimate. A summarization of estimate data is inevitable.
However, some of our clients desire a deeper analysis of their estimates vs. actuals. This can be accomplished in a few easy steps using Sage Estimating.
It’s a matter of copying the original estimate file and entering in actuals (at any level). The differences between the original estimate and the actual costs are proportionately distributed to the underlying details. This workflow is described below.
If you’re going to try this, make sure you copy the original estimate and label it with a reference to actual costs.
Capturing Actuals in Sage Estimating
Our example focuses on a concrete equipment pad. The original estimate includes activities and assemblies, which include items. In this example, the actuals are tracked at the assembly level (but any organizational level could be used for tracking; e.g., discipline, system, work package, etc.) as shown in Figure 1. below.
Figure 1. Original Estimate (Concrete Equipment Pad assembly is shown with items expanded)
Note the original estimate values in Figure 1. above:
- The 30 Concrete Equipment Pad Total Amount value is $4,851.
- The 30 Concrete Equipment Pad Total Cost/Unit value is $438.58/cy.
Let’s assume the actual cost for the A10.30 Concrete Equipment Pad is $6,000.00. Furthermore, let’s assume the original estimate assembly quantity was correct (or the project manager wasn’t tracking quantities).
The user enters 6000 into the Total Amount field of the A10.30 Concrete Equipment Pad. The system recognizes that the user is trying to force a value at an assembly level and prompts the user to decide how to distribute the difference. In this case, the difference is $1,149.21.
In Figure 2., Sage Estimating displays the Adjust Column panel to allow the user to determine a distribution strategy. That is, how should this difference be distributed?
Figure 2. Adjust Column window
We decided to spread the difference proportionately across all the items that make up the A10.30 Concrete Equipment Pad.
Viewing the results
The estimate is now adjusted to reflect the actual costs. Note the following in Figure 3. below:
- The 30 Concrete Equipment Pad Total Amount value is the actual cost of $6,000.
- The 30 Concrete Equipment Pad Total Cost/Unit value has been recalculated to $540.02/cy.
- All the items that build up the 30 Concrete Equipment Pad are proportionately adjusted to ensure their Total Amount values add up to the actual cost of $6,000.
- All respective item fields (e.g., the Total Cost/Unit field) are back-calculated to ensure the math is correct.
- The red triangles in the top right corner of the modified cells.
In Figure 3., the hover tip on the last item (Excavated Soils Disposal) identifies the relative change percentage and the original value.
Figure 3. Adjusted estimate to reflect actual costs
The actual amounts can be entered at any level. For example, let’s assume the project manager was tracking actual costs at the activity level (not the assembly level). The project manager could enter a value for the 100 Civil Total Amount value and the cost would be spread across all the assemblies in the 100 Civil activity (e.g., Spread Footing, Concrete Equipment Pad, Clear & Grub, and Asphalt Concrete Paving).
Analyzing the differences
In this final step, the estimator or project manager can analyze estimated vs. actual costs using a Sage Estimating Variance Report shown in Figure 4. below.
A filter has been applied to the report to isolate the A10.30 Concrete Equipment Pad. The only fields selected for the comparison are the Unit Cost and Amount fields, but others are available (e.g., Labor, Materials, etc.).