computer model

profileChrisyanqu
OM406_RA_6.Simulation_Using_Crystall_Ball.ppt
Home>Business & Finance homework help>computer model

Simulation Using Crystal Ball

Freddie the Newsboy

  • Freddie runs a newsstand in a prominent downtown location of a major city.
  • Freddie sells a variety of newspapers and magazines. The most expensive of the newspapers is the Financial Journal.
  • Cost data for the Financial Journal:
  • Freddie pays $1.50 per copy delivered.
  • Freddie charges $2.50 per copy.
  • Freddie’s refund is $0.50 per unsold copy.
  • Sales data for the Financial Journal:
  • Freddie sells anywhere between 40 and 70 copies a day.
  • The frequency of the numbers between 40 and 70 are roughly equal.

Spreadsheet Model for Applying Simulation

Figure 16.1 A spreadsheet model for applying computer simulation to the case study that involves Freddie the newsboy. The assumption cell is Simulated Demand (C12), the forecast cell is Profit (C19), and the decision variable is Order Quantity (C9).

Freddie

Freddie the Newsboy
Data Range Name Cell
Unit Sale Price $2.50 Demand C13
Unit Purchase Cost $1.50 OrderQuantity C9
Unit Salvage Value $0.50 Profit C19
PurchasingCost C16
Decision Variable SalesRevenue C15
Order Quantity 60 SalvageValue C17
SimulatedDemand C12
Simulation Minimum Maximum UnitPurchaseCost C5
Simulated Demand 55 Uniform 40 70 UnitSalePrice C4
Demand (rounded) 55 UnitSalvageValue C6
Sales Revenue $137.50
Purchasing Cost $90.00
Salvage Value $2.50
Profit $50.00

Demand Data

Freddie the Newsboy Historical Demand Data
Historical
Demand
Day Data
1 62
2 45
3 59
4 65
5 50
6 64
7 56
8 51
9 55
10 61
11 40
12 47
13 63
14 68
15 67
16 67
17 68
18 54
19 55
20 48
21 41
22 49
23 55
24 45
25 42
26 66
27 65
28 53
29 65
30 53
31 52
32 46
33 43
34 48
35 58
36 57
37 43
38 54
39 58
40 70
41 43
42 60
43 42
44 68
45 62
46 55
47 66
48 50
49 47
50 63
51 40
52 47
53 48
54 49
55 41
56 42
57 64
58 45
59 59
60 70

Sheet1

Application of Crystal Ball

  • Four steps must be taken to use Crystal Ball on a spreadsheet model:

Define the random input cells.

Define the output cells to forecast.

Set the run preferences.

Run the simulation.

Step 1: Define the Random Input Cells

  • A random input cell is an input cell that has a random value.
  • An assumed probability distribution must be entered into the cell rather than a single number.
  • Crystal Ball refers to each such random input cell as an assumption cell.
  • Procedure to define an assumption cell:

Select the cell by clicking on it.

If the cell does not already contain a value, enter any number into the cell.

Click on the Define Assumption button (first button in Crystal Ball toolbar).

Select a probability distribution from the Distribution Gallery.

Click OK to bring up the dialogue box for the selected distribution.

Use the dialogue box to enter parameters for the distribution (preferably referring to cells on the spreadsheet that contain these parameters).

Click on OK.

The Crystal Ball Toolbar

Figure 16.2 The Crystal Ball toolbar.

Crystal Ball Distribution Gallery

Figure 16.3 The Crystal Ball Distribution Gallery dialogue box. In addition to the 12 distributions displayed here, five more distributions can be accessed by clicking on the More button.

Crystal Ball Uniform Distribution Dialogue Box

Figure 16.4 The Crystal Ball Uniform Distribution dialogue box. It is being used here to enter a uniform distribution with the parameters in cells E12 and F12 into the assumption cell Simulated Demand (C12) in the spreadsheet model in Figure 16.1.

Step 2: Define the Output Cells to Forecast

  • Crystal Ball refers to the output of a computer simulation as a forecast, since it is forecasting the underlying probability distribution when it is in operation.
  • Each output cell that is being used to forecast a measure of performance is referred to as a forecast cell.
  • Procedure for defining a forecast cell:

Select the cell.

Click on the Define Forecast button (3rd button) in the Crystal Ball toolbar, which brings up the Define Forecast dialogue box.

This dialogue box can be used to define a name and (optionally) units for the forecast cell.

Click on OK.

Crystal Ball Define Forecast Dialogue Box

Figure 16.5 The Crystal Ball Define Forecast dialogue box. It is being used here to define the forecast cell Profit (C19) in the spreadsheet model in Figure 16.1.

