# Profit Projection

Document Sample

```					Introduction to Monte
Carlo Simulation

Jim Stuart
jestuart@eastman.com
Agenda
•   Definition
•   Areas of Application
•   Example Problem Setup
•   Monte Carlo in basic Excel
•   Monte Carlo in Minitab
•   Monte Carlo in Excel with @Risk
•   Interpreting Results

2
Definition
• Monte Carlo simulation is named after the city in
Monaco, where the primary attractions are
casinos that have games of chance. Gambling
games like roulette, dice, and slot machines,
exhibit random behavior.
• A problem solving technique used to approximate
the probability of certain outcomes by running
multiple trial runs, called simulations, using
variables demonstrating random behavior.

3
Earliest Large-Scale Application
The Manhattan Project
• Scientists were faced with intractably difficult equations to
solve in order to calculate the probability with which a
neutron from one fissioning Uranium1 atom would cause
another to fission.
• The geometry of the bomb itself complicated the problem.
Formulas alone were insufficient.
• They realized they could follow the trajectories of individual
neutrons in the bomb, and for each “leg”, used a team of
humans with mechanical calculators to compute the
probability of absorption, escape, or starting a new fission
reaction.

4
Common Applications
• The recently completed Supervisory Capital Assessment Program
(a.k.a “Big Bank Stress Tests”) identified the potential losses,
resources available to absorb losses, and resulting capital buffer
needed by the nation’s 19 largest banks.
• Most large corporations use simulation to estimate both the
average return and the riskiness of new products.
• Estimation of corporate performance such as forecasting net
income, predicting structural costs and purchasing costs, and
determining susceptibility to different kinds of risk.
• Drug companies use simulation to determine the optimal plant
capacity that should be built for each drug.
• Wall Street firms use simulation to price complex financial
derivatives and determine the Value at RISK (VAR) of their
investment portfolios.
• International companies use simulation to model and optimally
hedge foreign exchange risk.
5
Common Applications, cont’d
• Retailers use simulation to determine how many units of each
product line should be ordered from suppliers.
• Simulation can be used to value "real options," such as the
value of an option to expand, contract, or postpone a project.
• Financial planners use Monte Carlo simulation to determine
optimal investment strategies for their clients’ retirement.
• Used extensively in the conduct of environmental risk
assessments.
• Mathematicians use simulation in situations in which
computational approaches are “inconvenient”.
• Six Sigma belts use simulation of the X’s in a transfer function
to study the impact on the variability of the Y.
• What-if analysis of any model-able process involving
significant variability.
6
General Concept
• Have multiple inputs which are random in their
behavior – may be discrete or continuous
• The inputs translate into outputs, which will also
be random
• Often have cascading outputs which are a
function of the random inputs, and intermediate
outputs
• Relationships between variables are potentially
complex
• Objective is to understand the range of outcomes
for the outputs

7
An Example - Mathematically
• Y1= f(X1, X2, X3) – X1, X2 and X3 are random

• Y2= f(X1, X2, X4) – X1, X2 and X4 are random

• Y3= f(Y1, Y2, X5) – X5 is random, as are Y1, Y2
due to the randomness in X1, X2, X3 & X4

8
Visually
X1
Y1
X2
X3
X4       Y2
Y3
X5

9
What We Must Know
• “f” - The structural relationship between the
Xs and Ys (or upstream vs downstream Ys)
• D(x) - The nature of the variability of the Xs
• Corr(xi, xj) - Whether the Xs are independent
of one another

10
“f” – Structural Relationship of the Xs
to the Ys
• May be deterministic
– Financial analysis
– Engineering principles, etc
• Often empirically estimated
– Designed experiments
– Historical data analysis

11
Example: Profit Projection Analysis
•   X1: North America Forecast Sales (# Units)
•   X2: Canada Forecast Sales (# Units)
•   X3: Oil Price (\$/BBL)
•   X4: Demand Price Adder (Y=1, N=0)
•   X5: Standard Unit Cost (\$/Unit)
•   Y1: Actual Total Sales (# Units)
•   Y2: Actual Unit Sales Price (\$/Unit)
•   Y3: Net Profit

Y1=X1 + X2 + 0.00043*(X1+X2)*X3
Y2=38 + 0.0075*(X1+X2)*X4
Y3=Y1*Y2 - Y1*X5                         12
Example: Profit Projection Analysis
Term           X1        X2        X3        X4       X5       Y1       Y2         Y3
Demand                    Actual
NA Fcst Canada                       Cost per Actual
Descriptor                     Oil Price    Price                   Unit Sales Net Profit
Sales Fcst Sales                      Unit Units sold
Units        # Units   # Units   \$/BBL       Y/N    \$/Unit   # Units   \$/Unit      \$
Best Guess     550       350     \$55.00       1     \$37.00     921     \$44.75    \$7,138

Y1=X1 + X2 + 0.00043*(X1+X2)*X3
Y2=38 + 0.0075*(X1+X2)*X4
Y3=Y1*Y2 - Y1*X5
13
Best Guess Results
NA Fcst                                  Total Units
550         X1                             921
CAN Fcst                            Y1
350         X2
Oil Price
\$55         X3                     Sales Price

Y         X4                     Y2
Unit Cost                                               Y3 Net Profit
\$37.00 X5                                                   \$7,138
Y1=X1 + X2 + 0.00043*(X1+X2)*X3
Y2=38 + 0.0075*(X1+X2)*X4
Y3=Y1*Y2 - Y1*X5
14
How Good Are Our “Best Guesses”?

15
How Likely is the Best/Worst Case?
• What were criteria for the Hi/Low guesses?
– 90/10th Percentile?
– 95/5th Percentile?
– Maximum/Minimum values?
• Are the Xs independent of one another?
– Logical that all can be Hi/Low simultaneously?

16
Assuming the Xs Are Independent…
• The probability that the Best/Worst case would
be the predicted value or more favorable/bad is:
– 90/10 => 0.1(# Xs)
• Our example: 10 in million chance we Make \$19,776 or more
or Lose \$5,496 or more
– 95/5 => 0.05(# Xs)
• Our example: 0.31 in a million chance we Make \$19,776 or
more or Lose \$5,496 or more
– Max/Min => ???
• What will you tell management?

17
The Range of Possibilities
• With truly continuous Xs, the probability of
any specific value is infinitely small
• Have to view the results as a continuum
• Need to express the inputs as a continuum
– Best Guess anchors the shape
– Estimating Hi/Low extremes bounds the shape
– History & tribal knowledge guide the shape itself

18
Common Distributional Shapes
•   Normal Distribution: N(mean, sigma)
•   Uniform: U(Min, Max)
•   Triangular: Tri(Min, Mode, Max)
•   Discrete: Values:{v1, v2, vn}, P{p1, p2, pn}
– Where p1 + p2 + pn = 1
• Many more to choose from…

19
Monte Carlo in Basic Excel
• Can generate Uniform random values using
RANDBETWEEN function
• Options increase if using the Analysis Toolpack
–   Uniform
–   Normal
–   Bernoulli
–   Binomial
–   Poisson
–   Patterned
–   Discrete

20
Example with Excel Solution

Immediate observations?
21
Excel Variation Results, X1 Normal(550, 60)

22
Excel Variation Results, X2 Uniform(150,500)

23
Excel Variation Results, X3 Normal(55,5)

24
Excel Variation Results, X4 Discrete({1,0}, {.7,.3})

25
Excel Variation Results, X5 Uniform(31,50)

26
Excel Variation Results, Y1

27
Excel Variation Results, Y2

28
Excel Variation Results, Y3

29
Monte Carlo in
Minitab
• Many more
distributional
shapes to choose
from!

30
Minitab Variation Results, X1 Normal(550, 60)

31
Minitab Variation Results, X2 Triangular(150,350,500)

32
Minitab Variation Results, X3 Normal(55,5)

33
Minitab Variation Results, X4 Discrete({1,0}, {.7,.3})

34
Minitab Variation Results, X5 Triangular(31,37,50)

35
Minitab Variation Results, Y1

36
Minitab Variation Results, Y2

37
Minitab Variation Results, Y3

38
Monte Carlo Using @Risk Add-in to Excel
•   Multiple similar products on the market (Crystal Ball)
•   Can stay in Excel but flexibly apply Monte Carlo
•   Still more distributional shapes for inputs
•   Statistics/graphs on outputs are excellent

39
Monte Carlo Using @Risk Add-in to Excel
• Define input cells and
their distributional
form
• Define output cells to
track results of
• Choose number of
iterations/
simulations
• Many technical
controls around
sampling, etc.
• Many ways to
interpret the results
and determine the
key drivers

40
Verifying the Correlation in Sales Forecast Values
Told @Risk that X1 and X2 should have a correlation of 0.8

41
@Risk Results, Y1

42
@Risk Results, Y2

43
@Risk Results, Y3

44
• Provides a quick prioritization of the inputs
• Looking at how the variation in the inputs drives the variation in
the outputs

X3 contribution is insignificant                 45
• Results indicate volatility in unit cost is primary profit driver
• What if we could reduce that variation by 30%
• Physically change the Max/Min on the unit cost distribution
from Pert(31,37,50) to Pert(34,37,46)

46
• Unit cost is no longer the most important driver
• Overall profit picture improved “some”

X5~Pert(31,37,50)

X5~Pert(34,37,46)

47
Wrap-up
• Monte Carlo simulation is easy to do
• Leverages graphical results/interpretation
• Can use to communicate the range of results
that might be achieved
• Useful for exploring the possible impact of
process improvements before they are made

48

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 22 posted: 7/12/2011 language: English pages: 48
Description: Profit Projection document sample
How are you planning on using Docstoc?