Discussion 5 -

Chase@2000

Camm_4e_Ch11_PPT-Module5.pptx

Home >Education homework help >Discussion 5 -

Business Analytics

Monte Carlo Simulation

Chapter 11

Introduction (Slide 1 of 2)

Uncertainty pervades decision making in business, government, and our personal lives.

Monte Carlo simulation: Used to evaluate the impact of uncertainty on a decision.

Simulation models have been successfully used in a variety of disciplines:

Financial applications include investment planning, project selection, and option pricing.

Marketing applications include new product development and the timing of market entry for a product.

Management applications include project management, inventory ordering, capacity planning, and revenue management.

Introduction (Slide 2 of 2)

Probability distribution: Represents not only the range of possible values but also the relative likelihood of various outcomes.

A simulation model extends the spreadsheet modeling approach by replacing the use of single values for parameters with a probability distribution of possible values.

Parameters that are not known with a high degree of certainty are called random, or uncertain, variables.

The values for random variables are randomly generated from the specified probability distributions.

Simulation results help us to make decision recommendations for the controllable inputs that address not only the average output but also the variability of the output.

Risk Analysis for Sanotronics LLC

Base-Case Scenario

Worst-Case Scenario

Best-Case Scenario

Sanotronics Spreadsheet Model

Use of Probability Distributions to Represent Random Variables

Generating Values for Random Variables with Excel

Executing Simulation Trials with Excel

Measuring and Analyzing Simulation Output

Risk Analysis for Sanotronics LLC (Slide 1 of 33)

Decision makers are interested in risk analysis, that is, quantifying the likelihood and magnitude of an undesirable outcome.

Illustration: The Sanotronics Problem:

Analyze the first-year profit potential for a new medical device.

Key parameters to determine first-year profit:

Selling price per unit (p).

First-year demand (d).

Risk Analysis for Sanotronics LLC (Slide 2 of 33)

Sanotronics estimates with a high level of certainty that:

The device’s selling price will be $249 per unit.

The first-year administrative and advertising costs will total $1,000,000.

Sanotronics is not certain about the values for the cost of direct labor, the cost of parts, and the first-year demand; at this stage of the planning process, their base estimates of these inputs are:

$45 per unit for the direct labor cost.

$90 per unit for the parts cost.

15,000 units for the first-year demand.

Risk Analysis for Sanotronics LLC (Slide 3 of 33)

Base-Case Scenario:

Sanotronics’ first-year profit is computed by:

Sanotronics is certain of a selling price of $249 per unit, and administrative and advertising costs total $1,000,000. Substituting these values in the above equation yields:

Risk Analysis for Sanotronics LLC (Slide 4 of 33)

Base-Case Scenario (cont.):

Sanotronics’ base-case estimates of the direct labor cost per unit, parts cost per unit, and first-year demand are $45, $90, and 15,000 units, respectively. These values constitute the base-case scenario for Sanotronics.

Substituting these values into the equation yields the following profit projection:

Thus, the base-case scenario leads to an anticipated profit of $710,000.

Risk Analysis for Sanotronics LLC (Slide 5 of 33)

Base-Case Scenario (cont.):

Sanotronics is aware that the values of direct labor cost per unit, parts cost per unit, and first-year demand are uncertain and may consider performing a what-if analysis.

A what-if analysis involves considering alternative values for the random variables (direct labor cost, parts cost, and first-year demand) and computing the resulting value for the output (profit).

Sanotronics could use ranges of labor costs, parts cost, and first-year demand to perform a what-if analysis to evaluate a worst-case scenario and a best-case scenario.

Risk Analysis for Sanotronics LLC (Slide 6 of 33)

Worst-Case Scenario:

The worst-case for:

The direct labor cost = $47 (the highest value)

The parts cost = $100 (the highest value)

Demand = 0 units (the lowest value)

Substituting these values into the equation leads to the following profit projection:

So, the worst-case scenario leads to a projected loss of $1,000,000.

Risk Analysis for Sanotronics LLC (Slide 7 of 33)

Best-Case Scenario:

The best-case for:

The direct labor cost = $43 (the lowest value).

The parts cost = $80 (the lowest value).

Demand = 30,000 units (the highest value).

Substituting these values into the equation leads to the following profit projection:

So, the best-case scenario leads to a projected profit of $2,780,000.

Risk Analysis for Sanotronics LLC (Slide 8 of 33)

Best-Case Scenario (cont.):

At this point, the what-if analysis provides the conclusion that profits may range from a loss of $1,000,000 to a profit of $2,780,000 with a base-case profit of $710,000.

Although the base-case profit of $710,000 is possible, the what-if analysis indicates that either a substantial loss or a substantial profit is possible.

Simple what-if analyses do not indicate the likelihood of the various profit or loss values.

To conduct a more thorough evaluation of risk by obtaining insight on the potential magnitude and probability of undesirable outcomes, we now turn to developing a spreadsheet simulation model.

Risk Analysis for Sanotronics LLC (Slide 9 of 33)

Sanotronics Spreadsheet Model:

Figure 11.1 provides the formula and value views for Sanotronics.

Data on selling price per unit, administrative and advertising cost, direct labor cost per unit, parts cost per unit, and demand are in cells B4 to B8.

The profit calculation, corresponding to the equation, is expressed in cell B11 using appropriate cell references and formula logic.

The spreadsheet model computes profit for the base-case scenario; by changing one or more values for the input parameters, the spreadsheet model can be used to conduct a manual what-if analysis.

Risk Analysis for Sanotronics LLC (Slide 10 of 33)

Figure 11.1: Excel Worksheet For Sanotronics

For the values shown in Figure 11.1, the spreadsheet model computes profit for the base-case scenario.

By changing one or more values for the input parameters, the spreadsheet model can be used to conduct a manual what-if analysis (e.g., the best-case and worst-case scenarios).

