Bid Comparison Change Order Calculator

Purpose and model boundary

This calculator compares the bids you received for the same work on the whole scope, awards the best one, and adds your change orders to it. You list the scope items with your own allowance for each, the bids with the items each one leaves out, your estimate for the work and any change orders. The spreadsheet prices every bid on the whole scope by adding your allowance for each item it excludes, ranks the bids on that normalized price and then by duration, and returns the awarded bid with its revised total, duration and margin against your estimate after the change orders. It is arithmetic on the figures you enter: it is not a procurement recommendation or a contract document, and it does not judge a bidder's qualifications, bonding, insurance or schedule risk or the quality of the work. To weigh bids on criteria other than price, such as experience or schedule risk, use the Bid Evaluation Matrix.

Inputs and units

• Scope items and allowances: up to 10 items, each with a name and your allowance for it, a number of 0 or more. The allowance is your own figure for what the item would cost you if a bid leaves it out. At least one item is required.
• Bids: up to 10 bids, each with a Bidder name, a Price greater than zero, the Days it takes (0 or more), and Excludes: the scope items the bid leaves out, separated by commas and spelled as in the scope table. Leave Excludes blank when the bid covers the whole scope. At least one bid is required.
• Your estimate for this work: the figure the revised total is measured against, greater than zero.
• Change orders: up to 10, optional. Each has a description for your own reference, a cost change and a days change. Both changes are signed: a positive cost or days change adds work or time, a negative one is a credit or a saving, and 0 enters no change. Leave the table empty when there are no change orders.

Each table fills its rows from the top, with no blank row between them. The prices, allowances and costs carry no currency: every figure is in the currency you enter, and nothing is converted. Durations are in days.

The Excludes list follows these rules: the items are separated by commas; case and spaces around an item are ignored, so CLEANUP names cleanup; each item is named once; the items may come in any order; and the cell is blank for a bid that covers the whole scope. Writing none, a name that is not a scope item, an item twice or a trailing comma is refused. Because the list is separated by commas, a scope item name cannot contain one. Two scope items, or two bidders, may not share a name, again ignoring case and spaces at either end.

The page opens on worked example 11.5.7.1 of the requirement this calculator was built against, a five-item remodel: demolition 1,500, framing 6,000, drywall 3,500, paint 2,200 and cleanup 800, three bids (Alpha at 14,000 over 20 days, Bravo at 12,800 over 18 days leaving out cleanup, and Charlie at 15,500 over 15 days), an estimate of 16,000 and no change orders. These are the requirement's example figures, for review before use.

Governing relationships

A bid's excluded items are priced at your allowance for them, and the bid is compared on the whole scope:

Normalized price = price + the allowances of the items the bid excludes

The bids are ranked by normalized price, lowest first, then by duration: a bid is ranked ahead of another when its normalized price is lower, or when the two are equal and it takes fewer days. Both comparisons are made on the figures rounded to six decimal places, so two sums that differ only in the last binary digits count as equal. The best-ranked bid is awarded. Bids that tie on both normalized price and duration share the first rank: all of them are listed as awarded, as Tie: Alpha / Golf, and the status is a CHECK.

The change orders then apply to the awarded bid:

Revised total = normalized price of the awarded bid + the sum of the change orders' cost changes

Revised duration = days of the awarded bid + the sum of the change orders' days changes

Revised margin = (estimate − revised total) ÷ estimate

The revised figures build on the normalized price, not the bid's own price, so an excluded item stays in the total at your allowance. The margin is negative when the revised total is over your estimate, and a revised total over the estimate, compared at six decimal places, gives a CHECK.

A bid that excludes an item is compared at your allowance for it; one that excludes everything is compared at your own figures for the whole scope. The allowance is the whole cost of an excluded item here, whatever it finally costs you.

Duration only breaks ties. A faster bid is not preferred at any price difference, however small: weigh time against money yourself.

Calculation sequence

