The company produces certain products to individual orders. Each product has a set of numerical and drop list properties from which the estimated raw material cost and lead time are calculated. After production, the actual production time of the products and the cost of raw materials, which may vary, become apparent.
Now, when salespeople receive a new order from a customer, they try to filter their Power Bi report to find similar products that have been ordered in the past and try to estimate the most likely cost.
It is desirable to be able to take the parameters obtained and, based on the data already available in the history, to find the most similar orders automatically and, based on this, to tell the salesperson the most likely production time and cost of the raw materials.
He would like to see something like this:
Input
Length 120
Width 200
Height 80
Expected production time: 86 hours, possible deviation +/-30%
Expected cost of raw materials: 145 € possible deviation +/-30%
If I enter more parameters, the error should decrease accordingly
Input
Length 120
Width 200
Height 80
Colour Black
Estimated production time: 88 hours, possible deviation +/-10%
Expected cost of raw materials: 165 € possible deviation +/-15%
Can this be done with Power BI? What tools and technologies can I use?
First, PowerBI can filter what you tell him to do. Choose some inputs and check available history data based on that.
But be careful.
Manufacturing data and process can be pretty much complex. For examplewithin one project I did that what you describe but it took us months to set up the data and right calculations - we use Activity Base d Costing Methodology, Bill of Materials with component semi-products. Besides raw materials there are also human workers / (salary and other human) costs + also different machines costs + other costs.
Production time depends also of machine availability, quantity (since there are set-up / close-up time for machine to be prepared for operation at the beginning and after at the end) and some other factors.
We used different technology, but in this case tools and technology does not matters that much as business process and calculation behind the scene.
If you want to have just list of available history data based on 3-4 input history parameters + some calculations like average, median, min, max etc. you can use PowerBI without additional data setup offstage.
Sorry for not replying sooner, the forum post had fallen into SPAM and I didn’t expect to find a reply,
It is obvious that production times can vary even for completely identical products. After all, it depends not only on the size of the order, which is visible, but also on when the order was made - in the morning or in the evening, and what kind of worker was handling the order - a more experienced one or a newcomer. Similarly variates the cost of raw materials. But despite all these uncertainties, the current working time forecasting system provides data that can be relied on more or less, and which is actively used in the preparation of quotations.
Power BI is now used as follows:
the salesperson looks at how much labour time was needed and what the material cost was for a similar product in the past.
If the report finds a large number of records matching the criteria - then the search is narrowed down - either by reducing the dimensional inetrvals or by adding additional available criteria.
If it finds too few records for a reliable forecast, then the search is widened by increasing the dimensional frames, taking a larger time period into the past and so on.
The question is whether this work can be done by Power BI or some other business analysis system? The salesperson’s job would be to gather all the available information about the potential order, and the system would then try to search for similar orders in the past.
Based on what it found, it would give estimated hours and material costs and probable tolerances.