Transcript overhead - 07 Developing Simulation Modelsx

Materials for Lecture
• Chapter 2 pages 6-12, Chapter 6,
Chapter 16 Section 3.1 and 4
• Lecture 7 Probability of Revenue.xlsx
• Lecture 7 Flow Chart.xlsx
• Lecture 7 Farm Simulator.xlsx
• Lecture 7 Uniform.xlsx
• Lecture 7 Theta UPES.xlsx
• Lecture 7 View Distributions.xlsx
Brief Explanation of How
Simulation Works
• Examples how random numbers are
generated
• Uniform Distribution
• Inverse Transform to simulate any
distribution
• Standard Normal Distribution
• Normal distribution for simulating a
forecast
– Forecasts can come from Mean or OLS
Step 1 for Simulation
• Generate a Uniform Standard Deviate (USD)
=UNIFORM(0,1)
Simetar simulates 500 values
These are called iterations
They are 500 samples or draws
USD = UNIFORM(0,1)
Prob
CDF for Uniform(0,1)
0.12
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
0.1
0.08
0.06
0.04
0.02
0
0.2
0.4
0.6
0.8
1
0
0.00
0.13
0.25
0.38
0.50
0.62
0.75
0.87
• Equal chance of observing a number in each of the intervals; both
charts are for the same output
1.00
USD Output in SimData
• Simetar saves the 500 samples in
SimData and calculates summary statistics
Simetar Simulation Results for 500 Iterations. 9:36:20 AM 2/17/2013 (1 sec.). © 2011.
Variable Sheet1!B7
Mean
0.499985
StDev
0.288988
CV
57.79939
Min
0.000895
Max
0.999165
Iteration USD
1 0.512793
2 0.307316
3 0.581277
4 0.787495
5 0.94209
6 0.735971
7 0.048923
8 0.23733
Step 2 in Simulation
• Use the 500 USDs to simulate random
variables for your Ŷ variable
• This involves translating the USDs from a
0 to 1 scale to the scale for your random
variable
• This is done using the Inverse Transform
method shown on the next slide.
Step 2 Inverse Transform
• The 500 USDs are converted from 0 to 1
scale to the Y scale by direct interpolation
• Each random USD is associated with a
unique “random” Y value to get 500 Ỹs
USD or F(x)
1.00
0.90
0.80
0.70
0.60
0.50
0.40
0.30
0.20
0.10
0.00
55.00
CDF of a Random Variable
60.00
65.00
70.00
75.00
Step 2 Inverse Transform Cont.
• Results of 500 iterations for Y using
Inverse Transform
Simulation Results for 500 Iteratio
• USDs and their resulting Ỹs Simetar
Variable Sheet1!G33
Sheet1!G34
Mean
0.499985 65.19666
StDev
0.288988 3.136123
CV
57.79939 4.810251
Min
0.000895 56.38011
Max
0.999165 74.43161
Iteration USD
Y-Tilda
1 0.512793 65.22607
2 0.307316 63.61534
3 0.581277 65.7939
4 0.787495 67.72464
5 0.94209 70.20308
6 0.735971 67.17892
7 0.048923 60.03664
8 0.23733 62.91843
9 0.955568 70.68873
10 0.634662 66.23654
Step 3 Simulate Normal Distribution
• Parameters for a Normal Distribution
– Mean or Ŷ from OLS
– Std Dev or σ of residuals
• Simulated using the formula
Ỹ = Ŷ + σ * SND
Where the SND is a “standard normal deviate”
We generate 500 SNDs and thus simulate
(calculate) 500 random Y’s
Step 3 Generating SNDs
• Generate 500 USDs and transform them to SNDs using
the Inverse Transform
• SND’s have mean of Zero and range from ≈ +/- ∞
• SNDs are the “number of standard deviations from the
mean” or the number of σ’s Ỹ is from the Ŷ or Ῡ
Convert 500 USDs to Unique SNDs
Draw 500
Random USDs
-3.2
-2.2
-1.2
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
-0.2
0.8
1.8
2.8
Step 3 Simulate Normal Distribution
• Next apply the random SNDs in the
Normal distribution formula
Ỹ = Ŷ + σ * SND
In Simetar all of these steps are done for
you: =NORM(Ŷ, σ)
or
= NORM(Ŷ, σ, USD)
• Next problem is where to get Ŷ and σ ?
– In forecasting we estimate
Ŷ = a + bX1 +bX2 or another OLS equation
σ = Std Deviation of residuals
Normal Distribution: Simetar
Code and Output
• The USD is used to calculate the SND
• The SND is used to simulate Y-Tilda
• Simetar gives same result in one step
Simetar Simulation Results for 500 Iterations. 7:56:32
Variable Sheet1!B47Sheet1!B48Sheet1!B49Sheet1!B50
Mean
0.499985 -0.00015 65.48175 65.48175
StDev
0.288988 1.001471 3.946465 3.946465
CV
57.79939 -650265 6.026817 6.026817
Min
0.000895 -3.12303 53.1755 53.1755
Max
0.999165 3.143506 77.86988 77.86988
Iteration USD
SND
Y Tilda
Simetar
1 0.512793 0.032072 65.60874 65.60874
2 0.307316 -0.50347 63.49834 63.49834
3 0.581277 0.20516 66.29082 66.29082
4 0.787495 0.797758 68.62605 68.62605
5 0.94209 1.572561 71.6793 71.6793
6 0.735971 0.630975 67.96882 67.96882
7 0.048923 -1.65539 58.95901 58.95901
Simulation Models
• A Model is a mathematical representation of any
system of equations
– When you think through the many steps to solve a
problem you are constructing a model
– When you think or plan your way through a complex
situation you are making a virtual model
– Computer games are models
– Econometric equations can be a model
• We build models so we do not have to
experiment on the actual economic system
– Will the business be successful if we change
management practices, etc.?
Developing Simulation Models
•
•
•
•
Organization of a model in an Excel Workbook
Steps for model development
Parts in a simulation model
Generating random variables from uniform
distributions
• Estimating parameters for other distributions
– Parameters are the numbers that define the center
and the dispersion about the center of the random
variable
– For a Normally distributed random variable, the
parameters are the Mean & Std Dev
– For Empirical ….
Organization of Models in Excel
Input Data, such as –
Costs, inflation & interest rates,
Production functions
Assets & liabilities
Scenarios to analyze, etc.
Historical Data for Random
Variables, such as –
Prices
Production levels
Other variables not controlled
by management
Equations to calculate variables –
Production, Receipts, Costs, Amortize
Loans, Update Asset values, etc.
Model Outputs:
Statistics for KOVs
Probability charts
Decision summarys
Final report tables
Tables to report financial results –
Income statement, cash flow, balance sheet
KOV Table –
List all output variables of interest
Organization of Models in Excel
• Sheet 1 (Model)
–
–
–
–
–
Assumptions and all Input Data
Control variables for managing the system
Logical flow of all calculations
Table of intermediate results
Table of final results – the Key Output Variables
(KOVs)
• Sheet 2 (Stoch)
– Historical data for random variables
– Calculations to estimate the parameters for
random variables
– Simulate all random values
• Sheets 3-N (SimData, Stoplite, CDF)
– Simulation results and charts
Model Design Steps
KOVs
Design
Intermediate Results
Tables and Reports
Build
Equations and Calculations to
Get Values for Reports
Stochastic Variables
Exogenous and Control Variables
• Model development is like building a
pyramid
– Design the model from the top down
– Build from the bottom up
Steps for Model Development
• Determine the purpose of the model and KOVs
• Draw a sketch of how data will interact to
calculate the KOVs
• Determine the variables necessary to calculate
the KOVs
– For example to calculate Net Present Value (NPV)
we need:
• Annual net cash withdrawals which are a function of net
returns
• Ending net worth which is a function of assets and liabilities
– This means you need a balance sheet and a cash flow
statement to calculate annual cash reserves
– An annual income statement is needed as input into a cash
flow
– Annual net returns are calculated from an income statement
Example Flowchart of a Model
Flow Chart for Simulating NPV
Control Variables for Manager such
as: Levels of Production, Debt
Levels, Market Share
Macro Data as inflation rates
interest rates
Sections and Equations for the Model
Generate the Stochastic Values
Use Projected Means and Historical Data for Random Variables
Use the Stochastic Values in the Equations for the Model
Equations for the System to model
Production = f( scale of the farm and stochastic values)
Price = f( stochastic values)
Revenue = Price * Production for each enterprise
Variable Costs by Enterprise = Production * Unit Cost
Costs = Variable Costs for each enterprise + Fixed Costs
Net Returns = Revenue - Costs
Balance Sheet Information
Asset Valuation
Liabilities
Net Worth
Annual Projected Mean Prices
Key Output Variables
Net Present Value
Probability of Net Returns > 0
Probability of NPV > 0 ( or Prob of Success)
Probability of Increasing Real Net Worth
Analyze KOVs
Budgets for each of the
Enterprises
Stochastic Variables -- need the
historical data to estimate
parameters for random variables
Steps for Model Development
• Write out the equations by hand
– This organizes your thoughts and the model’s structure
– Avoids problem of forgetting important sections
– Example of equations for a model at this point:
•
•
•
•
•
Output/hour = stochastic variable
Hours Operated = management control value
Production = Output/hour * Hours Operated
Price = forecast mean each year with a risk component
Receipts = Price * Production
• Define input variables
– Exogenous variables are out of the control of management
and are deterministic; usually policy driven
– Stochastic variables management can not control and are
random in nature: weather or market driven
– Control variables the manager can manipulate
Steps for Model Development
• Stochastic variables (most time is spent here)
– Identify all random variables that affect the system
– Estimate parameters for the assumed distributions
• Normality – means and standard deviations
• Empirical – sorted deviates and probabilities
– Use the best model possible econometric model to
forecast deterministic part of stochastic variables to
reduce risk
• Model validation starts here
– Use statistical tests of the simulated stochastic
variables to insure that random variables are simulated
correctly
• Correlation tests, means tests, variance tests
• CDF and PDF charts to compare history to simulated values
Stochastic Variables?
• What are Stochastic Variables?
– Random variables we can not control, such as:
• Prices, yields, interest rates, rates of inflation, sickness, etc.
– Represented by the residuals from regression equations
as this is the part of a variable we did not predict
• Why include stochastic variables?
– To get a more robust simulation answer
– PDF rather than a single value
– We can assign probabilities to KOVs
– We can incorporate risk in our decisions
Simple Economic Model
• A Supply and Demand Model
– You learned there is one Demand and one Supply
– But there are many, due to the risk on the equations
Qx = a + b1Px +b2Y + b3Py gives a single line for Demand
Qx = a + b1Px +b2Y + b3Py + ẽ gives infinite Demands
– Now if Supply is a constant we get an infinite number
of Prices as we draw ẽ values at random
Price/U
Supply
Demand
Quantity/UT
Simple Business Model
• Profit is generally our Key Output Variable of interest
𝜋 = Total Receipts – Variable Cost – Fixed Cost
𝜋 = ∑(P~i * Ỹi ) - ∑(VCi * Ỹi * Qi ) – FC
~
Where Pi is the stochastic price for product i, as $/bu.
Ỹi is stochastic production level as yield or bu./acre
VCi is variable cost per unit of production for i, or $/bu.
Qi is the level of resources committed to i, as acres
Univariate Random Variables
• More than 50 Univariate Distributions in Simetar
–
–
–
–
–
–
–
Uniform Distribution
Normal and Truncated Normal Distribution
Empirical, Discrete Empirical Distribution
GRKS Distribution
Triangle Distribution
Bernoulli Distribution
Conditional Distribution
• Excel probability distributions have been made
Simetar compatible, e.g.,
– Beta, Gamma, Exponential, Log Normal, Weibull
Uniform Distribution
• A continuous distribution where each range has an
equal probability of being observed
• Parameters for the uniform are minimum and
maximum values and the domain includes all real
number’s
=UNIFORM(min,max)
• The mean and variance of this distribution are:
min  max