Step 3: Set the Run Preferences

  • Setting run preferences refers to such things as choosing the number of trials to run and deciding on other options regarding how to perform the simulation.
  • This step begins by clicking on the Run Preferences button on the Crystal Ball toolbar.
  • The Run Preferences dialogue box has six tabs to set various types of options.
  • The Trials tab allows you to specify the maximum number of trials to run for the computer simulation.

The Crystal Ball Run Preferences Dialogue Box

Figure 16.6 The Crystal Ball Run Preferences dialogue box after selecting the Trials tab.

Recommended Crystal Ball Run Preferences

Figure 16.53 This dialogue box shows the run preferences that are recommended for most applications of OptQuest. For optimizing a mean statistic, 500 trials are usually sufficient. For optimizing an extreme value statistic (e.g., the maximum value), more trials are needed (e.g., 1000).

Recommended Crystal Ball Run Preferences

Figure 16.53 This dialogue box shows the run preferences that are recommended for most applications of OptQuest. For optimizing a mean statistic, 500 trials are usually sufficient. For optimizing an extreme value statistic (e.g., the maximum value), more trials are needed (e.g., 1000).

Recommended Crystal Ball Run Preferences

Figure 16.53 This dialogue box shows the run preferences that are recommended for most applications of OptQuest. For optimizing a mean statistic, 500 trials are usually sufficient. For optimizing an extreme value statistic (e.g., the maximum value), more trials are needed (e.g., 1000).

Recommended Crystal Ball Run Preferences

Figure 16.53 This dialogue box shows the run preferences that are recommended for most applications of OptQuest. For optimizing a mean statistic, 500 trials are usually sufficient. For optimizing an extreme value statistic (e.g., the maximum value), more trials are needed (e.g., 1000).

Recommended Crystal Ball Run Preferences

Figure 16.53 This dialogue box shows the run preferences that are recommended for most applications of OptQuest. For optimizing a mean statistic, 500 trials are usually sufficient. For optimizing an extreme value statistic (e.g., the maximum value), more trials are needed (e.g., 1000).

Step #4: Run the Simulation

  • To begin running the simulation, click on the Start Simulation button.
  • Once started, a forecast window displays the results of the computer simulation as it runs.
  • The following can be obtained by choosing the corresponding option under the View menu in the forecast window display:
  • Frequency chart
  • Statistics table
  • Percentiles table
  • Cumulative chart
  • Reverse cumulative chart

The Statistics Table for Freddie’s Profit

Figure 16.7 The statistics table provided by Crystal Ball to summarize the results of running the simulation model in Figure 16.1 for the case study that involves Freddie the newsboy.

The Frequency Chart for Freddie’s Profit

Figure 16.7 The frequency chart provided by Crystal Ball to summarize the results of running the simulation model in Figure 16.1 for the case study that involves Freddie the newsboy.

Certainty that Profit ≥ $40

Figure 16.9 After setting a lower bound of $40 for desirable profit values, the Certainty box below this frequency chart reveals that 68.4% of the trials in Freddie’s simulation run provided a profit at least this high.

The Percentiles Table for Freddie’s Profit

Figure 16.8 The percentiles table provided by Crystal Ball to summarize the results of running the simulation model in Figure 16.1 for the case study that involves Freddie the newsboy.

The Cumulative Chart for Freddie’s Profit

Figure 16.8 The cumulative chart provided by Crystal Ball to summarize the results of running the simulation model in Figure 16.1 for the case study that involves Freddie the newsboy.

The Reverse Cumulative Chart for Freddie’s Profit

Figure 16.8 The reverse cumulative chart provided by Crystal Ball to summarize the results of running the simulation model in Figure 16.1 for the case study that involves Freddie the newsboy.

How Accurate Are the Simulation Results?

  • Precision Control: Expanded Define Forecast Dialogue Box

How Accurate Are the Simulation Results?

  • An important number provided by the simulation is the mean profit of $47.23.
  • This sample average provides an estimate of the true mean of the distribution. The true mean might be somewhat different than $47.23.

How Accurate Are the Simulation Results?

  • The mean standard error (on the Statistics Chart) of $0.29 gives some indication of how accurate the estimate might be.
  • The true mean will typically be within the mean one standard error of the estimated value approximately 68% of the time. It is about 68% likely that the true mean profit is between $46.94 and $47.52.
  • The true mean will typically be within the mean two standard error of the estimated value approximately 95% of the time. It is about 95% likely that the true mean profit is between $46.66 and $47.24.

How Accurate Are the Simulation Results?

  • The mean standard error can be reduced by increasing the number of trials. However, cutting the mean standard error in half typically requires more trials.

