Project Risk Management

Project Risk Management

Assessment 3 Report (Project Budget) (40%)

Monte Carlo Simulation

Due: end of week 13 (midnight Sunday)

Aim of Assessment

General To apply risk analysis quantitative techniques to projects

Learning Outcome2. Apply quantitative risk analysis using specialist software to create a cost model, and evaluate and analyse the results

ModulesThis assessment addresses information from modules 7, 8 and 9.

Select a project. Can be from your work experience; or a social project (could be same project as Part A of Assessment 1, but best for learning purposes if the project is a real future project. Could use project from Time Management or Cost Management units).

Budget Report (using Monte Carlo Simulation)

NOTE: This is a formal project management document, not an academic assignment.

Produce a Budget Report document to the Project Sponsor, who is not knowledgeable about the cost estimating process or Monte Carlo simulation, that must strictly have has the following six sections (a-f):

Introduction: e.g. executive summary (key results within the report), project scope, and any other relevant information (10 marks)

Recommended Baseline Budget (excluding contingency), composed of 10-30 cost variables (10 marks) (This is a deterministic estimate based on most likely value for each cost variable, excluding risk events and contingency).

Structure and content of the cost model.

Explain the source of information used to produce this budget.

NOTE:

Once you have completed the above deterministic baseline budget, then conduct Monte Carlo Simulation (including correlations). Your cost model for MCS will:

replace deterministic values in the Recommended Baseline Budget with probability distributions; and

add 2 specific risk events.

Brief explain your 2 risk events (one short paragraph each risk briefly explaining what the risk event is. Do not discuss: causes; probability or consequences; or treatments ) (5 marks) (Note: you must chose/explain risk events that if they eventuate they will increase the sponsors budget)

Recommendation for Contingency (explaining reasons) (10 marks)

135318538925500Sensitivity Analysis based on the Tornado Chart, explain to the Sponsor how you will control and minimise the following:

Most sensitive cost variable (10 marks)

Most sensitive risk event (10 marks)

Compare and comment on the your cost results against the organisational policy that states it is expected that the Baseline Budget should have an 80% probability of being with a range of -5/+10%, (10 marks)

Appendix:

Quick Output Report (no marks but should be referred to in answering above points)

The sponsor wants to understand MCS. To facilitate this, the sponsor has asked:

Explain and justify the selected values (minimum, most likely and maximum) in the probability distributions for ONE of the cost variables (10 marks )Explain and justify the selected values (minimum, most likely and maximum) (10 marks) and probability of occurrence for ONE of the risk events (5 marks)

Correlation matrix. Select 2 correlated variables (i.e. one correlation) from your cost model. Explain why they might be correlated. And the likely nature and strength of this correlation (5 marks)

Professional Report Style Presentation (5 marks)

Format

Length 2500-3500 words.

All assignments should be thoroughly checked for typing, spelling and grammatical errors before being submitted. Assessments to be one and half spacing, Arial narrow 10 font, 2.5cm margins.

Submission must be through Turnitin (under the Assessments tab in Blackboard) in MS Word (not PDF) format.

All source material MUST be acknowledged and any form of plagiarism will result in the appropriate measures being taken. Do not copy other students work or assist by showing your work to others.

Late submissions will be penalised as per the Curtin Late Assessment Policy in the Unit Outline.

Marking Criteria

Introduction 10

Recommended Baseline Budget 10

Explanation of 2 risk events 5

Recommendation for Contingency 10

Sensitivity Analysis Most sensitive cost variable 10

Most sensitive risk event 10

Compare and comment on cost results 10

Appendix Quick Output Report -

Explain and justify the selected values of one cost variable 10

Explain and justify the selected values 10

probability of occurrence for one of the risk events 5

Correlation matrix 5

Professional Report Style Presentation 5

Total 100 (marks) > 40%

47434579375Module 8

Monte Carlo Tutorial

00Module 8

Monte Carlo Tutorial

Table of Contents