2


max  min 

2
2
12
PDF and CDF for a Uniform Dist.
Probability Density Function
f(x)
min
max X
Cumulative Distribution Function
F(x) 1.0
0.0
min
max X
When to Use the Uniform Distribution
• Use the uniform distribution when every range of length
“n” between the minimum and maximum values has an
equal chance of occurrence
• Use this distribution when you have no idea what type of
distribution to use
• Uniform distribution is used to simulate all random
variables via the Inverse Transform procedure and USD
For example
USD is used to
simulate a
Normal
Distribution
Uniform Deviate
1.0
USDi
0.8
0.6
0.5
0.4
0.2
-
3
0
SNDi
+ 3 Std. Normal Dev.
Inverse Transform for Generating a SND from a USD
Uniform Standard Deviate (USD)
• In Simetar we simulate the USD as:
=UNIFORM(0,1) or =UNIFORM()
– Produces a Uniform Standard Deviate (USD)
– Special case of the Uniform distribution
• USD is building block for all random number
generation using the Inverse Transformation
method for simulation. Inverse Transform uses
a USD to simulate a Uniform distribution as:
X = Min + (Max-Min) * Uniform(0,1)
X = Min + (Max-Min) * USD
Simulate a Uniform Distribution
• Alternative ways to program the
Uniform( ) distribution function
= Uniform(Min, Max,[USD])
= Uniform(10,20)
= Uniform(A1,A2)
= Uniform(A1,A2,A3) where a USD is calculated
in cell A3
Uses for a Uniform Standard Deviate
• USD can be used in all random number
formulas in Simetar to facilitate correlating
random variables
• For example in Simetar we can add USDs:
=NORM(mean, std dev, [USD])
=TRIANGLE(min, middle, max, [USD])
= EMP( Si, F(Si), [USD])
=EMP(values , , [USD])
• Note the [USD] means that USD is optional
Simulating Random Variables
• Must assume a probability distribution shape
– Normal, Beta, Empirical, etc.
• Estimate parameters required to define the
assumed distribution
• Here are the parameters for selected distributions
–
–
–
–
Normal ( Mean, Std Deviation )
Beta ( Alpha, Beta, Min, Max )
Uniform ( Min, Max )
Empirical ( Si, F(Si) )
• Often times we assume several distribution forms,
estimate their parameters, simulate them and pick
the one which best fits the data
Steps for Parameter Estimation
• Step 1: Check for the presence of a trend, cycle or
structural pattern
– If present remove it & work with the residuals (ẽt)
– If no trend or structural pattern, use actual data (X’s)
• Step 2: Estimate parameters for several assumed
distributions using the X’s or the residuals (ẽt)
• Step 3: Simulate the different distributions
• Step 4: Pick the best match based on
–
–
–
–
Mean, Standard Deviation -- use validation tests
Minimum and Maximum
Shape of the CDF vs. historical series
Penalty function =CDFDEV() to quantify differences
Parameter Estimator in Simetar
• Use Theta Icon in Simetar
– Estimate parameters for 16 parametric distributions
– Select MLE method of parameter estimation
– Provides equations for simulating distributions
Parameter Estimator in Simetar
• Results for Theta Estimate parameters for 16 distributions
– Selected MLE in this example
– Provides equations for simulating distributions based on a common USD
Univariate Parameter Estimation for Random Variable at 2/27/2012 9:01:56 PM
MOM Estimates
Random Variables
Distribution
Parameters Parm. 1
Parm. 2
Distribution
MOM
Beta
α, β ; A≤x≤B, α,β>0
1.896
2.117 Beta
200.607
-∞<α<∞, β>0
Double Exponential α, β ; α≤x<∞, 187.812
5.025 Double Exponential207.451
Exponential
μ, σ ; -∞<x<∞,
180.705
-∞<μ<∞, σ>0
7.107 Exponential
213.405
Gamma
α, β ; 0≤x<∞, 698.392
α,β>0
0.269 Gamma
204.728
Inverse Gaussian μ, σ ; 0≤x<∞, 187.812
μ>0, σ>0
0.003 Inverse Gaussian 205.160
Logistic
μ, σ ; -∞x<∞, 187.812
-∞<μ<∞, σ>0
3.918 Logistic
205.801
Log-Log
μ, σ ; -∞<x<∞,
184.614
-∞<μ<∞, σ>0
5.541 Log-Log
210.081
Log-Logistic
μ, σ ; 0≤x<∞, -∞<μ<∞,
47.975 σ>0
187.678 Log-Logistic
206.526
Lognormal
μ, σ ; 0≤x<∞, -∞<μ<∞,
5.235 σ>0
0.038 Lognormal
204.930
Normal
μ, σ ; -∞<x<∞,
187.812
-∞<μ<∞, σ>0
7.107 Normal
204.334
Pareto
α, β ; α≤x<∞, 180.969
α,β>0
27.446 Pareto
213.999
Uniform
A, B ; A≤x≤B 175.503
200.122 Uniform
199.874
Weibull
α, β ; 0≤x<∞, α,β>0
34.000 ############## Weibull
############
Binomial
n, p ; x=0,1,2,...,n;
256.000
0≤p≤1
0.729 Binomial
203
Geometric
p ; x=1,2,...; 0≤p≤1
0.005
Geometric
865
Poisson
λ ; x=0,1,...;187.317
0≤λ<∞
Poisson
220
Negative Binomial s, p ; x=1,2,...; 0≤p≤1
Negative Binomial
Common USD
0.99
Which is the Best Distribution?
• Use Simetar function =CDFDEV(History, SimData)
– Perfect fit has a CDFDEV value of Zero
– Pick the distribution with the lowest CDFDEV
Distributions
Beta
Double Exponential
Exponential
Gamma
Logistic
Log-Log
Log-Logistic
Lognormal
Normal
Pareto
Uniform
Weibull
Binomial
Geometric
Poisson
CDFDEV
Formula
0.02
=CDFDEV(Sheet1!$A$2:$A$21,SimData!B9:B108)
0.37
=CDFDEV(Sheet1!$A$2:$A$21,SimData!C9:C108)
2.66
=CDFDEV(Sheet1!$A$2:$A$21,SimData!D9:D108)
0.07
=CDFDEV(Sheet1!$A$2:$A$21,SimData!E9:E108)
0.16
=CDFDEV(Sheet1!$A$2:$A$21,SimData!F9:F108)
0.43
=CDFDEV(Sheet1!$A$2:$A$21,SimData!G9:G108)
0.34
=CDFDEV(Sheet1!$A$2:$A$21,SimData!H9:H108)
0.10
=CDFDEV(Sheet1!$A$2:$A$21,SimData!I9:I108)
0.05
=CDFDEV(Sheet1!$A$2:$A$21,SimData!J9:J108)
79.04
=CDFDEV(Sheet1!$A$2:$A$21,SimData!K9:K108)
0.03
=CDFDEV(Sheet1!$A$2:$A$21,SimData!L9:L108)
0.08
=CDFDEV(Sheet1!$A$2:$A$21,SimData!M9:M108)
1.04
=CDFDEV(Sheet1!$A$2:$A$21,SimData!N9:N108)
33.81
=CDFDEV(Sheet1!$A$2:$A$21,SimData!O9:O108)
4.19
=CDFDEV(Sheet1!$A$2:$A$21,SimData!P9:P108)
Use the “View Distributions.xls”
• For a random variable with 10 observations can estimate
the parameters and view the shape of the distribution