1000 trials provided us with a 95% confidence interval around the mean within $0.59.

Results with Precision Control

10000 trials provided us with a 95% confidence interval around the mean within $0.18.

Figure 16.11 The results obtained after continuing Freddie’s simulation run until the precision specified in Figure 16.10 is achieved.

Results with Precision Control

100000 trials provided us with a 95% confidence interval around the mean within $0.06.

Figure 16.11 The results obtained after continuing Freddie’s simulation run until the precision specified in Figure 16.10 is achieved.

Results with Precision Control

130750 trials were required to get a 95% confidence interval around the mean within $0.05.

Figure 16.11 The results obtained after continuing Freddie’s simulation run until the precision specified in Figure 16.10 is achieved.

Bidding for a Project: Reliable Construction Co.

  • Reliable Construction Co. is bidding to construct a new plant for a major manufacturer.
  • Reliable estimates the cost of the project to be $4.55 million, There also is an additional cost of approximately $50,000 for preparing the bid.
  • Three other construction companies also were invited to submit bids for the project.
  • Competitor 1 is known to use a 30 percent profit margin but are unpredictable bidders because of an inability to accurately estimate the true cost of the project. Previous bids have ranged from 5% below the expected cost to 60% above.
  • Competitor 2 uses a 25% profit margin but is more accurate at predicting the true cost. In the past, they have missed this profit margin by up to 15% in either direction.
  • Competitor 3 is unusually accurate in estimating project cost. It is equally likely to set its profit margin anywhere between 20% and 30%.

Question: How much should Reliable bid for this project?

Spreadsheet Model for Applying Computer Simulation

Figure 16.12 A spreadsheet model for applying computer simulation to the Reliable Construction Co.’s contract bidding problem. The assumption cells are Competitor Bids (C8:E8), the forecast cell is Profit (C29), and the decision variable is Our Bid (C25).

Bidding

Reliable Construction Co. Contract Bidding
Data Range Name Cells
Our Project Cost ($million) 4.550 CompetitorBids C8:E8
Our Bid Cost ($million) 0.050 MinimumCompetitorBid C23
OurBid C25
Competitor Bids Competitor 1 Competitor 2 Competitor 3 OurBidCost C5
Bid ($million) 5.839 5.688 5.688 OurProjectCost C4
Profit C29
Distribution Triangular Triangular Uniform WinBid? C27
Competitor Distribution Parameters (Proportion of Our Project Cost)
Minimum 95% 110% 120%
Most Likely 130% 125%
Maximum 160% 140% 130%
Competitor Distribution Parameters ($millions)
Minimum 4.323 5.005 5.460
Most Likely 5.915 5.688
Maximum 7.280 6.370 5.915
Minimum Competitor
Bid ($million) 5.688
Our Bid ($million) 5.400
Win Bid? 1 (1=yes, 0=no)
Profit ($million) 0.800

Sheet1

Triangular Distribution for Competitor 1

Figure 16.13 The Triangular Distribution dialogue box. It is being used here to enter a triangular distribution with the parameters in cells C18:C20 into the assumption cell C8 in the spreadsheet model in Figure 16.12.

Triangular Distribution for Competitor 2

Figure 16.13 The Triangular Distribution dialogue box. It is being used here to enter a triangular distribution with the parameters in cells C18:C20 into the assumption cell C8 in the spreadsheet model in Figure 16.12.

Uniform Distribution for Competitor 3

Figure 16.13 The Triangular Distribution dialogue box. It is being used here to enter a triangular distribution with the parameters in cells C18:C20 into the assumption cell C8 in the spreadsheet model in Figure 16.12.

Statistics Table for Reliable’s Bidding Problem

Figure 16.14 The statistics table that summarizes the results of running the simulation model in Figure 16.12 for the Reliable Construction Co. contract bidding problem.

Frequency Chart for Reliable’s Bidding Problem

Figure 16.14 The frequency chart that summarizes the results of running the simulation model in Figure 16.12 for the Reliable Construction Co. contract bidding problem.

Transcontinental Airlines Overbooking Problem

  • Transcontinental has a daily flight (excluding weekends) from San Francisco to Chicago that is mainly used by business travelers.
  • There are 150 seats available in a single cabin.
  • The average fare per seat is $300. This is a nonrefundable fare, so no-shows forfeit the entire fare.
  • The fixed cost of operating the flight is $30,000.
  • The average number of reservation requests for this flight has been 195, with a standard deviation of 30.
  • Only 80% of passengers with a reservation actually show up to take the flight, so it makes sense to take more than 150 reservations (overbooking).
  • If more passengers arrive to take the flight than there are seats, some passengers must be “bumped”. The total cost (including rebooking, travel vouchers, and lost goodwill) is estimated to be $450.

