A Business Planning Example using Monte Carlo Simulation

Monte Carlo Simulation Definition
Monte Carlo Simulation Formula

SimVoi ® Monte Carlo Simulation Add-in for Excel For Mac Excel 2016-2019-365 and Windows Excel 2010-2013-2016-2019-365. SimVoi provides random number generator functions as inputs for your model, automates Monte Carlo simulation. Monte Carlo simulations are also known as stochastic modeling, stress testing, and worst-to-best case scenario analysis We're not a fan of Monte Carlo simulators in financial planning software. But customers kept asking for it, so we implemented it into the Integrated Financial Planner, all versions of our retirement software, and the college. Monte Carlo eXtreme (MCX) v.1.0 MCX is a Monte Carlo simulation software for static or time-resolved photon transport in 3D media. It uses GPU-based massively parallel computing techniques and is extremely fast compared to the traditional single-threaded CPU-based. Monte Carlo simulation enables us to model situations that present uncertainty and then play them out on a computer thousands of times. Note: The name Monte Carlo simulation comes from the computer simulations performed during the 1930s and 1940s to estimate the probability that the chain reaction needed for an atom bomb to detonate would work.

Imagine you are the marketing manager for a firm that is planning to introduce a new product. You need to estimate the first year net profit from this product, which will depend on:

Sales volume in units
Price per unit
Unit cost
Fixed costs

Net profit will be calculated as Net Profit = Sales Volume* (Selling Price - Unit cost) - Fixed costs. Fixed costs (for overhead, advertising, etc.) are known to be $120,000. But the other factors all involve some uncertainty. Sales volume (in units) can cover quite a range, and the selling price per unit will depend on competitor actions. Unit costs will also vary depending on vendor prices and production experience.

Uncertain Variables

To build a risk analysis model, we must first identify the uncertain variables -- also called random variables. While there's some uncertainty in almost all variables in a business model, we want to focus on variables where the range of values is significant.

Sales and Price

Based on your market research, you believe that there are equal chances that the market will be Slow, OK, or Hot.

In the 'Slow market' scenario, you expect to sell 50,000 units at an average selling price of $11.00 per unit.
In the 'OK market' scenario, you expect to sell 75,000 units, but you'll likely realize a lower average selling price of $10.00 per unit.
In the 'Hot market' scenario, you expect to sell 100,000 units, but this will bring in competitors who will drive down the average selling price to $8.00 per unit.

As a result, you expect to sell 75,000 units (i.e., (50,000+75,000+100,000)/3 = 75,000) at an average selling price of $9.67 per unit (i.e., ($11+$10+$8)/3 = $9.67).

Unit Cost

Another uncertain variable is Unit Cost. Your firm’s production manager advises you that unit costs may be anywhere from $5.50 to $7.50, with a most likely cost of $6.50. In this case, the most likely cost is also the average cost.

Uncertain Functions

Net Profit

Our next step is to identify uncertain functions -- also called functions of a random variable. Recall that Net Profit is calculated as Net Profit = Sales Volume * (Selling Price - Unit cost) - Fixed costs. However, Sales Volume, Selling Price and Unit Cost are all uncertain variables, so Net Profit is an uncertain function.

The Flawed Average Model

Before we explore how to use simulation to analyze this problem, consider the Excel model pictured below, which calculates Net Profit based on average sales volume, average selling price, and average unit cost.

Monte Carlo Simulation Definition

Intuition might suggest that plugging the average value of our uncertain inputs (Sales Volume, Selling Price, and Unit Cost) into our model should produce the average value of the output (Net Profit). However, as we’ll see in a moment, the Net Profit figure of $117,750 calculated by this model, based on average values for the uncertain factors, is quite misleading. The true average Net Profit is closer to $93,000! As Dr. Sam Savage warns, 'Plans based on average assumptions will be wrong on average.'

< Back to: Monte Carlo Simulation Tutorial StartNext: Introducing Uncertainty >

Add Monte Carlo Simulation to Your Spreadsheet Models

This guide describes how to convert a static Excel spreadsheet model into a Monte Carlo simulation, and the kind of information you can learn from the simulation. It will walk through the basic techniques, and the functions you will need to use. The full model, including each of the steps below, is available for download.

The examples in this guide use the RiskAMP Monte Carlo simulation functions.

You can download a free trial of our Excel add-in from our download page, and run these examples in Excel.

Or you can run the examples in your browser using RiskAMP web.

Whatsapp pocket registration code download. You can also use the embedded spreadsheets below to run monte carlo simulations right on this page.

Investment Portfolio Model

A typical investment portfolio model includes an opening balance, projections for returns and costs over several years, and a closing balance at some time in the future. A simple spreadsheet model might look like this:

Figure A: The original model

In figure A, the model is based on a fixed period (annual) return of 5.4%. Over the course of 5 years, this results in a return of 30.08%.

While the 5.4% is an expected return, we know that actual returns can vary greatly. The first step in building the Monte Carlo model is replacing these fixed returns with randomly distributed values, to better approximate the real world.

Step 1: Adding Random Data

In the Monte Carlo model, instead of a fixed 5.4% return, we anticipate that the return will be normally distributed with a mean (average) of 5.4% and a standard deviation of 7.3%. For each return cell in the spreadsheet (column D), we use the random function NormalValue:

Figure B: Adding random data

In figure B, the return in each period has been changed from a fixed 5.4% to a randomly distributed return, using the function seen in the function bar. The returns in each period are randomly generated. If you recalculate the model at this step, you will see each return change. The total return (F11) can also differ significantly from the original value (30.08%).

Randomly-distributed returns seem like a better approximation of the real world, but taking a single random return isn’t useful. The key to using Monte Carlo simulation is to take many random values, recalculating the model each time, and then analyze the results.

Step 2: Running a Monte Carlo Simulation

A Monte Carlo simulation calculates the same model many many times, and tries to generate useful information from the results. To run a Monte Carlo simulation, click the “Play” button next to the spreadsheet. (In Excel, use the “Run Simulation” button on the Monte Carlo toolbar).

The RiskAMP Add-in includes a number of functions to analyze the results of a Monte Carlo simulation. To start, we’ll look at the average results of the simulation using the SimulationMean function.

Note: the first time you enter these functions in a spreadsheet, you'll see an #N/A error. This is because the simulation hasn't collected data for the cell yet. Once you run a simulation, this error will go away.

Figure C: Average simulation results

In Figure C, we’ve added average simulation results in column H using the function s

Monte Carlo Simulation For Mac