Forecasted Project Progress
I would like to be able to create a graph that show forecasted percent complete over time (assuming activities are completed on time). This is daily and in the future. I assume this could be a report
Something like:
- Day 1 Projected Earned Value = 0%
- Day 2 Projected Earned Value = 0%
- Day 3 Projected Earned Value = 0%
- Day 4 Projected Earned Value = 3%
- Day 5 Projected Earned Value = 3%
- ....
- Day 16 Projected Earned Value = 40%
- ...
- Day 92 Projected Earned Value = 99%
- Day 93 Projected Earned Value = 99%
- Day 94 Projected Earned Value = 100%
Projected Earned Value is calculated through a formula that accounts for deliverable (task item) completion. Not all activities produce earned value.
Any idea on how to accomplish this? I couldn't figure this out even using %Complete functionality
Comments
-
You would either need to establish dates for Day 1, Day 2, Day 3, etc..., or you would need to assign Day 1, Day 2, Day 3, etc... to each of the deliverables.
You could then leverage that to count how many deliverables should be completed by each date or day depending on how you set it up and divide that by the total number of deliverables.
I use this quite frequently (except on a weekly schedule). I have my Projected in one column and my Actual in another. I then use a Line Graph on a dashboard to show the comparison.
-
Paul,
Thanks for your answer. Sorry, but I dont understand your explanation
I have regular schedule with task of different durations, dependencies, etc
How can I create a (table) view to see expected %complete per day?
-
How were you wanting to display the result?
Day 1
Day 2
Day 3
.
or.
.
3/1/19
3/2/19
3/3/19
.
Do you want it to be displayed as "Day #" or as an actual date?
-
Paul,
Prefer Day # (but I assumed I could move from one to the other in a simple manner)
-
It would be a pretty simple manner. Will the dates already be populated? Will this be in a template type of sheet that gets used for multiple projects, or just this one time?
Are you able to provide a few screenshots with sensitive/confidential data removed or replaced with "dummy data"?
-
Including screenshot for project.
-
Excellent. This will most certainly help.
Will you be using this as a template of sorts, or will the dates already be populated?
-
I really wanted to understand how to accomplish what I mentioned for other projects with different tasks/dependencies/durations/etc. The projects I want to use this on will have all tasks, etc
I thought there would be a report or some automated (or even semi-automated way)
-
It is automated once everything is set up.
What you will need to do is create 4 new columns. For this example I will assume we are basing the percentages off of the Finish Dates, and I will use for the column names:
[Day Number] (text/number)
[Date of Day] (date)
[Projected Percent Complete] (text/number formatted as a percentage)
[Actual Percent Complete] (text/number formatted as a percentage)
.
In the [Day Number] column is where you will enter Day 1, Day 2, Day 3, etc...
.
In the [Date of Day] column you will enter
=SMALL([Projected Finish Date]:[Projected Finish Date], VALUE(SUBSTITUTE([Day Number]@row, "Day ", "")))
.
In [Projected Percent Complete], enter this:
=COUNTIFS([Projected Finish Date]:[Projected Finish Date], AND(ISDATE(@cell), @cell <= [Date of Day]@row)) / COUNTIFS([Projected Finish Date]:[Projected Finish Date], ISDATE(@cell))
.
Finally, in the [Actual Percent Complete] column, you would use
=COUNTIFS([Actual Finish Date]:[Actual Finish Date], AND(ISDATE(@cell), @cell <= [Date of Day]@row)) / COUNTIFS([Projected Finish Date]:[Projected Finish Date], ISDATE(@cell))
.
Dragfill the formulas down, and you will have your baseline established right next to your actuals which can then be used in a graph on a widget which would look something like the attached image.
-
This is all on the same sheet?
So I would have the task start and finish with their plan dates and next there will be different date ?
For clarity, my tasks almost all last more than one day.
-
Paul,
Really appreciate it
Thanks, I think I figured it out and adjusted for my purposes. I am using another way of calculating % complete based on deliverables completed (where only some activities contribute)
Thanks!
-
This is all on the same sheet?
Correct
.
So I would have the task start and finish with their plan dates and next there will be different date ?
No. The formulas look at the dates that are already established whether planned or actual and works from those. The [Date of Day] column simply reflects these dates in an organized manner without repeating any dates to be able to provide a timeline.
.
For clarity, my tasks almost all last more than one day.
Yes. These calculations are based on the completion dates.
-
-
Paul, would it be possible for you to share your example as a file? I'm not following the Day 1, Day 2 column.
-
@Paul Newcome - I created the same Baseline Start and Finish and Actual Start and Finish formulas. It worked really well.
How would you add sub-tasks and incorporate the weighted average into the [Projected Percentage Complete] and [Actual Percentage Complete] columns?
Hope to hear from you soon. Thank you in advance for your assistance!
