Calculate weeks of forward coverage of inventory on separate sheet

[Slkrz]

Hi all,

I am having a problem with trying to get this working correctly.

Previously I found a formula which helps to calculate the number of weeks of inventory I have for any week depending on the forecasted demand quantities.

I have included the sample data below:

Forecast		10	30	50	20	50	40
Inventory	200	190	160	110	90	40	0
Weeks cover	6	5	4	3	2	1

<tbody>
</tbody>

Data range is from A2:H6 and the formula I used is as follows, starting in cell B6:

=IF(B4 < SUM(C2:$H$2),SUMPRODUCT(--(B4>=SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))))+LOOKUP(0,SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2-B4,(B4-(SUBTOTAL(9,OFFSET(C2:$H$2,,,,COLUMN(C2:$H$2)-COLUMN(C2)+1))-C2:$H$2))/C2:$H$2),IF(B4=SUM(C2:$H$2),COLUMNS(C2:$H$2),"Inventory Exceeds Forecast"))

The formula works well and I am able to get what I want, however, I need to be able to show row 6 in another summary sheet according to part numbers as this sample data is only for one part number. I have about 300 to list.

Also, it has to be repeatable as I get a new set of data every week and I would like to just copy/transfer the data over to another workbook with the summary sheet.

Is there anyway to do that? Does it need a macro or just formulas will do?

Thanks in advance for any assistance!

 

[Slkrz]

Hi again,

Can anyone help me with this? I really need to find a solution to this soon.