in this issue
The problem with spreadsheets in project cost tracking
10:07 AM MST | November 14, 2013 | By BOB HARRELL, CEO OF MANAGEMENT CONTROLS
Project cost engineers overseeing maintenance shutdowns have built some amazing Excel spreadsheets used to calculate hours and costs and create forecasts. But, spreadsheets are only as accurate as the shift-by-shift direct field hours reported by each vendor plus cost reports pulled from an enterprise resource planning tool. When headcount peaks at 1,500/day ($1 million/day), project execution teams need more.
Several shortcomings of the spreadsheets rule stand out. First, the actual hours, or timesheets, are subject to error and are often missing in action. Daily forecasts based on incorrect or incomplete information do not support optimal decision- making. Timesheets ultimately morph into vendors’ invoices. When the two don’t match, reconciliation can delay payment and damage relationships.
Second, cost information is late. Project contractors and vendors struggle reporting hours and costs promptly. Costs originally recorded in Enterprise resource planning (ERP) systems are not visible for days or even weeks. Third, spreadsheet methodologies are often known to too few. Because spreadsheet-based systems are highly customized by their creators, they can’t easily be used across multisite operations. Frankly, custom spreadsheets are mysteries. Who knows how they work or if they really work?
While spreadsheets deliver cost estimates that might be “good enough,” global competition demands that problems must be identified before they get out of hand. That requires a good plan and real-time progress monitoring. Contract spend management (CSM) and project forecasting tools deliver that information, and their effectiveness increases dramatically with integration.
Proven, automated integration with SAP, Oracle Primavera and badge-reading systems creates a closed loop of project information featuring real-time reporting. Fully automated, fully authorized, ready-to-pay service entries and time confirmations ensure contractors are paid correctly and on time. Asset-specific hours and costs are shared to update project forecasts and refresh planning templates. CSM and forecasting tools also bolster integrated estimating solutions.
Integrated solutions don’t just share information; they have a common user interface so they can be collaboratively used by the owner execution teams and contractors working on projects. So, it’s one source of truth, shared between interested parties. In the North American refining and chemicals space, the most popular contract spend management software is Track Controller, which is used by about 75% of US refining and many large chemical manufacturers.
In the absence of specialized software, progress on projects is typically reported using vendor-prepared paper timesheets or emails with attached timesheets and spreadsheets. It’s a constant hassle to gather all the timesheets used to support progress and forecasts. Vendors try to capture and report accurate headcount and hours by work order (WO) or purchase order (PO). It’s hard to do. Unfortunately, project cost engineers can’t progress the job without vendor and internal labor timesheets.
A true contract spend management solution calculates actual hours using contact terms and rates and distributes committed hours and costs to POs, WOs and work breakdown structure (WBS) elements. Not only are hours and costs reported at the end of the shift, their accuracy means they replace vendor timesheets and invoices. Project costs are booked in SAP daily. Teams executing shutdowns, turnarounds and outages gain visibility into the hours and costs committed each shift, giving them information to make decisions before POs or WOs or WBS elements are in trouble. Vendor on-site labor resources are immediately visible and used to calculate crew productivity at the end of each shift.
Shortening a turnaround by even one day leads to significant savings. Combine that with the desire of most asset-intensive manufacturers to standardize and consistently control and execute important projects, and the benefits of real-time progress monitoring are clear.
Accurate information focuses attention where it’s needed. The highest level of accuracy is achieved by software systems that combine project estimating, real-time field data reporting, and forecasting. A closed-loop system, in which each component informs and strengthens the others, produces a level of visibility that once seemed unattainable.