Bill Of Material Forecast Based on Timing of Material Change

[mwildcats]

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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item No[/TD]
[TD]Desc[/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[TD]Wk5[/TD]
[TD]Wk6[/TD]
[TD]Wk7[/TD]
[TD]Wk8[/TD]
[TD]Wk9[/TD]
[TD]Wk10[/TD]
[TD]Wk11[/TD]
[TD]Wk12[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]Prod A[/TD]
[TD]700152[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]700256[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]700152[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Material[/TD]
[TD]Desc[/TD]
[TD]Wk1[/TD]
[TD]Wk2[/TD]
[TD]Wk3[/TD]
[TD]Wk4[/TD]
[TD]Wk5[/TD]
[TD]Wk6[/TD]
[TD]Wk7[/TD]
[TD]Wk8[/TD]
[TD]Wk9[/TD]
[TD]Wk10[/TD]
[TD]Wk11[/TD]
[TD]Wk12[/TD]
[/TR]
[TR]
[TD]700152[/TD]
[TD]Mat 1[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]700256[/TD]
[TD]Mat 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

Is there any support for this question, is there a better way to go about this?

thanks,

 

[Swayzy]

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]

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]

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.