Master Long-Term Project Costs: Our Guide to Building a Cost Control Reporting System

Introduction to Project Accounting

When large projects spanning several years commence, one of the key requirements for senior management stakeholders and in particular the commercial staff is preparation of monthly or periodic performance reports.

The performance report has costs to date and forecast costs to complete giving the Estimate at Completion of the project. The forecast costs include:

Estimate of work outstanding and its estimated cost,
Assumptions for inflation, interest rates and any indices or other data used for cost and revenue,
Compare cost with budgets and forecasts,
Commentary for any variance with action to mitigate overruns and exploit savings,
Carry out risk analysis and adjust the cost,
Work out scenarios for best and worst outcomes by amending the cost, risk and commercial assumptions,
In some cases, there is a target price for the project with gains and losses shared between the employer and contractor.

Long term contracts must be closely monitored from beginning to end to keep control of costs and revenue. The impact to earnings and financial position of any organisation can be disastrous if the project is not monitored and managed proactively and regularly.

Building a Cost Control Reporting System from ground up is a complex undertaking which requires combining automation, data integration, financial modelling and understanding existing business processes to build a viable solution.

Case Study

This case study is for a long-term major infrastructure project spanning 5 years or so. The brief was to setup a system to monitor the performance of the project and meet the needs of Finance and Commercial Departments together with reporting requirements of key stakeholders including senior management. The brief was not specific as the data available was not known and the reporting requirements were being formulated.

The development of the system was on continuous improvement and development as there was uncertainty on available data and final requirements for performance measurement and reporting to stakeholders.

Outline

In brief, the plan was to:

Understand the requirements of all interested parties in principle,
Investigate the data available,
Transform and format the data to meet the project's requirements,
Develop the performance measurement and reporting in stages. This would involve close consultation with all stakeholders at regular intervals.

The costs were captured in a cost control system. What follows is a detailed description of training provided on this critical system which was the basis of the system being developed.

Training

A member of the IT team attended the site, installed the application to access the cost control system on laptops. A demonstration of how to log in and out of the application was provided. End of training in under 15 minutes!!!!

When we queried on how to navigate the application, we were informed installation of the application and teaching users to log in and out was the only training the IT team provided. Much to our surprise, there was no provision for further training by any other department or persons.

This aspect should be borne in mind when a project starts. There should be provision for training personnel on key systems in use. Members of the finance team who were responsible extracting information from the cost control system were new to the project and not familiar with it.

Data Discovery

Without trying to find anyone to provide help with using the system, we logged into the cost control system and spend a couple of days familiarising ourselves with it. The application was well written with an intuitive user interface.

An option to extract data was discovered. A decision was not to not write any reports in cost control system. Instead, an extract of the information required would be downloaded to be transformed and formatted for reporting and performance management.

A decision had to be made on the format of the data downloaded. The options were:

Detailed transaction level,
Summary Level,
A combination of both.

A decision was made to extract data at a detailed level. The advantage was extensive analysis was possible compared to data summaries. The downside was the volume of data. Approximately 30,00 records had to be downloaded each month. In addition to the transaction data, master files had to be incorporated to provide more descriptive information.

For example, the transaction file has the supplier or contractor code. A master file was required to look up the name. The WBS (Work Breakdown Structure) was provided for each transaction. This provided the current project, the location, an activity code and an analysis code. This WBS must be broken out into its constituent parts and each one referenced to a master file for a better description. See the diagram below.

Data Definitions

A methodology had to be devised to quickly include the additional records each month, carry out any lookups, transform the data and make it available for analysis. Speed was of the essence as once the data was available from the cost control system, the lookup and transform process had to be completed in a very short space of time, ideally in well under an hour, preferably half an hour.

Another issue was to decide which fields to download. Some field names were obvious and others less so. If a field was not included in the data downloaded from the cost control application and was subsequently required, the change in structure of the file will cause problems in the system design and there would have been difficulty in changing the design retrospectively. The decision was made to include all the fields in the data extract (over 200) as the file size was not much smaller if fewer fields were included. As the design included all fields, if in the future a need arose to use a field not previously required, only the reports would have to be amended and this could hopefully be accommodated without too much difficulty. The design of the system would not require any change.

