Project 3b Monte-Carlo Simulation
Overview
Monte-Carlo Simulation is a form of Automated Scenario Analysis that looks at all possible scenarios. It is used in situations where there is a great deal of uncertainty and risk.
Monte-Carlo Simulation is similar to both Sensitivity analysis and Scenario analysis, but it is far more complex. Here is a brief summary of all three methods:
Sensitivity analysis- In Sensitivity analysis we changed one input/independent variable to see what effect they would have on one of the output/dependent variables.
Scenario analysis- In Scenario analysis we changed one or more input/independent variable to see what effect they would have on one of the output/dependent variables.
Monte-Carlo Simulation- Monte-Carlo Simulation runs thousands of simulations with random numbers to measure the effects of uncertainty in the model. Monte-Carlo simulation is the best method to use when there is a lot of uncertainty because it gives the probability of the outcome where as the other two methods just use estimations made by the user.
Application with DSS
Monte-Carlo Simulation is performed in a program package called @Risk that is an add-in to Excel. The inputs used for estimating the firms demand in the model are Average Advertising, Average Price, Firm Price, and Firm Advertising. Here are the steps we used in our Monte-Carlo Simulation
1. First we computed some statistical measures on Average Advertising and Average Price. Then we put these values into our DSS and selected normal distribution
|
Statistical
Measure |
Average
Advertising |
Average
Price |
|
Mean |
377.78 |
93,326 |
|
St
Deviation |
6.1032 |
9,828 |
- Then we selected those variables as our inputs in the @Risk toolbar. We also selected the decision variables (Firm Price, 365 and Firm Demand, 99000) as input variables and selected normal distribution.
- Then we selected Total Industry Demand, Relative Demand, Market Share, and our Firms Estimated Demand as our outputs.
- The simulation was set to run 5,000 Iterations and the simulation was run. Below are the results in the DSS
|
A
Model for Forecasting Demand |
|
|
TID
Model's Coefficients |
|
|
|
|
|
|
|
Variable |
Coefficient |
|
|
|
Inputs |
|
|
Intercept |
164,336.00 |
|
|
|
|
|
|
Avg Price |
(445.00) |
|
|
|
|
|
|
Avg Adv |
0.26 |
|
|
|
Estimates
for Industry |
|
|
|
|
|
|
|
Estimated
Average Price |
378 |
|
|
|
|
|
|
Estimated
Average Advertising |
93326 |
|
RD
Model's Coefficients |
|
|
|
|
Number of
Firms in Industry |
10 |
|
Variable |
Coefficient |
|
|
|
|
|
|
Intercept |
16.13 |
|
|
|
Your
Decisions |
|
|
Prel |
(16.44) |
|
|
|
Price |
365 |
|
Arel |
0.78 |
|
|
|
Advertising |
99000 |
|
RD1 |
0.53 |
|
|
|
|
|
|
|
|
|
|
|
Historical
Data |
|
|
Outputs |
|
|
|
|
Your
Demand |
2430 |
|
|
|
|
|
|
Total
Industry Demand |
27000 |
|
Total
Industry Demand |
|
20,390.76 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Relative
Demand |
|
|
|
|
Calculations |
|
|
|
|
|
|
|
Relative
Price (Qtr = t) |
0.965608 |
|
Market
Share |
|
0.16 |
|
|
Relative
Advertising (Qtr = t) |
1.060798 |
|
|
|
|
|
|
|
|
|
Your
Firm's Estimated Demand |
3180.59 |
||
|
Average
Demand (Qtr = t-1) |
2700 |
|
|
|
|
|
|
Your
Firm's Relative Demand (Qtr = t-1) |
0.9 |
|
Average
Demand |
|
2,039.08 |
|
5. Firm Demand is estimated to be 3180 and Market Share is 16%. @Risk generates graphs to show the confidence in these estimates. From the Estimated Firm Demand distribution we can be 90% sure that our demand will be between 3030 and 3330. From the Market Share distribution we can be 90% sure that our market share will be between 15 and 17%.