1. The spreadsheet reads the three tables row by row, with spaces at either end of a name removed and names compared without regard to case.
2. It checks the tables and the estimate, as described below.
3. Each bid's excluded items are matched to the scope, and their allowances are added to its price: the normalized price.
4. The bids are ranked by normalized price, then by duration, ties in the order they were entered, and the best-ranked bid is awarded.
5. The change orders are added to the awarded bid: the revised total and the revised duration, and the margin against your estimate.
6. The status line is chosen. When it begins NOT VALID:, every figure reads 0 and the ranking is empty.

For the job the page opens with, worked example 11.5.7.1: Alpha covers the whole scope at 14,000; Bravo leaves out cleanup, so your 800 allowance is added to its 12,800, a normalized price of 13,600; Charlie covers the whole scope at 15,500. Bravo ranks first at 13,600, then Alpha and Charlie. With no change orders the revised total is 13,600 over 18 days, and the margin is (16,000 − 13,600) ÷ 16,000 = 15.0%.

Worked example 11.5.7.2: Delta's 9,900 leaves out demolition and cleanup, so 2,300 of allowances bring it to 12,200, below Alpha's 14,000: Delta is awarded at 12,200 over 22 days, a margin of 23.75%. Worked example 11.5.7.3: Bravo is awarded at 13,500 against Alpha's 14,000, and two change orders, a closet at 1,800 and 3 days and a paint upgrade at 600 and no days, bring the revised total to 15,900 over 21 days, a margin of 0.625%. Worked example 11.5.7.4: Echo's 12,500 plus the 800 cleanup allowance is 13,300, below Alpha's 14,000, so Echo is awarded although it takes 25 days to Alpha's 20; against a 15,000 estimate the margin is 11.3%. Worked example 11.5.7.5: one bid, Alpha at 14,000 over 20 days, and a credit change order deleting the paint, −2,200 and −2 days, give a revised total of 11,800 over 18 days and a margin of 26.25%, which the page shows as 26.3% (the requirement prints 26.2%).

When Alpha at 14,000 over 20 days meets Echo at 13,200 leaving out cleanup, the two tie on normalized price at 14,000, and Alpha's 20 days decide it. When Golf at 13,200 over 20 days, leaving out cleanup, meets Alpha, they tie on both, and the awarded bid reads Tie: Alpha / Golf with a CHECK.

Outputs and interpretation

Awarded bid, Awarded bid, normalized price, Revised total with change orders, Revised duration with change orders and Revised margin against your estimate are the headline figures. Change orders, net cost and Change orders, net days are the details, with the table Bids ranked by normalized price: each bid's Bidder, Price, Excludes (in scope order, spelled as in the scope table, blank for none), Allowance, Normalized price, Days and Rank, listed by rank with ties in the order entered. The margin is shown as a percentage.

The page shows the amounts to two decimals and the margin to one decimal, and every figure is the spreadsheet's own.

Validation and status logic

The status line is the spreadsheet's own verdict. It is the first of these that applies:

• NOT VALID: the scope items must fill consecutive rows from the top, with no blank row between them
• NOT VALID: the bids must fill consecutive rows from the top, with no blank row between them
• NOT VALID: the change orders must fill consecutive rows from the top, with no blank row between them
• NOT VALID: every scope item needs a name and an allowance, entered as a number
• NOT VALID: an allowance cannot be negative
• NOT VALID: a scope item name cannot contain a comma, because bids list the items they exclude separated by commas
• NOT VALID: two scope items have the same name; give each item its own
• NOT VALID: enter at least one scope item
• NOT VALID: enter at least one bid
• NOT VALID: every bid needs a bidder name
• NOT VALID: every bid needs a price greater than zero
• NOT VALID: every bid needs a duration in days, zero or more
• NOT VALID: two bids have the same bidder name; give each bid its own
• NOT VALID: each item a bid excludes must be a scope item named once, with the items separated by commas; leave the cell blank when the bid covers the whole scope
• NOT VALID: every change order needs a cost change and a days change, entered as numbers (0 for none)
• NOT VALID: enter your estimate, greater than zero
• NOT VALID: the change-order credits are larger than the awarded bid's normalized price, so the revised total would be below zero
• NOT VALID: the change orders remove more days than the awarded bid takes

