Monte Carlo Simulation Tool: Excel Add-in Guide

Monte Carlo Simulation Tool in Excel

Introduction to the Monte Carlo Tool

The Monte Carlo tool is a free Excel add-in developed at the University of Pennsylvania approximately twenty years ago. It serves as a pedagogical alternative to commercial simulation tools like Crystal Ball and @RISK, which often come with prohibitive costs (e.g., $1,500$ to $2,000$ for a desktop license). The tool is specifically designed to help users rapidly configure and run larger simulations within Excel, abstracting away the complexity of building simulations from scratch using complex Excel formulas (NORM, BINOM, RAND). It is a VBA-based spreadsheet that integrates additional menu options into Excel.

Accessing and Setting Up the Tool

Availability and Download

The Monte Carlo tool is available for download from the course's Canvas website. It can be found in the folder path: slides > Excel > topic three. Look for the file with a paperclip icon.

Installation and Security

Upon downloading the file, it is essential to "unblock" its content. This is done by right-clicking the downloaded file, selecting "Properties," and then choosing the "Unblock" option. Additionally, users might need to adjust their computer's security or trust permissions as it is a macro-enabled workbook. Failure to unblock or adjust permissions will prevent the tool from running correctly.

System Requirements

The tool is designed for Windows operating systems. It does not function properly on Macintosh computers due to VBA compatibility issues. Mac users must run the tool on a Windows partition or utilize the Camden's virtual computer lab, which provides a Windows-based, remote desktop environment.

Support Resources

Several resources are available on Canvas to assist users:

• Tutorial: A detailed, step-by-step guide on how to use the software, particularly useful for Team Member Three.
• Slide Pack: Basic instructions and reference material.
• Common Issues Posting: A PDF document from a previous GA (AV) outlining common problems encountered by users and guidance on how to navigate the tool's "temperamental aspects," as it is not industrial-strength software.

Core Functionality: Configuration

Once the tool is downloaded and unblocked, opening the spreadsheet will reveal a new "Add-ins" tab in the Excel ribbon. This tab contains the specific menu options for the Monte Carlo tool.

Inputs and Outputs: Terminology

It's important to note the tool's specific terminology:

• Inputs: Referred to as "Assumptions."
• Outputs: Referred to as "Forecasts."

Configuring Output Cells (Forecasts)

To designate an output cell (e.g., profit, revenue, cost), select the cell and click "Insert Forecast" from the "Add-ins" menu. The tool adds a red flag or a "note" (formerly called "comment" in older Excel versions) to the cell, identifying it as the simulation's output.

Configuring Input Variables (Assumptions)

Input variables are configured to conform to various probability distributions. The tool adds notes to these cells to indicate their role in the simulation.

Key principle: For distributions, the variable name must appear in the cell immediately to the left of the distribution's parameters. For example, if "Units Sold" is the variable name, its distribution parameters should be in the cells to its right.

Available Distributions and Parameters

The Monte Carlo tool offers three common distributions:

1. Uniform Distribution: Defined by two parameters: Low and High.
   • Automatic Configuration: If chosen, the current cell becomes the Low parameter, and the cell to its right becomes the High parameter.
2. Normal Distribution: Defined by two parameters: Mean and Standard Deviation.
   • Automatic Configuration: If chosen, the current cell becomes the Mean, and the cell to its right becomes the Standard Deviation.
3. Triangular Distribution: Defined by three parameters: Low, Mode (most common value), and High.
   • Automatic Configuration: If chosen, the current cell becomes the Low, the next cell to the right becomes the Mode, and the value further to the right becomes the High parameter.

Example: Configuring Units Sold (Normal Distribution)
To configure "Units Sold" with a normal distribution (e.g., mean of $10$ and standard deviation of $2$):

1. Delete any existing notes from the relevant cells.
2. Select the cell that will host the Mean parameter (e.g., F14).
3. Go to Add-ins > Assumptions.
4. Choose Normal and then Automatic.
5. The tool F14 as the Mean and G14 (the cell to the right) as the Standard Deviation for the variable "Units Sold."

Example: Configuring Price per Unit (Triangular Distribution)
To configure "Price per Unit" with a triangular distribution (e.g., low of $1$, mode of $2$, high of $4$):

1. Select the cell that will host the Low parameter.
2. Go to Add-ins > Assumptions.
3. Choose Triangular and then Automatic.
4. The tool will label the selected cell and the two cells to its right as Low, Mode, and High respectively for the variable "Price per Unit."