Question: How many reservations should Transcontinental accept for this flight?

Spreadsheet Model for Applying Computer Simulation

Figure 16.30 A spreadsheet model for applying computer simulation to the Transcontinental Airlines overbooking problem. The assumption cells are Simulated Ticket Demand (C10) and Number that Show (C17). The forecast cells are Profit (F23), Number of Filled Seats (C20), and Number Denied Boarding (C21). The decision variable is Reservations to Accept (C13).

Airline Overbooking

Transcontinental Airlines Overbooking
Data Range Name Cell
Available Seats 150 AvailableSeats C4
Fixed Cost $30,000 AverageFare C6
Avg. Fare / Seat $300 BumpingCost F21
Cost of Bumping $450 CostOfBumping C7
Demand C11
Mean Standard Dev. FixedCost C5
Ticket Demand 195 Normal 195 30 NumberDeniedBoarding C21
Demand (rounded) 195 NumberOfFilledSeats C20
NumberThatShow C17
Reservations to Accept 190 Profit F23
ReservationsToAccept C13
Tickets Probability SimulatedTicketDemand C10
Purchased to Show up TicketRevenue F20
Number that Show 152 Binomial 190 80% TicketsPurchased E17
Number of Filled Seats 150 Ticket Revenue $45,000
Number Denied Boarding 2 Bumping Cost $900
Fixed Cost $30,000
Profit $14,100

Sheet1

Normal Distribution for Ticket Demand

Figure 16.31 A binomial distribution with parameters F17 and E17 is being entered into the assumption cell Number that Show (C17). The “Dynamic” option has been chosen because the value in cell E17 may change with each trial of the simulation.

Binomial Distribution with Dynamic Option for
Number that Show

Figure 16.31 A binomial distribution with parameters F17 and E17 is being entered into the assumption cell Number that Show (C17). The “Dynamic” option has been chosen because the value in cell E17 may change with each trial of the simulation.

Statistics Table for Profit

Figure 16.32 The frequency chart summarizes the results for the forecast cell Profit (F23) from running the simulation model in Figure 16.30 for the Transcontinental Airlines overbooking problem. The certainty box below the chart reveals that 94.4% of the trials result in a positive profit.

Statistics Table for Number of Filled Seats

Figure 16.32 The frequency chart summarizes the results for the forecast cell Number of Filled Seats (C20) from running the simulation model in Figure 16.30 for the Transcontinental Airlines overbooking problem.

Statistics Table for Number Denied Boarding

Figure 16.32 The frequency chart summarizes the results for the forecast cell Number Denied Boarding (C21) from running the simulation model in Figure 16.30 for the Transcontinental Airlines overbooking problem.

Choosing the Right Distribution

  • A continuous distribution is used if any values are possible, including both integer and fractional numbers, over the entire range of possible values.
  • A discrete distribution is used if only certain specific values (e.g., only some integer values) are possible.
  • However, if the only possible values are integer numbers over a relatively broad range, a continuous distribution may be used as an approximation by rounding any fractional value to the nearest integer.

A Popular Central-Tendency Distribution: Normal

  • Some value most likely (the mean)
  • Values close to mean more likely
  • Symmetric (as likely above as below mean)
  • Extreme values possible, but rare

Figure 16.33 The characteristics and dialogue box for a popular central-tendency distribution: the normal distribution.

A Popular Central-Tendency Distribution: Triangular

  • Some value most likely
  • Values close to most likely value more common
  • Can be asymmetric
  • Fixed upper and lower bound

Figure 16.33 The characteristics and dialogue box for a popular central-tendency distribution: the triangular distribution.

A Popular Central-Tendency Distribution: Lognormal

  • Some value most likely
  • Positively skewed (below mean more likely)
  • Values cannot fall below zero
  • Extreme values (high end only) possible, but rare

Figure 16.33 The characteristics and dialogue box for a popular central-tendency distribution: the lognormal distribution.

The Uniform Distribution

  • Fixed minimum and maximum value
  • All values equally likely

Figure 16.34 The characteristics and dialogue box for the uniform distribution in Crystal Ball’s Distribution Gallery.

A Three-Parameter Distribution: Weibull

  • Random value above some number (location)
  • Shape > 0 (usually ≤ 10)
  • Shape < 3 becomes more positively-skewed (below mean more likely) until it resembles exponential distribution (equivalent at Shape = 1)
  • Symmetrical at Shape = 3.25, becomes negatively skewed above that
  • Scale defines width

