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
<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...
<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
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 No | Desc | Wk1 | Wk2 | Wk3 | Wk4 | Wk5 | Wk6 | Wk7 | Wk8 | Wk9 | Wk10 | Wk11 | Wk12 |
11111 | Prod A | 700152 | 700256 | 700152 |
<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...
Material | Desc | Wk1 | Wk2 | Wk3 | Wk4 | Wk5 | Wk6 | Wk7 | Wk8 | Wk9 | Wk10 | Wk11 | Wk12 |
700152 | Mat 1 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | |||||
700256 | Mat 2 | 10 | 10 | 10 | 10 | 10 |
<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