×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Monte Carlo Simulation
6

Monte Carlo Simulation

Monte Carlo Simulation

(OP)
Hi all of you out there..

I am currently inverstigating putting together a little spreadsheet in Excel or similar that will do Monte Carlo simulation's for projects.

This means that you find the critical path of the project and input ideal, optimistic and pessimistic duration for each task and the spreadsheet will then 'simulate' any number of possible total project durations and give you a analysis at the end for example - project is 95% likely to take X days, etc.

Is this of any interest to anyone, has it been done before and is it worth my time?

Yours input and/or suggestions would be appreciated.

S

RE: Monte Carlo Simulation

3
(OP)
I have now finished a preliminary version of the spreadsheet so if anyone is interested, it can be found at:

www.jimstemple.com/mcsim.zip

S

RE: Monte Carlo Simulation

I think the simulator is missing so i cannot test it. Can you attach that file as well?
Thanks

RE: Monte Carlo Simulation

I still think that the simulator module is missing (ATPVBAEN.XLA). I get the error message that this noted file cannot be found.
Please advise.
Thank you

RE: Monte Carlo Simulation

(OP)
Aaah, I see your problem..

To use the simulator you have to have the Data Anlysis Add-In installed..

Go to Tools - Add-Ins and Select Analysis Toolpack and Analysis Toolpack VBA and install these, the sheet uses the random number generation function to 'simulate the project'.

Hope this helps..

Any more problems, let me know.

S

RE: Monte Carlo Simulation

How cool is that!! Great stuff.

Now I can prove that I deserve my pay rise!!

RE: Monte Carlo Simulation

Great help from simmantix.

I think that this could be useful as one of many tools a project manager can use in order to improve plans and exucution of the plans.

I will use it in other aspects also such as anlyse of impact for identified risks etc.

RE: Monte Carlo Simulation

Now it works. However, I have one more question. Does the routine pick numbers on a totally random pattern constrained by the edge values (optimistic and pessimistic)? Or does it use some kind of probability distribution (like uniform or normal)? Do you know where can I find more information on how the function works?
I have never used the Analysis-Toolpak before and it seems there must be some interesting stuff in there.

RE: Monte Carlo Simulation

(OP)
If you go into the toolpack and have a llook at the random number generator then you will see that there is a variety of distribution's available. I have chosen normal distribution for the spreadsheet though a Beta is sometimes used.

To change this is simply to change the number in the argument of the number generator in the sheet macro, it is set at 2 = normal now but you can change this integer to another to use a different distribution. Don't know how this would affect the sheet though.. It would probably not work without some firther work.

S

RE: Monte Carlo Simulation

Neat program. Thanks for your help.

RE: Monte Carlo Simulation

Simmantix,

Nice product.  Based on your knowlege of the technique, can I use the same program for cost data to achieve a Monte Carlo simulation of my project budget?

RE: Monte Carlo Simulation

(OP)
I don't see why not..

I have just done a quick test and you can use the spreadsheet as above but where it says duration, days etc, just imagine it says optimistic cost, ideal cost, etc.

If you do have any problems then send me an e-mail @

simmantix@jimstemple.com

with your problem and I will adjust the code to suit. The only problems I can foresee are integer and value designation problems but, if they occur, they are easily fixed..

Regards

Sim

RE: Monte Carlo Simulation

(OP)
Better than that, I have changed the code and included a costing sim sheet in the download from the website:

www.jimstemple.com/mcsim.zip

So you can now do critical path simulation or project budget simulation..

Hope this helps..

Sim

RE: Monte Carlo Simulation

2
I see two assumptions that might not hold.

1)    The variation in task duration may change the critical path. One would have to see if a second path s near critical in duration and run the simulation for this second path to see if there is a change in critical path.
2)    The variation in any one task is independent of the variation in all other tasks. This might not be true for example if the critical path is weather dependant i.e. (site and earth work) or if one sub contractor is fast or slow on all tasks. (i.e. if the concrete crew in slow in erecting the forms they may be slow in stripping the forms.) This could be accounted for by grouping these tasks into one task.


