Magazine article Financial Management (UK)

# Monte Carlo Simulation: When It Comes to the Finance Department's Role in Producing Forecasts of Business Activity, Monte Carlo Simulation Offers the Chance to Gauge the Complete Range of Possible Outcomes

Magazine article Financial Management (UK)

# Monte Carlo Simulation: When It Comes to the Finance Department's Role in Producing Forecasts of Business Activity, Monte Carlo Simulation Offers the Chance to Gauge the Complete Range of Possible Outcomes

## Article excerpt

Finance departments have played an important role in putting together forecasts of business activity, alongside their traditional responsibilities for historic reporting. This could be monthly reforecasts or annual budgets. At the heart of producing financial forecasts is the need to produce forecast driver inputs that determine the forecast value of key profit and loss and balance sheet items. Using the example of a forecast model for an airline business, this might well be built on forecast assumptions about future passenger numbers and ticket prices, as well as the cost of key lines, such as fuel and labour, to produce a profit and loss forecast. These inputs will most likely be estimates, and will hopefully be based on mining the intelligence within the business, as well as taking into account externally produced data, such as market growth estimates and forecasts for key economic variables, such as GDP growth and inflation rates. However, even if such forecasts are the result of painstaking analysis, they are ultimately educated guesses, and the reality is that the actual passenger numbers, average ticket prices and key cost lines will inevitably be different to forecast values by some degree.

Because of this, prudent businesses are likely to produce sensitivity analysis showing the impact of changes in the values of key inputs - for example, those mentioned on key outputs, such as profit. These may well take the form of a classic Excel data table showing the impact of two particular inputs on the output.

Monte Carlo Simulation gives business planners the ability to take that process one stage further by defining the list of feasible values for key inputs (and the relative likelihood of each occurrence) and then effectively throwing the dice thousands of times to give an idea of the complete range of possible outcomes that would result from these different inputs.

We can illustrate this by using the example of a hypothetical airline with a simplified model consisting of model inputs for the passenger numbers, average customer spend, fuel price inflation and other costs. The model shows best - and worst - case scenarios for each variable. In the case of passenger numbers and fuel price inflation these are asymmetric, as the downside of the worst case is greater than the upside (e.g. the worst case for passenger numbers is the impact of a terrorist attack on an aviation target, as happened with September 11, while the worst case for oil prices is war or significant political upheaval that impact supply of a major oil producer). Figure 1 shows the different profit outcomes under the expected case, best case and worst case, with the downside of the worst case (an expected loss of [pounds sterling] 7,550), from the expected case of [pounds sterling] 3,575 being far greater than the upside from the best - case scenario of [pounds sterling] 4,764.

Figure 1: Airline forecast scenarios

```Figure 1: Airline forecast scenarios

Passengers (m)
Average passenger spend [pounds sterling]
Revenue ([pounds sterling]m)
Base Fuel Costs ([pounds sterling]m)
Fuel Inflation
Other Cost ([pounds sterling]m)
Profit ([pounds sterling]m)

Previous                                             Distribution
Year                            Forecast Year        of variable
Expected Case !   Worstcase 1   Best Case

100                   105             70        110  Triangular
250                   255            245        265  Normal
25,000             26,775         17,150     29,150
(12,500)         (13,000)       (14,300)   (14,586)
4%            10%         2%  Triangular
(10,000)         (10,200)       (10,600)    (9,800)  Normal
2,500               3,575        (7,750)      4,764
```

Running a simulation of all the possible out comes gives an idea of the likelihood of different profit outcomes. As Figure 2 shows, while the most likely outcome is that the business makes a profit, there is a 44 per cent chance of the business being loss - making, given asymmetric downside risk. …

Search by...
Show...

### Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.