TOC o "1-2" h z u Introduction PAGEREF _Toc518479204 h 3Learning Outcomes PAGEREF _Toc518479205 h 3Tutorial PAGEREF _Toc518479206 h 3Step 1 Download @Risk and Install PAGEREF _Toc518479207 h 4Step 2 Develop a Model PAGEREF _Toc518479208 h 4Step 3 Apply Correlation PAGEREF _Toc518479209 h 6Step 4 Select Sampling PAGEREF _Toc518479210 h 7Step 5 Produce Results PAGEREF _Toc518479211 h 9Step 6 Conduct Sensitivity Analysis PAGEREF _Toc518479212 h 12Step 7 Make Decisions based on Analysis PAGEREF _Toc518479213 h 13Required Reading PAGEREF _Toc518479214 h 13Additional Resources PAGEREF _Toc518479215 h 13

centercenter00

IntroductionWelcome to the tutorial for Module 8 of Project Risk Management Monte Carlo Tutorial.

In this tutorial you will apply the theory learned in Module 8 to a Monte Carlo risk simulation exercise be sure you have completed the module prior to attempting this tutorial. The exercise is for a simplistic project, and is designed to give you the baseline skills required to perform a more rigorous simulation when you go to create a cost contingency for your project that you have selected for assignment two.

Learning OutcomesIn this module you will learn how to:

Set up a cost model in excel;

Identify model inputs and outputs;

Run Monte Carlo simulations; and

Generate executive level reports for sponsors.

TutorialBefore starting the tutorial, please be aware that there are additional tutorial resources available via Pallisade, the makers of @Risk Software.

Useful Links

Pallisade, the makers of @Risk and Decision Tools provide an in depth video tutorial that is available to Curtin students at:

https://www.palisade.com/on-demand-webinars/?language=english&dimension=products&subfilter=risk

You will need to create a free account to access the materials. You can download a copy of the MS Excel tutorial file under the attachments tab once you have access to the video after establishing your account.

Step 1 Download @Risk and InstallFollow the instructions in blackboard for downloading @Risk and installing the software on your computer. Before starting, please be aware that @Risk has many helpful tutorials, webinars, and other helpful information under the resources tab:

Step 2 Develop a ModelCreate a cost model

For the purposes of this tutorial, please use the Microsoft Excel file titled Holiday Example, which can be found in the Module 8 Blackboard folder. The file contains a cost model for a holiday project, containing variability risks for cost variables (e.g. food); and contingent risks (i.e. risk events: e.g. a speeding fine).

2.Specify Input and Output Variables

Assign probability distributions (PD) for each uncertain input variable

Select one input cell in your Excel model that you wish to define a PD for (the input cells are all highlighted in yellow in the tutorial file). Select Define Distributions. Double-click a PD (e.g. Trigen) and insert values (e.g. min, most likely, max), click OK (Note: ignore unable to graph distribution keep typing!).

Do the same of all other input cells (i.e. Entertainment, Accommodation, kms, litres/kms, and petrol) (Note: Selecting a PD for a cell, automatically makes it an input variable. Use the following table as your guide for setting up your PD cells.).

Select the output variable(s)

Select the output variables you wish to analyse. Any model needs input values and output results. Typically, output cell(s) contain the results of your spreadsheet model (e.g. profit, total cost, total duration). Click the cell(s) you want to be outputs (in the tutorial file the blue cell should be set to the output cell). Select Add Output. A dialog box will pop up so that the output cell can be named (Total Cost pops up automatically, as @risk looks for words to left of output cell chosen). Click OK.

c.View the model

Once inputs and outputs have been selected, these can be viewed by selecting Model Window

Step 3 Apply CorrelationIt is important to account for correlation between input variables (see Module 8 for background theory and explanation of importance), to do so:

Hold down CTRL key and click the input cells you want to correlate (click all yellow cells).

Select Define Correlations, this bring ups correlation matrix.

