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?
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%
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.
- Fit a line to all your data and use this line to find when the project will be 100% complete.
- 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:
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
Once you have these values, just use the formula below to get your projected finish date.
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:
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.
A third option requires only a single formula and does essentially what is done in Method 1 without the extra step.
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.