Simulation & Modeling
• In many business models, the value for one or more cells representing independent variables is unknown or uncertain.
• As a result, there is uncertainty about the value the dependent variable will assume:
Y = f(X1, X2, …, Xk)
Copyright By PowCoder代写 加微信 powcoder
• Simulation can be used to analyze these types of models.
Random Variables
• A random variable is any variable whose value cannot be predicted or set with certainty.
• Many “input cells” in models are actually random variables.
the future cost of raw materials
future interest rates
future number of employees in a firm expected product demand
• Decisions made on the basis of uncertain information often involve risk.
Random Variables
Plugging in expected values for uncertain variables tells us nothing about the variability of the performance measure on which business decisions are based.
Suppose an $1,000 investment is expected to return $10,000 in two years. Would you invest if…
the outcomes could range from $9,000 to $11,000?
the outcomes could range from -$30,000 to $50,000?
Alternatives with the same expected value may involve different levels of risk.
What If/Scenario Analysis vs Simulation
What-If analysis:
• Plug in different values for the uncertain cells and see what happens.
• This is easy to do with spreadsheets.
• Problems:
Values may be chosen in a biased way.
Hundreds or thousands of scenarios may be required to generate a
representative distribution.
Does not supply the tangible evidence (facts and figures) needed to justify decisions to management.
What If/Scenario Analysis vs Simulation
• Resembles automated what-if analysis.
• Values for uncertain cells are selected in an unbiased manner.
• The computer generates hundreds (or thousands) of scenarios.
• Weanalyzetheresultsofthesescenariostobetterunderstand the behavior of the performance measure.
• This allows us to make decisions using solid empirical evidence.
Simulation
• To properly assess the risk inherent in the model we need to use simulation.
• Simulation is a 4 step process:
1) Identify the uncertain cells in the model.
2) Implement appropriate RNGs for each uncertain cell.
3) Replicate the model n times, and record the value of the bottom-line performance measure.
4) Analyze the sample values collected on the performance measure.
Business Case Sensitivity Example
2012 Actual
2013 Forecast
GROSS PROFIT (LOSS) $ 124,389 $ 128,975 $ 133,740 $ 138,693 $ 143,842 3.7%
2014 Forecast
2015 Forecast
2016 Forecast
% of Revenue
Sales Revenue Beverage
Other Revenue
TOTAL REVENUE $ 222,526 $ 230,155 $ 238,062 $ 246,255 $ 254,747 3.4%
Annual Software Licensing/Maint Costs
Sales growth MTG
Option 1: ERP System
Option 2: CRM System
Sales Revenue Meals to Go
163,732 $ 50,073 $ 16,350 $
168,644 $ 52,577 $ 16,841 $
173,703 $ 55,206 $ 17,346 $
178,914 57,966 17,866
Initial Investment
$ 2,500$ – $ 5,000$ 2,500 $ 800$ – $ 400$ 800
3.0% 1.0% 1.0% 5.0% 2.5% 2.5% 3.0% 0.0% 0.0%
0% 0% -5% 0% 0% -2% 0% 0% 0%
-4% 0% -8% -5% 0% 0% – – (500)
3.0% 5.0% 3.0%
% Growth YOY
Meals to Go Costs
Beverage Costs
Other Costs
3.4% 3.4% 3.4% 3.4%
Sales Growth Beverage
Sales Growth Other
81,481 $ 5,257 $ 14,443 $
83,926 $ 5,520 $ 14,876 $
86,443 $ 5,796 $ 15,322 $
89,037 6,086 15,782
Beverage Costs
Other Costs
Operating Supplies
Maintenance & Repairs
SG & A Costs
$ 32,044 $ 30,762 $ 29,532 $ 28,350
Discount Rate
Software Licencing & Contracts
$ 800 $ 800 $ 800 $ 800
$ 8,465 $ 8,465 $ 8,465 $ 8,465 $ 8,465
VARIABLE MARGIN $ 61,014 $ 65,841 $ 71,590 $ 77,471 $ 83,494 8.2% VM % 27.4% 28.6% 30.1% 31.5% 32.8%
Discounted Cash Flow $111,746
$80,385 $122,544 111746.0528
SG & A Costs
Legal & Fees
$ 15,009 $ 14,259 $ 13,546 $ 12,869
$ 2,003 $ 2,003 $ 2,003 $ 2,003 $ 2,003
EBITDA (OPERATING MARGIN) $ 17,177 $ 22,793 $ 29,292 $ 35,887 $ 42,587 25.5% OM % 7.7% 9.9% 12.3% 14.6% 16.7%
As part of the company’s yearly strategic planning, the management team has put together two business cases for major software development projects.
Case 1: Implement a new ERP System. The estimated investment cost and yearly maintenance costs are shown in the table above. The targeted profitability improvements come from assumptions around cost reduction and the elimination of two FTEs in the Supply Chaim Management Dept. as well as a one time inventory reduction in the second year after the system is adopted.
Capital Investment
Inventory Value Change
CASH FLOW $ 14,677 $ 22,793 $ 29,292 $ 35,887 $ 42,587
Case 2: Implement a new CRM System. The estimated investment cost and yearly maintenance costs are shown in the table above. The targeted profitability improvements come from assumptions around sales growth, a reduction in SG & A costs and a reduction of one FTE in the Inside Sales Dept.
Total Cost of Sales
Total Variable Costs
Total Fixed Costs
$ 98,137 $
$ 63,375 $
$ 43,838 $
Scenario Manager Summary
Scenario Summary
Current Values: Base Case ERP System ERP Low ERP High CRM Base CRM Low CRM High
Changing Cells:
$ 5,000 $ 400
1.0% 2.5% 0.0%
-5% -2% 0% -8% 0% (500)
– 1.0% 2.5% 0.0%
0% 0% 0% 0% 0%
5,000 $ 400 $
1.0% 2.5% 0.0%
-5% -2% 0% -8% 0% (500)
400 $ 0.8% 2.2% 0.0%
-3% 0% 0% 0% 0%
5,000 $ 400 $
1.2% 2.7% 0.0%
-7% -4% 0% -8% 0% (600)
2,500 $ 800 $
3.0% 5.0% 3.0%
2,500 $ 800 $
1.0% 2.0% 0.0%
0% 0% 0% 0%
7.0% 10.0% 3.0% 0% 0% 0% -4% -7%
Result Cells:
$M$3 $M$4 $M$5 $M$6 $M$7 $M$8 $M$9 $M$10 $M$11 $M$12 $M$13
$122,544 $80,385 $122,544 $93,909 Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.
$136,148 $111,746 $76,657 $157,851
Scenario Analysis vs. Simulation
Scenario Summary
Current Values: Base Case ERP System ERP Low ERP High
Changing Cells:
Result Cells:
$M$3 $M$4 $M$5 $M$6 $M$7 $M$8 $M$9 $M$10 $M$11 $M$12 $M$13
$ 5,000 $ 400
1.0% 2.5% 0.0%
-5% -2% 0% -8% 0% (500)
$ – $ 5,000 $ 500 $ 5,000 $ -$ 400$ 400$ 400
1.0% 1.0% 2.5% 2.5% 0.0% 0.0%
0% -5% 0% -2% 0% 0% 0% -8% 0% 0%
0.8% 1.2% 2.2% 2.7% 0.0% 0.0%
-3% -7% 0% -4% 0% 0% 0% -8% 0% 0%
$122,544 $80,385 $122,544 $93,909 Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.
Scenario Output Summary
Simulation Output Summary
Scenario Analysis vs. Simulation
Option 1: ERP System
Initial Investment
$ 5,000 $ 5,000 $ 5,000 $ 400 $ 400 $ 400
1.0% 2.5% 0.0%
-5% -2% 0% -8% 0% (500)
0.8% 1.2% 2.2% 2.7% 0.0% 0.0%
-3% -7% 0% -4% 0% 0% 0% -8% 0% 0%
Annual Software Licensing/Maint Costs
Sales growth MTG
Sales Growth Beverage
Sales Growth Other
Beverage Costs
Other Costs
SG & A Costs
Discount Rate
Option 1: ERP System
Initial Investment
$ 5,000 0 $400 0 2.0% 0.5% 3.0% 1.0% 0.0% 0.0% -7% 1% -3% 1% 0% 0% -10% 2% 0% 0% (500) 0
Annual Software Licensing/Maint Costs
Sales growth MTG
Sales Growth Beverage
Sales Growth Other
Beverage Costs
Other Costs
SG & A Costs
Discount Rate
ERP BC with 3 Scenarios (Base, Low, High) for random variables specified
ERP BC with simulated distributions (normal with mean & std dev) for random variables specified
Outline of Simulation Study source: Introduction to Management Science (5th edition). Hillier & Hillier, McGraw-Hill.
• Step 1: Formulate the Problem and Plan the Study
What is the problem that management wants studied?
What are the overall objectives for the study?
What specific issues should be addressed?
What kinds of alternative system configurations should be considered?
What measures of performance of the system are of interest to management? What are the time constraints for performing the study?
• Step 2: Collect the Data and Formulate the Simulation Model
The probability distributions of the relevant quantities are needed. Generally it will only be possible to estimate these distributions.
A simulation model often is formulated in terms of a flow diagram.
• Step 3: Check the Accuracy of the Simulation Model
Walk through the conceptual model before an audience of all the key people.
Outline of Simulation Study source: Introduction to Management Science (5th edition). Hillier & Hillier, McGraw-Hill.
• Step 4: Select the Software and Construct a Computer Program
Classes of software
Spreadsheet software (e.g., Excel, Crystal Ball)
A general purpose programming language (e.g., C, FORTRAN, Pascal, etc.)
A general purpose simulation language (e.g., GPSS, SIMSCRIPT, SLAM, SIMAN) Applications-oriented simulators
• Step 5: Test the Validity of the Simulation Model
If the real system is currently in operation, performance data should be compared with the corresponding output generated by the simulation model.
Conduct a field test to collect data to compare to the simulation model.
Have knowledgeable personnel check how the simulation results change as the
configuration of the simulated system is changed.
Watch animations of simulation runs.
Outline of Simulation Study source: Introduction to Management Science (5th edition). Hillier & Hillier, McGraw-Hill.
• Step 6: Plan the Simulations to Be Performed
Determine length of simulation runs.
Keep in mind that the simulation runs do no produce exact values. Each simulation run can be viewed as a statistical experiment that is generating statistical observations of the performance of the system.
• Step 7: Conduct the Simulation Runs and Analyze the Results
Obtain point estimates and confidence intervals to indicate the range of likely values for the measures.
• Step 8: Present Recommendations to Management
Example: Coin Flip Game
• Rulesofthegame:
1. Each play of the game involves repeatedly flipping an unbiased coin until the difference between the number of heads and tails tossed is three.
2. To play the game, you are required to pay $1 for each flip of the coin. You are not allowed to quit during the play of a game.
3. You receive $8 at the end of each play of the game.
• Examples:
THTTT THHTHTHTTTT
3 flips 5 flips 11 flips
You win $5
You win $3 You lose $3
Probability Distributions in Excel Excel file: 6_Sim_2_Distributions.xlsx
Uniform Dist Normal Dist Exponential Dist 1-15
Probability Distributions in Python NB file: 6_Sim_2_Distributions.ipynb
Probability Distributions in R RMD file: 6_Sim_2_Distributions.Rmd
Example: Simulating a Linear Model
Example: Endowment Investment
The Director of Investments for a small, private K-12 school manages an endowment with a current balance of $538,000. The funds are invested in a portfolio whose annual returns varies and can be represented as a normally distributed random variable with a mean of 6% and a standard deviation of 2%.
A new scholarship is being created as a memoriam to a distinguished alumna. The legal terms of the endowment require the Director of Investments to determine a constant scholarship payment amount from the endowment that, if made at the end of each of the next 15 years, would result in only a 5% chance of the endowment’s ending value dropping below its current value. Create a simulation model to make a recommendation for a scholarship amount that acheives this goal.
Example: Endowment Investment
Beginning Balance
Annual Return
Investment Earnings
Ending Balance
Return Mean
Return Std Dev
$ 1,000,810
$ 1,000,810
$ 1,060,814
$ 1,000,810
$ 1,061,592
$ 1,061,592
$ 1,119,546
$ 1,119,546
$ 1,154,495
$ 1,154,495
$ 1,209,490 5.5% 1.7%
Std Dev Return
Example: Endowment Investment
Return Return Std Scholarship
Mean 6% Dev 2% Amount $ 28,000 Loss % 6.6%
Scholarship Amount
Value Loss
Example: Reservation Management
The Operations Analyst for Piedmont Commuter Airlines (PCA) is looking at optimizing the number of reservations that the airline accepts for a popular regional flight.
The flight operates with a small turbo-prop aircraft with 19 passenger seats available. PCA sells non-refundable tickets for this flight for $150 per seat. Knowing that some passengers will purchase tickets and then not show for the flight, airlines tend to overbook their flights to increase their revenue. If the flight is overbooked and more than 19 passengers show up, some of them have to be bumped to a later flight. PCA gives bumped passengers vouchers for a free meal, a free flight at a later date and, if necessary, pays for an overnight stay at an airport hotel. The average cost of this package is $325 per bumped passenger.
Example: Queueing Model
Mean Interarrival Time (minutes)
Exponential
Time (minutes)
Time in Queue
Time in System
Max Service Time (minutes) 25
17.56 37.52
Customer Number
Interarrival Time
Time Time of Service Arrival Begins
Service Time
Time Service Ends
Time in Time in Queue System
Interarrival Time
=-1*B$3*LN(RAND())
Time of Arrival
Time Service Begins
=MAX(C9,F8)
Service Time
=B$4+(B$5-B$4)*RAND()
Time Service Ends
Time in Queue
Time in System
Number in Que
13 17 21 25 29 33 37 41 45 49 53 57 61 65 69 73 77 81 85 89 93 97
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com