Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations waross on being selected by the Tek-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

simmantix

Nuclear
Apr 30, 2003
31
0
0
GB
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

Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.



 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Any chance to repost this simulation for current download? The posted domain has expired since original posting.

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

Similar threads

norzul
  • Locked
  • Question
Monte Carlo Analysis
Replies
7
Views
8
norzul
norzul
rajawaqar
  • Locked
  • Question
Project simulation 2
Replies
7
Views
8
zeusfaber
zeusfaber
rajawaqar
  • Locked
  • Question
Spreadsheet simulation for Prject Management
Replies
0
Views
1
rajawaqar
rajawaqar
Back
Top