Risk Analysis for Sanotronics LLC (Slide 11 of 33)

Use of Probability Distributions to Represent Random Variables:

Using the what-if approach to risk analysis, manually select values for the random variables, and then compute the resulting profit.

Monte Carlo simulation randomly generates values for the random variables.

Sanotronics researched the random variables to identify probability distributions for the direct labor cost per unit, the parts cost per unit, and first-year demand.

Risk Analysis for Sanotronics LLC (Slide 12 of 33)

Figure 11.2: Probability Distribution for Direct Labor Cost Per Unit

Based on recent wage rates and estimated processing requirements of the device, Sanotronics believes that the direct labor cost will range from $43 to $47 per unit and is described by the discrete probability distribution shown in Figure 11.2.

Risk Analysis for Sanotronics LLC (Slide 13 of 33)

Use of Probability Distributions to Represent Random Variables (cont.):

There is 0.1 probability that the direct labor cost will be $43 per unit, a 0.2 probability that the direct labor cost will be $44 per unit, and so on.

The highest probability, 0.4, is associated with a direct labor cost of $45 per unit.

Based on the assumption that the direct labor cost per unit is best described by a discrete probability distribution, the direct labor cost per unit can take on only the values of $43, $44, $45, $46, or $47.

Risk Analysis for Sanotronics LLC (Slide 14 of 33)

Use of Probability Distributions to Represent Random Variables (cont.):

Sanotronics is relatively unsure of the parts cost because it depends on the general economy, the overall demand for parts, and the pricing policy of Sanotronics’ parts suppliers.

Sanotronics is confident that the parts cost will be between $80 and $100 per unit but is unsure as to whether any particular values between $80 and $100 are more likely than others.

Risk Analysis for Sanotronics LLC (Slide 15 of 33)

Figure 11.3: Uniform Probability Distribution for Parts Cost Per Unit

Sanotronics decides to describe the uncertainty in parts cost with a uniform probability distribution, as shown in Figure 11.3.

Risk Analysis for Sanotronics LLC (Slide 16 of 33)

Use of Probability Distributions to Represent Random Variables (cont.):

Costs per unit between $80 and $100 are equally likely.

A uniform probability distribution is an example of a continuous probability distribution, which means that the parts cost can take on any value between $80 and $100.

The mean or expected value of first-year demand is 15,000 units. The standard deviation of 4,500 units describes the variability in the first-year demand.

Risk Analysis for Sanotronics LLC (Slide 17 of 33)

Figure 11.4: Normal Probability Distribution for First-Year Demand

Based on sales of comparable medical devices, Sanotronics believes that first-year demand is described by the normal probability distribution shown in Figure 11.4.

Risk Analysis for Sanotronics LLC (Slide 18 of 33)

Generating Values for Random Variables with Excel:

Computer-generated random numbers are randomly selected numbers from 0 up to, but not including, 1; this interval is denoted [0, 1).

Placing the formula =RAND() in a cell of an Excel worksheet will result in a random number between 0 and 1 being placed into that cell.

In the Sanotronics model, representative values must be generated for the random variables corresponding to direct labor cost per unit, the parts cost per unit, and the first-year demand.

Risk Analysis for Sanotronics LLC (Slide 19 of 33)

Table 11.1: Random Number Intervals for Generating Value of Direct Labor Cost per Unit

Direct Labor Cost per Unit	Probability	Interval of Random Numbers
$43	0.1	[0.0, 0.1)
$44	0.2	[0.1, 0.3)
$45	0.4	[0.3, 0.7)
$46	0.2	[0.7, 0.9)
$47	0.1	[0.9, 1.0)

The interval of random numbers from 0 up to but not including 0.1, [0, 0.1), is associated with a direct labor cost of $43.

The interval of random numbers from 0.1 up to but not including 0.3, [0.1, 0.3), is associated with a direct labor cost of $44, and so on.

With this assignment of random number intervals to the possible values of the direct labor cost, the probability of generating a random number in any interval is equal to the probability of obtaining the corresponding value for the direct labor cost.

Risk Analysis for Sanotronics LLC (Slide 20 of 33)

Generating Values for Random Variables with Excel (cont.):

Using the RAND function in Excel, suppose the random number is 0.9109.

Since, 0.9109 is in the interval [0.9, 1.0), the corresponding simulated value for the direct labor cost is $47 per unit.

If the random number is 0.2841, from Table 11.1, the simulated value for the direct labor cost is $44 per unit.

Risk Analysis for Sanotronics LLC (Slide 21 of 33)

Generating Values for Random Variables with Excel (cont.):

The probability distribution for the parts cost per unit is the uniform distribution.

To generate a value for a random variable we use the Excel formula:

Risk Analysis for Sanotronics LLC (Slide 22 of 33)

Generating Values for Random Variables with Excel (cont.):

For Sanotronics, the parts cost per unit is a uniformly distributed random variable with a lower bound of $80 and an upper bound of $100.

Applying the equation in the previous slide, we get:

The first term of the above equation is 80 because Sanotronics is assuming that the parts cost will never drop below $80 per unit.

corresponds to how much more than the lower bound the simulated value of parts cost is.

Risk Analysis for Sanotronics LLC (Slide 23 of 33)

Figure 11.5: Generation of Value for Parts Cost per Unit Corresponding to Random Number 0.4576

Suppose that a random number of 0.4576 is generated by the RAND function; the value for the parts cost is:

Risk Analysis for Sanotronics LLC (Slide 24 of 33)

Generating Values for Random Variables with Excel (cont.):

Suppose that a random number of 0.5842 is generated on the next trial; the value for the parts cost is:

Need to generate a value corresponding to the probability distributions for first-year demand.

