Monte Carlo Simulation Tutorial - Introduction

Monte Carlo Simulation Tutorial

Welcome to our tutorial on Monte Carlo simulation -- from Frontline Systems, developers of the Excel Solver and Risk Solver software.  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 software with Interactive Simulation -- which Dr. Sam Savage calls "a cure for the Flaw of Averages."

Wait!  What is Monte Carlo simulation?  Consult our Monte Carlo Simulation Introduction page for answers to:

• What is Monte Carlo Simulation?
• Why Should I Use Monte Carlo Simulation?
• What Knowledge Do I Need to Use It?
• How Will This Help Me in My Work or Career?

For background on simulation analysis and simulation models, consult our Simulation Introduction.  For background on risk analysis, consult our Risk Analysis Overview.  Our Risk Analysis Tutorial is designed to sharpen your thinking about uncertainty and risk, and how to identify and quantify the uncertainties you face.

A Business Planning Example

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 >

< Back to Monte Carlo Simulation Introduction

To Learn More: For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.

User type Please Select Commercial User Consultant Corp Developer Govt/Military Professor Student VAR/ISV Firm Email address Name First Last Company University Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.