Data Storage

Data storage had to be considered. As a spreadsheet was not feasible, the data would have to stored in a database. Access to corporate databases on servers was not acceptable as it was against company policy in any case and would require experts to set up. The cost outweighed the benefit. A pc-based database would have to be used.

This would be stored on a shared area for different team members to use. The monthly update was responsibility of the finance team. All the transaction and master data would be kept in distinct files within the database.

All the lookups would be set up to automatically link together. Data for various analysis and reports would be set up as separate data feed and would only include fields necessary for any given task. If further fields were required, the amendment to the data feed was feasible.

Implementation

The basics of data availability and storage were laid out. A decision had to be made on how to implement it.

The basic plan was to:

Extract information from the Cost Control System and make it available to database as a text file,
The transaction data had to be uploaded to the database application,
The data feeds would be dynamic. Once all the links and automation were in place, the data would be available for analysis and performance measurement,
Users could include the data directly from the database application into their systems and spreadsheets for analysis and measurement as required.

The Solution to Building a Cost Control Reporting System

The solution was as follows:

Transaction data would be extracted monthly and stored in a shared area.
This file would be accessed by a spreadsheet. Using spreadsheet application’s scripting language, the file would be read and using SQL (structured query language) the data would be uploaded into the database.
The file would be read one line a time and the data loaded into the database one at a time. This eliminated the need to read the file, store data in the spreadsheet and then upload it to the database. There was no significant difference between reading all the data in first and reading it one line at a time.
End users would then be able access this database to download data into their applications for further processing.

As is apparent from the process described above, a good understanding in following technical areas was required:

Programming and scripting languages,
SQL to upload data into database,
Database design and implementation,
Design and implement the lookups,
Make data available to other users and their applications.

This was achieved and the diagram below illustrates the solution that was implemented.

Our expertise enabled this solution to be designed and implemented.

Conclusion - Over Delivered

The results exceeded our expectations and met all reporting requirements of key stakeholders. The database design was robust. From data extract to updating the database, there was no manual intervention. The entire process was fully automated.

The target to the update the database within half an hour from when data was available from cost control was met.

There were some unexpected benefits:

An insurance claim resulted in the loss adjuster looking into the costs claimed in detail. As a separate code was created for each claim, costs that could easily be traced to source documents. This worked so well, the finance team dealt with cost queries without the presence of the commercial team as far as the audit of the costs was concerned.
Purchasing department was looking for total spend for a particular supplier and were having issues retrieving the data from the cost control system. When the Finance team was requested to help, the information was on tap and made available in half an hour. Purchasing had spent a considerably more time previously sourcing this data.
A contractors’ report was prepared using the data from cost control system, taking 3 days to update. The process was manually driven. Any change meant inserting rows manually as well as amending subtotals. Reconciling back to control totals was difficult. Using our automated system, this report was prepared in minutes and was included as part of the month end process.
Ad hoc reporting was feasible without undue effort.

How can we help?

Feeling the drag of inefficient processes slowing your business down? At AnalytixPlanning, we can help you break free. Our team brings a powerful combination of technical expertise, programming skills, and design thinking to the table. This unique blend allows us to not only identify bottlenecks in your current workflows but also craft customized solutions that transform them into streamlined operations.

We start by taking a deep dive into your existing processes, pinpointing areas ripe for improvement. Leveraging automation wherever possible, we develop solutions that free up your valuable resources and ensure seamless data management through our programming skills. But it doesn't stop there. Our focus on user-friendly design ensures smooth adoption and minimal training needs for your team.

In short, we don't just solve problems; we empower your team to work smarter. Imagine a future where manual drudgery is a thing of the past, replaced by automated efficiency that fuels accurate results and frees up your employees to focus on high-value activities. If you're ready to unlock this potential and streamline your path to success, contact us today. Let's transform frustration into efficiency, together.

Analytix First, Then Planning

Turn data insights into action.