Introduction
In the last years we have seen project spending much more than the original budget, others who finished many years after the original planned date, and those who do not reach the scope defined by the client, etc.
There are many reasons for these project failures, but many of them can be found in the following:
- Lack of project formulation
- Lack of risk project analysis
In this paper we will review a simple project to demonstrate the typical failures that we can overcome when planning a project. A good project formulation is critical for a successful project.
Even though we get a good project formulation, the lack of an overall risk project analysis could bring a project into failure. In this paper we will discuss many tools for a quantitative risk analysis.
Quantitative Risk Analysis
Project estimations are variables depending on future events. The future is uncertain, and so it is the project outcomes. Therefore, a sensitivity analysis is critical to answer questions such as:
▪ What will happen with project estimates if a variable changes?
▪ How much could the costs increase and the project still be profitable?
▪ How much could the incomes decrease and the project still profitable?
▪ What are the critical variables?
▪ What happens to the project objectives if many variables change at the same time?
We will discuss five useful tools to find out the answer to these questions:
- Break-even point
- Elasticity
- Sensitivity analysis
- Scenario analysis
- Monte Carlo simulation
The approach to explain these tools will be using a very simple project as follows:
Net Income = (quantity x price) – (quantity x variable cost) – fix cost
Net Income = 1,000 x $20 – 1,000 x $12 - $6,000 = $2,000
Break-Even Point
The break-even point determines how much a variable can change in order to make the net income equals zero.
For example, if the quantity comes down to 750 units, the net income equals zero. But, how do we find this value? You can do some algebra steps or start moving the quantity value in a spreadsheet until you find the answer as shown here:
However, finding the break-even value manually using the spreadsheet quantity value until the net income equals zero is inefficient, because the “Goal Seek” tool incorporated into Excel is faster.
Step by Step With Excel:
- Office Button / Excel options / Add-Ins / Go / Check “Tools analysis” / Click OK
- Data / What-If Analysis / Goal Seek
- Complete the box: Set cell: B5 (Net income) To value: 0 By changing cell: B1 (Quantity)
- Click OK. The cell B1 moves from 1000 to 750 (break-even point)
- To find the break-even point to the other variables, you should click the “Cancel” button in order to come back to the original values.
Repeating steps 1 to 4 for the other variables, we get the break-even values as shown below. Moreover, in the last column we calculate the percentage change of the variable against its original value.
For instance, if the variable cost increases from $12 to $14, an increase of 17%, the net income is zero. If the variable cost is higher than $14 the net income is negative, and if the variable cost is smaller than $14 the net income is positive.
| Break-even | |||
| Baseline | point | ∆ % | |
| Quantity | 1000 | 750 | -25% |
| Price ($) | 20 | 18 | -10% |
| Variable cost ($) | 12 | 14 | 17% |
| Fix cost ($) | 6000 | 8000 | 33% |
| Net Income ($) | 2000 | ||
Analyzing all the break-even points against its original values, we can figure out the critical variables for this project. The most critical value in this example is the price, because a decrease larger than 10% implies a negative net income. On the other hand, the less critical variable is the fix cost, because it can increase up to 33% and the net income will remain a positive value.
Elasticity
Another way to study the project critical values is getting the elasticity of each variable. For example, we can calculate the change on the net income when a variable increases or decreases by 10%.
In the following table we can see that if we increase the quantity by 10% (from 1000 to 1100), the net income increases by 40% (from $2000 to $2800)
Using the software TopRank from Palisade, an add-in for Excel, we can find out the elasticity for each variable.
| Quantity | 1100 | |
| Price ($) | 20 | |
| Variable cost ($) | 12 | |
| Fix cost ($) | 6000 | |
| Net Income ($) | 2800 | 40% |
TopRank – Step by Step:
- Install the software
- Click over B5 (net income) / Click on “Add Output” / OK
- Click on “Run What-If Analysis”
- What-If Analysis Summary
The most critical variable is the Price, because when it changes 10% the net income changes 100%. On the other hand, the least critical variable is the Fix Cost, and when it changes 10% the net income changes only by 30%.
Sensitivity Analysis: Two Variables
Until now we have been analyzing the impact over the project one variable at a time, maintaining all the other variables in their original value. But what happens if we need to analyze the impact of two variables together?
We will discuss the impact on the two most critical variables identified in our project: price and variable cost.
We will use the tool “Table” included in Excel.
Step by Step – Table
- Construct a double entry table with the variables to analyze. For instance, in the following figure, we range the “variable cost” from $10 to $14 in cells C7:G7, and the “price” from $18 to $22 in cells B8:B12. Moreover, in the left upper corner we link the output variable (B7, net income).
- Select all the table (cells B7:G12)
- Data / What-If Analysis / Data Table
- Complete the box: Row input cell: B3 (variable cost) Column input cell: B2 (price)
- Click OK
The results show all the net income when combining prices from $18 to $22 and variable cost from $10 to $14. For example, if the price is $22 and the variable cost is $11, the net income is $5000 (cell D12).
When most of the results of the table are positive, the project risk level is low. On the other hand, if most of the results are negative, the project risk is high.
Scenarios: Multiple Variables
How we can do sensitivity analysis for more than two variables? One more time the answer is “Excel.” Let’s assume that we need to know how the net income will change when all the variables of our project change together.
Step by Step – Scenario
- Data / What-If Analysis / Scenario Manager
- Add
- Complete the box: Scenario name: Pessimistic Changing cells: B1:B4 (quantity, price, variable cost, fix cost)
- Click OK. Complete the box with pessimistic values. Then OK.
- Repeat steps 2 to 4 adding an optimistic scenario
- Summary
- Result cells: B5 (net income). Click OK
- Scenario Summary
As we can see in the scenario summary table, in a pessimistic scenario the net income is -7200 and 14000 for an optimistic scenario.
If you think the future will be pessimistic it is not a good decision to undertake this project. However, if you guess the future will be normal or optimistic, you should undertake this project.
The limitation of this tool is that the definition of the different scenarios could be subjective depending on the project analyst. To mitigate this limitation, let’s review another tool for a dynamic risk analysis: Monte Carlo simulation.
Monte Carlo Simulation
One software to run Monte Carlo simulations as an add-in for Excel is @Risk from Palisade.
Let’s suppose that the quantity estimated to sell products is a well-known variable because we have signed a good contract with the client, so we do not need to change this estimate (i.e., 1000).
On the other hand, we have a good historical data sample showing that the price estimated has a normal standard distribution with a media of $20 and a standard deviation of $2.
We do not have historical data regarding the variable cost. However, an expert panel has estimated three values for this unknown: $10 (optimistic), $12 (most probable), $16 (pessimistic). In this situation a triangular distribution could fit well for this variable.
Finally, we do not have any information for the fix cost. The analysts have estimated that it could be in a range from $4000 to $8000 without knowing if it is most probable one value or another. Therefore, a uniform distribution is the most appropriate in these cases.
In this table, we resume the probability distribution for each variable.
| Variable | Baseline | Distribution |
| Quantity | 1000 | None |
| Price ($) | 20 | Normal Standard (20; 2) |
| Variable cost ($) | 12 | Triangular (10; 12; 16) |
| Fix cost ($) | 6000 | Uniform (4000; 8000) |
Step by Step – Monte Carlo Simulation with @Risk:
- Install @Risk for Excel
- Step over the input variable to introduce a distribution. For example, B2 (price). Then click “Define Distribution”. Then select the distribution from the list. In this particular case select “Normal”.
- Select Distribution. Complete the box: μ = 20; σ = 2. Then click OK.
- Repeat steps 1 and 2 for the rest of the variables, as shown in the following figures.
- Step over the output variable: B5 (net income). Add Output. OK
- Complete de number of iterations, for example, 10,000 / Start Simulation.
Iterations running:
- Output results.
We can see on the right side that the simple average, after the 10,000 iterations, of the net income is 1333 (Mean). On the top we can see that there is a 90% probability that the net income is between -2990 (-2,99) and 5590 (5,59).
To evaluate the probability of losing money with this project, we can move the vertical lines as shown in the next figure.
The probability of losing money is 30,3%. In other words, after 10,000 random iterations, 3,030 scenarios get a negative net income and the other 6,970 has positive net income.
Following the same logic that we use to estimate the probability of losing money, we can estimate the probability of complete a project under schedule. For that we can use the software @Risk Project, among others.
Final Words
Once we have identified and quantify the project risks, it will be necessary to manage them.
Remember that you are always the one managing the risks, defining which ones to accept or reject. Do not let risks to control your project, be proactive with an overall risk project management.