Earned Value Graph

[Paul]

I need to show % complete from now through project completion for a 700-task
Project schedule. How can I create such a graph? Is there a way to do this
in Project? Do I need to transfer the data to Excel? If so, what do I do
with it in Excel to get future % complete values?

Please Help

 

[John]

I need to show % complete from now through project completion for a 700-task
Project schedule. How can I create such a graph? Is there a way to do this
in Project? Do I need to transfer the data to Excel? If so, what do I do
with it in Excel to get future % complete values?

Please Help

Paul,
Hey, where'd you get your time machine? I'm sure there are a whole bunch
of Project Managers out there who would love to know how to get future %
complete values.

Crazy as it may sound I actually did something similar some years ago. I
didn't plot a graph but I did create an algorithm that made some
progress predictions based on recent history and likely trends. But even
with some good assumptions, predicting what is likely to happen is
pretty much limited to the near future. If you intend on predicting
outcome through project completion, it is pure crystal ball.

As far a graphing, Project is very limited - it can only show a limited
set of graphs for resource data. That said, to create any other kind of
graph of Project data, yes, you will have to export it to Excel (or some
similar application with graphing capability).

Hope this helps.
John
Project MVP

 

[Paul]

John,
No time machine. (although if you find one, let me know, I have some stocks
I'd like to go back and invest in) But every task has an expected completion
date and a duration. I would think there would be a way to convert that info
into a reasonable schedule. It sounds like I need to export into Excel, but
once I get it there, then what? Whatever I do has to be repeatable. My boss
will want one twice a week.

Paul

 

[JackD]

Have you tried the "Analyze timescaled data in excel" add-in that comes with
Project? It should export what you need to excel and you can then chart
away.

If you want something simpler you can write some VBA code. The following
requires that you have resources assigned to each task and that the resource
has a cost associated with it. If you do not have a resource loaded
schedule, you can simply make a dummy resource with a cost of 1 and assign
it to all tasks. Then baseline the project and run this macro. It exports
into a .csv file that can be opened with excel



MyFile = "c:\" & "HardwareBreakout" & ActiveProject.Name & "_" & Date$ &
".csv"
fnum = FreeFile()
Open MyFile For Output As fnum
mystring = ActiveProject.Name & " " & ActiveProject.LastSaveDate & " " &
Application.UserName
Write #fnum, mystring 'write project info
Write #fnum, ' Write blank line.
jPeriod = 7 'select time reporting period 1 = 1 day, 7 = 1 week
jStart = ActiveProject.ProjectSummaryTask.Start
jEnd = ActiveProject.ProjectSummaryTask.Finish
jStatus = jStart
myBCWS = 0
myACWP = 0

Do While jStatus <= jEnd + jPeriod
ActiveProject.StatusDate = jStatus
'CalculateProject
myBCWS = 0
myACWP = 0
For Each myTask In ActiveProject.Tasks
If (Not myTask Is Nothing) And (Not myTask.summary) Then
myBCWS = myBCWS + myTask.BCWS
myACWP = myACWP + myTask.ACWP
End If
Next myTask
mystring = jStatus & ", " & myBCWS & ", " & myACWP
Write #fnum, mystring 'write project info
jStatus = jStatus + jPeriod
Loop
Close #fnum
End Sub

 

[John]

John,
No time machine. (although if you find one, let me know, I have some stocks
I'd like to go back and invest in) But every task has an expected completion
date and a duration. I would think there would be a way to convert that info
into a reasonable schedule. It sounds like I need to export into Excel, but
once I get it there, then what? Whatever I do has to be repeatable. My boss
will want one twice a week.

Paul

Paul,
Well, what you describe is the classic building block of a schedule
plan. Each task that is required to complete the plan has a start date
and estimated duration. The start date may be independent of other tasks
or dependent. In the latter case, tasks are logically linked to show the
flow of task progression. Project then takes the start dates, estimated
duration and intertask linkages and creates a schedule. So it sounds
like you have the elements of a schedule. What I'm having trouble
understanding is the concept of future % complete. In Project, %
complete is used to describe what has happened as of some point in time
with respect to working the original plan. Therefore, % complete values
are only available through that point in time, usually referred to as
the Status Date. "Future" % complete is speculative.

Now either I don't understand what you are after or you are asking for
something that isn't available. Jack supplied a couple of methods for
exporting data to Excel where it can be plotted, but again, that plot
will only describe performance through the Status Date.

Still confused.
John
Project MVP

 

[JackD]

I took it to mean planned percent complete. AKA BCWS.

 

[John]

I took it to mean planned percent complete. AKA BCWS.

Jack,
OK, now that makes some sense. Hopefully Paul will confirm your
assumption.

John

 

[Paul]

Yes BCWS. Visual Basic is something I have zero familiarity with, so I plan
to try the "Analyze timescale data in excel".
I also tried exporting the data to Excel, assigning each task a value of
(task duration / sum of all task durations) ordering the tasks by schedule
finish date, then graphing cumulative value from 0 to 100%. This produces a
usable graph, and I will probably go with that unless the "timescale data"
thing produces something better.

Thanks,
Paul

 

[JackD]

If you have set a baseline then the analyze timescale data allows you to
plot BCWS at whatever granularity you want (day, week, month). This should
be more satisfactory than doing it the way you are doing it in excel.