EXAMPLES OF PERFORMING SIMULATIONS ON SPREADSHEETS WITH ANALYTIC SOLVER PLATFORM:BIDDING FOR A CONSTRUCTION PROJECT

BIDDING FOR A CONSTRUCTION PROJECT

Managers frequently must make decisions whose outcomes will be greatly affected by the corresponding decisions being made by the management of competitor firms. For example, marketing decisions often fall into this category. To illustrate, consider the case in which a manager must determine the price for a new product being brought to market. How well this decision works out will depend greatly on the pricing decisions being made nearly simultaneously by other firms marketing competitive new products. Similarly, the success of a decision on how soon to market a product under development will be determined largely by whether this product reaches the market before competitive products are released by other firms.

When a decision must be made before learning the corresponding decisions being made by competitors, the analysis needs to take into account the uncertainty surrounding what competitors’ decisions will be. Simulation provides a natural way of doing this by using uncertain variable cells to represent competitors’ decisions.

The following example illustrates this process by considering a situation where the decision being made is the bid to submit on a construction project while three other companies are simultaneously preparing their own bids.

The Reliable Construction Co. Bidding Problem

The prototype example carried throughout Chap. 22 involves the Reliable Construction Co. and its project to construct a new plant for a major manufacturer. That chapter describes how the project manager (David Perty) made extensive use of PERT/CPM mod- els to help guide his management of the project.

As the opening sentence of Sec. 22.1 indicates, the example in that chapter begins as the company has just made the winning bid of $5.4 million to do this project. We now will back up in time to describe how the company’s management used simulation with ASPE to guide its choice of $5.4 million as its bid for the project. You will not need to review the presentation in Chap. 22 to follow the current example.

Reliable’s first step in this process was to estimate what the company’s total cost would be if it were to undertake the project. This was determined to be $4.55 million. (This amount excludes a penalty for missing the deadline for completion of the project, as well as a bonus for completion well before the deadline, since management considers either event to be relatively unlikely.) There also is an additional cost of approximately $50,000 for preparing the bid, including estimating the project cost and analyzing the bidding strategies of the competition.

Three other construction companies also were invited to submit bids for this project. All three have been long-standing competitors of the Reliable Construction Co., so the company has had a great deal of experience in observing their bidding strategies. A veteran analyst in the bid preparation office has taken on the task of estimating what bid each of these competitors will submit. Since there is so much uncertainty in this process, the analyst has determined that each of these estimates needs to be in the form of a probability distribution. Competitor 1 is known to use a 30 percent profit margin above the total (direct) cost of a project in setting its bid. However, competitor 1 also is a particularly unpredictable bidder because of an inability to estimate the true costs of a project with much accuracy. Its actual profit margin on past bids has ranged from as low as minus 5 percent to as high as 60 percent. Competitor 2 uses a 25 percent profit margin and is somewhat more accurate than competitor 1 in estimating project costs, but it still has set bids in the past that have missed this profit margin by as much as 15 percent in either direction. On the other hand, competitor 3 is unusually accurate in estimating project costs (as is the Reliable Construction Co.). Competitor 3 also is adept at adjusting its bidding strategy, so it is equally likely to set its profit margin anywhere between 20 and 30 percent, depending on its assessment of the competition, its current backlog of work, and various other factors.

This information about the competitors is invaluable, but the analyst who developed it knew that her work wasn’t quite done yet. Based on these numbers, she still needed to develop an estimate of the probability distribution of what the bid will be for each of the competitors.

This task is relatively straightforward in the case of competitor 3. Because the analyst estimates that this competitor is equally likely to set its profit margin anywhere between 20 and 30 percent, its bid then is equally likely to be anywhere between 120 and 130 percent of the total project cost. The probability distribution that fits this is the uniform distribution between 120 and 130 percent.

However, this task is not as easy when considering competitors 1 and 2. Fortunately, the analyst has been able to estimate three key numbers for each competitor—a minimum value, a most likely value, and a maximum value—for the profit margin and so (by adding 100 per- cent) for the bid as a percentage of the total project cost. For example, the analyst has esti- mated that the bid of competitor 1 (expressed as a percentage of total project cost) has a minimum value of 95 percent, a most likely value of 130 percent, and a maximum value of 160 percent. (The corresponding numbers for competitor 2 are 110 percent, 125 percent, and 140 percent, respectively.) There is a particularly convenient type of probability distribution

