Download
1 / 48
480 likes | 598 Vues
Chapter 11. Simulation Models. Introduction. In the previous chapter, we introduced most of the important concepts for developing and analyzing spreadsheet simulation models.
E N D
Chapter 11 Simulation Models
Introduction • In the previous chapter, we introduced most of the important concepts for developing and analyzing spreadsheet simulation models. • We also discussed many of the features available in the powerful simulation add-in, @RISK, that you receive with this book. • Now we apply the tools to a wide variety of problems that can be analyzed with simulation.
Introduction continued • For convenience, we group the applications into four general areas: • operations models, • financial models, • marketing models, • games of chance. • The only overriding theme in this chapter is that simulation models can yield important insights in all of these areas. • You do not need to cover all of the models in this chapter or cover them in any particular order. • You can cover the ones of most interest to you in practically any order.
Operations models • Whether we are discussing the operations of a manufacturing or a service company, there is likely to be uncertainty that can be modeled with simulation. • In this section we look at examples of bidding for a government contract (uncertainty in the bids by competitors), warranty costs (uncertainty in the time until failure of an appliance), and drug production (uncertainty in the yield and timing).
Bidding for contracts • In situations where a company must bid against competitors, simulation can often be used to determine the company’s optimal bid. • Usually the company does not know what its competitors will bid, but it might have an idea about the range of the bids its competitors will choose. • In this section we show how to use simulation to determine a bid that maximizes the company’s expected profit.
Warranty costs • When you buy a new product, it usually carries a warranty. A typical warranty might state that if the product fails within a certain period such as one year, you will receive a new product at no cost, and it will carry the same warranty. • However, if the product fails after the warranty period, you have to bear the cost of replacing the product. • Due to random lifetimes of products, we need a way to estimate the warranty costs (to the manufacturer) of a product. • Example 11.2 illustrates how this can be accomplished with simulation.
Drug production with uncertain demand • In many manufacturing settings, products are produced in batches, and the usable yields from these batches are uncertain. • This is particularly true in the drug industry. • Example 11.3 illustrates how a drug manufacturer can take this uncertainty into account when planning production.
Deming’s funnel experiment • Edwards Deming was an American statistician whose views on quality management revolutionized the way companies do business across the world. • Deming has been given much of the credit for Japan’s spectacular post–World War II economic recovery. • He traveled around the United States giving a famous four-day seminar on quality management. • An important component of Deming’s seminar was his famous funnel experiment. • The funnel experiment is designed to show how businesses often greatly overadjust “stable” processes. • We illustrate how it works in Example 11.4.
Financial models • There are many financial applications where simulation can be applied. • Future cash flows, future stock prices, and future interest rates are some of the many uncertain variables financial analysts must deal with. • In every direction they turn, they see uncertainty. • In this section we analyze a few typical financial applications that can benefit from simulation modeling.
Financial planning models • Many companies, such as GM, Eli Lilly, Procter & Gamble, and Pfizer, use simulation in their capital budgeting and financial planning processes. • Simulation can be used to model the uncertainty associated with future cash flows. In particular, simulation can be used to answer questions such as the following: • What are the mean and variance of a project’s net present value (NPV)? • What is the probability that a project will have a negative NPV? • What are the mean and variance of a company’s profit during the next fiscal year? • What is the probability that a company will have to borrow more than $2 million during the next year? • Example 11.5 illustrates how simulation can be used to evaluate an investment opportunity.
Cash balance models • All companies track their cash balance over time. • As specific payments come due, companies sometimes need to take out short-term loans to keep a minimal cash balance. • Example 11.6 illustrates one such application.
Investment models • Individual investors typically want to choose investment strategies that meet some prespecified goal. • Example 11.7 is typical. • Here, a person wants to meet a retirement goal, starting at an early age.
Simulating stock prices and options • In this section, we illustrate how @RISK can be used to simulate stock prices.
Modeling the price of a stock • An enormous amount of research has been devoted to discovering the way stock prices change. • Although few agree on the best model of stock price changes, one popular model states that price changes follow a lognormal distribution. • Essentially, this means that the logarithm of a stock’s price at any time is a normally distributed random variable.
Modeling the price of a stock continued • To be more specific, the stock price ptat any time t in the future is related to the current price p0by the formula • Here, μ is the mean percentage growth rate of the stock; σ is the standard deviation of the growth rate, usually called the volatility; and Z is a normal random variable with mean 0 and standard deviation 1.
Modeling the price of a stock continued • The spreadsheet here illustrates how to estimate the parameters μ and σ in from monthly returns. (See the file Stock Returns.xlsx.)
Valuing a European call option • A European option on a stock gives the owner of the option the right to buy (if the option is a call option) or sell (if the option is a put option) 100 shares of a stock on a particular date for a particular price. • The price at which an option holder can buy or sell the stock is called the exercise price (or strike price) of the option. The date by which the option must be used (or “exercised”) is called the exercise date.
Valuing a European call option • You must pay for the option in the first place. • The question is, what is a fair price for such an option? Because option trading is a multibillion- dollar business, this is an important question. • Black and Scholes (1973) were the first to derive a formula for pricing options. • Shortly after that, Cox et al. (1979) derived a different but equivalent method for pricing options. • We use their method, which is based on the following extremely important result.
Option pricing result • The price of an option on a nondividend-paying stock must be the expected discounted value of the cash flows from an option on a stock having the same standard deviation as the stock on which the option is written and growing at the risk-free rate of interest. • Here, discounting is done continuously at the risk-free rate. (If the stock pays dividends, the risk-free rate should be replaced by the difference between the risk-free rate and the dividend rate in what follows.)
Option pricing result continued • One surprising implication of this result is that the price of the option does not depend on the mean growth rate of the stock itself, only on the risk-free rate and the standard deviation of the growth rate of the stock. • Example 11.9 illustrates how @RISK can be used to estimate the price of a European option.
Return on a portfolio with a stock and an option on the stock • We now extend the previous example by simulating a portfolio that includes a company’s stock and a call option on that stock.
Value of a more exotic call option • The European call option is fairly simple. A variety of other derivative securities are currently available. • In fact, their variety and complexity are what make them attractive – and dangerous for the unsuspecting investor. • We illustrate one variation of the basic call option, an Asian option. • Its payoff depends, not on the price at expiration of the underlying stock, but on the average price of the stock over the lifetime of the option.
Value of a more exotic call option continued • To price an Asian option (or any number of other exotic options), you again find the expected discounted value of the cash flow from the option, assuming that the stock grows at the risk-free rate. • Example 11.10 illustrates how to approximate this expected value with simulation.
Asian option • This option is a variation of the call option. • Its payoff depends not on the price at expiration of the underlying stock, but on the average price of the stock over the lifetime of the option. • To price an Asian option, we again need to find the expected discounted value of the payoff from the option, assuming that the stock grows at the risk-free rate.
Marketing models • There are plenty of opportunities for marketing departments to use simulation. • They face uncertainty in the brand-switching behavior of customers, the entry of new brands into the market, customer preferences for different attributes of products, the effects of advertising on sales, and so on. • We examine some interesting marketing applications of simulation in this section.
Models of customer loyalty • What is a loyal customer worth to a company? This is an extremely important question for companies. • It is an important part of customer relationship management, or CRM, currently one of the hottest topics in marketing. • Companies know that if customers become dissatisfied with the company’s product, they are likely to switch and never return. • Marketers refer to this customer loss as churn. • Example 11.11 uses a reasonable model of customer loyalty and simulation to estimate the worth of a customer to a company.
Models of customer loyalty continued • Example 11.12 is a variation of the previous example. We now investigate the effect of offering a customer an incentive to remain loyal.
Marketing and sales models • We conclude this marketing section with a model of marketing and selling condos. • Themain issue is the timing of sales, and we demonstrate how a deterministic model of this timing can provide very misleading results.
Simulating games of chance • We realize that this is a book about business applications. • However, it is instructive (and fun) to see how simulation can be used to analyze games of chance, including sports contests. • Indeed, many analysts refer to Monte Carlo simulation, and you can guess where that name comes from—the gambling casinos of Monte Carlo.
Simulating the game of craps • Most games of chance are great candidates for simulation because they are, by their very nature, driven by randomness. • In this section we examine one such game that is extremely popular in the gambling casinos: the game of craps. • In its most basic form, the game of craps is played as follows.
Simulating the game of craps continued • A player rolls two dice and observes the sum of the two sides turned up. If this sum is 7 or 11, the player wins immediately. • If the sum is 2, 3, or 12, the player loses immediately. • Otherwise, if this sum is any other number (4, 5, 6, 8, 9, or 10), that number becomes the player’s point. • Then the dice are thrown repeatedly until the sum is the player’s point or 7. • In case the player’s point occurs before a 7, the player wins. But if a 7 occurs before the point, the player loses. • Example 11.14 uses simulation to determine the properties of this game.
Simulating the NCAA basketball tournament • Each year the suspense reaches new levels as “March Madness” approaches, the time of the NCAA Basketball Tournament. • Which of the 64 teams in the tournament will reach the “Sweet Sixteen,” which will go on to the prestigious “Final Four,” and which team will be crowned champion? • We share this simulation in the following example.
Using TopRank with @RISK for powerful modeling • In this section, we illustrate how another Palisade Decision Tools add-in, TopRank, can be used together with @RISK as a very powerful modeling combination. • As you have seen, @RISK introduces uncertainty explicitly into a spreadsheet model by allowing several inputs to have probability distributions. • TopRank is a what-if tool that allows you to see which of many inputs have large effects on an output variable.
Using TopRank continued • Example 11.16, which illustrates how TopRank and @RISK can work in tandem, is an extremely important one. • Simulation in the business world is often used to analyze potential products. • The profitability of a new product is highly uncertain because it depends on many uncertain quantities. • Many companies begin the analysis of every new product by determining the uncertain quantities that can affect the profitability of the product. • This analysis is often the deciding factor in whether the product is developed and marketed.
Tornado charts • Perhaps the best way to understand TopRank results is through a tornado chart. • Each bar in the chart indicates the variation in NPV as an individual input varies its minimum and maximum.
Tornado charts continued • Because the longer bars are always on the top and the shortest ones are always on the bottom, the inputs at the top of the chart are always the most important ones. • In this case the five most important inputs are product lifetime, unit price, initial demand, discount rate, and unit production cost. • Clearly, if SimTex is going to simulate the product’s NPV, it should spend most of its time accurately assessing the distribution of these five key inputs.
Tornado charts continued • In contrast, the tornado chart indicates that annual fixed cost and salvage value have virtually no effect on NPV. Therefore, little effort should be spent trying to estimate their values accurately - the base-case value will suffice. • Before proceeding to a simulation, we mention two other chart type available in TopRank: spider charts and sensitivity charts. Let’s look at a spider chart first.
Spider charts • A spider chart for the SimTex model appears below. • This chart is fairly straightforward. • For each of the five inputs, there is a curve that shows the percentage change in NPV as a function of the percentage change in the input - over the range we specified for the input.
Spider charts continued • From the spider chart we learn (not surprisingly) that changes in unit price, unit cost, and initial demand result in linear changes in NPV. • Also, a 1% increase in unit price results in a larger percentage increase in NPV than does a 1% percentage increase in demand. • As the discount rate increases, NPV decreases, but the rate of decrease slows; after a while increases in the discount rate cannot decrease NPV much further. Increases in product lifetime appear to increase product NPV in a complex nonlinear fashion.
Sensitivity charts • This chart type is similar to the spider chart, except that it shows one input only. Also it shows actual values rather than percentage changes. • To get a sensitivity chart for any input/output combination, click on the desired input and output in TopRank Results window, and then click on the Graph button and select Sensitivity option. • For example, this graph shows NPV versus product lifetime.
@RISK simulation • The sensitivity analysis with TopRank has indicated that the five key drivers of NPV are product lifetime, unit price, unit cost, initial product demand, and discount rate. • We will run an @RISK simulation of this model to estimate the distribution of NPV earned by Biathnon. • We will keep all inputs other than the five key inputs fixed at their base values, and we will use @RISK functions for the key inputs.
@RISK simulation continued • Actually we will use random functions for a product lifetime, unit price, unit cost and initial demand, and will vary discount rate systematically with the RISKSIMTABLE function. • Which probability distributions should we use to model the product lifetime, unit price, unit cost, and initial demand inputs?
@RISK simulation continued • There are several ways to proceed in general. • First, if we have a lot of historical data on any input, we can use the fitting capabilities of @RISK to fit a distribution to the historical data. • Second, we can use @RISK model window to examine shapes of potential distributions that look like good candidates. • Finally, we can choose a simple distribution that management has confidence in and assess its parameters. • We chose the latter approach, using a triangular distribution for each of the random inputs.
@RISK simulation continued • The use of a triangular random variable is common at many companies. The triangular distribution is often used because, unlike the normal distribution, it makes no assumption that the distribution of the uncertain quantity is symmetric about the mean or most likely value. • To assess a triangular distribution for any input, all we need are the minimum, most likely, and maximum values for the input. We use the same values of these that we used in the TopRank analysis. They are shown in columns E-G of the table on the next slide.
Conclusion • We claimed in the previous chapter that spreadsheet simulation, especially together with an add-in like @RISK, is a very powerful tool. • After seeing the examples in this chapter, you should now appreciate how powerful and flexible simulation is. • Unlike Solver optimization models, where you often make simplifying assumptions to achieve linearity, say, you can allow virtually anything in simulation models.
Conclusion continued • All you need to do is relate output cells to input cells with appropriate formulas, where any of the input cells can contain probability distributions to reflect uncertainty. • The results of the simulation then show the distribution of any particular output.
