7 Simulation Design

This assignment builds on the data preparation and analysis conducted in previous weeks to design and implement a Monte Carlo simulation that evaluates potential business solutions.

7.1 Learning Objectives

Generate data for uncertain variables and calculate key performance indicators (KPIs).
Design a flowchart for the Monte Carlo simulation process.
Develop and run a Monte Carlo simulation in Excel.
Create user interfaces and save simulation outputs for analysis.
Analyze the simulation results and provide recommendations based on the findings.

7.2 Background: Monte Carlo Simulation

A Monte Carlo simulation is a computerized mathematical technique that allows accounting for risk in quantitative analysis and decision-making. It is used to understand the impact of risk and uncertainty in prediction and forecasting models. The simulation model is run multiple times to assess the variability of the results and the likelihood of different outcomes.

7.2.1 Approach Overview

To design and implement a Monte Carlo simulation, the following steps are typically followed:

Input Variables: Identify the uncertain factors.
Distribution Assignment: Assign probability distributions to these variables based on historical data or assumptions. If possible, back up your assumptions using sources (e.g., literature, company data, expert opinions, common practice).
Scenario Generation: Generate thousands of possible scenarios by randomly sampling from these distributions.
KPI Calculation: For each scenario, calculate the relevant KPIs (e.g., stockouts, costs, turnaround times) and aggregate the results to understand the range of possible outcomes.
Decision Support: Use the simulation results to recommend the best strategies under different levels of uncertainty.

7.2.2 Example: Profit Simulation

Consider a company that wants to simulate the impact of different production strategies on its profits. The company has identified the following uncertain variables:

Revenue: The revenue generated from selling products.
Fixed Costs (FC): The costs that do not change with the level of production.
Variable Costs (VC): The costs that vary with the level of production.

These variables are distributed as follows:

Revenue: Normal distribution with a mean of $100,000 and a standard deviation of $10,000.
Fixed Costs: Normal distribution with a mean of $50,000 and a standard deviation of $5,000.
Variable Costs: Normal distribution with a mean of $30,000 and a standard deviation of $3,000.

The company wants to run 10,000 simulations to understand the range of possible profits and the likelihood of incurring a loss.

The output KPIs are as follows:

Mean Profit: The average profit across all simulations.
Standard Deviation of Profit: The variability of profits across all simulations.
Minimum Profit: The lowest profit observed across all simulations.
Maximum Profit: The highest profit observed across all simulations.
Risk of Loss: The rate of scenarios (i.e., simulations) out of the total where the profit is negative. Calculated as: \[ \text{Risk of Loss} = \frac{\text{Count of Negative Profits}}{\text{Total Number of Simulations}} \]

The flowchart in Figure 7.1 illustrates the Monte Carlo simulation process for this example. The simulation involves sampling¹ from the revenue, fixed costs, and variable costs distributions, calculating the profit, and storing the results for analysis. The simulation is repeated until the desired number of simulations is reached. The results are then summarized, and the risk of loss is calculated based on the number of negative profits observed.

flowchart TD
    A(("Start")) --> I[/"Distributions for Revenue, <br> Fixed Costs, and Variable Costs;<br> N. of Simulations"/]
    
    I --> SS["Set Simulation Count = 0"]
    SS --> J{"Is<br>Simulation<br>Count<br><<br>N. of<br>Simulations?"}
    
    
    J -- "No" --> K["Simulate<br>Revenue"]
    K --> L["Simulate<br>Fixed Costs (FC)"]
    L --> M["Simulate<br>Variable Costs (VC)"]
    
    M --> N["Profit =<br> Revenue - (FC + VC)"]
    
    N --> O["Store Simulation Results<br>(Revenue, FC, VC, Profit)"]
    O --> P["Increment<br>Simulation Count"]
    
    P --> J
    
    J -- "Yes" --> R["Calculate Profit<br>Summary Statistics<br>(Mean, Std Dev,<br>Min, Max)"]
    R --> S["Set Risk of Loss =<br> Count Negative Profits /<br>N. of Simulations"]
    S--->OUT[/"Summary Statistics,<br>Risk of Loss, and<br>Simulation Results"/]
    OUT ----> T(("End"))

Figure 7.1: Monte Carlo Simulation Flowchart For Financial Analysis. Simulating revenue, fixed costs, and variable costs consists of sampling from distributions and storing the results.

Besides the KPIs mentioned above, the company may also want to visualize the results to understand the range of possible outcomes. Possible visualizations may include:

Profit Histogram: A histogram showing the distribution of profits across all simulations.
Profit vs. Simulation Number: A line chart showing the profit for each simulation run.
Profit vs. Revenue: A scatter plot showing the relationship between revenue and profit.

Scenario Analysis

The company can use the simulation results to conduct scenario analysis and evaluate the effect of different strategies on the profits and risks. For example, one could test the following scenarios:

What if the company increases its revenue by 10%?
What if the company reduces its fixed costs by 5%?
What if the company increases its variable costs by 10%?
What if the standard deviation of revenue increases by 20%?
What if the standard deviation of fixed costs decreases by 10%?

User Interface and Results Presentation