Figure 16.35 The characteristics and dialogue box for a three-parameter distribution: the Weibull distribution.

A Three-Parameter Distribution: Beta

  • Random value between 0 and some positive number (Scale)
  • Shape specified using two positive values (alpha, beta)
  • Alpha < beta: positively skewed (below mean more likely)
  • Beta < alpha: negatively skewed

Figure 16.35 The characteristics and dialogue box for a three-parameter distribution: the beta distribution.

A Distribution for Random Events: Exponential

  • Widely used to describe time between random events (e.g., time between arrivals)
  • Events are independent
  • Rate = average number of events per unit time (e.g., arrivals per hour)

Figure 16.36 The characteristics and dialogue box for a distribution that involves random events: the exponential distribution.

A Distribution for Random Events: Poisson

  • Describes the number of times an event occurs during a given period of time or space
  • Occurrences are independent
  • Any number of events is possible
  • Rate = average number of events per unit of time, assumed constant over time

Figure 16.36 The characteristics and dialogue box for a distribution that involves random events: the Poisson distribution.

Distribution for Number of Times an Event Occurs: Binomial

  • Describes number of times an event occurs in a fixed number of trials (e.g., number of heads in 10 flips of a coin)
  • For each trial, only two outcomes are possible
  • Trials independent
  • Probability remains the same for each trial

Figure 16.37 The characteristics and dialogue box for the binomial distribution in Crystal Ball’s Distribution Gallery.

Distribution for Number of Trials Until Event Occurs: Geometric

  • Describes number of trials until an event occurs (e.g., number of times to spin roulette wheel until you win)
  • Probability same for each trial
  • Continue until succeed
  • Number of trials unlimited

Figure 16.38 The characteristics and dialogue box for a distribution that involves the number of trials until an event occurs: the geometric distribution.

Distribution for Number of Trials Until n Events Occur:
Negative Binomial

  • Describes number of trials until an event occurs n times
  • Same as geometric when Shape = n = 1
  • Probability same for each trial
  • Continue until nth success
  • Number of trials unlimited

Figure 16.38 The characteristics and dialogue box for a distribution that involves the number of trials until an event occurs n times: the negative binomial distribution.

The Custom Distribution (Set of Discrete Values)

  • Enter set of values with varying probabilities
  • For each discrete value, enter “Value” and “Prob.” (leave other boxes blank)
  • Clicking Enter clears boxes for entering next discrete value

Figure 16.39 Following the instructions, this dialogue box illustrates how Crystal Ball’s custom distribution can enable you to custom design your own distribution to enter a set of discrete values and their probabilities.

Historical Demand Data for the Financial Times

Figure 16.41 Cells F4:F63 contain the historical demand data that have been collected for the case study involving Freddie the newsboy that was introduced in Section 16.1. Columns B and C come from the simulation model for this case study in Figure 16.1.

Freddie

Freddie the Newsboy Historical
Demand
Data Day Data
Unit Sale Price $2.50 1 62
Unit Purchase Cost $1.50 2 45
Unit Salvage Value $0.50 3 59
4 65
Decision Variable 5 50
Order Quantity 60 6 64
7 56
Simulation 8 51
Simulated Demand 55 9 55
Demand (rounded) 55 10 61
11 40
Sales Revenue $137.50 12 47
Purchasing Cost $90.00 13 63
Salvage Value $2.50 14 68
15 67
Profit $50.00 16 67
17 68
18 54
19 55
20 48
21 41
22 49
23 55
24 45
25 42
26 66
27 65
28 53
29 65
30 53
31 52
32 46
33 43
34 48
35 58
36 57
37 43
38 54
39 58
40 70
41 43
42 60
43 42
44 68
45 62
46 55
47 66
48 50
49 47
50 63
51 40
52 47
53 48
54 49
55 41
56 42
57 64
58 45
59 59
60 70

Demand Data

Freddie the Newsboy Historical Demand Data
Historical
Demand
Day Data
1 62
2 45
3 59
4 65
5 50
6 64
7 56
8 51
9 55
10 61
11 40
12 47
13 63
14 68
15 67
16 67
17 68
18 54
19 55
20 48
21 41
22 49
23 55
24 45
25 42
26 66
27 65
28 53
29 65
30 53
31 52
32 46
33 43
34 48
35 58
36 57
37 43
38 54
39 58
40 70
41 43
42 60
43 42
44 68
45 62
46 55
47 66
48 50
49 47
50 63
51 40
52 47
53 48
54 49
55 41
56 42
57 64
58 45
59 59
60 70

Sheet1

Procedure for Fitting the Best Distribution to Data

Gather the data needed to identify the best distribution to enter into an assumption cell.

