Working In Uncertainty

Coordinating predictions from different people

Contents

A helpful, but largely unsung, revolution has taken place in business management over the last two decades or so. Today we tend to do more to consider alternative possible outcomes when making plans and decisions at work. For example, if you are launching a new product to be made in batches and need to decide how many to make in the first batch, many people today would run the numbers to see the implications of selling more or less than the number thought most likely. Why do we do this? Firstly, because we think it's a good idea. Secondly, because these days it is easy to do thanks to spreadsheet software. Thirty years ago doing the numbers several times was a chore and a complicated forecast might take hours to work out just once (including checking for arithmetic mistakes). Today, we just type in alternative numbers then look to see what difference it makes.

Nobody knows the impact of this on the overall risk management capability of modern managers. I suspect it has been gradual but significant.

This article is about how to delegate this 'what if' thinking and overcome a subtle but important error that can creep in if you don't manage it. The problem arises when different people make forecasts (or any other kind of prediction) for their part of an organization.

The coordination problem illustrated

Imagine a business with three divisions, A, B, and C. Each is asked to forecast its profit for the year ahead and each does so separately, to the best of its ability, providing a best guess and a range to give some understanding of the uncertainty involved. Imagine these are their forecasts:

  Division A Division B Division C TOTAL
Best estimate £11m £18m £4m ?
Range £9m - £14m £15m - £19m £3m - £5m ?

So what is the overall forecast, with a range? That's a tricky one. Is it even safe to add up the best estimates? The range surely depends on what the divisions do. For example, what if the business is a car dealership and Division A sells new cars while Division B sells used cars. When the economy is good, new car sales are strong but, when the economy is weak, used car sales may actually rise as new car sales fall. In contrast, if the divisions all sell luxuries to middle-income customers then a weak economy may hit all divisions at the same time.

(In addition, you might also have some worries about the way each division has produced its estimate and range. What scenarios did they think about, if any? How did they arrive at their best estimate and their range?)

The main problem, however, is one of coordination. The forecasts were produced separately and so it is hard to know how to combine them. How can you coordinate the forecasts but still delegate/decentralise the forecasting?

A simple solution

A good way to tackle the coordination problem is to give each division the same list of scenarios to consider and ask them for their best forecast in each one. Using the Divisions in the previous illustration, their forecasts might be as follows:

  Division A Division B Division C TOTAL
Scenario 1: Recession £9m £15m £5m £29m
Scenario 2: Slow growth £11m £18m £4m £33m
Scenario 3: Rapid growth £14m £19m £3m £36m

You can see that Division C's results move in opposition to the general economy, but its contribution is small compared to the other divisions, making this harder to see without looking at it in this coordinated way.

While you could describe each scenario to the divisions by just saying 'Recession', 'Slow Growth', and 'Rapid Growth', that seems a bit vague. It would be better to give them some numbers for major economic indicators (or whatever are the most important indicators of the business environment) that convey more precisely what is meant by each scenario. This is where spreadsheet power comes in.

The divisions should realise that their easiest way to provide the forecasts is to make a little model on a spreadsheet that uses your scenario description numbers as some of its main inputs, and responds to them, producing forecasts that vary with your inputs (among other things). That means that if, at Head Office, someone thinks of another scenario and wants forecasts for that too then the Divisions can easily provide the new forecasts.

If you have any experience of spreadsheeting then you can probably imagine how this might be done and how it would coordinate forecasts.

This is a simple way to do something important and useful. However, there's more.

Probability Management

The name 'Probability Management' refers to an approach to coordinating business forecasts that takes this simple approach to a higher level, producing some impressively quick and informative models that beautifully express the uncertainty involved. An organization to promote these ideas has been created (www.ProbabilityManagement.org) and its board of directors includes Sam Savage and Harry Markowitz. Corporate supporters of this organization include (at the time of writing) Chevron Corporation, Computer Law LLC, Foundation for Creativity in Dispute Resolution, General Electric, Lockheed Martin, Ortec Consulting Group, and Wells Fargo.

The website has some beautiful, downloadable examples of models, cleverly exploiting functionality in Microsoft's Excel product that you might not even know exists. They have achieved decentralised but coordinated Monte Carlo simulation using no special software, but providing fabulous graphical dashboards and blinding speed.

If we set aside for a moment the clever uses of software, Probability Management adds just one more idea on top of the coordinating power of scenarios. That idea is that a very large number of scenarios (e.g. thousands) with numbers that are representative of our uncertain beliefs can be used to compute the implications of our uncertainty around inputs for the uncertainty around outputs forecast.

