The client provides complex ERP systems for large enterprises, as well as audit and consulting services for manufacturing companies, helping them to optimize their work and financial statements.
PROBLEM AND OBJECTIVES
Analysts of the client used to calculate all the costs and expenditures of different factories in cumbersome Excel spreadsheets. The more data were in the tables, the slower the program worked. Moreover, if it was needed to add some columns to the table (e.g. with new components), users had to change formulas within cells manually. That led to slowing down the analytical process and caused some human errors.
In order to streamline the work and increase efficiency, the client came up with an idea to integrate to the existing ERP system an additional module with detailed production analytics and forecasts. The module is supposed to be easily customized to any factory and production process, so users just add required data about machines, materials, components and staff, while all the calculations are predetermined and proceed in the backend.
What Was Done
Our team developed from scratch a new part for an existing ERP platform, which covers the production structure, interrelationships between its components, cost calculation and simulation.
The first part of the solution represents a scheme with a plant structure. Users can build a custom model of their factory, adding level by level – workshops, equipments, modules. The scheme helps analysts to understand a factory model better and at a glance.
Clicking the items at the lowest level of the scheme, users open a section with accumulated information about each manufacturing unit: pages with general overview of its work and output, manufactured and defective products, materials used, losses, labour. It is possible to set KPIs with different parameters (output, scrap, etc.) for each machine and use charts to track changes in absolute and relative terms. Analysts can check the efficiency of each machine: how many products were manufactured, at what speed, with how many resources and losses.
Products, materials, labour pages contain tables with more detailed information about each item. For instance, labour page allows seeing the actual number of workers vs standard brigade size, actual working hours and overtime vs standard shift time, and so on. To examine expenditures, users can look into tables with total salary and its breakdown by employees, working hours and overtimes. As a result, such a structure enables analysts to identify bottlenecks and new growth points from the outset, and dig deeper into each parameter to find out the ways for improvement of production process and financial performance.
Another part of our solution was the cost module. Its main page looks like a dashboard with a summary of production costs for a chosen period of time. It provides accumulated data about output, total costs, costs per unit produced, etc. Users can also compare data with a reference period to track changes over time. A small P&L table gives more information on spent cost per element (labour, raw materials, energy, depreciation, etc.) in comparison to the allocated budget and percentage of losses. For clarity, all cost categories are also presented in a graph. This overview allows users to understand at a glance the weight of each category in total production costs and identify problem areas for their optimization.
To get more details about cost structure, users can plunge into the information presented in large P&L spreadsheets, which are another part of the cost module. They include more parameters of each P&L component and allow users to calculate how production costs depend on output volume, price changes over years, losses, and so on.
The spreadsheet configuration provides already a list of general elements which are common for different types of plants – direct and indirect labour, raw materials, packaging, energy, maintenance, quality control, operating expenses, etc. So users need just to choose required elements in settings and adapt them to own needs: change names if required, insert additional lines with components, add actual costs and prices.
The spreadsheet interface is especially easy to use: each row has a few buttons for editing data, adding a new nested row, repeating the same row or deleting it. All formulas and complicated calculations with many interrelated parameters are “hidden” in the backend, so ordinary users without admin rights won’t accidentally change or spoil them. The data can be imported from other tables (e.g. CSV, XML Spreadsheets), that saves users a lot of time and minimizes manual work.
The next part of the cost module makes it possible to simulate top-down and bottom-up scenarios. Analysts can set reduction targets (e.g. by costs, losses) and get estimations of cost optimization by years. This helps to find the perfect combination of all parameters, allowing production companies to minimize costs at the same quality and increase their EBITDA.
When a scenario is approved, it is used as a benchmark to track the current efficiency of production. Thus, analysts can detect serious deviations in time and advice on taking corrective actions if necessary.
The client had a clear vision of how the ideal analytical module should look like, so we got mock-ups of the interface and formulas to calculate the production costs. We solved the issues with everything else – software architecture, selection of the most suitable technologies and frameworks for flawless operation, frontend and backend development. Due to close cooperation of our Business Analyst with a strong financial background and the client’s analysts, together we managed to turn the complex logic of interrelations between all parameters into an efficient system of calculations on the Backend side and illustrative charts and tables on the user interface.
During Discovery phase, our team examined the existing ERP platform of the client, dived deeply into the business processes, explored alternatives and figured out the best possible technological solution. We designed a model which outlines business entities in the system and their relationships, created a detailed plan for development of each module and agreed with the client on the work flow.
According to the specifications and requirements, we recommended the technologies and frameworks which perfectly suited to the client's needs. For instance, ngx-datatable was chosen to handle complex datasets in a format of spreadsheets. It provides all the necessary features to work with a table (sort, filter, add sub-rows, etc.) and is very flexible and light. D3.js was used for data visualizations and creating responsive chart components based on dynamic data. Thus, all the information is presented in easy and illustrative form that enables users to understand data and make conclusions much faster.
After agreement of the work breakdown structure with the client, we got down to developing the MVP. We delivered module by module every two weeks, discussed tasks in Slack and had weekly videoconferences in Google Meets. Each development phase concluded with unit and manual testing, so we could detect and fix even the smallest bugs at the earliest possible stage, preventing them to turn into great issues.
- 2 Backend Developers
- 2 Frontend Developers
- 1 Business Analyst
- 1 QA Engineer
- 4 months for MVP
- 9 months for full project (including 2 months of user acceptance testing)
- Backend: .NET 5, SQL (MS SQL Server), Swagger, CQRS
- Frontend: Angular 11, RxJs, NgRx, Bootstrap 4.6, Material, ngx-datatable, D3.js, Font Awesome
- Platforms: Web
As the system was designed from the outset to be as user-friendly as possible, it took just an hour to train employees working within a new module. The results of its implementation became visible already in a week: the preparation of reports became 3 times faster than before, so users got more time for closer examination of data and simulation of different scenarios. This enabled analysts to come up with a more calibrated set of findings and recommendations for each factory within a shorter period of time, thus enhancing business value and increasing customer loyalty.