The portfolio representation described previously included project costs and returns that were purely deterministic. In reality there were be large uncertainties in both, for example a 6 month project overrun will increase overall project costs and is also likely to reduce returns. We can use Monte Carlo analysis to model the effect of these uncertainties on the discrete portfolio options.
A real model can have different layers of granularity and detail, specific to the individual projects. For example, uncertain returns will be dependent on volumes of sales over time, pricing, competition etc. To illustrate the principle we use a simple model where the individual project are independent and returns and costs are triangular distributions in which we specify the minimum, maximum and average.
Project 

Return 


Cost 

– 
Min 
Average 
Max 
Min 
Average 
Max 
A 
$5.00 
$8.00 
$9.00 
$3.80 
$4.00 
$6.00 
B 
$0.50 
$2.50 
$5.00 
$0.80 
$1.10 
$1.50 
C 
$1.00 
$4.00 
$4.80 
$1.00 
$1.30 
$2.00 
D 
$1.00 
$3.00 
$6.00 
$0.70 
$0.75 
$1.30 
E 
$1.50 
$2.00 
$3.00 
$1.00 
$1.25 
$2.00 
F 
$0.30 
$1.60 
$1.90 
$1.70 
$2.00 
$2.40 
Simulating Uncertain Portfolio Returns and Costs
When we run a simulation we resample from the distributions and realise a particular outcome. Below are two simulated instances; we can run a full simulation of thousands of samples with a package such as @Risk or the Solver risk optimiser.
In this instance Project A has a return of $7.15M and cost of $5.24M, Project D had a return of $3.95M and cost of $0.78M; they are independent so the portfolio selection of Project A and D has an overall return of $11.4M and cost of $6.02M. This turns out to be the optimum portfolio for this level of investment.
In this instance Project A has a return of $7.36M and cost of $3.91M, Project D had a return of $2.41M and cost of $0.94M; they are independent so the portfolio selection of Project A and D has an overall return of $9.77 and cost of $4.85M. In this instance the combination of A and D is no longer the optimum portfolio for this level of investment.
What becomes clear is that when we add uncertainty there will not necessarily be a unique optimum that we can select. The optimum portfolio can be different but we don’t know how the uncertainties will be resolved in advance.
Visualisation of Portfolio Uncertainties
When we run the simulation each discrete portfolio point will have an associated cost and return distribution. We can add this distribution information to the portfolio representation by adding errors bars. The y error bars are the 95% limits of the distribution of return and the x error bars are the 95% limits of the distribution of cost. We now have representation that embeds risk, which has a very high information density to help us make optimum portfolio decisions with regard to return, cost and risk.
Optimisation of an Uncertain Portfolio
MinMax Rules
The uninitiated executive may have no knowledge of a probability distribution or understand how to use it. What are more common are questions like, “well what is the worst case?”, “what happens if the costs are high and the return is lower than we expect?” We can add extra interactivity to the dash board to allow the executive to explore these possibilities and see how that may affect the optimum portfolio selection as a function of their own appetite for risk.
The first representation is what we have already seen previously; we plot the average cost and return and select the optimum as normal.
We now replot the portfolio to show the worst case of maximum cost and minimum return. In most cases there would be a degree of interdependency in cost and return and we may have to resimulate to obtain this plot. One approach would be to extract the 5% and 95% percentiles for return and cost respectively.
The best case would be the maximum return for minimum cost.
Between these extremes we can continuously vary the plot of return and cost. It is also very straightforward to add likelihood figures to the plots to show the executive that the extreme values are less likely than the average values; they are getting all the information about the underlying distribution but the representation and output is more accessible.
What is apparent is that the pareto efficient frontier will change. The optimum portfolio will be different for those who have a differing preference for risk. Some of the portfolio options may have a very large upside but also a large downside; if I was risk averse and was operating a max cost minimum return decision rule this portfolio option would be plotted in the lower region of the chart and would not be an optimum choice for me given the actual portfolio and my risk appetite. The reverse would be true if I was risk seeking. We have a representation that allows us to revise the optimal profile as a function of risk behaviour.
Semantic Zooming
Each portfolio combination point has an associated cost and return distribution. Points in the lower region of the plot are clearly suboptimal, the returns are low and costs are high, however with points close to each other on Pareto frontier it can sometimes be difficult to decide which one is better given their distribution profiles.
We can use a drill down or semantic zoom to look at distributions of adjacent points on the efficient frontier.
There are a few interesting situations where it is difficult to decide the optimum; typically the resolution boils down to a question of appetite for risk. In the example below the portfolio combinations A and B are very similar; the costs are comparable and the average returns are almost the same. However when we drill down to the return distribution we can see that even though the mean is similar there is a big difference in range of possible returns. Portfolio A has both a higher upside and lower downside; if I was risk averse I may prefer portfolio B, which has a slightly lower return but with more chance of hitting.
can u please provide mw with a ecel spread sheet which can explain monte carlo simulation in Value at risk using simple excel software of a portfolio