Running and Interpreting Simulations

After configuring all input and output variables, the simulation can be run by clicking "Run Simulation" in the "Add-ins" menu.

Simulation Execution Options

Users can choose one of two ways to run the simulation:

1. Fixed Number of Iterations: Specify a certain number of "transactions" or "iterations" to generate (e.g., $100$ iterations).
2. Time-Limited Run: Allow the simulation to run for a specified duration, generating as many iterations as possible within that timeframe.

Understanding Simulation Outputs

Upon execution, the tool generates several new sheets, some of which are initially hidden.

1. Simulation Output (Hidden Sheet)

This sheet contains the raw, detailed data for every iteration generated. To view it, right-click any sheet tab at the bottom of Excel, choose "Unhide," and then select "Simulation Output." This allows users to examine the values generated for all input variables and the resulting output variable for each transaction (e.g., $100$ transactions with corresponding units sold, price per unit, etc.).

2. Summary Sheet

The summary sheet provides descriptive statistics for each variable configured in the main workbook. Column headings represent the variable names (e.g., Units Sold, Price Per Unit), and rows contain statistics such as minimum, maximum, average, standard deviation, and median. These statistics should align with the distributional assumptions made during configuration (e.g., a normal distribution with a mean of $10$ and standard deviation of $2$ should yield similar summary statistics).

3. Frequency Chart for Output Variable

For the output variable (forecast cell), the tool generates a chart showing its range and the frequency of profit values within that range. This visual representation helps understand the distribution of the output (e.g., profit varies between $- ext{8}$ and $31$, with a cluster of values between $4$ and $8$), saving the user the effort of manually creating frequency tables.

4. Staircase/Tornado Chart

Also known as a tornado chart, this visual displays the influence of each input variable on the volatility of the output variable. It illustrates how much the output variable changes as each input variable varies between its low and high values.

• Interpretation: Variables at the top of the chart have the most significant impact on the output (e.g., "Units Sold" having a massive impact on profit).
• Interpretation: Variables at the bottom have minimal or no influence. Managers should focus on controlling the most influential variables.
• Dependency on Assumptions: The influence shown in the tornado chart is highly dependent on the ranges and distributions configured for the input variables. Incorrect or narrow ranges in the assumptions will lead to an inaccurate depiction of variable influence. It is crucial to ensure assumptions are reasonable.

Application for Team Member Three

Team Member Three is expected to apply this tool to their specific business context. This requires significant adaptation and innovation beyond the basic example provided in the demonstration.

Do's and Don'ts for the Assignment

• Don't: Simply replicate the example spreadsheet with minor changes to variable names. This demonstrates only basic button-clicking, not understanding.
• Do: Use the demo as inspiration but start from scratch with a format that the Monte Carlo tool expects (variable names as row headings, parameters to the right).
• Do: Add new rows and new variable names to the simulation. The simulation should include more than just the basic revenue-cost-profit model.
• Do: Simulate physical metrics in addition to financial ones. For instance, if simulating orders for a target, generate transactions that include product dimensions (width, height, length) to assess space requirements (e.g., in a fridge).
• Do: Customize the transactions to reflect the specific 'objects' of the business (e.g., new orders, cars, customers) and their relevant variables.
• Do: Read the documentation thoroughly, digest the learnings, and go well beyond the basic configuration. The goal is to demonstrate a confident and competent application of the tool to a unique business scenario.

Simulating Interim Variables

While generally advisable to simulate input variables, it is permissible to simulate interim variables (e.g., waiting time) from a static distribution if simulating them from first principles (which might involve complex formulas) is too challenging. However, a caveat is that a static distribution for an interim variable might not accurately reflect its behavior if the underlying input parameters change in the future.

Alternatives

Students are permitted to use commercial tools like Crystal Ball or @RISK if they prefer. However, users must be prepared to create a very detailed video explanation of their simulation, as GAs may not have access to these commercial software packages to run the simulations themselves.

Overall Goal

The primary objective is to demonstrate an understanding of how add-ins for Excel facilitate simulation operations, allowing users to visually configure simulation parameters without needing to build complex Excel formulas from scratch. This exposure to different simulation paradigms highlights the pros and cons of such visual tools compared to manual configuration. The emphasis is on critically adapting the tool's capabilities to a specific business problem, generating meaningful transactions and summaries beyond simple financial metrics.