cancel
Showing results for
Did you mean:
Regular Visitor

## Forecasting ingredients and packaging needed from BOM

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,

4 REPLIES 4
Super User

## Re: Forecasting ingredients and packaging needed from BOM

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.

Regular Visitor

## Re: Forecasting ingredients and packaging needed from BOM

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

Super User

## Re: Forecasting ingredients and packaging needed from BOM

@Kroneborge

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).

Regular Visitor

## Re: Forecasting ingredients and packaging needed from BOM

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

Announcements