To generate a value for a random variable characterized by a normal distribution with a specified mean and standard deviation, the following Excel formula is used:

Risk Analysis for Sanotronics LLC (Slide 25 of 33)

which implies 60.26 percent of the area under the normal curve is to the left of this value.

Generating Values for Random Variables with Excel (cont.):

The first-year demand is normally distributed with a mean of 15,000 units and a standard deviation of 4500 units (see Figure 11.6).

Suppose the random number is 0.6026.

Applying equation 11.5, we get:

Risk Analysis for Sanotronics LLC (Slide 26 of 33)

Figure 11.6: Generation of Value for First-Year Demand Corresponding to Random Number 0.6026

Risk Analysis for Sanotronics LLC (Slide 27 of 33)

Figure 11.7: Formula Worksheet for Sanotronics

The static values in Figure 11.1 for these parameters in cells B6, B7, and B8 are replaced with cell formulas that will randomly generate values whenever the spreadsheet is recalculated.

Risk Analysis for Sanotronics LLC (Slide 28 of 33)

Generating Values for Random Variables with Excel (cont.):

Cell B6 uses a random number generated by the RAND function and looks up the corresponding cost per unit by applying the VLOOKUP function to the table of intervals contained in cells A15:C19.

Cell B7 executes the equation (11.4) using references to the lower bound and upper bound of the uniform distribution of the parts cost in cells B22 and B23, respectively.

Cell B8 executes the equation (11.6) using references to the mean and standard deviation of the normal distribution of the first-year demand in cells D22 and D23, respectively.

Risk Analysis for Sanotronics LLC (Slide 29 of 33)

Executing Simulation Trials with Excel:

To facilitate the execution of multiple simulation trials, use Excel’s Data Table functionality.

To populate the data table in cells A26 through E1025 in Figure 11.8, we execute the following steps:

Step 1. Select cell range A26:E1025.

Step 2. Click the Data tab in the Ribbon.

Step 3. Click What-If Analysis in the Forecast group and select Data Table…

Step 4. When the Data Table dialog box appears, leave the Row input cell: box blank and enter any empty cell in the spreadsheet (e.g., D1) into the Column input cell: box.

Step 5. Click OK.

Risk Analysis for Sanotronics LLC (Slide 30 of 33)

Figure 11.8: Setting Up Sanotronics Spreadsheet for 1,000 Simulation Trials

Figure 11.8 shows that A26:A1025 numbers the 1,000 simulation trials (rows 47 through 1,024 are hidden).

Cells B26:E26 contain references to the cells corresponding to Direct Labor Cost, Parts Cost per Unit, Demand and Profit.

Risk Analysis for Sanotronics LLC (Slide 31 of 33)

Figure 11.9: Output from Sanotronics Simulation

Figure 11.9 shows the results of a set of 1,000 simulation trials.

After executing the simulation with the data table, each row in this table corresponds to a distinct simulation trial consisting of different values of the random variables.

Risk Analysis for Sanotronics LLC (Slide 32 of 33)

Measuring and Analyzing Simulation Output:

For the collection of simulation trials, it is helpful to compute descriptive statistics such as sample count, minimum and maximum sample value, sample mean, sample standard deviation, sample proportion, and sample standard error.

To compute these statistics for the Sanotronics example, we use the following Excel functions:

Risk Analysis for Sanotronics LLC (Slide 33 of 33)

Measuring and Analyzing Simulation Output (cont.):

Figure 11.9 shows a mean profit of $712,014, standard deviation of $524,726, extremes ranging between $1,011,895 and $2,302,801, and a sample proportion of 0.087.

Recall from the what-if analysis, the base-case scenario projected a profit of $710,000, the worst-case scenario projected a loss of $1,000,000, and the best-case scenario projected a profit of $2,591,000.

The simulation results help Sanotronics’ management better understand the profit/loss potential of the new medical device.

The 0.070 to 0.104 probability of a loss may be acceptable to management.

Inventory Policy Analysis for Promus Corp

Spreadsheet Model for Promus

Generating Values for Promus Corp’s Demand

Executing Simulation Trials and Analyzing Output

Inventory Policy Analysis for Promus Corp (Slide 1 of 17)

Promus Corp sells wireless routers. Each router costs Promus $75 and sells for $125. Thus, the profit is $50 for each router sold.

Monthly demand for the router is uncertain, but Promus has collected data to help characterize it.

Promus receives monthly deliveries from its supplier and replenishes its inventory to a predetermined level, which is referred to as the replenishment level.

Inventory Policy Analysis for Promus Corp (Slide 2 of 17)

If monthly demand is less than the replenishment level, Promus must hold the routers in inventory. This costs $15 per router due to cost of storage, insurance, and cost-of-capital.

If monthly demand is greater than the replenishment level, then a stock-out occurs, which results in a shortage cost of $30 being charged for each unit of demand that cannot be satisfied.

Management would like to use a simulation to determine the monthly net profit resulting from using different replenishment levels.

Inventory Policy Analysis for Promus Corp (Slide 3 of 17)

Spreadsheet Model for Promus

Input parameters:

Gross profit per unit (known to be $50)

Unit holding cost ($15)

Unit shortage cost ($30)

Monthly demand (uncertain, D)

Replenishment level (a decision, Q)

Output measure of interest:

Monthly net profit

Inventory Policy Analysis for Promus Corp (Slide 4 of 17)

Spreadsheet Model for Promus (cont.)

For a specified replenishment level (Q) and observed monthly demand (D), monthly net profit is calculated as follows:

Inventory Policy Analysis for Promus Corp (Slide 5 of 17)

Spreadsheet Model for Promus (cont.)

When demand is less than or equal to the replenishment level, D units are sold, and an inventory holding cost of $15 is incurred for each of Q minus D units that remain in storage, and no shortage occurs. This results in:

