I'm trying to calculate the number of weeks cover an inventory value
represents. My worksheet is laid out as follows:
Each Row represents a week
Col A Col B Col C
W/ending Inv Demand Qty Weeks Cover
1095 44 15.1
1046 64 14.3
984 62 13.3
922 62
863 59
806 57
1019 57
962 57
1034 72
939 95
844 95
750 94
656 94
593 63
514 79
435 79
357 78
265 92

The values in the weeks cover (Col C) need to be a calculation based on the
value in column A using the demand from column B to work out how many weeks
the on hand inventory will last.
The first 3 values are what I expect the function to return in these cells.
I assume this will require an array and to be honest I'm struggling with the
logic.
Can anyone help me with the correct function to calculated this.

Can you explain what the numbers represent? Walk us through an example using
that data.

--
HTH

Bob Phillips

""Kevin Carroll via OfficeKB.com"" wrote in message
...
I'm trying to calculate the number of weeks cover an inventory value
represents. My worksheet is laid out as follows:
Each Row represents a week
Col A Col B Col C
W/ending Inv Demand Qty Weeks Cover
1095 44 15.1
1046 64 14.3
984 62 13.3
922 62
863 59
806 57
1019 57
962 57
1034 72
939 95
844 95
750 94
656 94
593 63
514 79
435 79
357 78
265 92

The values in the weeks cover (Col C) need to be a calculation based on
the
value in column A using the demand from column B to work out how many
weeks
the on hand inventory will last.
The first 3 values are what I expect the function to return in these
cells.
I assume this will require an array and to be honest I'm struggling with
the
logic.
Can anyone help me with the correct function to calculated this.

The Data in Column A is the quantity of the item I have in stock in that week
(From MRP).
The Demand in Column B is the quantity of cases we have forecasted sales.
Hence if A2 = 1095, to work out how long it would take before the quantity
would be consumed I deduct the quantity in B2, then B3, then B4 until it
reaches zero. The number of rows it takes to consume the quantity from A2 =
the weeks cover I need.
N.B I do not want to avg the demand as the product is highly seasonal. I need
to work on actual values for each week as far out as I can.
The sheet contains multiple items and hence every 36 rows the item changes
and the function will need to cater for this.
Hope this makes it clear..

--
Message posted via http://www.officekb.com