Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations KootK on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Monte Carlo Simulation 6

Status
Not open for further replies.

simmantix

Nuclear
Apr 30, 2003
31
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
 
Replies continue below

Recommended for you

I think the simulator is missing so i cannot test it. Can you attach that file as well?
Thanks
 
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
 
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
 
How cool is that!! Great stuff.

Now I can prove that I deserve my pay rise!!
 
Great help from simmantix.[thumbsup2]

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.



 
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.

 
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
 
Neat program. Thanks for your help.
 
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?
 
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
 
Better than that, I have changed the code and included a costing sim sheet in the download from the website:


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

Hope this helps..

Sim
 
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
 
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.
 
wanted to try out the simulation pack. Is it still on at the site or pulled off! Thanx
 
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.
 
Any chance to repost this simulation for current download? The posted domain has expired since original posting.

Best Regards,
 
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...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor