Weeks on hand

[Theawilla]

Excel 2003

Hello, I am trying to calculate weeks on hand.

I have a forecast for an item by week (A=week1, B=week2, etc) I also have
the total quantity on hand. I need a formula that will calculate how many
cells (weeks) = the total quantity on hand. Example:
A1=99; B1=105; C1=89; D1=102 (forecasted quantities)
A2=312 (quantity on hand)
A3=3.2 (weeks on hand- rounded to tenths)- this is where I need the
formula.

Thanks in advance for any help given.

 

[Mike H]

Hi,

I'm confused by this question, why does A2=312?

Also, if you can describe how you got to 3.2 in A3 then I'm sure someone
will provide you with a formula.


Mike

 

[vezerid]

A simple approach would be to divide 312 with the average consumption
per week, hence in A3:

=A2/AVERAGE(A11)

With your numbers as input the formula returns: 3.159493671

Does this help?
Kostis Vezerides

 

[Rick Rothstein \(MVP - VB\)]

Your example layout is somewhat confusing. I'm not 100% sure, but I think
you may be looking for this formula...

=ROUND(A2/A1,1)

Rick

 

[Theawilla]

This is really close but is there some way to do it without averaging? I
have 52 weeks of forecast data and this would work if the weeks were all
pretty even, but doesn't account for huge spikes during
Thanksgiving/Christmas.

This would work if I just counted one week past my total on hands and only
used those weeks but I would have to do that for every week instead of just
copying the formula.

Thanks so much for your post.

 

[vezerid]

I understand your point and thought as much in the first post, only
wondered whether a simpler approach would do.

The new approach basically searches until we get to a month where
inventory will not be enough. Then it divides the remainder by the
inventory amount. To do this I modify the layout somewhat. A11 still
contain forecasts. Now in A2:

=SUM($A$1:A1)

Copy to the right to produce cumulative consumption. Now it is cell A3
that contains the inventory. The following formula, for your data,
returns 3.078431373

=IF(ISNUMBER(MATCH(A3,A22,0)),MATCH(A3,A22,0),MATCH(A3,A22)+(A3-
INDEX(A22,MATCH(A3,A22)))/INDEX(A11,MATCH(A3,A22)+1))

Philosophy: If it so happens that the inventory is consumed exactly at
the end of a month then the formula returns exactly the number of
months. Otherwise the formula finds the last month that inventory was
not exceeded. Then it adds the ratio of unconsumed divided by the
forecast of the next month.

Does this help?
Kostis

 

[Theawilla]

Wow what a formula, I've been trying to work with this but while it works for
the first calculation when I copy it to the next week it is too low (I added
cells E1 and F1 at 100 each and changed the formula to compensate). My
answer comes out to 1.03 when it should be 2.09 for the B cell calculation.

 

[vezerid]

Hi, I just saw your reply. Do you care to send me your data and point
out where the formula is not giving you the expected result? You can
email me at
vezerid at act dot edu

Regards,
Kostis