Monte Carlo Simulation Tutorial - Introduction

Welcome to our tutorial on Monte Carlo simulation -- from Frontline Systems, developers of the Excel Solver and Risk Solver Platform. In the next few pages, we'll show how you can convert a conventional spreadsheet model of a business plan sales forecast -- one that yields a flawed "average" Net Profit forecast based on average inputs -- into a far more realistic and useful simulation model that reveals the full range of Net Profit outcomes. And we'll show the power of Risk Solver Platform software with Interactive Simulation -- which Dr. Sam Savage calls "a cure for the Flaw of Averages."

For background on risk analysis consult our Risk Analysis Overview and to view some video showing our Monte Carlo simulation tool in use visit our Video Demos page.

A Business Planning Example

You can follow along with this example in Risk Solver Platform. After installing Risk Solver Platform and selecting the Risk Solver Platform menu tab, go to to Help > Simulation Examples and then click on the BusinesForecast.xls link to open the model.

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

• Sales in units
• Price per unit
• Unit cost
• Fixed costs

Profit will be calculated as Profit = Sales * (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 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.

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. 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. Net Profit is calculated as Profit = Sales * (Price - Unit cost) -Fixed costs. Sales Volume, Selling Price and Unit Cost are all uncertain variables, so Net Profit is an uncertain function.

The Flawed Average Model

At this point, we can summarize the problem in the Excel model pictured below, which calculates Net Profit based on average sales, price, and unit cost.

The Net Profit figure of $117,750 calculated by this model, based on average values for the uncertain factors, is quite misleading, as we’ll see in a moment. 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."

Next: Introducing Uncertainty >

< Risk Analysis Tutorials Overview Page