Using Simulation to Teach Distributions

Figures & data

Figure 1. Three Ways to Visualize Exam Scores

Table 1. Components of a Simulation Model

Component	Explanation
List of deterministic factors F1, F2, …, Fm	These are quantities that are known or fixed, or whose behavior we choose not to model (i.e., exogenous).
List of stochastic input variables V1, V2, …, Vk	These are quantities whose value cannot be known with certainty, and are assumed to vary randomly.
List of output variables O1, O2, …, Op	These are stochastic quantities that are important to a decision problem, but whose value depends on many things in the model and whose distribution is not easily found.
Assumed distribution for each stochastic input variable	These are chosen from known statistical distributions, such as normal, Poisson, triangular, and so on.
A model which specifies the rules or formulas that define the relationships among Fs and Vs	Formulas including simple accounting identities such as Profit = Revenue – Cost or End Balance = Begin Balance – Withdrawals+ Deposits, or behavioral hypotheses such as Car Sales = b0 + b1(Personal Income After Taxes) + b2(Net Worth) + b3(3-month T-Bill Interest Rate).
A simulation method that produces random data from the specified distributions and captures the results.	This is a programming language (such as VBA) although it may be embedded invisibly in a spreadsheet with built-in functions like Excel's RAND() or other add-ins.
An interface that summarizes the model's inputs, outputs, and simulation results.	Typically, spreadsheet tables or graphs to summarize the outcomes of the simulation.

Table 2. Simulation Setup for Revenue Calculation

Variable	Type	Product A	Product B	Product C
Price	Deterministic	PA = 80	PA = 150	PA = 400
Quantity	Stochastic input	Normal QA ∼N(50, 10)m =50,s = 10	TriangularQB ∼T(0, 40, 5) Min =0, Max = 40, Mode = 5	Exponential QC ∼E(2.5) l = 2.5
Revenue	Stochasticoutput	PA QA	PB QB	PC QC

Table 3. Results of 100 Iterations of Revenue Simulation

Percentile	Product A	Product B	Product C	Total Revenue
Min	26	1	0	4,180
5%	34	4	0	4,745
25%	44	8	1	5,943
50%	50	14	2	7,000
75%	56	20	3	8,340
95%	64	32	6	10,022
Max	69	35	11	10,780
Sample Mean	49.85	15.11	2.30	7,335
Theory Mean	50	15	2.5	7,250

Notem_B = (a + b + c)/3 = (0 + 40 + 5)/3 = 15.m_A = 50 and m_C = 2.5 we can find the theoretical mean of total revenue as P_A m_A + P_B m_B + P_C m_C = (80)(50) + (150)(15) + (400)(2.5) = 4,000 + 2,250 + 1,000 = 7,250. This compares well with the sample result over 100 iterations. Although product A contributes the most to total revenue at the mean, this may not be the case in a particular simulation, because demand can fluctuate.

Figure 2. Histograms for 100 Iterations of Revenue Simulation

Figure 3. Bob's Stochastic Balance Sheet

Table 4. Distributions Used in Bob's Stochastic Balance Sheet

Asset	Distribution	Comments
Mustang	T(8000,15000,10000)	Right-skewed
Beer can collection	T(0,1000,50)	Very right-skewed
Garland stock	N(15.38,3.15)	Symmetric, large C.V.
Oxnard stock	N(26.87,2.02)	Symmetric, small C.V.
Scamco stock	N(3.56,0.26)	Symmetric, small C.V.

Figure 4. Simulation Summary for Bob's Balance Sheet (Excel Version)

Figure 5. Buttons to Set Up the Simulation

Figure 6. Suggested Simulation Settings

Figure 7. Selecting a Graph

Figure 8. Histogram of 500 Iterations of Net Worth

Figure 9. Distribution of Net Worth for 500 Iterations

Figure 10. Tornado Graph for Sensitivity Analysis

Figure 11. Inverse Continuous CDF

Figure 12. Inverse Discrete CDF

Table 5. Creating Random Data in Excel

Distribution	What to Put in Excel Cell	Explanation
Uniform U(0, 1)	=RAND()	Built-in Excel function
Uniform U(a, b)	=$A$1+($A$2-$A$1)*RAND()	$A$1 is the minimum and $A$2 is the maximum (or use cell names like Xmin and Xmax).
Normal N(0, 1)	Method A: =NORMSINV(RAND()) Method B: =SQRT(−LN(RAND()))*COS(2*PI()*RAND())	Excel's inverse normal function or do-it-yourself Box-Muller transform.
Normal (m, s)	Method A: =NORMINV(RAND(),$A$1,$A$2) Method B: =$A$1+$A$2*SQRT(−2*LN(RAND()))*COS(2*PI()*RAND())	$A$1 is the mean and $A$2 is the standard deviation (or use cell names like Mu and Sigma).
Exponential E(l)	=-LN(RAND())*$A$1	$A$1 is the mean Poisson arrival rate (or use cell name like Lambda)
Triangular T(a, b, c)	No single cell formula but can be done in Excel with two cells.	Better to use @Risk or Crystal Ball.
Bernoulli B(p)	=IF(RAND()<$A$1,1,0)	$A$1 is the probability of success (or use a cell name like ProbSuccess)

Table

File Name	Type	Contents
SimulationBasics.doc	Word	Brief handout on simulation and distributions (examples, functions, Excel, @Risk)
[e-mail]	Word	Optional handout on @Risk software (methods, iterations, output options) to get teams started if they use the @Risk versions of the cases.
ProjectInstructions.doc	Word	Instructions to student teams for in-class projects using the case studies. Includes separate instructions for Excel-only and @Risk versions.
ThreeProductExample.xls	Excel	Suggested in-class demonstration using only Excel. Illustrates normal, triangular, and exponential distributions using a familiar business example.
@RiskVersion-Case1.xls @RiskVersion-Case2.xls @RiskVersion-Case3.xls	Excel	Three Excel spreadsheet cases using @Risk functions, with questions to be answered by student teams. Requires @Risk software. Stochastic cells will show #NAME? until the @Risk Add-In is installed and @Risk is started.
ExcelVersion -Case1.xls ExcelVersion-Case2.xls ExcelVersion-Case3.xls	Excel	Three Excel spreadsheet cases containing formulas, questions to be answered by the student teams, and a simulation results worksheet. Initial stochastic cell values change when F9 is pressed. Fewer capabilities than the @Risk version, but works using only Excel without any add-ins.