Rick Kitson MBA P.Eng

Construction Project Management
From conception to completion
www.kitsonengineering.com

RE: Monte Carlo Simulation

There's a UK company called Risk Decisions Ltd who market a couple of commercial products ("PREDICT! Risk analyser" and "PREDICT! Risk controller") which do this sort of thing.

Both offer a limited compatibility with MS Project (that is you can import from MSP at the outset, but neither export data back, or import plan updates).

Over the last three or four years, I've used both.  Risk Analyser is basic Monte-Carlo.  Risk Controller starts to integrate a risk register, and has a slightly friendlier interface.

Advice from bitter experience for what it's worth, is to be very careful with the results from Monte-Carlo simulations.  It's easy to lose sight of the simplifying assumptions in all the excitement of a vast simulation.  A common trouble area is that a triangular distribution based on three point estimates is actually quite a poor model of task duration (the same may not be quite so true for cost).  Most real distributions are reasonably tight, with one or two smaller peaks off to the right somewhere.  The difference, once fed into a simulation is remarkable.

ANM.

RE: Monte Carlo Simulation

wanted to try out the simulation pack. Is it still on at the site or pulled off! Thanx

RE: Monte Carlo Simulation

About 20 years ago I wrote a PERT-SIM programme in BASIC.  I allowed any of three statistical distributions (Triangular, Beta or Normal) to be assigned to any activity in a project.  I found it very useful on projects with no more than 100 activities.  However I found that the Customer and many of my co-workers were very anti having to come up with Op, Ml and Pe estimates for any one activity.  Talk to people as many times as you consider necessary until you achieve the range of inputs you want.  Just going in cold does tend to put people off-side.  I researched, privately, simulated PERT routines and found that running the simulation 10 000 times provided the right sort of results.  The customer was very skeptical about the results. The Customer just said "I want this activity to take X days and thats how long you have"!

bwst of luck with your simulations.

RE: Monte Carlo Simulation

Any chance to repost this simulation for current download? The posted domain has expired since original posting.

Best Regards,

RE: Monte Carlo Simulation

(OP)
Dear all,

After I authored the program, I uploaded it to my website..

I am afraid that my website is no more..

If you would like the program then please contact me on:

james@elsl.co.uk

Thanks for your interest..

Simmantix..

Simmantix
---------
Phases of a Project:
Exultation, Disenchantment, Search for the Guilty, Punishment of the Innocent, Praise for the Uninvolved...

RE: Monte Carlo Simulation

