Hi,
Need advise from the experts in this forum. Data is below
[TABLE="width: 774"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]W1[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD]W5[/TD]
[TD]W6[/TD]
[TD]W7[/TD]
[TD]W8[/TD]
[TD]W9[/TD]
[TD]W10[/TD]
[/TR]
[TR]
[TD]Forecast[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Closing stock[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Week supply[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
I would like to get a automatic calculation formulation for Week supply. For example. In Week 1 Week supply is 2 because of Week 1 closing stock can cover forecast for week 2 and week 3 (20+20) - 40 = 0, therefore it is 2.
However in Week 5 Week supply is 1.5 week because week 6 forecast is 10 and week 7 forecast is 20, closing stock in week 5 is 20, therefore week supply base on closing stock week 5 going forward is 1.5.
Hope this explains.
Cheers
Sasils
Need advise from the experts in this forum. Data is below
[TABLE="width: 774"]
<colgroup><col><col span="10"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]W1[/TD]
[TD]W2[/TD]
[TD]W3[/TD]
[TD]W4[/TD]
[TD]W5[/TD]
[TD]W6[/TD]
[TD]W7[/TD]
[TD]W8[/TD]
[TD]W9[/TD]
[TD]W10[/TD]
[/TR]
[TR]
[TD]Forecast[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Closing stock[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Week supply[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]0.5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
I would like to get a automatic calculation formulation for Week supply. For example. In Week 1 Week supply is 2 because of Week 1 closing stock can cover forecast for week 2 and week 3 (20+20) - 40 = 0, therefore it is 2.
However in Week 5 Week supply is 1.5 week because week 6 forecast is 10 and week 7 forecast is 20, closing stock in week 5 is 20, therefore week supply base on closing stock week 5 going forward is 1.5.
Hope this explains.
Cheers
Sasils