How to extrapolate or predict a project completion date

Posted on

QUESTION :

I have a few data points consisting of a percentage completed and a date. I would like to be able to predict the expected completion date based on the progress made so far.

If this cannot simply be done in excel is there a different application to do this?

For example:-

With a few data points like the following how can I tell when the 100% completion is expected.

09/19 0.0% 
09/20 2.0%
09/24 7.0%
09/29 9.0%
10/01 9.0%
10/04 9.0%
10/07 13.0%
10/08 15.0%
10/09 16.0%
10/16 21.0%
10/17 24.0%
10/22 30.0%
10/24 38.0%
10/31 48.0%
11/05 51.0%

ANSWER :

Doing a projection like this requires you to have some sort of model of how the data is expected to behave, e.g. linearly or exponentially. If you are unsure, it may be helpful to create a scatter plot of your data points and observe any trend that may be underlying your data. Doing this for the data you provided shows a pretty clear linear trend.

Here are two options for projecting a linear trend to the completion point.

  1. Fit a line to all your data and use this line to find when the project will be 100% complete.
  2. Use only your start and end data points to make a quick-and-dirty linear model and use it to find the 100% complete date.

Method 1 uses Excel’s linear trend fitting function LINEST, which returns the slope and y-intercept for the line fitting given data. To use LINEST, select two cells in adjacent columns (D2:E2 in my example) and paste the following into the formula bar:

=LINEST(B1:B15,A1:A15,TRUE,FALSE)

where B1:B15 are the percents, and A1:A15 are the dates. Press Ctrl+Shift+Enter to enter this formula. If done correctly, the formula will show up wrapped in curly brackets and the slope value will be in D2 and the y-intercept will be in E2.

Once you have these values, just use the formula below to get your projected finish date.

=TEXT((1-E2)/D2,"mm/dd")

Method 2 is a little less technical and probably a little less accurate since it only uses the endpoints of your data. But for data that really follows a linear trend, it should work fairly well.

All you need to do for this method is enter the following formula on your sheet to find the projected finish date:

=TEXT((A15-A1)/(B15-B1)+A1,"mm/dd")

where A1:B1 is your first data point and A15:B15 is your last data point.

As shown below, these methods project dates within 3 days of each other.

enter image description here

EDIT:
A third option requires only a single formula and does essentially what is done in Method 1 without the extra step.

=TEXT(TREND(A1:A15,B1:B15,1,TRUE),"mm/dd")

For some reason this returns 12/19 instead of 12/23, but it should work just as well for a projection.

If I’m not mistaken then you have spanned 47 days and completed 51% which equates to an average of 1.085106382978723% per day.

100% / 1.085106382978723% = 92.15686274509807 days

So essentially 9/19 + ~92 days

You will have to perform this calculation every time you enter a new date and percentage because it needs to recalculate every time.

Leave a Reply

Your email address will not be published. Required fields are marked *