When demand is greater than the replenishment level, Q units are sold, no inventory remains, and a shortage cost of D minus Q occurs. This results in:

Inventory Policy Analysis for Promus Corp (Slide 6 of 17)

Spreadsheet Model for Promus (cont.)

For a replenishment level of 90 and monthly demand of 100, Figure 11.10 displays the Excel implementation of equation (11.7).

The gross profit per unit, holding cost per unit, and shortage cost per unit data are entered into cells B4, B5, and B6, respectively.

Observed demand of 100 units is entered into B7.

Replenishment level is entered into B10.

Inventory Policy Analysis for Promus Corp (Slide 7 of 17)

Figure 11.10: Excel Worksheet for Promus Corp

Inventory Policy Analysis for Promus Corp (Slide 8 of 17)

Generating Values for Promus Corp’s Demand

The next step is to characterize the demand uncertainty and randomly generate values for demand in a manner that reflects the relative likelihood of future monthly demand values.

Figure 11.11 plots the monthly demand over the past 60 months.

Inventory Policy Analysis for Promus Corp (Slide 9 of 17)

Figure 11.11: Router Demand Over Time

There is no detectable pattern in the variation. Promus feels comfortable in treating monthly demand as independent quantities.

Inventory Policy Analysis for Promus Corp (Slide 10 of 17)

Figure 11.12: Distribution of Monthly Router Demand

The distribution of monthly demand is bell-shaped, suggesting that a normal distribution would be a good fit for the data.

Inventory Policy Analysis for Promus Corp (Slide 11 of 17)

Generating Values for Promus Corp’s Demand (cont.)

The sample mean is calculated as 101 and the sample standard deviation is calculated as 17.

In Figure 11.13, the spreadsheet model is modified by replacing the static value of demand with the formula =NORM.INV(RAND(),E6,E7), where cells E6 and E7 contain values for the sample mean and standard deviation.

Inventory Policy Analysis for Promus Corp (Slide 12 of 17)

Figure 11.13: Modeling Demand as a Normal Random Variable

Inventory Policy Analysis for Promus Corp (Slide 13 of 17)

Figure 11.14: Setting Up Promus Spreadsheet for Profit Comparison

Promus would like to compare the average monthly profit corresponding to a replenishment level of 90 units to the average monthly profit corresponding to a replenishment level of 110 units.

In cells C11:C15, we implement the same logic for a replenishment level of 100 that we did for a replenishment level of 90 in cells B11:B15.

Inventory Policy Analysis for Promus Corp (Slide 14 of 17)

Executing Simulation Trials and Analyzing Output

Each trial in the simulation involves randomly generating a value for the corresponding month’s demand, and then computing the difference in the net profit when using a replenishment level of 110 versus 90.

Figure 11.15 shows the structure of the spreadsheet for the execution of 1,000 simulation trials.

Inventory Policy Analysis for Promus Corp (Slide 15 of 17)

Figure 11.15: Setting Up Promus Spreadsheet for 1,000 Simulation Trials

To populate the table of simulation trials in the Model Worksheet, use the following steps:

Step 1. Select cell range A25:L1024

Step 2. Click the Data tab in the Ribbon

Step 3. Click What-If Analysis in the Forecast group and select Data Table…

Step 4. When the Data Table dialog box appears, leave the Row input cell: box blank and enter any empty cell in the spreadsheet (e.g., D1) into the Column input cell: box

Step 5. Click OK

Inventory Policy Analysis for Promus Corp (Slide 16 of 17)

Figure 11.16: Output Worksheet for Promus Corp

Inventory Policy Analysis for Promus Corp (Slide 17 of 17)

Executing Simulation Trials and Analyzing Output (cont.)

Based on 1,000 simulation trials:

There is a mean difference of $667 between the net profit generated by a replenishment level of 110 versus 90.

Estimated probability of 0.657 that a replenishment level of 110 will generate a larger monthly profit than a replenishment level of 90.

A different set of 1,000 simulation trials can be generated by pressing F9.

Helped to gauge how much sampling error exists in the output statistics.

Simulation Modeling for Land Shark Inc.

Spreadsheet Model for Land Shark

Generating Values for Land Shark’s Random Variables

Executing Simulation Trials and Analyzing Output

Generating Bid Amounts with Fitted Distributions

Simulation Modeling for Land Shark Inc. (Slide 1 of 39)

Illustration: The Land Shark Problem:

Land Shark Inc. is a real estate company that purchases properties that it develops and then resells.

Land Shark has successfully acquired properties via first-price sealed-bid auctions.

In a first-price sealed-bid auction, each bidder submits a single concealed bid; the bids are then compared, and the party with the highest bid wins the property and pays the bid amount.

In case of a tie (a rare occurrence), a coin flip decides the winner.

Simulation Modeling for Land Shark Inc. (Slide 2 of 39)

Land Shark has identified a commercial property of interest and estimates the value of this property to be $1,389,000.

Table 11.2 displays bid data on 13 recent auctions that Land Shark believes are similar to the upcoming property auction.

Land Shark is considering a bid of $1,229,000 and would like to evaluate its chances of winning the upcoming auction with this bid.

Simulation Modeling for Land Shark Inc. (Slide 3 of 39)

Table 11.2: Bid Data on Commercial Property Auctions

Bid Amount (as a Fraction of Estimated Property Value)

Property No.	Bid 1	Bid 2	Bid 3	Bid 4	Bid 5	Bid 6	Bid 7	Bid 8
1	0.830	0.797	0.833	0.878	0.839	0.843
2	0.835	0.823	0.781	0.892	0.767	0.787
3	0.763	0.862	0.814	0.895
4	0.771	0.859	0.867	0.850	0.833
5	0.836	0.898	0.831	0.897	0.831	0.657	0.846
6	0.850	0.863	0.825	0.910	0.848
7	0.890	0.820	0.874	0.877	0.818

