How to price a Bill of Quantities in Excel
A working order of play for an estimator with a tender on the desk: read the rules, get the bill into one sheet, prove the sheet is complete, then price it and check it again before it leaves the office.
In plain terms
What pricing a BOQ involves
- Pricing a bill of quantities
- Entering a unit rate against every measured item in the bill so that quantity x rate gives each amount, and the amounts add up through the collection pages to the tender sum.
The arithmetic is simple. The work is in knowing the sheet matches the bill, building rates you can defend and treating provisional sums, prime cost sums, rate-only and included items the way the document says. This guide follows that order.
It is a practical guide, not a standard. The contract documents, the preambles and the instructions to tenderers govern anything on which this page differs.
Step by step
Nine steps from tender PDF to a priced bill
-
Read the preambles and the instructions to tenderers first
The rules for pricing live there, not in the item lines: what the rates must include, how preliminaries are to be priced, how errors are treated and in what form the priced bill must come back. Ten minutes here saves a re-price later.
-
Check the sheet against the document before pricing
Compare the item count with the bill, and each section total with the "Carried to Collection" figure where the bill is priced. Resolve flagged lines now. A fault found after the rates are in means re-checking the rates as well.
-
Separate the lines that are not measured work
Filter the Tags column. Provisional sums and prime cost sums carry the amount stated. Rate-only lines need a rate but stay out of the total. "Included" lines have no separate price. "Extra over" lines price only the additional cost over the parent item.
-
Build a unit rate for each measured item
Combine labour (output and wage cost), plant, materials with wastage, and subcontract or supply quotes. Use your own historic rates where you have them and current quotes where you do not. Keep the build-up on a separate sheet so the pricing sheet stays one row per item.
-
Enter the rates and let the amounts calculate
In the workbook, Amount is Quantity x Rate once both are present. Do not overtype it. Lines with a fixed amount, such as a provisional sum, are the only ones that should hold a typed amount.
-
Price preliminaries, overheads and profit as the tender asks
Where the bill has a preliminaries section, price its items. Where the instructions allow overheads and profit to be spread through the rates, apply them consistently. Where they require a separate line, keep it separate. Do not add margin to a provisional sum unless the document invites you to.
-
Reconcile with the collection pages and summary
Every section total on the Sections sheet should equal the figure the tender prints once rates are in, and the sections should add up to the summary. Any variance means a line is missing, repeated or priced wrongly.
-
Return the price in the form the tender requires
Most tenders want the prices on the BOQ as issued, with the same item order and numbers. Copy the rates across, re-check the arithmetic and read how the instructions treat arithmetical errors before you submit.
Check list
Where pricing sheets go wrong, and how to catch it
| Fault | How it happens | How to catch it |
|---|---|---|
| A wrapped description priced as two items | The second line has no item number or quantity but looks like a row. | Compare the item count with the bill. The converter joins such rows to the item above. |
| A page missing from the sheet | A page is skipped when copying or the file is cut short. | The next "Carried to Collection" does not tie; the section shows a variance. |
| A "Carried to Collection" row added up as work | The subtotal is read as an item, so the section total doubles. | Subtotal rows are treated as checkpoints and are never summed into the amount. |
| A quantity with the wrong or no unit | The unit drops off, or m and m2 are mixed up. | Quantities without a unit are flagged, and totals are kept per unit, never across units. |
| A rate typed over a calculated amount | A typed amount replaces the formula, so later rate changes do not flow through. | Items whose amount is not quantity times rate within 0.5% are flagged. |
| A provisional sum re-priced | A rate is entered against a sum the client has already fixed. | Filter Tags for provisional-sum and prime-cost and check each carries the stated amount. |
Compared
Preparing the pricing sheet three ways
| What you need | Re-typing by hand | Generic PDF-to-Excel | BOQ Estimator |
|---|---|---|---|
| Preparing the sheet | Type each line from the PDF, then check it. | Convert, then repair wrapped lines, headers and subtotals. | One row per item with sections and tags, and the checks laid out per section. |
| Knowing it is complete | Count and re-add by hand. | Not available. | Item count, amount sum, stated subtotal and variance for every section. |
| Finding the lines that need judgement | Reading every description for "provisional", "PC" or "rate only". | Nothing marks them. | Tags column: provisional-sum, prime-cost, rate-only, included, extra-over. |
| Pricing in the sheet | Your own formulas. | Your own formulas. | Amount formulas ready, and a Sections sheet that re-adds as you price. |
FAQ
Questions estimators ask
How do you price a bill of quantities?
Read the preambles first, then work item by item: build a unit rate for each line from labour, plant, materials and any subcontract or supply quotes, multiply by the bill quantity, and carry the amounts through the collection pages to the summary. Add preliminaries, overheads and profit as the tender allows, and check the arithmetic before it leaves the office. The steps above set that out in order.
What goes into a unit rate?
Typically labour (output rates times wage cost), plant (hire or owned cost times hours), materials (delivered price plus wastage) and subcontract or supplier quotes, with allowances for site overheads where you do not price them in the preliminaries. How you split overheads and profit between rates and a separate adjustment is a company policy, but the tender instructions may restrict it.
How do I treat a provisional sum?
Carry it at the amount stated in the bill. It is the client's allowance for work that cannot be defined yet, not something to re-price. The preambles or the item itself say whether you may add attendance, profit or both against it, so read the item before you add anything.
What is a prime cost sum?
An allowance for goods or work to be supplied by a supplier or specialist the client names or selects. Carry the stated sum. Where the bill asks for profit and attendance to be priced separately against it, price those on their own lines and keep them visible.
What does "Included" mean against an item?
That the cost is covered by another item or rate, so there is nothing separate to price. Do not leave the line blank without checking which item carries the cost, and do not enter a second rate for the same work. The converter tags these lines so you can find every one.
What does "rate only" mean?
The client wants a rate for the item but is not giving a quantity, so it does not feed the tender sum. Typical uses are options, daywork and work that might be instructed later. Price the rate with the same care as a measured item, because it can be used to value instructed work.
How can I check a pricing sheet before it goes out?
Count the items and compare with the document; compare each section total with the collection figure; look for blank rate cells; confirm no wrapped line has been priced as its own item; and check that your grand total equals the sum of the collection pages. In the converter's output those checks are already laid out per section: item count, amount sum, stated total and variance.
Should I keep the original item numbers and order?
Yes. Reviewers compare your return against the issued bill line by line, and any later valuation is made against those numbers. Keep the item references exactly as printed and do not re-sort the lines. The converter keeps document order and prints the item number as written.
What if the BOQ is longer than the page cap?
Pro handles files up to 150 pages. A 300-page tender is over that cap, so split the PDF at a bill or section boundary and convert the parts. Each part ties out against its own subtotals. Join the results afterwards in Excel, keeping the section names unchanged.
Do I need the drawings and specification as well as the BOQ?
Yes. A bill is a list of what has been measured, and its item descriptions refer to the specification and drawings. Gaps, ambiguities and items that appear to be missing are best found by reading the bill against those documents, and raised as tender queries before the closing date.