Chapter 61: Calculating an Optimal Bid | MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

How do I simulate a binomial random variable?
How can I determine whether a continuous random variable should be modeled as a normal random variable?
How can I use simulation to determine the optimal bid for a construction project?

When bidding against competitors on a project, the two major sources of uncertainty are the number of competitors and the bid submitted by each competitor. If your bid is too high, you’ll make a lot of money on each project, but you’ll get very few projects. If your bid is too low, you’ll work on lots of projects, but make very little money on each one. The optimal bid is somewhere in the middle. Monte Carlo simulation is a useful tool for determining the bid that maximizes expected profit.

How do I simulate a binomial random variable?
The formula CRITBINOM(n,p,rand()) simulates the number of successes in n trials, each of which has a probability of success equal to p. As shown in the file Binomialsim.xlsx (see Figure 61-1 on the next page), when you press F9, the formula CRITBINOM(100,0.9,D3) entered in cell C3 simulates the number of free throws that Steve Nash (a 90 percent foul shooter in the NBA) makes in 100 attempts. The formula CRITBINOM(100,0.5,D4) in cell C4 simulates the number of heads tossed in 100 tosses of a fair coin. In cell C5, the formula CRITBINOM(3,0.4,D5) simulates the number of competitors entering the market during a year in which there are three possible entrants and each competitor is assumed to have a 40 percent chance of entering the market. Of course, in D3:D5, I’ve entered the formula =RAND().

Figure 61-1: Simulating a binomial random variable
How can I determine whether a continuous random variable should be modeled as a normal random variable?
Let’s suppose we think the most likely bid by a competitor is $50,000. Recall that the normal pdf is symmetric about its mean. Therefore, to determine whether a normal random variable can be used to model a competitor’s bid, we need to test for symmetry about the bid’s mean. If the competitor’s bid exhibits symmetry about the mean of $50,000, bids of $40,000 and $60,000, $45,000 and $55,000, and so on should be approximately equally likely. If the symmetry assumption seems reasonable, we can then model each competitor’s bid as a normal random variable with a mean of $50,000.
How can we estimate the standard deviation of each competitor’s bid? Recall from the rule of thumb discussed in Chapter 37, “Summarizing Data by Using Descriptive Statistics,” that data sets with symmetric histograms have roughly 95 percent of their data within two standard deviations of the mean. Similarly, a normal random variable has a 95 percent probability of being within two standard deviations of its mean. Suppose that we are 95 percent sure that a competitor’s bid will be between $30,000 and $70,000. This implies that 2*(standard deviation of competitor’s bid)=$20,000, or the standard deviation of a competitor’s bid equals $10,000.
Assuming the symmetry assumption is reasonable, we could now simulate a competitor’s bid with the formula NORMINV(rand(),50000,10000). (See Chapter 60, “Introduction to Monte Carlo Simulation,” for details about how to model normal random variables using the NORMINV function.)
How can I use simulation to determine the optimal bid for a construction project?
Let’s assume that we’re bidding on a construction project that will cost us $25,000 to complete. It costs us $1,000 to prepare our bid. There are six potential competitors, and we estimate that there is a 50 percent chance that each competitor will bid on the project. If a competitor places a bid, their bid is assumed to follow a normal random variable with a mean equal to $50,000 and a standard deviation equal to $10,000. Also suppose we are only considering bids that are exact multiples of $5,000. What should we bid to maximize expected profit? Remember, the low bid wins! Our work is in the file Bidsim.xlsx, shown in Figures 61-2 and 61-3.

Figure 61-2: Bidding simulation model

Figure 61-3: Bidding simulation data table
Our strategy is as follows:
- Generate the number of bidders.
- For each potential bidder who actually bids, use the normal random variable to model the bid. If a potential bidder does not bid, we assign a large bid (for example, $100,000) to ensure that they do not win the bidding.
- Determine whether we are the low bidder.
- If we are the low bidder, we earn a profit equal to our bid, less project cost, less $1,000 (the cost of making the bid). If we are not the low bidder, we lose the $1,000 cost of the bid.
- Use a two-way data table to simulate each possible bid (for example, $30,000, $35,000, … $60,000) 1000 times, and then choose the bid with the largest expected profit.
To begin, I’ve assigned the names in the cell range D1:D4 to the range E1:E4. We determine in cell E3 the number of bidders with the formula CRITBINOM(6,0.5,F3). Cell F3 contains the =RAND() formula. Next we determine which of our potential bidders actually bid by copying from E9 to E10:E14 the formula IF(D9<=Number_bidders,"yes","no").
We then generate a bid for each bidder (nonbidders are assigned a bid of $100,000) by copying from cell F9 to F10:F14 the formula IF(E9="yes",NORMINV(G9,50000, 10000),100000). Each cell in the cell range G9:G14 contains the =RAND() function. In cell D17, I determine whether I am the low bidder and win the project with the formula IF(mybid<=MIN(F9:F14),"yes","no"). In cell D19, I compute my profit with the formula IF(D17="yes",mybid–costproject–cost_bid,–cost_bid), recognizing that I only receive the amount of the bid and pay project costs if I win the bid.
Now we can use a two-way data table (shown in Figure 61-3) to simulate 1000 bids between $30,000 and $60,000. We copy our profit to cell D22 by entering the formula =D19. Then we select the table range D22:K1022. On the Data tab of the Ribbon, in the Data Tools group, click What-If Analysis and then click Data Table to specify the input values for the data table. Our column input cell is any blank cell in the worksheet, and our row input cell is E4 (the location of our bid). Clicking OK in the Data Table dialog box simulates the profit from each bid 1000 times.
Copying from E21 to F21:K21 the formula AVERAGE(E23:E1022) calculates the mean profit for each bid. Each time we press F9, we see that our mean profit for 1000 trials is maximized by bidding $40,000.