Simulation Modeling for Land Shark Inc. (Slide 4 of 39)

Table 11.2: Bid Data on Commercial Property Auctions (cont.)

Bid Amount (as a Fraction of Estimated Property Value)

Property No.	Bid 1	Bid 2	Bid 3	Bid 4	Bid 5	Bid 6	Bid 7	Bid 8
8	0.804	0.881	0.786	0.884	0.773	0.819	0.824
9	0.819	0.851	0.786	0.896	0.784	0.792
10	0.860	0.756	0.876	0.887	0.866
11	0.880	0.834	0.831	0.871	0.857	0.759
12	0.810	0.870
13	0.887	0.716	0.817	0.9	0.869	0.885	0.856	0.761

Simulation Modeling for Land Shark Inc. (Slide 5 of 39)

Spreadsheet Model for Land Shark:

Land Shark is considering a bid of $1,229,000.

To evaluate its chances of winning the upcoming auction with this bid, develop a simulation model for the auction.

First, identify the input parameters for the upcoming auction, which are the:

Estimated value of the property.

Number of bidders.

Submitted bid amounts.

Simulation Modeling for Land Shark Inc. (Slide 6 of 39)

Spreadsheet Model for Land Shark (cont.)

The output that we are interested in is whether Land Shark wins the simulated auction given its specified amount and Land Shark’s net return.

If Land Shark wins the auction, its return is computed as the difference between the estimated value of the property and its bid amount.

If Land Shark does not win the auction, its return is $0.

Whether Land Shark wins the simulated auction can be determined by comparing Land Shark’s bid amount to the largest competitor bid amount.

Simulation Modeling for Land Shark Inc. (Slide 7 of 39)

Spreadsheet Model for Land Shark (cont.):

From Table 11.2, we assume that the number of submitted bid amounts may range from two to eight.

To determine the largest competitor bid amount, the spreadsheet model must be able to compute the maximum bid from a varying number of bids.

Simulation Modeling for Land Shark Inc. (Slide 8 of 39)

Figure 11.17: Base Spreadsheet Model for Land Shark

Simulation Modeling for Land Shark Inc. (Slide 9 of 39)

Spreadsheet Model for Land Shark (cont.):

Cell B4 contains the estimated value of the property; cell B5 contains a value for the number of bidders (an uncertain value).

Cells B8 through B15 contain values of eight possible competing bids expressed as fractions of the property’s estimated value (also uncertain values).

Cells C8 through C15 express the respective bid fractions in cells B8 through B15 as dollar amounts using the IF function.

If a bid index (from the range A8:A15) exceeds the realized number of bidders in cell B5, the corresponding bid amount in the cell range C8:C15 is set to $0, otherwise the bid amount is computed.

Simulation Modeling for Land Shark Inc. (Slide 10 of 39)

Spreadsheet Model for Land Shark (cont.):

It compares the bid number in cell A8 to the number of bidders in cell B5 and if the bid number exceeds the number of bidders, a bid amount of $0 is calculated so that the bid is not considered.

Otherwise, the bid amount is calculated by multiplying the bid percentage (cell B8) by the estimated value of the property (cell B4).

Cell B18 contains Land Shark’s bid amount.

Simulation Modeling for Land Shark Inc. (Slide 11 of 39)

Spreadsheet Model for Land Shark (cont.):

Cell B19 computes the largest competing bid by taking the maximum value over the range C8:C15.

Simulation Modeling for Land Shark Inc. (Slide 12 of 39)

Generating Values for Land Shark’s Random Variables:

In the Land Shark simulation model, there are uncertain quantities:

The number of competing bidders.

How much the competitors will bid (as a percentage of property’s value).

To specify probability distributions for uncertain quantities, or random variables, first consider the number of bidders.

Frequency distribution shown in Figure 11.18.

Has ranged from two to eight over the past 56 auctions.

Simulation Modeling for Land Shark Inc. (Slide 13 of 39)

Generating Values for Land Shark’s Random Variables (cont.):

Figure 11.18 suggests that the relative likelihood of different values for the number of bidders appears to be equal.

Thus, Land Shark decides to use an integer uniform distribution in which the number of bidders is equally likely to be 2, 3, 4, 5, 6, 7, or 8.

To generate a value for a random variable characterized by an integer uniform distribution, the following Excel formula is used:

Simulation Modeling for Land Shark Inc. (Slide 14 of 39)

Figure 11.18: Frequency Distribution of Number of Bidders in 56 Previous Auctions

Simulation Modeling for Land Shark Inc. (Slide 15 of 39)

Generating Values for Land Shark’s Random Variables (cont.):

For Land Shark, the lower integer value is 2 and the upper value is 8.

Applying equation (11.8), enter the formula RANDBETWEEN(2, 8) into cell B5.

Each competitor’s bid fraction is also a random variable.

From the past 56 auctions, there has been a total of 280 observations of how competitors have bid.

Figure 11.19 contains a histogram of the bid amount data grouped into 13 bins.

Simulation Modeling for Land Shark Inc. (Slide 16 of 39)

Figure 11.19: Frequency Distribution of 280 Bid Fractions in 56 Previous Auctions

We see that the bid amount distribution is negatively skewed, and that bid amounts most commonly occur in the range (0.875, 0.90).

Simulation Modeling for Land Shark Inc. (Slide 17 of 39)

Generating Values for Land Shark’s Random Variables (cont.):

There are several ways to use the 280 bid amount observations as a basis for simulating bid amount values in the spreadsheet model.

Could use Figure 11.19 as the basis for choosing a discrete probability distribution to represent this uncertain value.

