Calculate weeks cover

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

--
HTH

Bob Phillips

""Kevin Carroll via OfficeKB.com"" <[email protected]> wrote in message
news:[email protected]...
> 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