Unit price bid schedule with alternates template

	A	B	C	D
1	Unit price bid summary
2	Example data — replace the blue input cells with your own.
3
4	BASE BID	ACCEPTED ALTERNATES, NET	TOTAL CONTRACT AMOUNT
5	$697,583	$2,500	$700,083
6
7	Base bid by section
8	Section	Amount	Share of base bid	Lines with an amount
9	General	$55,900	8.0%	3
10	Earthwork	$101,645	14.6%	3
11	Paving	$337,560	48.4%	5
12	Utilities	$96,230	13.8%	5
13	Landscaping	$40,030	5.7%	5
14	Allowances	$33,000	4.7%	2
15	Contingency	$33,218	4.8%	1
16	Base bid total	$697,583	100.0%	24

	A	B	C	D	E	F	G
1	Base bid: unit prices by section
2	Quantities come from the bid documents; enter the bidder's unit price. Allowances and contingency sit below the item list.
3
4
5
6	Section	Item no.	Description	Unit	Quantity	Unit price	Extended amount
7	General	01.01	Mobilization and demobilization	LS	1	$38,000.00	$38,000.00
8	General	01.02	Temporary construction fencing	LF	1,200	$9.50	$11,400.00
9	General	01.03	Construction staking and survey	LS	1	$6,500.00	$6,500.00
10	Earthwork	02.01	Excavation, common, to subgrade	CY	3,400	$14.00	$47,600.00
11	Earthwork	02.02	Structural fill, placed and compacted	CY	1,850	$22.50	$41,625.00
12	Earthwork	02.03	Fine grading	SY	9,200	$1.35	$12,420.00
13	Paving	03.01	Aggregate base course, 8 in	TON	2,900	$31.00	$89,900.00
14	Paving	03.02	Asphalt paving, 3 in	TON	1,420	$118.00	$167,560.00
15	Paving	03.03	Concrete walk, 5 in, broom finish	SF	6,800	$9.25	$62,900.00
16	Paving	03.04	Accessible curb ramp, detectable warning	EA	4	$3,200.00	$12,800.00

	A	B	C	D	E	F
1	Add and deduct alternates
2	An alternate changes the base bid only when it is accepted. Deducts are shown as negative amounts.
3
4
5	Alternate no.	Description	Type	Amount (positive)	Accepted?	Signed amount
6	A-1	Decorative concrete entry plaza instead of broom finish	Add	$18,500	Yes	$18,500
7	A-2	Omit drinking fountain and its water connection	Deduct	$4,200	Yes	-$4,200
8	A-3	Second restroom building	Add	$96,000	No	$0
9	A-4	Concrete curb in place of granite curb	Deduct	$11,800	Yes	-$11,800
10	A-5	Solar pathway lighting	Add	$27,500	No	$0
11
12
13
14
15
16		Net effect of accepted alternates				$2,500

	A
1	Unit price bid schedule
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Prices a unit price bid schedule for a site improvement project. The base bid is the sum of quantity times unit price for every item, plus allowances and a contingency. Add and deduct alternates are priced separately and move the total con…
6
7	How to use it
8	1. Enter the quantity for each item from the bid documents on the Base bid tab. The example uses fictional quantities.
9	2. Enter your unit price for each item. Items with a quantity but no unit price are reported on the Summary tab.
10	3. Enter the allowance amounts and the contingency rate.
11	4. On the Alternates tab, enter each alternate, its type (Add or Deduct), its positive amount and whether it is accepted.
12	5. Read the base bid, accepted alternates and total contract amount on the Summary tab.
13
14	Formulas and method
15	Extended amount: quantity times unit price. Section subtotals: SUMIF of the extended amounts by section name.
16	Contingency: contingency rate times (sum of item amounts plus allowances). Base bid: item amounts, allowances and contingency.

What does this template do?

A unit price bid schedule is the form a contractor completes for a public or private site project. Each item has a fixed quantity from the bid documents and a unit price that the contractor enters. This workbook extends each line, adds the owner's allowances and a contingency, and totals the base bid by section. Add and deduct alternates are priced separately, and each one moves the total contract amount only when it is accepted.

The Base bid tab holds up to 100 item rows in five sections: General, Earthwork, Paving, Utilities and Landscaping. The Summary tab sums each section with SUMIF, shows each section's share of the base bid, and counts quantity lines with no unit price. The Alternates tab takes each alternate's type, positive amount and accepted status, and signs the accepted amounts. The contract amount is the base bid plus the net effect of the accepted alternates.

The project, quantities, unit prices, allowances and alternates are fictional. Example Park site improvements is a made-up project, and the figures are for layout only.

What’s inside

Extended amounts for up to 100 items, grouped in five sections
Owner allowances and a percentage contingency below the items
Section subtotals with SUMIF and each section's share of the base bid
Five add and deduct alternates, signed only when accepted
Check for quantity lines without a unit price

Which tabs does the workbook have?

Tab	What it holds
Summary	Base bid, accepted alternates and total contract amount, section subtotals, alternates summary and checks.
Base bid	Items with quantity, unit price and extended amount, followed by allowances, contingency and the base bid total.
Alternates	Add and deduct alternates with amount, accepted status and signed amount.
Notes	Purpose, steps, formulas used, assumptions and limits.

What formulas does this template use?

This template holds 148 formulas in 383 cells across 4 tabs, so 39% of its cells calculate. They use 9 distinct functions; the longest formula is 207 characters and 21 of them read from another tab.

Function	Uses	What it does
`IF`	152	one result when a test is true, another when false
`OR`	110	true when any test is true
`SUMPRODUCT`	9	multiplies matching entries, then adds them
`ISNUMBER`	7	true for a number
`SUM`	7	adds numbers
`SUMIF`	7	adds values meeting one condition
`SUMIFS`	2	adds values meeting several conditions
`ABS`	1	absolute value
`COUNTIF`	1	counts cells meeting one condition

Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

On the Base bid tab, enter each item's section, item number, description, unit and quantity from the bid documents.
Enter the unit price for each item in the blue column.
Enter the allowance amounts and the contingency percentage.
On the Alternates tab, enter each alternate with its type, positive amount and accepted status.
Read the base bid, the accepted alternates and the total contract amount on the Summary tab.

What is it good for?

Pricing a unit price bid for site improvements
Showing the effect of accepted add and deduct alternates on the contract
Checking that every quantity line has a unit price before submitting the bid

Questions about this sheet

How is contingency calculated?

Contingency is the contingency percentage times the sum of the item amounts and the allowances. It is calculated after the allowances, so changing an allowance also changes the contingency.

What happens if an item has no unit price?

Its extended amount stays blank, so the base bid leaves it out. The Summary tab reports how many quantity lines have no unit price.

Are deducts shown as negative amounts?

Yes. Enter the deduct amount as a positive number and set its type to Deduct. An accepted deduct is shown as a negative amount, and only accepted alternates change the total.