Such a discrete probability distribution would result in a loss of information, as only bid percentages of, say, 0.65, 0.675, 0.70, 0.725, 0.75, 0.775, 0.80, 0.825, 0.85, 0.875, 0.90, 0.925, and 0.95 would be possible.

From the 280 observations, the bid percentages take on many values between the minimum of 0.645 and the maximum of 0.947.

Simulation Modeling for Land Shark Inc. (Slide 18 of 39)

Generating Values for Land Shark’s Random Variables (cont.):

Two other primary alternatives are to:

Directly sample from the 280 observations to generate values for simulation trials.

To fit a continuous probability distribution based on the 280 observations.

Directly sampling from data is a good modeling choice if Land Shark believes that these 280 bid fraction values are an accurate representation of the distribution of future bids.

Simulation Modeling for Land Shark Inc. (Slide 19 of 39)

Generating Values for Land Shark’s Random Variables (cont.):

To simulate the bids for the upcoming auction, randomly select a value from one of these 280 bid values.

Resampling empirical data is a good approach only when the data adequately represent the range of possible values and the distribution of values across this range.

If sample data do not adequately describe possible values, may be more appropriate to identify a probability distribution that fits the data.

This does not mean the resulting values are uniformly distributed across a range but rather that the generated values will have an empirical distribution similar to the sample on which they are based.

Simulation Modeling for Land Shark Inc. (Slide 20 of 39)

Executing Simulation Trials and Analyzing Output

To prepare the spreadsheet for the execution of 1,000 simulation trials, structure the spreadsheet as in Figure 11.20.

Cell range A24:L1024 is prepared to hold the 1,000 simulation trials.

Cell range A25:A1024 numbers the rows that will correspond to the simulation trials.

First row (B25:L25) contains Excel formulas referencing random variables and the two output measures.

Simulation Modeling for Land Shark Inc. (Slide 21 of 39)

Figure 11.20: Setting Up Land Shark Spreadsheet for 1,000 Simulation Trials

Simulation Modeling for Land Shark Inc. (Slide 22 of 39)

Executing Simulation Trials and Analyzing Output (cont.):

To populate the table of simulation trials:

Step 1. Select cell range A25:L1024.

Step 2. Click the Data tab in the Ribbon.

Step 3. Click What-If Analysis in the Forecast group and select Data Table…

Step 4. When the Data Table dialog box appears, leave the Row input cell: box blank and enter any empty cell in the spreadsheet into the Column input cell: box.

Step 5. Click OK.

Figure 11.21 shows the results of a set of 1,000 simulation trials.

Simulation Modeling for Land Shark Inc. (Slide 23 of 39)

Figure 11.21: Output from Land Shark Simulation

Simulation Modeling for Land Shark Inc. (Slide 24 of 39)

Executing Simulation Trials and Analyzing Output (cont.):

Based on this set of 1,000 simulation trials, when Land Shark bids $1,229,000:

Estimated mean return is $35,680.

Probability of winning is 0.223.

It wins the auction 223 times and loses 777 times.

A different set of 1,000 simulation trials can be generated by pressing F9.

To gauge sampling error, press F9 and observe the variance in output statistics.

Increasing the number of trials in a simulation will decrease variability in the summary statistics from one set to another.

Simulation Modeling for Land Shark Inc. (Slide 25 of 39)

Generating Bid Amounts with Fitted Distributions:

Another approach: Use the 280 bid observations to fit a continuous probability distribution to a histogram based on the data.

Advantage: It will allow you to generate values that may not exist in the list of original 280 observations, but still share characteristics with these data.

Disadvantage: The process is a bit more involved and requires more familiarity with probability distributions.

Goal: To identify a continuous probability distribution that fits the histogram of the bid fraction data shown in Figure 11.19.

