BOQ to cost plan in Excel: structure, section codes and formulas
To turn a priced BOQ into a cost plan in Excel, give every item a cost code, then total the amounts by code with SUMIF. Keep the items on one sheet, a section-to-code map on another, and a cost plan sheet that rolls up by code and ties back to the tender sum.
By BOQ Estimator. Published .
What a cost plan built from a bill is for
A contractor's commercial team uses a cost plan, or budget, to track packages against the money in the tender: what is allowed for groundworks, frame, finishes, services, preliminaries and so on. The bill is organised the client's way. The cost plan is organised the way you buy and control the work.
If your client or employer prescribes a cost-plan structure, such as an elemental breakdown, use their codes. Otherwise use your own. The mechanics below work for either. For the item layout itself, see the BOQ template.
Two code systems are common. Trade or package codes suit a contractor buying the work, and element codes suit a client's cost plan. If you need both, hold them in two columns and map at item level. Decide too what the budget means: the BOQ total, the BOQ total less an allowance, or the buy-out price once it is known. Record the rule next to the budget column so the figure can be audited.
Step by step
How to build the cost plan
-
Get the items into one sheet
One row per item with section, item reference, description, unit, quantity, rate and amount. Keep item references as text so 1.10 stays 1.10.
-
Design the code list
Use short, stable codes, for example 02 Groundworks, 03 Concrete frame and 07 Roofing, each with a package name and an owner. Keep codes as text so 02 does not become 2.
-
Map sections to codes
On a Map sheet list each bill section once with its code. Where a section spans two packages, map at item level with a code column on the Items sheet instead.
-
Add the code to every item
In a new column J use
=IFERROR(VLOOKUP(A2,Map!$A:$B,2,FALSE),"UNMAPPED")and copy it down. Filter for UNMAPPED and fix the map. -
Build the cost plan sheet
One row per code. The BOQ total is
=SUMIF(Items!$J:$J,A5,Items!$G:$G). Add columns for allowances, adjustments, the budget and its share of the total. -
Add the lines that are not measured
Preliminaries, overheads and profit, risk and provisional sums get their own rows, so they stay visible instead of being buried in trades. See preliminaries.
-
Prove that it ties
The cost plan total less the Items total is zero, every item has a code, and the item count matches the bill.
-
Review the largest codes first
Sort the cost plan by value and look at the top few codes. They usually carry most of the money, so check their mapping and their allowances before the small ones.
Reference
Workbook layout
| Sheet | Contents | Key formula |
|---|---|---|
| Items | One row per item, columns A to I as exported, plus cost_code in column J. | =IFERROR(VLOOKUP(A2,Map!$A:$B,2,FALSE),"UNMAPPED") |
| Map | Each bill section once, with its cost code and package. | Typed once; check there are no duplicate sections. |
| CostPlan | One row per code: BOQ total, allowances, adjustments, budget and share. | =SUMIF(Items!$J:$J,A5,Items!$G:$G) |
| Checks | Totals, item count and unmapped count. | =SUM(CostPlan!C:C)-SUM(Items!G:G) and =COUNTIF(Items!J:J,"UNMAPPED") |
Watch for
What goes wrong with BOQ cost plans
- Mapping by keyword. Matching descriptions to codes by words misses items. Map by section, or by an explicit code.
- Unmapped items. One item without a code leaves the plan short of the tender sum. Count UNMAPPED on the Checks sheet.
- Numbers stored as text. SUMIF ignores text that looks like a number. Convert the Amount column if a total comes out short.
- Sums counted twice. Provisional sums on the summary and in a trade section are counted twice if both are mapped.
- Patching after an addendum. Rebuild from the re-issued bill so quantities, rates and codes stay in step.
- Ranges that stop short. A formula over rows 2 to 500 misses row 501. Use whole-column references or an Excel table so added rows are included.
- Codes that change mid-way. Renumbering the code list after items are mapped breaks the lookups. Freeze the list before mapping, and add new codes at the end.
The tool
How BOQ Estimator helps
BOQ Estimator produces the first sheet for you. The Excel workbook has an Items sheet with one row per item and a Sections sheet with item count, amount sum, stated total and variance for each section, and blank amounts are Quantity x Rate formulas. Adding the cost code column, the Map and the cost plan is your own work in Excel; the tool does not assign codes. Try it on the BOQ to Excel converter. Free: 1 file per day, up to 10 pages, no account. Files over 10 pages are converted up to page 10 on the free allowance; a page code converts the whole file, up to 150 pages. See plans and limits.
Related
Related guides
FAQ
Questions estimators ask
What is the difference between a BOQ and a cost plan?
A BOQ lists measured work items for pricing, in the client's order. A cost plan allocates the money by element or package, in the order you buy and control the work. One is built from the other.
How do I link BOQ items to cost codes in Excel?
Keep a Map sheet of bill sections and cost codes, then look up each item's section in it with VLOOKUP, or INDEX and MATCH. Map at item level where one section spans several packages.
Should I use SUMIF or a PivotTable?
Either works. SUMIF formulas recalculate as rows change and are easy to audit. A PivotTable is quick to build but needs refreshing, so the cost plan can fall behind the Items sheet.
What section codes should I use?
Use the client's structure if one is prescribed, such as an elemental cost plan. Otherwise use short, stable codes of your own, stored as text, with a package name and an owner.
How do I treat preliminaries and provisional sums in the cost plan?
Give each its own rows. Preliminaries are priced from the programme and provisional sums are the client's figures, so neither should disappear into a trade total.
How do I keep the cost plan tied to the tender sum?
Add a Checks sheet with the difference between the cost plan total and the Items total, an unmapped count, and the item count against the bill. All three should be clean before you rely on it.
Can I use the same workbook after award?
As a starting budget, yes. Tracking commitments, forecasts and valuations is a further step and is outside this guide.