Here's a simple illustration of how that works. Imagine that you are forecasting the total sales of two products over the next year. The total sales will be just the total of the sales of each product. However, you don't know for certain what the sales of each product will be. Now imagine that a list of numbers representing possible sales levels for each product is created, somehow, so that likely sales levels appear more often in the list than unlikely sales levels. More than that, a histogram of the distribution of the numbers closely matches your views about how likely different levels of sales are.

(We'll consider how such a list of numbers might be created a little later.)

On a spreadsheet it might look something like this:

Ref Product A sales (£) Product B sales (£) TOTAL sales (£)
1 200,000 400,000 600,000
2 210,000 450,500 660,500
3 215,100 440,600 655,700
4 190,000 390,000 580,000
... ... ... ...
10,000 195,000 405,200 600,200

Notice that each total is just the sum of the two sales numbers on the same row. Could this be much simpler?

As long as the individual product sales numbers are distributed according to our beliefs about the true values then the TOTAL Sales numbers will also be distributed in a way that represents our beliefs about the true value of the total.

A fast and powerful way to do Monte Carlo simulation

If you are familiar with Monte Carlo simulation you will recognize a lot of this. The main difference with Probability Management is that random numbers for many of the input distributions to a model are calculated or gathered in a separate exercise, before the model is run. Then, when the model is run, all the trial results are saved as a table, to be combined or reused in other models perhaps. Advantages include these:

  • Coordination: As explained above, giving each forecasting team a consistent set of scenarios to forecast for means that their forecasts can be consolidated easily, capturing all those relationships between them.

  • Non-duplication of work: At least some of the work of expressing uncertainty about the future value of important variables is done just once and then shared rather than being a separate task for everyone who does a forecast. The person who makes the shared distribution can be an expert who also helps others use them.

  • Understanding: Looking at the table of thousands of results in thousands of scenarios makes it much easier to understand how the Monte Carlo simulation has been done. Many analyses are possible, obviously. For example, you can sort and filter the results to study all the occasions where particular things happened. (Sam Savage has a good joke about this but I won't spoil it by trying to tell it.)

  • Ordinary software only: Ordinary Excel can do it all. The Probability Management experts have shown that even Excel 2003 can do it, while later version of Excel have added bigger worksheets and some cute graphics.

  • Speed: Removing the random number generation part of Monte Carlo simulation (by doing it earlier) means that the simulation itself runs faster.

  • Using optimisation features of spreadsheets: Since ordinary Excel is all that is needed, the Solver tool can be used with the simulation. Solver searches iteratively for better and better answers to problems you define. It can do a whole Monte Carlo simulation in each iteration using the methods explained by Probability Management.

Creating the lists of numbers to use as inputs

How can the lists of data values be created for use in these models? The two main ways are (1) to use real, historical data values, and (2) to generate pseudorandom numbers according to probability distributions.

Using past data means that you do not need to understand how or why values of different variables are related. You just record them all and each set of records is another scenario for your list. Unfortunately, this is only possible when you have past data you can use and it also means that your simulation will never consider a situation that has not happened in the past.

Using synthetic, generated numbers means that you can make lots of them even if you have no past data and they can cover many scenarios that have never happened in the past. However, you need to understand a bit more mathematics and know how to use more spreadsheet formulae (or other tools) to generate the appropriate random numbers. Almost any pattern of random numbers can be generated if you know how. Last Christmas I created a two dimensional probability distribution that spelled out the letters of a Christmas greeting, then generated random numbers using it that scattered snowflakes over the distribution, gradually drawing out the greeting on the screen. Usually you just need the RAND() function combined with an inverse probability distribution, such as INVNORMDIST().

There are plenty of finer points, such as how to get all your number lists to be the same length and how to keep them in one standard format as a library, and I recommend a visit to www.ProbabilityManagement.org for more detail.

Finally

The basic idea we started with is that you can coordinate forecasts by several people by giving them each the same standard list of scenarios and asking for their best forecast for each scenario. Using scenarios is a common idea, made famous by scenario planning methods but also illustrated by Russ Vane's approach to military planning workshops.

It seems that it is often, though not always, easier for people to make forecasts given basic scenarios that carve up the possible futures than to make forecasts without such a structure.

Professor Savage and his colleagues at Probability Management have shown that using many pre-generated scenarios is a good way to calculate with your uncertainty too.






Made in England

 

Words © 2013 Matthew Leitch