Rollup of custom duration fields doesn't account for dependencies?

[Spin-Dizzy]

Hi there! I am attempting to use Project to do a sort of Best Case / Average
Case / Worst case analysis using task durations. Basically, I am using the
default duration field to enter the number of hours each task will take in
the average case. I have created custom duration fields for best case and
worst case durations, and I have set their calculation to be "Sum", but I
have found out that it's really just a simple arithmetic sum - it doesn't
account for the fact that independent tasks can run in parallel.

For example, if I have two level-2 tasks, each without any predecessors,
each with a duration of 8 hours, the built-in duration field at level-1
summary correctly calculates to 8 hours because it understands that the tasks
can be done in parallel. Unfortunately, if I enter 8 hours for each task in
the custom duration fields, the level-1 summary shows 16 hours. Because of
this behavior, I cannot rely on the totals at the top of my project plan as a
means of comparing my average case total duration to my best and worst case
total durations.

Does anyone know how to create secondary (and tertiary, etc.) durations that
actually sum based on dependencies?

Any help would be greatly appreciated!

Spin

 

[Joe]

Look into using PERT. PERT uses Best case, most likely case, and worst case
scenarios withiut having to use custom fields.

 

[Spin-Dizzy]

Thanks Joe, that's an interesting avenue of pursuit and I will be looking
into it further to see if it will in fact meet my needs. My situation is
slightly different in that the alternate durations I am providing are in fact
not best and worst case durations per se...I just found that that was an easy
way to express the issue.

Basically, I am analyzing the difference between running certain tasks in a
fully automated production environment, running them in a hybrid
automated/manual environment, running them in a purely manual environment,
and also the historical durations of the previous time the tasks were
performed in their entirety. So the first problem that I see in using PERT is
that I require 3 alternate durations in addition to the "real" one. The
second issue I see is that even when first enter the "PERT Entry Form" view,
the summary line shows the summary value of 16h where it should be showing 8
just like the default duration field does.

Any further insight would be very welcome!

 

[Saratoga]

I know this is a MS Project discussion group but you may also want to
consider looking at other forcasting or Monte Carlo simulation tools such as
Crystal Ball or @Risk.

 

[Spin-Dizzy]

Interesting suggestion...unfortunately I'm in a corporate environment where
the tools are limited. I also looked into using PERT and it is not workable
since it is based on a set weighting of optimistic/pessimistic/expected
across the tasks where in my case I am physically entering precise durations
in four separate duration fields.

Can anyone suggest custom fields other than Duration 1-10 that might work
better for accumulating hours that take into account the dependencies in the
project? Will I really have to copy/paste the entire file into four separate
ones just to be able to use the "default" Duration field's dependency-based
aggregation four times? If I do need to do this, does anyone have any
suggestions on keeping four separate MPP files in sync with each other?

 

[John]

Interesting suggestion...unfortunately I'm in a corporate environment where
the tools are limited. I also looked into using PERT and it is not workable
since it is based on a set weighting of optimistic/pessimistic/expected
across the tasks where in my case I am physically entering precise durations
in four separate duration fields.

Can anyone suggest custom fields other than Duration 1-10 that might work
better for accumulating hours that take into account the dependencies in the
project? Will I really have to copy/paste the entire file into four separate
ones just to be able to use the "default" Duration field's dependency-based
aggregation four times? If I do need to do this, does anyone have any
suggestions on keeping four separate MPP files in sync with each other?

Spin-Dizzy,
I've read the thread to get the gist of what you need and maybe I can
help. Saratoga mentioned the use of a Monte Carlo simulation and
suggested a couple of applications. You responded that in your corporate
environment you may not be able to buy additional software. Fellow MVP,
Jack Dahlgren has a free VBA macro that does Monte Carlo simulation.
Although I never used Jack's macro, I took a quick look at the
description and it indicates that the user can independently enter
separate duration values for each task. You can find Jack's macro on his
website at: http://masamiki.com/project/blackjack.htm

As you noted a simple rollup formula for a spare fields at a summary
line does not give what you need. As an alternate, you could use VBA to
calculate summary line values for spare duration fields. And once you
get into the VBA world, you can do virtually anything you need.

John
Project MVP

 

[Spin-Dizzy]

Thanks John, first for investing time in trying to understand the issue, and
second for directing me to Jack's site - I will definitely take a look at the
macro and see if it will get me where I need to go.

