Limiting unforeseen project costs with an Early Warning System

The challenge

Merford’s case is one that may be recognizable to many companies. How can you on forehand determine the budget and margin on a project as accurately as possible? Merford wants more insight into the progress of the projects and the associated costs.

Due to Merford’s wide variety of sound solutions, safety procedures and risks due to different execution and assembly conditions, each project follows a different procedure. Due to this difference, there is a chance that the profit margin will be lower than expected, which may lead to a mistake causing several tons of loss. This must of course be prevented.

These costs are currently already being monitored by project leaders and financial controllers, but there is still room for improvement. Reporting is now often retrospective, which means that timely action cannot be taken. Project leaders’ forecasts of how a project will progress are also subjective, which can cause a distorted picture. Merford would like to be informed of the (expected) expenditure during the execution of the project and to be notified if a project turns out to be more expensive than initially thought. By detecting these financial risks in a timely manner, adjustments can be made where necessary to ultimately prevent unforeseen costs.

The results

The MKB Datalab has created a dynamic link between a Python script and the SQL Database as a solution for Merford. This makes a weekly forecast of the margins for projects and places this in the SQL Database. It took 4 projects to achieve the end result, which are detailed below step by step.

1. Gain insight into data and cluster different projects

First, we look at the factors that influence costs. It starts with a data analysis to map the different data sources and their quality. The quality initially seemed good, but as soon as we started to delve deeper, it turned out that no consistent start time for the next phase was noted, which influences the final costs per phase. As a result of this project, a report was made that describes the findings from the data. A simple Linear Regression model in Python has also been made which predicts the margin per project, even though this model is not yet optimal.

The base of Linear Regression model in Python

2. Improvement forecast model and proof of concept in Excel

With the second project, we improved the model of the first project. With this we have been able to make a Linear Regression model which can predict the expected margin per project with 72% accuracy. We then worked towards a usable proof of concept in which Merford can execute the Python script at the push of a button and receive an Excel file with the expected margins per project. For this proof of concept they don’t need any knowledge of Python, but they do get first insights that can identify outliers in costs!

3. More specific prediction model

The existing Linear Regression model looks at various factors across the entire project and tries to predict the final margin of the project based on that. However, it is interesting for Merford to know whether it is possible to predict the margin for each different phase (design, assembly, engineering, etc.). In this way, they can check per phase whether the project remains within budget or not, and respond to this during the project. Instead of predefined phases, we can also see if it is possible to predict the margins per different time units, eg at 10% of the expected lead time. Unfortunately, further specification of the model did not yield any results, so we continued with the previous general model.

4. Implementation

We have made a working Linear Regression model, now the trick is to get it working so that Merford can easily view the predicted margins. For this we have created a Windows Task Scheduler that automatically runs the Python script to make new predictions. These predictions are immediately processed in the SQL database.

Because new data is released with each project, we update the model once a month with this new data. To ensure that the Python script runs easily on the SQL server, we have made a “package” with Docker which can be placed on the server in its entirety. This makes installation easier. To guarantee all this knowledge, a Github account has also been created that manages the different versions of the application.