Merford is a specialist in noise control and is a supplier of soundproof materials and spaces. For example, in Merford projects, think of making industrial machines or production lines soundproof. The prices and duration of these projects vary widely, which sometimes makes it difficult to estimate what the profit margin of projects will be. Because this sometimes turns out lower than expected, MKB Datalab started working with a model that predicts the ultimate margin on a project. Thanks to an Early Warning System, Merford can anticipate immediately, which leads to limited unforeseen project costs.
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 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.
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.
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!
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.
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.
“When you realize projects in construction and industry, there are several factors that influence the margin. In addition to experience and craftsmanship, data science also has a place in this. Despite the high complexity that has to be overcome to create such a system, JADS MKB Datalab delivers a working system step by step.”
Joost Vertooren – Merford
“As an organization, you change along with the project and the process. A student helps to shape this process. As an organization, it is important to participate in this process and not only to give the student an assignment. If your organization also moves with the student, you can really take the next step and you will get a lot in return. We are very satisfied with the results of the projects and have now started our 6th project with MKB Datalab.”
JADS provides innovative data science programs at undergraduate, graduate and postgraduate level, conducts cutting edge data science research and offers great business opportunities with a continually growing ecosystem. From startups to SMEs and large corporates, our business partners gain access to talent, high level knowledge and business opportunities.
Partner up with JADS like 300+ other organizations and businesses and shape your data-driven future