project schedule

@RISK for Cost and Schedule Risk Using Risk Registers (with Example Model)

@RISK can be used in conjunction with MS Project and Excel to model the schedule and cost risks inherent in large, complex projects. This example demonstrates the use of @RISK to build a complete model of the construction of a new commercial venue. The model includes uncertainty in task times, a Risk Register for calculating contingencies, and a link to real-time cash flows in an NPV calculation model.

@RISK probability distributions have been assigned to the durations of several tasks in the schedule, some with a distribution and others using Risk Categories. The uncertain task times are assumed to be uncorrelated.

A Risk Register lists three possible risk events that could impact the project schedule and costs. By using the RiskProjectAddDelay function, these risks introduce schedule delays and associated costs. Specifically, this function allows the model to generate new tasks dynamically, depending on whether the risks occur or not. Changes are reflected at run time only, so it is necessary to run a simulation to see the impact and results of the Risk Register.

The example also contains a model of cash flows that leads to the NPV of the project. In particular, the project costs create a Timescaled Data report. This collects the total cumulative costs during a simulation. After a simulation, you can see the total cumulative costs for the project as they grow over time.

The other reports generated are the NPV and the Contingency for the Risk Register, at different confidence levels. Finally, the cash flow also includes a Revenue Adjustment calculation that takes the portion of the year in which Sales are initiated and applies a discount to the predicted annual revenue.

» Download the model (Requires @RISK 6.x Professional or higher, Microsoft Project must be installed.): XLSX fileMPP file
» Download a trial of @RISK Industrial

@RISK Models Public Transit Expansion in Edmonton, Alberta

Edmonton The Canadian city of Edmonton, Alberta, is expanding their public transportation, known as the Light Rail Transit (LRT) system, from just one line to a network that connects all sectors of the city.

Of course, projects like these are expensive—anywhere from $200-$800 million—and come with considerable uncertainty. To deal with this, the City brought on a team to help them hone in on the costs and risks associated with the project, with a goal of greater cost and schedule certainty. As part of that team, SMA Consulting’s Risk and Project Controls Manager Jesse Kostelyk was tasked with estimating the potential costs and analyzing the uncertainties involved in the project. To do so, he turned to @RISK.

@RISK allows for the project management modeling that Kostelyk and SMA Consulting need, enabling them to estimate the impact of various risks on the cost and schedule of the project. Thanks to @RISK’s integration of Microsoft Project schedules with Microsoft Excel models, the consulting team could manipulate schedule inputs and then observe how those changes would affect the budgetary outcome of this important, multi-million dollar project. The result was a much more objective, precise view of the scope of the LRT expansion for decision-makers.

By leveraging the spreadsheet environment as an interface to a project schedule, @RISK enables modeling flexibility that was never possible in Microsoft Project alone.

“@RISK’s ability to integrate models in Excel and Project opens up a world of possibilities for capturing and quantifying construction project uncertainty,” reports Kostelyk. “At SMA Consulting, it’s our go-to program for risk analysis.”

After running the risk analysis using Monte Carlo simulation, the project managers were able to bring their results to the Edmonton City Council and present them with a defined cost range. “They are asked how much risk are you willing to take to move this project forward,” says Kostelyk, “and then we can quantify how certain we are about how much it will cost.”

Accordingly, the project team decided to choose a budget at the 85th percentile of certainty, which, when the construction contract was awarded, was within 2% of the winning bid. Thanks to @RISK’s integration of both schedule and cost concerns, the Edmonton LRT project began on solid financial footing.

Read the full case study here.