statistical graph showing percentages

[Sandy82]

I would like to produce a statistical graph in excel showing the percentage
of the duration of the project elapsed and the percentage of the tasks
completed, in comparison with the baseline.

 

[John]

I would like to produce a statistical graph in excel showing the percentage
of the duration of the project elapsed and the percentage of the tasks
completed, in comparison with the baseline.

Sandy82,
There are various ways to do it, but first a little better understanding
of your parameters is required. For example, are you looking for a set
of three data points for the whole project taken over time (i.e. 3 data
points as of today, 3 more as os next Monday, etc.), or are you trying
to plot 3 data points for each and every performance task? Also, what do
you mean by "baseline"? Project has several baseline fields. Are you
talking about baseline duration, baseline start, baseline finish, or
some other baseline?

Once the parameters are nailed down and appropriate data calculated (if
necessary), the data can be exported to Excel using an export map, a VBA
macro, or if you have Project 2007, visual reports.

Lots of options.

John
Project MVP

 

[Sandy82]

Forgive me if I was a little vague, I am a complete novice with Project. I
have to create a report or graph which shows, month on month cumulatively,
the percentage of tasks which have been completed in comparison to the
predicted task durations (baseline). There are no resources or costs to
consider.

 

[Sandy82]

Thank you for your reply John, I should add I have tried various methods of
acomplish this e.g. exporting to excel by way of the analysis timephased
option and the "save as xls" but I cannot seem to find the correct
information to imput to achieve the desired results. I would be very
grateful for any suggestions you may have as presently I am wading through 3
different MS Project Books and seem to be going round in circles.
Best regards
Sandy82

 

[John]

Forgive me if I was a little vague, I am a complete novice with Project. I
have to create a report or graph which shows, month on month cumulatively,
the percentage of tasks which have been completed in comparison to the
predicted task durations (baseline). There are no resources or costs to
consider.

Sandy82,
My first question is, how did those tasks get completed if there are no
resources assigned to do them? Pretty neat trick - you must be saving
tons of money on labor costs. Just an observation.

Getting a cumulative value of how many tasks have been completed each
month is pretty easy but I don't quite understand what you mean by
"predicted task durations" and how that relates to task completions.

In Project, duration is the difference in working time between the start
of a task and the finish of that task. That duration is only a
"baseline" if a baseline is set, otherwise that duration is the
scheduled duration and subject to change as the schedule is accelerated
or falls behind.

It sounds to me that perhaps what you want to do is to get a cumulative
value of how many tasks actually completed each month compared to how
many tasks were scheduled to finish during that month, and perhaps you
want to track the original finish dates (i.e. baseline) of each task.
For that, you need to set a baseline under Tools/Tracking/Save Baseline.
That will basically take a snapshot of various Project data fields and
store those values into separate defined baseline fields. However, if
you've already been messing around with the schedule such that dates are
changing, it is going to be pretty hard to establish baseline data that
was valid when the plan was first developed.

Well ok, let's say you did save a baseline, now what? You could create
two filters and then count the tasks that resulted when the filters were
applied. The first filter would find all tasks that are between month
start and month end and are 100% complete. The second filter would find
all tasks that have a baseline finish date between month start and month
end. To create those filters, do the following
1. Go to Project/Filtered for/More filters
2. Hit "new", then create the following filter - call it "Cmpl this
month", or whatever.
And/Or Field Name Test Value
Finish is greater than or equal to "Enter Month start"?
And Finish is less than "Enter Month end"?
And % Complete equals 100

uncheck the option to show related summary rows

3. Repeat step 2 and create a second filter as follows:
And/Or Field Name Test Value
Baseline Finish is greater than or equal to "Enter Month
start"?
And Baseline Finish is less than "Enter Month
end"?

uncheck the option to show related summary rows

4. Apply the first filter and count the tasks
5. Apply the second filter and count the tasks

Now here are some downsides to this simplistic metric. Counting task
completions tells you absolutely nothing about how the schedule is
going. You could have tons of unimportant tasks complete and the metric
would look great but you're really in deep yogurt. Similarly you might
have 5 tasks that were supposed to be complete this month and 5 tasks
that were complete this month - the metric looks great, except it won't
show if the 5 tasks that were complete were the 5 tasks that were
SUPPOSED to be completed.

John
Project MVP

 

[Sandy82]

Sandy82,
My first question is, how did those tasks get completed if there are no
resources assigned to do them? Pretty neat trick - you must be saving
tons of money on labor costs. Just an observation.

Getting a cumulative value of how many tasks have been completed each
month is pretty easy but I don't quite understand what you mean by
"predicted task durations" and how that relates to task completions.