Enter the data into the spreadsheet containing your simulation model.

Select the cell that you want to define as an assumption cell that contains the distribution that best fits the data.

Choose Define Assumption from the Crystal Ball toolbar, which brings up the Distribution Gallery dialogue box.

Procedure for Fitting the Best Distribution to Data

Click the Fit button on the dialogue box, which brings up the Fit Distribution dialogue box.

Procedure for Fitting the Best Distribution to Data

Use the Range box in this dialogue box to enter the range of the historical data in your worksheet.

Use this dialogue box to specify which continuous distributions are being considered for fitting.

Also use this dialogue box to select which ranking method should be used to evaluate how well a distribution fits the data. (Choose chi-square test.)

Procedure for Fitting the Best Distribution to Data

Click OK, which brings up the comparison chart that identifies the distribution (including its parameter values) that best fits the data.

If desired, the Next Distribution button can be clicked repeatedly for identifying the other types of distributions that are next in line for fitting the data well.

After choosing the distribution that you want to use, click the Accept button while that distribution is showing.

Procedure for Fitting the Best Distribution to Data

This will enter the appropriate parameters into the dialogue box for this distribution. Clicking OK then enters this distribution into the assumption cell.

Decision Making with Decision Tables

  • Many simulation models include at least one decision variable
  • Examples: Order quantity, Bid, Number of reservations to accept
  • Crystal Ball can be used to evaluate a particular value of the decision variable by providing a wealth of output for the forecast cells.
  • However, this approach does not identify an optimal solution for the decision variable(s).
  • Trial and error can be used to try different values of the decision variable(s).
  • Run a simulation for each and see which one provides the best estimate of the chosen measure of performance.
  • The Decision Table tool in Crystal Ball does this approach in a systematic way.

Procedure for Defining a Decision Variable

Select the cell containing the decision variable.

If the cell does not already contain a value, enter any number into the cell.

Click on the Define Decision button in the Crystal Ball toolbar, which brings up the Define Decision Variable dialogue box.

Procedure for Defining a Decision Variable

Enter the lower and upper limit of the range of values to be simulated for the decision variable.

Click on either Continuous or Discrete to define the type of variable.

If Discrete is selected in Step 5, use the Step box to specify the difference between the successive possible values (not just those to be simulated).

Click on OK.

Decision Table:

Step 1: From the More Tools menu select Decision Table

Figure 16.45 To prepare for generating a Decision Table, this dialogue box is used to specify which forecast cell will be the target cell. The choice made here is for the case study that involves Freddie the newsboy.

Decision Table: Specify Target Forecast

Step 2: Select the target cell from the list of forecasts

Figure 16.45 To prepare for generating a Decision Table, this dialogue box is used to specify which forecast cell will be the target cell. The choice made here is for the case study that involves Freddie the newsboy.

Decision Table: Specify Decision Variable(s) to Vary

Step 3: Select decision variable(s) to evaluate

Figure 16.45 To prepare for generating a Decision Table, this dialogue box is used to specify which one or two decision variables will be varied. The choice made here is for the case study that involves Freddie the newsboy.

Decision Table: Specify Options

Step 4: Specify options

Figure 16.45 To prepare for generating a Decision Table, this dialogue box is used to specify running options. The choices made here are for the case study that involves Freddie the newsboy.

The Decision Table for Freddie’s Order Quantity

Figure 16.46 The Decision Table for the case study introduced in Section 16.1.

Decision Variable for Reliable’s Bidding Problem

Figure 16.49 This dialogue box specifies the characteristics of the decision variable Our Bid (C25) in Figure 16.12 for the Reliable Construction Co. contract bidding problem.

Bidding

Reliable Construction Co. Contract Bidding
Data Range Name Cells
Our Project Cost ($million) 4.550 CompetitorBids C8:E8
Our Bid Cost ($million) 0.050 MinimumCompetitorBid C23
OurBid C25
Competitor Bids Competitor 1 Competitor 2 Competitor 3 OurBidCost C5
Bid ($million) 5.839 5.688 5.688 OurProjectCost C4
Profit C29
Distribution Triangular Triangular Uniform WinBid? C27
Competitor Distribution Parameters (Proportion of Our Project Cost)
Minimum 95% 110% 120%
Most Likely 130% 125%
Maximum 160% 140% 130%
Competitor Distribution Parameters ($millions)
Minimum 4.323 5.005 5.460
Most Likely 5.915 5.688
Maximum 7.280 6.370 5.915
Minimum Competitor
Bid ($million) 5.688
Our Bid ($million) 5.400
Win Bid? 1 (1=yes, 0=no)
Profit ($million) 0.800