Type in correlation coefficients between inputs i.e. type a value between -1 and 1 (e.g. -0.3, 0.5) in any cell where you have decided that 2 variables are correlated these are cells that show as default value of 0 (zero). If no correlation, leave those cells as 0. Then Click OK. You will then be asked where you want to locate the matrix table within your Excel spreadsheet chose somewhere below the model (e.g. click cell A14). Click OK. (Note: If you now place the cursor in any cell within the correlation matrix then click Define Correlations, this brings up the correlation matrix, named Define Correlations; New Matrix 1. If you wish, you can change values or add more input cells)

Step 4 Select Sampling1.Before running the simulation, some parameters need to be set. Settings to be considered include:

Select sampling method to be used to select values from the input PDs.- Monte Carlo or Latin Hypercube Sampling (see Module 8). Select Simulations Setting, Sampling, Sampling Type: Monte Carlo

From the same menu as the previous step, Select Initial Seed: Fixed. OK (see below). This means the exact same sequence of random numbers will be repeated simulation to simulation. Setting a fixed seed value is useful when you wish to control the simulation sampling environment. For example, you may want to simulate the same model twice, only changing the values for one distribution function. By setting a fixed seed, the same values will be sampled each iteration from all distribution functions except the one you changed. Thus, the differences in the results between the two runs will be directly caused by changing the argument values of the single. For the final simulation that will be used for risk analysis, select Initial Seed: Choose Randomly

How many iterations shall I run? (Typically 1000-1500 times). It is important to run enough iterations so that the outputs statistics are reliable. There comes a point when the time spent for additional iterations is essentially wasted because the outputs statistics are not changing significantly. In the tool bar, Iterations, Type 1500. (Note: @Risk includes a convergence monitoring capability, which can be used to stop the iterations once the iterations are having little additional effect on the output statistics).

Step 5 Produce ResultsOnce we have established the model and it parameters we can run the simulation:

To run the simulation Select Start Simulation and watch what happens! The animation shows, very quickly, each iteration and the selection of input values and effects on the output variable (moving probability distribution histogram) A simulation involves repetitive recalculations of your model. Simulation means the computer recalculates the Excel worksheet over and over again (each recalculation is an iteration), each time using different randomly selected values for each input PD within the Excel cells.

2.Review statistical results. Once the simulation has been completed, various results can be viewed:

Click Browse Results (see image below), which shows key statistics of the input and, more importantly, output variable e.g. probability distribution, mean, median, minimum, maximum, 5% percentile, 95% percentile. A probability distribution is created for the output variable, summarising the output values generated by the iterations this is the most important result of Monte Carlo simulation.

Add the ascending cumulative overlay line. Then right-click on icon Select type of distribution graph to display (circled in image below) select cumulative overlay ascending. The delimiter lines (two vertical lines) can be dragged to quickly calculate Target Values and answer very important project management questions about risk to the budget: e.g. what is the probability of the trip budget of $2700 being enough? How much contingency must be add to the $2500 to make a total budget have a 80% probability of being enough? This can now be used for extremely valuable analysis: e.g. probability of a selected output will not be exceeded.

The Simulation Detailed Statistics shows more statistics e.g. standard deviation, skewness, kurtosis, and percentiles. The statistics can be interrogated by setting Target Values e.g. what is the probability of total cost being $2700 or less?; or, whats the probability of total cost being between $2700 and $3000? What is the 80-percentile value for the output variable? (Basically this answers the same questions as previous paragraph).

The Simulation Data button shows the values selected for each iteration of the simulation.

The results of the simulation can be generated in a report directly into your Excel file as a new tab or as an entirely new Excel file. Select Output Reports, then select whatever information you want in your report.

It is suggested that Compact Output Report is selected (circled in red in the pic below). Select options that you require (yellow circles in the pic below), then generate report so that the Compact Output Report is shown. If you have elected to open a new Excel workbook, click save save as Output Report. The Compact Output Report is the typical type of report provided to sponsors.

Step 6 Conduct Sensitivity AnalysisWhen conducting a sensitivity analysis, the goal is to identify the input variables which are significant in determining output values. In particular we will examine two different analytical techniques: regression analysis; or correlation (Spearman rank).

