74. SCENARIO ANALYSIS

OBJECTIVE

Identify possible future scenarios.

BEST–WORST–AVERAGE CASE SCENARIO ANALYSIS

Simple scenario analysis can be managed in a spreadsheet into which we can insert data and functions that represent our business model. In the proposed example, the spreadsheet contains the figures for an investment in a business intelligence system for a hotel chain and the costs and returns associated with this investment. In the first sheet, the most likely scenario is presented, and with a simple what-if analysis we add a worst-case scenario and a best-case scenario.

First of all we define the main input variables that affect the outcome:

• -     Revenues: we project the expected additional revenues in future years thanks to the new BI system. The additional revenues are expected due to the better service personalization, better client service, and more effective marketing campaigns.
• -       Costs: we will incur additional costs associated with the BI system management.
• -       Cost of capital: to calculate the net present value, we discount the cash flows with the cost of capital for our company.

The second step is to define the number of scenarios. In our case we have three, but we can also opt for a multiple-scenario analysis including more intermediate scenarios (bear in mind that, if we increase the number of scenarios, on one side we will have more realistic results but on the other side the analysis will become more complicated).

Then we modify our input variables (revenues, costs, and cost of capital) by reasoning on the underlying factors that can change them. For example, a worse global economic situation negatively affects revenues, and some unexpected situations can increase the costs associated with the project or just our estimations of the positive effects of the BI system. After defining the main factors and the underlying assumptions, we modify the input variables with the maximum negative effect for the worst-case scenario and with the maximum positive effect for the best-case scenario.

We can use the Excel What-If Analysis tool to save different scenarios and to create a summary of them by clicking on “Data,” “What-If Analysis,” and “Scenario Manager.” We will see the three scenarios that I saved; if we click on one scenario and then “Show,” we will see the scenario. We can also add a new one by clicking “Add” (I suggest first changing the changing cells in the spreadsheet – green cells – and then adding a new scenario; otherwise, we will have to change the values in a less intuitive way in the scenario manager window). Once we have added a new scenario, we can click on “Summary” and choose the report type and result cells (by default we will see the cells of NPV of the scenarios that I have saved).

Scenario Summary

The figure above presents the result cells of the summary scenario (changing cells have been deleted). The current values are the same as the most likely scenario, since they are the value at the moment of running the summary. With the cumulated net present values in the next five years, we can now evaluate the risks and opportunities of this project.

OTHER TYPES OF SCENARIO ANALYSIS

We can also opt for a multivariate scenario analysis, by adding more intermediate scenarios. In this case a common practice is to assign a probability to each scenario to evaluate the risks and opportunities according to both the magnitude of the impact and the associated probability.

Another common technique is decision tree analysis, which tries to represent all the possible events with associated probabilities and impacts (see 75. SIMPLE DECISION TREES). A more precise but more complex technique is to perform simulations, which rely on input variable probability distributions to create a distribution of possible outcomes (or scenarios). The advantage of simulations is a more accurate analysis, since we can calculate statistical parameters with accuracy, such as the standard error, confidence intervals, and so on (see 77.MONTE CARLO SIMULATIONS).

More information concerning probabilistic scenario analysis is available in the following document:

http://people.stern.nyu.edu/adamodar/pdfiles/papers/probabilistic.pdf

TEMPLATE

Download the Scenario Analysis Template

77. MONTE CARLO SIMULATIONS

OBJECTIVE

Determine probable outcomes.

DESCRIPTION

In deterministic models we predict events in a simple linear system and we assume that the initial conditions do not change. Besides, the same initial conditions will give the same results. However, the world is more complicated and events are usually determined by a complex interrelation of different variables, some of which are difficult or almost impossible to estimate. Monte Carlo simulations solve this problem by using probability distributions for each input variable and then running several simulations to produce probable outcomes. We can say that this model allows the prediction of an outcome without conducting many expensive experiments.

The steps for performing a Monte Carlo simulation are:

• -          Define the mathematical formula for the outcome;
• -          Identify the probability distributions of the input variables and define their parameters;
• -          Run the simulations;
• -          Analyze and optimize.

Monte Carlo Simulation Output

Input and Output Variables

The first step in a Monte Carlos simulation is to define the output, that is to say to identify the variable that we want to predict, for example “profits.” Then we should identify the input variables on which “profits” depend. Some of them may be certain; for example, we can have a fixed cost with a specific value, but usually they are uncertain. For each of the uncertain variables, we need to identify a specific probability distribution to use for the simulation. Examples of distribution are:

• -       Discrete distribution: we define the probability of a finite number of values;
• -    Uniform distribution: each variable value has similar probabilities (for example, when throwing a die, each number has a 1/6 probability);
• -       Bernoulli distribution: we have only two alternative and exclusive outcomes (0 or 1);
• -     Normal distribution: the central values are the most probable ones (defined by the mean and standard deviation);
• -       Triangular distribution: we have a most probable value and a lower and an upper limit;
• -        Other distributions: exponential, logarithmic, binomial, beta, etc.

Having identified the distribution, we can use a chi-square test (see 48. CHI-SQUARE) to check whether the data fit the chosen distribution. An alternative would be to conduct a Korm–Smirnov test.

In this phase we also write the mathematical formula by which the outcome is defined, for example:

Profits = (Price – Variable cost) * Units – Fixed Costs

The simulation is then performed repeating the input variables (with each specific probability distribution) hundreds or thousands of times to obtain a distribution of probable results.

Analysis and Optimization

Once the range of probable results has been obtained, depending on the objective, we use indicators such as the minimum value, maximum value, average, standard deviation, and so on. In general we usually compare:

• -        Expected value: the mean of all the outputs with its confidence intervals;
• -       Risk: in the proposed example it is the probability of negative profits (% of outputs < 0), but we can also choose a specific value.

It is also possible to compare different simulations with different input variables’ values or distributions. To compare them, we should calculate the confidence intervals of both expected values and risks. If the range between the confidence intervals does not overlap, we can infer that one scenario is better or worse than the other one.

If the objective is to use the results for a business plan or in risk analysis, we can stop here, but if we want to optimize the outcomes, a sensitivity analysis is needed. In this kind of analysis, we measure the “importance” of each input and may decide to act on the most influential ones. Usually the correlation coefficient between each input and the output is used, but different techniques can be adopted.

TEMPLATE

Discount code -40%: BLOG_ANALYTICS_MODELS