EXAMPLES OF PERFORMING SIMULATIONS ON SPREADSHEETS WITH ANALYTIC SOLVER PLATFORM:DECISION MAKING WITH PARAMETER ANALYSIS REPORTS AND TREND CHARTS

DECISION MAKING WITH PARAMETER ANALYSIS REPORTS AND TREND CHARTS

Many simulation models include at least one decision variable. For example, the model formulated for both the bidding example in Sec. 28.1 and the overbooking example in Sec.

28.5 included a single decision variable, as listed below:

Bidding example: OurBid (C25) in Fig. 28.2

Overbooking example: ReservationsToAccept (C13) in Fig. 28.16

In both of these cases, you have seen how well simulation with ASPE can evaluate a particular value of the decision variable by providing a wealth of output for the results cell(s). However, in contrast to many OR techniques, this approach has not identified an optimal solution for the decision variable(s). Fortunately, ASPE provides a way to systematically perform multiple simulations by using parameter cells. This makes it easy to identify at least an approximation of an optimal solution for problems with only one or two decision variables. In this section, we describe this approach and illustrate it by applying it in turn to the two decision variables listed above. (Recall that Sec. 20.6 included still another approach, using the Solver in ASPE to search for an optimal solution for simulation models.)

An intuitive approach for searching for an optimal solution is to use trial and error. 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 interactive simulation mode in ASPE makes this especially easy, since the results in the statistic cells are available immediately after changing the value of a decision variable. Using parameter cells allows you to do the same thing in a more systematic way. After defining a parameter cell, all the desired simulations are run and the results soon are displayed nicely in the parameter analysis report. If desired, you also can view an enlightening trend chart, which can provide additional details about the results.

If you have previously used parameter cells with the Solver in ASPE to generate para- meter analysis reports for performing sensitivity analysis systematically (as was done in Chap. 7), the parameter analysis reports in simulation models work in much the same way. Two is the maximum number of decision variables that can be varied simultaneously in a parameter analysis report.

Let us begin by returning to the bidding example mentioned above and use a parame- ter cell to run multiple simulations.

A Parameter Analysis Report for the Reliable Construction Co. Bidding Problem

We turn now to generating a parameter analysis report for the Reliable Construction Co. bidding problem presented in Sec. 28.1. Since the procedure for how to generate a parameter analysis report already has been presented in Sec. 20.6, our focus here is on summarizing the results.

Recall that the management of the company is concerned with determining what bid it should submit for a project that involves constructing a new plant for a major manufacturer. Therefore, the decision variable in the spreadsheet model in Fig.28.2 is OurBid (C25). The parameter cell dialog box in Fig. 28.29 is used to further describe this decision variable. Management feels that the bid should be in the range between $4.8 million and

$5.8 million, so these are the numbers (in units of millions of dollars) that are entered into the entry boxes for Bounds in this dialog box.

Management wants to choose the bid that would maximize its expected profit. Consequently, the results cell in the spreadsheet model is Profit (C29). After choosing Parameter Analysis from the Reports>Simulation menu on the ASPE ribbon, the corresponding dialog box in Fig. 28.30 is used to specify that the mean of the Profit should be shown as the

parameter cell OurBid is varied over six major axis points. The six values automatically are distributed evenly over the range specified in Fig. 28.29, so simulations will be run for bids of 4.8, 5.0, 5.2, 5.4, 5.6, and 5.8 (in millions of dollars).

Figure 28.31 shows the resulting parameter analysis report. A bid of $5.4 million gives the largest mean value of the profits obtained on the 1,000 trials of the simulation run. This mean value of $482,000 in cell B5 should be a close estimate of the expected profit from using this bid. The prototype example in Chap. 22 begins with the company having just won the contract by submitting this bid.

Problem 28.8 asks you to refine this analysis by generating a parameter analysis report that considers all bids between $5.2 million and $5.6 million in multiples of $0.05 million.

A Parameter Analysis Report and Trend Chart for the Transcontinental Airlines Overbooking Problem As described in Sec. 28.5, Transcontinental Airlines has a popular daily flight from San Francisco to Chicago with 150 seats available. The number of requests for reservations usually exceeds the number of seats by a considerable amount. However, even though the fare is nonrefundable, an average of only 80 percent of the customers who make reservations actually show up to take the flight, so it seems appropriate to accept more reservations than can be flown. At the same time, significant costs are incurred if customers with reservations are not allowed to take the flight. Therefore, the company’s OR group is analyzing what number of reservations should be accepted to maximize the expected profit from the flight.

In the spreadsheet model in Fig. 28.16, the decision variable is Reservations To Accept (C13) and the results cell is Profit (F23). The OR group wants to consider integer values of the decision variable over the range between 150 and 200, so the parameter cell dialog box is used in the usual way to specify these bounds on the variable. The decision is made to test 11 values of Reservations To Accept (C13), so simulations will be run for values in intervals of five between 150 and 200.

The results are shown in Fig. 28.32. The parameter analysis report on the left side of the figure reveals that the mean of the profit values obtained in the respective simulation runs climbs rapidly as Reservations To Accept (C13) increases until the mean reaches a peak of $11,912 at 185 reservations, after which it starts to drop. Only the means at 180 and 190 reservations are close to this peak, so it seems clear that the most profitable num- ber of reservations lies somewhere between 180 and 190. (Now that the range of numbers that need to be considered has been narrowed down this far, Problem 28.10 asks you to continue that analysis by generating a parameter analysis report that considers all integer values over this range.)

The trend chart on the right side of Fig. 28.32 provides additional insight. The bands in this chart trend upward until the number of reservations to accept reaches approximately

185; then they start trending slowly downward. This indicates that the entire frequency distribution from the respective simulation runs keeps shifting upward until the run for 185 reservations and then starts shifting downward. Also note that the width of the entire set of seven bands increases until about the simulation run for 180 reservations and then remains about the same thereafter. This indicates that the amount of variability in the profit values also increases until the simulation run for 180 reservations and then remains about the same thereafter.