Select Simulation Sensitivities, select regression or correlation (coefficients). This ranks the input variables and their effect on the output. A value of zero means the input has no effect on the output and the higher the correlation between the input and output, the more significant the input is in determining the outputs value.

The results can be displayed as a Tornado Graph, with longer bars at the top representing the most significant input variables. Select Browse Results (make sure cursor is in blue total cost cell), Tornado Graph, Regression Coefficients. For more information on sensitivity analysis see https://help.palisade.com/v8_1/en/@RISK/Analysis/Sensitivity-Analysis.htm

Step 7 Make Decisions based on AnalysisNow that you have run your simulations you will need to make decisions regarding:

The amount of contingency that you will make available for this project;

Any impact or adjustments to amount of management reserve that are necessary if you are to continue with this project;

The management of sensitive variables; and

Whether or not to continue with the project.

Monte Carlo simulation is simply a tool that helps us to make better informed management decisions for our projects. It should be used alongside other decision making processes in order to have a more robust overall risk management process.

Required ReadingRead Module 8 prior to starting this tutorial.

Additional ResourcesFor those of you who work or plan on working on large scale capital works projects comprehensive risk analysis will be required in order to get projects approved. Having a firm understanding of risk simulation will make you more valuable to your organisation. The following links will help you to develop that understanding and will also help you to achieve a top mark on the assignment.

Useful Videos

Check out these @RISK by Palisade webinars:

Intro to @Risk Monte Carlo SimulationIntro to Cost Estimating with @RiskWhich distributions should I choose in @Risk@Risk Model Correlation Essentials

PROJECT RISK MANAGEMENT (PRJM6003)

MODULE 8 MONTE CARLO SIMULATION - TUTORIAL (note: this is a shorter version of 14-page tutorial in Blackboard)

In this tutorial you will apply the theory learned in Module 7 (Probability Distributions) and Module 8 (Monte Carlo Simulation) to a risk simulation exercise be sure you have completed the modules prior to attempting this tutorial. The exercise is for a simplistic project and is designed to give you the baseline skills required to perform a more rigorous simulation for your project that you have selected for assignment two.

Learning Outcomes:

Set up a cost model

Set up parameters for Monte Carlo simulation e.g. probability distributions for input variables, correlations, sampling)

Run Monte Carlo simulations and analyse/test the results

Generate reports

NOTE: You must download/install @Risk and have Excel on your computer. And download Excel file: Holiday Example.xlsx from Module 8 Blackboard folder

STEPS (you now have opened @risk, with typical Excel toolbar. Only difference is @RISK tab in toolbar)

DEVELOP A COST MODEL Open Holiday Example.xlsx, which contains a cost model for a holiday project, containing target uncertainty (i.e. cost variables e.g. food, accommodation); and event uncertainty (i.e. risk events: e.g. a speeding fine).

SPECIFY INPUT and OUTPUT VARIABLES

Assign probability distributions [PD] for each uncertain input random variable

Select 1 input cell at a time in your Excel model that you wish to define a PD (yellow cells). Select Define Distributions Double-click a PD (e.g. Triang) and insert values (eg min, most.likely, max), click OK (Note: ignore unable to graph distribution keep typing!). Do the same of all other input cells. Note: Selecting a PD for a cell, automatically makes it an input variable.

Use the following table as your guide for setting up your PD cells

SELECT THE OUTPUT VARIABLE(S) you wish to analyse. Any model needs input values and output results. Typically the output cell(s) are the results of your model (eg profit, total cost, total duration). Click the cell(s) you want to be outputs (blue cell in xlsx file). Select Add Output . A dialog box will pop up so that the output cell can be named (total cost pops up automatically, as @risk looks for words to left of output cell chosen). Click OK

Once inputs and outputs have been selected, these can be viewed by selecting Model Window

APPLY CORRELATION - It is important to account for correlation between dependent input variables (See Module 8 notes).

Hold down CTRL key and click the input cells you want to correlate (click yellow cells Food and Entertainment because we have decided these are the only related/dependent variables in our model).

Select Define Correlations This bring ups correlation matrix.