Triangular distribution

called the triangular distribution that is based on these same three kinds of numbers. Figure 28.1 shows the shape of a triangular distribution. Its three parameters are min (the minimum value), likely (the most likely value), and max (the maximum value). (Figure 28.1 shows likely as being much closer to min than to max, but it actually can be anywhere between min and max.) These three parameters are a perfect fit for the distributions of the bids from competitors 1 and 2, so the analyst has chosen a triangular distribution as her best estimate of these distributions. (This is not surprising since triangular distributions are a particularly popular choice for performing simulations.)

In summary, the estimated probability distributions of the bids that the three competi- tors will submit, expressed as a percentage of Reliable’s assessment of the total project cost ($4.55 million), are as follows.

Competitor 1: A triangular distribution with a minimum value of 95 percent, a most likely value of 130 percent, and a maximum value of 160 percent.

Competitor 2: A triangular distribution with a minimum value of 110 percent, a most likely value of 125 percent, and a maximum value of 140 percent.

Competitor 3: A uniform distribution between 120 percent and 130 percent.

A Spreadsheet Model for Applying Simulation

Figure 28.2 shows the spreadsheet model that has been formulated to evaluate any possible bid that Reliable might submit. Since there is uncertainty about what the competitors’ bids will be, this model needs CompetitorBids (C8:E8) to be uncertain variable cells, so the above probability distributions are entered into these cells. As described in Sec. 20.6, this is done by selecting each cell in turn, choosing the appropriate distribution from the Distribu- tions menu on the ASPE ribbon (in this case under the Common submenu), which brings up the dialog box for that distribution. Figure 28.3 shows the Triangular Distribution dialog box that has been used to set the parameter values (min, likely, and max) for competitor 1, and competitor 2 would be handled similarly. These parameter values for competitor 1 come from cells C18:C20, where the parameters in percentage terms (cells C13:C15) have been converted to dollars by multiplying them by OurProjectCost (C4). The Uniform Distribu- tion dialog box is used instead to set the parameter values for competitor 3 in cell E8.

MinimumCompetitorBid (C23) records the smallest of the competitors’ bids for each trial of the simulation. The company wins the bid on a given trial only if the quantity entered into OurBid (C25) is less than the smallest of the competitors’ bids. The IF func- tion entered into WinBid? (C27) then returns a 1 if this occurs and a 0 otherwise.

Since management wants to maximize the expected profit from the entire process of determining a bid (if the bid wins) and then doing the project, the results cell in this model is Profit (C29). The profit achieved on a given trial depends on whether the company wins the bid. If not, the profit actually is a loss of $50,000 (the bid cost). However, if the bid wins, the profit is the amount by which the bid exceeds the sum of the project cost and the bid cost. The equation entered into Profit (C29) performs this calculation for whichever case applies. Profit (C29) is defined as a results cell by clicking on the cell and then choosing Output/In Cell from the Results menu on the ASPE ribbon. Finally, MeanProfit (C31) is defined as a statistic cell by selecting the Profit cell (C29), choosing Mean from the Statistic submenu of the Results menu, and then clicking in cell C31. This will show the mean value of the profit after the simulation is run.

Here is a summary of the key cells in this model.

To evaluate a possible bid of $5.4 million entered into OurBid (C25), a simulation of this model ran for 1,000 trials. Figure 28.4 shows the results in the form of a frequency chart and a statistics table. Using units of millions of dollars, the profit on each trial has only two possible values, namely, a loss shown as –0.050 in these figures (if the bid loses) or a profit of 0.800 (if the bid wins). The frequency chart indicates that this loss of $50,000 occurred on about 380 of the 1,000 trials whereas the profit of $800,000 occurred on the other 620

trials. This resulted in a mean profit of 0.487 ($487,000) from all 1,000 trials, as well as the other statistics recorded in the statistics table.

By themselves, these results do not show that $5.4 million is the best bid to submit. We still need to estimate with additional simulation runs whether a larger expected profit could be obtained with another bid value. Section 28.7 will describe how doing this with a parameter analysis report leads to choosing $5.4 million as the bid. This turned out to be the winning bid for the Reliable Construction Co., which then led into the prototype exam- ple that was analyzed in Chap. 22.