cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kroneborge Regular Visitor
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
Super User

Re: Forecasting ingredients and packaging needed from BOM

Hi @Kroneborge

 

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.

Kroneborge Regular Visitor
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 NoDescription1/1/20192/1/20193/1/20194/1/20195/1/20196/1/20197/1/20198/1/20199/1/201910/1/201911/1/201912/1/2019 Forcast for period
12408Vanilla    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

TopParentProductMain DescriptionNo_DescriptionUnit of Measure CodeSum of Quantity ForecastOn Hand Needed  Needed Qty 
12408Vanilla12408(blank)(blank)     347,96853652    294,316                        -  
12408 BOM0049Vanilla 10.7% Ice CreamGALLON0.5    347,96853652    294,316             147,158
12408 P00298Bottom ICEA1    347,96853652    294,316             294,316
12408 P00399Sidewall 56 oz VanillaEA1    347,96853652    294,316             294,316
12408 P00424Lid IC VanillaEA1    347,96853652    294,316             294,316
12408 R00005Skim PowderLB0.5    347,96853652    294,316             147,158
12408 R00008Sugar Luquid 75/25 BlendLB2.5    347,96853652    294,316             735,790
12408 R00125Annatto ExtractOUNCE0.2    347,96853652    294,316               58,863
12408 R00179Stabilizer SummitLB0.1    347,96853652    294,316               29,432
12408 R00182Whey PowderLB0.5    347,96853652    294,316             147,158
12408 R00185Raw Milk - Skim IILB5    347,96853652    294,316         1,471,580
12408 R00186Raw Milk - Butterfat IILB2    347,96853652    294,316             588,632
12408 R00270VanillaOUNCE0.1412    347,96853652    294,316               41,557
12408 W00033FZ Mix  10.7% Ice CreamGALLON1    347,96853652    294,316             294,316

 

Ingredient forecast

 

No_DescriptionBalance QtySafety Stock Level Forecast Needed
R00125Annatto Extract3,5792,560        3,676     (2,657)
R00182Whey Powder66,27520,000     64,885   (18,610)
R00270Vanilla31,827144     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
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).

Kroneborge Regular Visitor
Regular Visitor

Re: Forecasting ingredients and packaging needed from BOM

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

 

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

 

 

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)