1 uncertain numbers and diversification
DESCRIPTION
TRANSCRIPT
Judge Business SchoolJudge Business School
(name)
(date)
THE LANGUAGE OF UNCERTAINTY I
Management Science Group
Four basic questions for the next 3 sessions
1. What is an uncertain number?
2. What happens when uncertain numbers are combined in our plans?
3. What happens when uncertain numbers in business plans are replaced by projections (aka common practice)?
4. Uncertain numbers can depend on one-another. What effect does that have on our plans?
Page 2
Management Science Group
Today
1. What is an uncertain number?
2. What happens when uncertain numbers are combined in plans?
PLUS
Introduction to Monte Carlo simulation in Excel
Page 3
Management Science Group
1. What is an uncertain number?
Page 4
Management Science Group
1. What is an uncertain number?
An uncertain number has a range of possible outcomes
To allow for the fact that some outcomes might be considered more likely than others, we use weights to reflect our judgement of these relative likelihoodsThese weights are called the probabilities of the outcomes (between 0=won’t happen and 1=will happen for sure)
Recall the 3 interpretations of “probability”
Page 5
Management Science Group
Representing uncertain numbers
HISTOGRAM• An uncertain number is best represented by a bar chart• Bars indicate specific regions within the range of outcomes• Height of bars indicating the probabilities that the outcome
will be in the respective region
Page 6
Management Science Group
Example: A histogram of uncertain revenues
Page 7
What is the chance that revenues will be between $9M and $10M?
• Your mark on this course is uncertain – create a histogram
• What is the range for this uncertain number?
• What is a sensible histogram shape for this uncertain number?
Page 8
How to create a histogram
1. Decide on a sensible range
2. Create scenarios: Chop range into a few sub-ranges of the SAME SIZE
3. Draw the histogram: Depict the relative likelihoods of any one of the scenarios as height of a bar in a bar chart
Exercise
Scenarios
Associated probabilities
• Pick another uncertain number that is relevant to your life and create a histogram
• What would be a reasonable range for this uncertain number?
• What is a sensible histogram shape for this uncertain number?
Page 9
How to create a histogram
1. Decide on a sensible range
2. Create scenarios: Chop range into a few sub-ranges of the SAME SIZE
3. Draw the histogram: Depict the relative likelihoods of any one of the scenarios as height of a bar in a bar chart
Exercise
Scenarios
Associated probabilities
Management Science Group
Target probabilities…
Page 10
What is the chance of achieving a target of $8M?
Management Science Group
Turning a histogram into a target curve
Page 11
Add up probabilities (stack up blue bars) to the left
Management Science Group
Turning a histogram into a target curve
Page 12
Probability of missing a $8M target is about 45%Probability of achieving the target is about 55%
Management Science Group
Bar chart is often turned into a curve: the target curve
Page 13
The target curve is also known as the “cumulative distribution function”
Management Science GroupPage 14
Probability of missing a $8M target is ~45%
The target curve is also called “cumulative distribution function”
Bar chart is often turned into a curve: the target curve
Management Science Group
Crucial Concept # 1
Page 15
An uncertain number is a shape
Stats-Latin: Statisticians call the shape of an uncertain number its “distribution”Statisticians call the target curve the “cumulative distribution function”
Summary statistics are numbers derived from the shapeI. The Average
Mean = Average
The average is the point where the histogram, if it were made of wood, would balances out
Not the balance point
Summary statistics are numbers derived from the shapeI. The Average
Mean = Average
The average is point where the downside (left of the average) “weighs” as much as the upside (right of the average)
Too much weighton downside
Average = balance point
Summary statistics are numbers derived from the shapeI. The Average
Mean = Average• For data: Average = Sum of data divided by the number of data points
• Formal definition: 1. multiply all possible realizations of the uncertain number2. sum up all these probability-weighted possible realizations
• Example: average of dice 1*1/6+2*1/6+…+5*1/6+6*1/6=3.5o Interesting: The average of an uncertain number is not necessarily a possible realisation of
this uncertain number, as in the dice example
• Approximation from histogram1. multiply the mid-point of each bin with the height of its bar2. sum up all these probability-weighted mid-points
Summary statistics are numbers derived from the shapeII. Percentiles
xth Percentile: x% of the data are below this value
Easily read off target curve (=percentile graph)
60th percentile = $9M
60% chanceThat revenueIs below $9M
The target curve (or“cumulative distribution function”) is also called “percentile curve”
Summary statistics are numbers derived from the shapeIII. QuantilesDeciles: chop the y-axis (0%- 100%) into 10 equal intervals Separating
points on the x axis are the 10 deciles 10
4th decile is about $7.5M
Chopping the y-axis into a different number intervals lead to other quantiles
chopping it into 4 intervals give “quartiles”, what’s the third quartile?
chopping it into 5 intervals leads to “quintiles”, what’s the third quintile?
chopping it into 100 intervals gives the percentiles
What’s the “upper decile sales performance?”
Summary statistics are numbers derived from the shapeII. Median
• Median = 50th percentile People often mix up median and mean. Vignette to help you memorize the difference: When Bill Gates enters this lecture room, the median annual income won’t change much – but the mean will increase dramatically
• Lower quartile = 25th percentile, upper quartile = 75th percentile
• Inter-quartile range = range from lower to upper quartile
• Common abbreviations: P10 = lower decile (10th percentile)P90 = upper decile (90th percentile)
Summary statistics are numbers derived from the shapeIII. Mode
Mode = most likely value (or most likely bin in histogram), if there is one
Mode: $6M-$7M
Homework:Make sure you understand the difference between mean, median and mode
They are the same only in special cases (e.g. bell-shape distribution)
Data has a shape as well…
Open EasyBedsData.xls
Use the percentile function in Excel to produce a target curve for
Number of enquiries
What’s the percentage of days with enquiries between 1000 and 1500?
Management Science Group
Histograms from data…
… are a bit trickier to produce
Homework: Produce histograms for
Number of enquiries
Bookings completed
Daily Revenue
Number of No-shows
There are plenty of tutorials on this on the web…
Page 24
Management Science Group
Further practice on histogramsI will describe an uncertain number to you
Please draw a histogram of this uncertain number
You are managing a business unit. Next year’s profit is uncertain. Suppose it can range between £0 and £1M
Suppose the uncertainty is resolved by me spinning a wheel of fortune with equally spaced numbers 0,1,…,1M
Today, before the wheel of fortune is spun, the revenue is uncertain.
What is the shape of this uncertain revenue?
Draw a histogram with 5 bins
Page 25
Management Science Group
The wheel of fortune uncertainty has a flat shape between 0 and 1M
Page 26
Management Science Group
The spinner function in @RiskThe @Risk software package on your laptop has a spinner function - “=riskuniform(0,1)” and many other shapes
If you hit the little button with the picture of the two dice on the Simulation tab, it “wakes up” the uncertain number (which is reset to its average if you hit the button again): Every time you change a cell in the spreadsheet – or hit F9 – it produces a different number in this cell
THE EXCEL DEFINITION OF AN UNCERTAIN NUMBER:
AN UNCERTAIN NUMBER IS A NUMBER IN A MODEL THAT CHANGES EVERY TIME YOU HIT F9
You can use @Risk to “hit the F9 key” 10,000 times and record the results, so that you can check the shape of the uncertain number
Go on the cell with the “=riskuniform(0,1)” formula and hit the “Add Output” button; then increase “Iterations” to 10,000 and hit the “Start Simulation” button
Page 27
Management Science Group
Histogram of 10,000 simulation trials
Page 28
Roughly – but not precisely – 2,000 results in each bin
Why don’t we find precisely 2,000 results in each bin? If you rolled 6000 dice, would you expect precisely 1,000 1’s, 2’s,…?
Management Science Group
So...
An uncertain number is a shape
Histogram or target curve
An uncertain number cell in Excel is a number that changes when F9 is hit
We can use Monte Carlo simulation to check the shape of any uncertain number cell
Page 29
Management Science Group
Practice continued…
Now, next year’s uncertain profit of your unit is the sum of the profits of two divisions
The revenue for each division ranges between £0 and £0.5M
For each division, the uncertainty is resolved by a spin of a wheel of fortune (each division has its own wheel of fortune)
Today, before the spinners are spun, the revenues are uncertain.
What it the shape of the uncertain revenue of each division?
What is the shape of the uncertain revenue of the company as a whole?
Draw rough histograms with 5 bars
Page 30
Management Science Group
Monte Carlo Simulation
Traditional models: Numbers in numbers out
Monte Carlo Simulation models: Shapes in shapes out
Monte Carlo works like this: 1. Specify shape for each uncertain input
@ Risk: Put a “=riskXXX” fomula in the input cell
2. Specify the output cells of interest to you
3. Specify how often you want to “roll the dice” (the number of Monte Carlo trials) and
4. For each Monte Carlo trial:
- Roll the dice for all uncertain input cells (“Press F9”)- Store the realizations of your uncertain inputs and the corresponding calculation of your specified outputs in one row of a “results spreadsheet”
5. Repeat this for the specified number of trials (1000 - 10,000) store many rows of MC records, one for each trial (“F9 hit”)
6. Produce the shape of the output in the MC records spreadsheet
Page 31
Management Science Group
Generating the shape of a 2-division firm
Total profit = profit division 1 + profit division 2
Profit division 1 and division 2 are uncertain numbers
Generate input shapes
Profit division 1: 10,000 trials of =RiskUniform(0,1)*500
Profit division 2: 10,000 trials of another =RiskUniform(0,1)*500
Generate output cell
Add the input trials up, trial by trial, to get 10,000 trials of total profit
To get a target curve: Right-click on histogram graph, go to “Distribution Format”, choose “Cumulative Ascending”
Page 32
Management Science Group
Recall the four basic questions
1. What is an uncertain number?
2. What happens when uncertain numbers are combined in our plans?
3. Uncertain numbers can depend on one-another. What effect does that have on our plans?
4. What happens when uncertain numbers in business plans are replaced by projections (aka common practice)?
Page 33
Management Science Group
Crucial Concept # 2
Page 34
When uncertainties are combined the shape goes up in the middle
+
=
Management Science Group
So what? – Here is your choice….
Page 35
In both cases revenues range between 0 and $1MIn both cases the expected revenue is $0.5M
Management Science Group
Page 36
Which shape would you prefer ifyour were sure to get fired
if you achieve less than $200k?
Management Science Group
What is the chance of meeting a 200k target?
Page 37
Management Science Group
Page 38
Which shape would you prefer ifyour contract promises a huge bonus
if you achieve at least $800k?
Why did the shape go up in the middle?
Management Science Group
(Picture courtesy of Analycorp.com )
Page 39
Management Science Group
Diversification
Extreme outcomes become less likely
You “buy” a reduction of downside risk and pay for this through a reduction of upside opportunity
Diversification works for all kind of shapes, not just flat ones
The peaking increases the more shapes you add up
Let’s check this with Monte Carlo simulation
Page 40
If you add up uncertain numbers, the shape of the sum is more peaked in the middle than the
individual shapes
The Central Limit Theorem
Fundamental mathematical result:
When you add up many (independent) uncertain numbers, then the resulting uncertain sum has a bell shape (“normal distribution”)
Page 41
Will discuss the concept of “independent” later
Some more statistics-Latin: What’s the “standard deviation”? The 66,95,99% rules
KEY: The standard deviation makes only intuitive sense in the case of a bell-shape (normal distribution)
If an uncertain number has a bell shape then there is about
- 66% chance that the result will be within one standard deviation of the mean
-95% chance that the result will be within two standard deviations of the mean
- 99.7% chance that the result will be within three standard deviations of the mean
Target curve for a normal distribution with mean = 2000 and standard deviation = 50
1950 = mean – 1 * stdev = 17th percentile2050 = mean + 1* stdev = 83rd percentileHence 66% of data is within 1 stdev of mean
1900 = mean – 2 * stdev = 2.5th percentile2000 = mean + 2* stdev = 97.5d percentileHence 95% of data is within 2 stdev of mean
Key learning points
Crucial Concept # 1: Uncertain numbers are shapes Range of outcomes Probability of landing in a particular region of the range Represent uncertain numbers by histograms and / or target
charts
Crucial Concept #2: When uncertain numbers are combined the shape goes up in the middle
Probability of extreme events (both sides) goes down
Crucial Technology: We can calculate with shapes just as with numbers, using Monte Carlo Simulation
Page 44
Management Science Group
Individual workMake yourself familiar with @Risk, using steps 1-3 and 4a of the tutorial at http://www.palisade.com/risk/5/tips/en/gs/
Page 45
Step 1: Quick StartStep 2: ModelStep 3: SimulateStep 4.a: Histograms and cumulative curves
Management Science Group
Group work
Perform Group Activity Session 5
Page 46