Sheet1

Decision Variable for Reliable’s Bidding Problem

Figure 16.49 This dialogue box specifies the characteristics of the decision variable Our Bid (C25) in Figure 16.12 for the Reliable Construction Co. contract bidding problem.

Decision Table: Specify Target Cell

Figure 16.50 The first Decision Table dialogue box for the Reliable Construction Co. contract bidding problem.

Decision Table: Specify Decision Variable

Figure 16.50 The second Decision Table dialogue box for the Reliable Construction Co. contract bidding problem.

Decision Table: Specify Options

Figure 16.50 The third Decision Table dialogue box for the Reliable Construction Co. contract bidding problem.

Decision Table for Reliable’s Bid

Figure 16.51 The Decision Table for the Reliable Construction Co. contract bidding problem described in Section 16.2.

Decision Table for Airline Overbooking Problem

Figure 16.49 This dialogue box specifies the characteristics of the decision variable Our Bid (C25) in Figure 16.12 for the Reliable Construction Co. contract bidding problem.

Airline Overbooking

Transcontinental Airlines Overbooking
Data Range Name Cell
Available Seats 150 AvailableSeats C4
Fixed Cost $30,000 AverageFare C6
Avg. Fare / Seat $300 BumpingCost F21
Cost of Bumping $450 CostOfBumping C7
Demand C11
Mean Standard Dev. FixedCost C5
Ticket Demand 195 Normal 195 30 NumberDeniedBoarding C21
Demand (rounded) 195 NumberOfFilledSeats C20
NumberThatShow C17
Reservations to Accept 190 Profit F23
ReservationsToAccept C13
Tickets Probability SimulatedTicketDemand C10
Purchased to Show up TicketRevenue F20
Number that Show 152 Binomial 190 80% TicketsPurchased E17
Number of Filled Seats 150 Ticket Revenue $45,000
Number Denied Boarding 2 Bumping Cost $900
Fixed Cost $30,000
Profit $14,100

Sheet1

Decision Variable for Airline Overbooking Problem

Figure 16.49 This dialogue box specifies the characteristics of the decision variable Our Bid (C25) in Figure 16.12 for the Reliable Construction Co. contract bidding problem.

Decision Table: Specify Target Cell

Figure 16.50 The first Decision Table dialogue box for the Reliable Construction Co. contract bidding problem.

Decision Table: Specify Decision Variable

Figure 16.50 The second Decision Table dialogue box for the Reliable Construction Co. contract bidding problem.

Decision Table: Specify Options

Figure 16.50 The third Decision Table dialogue box for the Reliable Construction Co. contract bidding problem.

Decision Table for Transcontinental’s Reservations to Accept

Figure 16.52 The Decision Table for the Transcontinental Airlines overbooking problem described in Section 16.6.

Optimizing with OptQuest

  • Crystal Ball includes a module called OptQuest that automatically searches for an optimal solution for a simulation model with any number of decision variables.
  • The search is conducted by executing a series of simulation runs of leading candidates to be the actual optimal solution.
  • The results of each run are used to determine the most promising remaining candidate to try next.
  • A powerful search engine (based on genetic algorithms) conducts an intelligent and efficient search.

Procedure for Applying OptQuest

Formulate your simulation model on a spreadsheet.

Use Crystal Ball to complete your formulation by defining your assumption cells, forecast cells, and decision variables, as well as setting your run preferences.

Choose OptQuest from the Crystal Ball Tools menu.

Use the Objectives Selection dialogue box to select your objective.

Use the Decision Variable Selection dialogue box to select your decision variables.

Use the Constraints dialogue box to specify your constraints (if any).

Use the Forecast Selection dialogue box to specify the running time.

Use the Options dialogue box to specify the options.

Select Start from the Run menu to run the optimization.

OptQuest for Freddie’s Problem:

Figure 16.54 These OptQuest dialogue boxes are used to (1) select the decision variables to vary and set their bounds, and (2) specify any constraints. The choices are made for the case study that involves Freddie the newsboy.

Freddie

Freddie the Newsboy
Data Range Name Cell
Unit Sale Price $2.50 Demand C13
Unit Purchase Cost $1.50 OrderQuantity C9
Unit Salvage Value $0.50 Profit C19
PurchasingCost C16
Decision Variable SalesRevenue C15
Order Quantity 60 SalvageValue C17
SimulatedDemand C12
Simulation Minimum Maximum UnitPurchaseCost C5
Simulated Demand 55 Uniform 40 70 UnitSalePrice C4
Demand (rounded) 55 UnitSalvageValue C6
Sales Revenue $137.50
Purchasing Cost $90.00
Salvage Value $2.50
Profit $50.00