Simulation Modeling for Land Shark Inc. (Slide 26 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

For the bid fraction data, seek a continuous probability distribution due to the large number of possible values for a submitted bid fraction.

The range of bid fractions has a lower bound of zero and upper bound of one; a competitor cannot bid a negative fraction, and a competitor will never bid more than the property’s estimated value.

There are many possible continuous probability distributions that have both lower and upper bounds, but some of the most common are the uniform, triangular, and beta distributions.

Simulation Modeling for Land Shark Inc. (Slide 27 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

The uniform distribution assumes each value between a specified minimum value and minimum value is equally likely, which does not appear to be the case for bid fractions shown in Figure 11.19.

The triangular distribution is a unimodal distribution characterized by three input parameters: minimum (a), mode (m), and maximum (b); while the shape of the bid fraction distribution does not appear exactly triangular, it could be a worthwhile option to explore.

To determine the mode (most likely) value of the triangular distribution, note that computing the mode of the effectively continuous bid fraction data is a bit dubious as no single value occurs frequently.

Simulation Modeling for Land Shark Inc. (Slide 28 of 39)

Figure 11.22: Fit of Triangular Distribution to Bid Fraction Data

Figure 11.22 provides a visualization of triangular distribution’s fit to the bid fraction data.

The triangle-shaped curve represents the theoretical continuous distribution from which values from the triangle distribution are generated.

The blue columns correspond to one possible sample of 280 values generated from the triangular distribution.

Comparing the blue curve (and blue columns) to the red columns representing the observed bid fractions, this triangular distribution appears to generate more bid fractions in the 0.645 to 0.80 range and fewer bid fractions in the 0.925 to 0.95 range.

Compared to the observed bid fraction data, Figure 11.22 shows that this triangular distribution appears more likely to generate smaller competing bid fractions than directly sampling from the 280 observed bid fractions.

Simulation Modeling for Land Shark Inc. (Slide 29 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

To generate a value for a random variable characterized by a triangular distribution, the following Excel formula is used:

Simulation Modeling for Land Shark Inc. (Slide 30 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

Applying equation (11.9) for the triangular distribution fit to the 280 bid observations yields:

Simulation Modeling for Land Shark Inc. (Slide 31 of 39)

Figure 11.23: Land Shark Formula Worksheet for Bid Fraction Value Generated from Triangular Distribution

Figure 11.23 displays the formula view of the Land Shark simulation model implementing equation (11.10) to generate bid fraction values.

Simulation Modeling for Land Shark Inc. (Slide 32 of 39)

Figure 11.24: Output from Land Shark Simulation Using Triangular Distribution to Generate Bid Fraction Values

In Figure 11.24, modeling bid fraction values with a triangular distribution results in a 95% confidence interval of $49,224 to $58,616 on the mean return and a 95% confidence interval of 0.308 to 0.366 on the probability of winning the auction.

Simulation Modeling for Land Shark Inc. (Slide 33 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

The final alternative for modeling the bid fraction values would be to fit a beta distribution to the 280 bid observations.

The beta distribution is a very flexible distribution characterized by four

Simulation Modeling for Land Shark Inc. (Slide 34 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

Simulation Modeling for Land Shark Inc. (Slide 35 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

To generate a value for a random variable characterized by a beta distribution, the following Excel formula is used:

For the Land Shark problem, substituting the values of the parameters results in:

Simulation Modeling for Land Shark Inc. (Slide 36 of 39)

Figure 11.25: Fit of Beta Distribution to Bid Fraction Data

Figure 11.25 provides a visualization of the beta distribution’s fit to the bid fraction data.

The blue curve represents the theoretical continuous distribution from which values from the beta distribution are generated.

The blue columns correspond to one possible sample of 280 values generated from the beta distribution.

When comparing the blue curve (and blue columns) to the red columns representing the observed bid fractions, this beta distribution appears to reasonably fit the observed bid fractions.

Simulation Modeling for Land Shark Inc. (Slide 37 of 39)

Figure 11.26: Land Shark Formula Worksheet for Bid Fraction Value Generated from Beta Distribution

Figure 11.26 displays the formula view of the Land Shark simulation model implementing equation (11.14) to generate bid fraction values.

Simulation Modeling for Land Shark Inc. (Slide 38 of 39)

Figure 11.27: Output from Land Shark Simulation Using Beta Distribution for Bid Fraction Values

In Figure 11.27, modeling bid fraction values with a beta distribution results in a 95% confidence interval of $26,199 to $33,961 on the mean return and a 95% confidence interval of 0.164 to 0.212 on the probability of winning the auction.

These results are less optimistic than the results from Figure 11.21 based on generating bid fraction values by directly sampling the 280 bid observations..

Simulation Modeling for Land Shark Inc. (Slide 39 of 39)

Generating Bid Amounts with Fitted Distributions (cont.):

While it is impossible to discern what is the “best” way to model the uncertain bid fraction values, testing different distributions generates insight.

One benefit of using a good-fitting theoretical distribution to generate bid fraction values is that it generates thousands of unique bid fractions.

The appropriate way to generate values for the random variables in a Monte Carlo simulation may be difficult to determine.

It is important to test the implications of different modeling approaches; a simulation model is not a crystal ball, but rather helps you understand the impact of uncertainty on your decisions.

Simulation with Dependent Random Variables

Spreadsheet Model for Press Teag Worldwide

Simulation with Dependent Random Variables (Slide 1 of 11)

Press Teag Worldwide (PTW) manufactures all of its products in the United States, but it sells the items in three different overseas markets: the United Kingdom, New Zealand, and Japan.

Each of these overseas markets generates revenue in a different currency: pound sterling in the United Kingdom, New Zealand dollars in New Zealand and yen in Japan.

At the end of each 13-week quarter, PTW converts the revenue from these three overseas markets back into U.S. dollars, exposing PTW to exchange rate risk.

Simulation with Dependent Random Variables (Slide 2 of 11)

Spreadsheet Model for Press Teag Worldwide:

To assess the degree of PTW’s exposure to quarterly fluctuations in exchange rates, develop a simulation model.

The first step is to identify the input parameters and output measures.

The next step is to develop a spreadsheet model that computes the values of the output measures given value of the input parameters.

Then prepare the spreadsheet model for simulation analysis by replacing the static values of the input parameters that are uncertain with probability distributions of possible values.

Simulation with Dependent Random Variables (Slide 3 of 11)

Spreadsheet Model for Press Teag Worldwide (cont.):

To model the fluctuation in the exchange rate between the pound sterling and the U.S. dollar over the next quarter, PTW expresses the number of pounds sterling (£) per U.S. dollar ($) by:

Simulation with Dependent Random Variables (Slide 4 of 11)

Spreadsheet Model for Press Teag Worldwide (cont.):

The equations computing the end-of-quarter exchange rates between New Zealand dollars (NZD) per U.S. dollar and Japanese yen (¥) per U.S. dollar are as follows:

Simulation with Dependent Random Variables (Slide 5 of 11)

Spreadsheet Model for Press Teag Worldwide (cont.):

Once the end-of-quarter exchange rates are known, the quarterly revenue in pounds sterling, New Zealand dollar, and Japanese yen can be converted into U.S. dollars as follows:

Simulation with Dependent Random Variables (Slide 6 of 11)

Figure 11.28: Base Spreadsheet Model for Press Teag Worldwide

The total revenue in U.S. dollars is $155,385 + $208,897 + $103,219 = $467,502.

Figure 11.28 shows the formula view and value view of the PTW spreadsheet model for the base scenario just presented.

103

Simulation with Dependent Random Variables (Slide 7 of 11)

Spreadsheet Model for Press Teag Worldwide (cont.):

The percent change in the exchange rate between pairs of currencies from the start to the end of a quarter is uncertain.

PTW realizes that there are dependencies between the exchange rate fluctuations.

The percent changes in the exchange rates should not be generated independently, but instead these values should be generated jointly.

Simulation with Dependent Random Variables (Slide 8 of 11)

Figure 11.29: Pairwise Relationships Between PTW Exchange Rate Data

To account for these dependencies, PTW constructed a data set on the joint percent changes between the three exchange rates for 2,000 quarter-scenarios in the Data worksheet of the file QuarterlyExchange.

These data are based on historical observations as well as scenarios based on expert judgment.

Figure 11.29 displays these data as three scatter plots showing the pairwise relationships between the exchange rates.

Figure 11.29 indicates that the percent changes in exchange rates are correlated.

105

Simulation with Dependent Random Variables (Slide 9 of 11)

Spreadsheet Model for Press Teag Worldwide (cont.):

To directly sample one of the 2,000 scenarios and obtain the corresponding percent change in £ per $ rate, NZD per $ rate, and ¥ per $ rate, we use the respective Excel formulas:

Simulation with Dependent Random Variables (Slide 10 of 11)

Figure 11.30: Formula Worksheet for PTW

As Figure 11.30 illustrates, in equations (11.20), (11.21), and (11.22), cell E7 contains the Excel function =RANDBETWEEN(1, 2000) which randomly generates the index of one of the 2,000 quarter scenarios.

107

Simulation with Dependent Random Variables (Slide 11 of 11)

Figure 11.31: Output from PTW Simulation

Figure 11.31 shows the results of 1,000 simulation trials. PTW can use this simulation model to assess its exposure to currency exchange rates and consider actions to hedge against this risk.

108

Simulation Considerations

Verification and Validation

Advantages and Disadvantages of Using Simulation

Simulation Considerations (Slide 1 of 6)

Verification and Validation:

Verification: The process of determining that the computer procedure that performs the simulation calculations is logically correct.

In some cases, an analyst may compare computer results for a limited number of events with independent hand calculations.

In other cases, tests may be performed to verify that the random variables are being generated correctly and that the output from the simulation model seems reasonable.

The verification step is not complete until the user develops a high degree of confidence that the computer procedure is error free.

Simulation Considerations (Slide 2 of 6)

Verification and Validation (cont.):

Validation: The process of ensuring that the simulation model provides an accurate representation of a real system.

Validation requires an agreement among analysts and managers that the logic and the assumptions used in the design of the simulation model accurately reflect how the real system operates.

The first phase of the validation process is done prior to or in conjunction with the development of the computer procedure for the simulation process.

Simulation Considerations (Slide 3 of 6)

Verification and Validation (cont.):

Validation continues with the analyst reviewing the simulation output to see whether the simulation results closely approximate the performance of the real system.

An analyst can also have one or more individuals experienced with the operation of the real system review the simulation output to determine whether it is a reasonable approximation of what would be obtained with the real system under similar conditions.

Verification and validation are key steps in any simulation study and are necessary to ensure that decisions and conclusions based on the simulation results are appropriate for the real system.

Simulation Considerations (Slide 4 of 6)

Advantages and Disadvantages of Using Simulation:

The primary advantages of simulation are that it is easy to understand and that the methodology can be used to model and learn about the behavior of complex systems.

Simulation models are flexible; they can be used to describe systems without requiring the assumptions that are often required by mathematical models.

Changing assumptions or operating policies in the simulation model and rerunning it can provide results that help predict how such changes will affect the operation of the real system.

Simulation Considerations (Slide 5 of 6)

Advantages and Disadvantages of Using Simulation (cont.):

For complex systems, the process of developing, verifying, and validating a simulation model can be time-consuming and expensive.

As with all mathematical models, the analyst must be conscious of the assumptions of the model in order to understand its limitations.

The summary of the simulation data provides only estimates or approximations about the real system.

Simulation Considerations (Slide 6 of 6)

Advantages and Disadvantages of Using Simulation (cont.):

The danger of obtaining poor solutions is greatly mitigated if the analyst exercises good judgment in developing the simulation model and follows proper verification and validation steps.

If a sufficiently large enough set of simulation trials is run under a wide variety of conditions, the analyst will likely have sufficient data to predict how the real system will operate.

End of Chapter 11

()

Direct labor cost per unit ()

Parts cost per unit ()

First-year administrative and advertisin

g costs

(

)

(

)

Profit = 249459015,0001,000,000710,000.

---=

(

)

(

)

Profit = 2494710001,000,0001,000,000.

---=-

(

)

(

)

Profit = 249438030,0001,000,0002,780,000

---=

Since RAND is between 0 and 1, the secon

d term,

0RAND()

80200.4576

809.15

89.15 per unit

=+´

Parts cost80200.58428011.6891.68 per uni

=+´=+=

(

)

(

)

Cell H26 = COUNT(E:26:E1025)

Cell H27 = MIN(E26:E1025)

Cell H28 = MAX(E26:E1025)

Cell H29 = AVERAGE(E26:E1025)

Cell H30 = STDEV.S(E26:E1025)

Cell H32 = COUNTIFE26:E1025,"<0"COUNTE26

:E1025

Cell H33 = SQRTH32

(

)

(

)

1H32H36

Consider the formula in cell C8, =IF(A8>

$B$5,0,B8$B$4).

the auction by returning the value of 1,

and otherwise returning the

value of 0 if Land Shark loses the aucti

on.

The logic =IF(B18>B19,1,0) in cell B20 i

ndicates that Land Shark wins

The formula in cell B21, =B20(B4B18), co

mputes the return from

the auction.

=VLOOKUP(RANDBETWEEN(1, 280), BidList!$A

$2:$B$281, 2, FALSE)

input parameters: alpha (), beta (), min

imum (), and maximum ().

A common method for estimating the value

s in a beta

distribution uses the sample mean () and

sample standard deviation ():

and