Monte Carlo Simulation (Part 1)

Learning Objectives

Upon completing this chapter, you will be able to:

• LO 13-1: Distinguish between the base-case, worst-case, and best-case scenarios in a decision-making problem with uncertainty.

• LO 13-2: Construct and interpret a simulation model that implements a continuous probability distribution to model an uncertain quantity.

• LO 13-3: Construct and interpret a simulation model that implements a discrete probability distribution to model an uncertain quantity.

• LO 13-4: Construct and interpret a simulation model that directly samples values from a set of observations to model an uncertain quantity.

Introduction to Uncertainty and Monte Carlo Simulation

• Uncertainty impacts decision-making in various areas.

• Monte Carlo Simulation:

  • A method that uses repeated random sampling to represent uncertainty in a model and computes values of model outputs.

  • Developed during WWII.

Applications of Simulation Models

Simulation models find usage in various fields including:

• Finance: Investment planning.

• Marketing: New product development.

• Management: Project management, inventory ordering.

• Healthcare: Treatment cost estimation.

• Sports: Performance forecasting.

Building a Simulation Model

• Spreadsheet simulation requires logical formulas.

  • Example: Retailer's profit model based on inputs such as units ordered and customer demand.

  • Simulation Extension: Replace static demand with a probability distribution.

Probability Distribution

• Definition: A statistical function that describes all possible values and the likelihoods that a random variable can assume within a specified range.

Decision Evaluation with Monte Carlo Simulation Steps

1. Identify uncertain parameters as random variables.

2. Randomly generate values for these random variables based on assigned probability distributions.

3. Simulation produces output values, illustrating possible outcomes and their likelihoods.

   • Analysis should include average and variability of outputs.

Monte Carlo Simulation in Excel

• Practical simulation models utilize Excel's built-in functions such as RAND(), AVERAGE, STDEV, NORM.INV, and IF statements.

• Advanced simulation software (e.g., @RISK) offer enhanced features.

Key Terms

• Monte Carlo Simulation: A method using random sampling to model uncertainty and compute model outputs.

• Random Variables: Inputs to the model with uncertain values described by probability distributions.

• Controllable Inputs: Variables selected by the decision-maker.

13.1 Risk Analysis

• Focus: Evaluate decision-making under uncertainty, considering average outcomes and risks.

• Risk Analysis Definition: Quantifies the likelihood and impact of undesirable outcomes.

Profit Concerns

• Challenges: Cash flow and potential losses.

• Key Profit Parameters: Selling Price, Administrative and Advertising Costs, Direct Labor Cost, Parts Cost, First-Year Demand.

• First-Year Profit Calculation:

  $\text{Profit} = (p - c<em>l - c</em>p) \times d - c_a$

Risk Analysis Approach

• Objective: Assess potential for loss and profit variability.

• Focus Areas: Uncertainty in direct labor cost ($c<em>l$), parts cost ($c</em>p$), and demand ($d$).

• Initial Step: Implement what-if scenarios.

What-If Analysis

• Definition: A trial-and-error method to understand different outcomes by adjusting uncertain variable values.

• Scenarios:

  • Base-Case Scenario: Most likely values.

  • Best-Case Scenario: Most optimistic estimates.

  • Worst-Case Scenario: Most pessimistic estimates.

Profit Estimates in Different Scenarios

• Base-Case Scenario Profit Calculation: Profit calculated with most likely values.

• Best-Case Scenario: Profit calculated with optimistic assumptions.

• Worst-Case Scenario: Profit calculated with pessimistic assumptions, indicating potential losses.

13.1 Spreadsheet Model

• Objective: Estimate first-year profitability.

• Certainties: Selling Price per Unit, First-Year Administrative and Advertising Costs.

• Base Estimates: Direct Labor Cost per Unit, Parts Cost per Unit, First-Year Demand.

Spreadsheet Calculations

• Profit Calculation: Uses a formula to combine cost, price, and demand.

13.1 Use of Probability Distributions

• Outcome: What-if analysis provides a range of profits/losses.

• Limitation: What-if analyses cannot determine the likelihood of outcomes; Monte Carlo simulation does this via probability distributions.

Direct Labor Cost Probability Distribution

• Values: Direct labor cost per unit can take on specific values with defined probabilities.

• Distribution Type: Discrete probability distribution.

Parts Cost Probability Distribution

• Values: Parts cost per unit ranges continuously over a given interval.

• Distribution Type: Uniform probability distribution.

First-Year Demand Probability Distribution

• Characteristics: Follows a normal probability distribution.

• Mean ($\mu$): 15,000 units

• Standard Deviation ($\sigma$): 4,500 units

13.1 Generating Random Variable Values with Excel

• Trial Definition: Each trial consists of sets of random variable values and profit estimates.

• Excel Random Number Generation: =RAND() yields a random number from $[0,1)$.

Generating Direct Labor Cost Values

• Method: Divide the $0$ to $1$ interval based on cumulative probabilities to select a cost.

Generating Parts Cost Values

• Formula: Uses a uniform distribution formula with RAND().

  $\text{Parts Cost} = 80 + (100 - 80) \times \text{RAND()}$

Generating Demand Values

• Formula for Normal Distribution:

  $\text{Demand} = \text{NORM.INV}(\text{RAND()}, 15000, 4500)$

13.1 Formula Worksheet

• Excel Modeling: Replace static inputs with formulas generating random values.

Simulating Direct Labor Cost Calculation

• Formula: Combines RAND() with XLOOKUP for costs based on cumulative probability.

Simulating Parts Cost Calculation

• Method: Uses bounds of a uniform distribution.

Simulating Demand Calculation

• Process: References mean and standard deviation for normal distribution.

13.1 Executing Simulation Trials in Excel

• Setup: Utilize Excel's Data Table feature to run multiple trials.

13.1 Measuring the Simulation Output

• Measurement Statistics: Compute for output evaluation:

  • Number of observations

  • Minimum profit

  • Maximum profit

  • Average profit

  • Standard deviation

Analyzing the Simulation Output

• Results: Profits range, mean profit, and standard deviation are calculated from trials.

• Distribution Visualization: A histogram demonstrates the distribution of profit values.

13.1 Interpreting the Simulation Output

• Confidence Interval for Mean Profit Calculation: Calculated using statistical functions.

  $\text{Lower Bound} = H30 - \text{CONFIDENCE.T}(0.05, H31, H27)$

  $\text{Upper Bound} = H30 + \text{CONFIDENCE.T}(0.05, H31, H27)$

• 95% Probability Interval Computation: Indicates where most profits are likely to lie.

  $H36 = H30 - \text{PERCENTILE.EXC}(E27:E1026, 0.025)$

  $H37 = H30 + \text{PERCENTILE.EXC}(E27:E1026, 0.975)$

Sample Proportion of Loss Estimation

• Calculation: Proportion of trials resulting in loss, and its standard error.

Confidence Interval for Probability of Loss

• Interval Calculation: For potential losses based on the sample proportion.

  $H42 = H39 - \text{NORM.S.INV}(0.975) \times H40$

  $H43 = H39 + \text{NORM.S.INV}(0.975) \times H40$

• The final output provides a comprehensive analysis of profit projections and loss probabilities.