A simple user interface can be created to allow users to input the number of simulations, the distribution parameters for revenue, fixed costs, and variable costs, and view the simulation results. The interface can send the input to the simulation model, which will run the simulations and store the results in a separate sheet for analysis. After the simulations are completed, the results can be stored in a separate sheet for further analysis, and the interface can display the summary statistics, risk of loss, and visualizations (i.e., profit histogram, profit vs. simulation number, profit vs. revenue).

7.3 Assignments

Considering your team’s specialization, you will design and implement a Monte Carlo simulation to evaluate potential business solutions. The simulation should be based on the data prepared in previous weeks and should focus on the specific challenges and opportunities related to your specialization.

7.3.1 Simulation Design

Explain the experiment settings and variables that will be used in the simulation. Your explanation should answer the following questions:

What are the key variables that will be used in the simulation?
How do these variables relate to the company’s operations and challenges?
How many simulations will be run, and why?
What are the experiment settings that will be used to control the simulation process?
How will the simulation results be used to make decisions or recommendations?
What KPIs will be calculated based on the simulation results?
How will the simulation results be visualized and presented to the company?
How will the simulation results be used to conduct scenario analysis and evaluate different strategies?

Deliverables

A detailed explanation of the Monte Carlo simulation design, including the following:

A summary of the input variables and their ranges, distributions, or assumptions presented as a table. The table should include the variable name, description, and parameters. Make sure you include at least:

2 different values for the number of simulations, and
2 scenarios for one the variables.

For example, in Table 7.1, the standard deviation of “Variable Costs” is varied to simulate different levels of uncertainty.

Table 7.1: Experimental settings and variables for the Monte Carlo simulation. Scenario 1 and Scenario 2 represent different levels of uncertainty for Variable Costs.

Variable Name	Description	Parameters
Revenue	Revenue generated from sales	Mean = $100,000, Std Dev = $10,000
Fixed Costs	Costs that do not change with production level	Mean = $50,000, Std Dev = $5,000
Variable Costs	Costs that vary with production level	Scenario 1: Mean = $30,000, Std Dev = $3,000
		Scenario 2: Mean = $30,000, Std Dev = $5,000
Number of Simulations	Number of simulation runs	1,000, 5,000

A summary of the KPIs that will be calculated based on the simulation results presented as a table. For example:

Table 7.2: Key performance indicators (KPIs) for the Monte Carlo simulation.

KPI Name	Description
Mean Profit	Average profit across all simulations
Std Dev of Profit	Variability of profits across all simulations
Min Profit	Lowest profit observed across all simulations
Max Profit	Highest profit observed across all simulations
Risk of Loss	Rate of scenarios with negative profits

A detailed explanation of the experiment settings and variables used in the simulation and their relevance to the company’s operations. The explanation should refer to the tables provided above.

7.3.2 Assessment Criteria

Clarity: The explanation should be clear and easy to understand.
Relevance: The variables and settings should be relevant to the company’s operations and challenges regarding your specialization.
Completeness: The explanation should cover all essential aspects of the simulation design.
Applicability: The simulation should be designed to provide actionable insights and recommendations to the company.
Visualizations: The explanation should include how the simulation results will be visualized and presented to the company.
Scenario Analysis: The explanation should cover how the simulation results will be used to conduct scenario analysis and evaluate different strategies.
Table Format: The tables should be well-structured and easy to read.

7.3.3 Flowchart

Create a flowchart that explains the Monte Carlo simulation process for your specific case. The flowchart should include the steps involved in the simulation, such as calculating KPIs, showing results, giving input, and simulating data for uncertain variables. Ensure the flowchart is clear and easy to understand.

Deliverables

A flowchart illustrating the Monte Carlo simulation process for your specific case.
A detailed explanation of the flowchart, highlighting the key steps and their relevance to your case.

Assessment Criteria

Clarity: The flowchart should be easy to follow and understand.
Relevance: The steps in the flowchart should be relevant to the Monte Carlo simulation process.
Completeness: The flowchart should cover all essential steps of the Monte Carlo simulation process.
Quality: The flowchart should be well-designed and visually appealing.
Explanation: The explanation should provide a detailed description of the flowchart, highlighting the key steps.

7.3.4 User Interface and Simulation Output

Create a user interface that allows users to input the experiment settings and control the simulation process. The interface should include input fields for the experiment variables and settings and a button to start the simulation.

Upon starting the simulation, two things should happen:

The simulation should generate sample data for the uncertain variables based on the experiment settings provided by the user. The sample data should be saved in a separate sheet (entitled “simulation_output”) for further analysis. This sheet should include:
- The run number (i.e., the simulation iteration).
- The input variables (experiment settings).
- The output (KPIs) calculated based on the simulation results (i.e., the sample data).
The simulation results should be displayed in a separate sheet (entitled “simulation_results”) for analysis and visualization. This sheet should include:
- The simulation input variables.
- The KPIs calculated based on the simulation results.
- Any visualizations or summary statistics of the simulation results.

Deliverables