A blank row between rows names the first table that has one, in the order scope items, bids, change orders. Otherwise the status is OK, or a CHECK when bids tie on both normalized price and duration, when the revised total is over your estimate, or both: CHECK: bids tie on normalized price and duration, so every tied bid is listed as awarded, CHECK: the revised total is over your estimate, or the two joined by a semicolon. A CHECK comes with the figures; a NOT VALID: verdict is shown in place of them, every figure reading 0.

Every check this workbook makes applies to the web page, an API call and an MCP call alike; the platform first refuses out-of-bounds, mistyped and missing required values in its own words. So a negative allowance, price or duration, a negative estimate, a bidder name entered as a number, an empty scope or bids table, a scope item with no name or no allowance and a bid with no bidder, price or days are refused before the spreadsheet is asked, and the spreadsheet's own lines for them are a second line of defence. A blank row between rows, a comma or a repeated name, a price of zero, an Excludes entry that does not match the scope, a change order with no cost or days change, change orders that take the total or the duration below zero, and an estimate of zero reach the spreadsheet, which refuses them.

Assumptions and limitations

• The allowances are your own, and a bid that excludes an item costs exactly your allowance for it, whatever the item finally costs.
• Duration decides only between bids with equal normalized prices; a faster bid is not preferred at any price difference, and the value of time is not modelled.
• Bidder qualifications and bonding, alternates and unit prices, markup on change orders, liquidated damages, retainage, taxes, escalation and currency conversion are not modelled.
• The calculator does not check that the bids describe the same work beyond the scope items you list.
• The tables hold 10 scope items, 10 bids and 10 change orders, filled from the top with no blank row between rows.
• The descriptions of change orders feed no figure.
• Nothing is rounded: money shows two decimals and the margin one, so an exact 26.25% shows as 26.3%.
• The default scope, bids and estimate are the requirement's example figures for review before use.

Restrictions and non-computing states

This calculator accepts allowances of 0 or more, bid prices greater than zero, durations of 0 or more, an estimate greater than zero, and signed change orders. The scope and bids tables need at least one row each, complete in their required columns. A blank row between rows, a scope item name with a comma, a repeated scope item or bidder, an Excludes entry that is not a list of scope items named once, a change order missing a cost or days change, and change orders taking the revised total or duration below zero are not computed.

Errors and warnings

The results update as you change the inputs. A blank required input, a bid with no bidder, price or days among them, stops the calculation until you fill it, and the page asks for the required inputs in place of the results. If the calculation service refuses an input, the page names the input and the reason. If the service does not answer, the page shows the error and keeps your inputs. A NOT VALID: status means the figures cannot be computed and none are shown; a CHECK: status is shown beside the figures.

Sources and further reading

The model is the requirement's arithmetic on the figures you enter. Its specification is section 5.7 of the Contractor Calculator Requirements v2.3, and its five worked examples (11.5.7.1 to 11.5.7.5) are its tests. It uses no published rate, factor, code value or standard: every price, allowance, duration and change order is your own. The sources below describe the practice its two comparisons and its change orders come from; they supply no figure to it.

• Federal Acquisition Regulation, 48 CFR 15.404-1, "Proposal analysis techniques," paragraph (b)(2). acquisition.gov/far/15.404-1. Price analysis by comparing the prices received in response to a solicitation with each other and with an independent cost estimate: the ranking of the bids and the margin against your estimate.
• Federal Acquisition Regulation, 48 CFR 52.243-4, "Changes," paragraph (d). acquisition.gov/far/52.243-4. A change that increases or decreases the contractor's cost of, or the time required for, the work is met by an equitable adjustment to the contract: the basis of the change orders' cost and days added to the awarded bid.

Links were current at build time. If one moves, search the document's title rather than reporting a broken link as a defect.

The default scope, allowances, bids and estimate are the figures of the requirement's first worked example, for review before use; they are not the prices of any market or the allowances of any job. This calculator does not certify a bid, a bidder or a contract. Check each bid's scope, exclusions and terms with the bidder, and use allowances from your own records or quotes.