In Project, duration is the difference in working time between the start
of a task and the finish of that task. That duration is only a
"baseline" if a baseline is set, otherwise that duration is the
scheduled duration and subject to change as the schedule is accelerated
or falls behind.

It sounds to me that perhaps what you want to do is to get a cumulative
value of how many tasks actually completed each month compared to how
many tasks were scheduled to finish during that month, and perhaps you
want to track the original finish dates (i.e. baseline) of each task.
For that, you need to set a baseline under Tools/Tracking/Save Baseline.
That will basically take a snapshot of various Project data fields and
store those values into separate defined baseline fields. However, if
you've already been messing around with the schedule such that dates are
changing, it is going to be pretty hard to establish baseline data that
was valid when the plan was first developed.

Well ok, let's say you did save a baseline, now what? You could create
two filters and then count the tasks that resulted when the filters were
applied. The first filter would find all tasks that are between month
start and month end and are 100% complete. The second filter would find
all tasks that have a baseline finish date between month start and month
end. To create those filters, do the following
1. Go to Project/Filtered for/More filters
2. Hit "new", then create the following filter - call it "Cmpl this
month", or whatever.
And/Or Field Name Test Value
Finish is greater than or equal to "Enter Month start"?
And Finish is less than "Enter Month end"?
And % Complete equals 100

uncheck the option to show related summary rows

3. Repeat step 2 and create a second filter as follows:
And/Or Field Name Test Value
Baseline Finish is greater than or equal to "Enter Month
start"?
And Baseline Finish is less than "Enter Month
end"?

uncheck the option to show related summary rows

4. Apply the first filter and count the tasks
5. Apply the second filter and count the tasks

Now here are some downsides to this simplistic metric. Counting task
completions tells you absolutely nothing about how the schedule is
going. You could have tons of unimportant tasks complete and the metric
would look great but you're really in deep yogurt. Similarly you might
have 5 tasks that were supposed to be complete this month and 5 tasks
that were complete this month - the metric looks great, except it won't
show if the 5 tasks that were complete were the 5 tasks that were
SUPPOSED to be completed.

John
Project MVP

Hi John
Thank you for your help, I have tried out your suggestion and it is a really
useful tool, however my manager would like me to investigate further options.
I realise the reporting criteria seems odd but basically, for one of our
KPIs this year, our Project Department has been asked to track the task
performance of 10 of their projects. The remit is to save a baseline and
ensure the actual task durations stay within 80% of the baseline task
durations (the idea is to track using a high level view). The status of the
tasks will be updated using “percent complete†in the “Task Informationâ€
option and I have to produce a chart or report that shows the total
percentage of any schedule variance from the actual tasks performed vs. the
baseline tasks on a monthly basis, for example a bar chart showing months
January to December showing baseline task durations vs. actual task
durations. I have tried using the Gantt Wizard choosing a customised Gantt
but can’t seem to achieve the desired results (the percentages show per
individual task). I do hope I have explained myself clearly, I am due to
attend intermediate and advanced MS Project training this year when hopefully
things will become clearer, but unfortunately my reports have to be produced
by the beginning of February so if you or any of the other experts have any
further suggestions I would be extremely grateful.

Best regards
Sandy82

 

[John]

Hi John
Thank you for your help, I have tried out your suggestion and it is a really
useful tool, however my manager would like me to investigate further options.
I realise the reporting criteria seems odd but basically, for one of our
KPIs this year, our Project Department has been asked to track the task
performance of 10 of their projects. The remit is to save a baseline and
ensure the actual task durations stay within 80% of the baseline task
durations (the idea is to track using a high level view). The status of the
tasks will be updated using “percent complete†in the “Task Informationâ€
option and I have to produce a chart or report that shows the total
percentage of any schedule variance from the actual tasks performed vs. the
baseline tasks on a monthly basis, for example a bar chart showing months
January to December showing baseline task durations vs. actual task
durations. I have tried using the Gantt Wizard choosing a customised Gantt
but can’t seem to achieve the desired results (the percentages show per
individual task). I do hope I have explained myself clearly, I am due to
attend intermediate and advanced MS Project training this year when hopefully
things will become clearer, but unfortunately my reports have to be produced
by the beginning of February so if you or any of the other experts have any
further suggestions I would be extremely grateful.

Best regards
Sandy82

SandyB2,
Sorry I didn't respond earlier - it's been a busy week.

You still didn't answer the basic question of how your task are getting
done without resources. It almost sounds like someone is "statusing" the
plan using a seat-of-the-pants approach (i.e. guessing on how complete a
task is using "gut feel").

