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.
I created a sample PBX file which hopefully illustrates this.
Forecast Example.pbix (162.7 KB)