Demand Data

Freddie the Newsboy Historical Demand Data
Historical
Demand
Day Data
1 62
2 45
3 59
4 65
5 50
6 64
7 56
8 51
9 55
10 61
11 40
12 47
13 63
14 68
15 67
16 67
17 68
18 54
19 55
20 48
21 41
22 49
23 55
24 45
25 42
26 66
27 65
28 53
29 65
30 53
31 52
32 46
33 43
34 48
35 58
36 57
37 43
38 54
39 58
40 70
41 43
42 60
43 42
44 68
45 62
46 55
47 66
48 50
49 47
50 63
51 40
52 47
53 48
54 49
55 41
56 42
57 64
58 45
59 59
60 70

Sheet1

OptQuest for Freddie’s Problem:
Specifying Objective

Figure 16.54 These OptQuest dialogue boxes are used to (3) specify the objective for the optimization, and (4) control the running time. The choices are made for the case study that involves Freddie the newsboy.

OptQuest for Freddie’s Problem:
Selecting Variables

Figure 16.54 These OptQuest dialogue boxes are used to (1) select the decision variables to vary and set their bounds, and (2) specify any constraints. The choices are made for the case study that involves Freddie the newsboy.

OptQuest for Freddie’s Problem:
Selecting Constraints

No constraints for this problem

Figure 16.54 These OptQuest dialogue boxes are used to (1) select the decision variables to vary and set their bounds, and (2) specify any constraints. The choices are made for the case study that involves Freddie the newsboy.

OptQuest for Freddie’s Problem:
Specifying Options

Figure 16.54 These OptQuest dialogue boxes are used to (3) specify the objective for the optimization, and (4) control the running time. The choices are made for the case study that involves Freddie the newsboy.

OptQuest Results for Freddie’s Problem

Figure 16.55 The optimization results provided by OptQuest for the case study introduced in Section 16.1. The best solution found for Freddie the newsboy is to use an order quantity of 55.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

A

B

C

D

E

F

Freddie the Newsboy

Data

Unit Sale Price

$2.50

Unit Purchase Cost

$1.50

Unit Salvage Value

$0.50

Decision Variable

Order Quantity

60

Simulation

Minimum

Maximum

Simulated Demand

55

Uniform

40

70

Demand (rounded)

55

Sales Revenue

$137.50

Purchasing Cost

$90.00

Salvage Value

$2.50

Profit

$50.00

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

A

B

C

D

E

Reliable Construction Co. Contract Bidding

Data

Our Project Cost ($million)

4.550

Our Bid Cost ($million)

0.050

Competitor Bids

Competitor 1

Competitor 2

Competitor 3

Bid ($million)

5.839

5.688

5.688

Distribution

Triangular

Triangular

Uniform

Competitor Distribution Parameters (Proportion of Our Project Cost)

Minimum

95%

110%

120%

Most Likely

130%

125%

Maximum

160%

140%

130%

Competitor Distribution Parameters ($millions)

Minimum

4.323

5.005

5.460

Most Likely

5.915

5.688

Maximum

7.280

6.370

5.915

Minimum Competitor

Bid ($million)

5.688

Our Bid ($million)

5.400

Win Bid?

1

(1=yes, 0=no)

Profit ($million)

0.800

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

A

B

C

D

E

F

Transcontinental Airlines Overbooking

Data

Available Seats

150

Fixed Cost

$30,000

Avg. Fare / Seat

$300

Cost of Bumping

$450

Mean

Standard Dev.

Ticket Demand

195

Normal

195

30

Demand (rounded)

195

Reservations to Accept

190

Tickets

Probability

Purchased

to Show up

Number that Show

152

Binomial

190

80%

Number of Filled Seats

150

Ticket Revenue

$45,000

Number Denied Boarding

2

Bumping Cost

$900

Fixed Cost

$30,000

Profit

$14,100

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

58

59

60

61

62

63

A

B

C

D

E

F

Freddie the Newsboy

Historical

Demand

Data

Day

Data

Unit Sale Price

$2.50

1

62

Unit Purchase Cost

$1.50

2

45

Unit Salvage Value

$0.50

3

59

4

65

Decision Variable

5

50

Order Quantity

60

6

64

7

56

Simulation

8

51

Simulated Demand

55

9

55

Demand (rounded)

55

10

61

11

40

Sales Revenue

$137.50

12

47

Purchasing Cost

$90.00

13

63

Salvage Value

$2.50

14

68

15

67

Profit

$50.00

16

67

17

68

55

41

56

42

57

64

58

45

59

59

60

70