Forecasting ingredients and packaging needed from BOM

[Anonymous]

Hi,

I'm trying to get the forecasted amount needed of packaing and ingredients.

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,

[AlB]

Hi @Anonymous

You'll need to show the structure of the tables in your data model (in table/text format preferably). Or even better if you can share the pbix.

[Anonymous]

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” 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 item

Forecast table

Item No	Description	1/1/2019	2/1/2019	3/1/2019	4/1/2019	5/1/2019	6/1/2019	7/1/2019	8/1/2019	9/1/2019	10/1/2019	11/1/2019	12/1/2019		Forcast for period
12408	Vanilla	55,998	51,888	73,040	63,786	75,969	83,285	92,874	78,987	60,939	92,370	83,199	65,880		347,968

BOM

TopParentProduct	Main Description	No_	Description	Unit of Measure Code	Sum of Quantity		Forecast	On Hand	Needed	Needed Qty
12408	Vanilla	12408	(blank)	(blank)			347,968	53652	294,316	-
12408		BOM0049	Vanilla 10.7% Ice Cream	GALLON	0.5		347,968	53652	294,316	147,158
12408		P00298	Bottom IC	EA	1		347,968	53652	294,316	294,316
12408		P00399	Sidewall 56 oz Vanilla	EA	1		347,968	53652	294,316	294,316
12408		P00424	Lid IC Vanilla	EA	1		347,968	53652	294,316	294,316
12408		R00005	Skim Powder	LB	0.5		347,968	53652	294,316	147,158
12408		R00008	Sugar Luquid 75/25 Blend	LB	2.5		347,968	53652	294,316	735,790
12408		R00125	Annatto Extract	OUNCE	0.2		347,968	53652	294,316	58,863
12408		R00179	Stabilizer Summit	LB	0.1		347,968	53652	294,316	29,432
12408		R00182	Whey Powder	LB	0.5		347,968	53652	294,316	147,158
12408		R00185	Raw Milk - Skim II	LB	5		347,968	53652	294,316	1,471,580
12408		R00186	Raw Milk - Butterfat II	LB	2		347,968	53652	294,316	588,632
12408		R00270	Vanilla	OUNCE	0.1412		347,968	53652	294,316	41,557
12408		W00033	FZ Mix  10.7% Ice Cream	GALLON	1		347,968	53652	294,316	294,316

Ingredient forecast

No_	Description	Balance Qty	Safety Stock Level	Forecast	Needed
R00125	Annatto Extract	3,579	2,560	3,676	(2,657)
R00182	Whey Powder	66,275	20,000	64,885	(18,610)
R00270	Vanilla	31,827	144	41,557	(9,875)

I do have a simplified pbix I can share, but don't see how to upload a file to the forums

[AlB]

@Anonymous

You need to post here the URL to the file hosted elsewhere. You can use platforms like OneDrive, Dropbox or upload the file to a site like tinyupload.com (no sign-in required).

[Anonymous]

Got it, here you go.   Here is a simplified version of the pbx. 

https://drive.google.com/file/d/1i2pqmyic7uKH8SoH31U4Ri_eeJqxd4YE/view?usp=sharing