Forecasted Project Progress

Gabriel Arce

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 

 

Paul Newcome

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.

Gabriel Arce

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?

 

Paul Newcome

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?

Gabriel Arce

Paul,

Prefer Day # (but I assumed I could move from one to the other in a simple manner)

Paul Newcome

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"?

Gabriel Arce

Including screenshot for project.

 

Paul Newcome

Excellent. This will most certainly help.

 

Will you be using this as a template of sorts, or will the dates already be populated?

Gabriel Arce

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)

Paul Newcome

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.

Gabriel Arce

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. 

Gabriel Arce

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!

Paul Newcome

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 Newcome

Happy to help. 

Travis Dykes

Paul, would it be possible for you to share your example as a file? I'm not following the Day 1, Day 2 column.

Marie_T

@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!