OK, let me try this again. Unless you are using a level-of-effort type
of tracking, task duration has absolutely no relevance when it comes to
task progress. In Project, task duration is simply the difference in
working time between the start of a task and the finish of a task.
Duration accomplishes nothing - it is simply a measure of time. I take
that back - time does heal all wounds.

Saving a baseline will indeed capture the original duration estimate. If
I understand properly your process then strives to insure that durations
stay within 80% of the baseline value. But what changes the task
duration? Do you manually change the start and/or finish date? If so,
you do not have a dynamic schedule and you might as well do all this in
Excel. On the other hand perhaps you manually change the duration value.
In that case what do you do with the % Complete value because it will
need to change? Why? Let me give you an example. Let's say a task was
scheduled with an estimated duration of 10 days and a baseline was
saved. Then the task was statused as being 50% complete the first week.
The second week things didn't go so well so the duration was extended to
15 days. Notice that by doing so, the % Complete now falls back to 33%,
however, the task was already declared as being half done. Unless the %
Complete is re-adjusted back to at least 50%, the data is worthless and
the whole process falls apart.

I applaud you for getting set up for Project training, but to be honest,
if your management is directing you to manage projects using the method
you describe, it is probably they who need the training in project
management. Don't get me wrong, there are some very isolated cases where
a simplistic metric for tracking a project makes sense. I'm just not
convinced that yours is one of those cases.

John
Project MVP

 

[Sandy82]

John

Once again thanks again for your help, and understand from what you have
said that reporting purely by duration will not produce any useful measure of
how work is progressing and I will relay your comments to my Department,
although I think they are now aware the information they originally wanted
cannot be produced using the original criteria as all the other items I have
sourced have said the same as you.

From a personal point of view, work aside, I have found this week very
informative and have learnt a great deal from yourself and from other items
in the Discussion Groups which I probably wouldn't have if I hadn't been
asked to carry out this work and I am determined, for my own benefit, to get
to grips with as many aspects of MS Project as I can.

 

[John]

John

Once again thanks again for your help, and understand from what you have
said that reporting purely by duration will not produce any useful measure of
how work is progressing and I will relay your comments to my Department,
although I think they are now aware the information they originally wanted
cannot be produced using the original criteria as all the other items I have
sourced have said the same as you.

From a personal point of view, work aside, I have found this week very
informative and have learnt a great deal from yourself and from other items
in the Discussion Groups which I probably wouldn't have if I hadn't been
asked to carry out this work and I am determined, for my own benefit, to get
to grips with as many aspects of MS Project as I can.

SandyB2,
You're welcome and thanks for the feedback.

John

 

[John Garay - DecisionEdge]

Hi Sandy82,

I have been studying this thread trying to figure out what you wanted.

At first, I thought you wanted a chart that showed actual vs. baseline
progress, using something like number of tasks completed. For example, say
your project had 100 tasks spread evenly over 10 months (10 tasks finish per
month). This plan was saved as a baseline. A chart with just this baseline
information would show a baseline line go up 10% every month, until it
reaches 100% in month 10. Okay, now add an actual line - if you completed 8
tasks in month 1, 4 tasks in month 2 and 11 tasks in month 3, the actual
line would be 8%, 12%, and 23% through month 3. When compared to the
baseline line, you could definitely see things were progressing behind
schedule. Across multiple projects and/or in much more complicated
projects, I can see how this would be a nice overview of actual progress vs.
baseline, without going into resources or costs or anything...

However, I re-re-read the thread and realize you wanted to show progress
based on durations, not just the task finish event. This gets kinda tricky,
as Microsoft does not seem to expose time-phased task duration information
(it exposes time-phased resource, cost, and earned value information, but
not simply task duration information). This means it would be more
difficult to create a time-phased chart (i.e. one that shows progress v
baseline over time). However, doing a single value per project would be
easy - the formula would be: total actual durations + total remaining
durations / baseline duration. This single number would tell you
immediately whether a given project is trending towards a finish within
acceptable boundaries or not... You could create a multi-project report
that included this information (and other project-level numbers, if
desired). Would that help?

Anyways, if you're still interested, I'm willing to discuss further how to
get the charts/reports you what you need from Microsoft Project.

Regards, John Garay

 

[Sandy82]

Dear John

Many apologies for not responding sooner, I had reached a reasonable
solution to my promlem before your response and have only just revisited the
site with an excel question. I have read your answer which, had I done
earlier, would have saved me a great deal of time.

Many thanks for your time and once again, please accept my apolgies for my
slow response.