Type in correlation coefficients between inputs i.e. type a value between -1 and 1 (e.g. 0.5, -0.7) in any cell where you have decided that 2 variables are correlated cells show default value of 0 (zero). If no correlation then leave those cells as 0. Then Click OK. You will then be asked where you want to locate the matrix table within your Excel spreadsheet chose somewhere below the model e.g. click cell A14. Click OK. (Note: If you now place the cursor in any cell within the correlation matrix. Then click Define Correlations. This brings up the correlation matrix, named Define Correlations; New Matrix 1. If you wish you can change values or add more input cells)

SELECT SAMPLING. Before running the simulation, some sampling parameters need to be set. Settings to be considered include:

Select sampling method to be used to select values from the input PDs: Monte Carlo or Latin Hypercube Sampling (see Module 8). Select Simulations Setting , Sampling, Sampling Type: Monte Carlo

Select Initial Seed: Fixed. OK. This means the exact same sequence of random numbers will be repeated simulation to simulation. (explained in Module 8 notes). For the final simulation that will be used for risk analysis, select Initial Seed: Choose Randomly

How many iterations shall I run? (Typically 1000-1500 times). It is important to run enough iterations so that the output statistics are reliable. There comes a point when the time spent for additional iterations is essentially wasted because the outputs statistics are not changing significantly. In tool bar, Iterations, Type 1500. (Note: @Risk includes a convergence monitoring capability, which can be used to stop the iterations once the iterations are having little additional effect on the output statistics)

PRODUCE RESULTS

The simulation can now be run. Select Start Simulation and watch what happens! The animation shows, very quickly, each iteration and the selection of input values and effects on the output variable (moving probability distribution histogram) A simulation involves repetitive recalculations of your model. Simulation means the computer recalculates the Excel worksheet over and over again (each recalculation is an iteration), each time using different randomly selected values for each input PD within the Excel cells.

Review statistical results. Once the simulation has been completed, various results can be viewed:

Click Explore , then scroll down to Simulation Details - Statistics ,which shows key statistics of the input and, more importantly, output variable e.g probability distribution, mean, median, minimum, maximum, 5% percentile, 95% percentile, etc. A probability distribution is created for the output variable, summarising the outputs values generated by the iterations this is the most important result of Monte Carlo simulation.

The Simulation Data show the values selected for each iteration

The Explore Browse Results, which pulls up the simulation statistics e.g. standard deviation, skewness, kurtosis, and percentiles. The statistics can be interrogated by typing values into the chart to answer very important project management questions about uncertainty to the budget such as:

What the probability of the clients baseline budget of $2791 not being exceeded? Type $2791 for Target #1(value above black triangle) answer is the percentage to the left of the line

What the probability of the clients budget of $3000 (maximum prepared to spend) not being exceeded? Type $3000 for Target #2 (value above second black triangle) determine on your own how to answer this question. A basic knowledge of statistics should allow you to do this.

What is the client budget that will has a 90% probability of not being exceeded? Use the sliders to set values so that you can answer the question

What is the probability of total cost being between desired policy range of $2500 and $3000? Use the sliders to set values so that you can answer the question

The results of the simulation can be generated in a report directing into Excel. Select Reports , then choose output reports then select whatever information you want in your report. It is suggested that Compact Output is selected. Click OK, then Quick Output Report is shown. Click save save as Report. The Quick Report is the typical type of report provided to sponsors.

CONDUCT SENSITIVITY ANALYSIS. I.e. identify the input variables which are significant in determining output values, using two different analytical techniques: regression analysis; or correlation (Spearman rank)

Select Explore Simulation Graphs Sensitivities (make sure cursor is in blue total cost cell), select regression or correlation (coefficients). This ranks the input variables and their effect on the output. A value of zero means the input has no effect on the output and the higher the correlation between the input and output, the more significant the input is in determining the outputs value.

The results can be displayed as a Tornado Graph, with longer bars at the top representing the most significant input variables.

MAKE DECISIONS e.g baseline budget, amount of contingency; how to manage sensitive variables