Forecast completion date from original plan and actual work done

I have a table of values showing a time period ( with start and finish dates) and corresponding cumulative % values. ( THIS IS THE PLAN)

Now I have an actual % value at a particular date.
I need to forecast completion date based on the actual % and plan.
I have manually calculated the result as shown in attachment.


Thanks in advance.

Hi

I do not know which calculus you have done. The result in my approach is not the same as yours.
Try this
In column M from M2 use this formula and drag down

Formula:

Please Login or Register  to view this content.

In I4 use this formula to get the forecast completion date

Formula:

Please Login or Register  to view this content.

Note: M6=0

Hi Jose Augusto,

Thanks for the reply.
My approach was different
I was calculating the % value closest to the actual available on the plan..
Then adjusting the duration for the difference..
And from that point, continuing the distribution as per original plan..
Your approach also gives almost the same results...I have to test it further and then will let you know..
Could you please explain what is calculated in column M?

Hi

Sorry for the late answer.

The M2 value is the result of the difference between the actual value verified (20%) and the estimated value planned in K2, that is, on March 1 the estimated value in the plan was FORECAST.ETS(K2,$D$2:$D$5,$C$2:$C$5), approximately 60.47% of which only 20% is performed: 20% -60% = -40%. The formula does the same for the other dates , based on the initial plan.

Hi Bo_Ry,

Thanks for your reply.
I think there should be a modification in the formula because it will not work unless the lookup % is first value in the table.
Can you modify the formula accordingly?

Hi Bo_Ry,

Please find attached.

As of 22-Mar-18, if actual is 85% it means that work is progressing exactly as per plan
So the completion date should be same as in plan

Hi Bo_Ry,

I managed to get a working formula but its very long and not elegant.
Hope you can help..

Formula:

Please Login or Register  to view this content.

Hi

For my formula to work, I need to have the start date associated with the 0% value run in the same column as the other end-of-period dates. I made that adaptation.
The formula that I propose, determines the date of completion of the project according to a date and a percentage of the executed, taking into account what was planned.
See if this serves you.

Formula:

Please Login or Register  to view this content.

Hi Jose Augusto,

Thank u so much..this works perfectly..
Brilliant concept used in the formula..

You are welcome.

Do not forget to mark this thread as SOLVED.

Thanks.