Hi,
I have a forecast of future Ice Cream Sales that I want to translate into a forecast for needed ingredients and packaging. I have a table “BOM” that says how many of ingredient X I would need for each item Y.
In addition, I want the forecast to take into account how many of item Y why we have on hand, as well as how much of ingredient X we have on hand.
For example, if Vanilla has projected sales of 100 units a month for the next 3 months. Has 50 units on hand right now. Has 75 ounces of Vanilla extract on hand. And the BOM requires 2 ounces of vanilla per finished good unit.
Then total vanilla demand would be 300 unit sale forecast - 50 units on hand for 250 total unit demand. * 2 ounces of vanilla = demand for 500 ounces of vanilla - 75 ounces of vanilla on hand means we need to order 425 ounces of vanilla to meet the demand for the next 3 months.
I get very confused on how I’m supposed to connect the sales forecast, quantities on hand and the BOM and pull it all together. I’m thinking maybe something with related table, but I’m not quite sure how it would work.
Thanks,
I created a sample PBX file which hopefully illustrates this.
Forecast Example.pbix (162.7 KB)
A little confused on what you are trying to solve here. I had a look at the PBIX and still unsure what else you want calculated?
Thanks
Jarrett
I need the forecasted amount of future ingredients. IE given a forecasted demand of future finished goods, given current finished goods inventory, and given ingredients on hand, how much ingredients do I need to order?
The tricky part that I don’t get is how I translate that through the BOM (bill of materials).
Because if I need to produce 1000 units of finished product I might need 1000 * x of the ingredient where X is the number on the BOM. And of course multiple finished products might share some of the same ingredients.
So what I’m looking for is something like ingredient X demand = finished good item A demand * the amount of X in the bill of materials + the finished good demand of item B * the amount of X in the bill of materials (If A or B have item X in the BOM of course)
Does that make sense?
That makes sense. I will have a look again at your PBIX and see what I can come up with.
Thanks
I created a working example of what I’m trying to do in excel.
Get the finished goods forecast from tab “forecast”
On the “BOM Pivot tab” take the finished goods forecast and apply it to individual items on the BOM based on quantity on the BOM
Then on the ingredient tab get the amount needed of each itemIC Forecast test.xlsx (17.6 KB)
How was this solved? I’m a bit unclear here. I am facing a similar issue.