An Excel (.xlsm) file with a fully functional user interface for the Monte Carlo simulation. This file should include the following components:
- Data Sheets: Sheets containing the data required for the simulation, labeled with a prefix “data_” (e.g., data_costs, data_historical_demand).
- User Interface Sheet: A sheet labeled “interface” that provides a user-friendly interface for controlling the simulation. This sheet should include:
  - Input fields for specifying the experiment variables and settings (e.g., number of simulations, distribution parameters).
  - A button to start the simulation process.
- Simulation Output Sheet: A sheet labeled “simulation_output” to store the raw simulation results. This sheet should include columns for:
  - Run number (simulation iteration).
  - Input variables (experiment settings).
  - Output KPIs calculated from the simulation results.
- Simulation Results Sheet: A sheet labeled “simulation_results” to display the summarized simulation results. This sheet should include:
  - Tables summarizing the KPIs and other key metrics.
  - Visualizations such as graphs and charts to analyze the simulation results.
  - Summary statistics to provide insights into the simulation outcomes.

7.3.5 Analysis and Recommendations

Analyze the simulation results and provide recommendations based on the findings. Your analysis should include:

A table summarizing the results for each experiment setting and variable. For example, considering the inputs presented in Table 7.1 and the outputs presented in Table 7.2, you could create a table like the one below:

Table 7.3: Summary of the simulation results for different experiment settings and variables consdiring different scenarios and number of simulations.

Number of Simulations	Scenario	Variable Name	Mean	Std Dev	Min	Max	Risk of Loss
1,000	Scenario 1	Revenue	$100,000	$10,000	$80,000	$120,000	0.05
1,000	Scenario 1	Fixed Costs	$50,000	$5,000	$40,000	$60,000	0.02
1,000	Scenario 1	Variable Costs	$30,000	$3,000	$25,000	$35,000	0.10
1,000	Scenario 2	Revenue	$100,000	$10,000	$80,000	$120,000	0.05
1,000	Scenario 2	Fixed Costs	$50,000	$5,000	$40,000	$60,000	0.02
1,000	Scenario 2	Variable Costs	$30,000	$5,000	$20,000	$40,000	0.15
5,000	Scenario 1	Revenue	$100,000	$10,000	$80,000	$120,000	0.05
5,000	Scenario 1	Fixed Costs	$50,000	$5,000	$40,000	$60,000	0.02
5,000	Scenario 1	Variable Costs	$30,000	$3,000	$25,000	$35,000	0.10
5,000	Scenario 2	Revenue	$100,000	$10,000	$80,000	$120,000	0.05
5,000	Scenario 2	Fixed Costs	$50,000	$5,000	$40,000	$60,000	0.02
5,000	Scenario 2	Variable Costs	$30,000	$5,000	$20,000	$40,000	0.15

A summary of the key insights from the simulation results. Including:
- Trends, patterns, or significant findings that emerged from the analysis.
- Scenario analysis results and their implications for decision-making.
- Recommendations based on the simulation results and insights gained.
- Any limitations or assumptions made during the simulation process.

Deliverables

A table summarizing the simulation results for different experiment settings and variables.
A summary of the key insights and recommendations based on the simulation results.

Assessment Criteria

Clarity: The analysis should be clear and easy to understand.
Relevance: The insights and recommendations should be relevant to the company’s operations and challenges.
Completeness: The analysis should cover all essential aspects of the simulation results.
Applicability: The insights and recommendations should be actionable and relevant to the company’s decision-making process.
Insights: The analysis should provide meaningful insights and recommendations based on the simulation results.
Table: The table should be well-structured and easy to read.

7.4 Delivery Instructions

Submit the following deliverables on Canvas:

Report in PDF format containing sections:
- 1) Simulation Design [max. four pages]
  - 1.1) Input Variables [around one page]
    - Explanation of the variables and their relevance.
    - Table of variables and their parameters.
  - 1.2) KPIs [around one page]
    - Explanation of the KPIs and their relevance.
    - Table of KPIs.
  - 1.3) Experiment Settings [around one page]
    - Detailed explanation of the experiment settings and variables.
    - Table of experiment settings.
    - Requirements: 2 different values for the number of simulations, and 2 scenarios for one of the variables.
  - 1.4) Flowchart [around one page]
    - Flowchart and explanation.
- 2) Analysis and Recommendations [max. 2 pages]
  - Table summarizing the simulation results.
  - Summary of key insights and recommendations.
Important

Label the report as groupXX_p3_simulation_report.pdf, where XX is your group number (e.g., group01_p3_simulation_report.pdf).
Excel file containing sheets:
- Data required for the simulation with prefix data_
  (for example, data_costs, data_historical_demand).
- User interface (sheet labeled interface).
- Simulation output (sheet labeled simulation_output):
  - Output of the simulation (run number, input variables, KPIs).
- Simulation results (sheet labeled simulation_results):
  - Summary of the simulation results (graphs, tables, summary statistics).
Important

Label the file as groupXX_p3_simulation.xlsm, where XX is your group number (e.g., group01_p3_simulation.xlsm).

Sampling a distribution consists of drawing random values from the distribution according to its parameters (mean, standard deviation, etc.).↩︎