Off the bat the only thing that I could see getting in the way of it working
properly is that no single duration column in my project file is consistently
higher or lower than another...i.e. depending on the task in question, the
duration of a task in durationA may actually be higher than the time in
durationB even though generally speaking, the durationA times are lower. So I
would have to figure out how to map my duration columns over to an
optimistic/expected/pessimistic structure.

As much as I have to just accept it, I'm actually still quite amazed that
custom duration columns don't roll up in the same way as the default duration
column does. Seems like a no-brainer to me that they should...if you want the
rollup to be dependency-based, use a custom duration column...if you want the
rollup to be a pure sum, use a custom number field. Wouldn't that make sense?
Is it just me?

(

 

[Spin-Dizzy]

Okay, so all the purists are going to shake their heads in disgust at this
one. I have gone with a 100% ridiculous workaround, but given the complexity
of solving the real issue, and the relative lack of complexity in the
parallelism of my project's tasks, it just makes sense for me rather than
continuing to beat my head against the wall.

I scanned my plan for where there were parallel tasks, added up the amount
of time saved by them not being done serially, and inserted an "adjustment"
task with a negative duration in the custom duration field(s).

As an example, let's say I have a group of level 3 tasks as follows:
Task A for 4 hours for default and custom durations
Task B for 3 hours for default and custom durations, and dependent on task A
Task C for 2 hours for default and custom durations, and dependent on task B
Task D for 20 hours for default and custom durations, and dependent on task A

My default duration field in the level-2 summary line would read 24 hours
duration.
My custom duration field in the level-2 summary line would read 29 hours
duration (for the record, I still think that only having "SUM" as a rollup
option is dumb and that there should also be a rollup option called
"Dependency SUM").

The level-3 "adjustment" task (call it task E) that I create therefore needs
to have 0 hours in its default duration, and -5h in it's custom duration
field, and voila...both summary values are correct at 24h.

Now, I know it's idiotic and I know that any time the dependencies or
durations on the tasks change, it could throw everything out of whack, but in
this particular case my 300 line plan only has 5 or 6 activities that are
parallel in nature, so the amount of calculation I need to do is relatively
limited. Beyond that, the entire plan is really just an intellectual exercise
to come up with a reasonable bottom-up estimate where only a "pie-in-the-sky"
top-down estimate existed before, and as soon as the estimate is vetted by
the rest of the team the plan will be archived and will never be used for
execution.

So thank you everyone for your help - I do appreciate the time you took to
try to help me through this issue!

 

[John]

Thanks John, first for investing time in trying to understand the issue, and
second for directing me to Jack's site - I will definitely take a look at the
macro and see if it will get me where I need to go.

Off the bat the only thing that I could see getting in the way of it working
properly is that no single duration column in my project file is consistently
higher or lower than another...i.e. depending on the task in question, the
duration of a task in durationA may actually be higher than the time in
durationB even though generally speaking, the durationA times are lower. So I
would have to figure out how to map my duration columns over to an
optimistic/expected/pessimistic structure.

As much as I have to just accept it, I'm actually still quite amazed that
custom duration columns don't roll up in the same way as the default duration
column does. Seems like a no-brainer to me that they should...if you want the
rollup to be dependency-based, use a custom duration column...if you want the
rollup to be a pure sum, use a custom number field. Wouldn't that make sense?
Is it just me?

(

Spin,
First of all you're welcome - I kind of like doing custom things with
Project, especially if it involves some VBA. Must be the geek in me ;-)

Second, let me address your complaint about spare fields. In Project,
spare fields are included for user convenience. First, they are static
(unless customized with a formula), and second, users may use them for
many different things. Therefore, it is virtually impossible to set them
up with the same dynamics as the standard fields that are used by
Project's scheduling engine. A limited amount of basic functionality was
added to summary limes for customized fields. One of them is of course
the sum of values in subtasks. I guess you could argue that schedule
dynamic based calculations would be very useful for certain spare fields
but then I haven't seen any other posts by users who also desired this
feature and most new features are added based on user needs.

Third, it sounds like you found a process that works for you and that's
great, but it does sound rather involved. Since you apparently only have
a very limited number of tasks that you need to run a "what-if" scenario
against, might another option be to replicate those tasks with the
alternate duration values in the normal Duration field. The alternate
scenarios could be linked into the main tasks with a simple addition or
deletion of a predecessor/successor.

John
Project MVP