Forecast a date from data given

[Jelewis72076]

Hello, any help with this would be more than appreciated.
I need my spreadsheet to give me a date when I will run out of parts.

A2: todays date
C4 - J4 has my weekly requirements
L4- has my parts on hand

I need to find out based on my weekly requirements when I will be out of
parts.

Looks something like this.

A2
(today date)
c4 d4 e4 f4 g4 h4 i4 j 4 L4
m4
wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8
20 20 20 20 20 20 20 20 73
(Need parts date)

Any help with a formula to calculate this would, again, be more than
appreciated. I've been racking my brain for a week on this one.

Thanks,

James

 

[Pete_UK]

Bit of line wrap on the posting. Do you mean that you want to know in
M4 the projected date when you will have used up all your parts in
hand (L4)? Does week 1 relate to today's date in A2?

Pete

 

[Pete_UK]

Your example shows the same weekly usage throughout. Assuming this to
be the case, put this formula in M4:

=A2+INT(L4/C4*5)

Format as a date. This assumes a 5-day week - change to 7 if you wish.

Hope this helps.

Pete

 

[Jelewis72076]

Thanks Pete,
I think you are on the right track. I tried tweaking that a little bit but
not getting exactly what I need. My example did show the same number but
they are actually different. Here is an actual line copy of my header and
one of my part lines.

Weekly Requirements

1-Apr-08 8-Apr-08 15-Apr-08 22-Apr-08 29-Apr-08 6-May-08 13-May-08 20-M
223 550 715 715 715 605 770 495

This is in C4-J4. In L4 (not shown) is my parts on hand. In M4 I would
like the actual date or week (from those above) I run out of parts. In this
example I have 1093 parts on hand. Therefore I would run out of parts in the
15-apr-08 week. Thus I must make more during the 8-apr-08 week. I know that
because I'm giving weekly requirements instead of daily requirements it makes
it difficult to pinpoint a date but I am thinking that by referencing todays
date in cell A2 it will ballpark it for me.

any suggestions on how to make this work will help. Thanks

James

 

[Pete_UK]

Not sure if you are still monitoring this, but you could set up a user-
defined function to do this. Essentially, add the weekly usage each
week until it exceeds the amount in hand, and thus identify the number
of complete weeks and the number of days by simple interpolation
between the two final amounts in that sequence.

Hope this helps.

Pete