Which bid is low enough to win and high enough to pay for the job?

A job costing about 450,000 against a rival guessed at 520,000. Bid 480,000 and you win in 95 in 100 runs; bid 508,000 and you win in 71 in 100 and still clear cost in 99 in 100. That is the 70% bid.

Work Starter Monte Carlo Pro engine

Install free (opens the Google Workspace Marketplace in a new tab) How Monte Carlo simulation works

After you install, this is the model to open.

What bid wins with 70% odds and still makes money?

  1. In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
  2. Click Start from a template and put that name in the search box.
  3. Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.

This one runs on a Pro engine, and every free install includes five full-quality runs on your own numbers, shared across all five Pro engines rather than five for each. After that, Pro is $199/year.

What it does

A job that costs about 450,000 to deliver, against a rival whose lowest bid is guessed at 520,000, with three bids on the table: 480,000, 500,000 and 508,000. As typed all three win, by 40,000, 20,000 and 12,000, and the high bid makes 58,000. Click Run with our cost and the rival's bid as three-point estimates. The seed is set to 7 and the trials to 20,000 so your first run reproduces these figures exactly.

The low bid comes in under the rival in 95 in 100 runs, the middle bid in 79 in 100 and the high bid in 71 in 100, so 508,000 is the bid that wins with at least 70% odds. It still makes money: profit at the high bid has a median of 52,691 and a mean of 51,227, clears zero in 99 in 100 runs and clears 20,000 in 91 in 100. Room under the rival at the high bid has a median of 17,245 and a fifth percentile of -28,042, which is what losing by a margin looks like.

Read the three win counts together and the price of a dollar of margin appears: the 20,000 between the low and middle bids costs 16 points of winning odds, and the next 8,000 costs 8 more. The tornado is not the point of this one. The rival's bid is the whole of the room and our cost is the whole of the profit, each at 1.00, because each output has a single uncertain driver; what the run adds is the two odds, and the sheet cannot produce either.

Second run: give the rival a sharper range, 480,000 low, 500,000 likely and 560,000 high, and rerun. The winning odds fall at every bid and the 70% bid moves down, and how far it moves is what the rival intelligence is worth. What the model cannot tell you: winning and making money are counted separately, so a trial can win and lose money and the two counts do not combine into one; there is one rival, when a tender usually has several; nothing scores quality, only price; and the cost is drawn independently of the bid, when a bid that is too low tends to be delivered by cutting corners.

To make it yours, put in your own cost range and the rival estimate you would defend, and retype the three candidate bids.

The model

It arrives on a tab called Template: Bid With 70% Odds, carrying these columns:

  • Our cost to deliver the job ($)
  • 450000

Once it is in your sheet

  1. The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
  2. Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
  3. Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.

Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.

Next question

Every model like this one, and the method behind them: Monte Carlo simulation.

Install free (opens the Google Workspace Marketplace in a new tab) All 379 templates