|
|
|
|
|
 |
Frontline Systems, Inc. |
||||||||||||||||||
|
|||||||||||||||||||
|
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:
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 ExampleImagine 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:
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 VariablesTo 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.
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 FunctionsNet 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 ModelAt 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." |
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|||||||||||||||||||||||||||||||||||||||||||||
 |
|
|
