Bill Of Material Forecast Based on Timing of Material Change

mwildcats

New Member
Joined
May 16, 2019
Messages
6
Hi All,

I am looking for help regarding forecasting future material requirement from finished goods forecast.
I have this set up currently in excel with a few index and sumproduct formulas.

However the material code within the finished goods can change depending on promotion etc, at various points through the year.

I currently have a "change calendar" set up whereby it lists what material code should be used in each finished product, and when a change is happening (see below)

So as you can see we should use code 700152 from wk1 - wk 4 and then change to 700256 from wk5-wk9 and then go back to 700152 from wk10 until further notice

Item NoDescWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9Wk10Wk11Wk12
11111Prod A700152700256700152

<tbody>
</tbody>

Based on a sales forecast of 10 FG per week (where sales forecast is set up in the same way i.e. FG week numbers and qtys, (and for arguments sake the BOM is 1 part 700152 for 1 FG) I would want to see a material forecast - like the below...

MaterialDescWk1Wk2Wk3Wk4Wk5Wk6Wk7Wk8Wk9Wk10Wk11Wk12
700152Mat 110101010101010
700256Mat 21010101010

<tbody>
</tbody>


How can i get this, using Excel?
May i add there would be multiple FG's with the same material change, so would need to sum them all into a consolidated view in the format of the above


Many thanks in advance for your help and support

regards,
mark
 

mwildcats

New Member
Joined
May 16, 2019
Messages
6
Is there any support for this question, is there a better way to go about this?

thanks,
 

Swayzy

Board Regular
Joined
Mar 30, 2018
Messages
77
Is there any support for this question, is there a better way to go about this?

thanks,
Some combo around offset and sumifs could be what you are looking for. I have found these two together can solve most calendar sum problems. The row, col, and especially height and width arguments open up alot of options
 

mwildcats

New Member
Joined
May 16, 2019
Messages
6
Some combo around offset and sumifs could be what you are looking for. I have found these two together can solve most calendar sum problems. The row, col, and especially height and width arguments open up alot of options
Okay say for example both my tables above started in B1 &D1 respectively how would the formula look?

thanks,
mark
 

Swayzy

Board Regular
Joined
Mar 30, 2018
Messages
77
Okay say for example both my tables above started in B1 &D1 respectively how would the formula look?

thanks,
mark

If you want direct help to solve the problem I would require that you upload (and share through Dropbox or similar) dummy workbooks that have the desired structure and outcome. That way I can see exactly what/where the end is and can then much easier fill in the formulas. As is the explanation is hard to understand fully. I guess the problem is easily solved if the end result is visibly clear. Offset and sumif/countif can do some wonderful things but that also means a very clear description of the problem. For example if you have calendar with dates from 1/1 to 31/12 then you can easily have a monthly total by using Offset and extend its range by the count of days in the month, and to that you can use sumif if you want to be able to filter for specific products etc.
 

Forum statistics

Threads
1,082,612
Messages
5,366,606
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top