Formula on Actual Vs Plan

Hi Friends!
Could some help me on this formula?
I need to complete a task say from 1-Jun-05 to 30-Jun-05 i.e 30 days,
but however on a given day when I analyse the Project schedule (say
today), I find out that on the 9th day i.e 9-Jun-05 on actual progress
I have completed only 4% of the Job, Hence what should be my forecasted
days to complete the job a 100% in actual, now putting it in a
mathematical sense:
100% = 30days (Plan)
4% = 9days (Actual & when the plan is 30%)
Hence 96% = ? (How may days in Forecast)

I want the formula to be dynamic, such that on the 15 day if I catch up
with my plan I will be 50% in Actual but if on the 28th day I am still
say 65% in Actual then what would be the forcasted days or total days
to still complete my work a 100% in actual

Any help would be very appreciated
Regards
Edward

Try this
Cell G1 contains the project start date

A2 contains the current date, B2 has the current completion %.
C2 contains =+(100/B2)*(A2-G$2) which will return total project days at current rate
=+G$2+C2 then shows forecast completion date
=+C2-(A2-G$2) will show number of days left befre completion (at current rate)

Hope this helps

If I understand you correctly, one way:

If A1 = start date, and B1 = %complete, then if 4% complete after 9
days, the total job will be 225 days (9/4%), with 216 days remaining:

=(TODAY()-A1+1)*(1/B1-1)






In article <[email protected]>,
"Eddy" <[email protected]> wrote:

> I need to complete a task say from 1-Jun-05 to 30-Jun-05 i.e 30 days,
> but however on a given day when I analyse the Project schedule (say
> today), I find out that on the 9th day i.e 9-Jun-05 on actual progress
> I have completed only 4% of the Job, Hence what should be my forecasted
> days to complete the job a 100% in actual, now putting it in a
> mathematical sense:
> 100% = 30days (Plan)
> 4% = 9days (Actual & when the plan is 30%)
> Hence 96% = ? (How may days in Forecast)
>
> I want the formula to be dynamic, such that on the 15 day if I catch up
> with my plan I will be 50% in Actual but if on the 28th day I am still
> say 65% in Actual then what would be the forcasted days or total days
> to still complete my work a 100% in actual

Alex Delamain wrote:
> Try this
> Cell G1 contains the project start date
>
> A2 contains the current date, B2 has the current completion %.
> C2 contains =+(100/B2)*(A2-G$2) which will return total project days at
> current rate
> =+G$2+C2 then shows forecast completion date
> =+C2-(A2-G$2) will show number of days left befre completion (at
> current rate)
>
> Hope this helps
>
>
> --
> Alex Delamain

Thanks for your replies, but I think you all are missing the point. I
think you are ignoring either the Finish date or the Plan percentage
completion. I would appreciate if you took this into consideration,
Edward
> ------------------------------------------------------------------------
> Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
> View this thread: http://www.excelforum.com/showthread...hreadid=377692

Then you need to be more explicit. The answers you got were pretty
consistent with the amount of information you gave.

To use your example, with a 30 day plan, if you find you're 4% done on
day 9, how should one factor in the Finish date or plan percentage in
calculating "forcasted days" or total days to complete?

Seems to me that if you're 4% done after 9 days, you're completing 4/9%
per day, and it will take you 24*9 (216) additional days to finish, no
matter what the plan said.


In article <[email protected]>,
"Eddy" <[email protected]> wrote:

> Thanks for your replies, but I think you all are missing the point. I
> think you are ignoring either the Finish date or the Plan percentage
> completion. I would appreciate if you took this into consideration,
> Edward

How is the percentage of completion being determined? Is it manually
entered or computed using a formula?

Jerry
"Eddy" <[email protected]> wrote in message
news:[email protected]...
> Hi Friends!
> Could some help me on this formula?
> I need to complete a task say from 1-Jun-05 to 30-Jun-05 i.e 30 days,
> but however on a given day when I analyse the Project schedule (say
> today), I find out that on the 9th day i.e 9-Jun-05 on actual progress
> I have completed only 4% of the Job, Hence what should be my forecasted
> days to complete the job a 100% in actual, now putting it in a
> mathematical sense:
> 100% = 30days (Plan)
> 4% = 9days (Actual & when the plan is 30%)
> Hence 96% = ? (How may days in Forecast)
>
> I want the formula to be dynamic, such that on the 15 day if I catch up
> with my plan I will be 50% in Actual but if on the 28th day I am still
> say 65% in Actual then what would be the forcasted days or total days
> to still complete my work a 100% in actual
>
> Any help would be very appreciated
> Regards
> Edward
>