simmantix, I have not looked at the Excel program that you have authored, but it seems that you are describing a PERT calculation and not a Monte Carlo calculation (which requires iterative schedule calculations through the logic network).  In any event, I thought the following excerpt might be instructive for the unfamiliar:

    11.2 Scheduling with Uncertain Durations

    The most common formal approach to incorporate uncertainty in the scheduling process is to apply the critical path scheduling process ... and then analyze the results from a probabilistic perspective. This process is usually referred to as the PERT scheduling or evaluation method. ... the duration of the critical path represents the minimum time required to complete the project. Using expected activity durations and critical path scheduling, a critical path of activities can be identified. This critical path is then used to analyze the duration of the project incorporating the uncertainty of the activity durations along the critical path. The expected project duration is equal to the sum of the expected durations of the activities along the critical path. Assuming that activity durations are independent random variables, the variance or variation in the duration of this critical path is calculated as the sum of the variances along the critical path. With the mean and variance of the identified critical path known, the distribution of activity durations can also be computed.

    ...

    While the PERT method has been made widely available, it suffers from three major problems. First, the procedure focuses upon a single critical path, when many paths might become critical due to random fluctuations. For example, suppose that the critical path with longest expected time happened to be completed early. Unfortunately, this does not necessarily mean that the project is completed early since another path or sequence of activities might take longer. Similarly, a longer than expected duration for an activity not on the critical path might result in that activity suddenly becoming critical. As a result of the focus on only a single path, the PERT method typically underestimates the actual project duration.

    As a second problem with the PERT procedure, it is incorrect to assume that most construction activity durations are independent random variables. In practice, durations are correlated with one another. For example, if problems are encountered in the delivery of concrete for a project, this problem is likely to influence the expected duration of numerous activities involving concrete pours on a project. Positive correlations of this type between activity durations imply that the PERT method underestimates the variance of the critical path and thereby produces over-optimistic expectations of the probability of meeting a particular project completion deadline.

    Finally, the PERT method requires three duration estimates for each activity rather than the single estimate developed for critical path scheduling. Thus, the difficulty and labor of estimating activity characteristics is multiplied threefold.

    As an alternative to the PERT procedure, a straightforward method of obtaining information about the distribution of project completion times (as well as other schedule information) is through the use of Monte Carlo simulation. This technique calculates sets of artificial (but realistic) activity duration times and then applies a deterministic scheduling procedure to each set of durations. Numerous calculations are required in this process since simulated activity durations must be calculated and the scheduling procedure applied many times. For realistic project networks, 40 to 1,000 separate sets of activity durations might be used in a single scheduling simulation. ...

    ...

    The disadvantage of Monte Carlo simulation results from the additional information about activity durations that is required and the computational effort involved in numerous scheduling applications for each set of simulated durations. For each activity, the distribution of possible durations as well as the parameters of this distribution must be specified. For example, durations might be assumed or estimated to be uniformly distributed between a lower and upper value. In addition, correlations between activity durations should be specified. For example, if two activities involve assembling forms in different locations and at different times for a project, then the time required for each activity is likely to be closely related. If the forms pose some problems, then assembling them on both occasions might take longer than expected. This is an example of a positive correlation in activity times. In application, such correlations are commonly ignored, leading to errors in results. As a final problem and discouragement, easy to use software systems for Monte Carlo simulation of project schedules are not generally available. This is particularly the case when correlations between activity durations are desired.


Project Management for Construction by Chris Hendrickson, Department of Civil and Environmental Engineering, Carnegie Mellon University

http://www.ce.cmu.edu/pmbook/11_Advanced_Scheduling_Tec...

Bernard Ertl
www.interplansystems.com
 - eTaskMaker Project Planning Software
 - ATC Professional Turnaround Management Software

RE: Monte Carlo Simulation

(OP)
If anyone still wants this I will put it on a new website??

Anyone interested?

Jim

Simmantix
---------
Phases of a Project:
Exultation, Disenchantment, Search for the Guilty, Punishment of the Innocent, Praise for the Uninvolved...

RE: Monte Carlo Simulation

Another useful commercial product:

D-Risk from Frazer-Nash Consulting

www.fnc.co.uk/downloads/Drisk/DRisk%20Info%20Sheet%20v1.pdf

This one's only been on the market since July, so I guess its stability isn't as well proven as some of the alternatives - nonetheless, I haven't seen any real problems in the several months I've been playing.

Comes as a straight plugin to MS Project, with output direct to MSP too, so fairly painless to use.  I quite like it.

A.

RE: Monte Carlo Simulation

simmantix, sure, I'll bite

TTFN

RE: Monte Carlo Simulation

(OP)
OK then.. You should find everything you need here:

http://www.elsl.co.uk/assets/files/mcsim.zip

Please do me the kindness of posting or e-mailing me if you take a copy so I can issue updates if neccessary..

Thanks

Jim

Simmantix
---------
Phases of a Project:
Exultation, Disenchantment, Search for the Guilty, Punishment of the Innocent, Praise for the Uninvolved...

RE: Monte Carlo Simulation

and no Alex Trebek?

TTFN

RE: Monte Carlo Simulation

(OP)
sorry??

Simmantix
---------
Phases of a Project:
Exultation, Disenchantment, Search for the Guilty, Punishment of the Innocent, Praise for the Uninvolved...

RE: Monte Carlo Simulation

Who is the famous Canadian host of a popular game show?

TTFN

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources