The broken-reference error that nearly mispriced the deepest pipe run: audit your rate calculator before tender day

Steve Parker
Jul 4
8 min read

Updated: Jul 9

A drainage subbie's rate calculator had a dead zone nobody knew about: every in-road rate deeper than 3 m returned a broken-reference error. Those bands covered about 185 m of large-diameter pipe — the single most expensive line in a subdivision tender. Caught in a pre-tender audit, the fix cost a day's work instead of a six-figure mispricing.

By Steve Parker · Trueworks · NZ construction estimation · 7 min

What you'll learn in this case study

How one deleted row silently broke every dependent rate — and stayed broken through multiple tenders
Why the broken bands covered the single most valuable line in the schedule, and what that concentration risk means
A pre-tender audit routine — error-scan, dummy-quantity tests, version control — that takes hours and protects the whole price

Quick answer: A drainage subcontractor pricing a large subdivision civils tender in South Auckland — measure and value, around 120 priceable lines — sent us their in-house pipe-lay rate calculator for an audit before tender day. Every rate in the "in-road, deeper than 3 m" bands returned a broken-reference error. A day-rate row had been deleted, every dependent formula broke silently, and nobody had priced from those bands since. Those bands covered the deepest road-corridor run in the tender — about 185 m of large-diameter pipe at 3–5 m deep, the single most expensive line in the schedule. We traced the precedent chain, rebuilt the crew day-rate build-up at about $5,500–6,000 per crew day, and re-tested every rate band with dummy quantities. The lay-only drainage subtotal came out around $200–220k, priced from a working calculator instead of a deadline guess.

The tender

A drainage subcontractor was pricing the civils drainage package on a large residential subdivision in South Auckland. The tender was measure and value against an engineer's schedule — around 120 priceable lines covering stormwater and wastewater reticulation, manholes, and connections. On a schedule that size, the rates do the pricing: get the rate build-ups right and the quantities look after themselves.

The subbie's rates come from an in-house calculator — a spreadsheet grown with the business over the years. It builds a lay rate per metre from first principles: a crew day rate (crew, excavator and operator, truck) divided by metres laid per day, varying by pipe size, depth band, and berm versus road corridor. In-road work is slower and dearer: traffic management, reinstatement, tighter compaction, and shoring at depth.

The engagement was a pre-tender audit of that calculator — not the tender itself, but the machine that would price it. All 120 lines would inherit rates from the workbook, so an error would not stay in one cell; it would propagate through the whole schedule.

Pricing off a rate or a rule of thumb? Get the numbers checked in writing before you submit — first check free. Send us the drawings and the quote or tender pack. We return a code-cited review packet within 24 hours. No charge for your first packet. NDA available, NZ-hosted processing. → Get the free check at trueworks.co.nz/contact — or email hello@trueworks.co.nz

Caught something similar on your job?

What we found

The error-scan turned it up within the hour: every rate in the "in-road, deeper than 3 m" bands returned a broken-reference error — the #REF result a spreadsheet gives when a formula points at a cell that no longer exists. Not one band — all of them, at every pipe size.

The root cause was a single deleted row. At some point — nobody could say when — a crew day-rate row had been removed from the build-up sheet. Every dependent formula broke at that moment, silently: a spreadsheet does not announce a broken reference, it just shows the error in cells nobody is looking at. The shallow and in-berm bands still worked, so the calculator looked healthy day to day. Nobody had priced an in-road run deeper than 3 m since the deletion, so the dead zone sat through multiple tenders, waiting.

Mapping the broken bands against the tender schedule turned a housekeeping find into a material one. The deepest run in the job — about 185 m of large-diameter pipe in the road corridor at 3–5 m deep — fell squarely inside them. It was the single most expensive line in the schedule: deep excavation, shoring, imported bedding, slow productivity, full reinstatement. The calculator literally could not price the most valuable work in the tender.

Without the audit the ending is predictable: under deadline pressure a broken rate becomes a guessed rate — someone's memory of an old job — or a number transplanted from a shallower band. Either way, the most expensive line in the job carries the least reliable rate in the submission, on a measure-and-value contract where that rate then governs payment for the whole run.

Trueworks runs quote-checks, tender pricing packs, and risk registers for NZ trades and subcontractors — code-cited, in writing, priced per job. Get your first quote check →

Want this kind of review on every job?

How we repaired it — and how to audit your own calculator

Trace the precedent chain before touching anything. We followed the broken formulas back to the missing row: the crew day-rate build-up for the deep in-road configuration. Repair means restoring the logic, not making the error disappear — overtyping a broken formula with a number hides the problem and breaks the audit trail.

Rebuild the day rate from first principles. Crew, excavator and operator, truck, plus the deep-trench additions: shoring allowance, dewatering contingency, traffic management attendance. The rebuilt figure — about $5,500–6,000 per crew day — reconciled with the subbie's cost records from recent jobs.

Re-test every band with dummy quantities — not just the broken ones. We ran a nominal quantity through every pipe size, depth, and location combination and eyeballed each result: rates should rise with depth, rise again in-road, and rise with diameter. Any band that breaks the pattern gets its build-up opened. Most estimators test only the bands the current job needs — exactly how a dead zone survives.

Recalculate and error-scan as a gate. Force a full recalculation, then scan the whole workbook for error values — broken references, division errors, lookup failures — before any rate leaves the file. Minutes of work, catching the class of fault visual review never will.

Version the calculator and log changes. The deletion had no date, no author, no recorded reason. A dated copy per tender plus a one-line change log turns "sometime in the last few years" into "row removed in March, here's why" — and makes the next audit an hour instead of a day.

With the bands rebuilt and tested, the schedule priced cleanly: a lay-only subtotal around $200–220k, and a deep-run rate the subbie could defend line by line.

What it costs when it's caught late

| Stage caught | Cost range | Why | |---|---|---| | At tender (pre-submission audit) | ~$1–3k | A day of audit and repair work; every rate submitted is a working rate | | Post-tender, pre-award | ~$10–30k | Re-pricing under a tender query, with the engineer now examining every other rate in the schedule | | Post-award | ~$40–80k | A guessed deep-lay rate is now the contract rate for the whole run; measure and value pays the error out metre by metre | | On site | ~$80–150k | Shoring, dewatering, and slow deep-trench productivity cost what they cost, while revenue is fixed at the broken rate | | In dispute | ~$150k+ | Arguing rate review with thin records, plus professional fees, on a rate your workbook never actually calculated |

Five checks before tender day

Force a full recalculation and error-scan every sheet — not just the summary — before any rate goes into a tender.
Test every rate band with a dummy quantity, and check the pattern: deeper and in-road should always cost more than shallow and in-berm.
Map the schedule's biggest-value lines to their source bands and open those build-ups by hand — concentration deserves scrutiny.
Version the calculator per tender and keep a one-line change log — every row added, deleted, or re-rated, with a date and a reason.
Never overtype a broken formula with a number — trace the precedent chain, restore the build-up, and re-test the band.

FAQ — rate calculator audits

Q1: What actually causes a broken-reference error in a rate calculator? A formula pointing at a cell, row, or sheet that has been deleted. The spreadsheet cannot resolve the reference, so it returns an error value instead of a number. The error shows only in dependent cells; if nobody prices from those bands, it goes unseen indefinitely.

Q2: Wouldn't we notice a broken rate when we price the job? Only if the job touches the broken band and someone looks at the cell. Under deadline pressure the likelier outcome is a guessed substitute or a value copied from a neighbouring band — a plausible number with nothing underneath it, worse than a visible error.

Q3: How long does a proper pre-tender calculator audit take? For a workbook of this kind, a few hours to a day: error-scan, precedent tracing on anything suspect, dummy-quantity tests across all bands, and reconciliation of crew day rates against recent cost records. The effort scales with the workbook, not the tender — it pays for itself fastest on big jobs.

Q4: Why did the broken bands matter so much on this particular tender? Concentration. The "in-road, deeper than 3 m" bands covered about 185 m of large-diameter pipe — the single most expensive line in a roughly $200–220k lay-only subtotal. On measure and value the tendered rate governs payment for every metre of the run, so an error in one band does not average out; it multiplies.

Q5: What is the minimum version-control discipline worth having? A dated, read-only copy of the calculator saved with each tender, plus a running change log — one line per change: date, what moved, why. That answers "what did we price this from?" months later, and shows that a row deleted in one cycle is why a band misbehaves in the next.

Who this helps

Trueworks is the analyst layer under your pricing decision — it works alongside your QS or your own numbers, not instead of them. If one of these sounds like your desk, start with the page written for you:

Get a second pair of eyes on your next quote or tender

Drawings + your quote or the tender pack = a code-cited review packet within 24 hours, ready before you commit.

No charge for your first packet. No commitment. NDA available. Files NZ-hosted, deleted after 30 days unless you ask us to retain them.

→ Get the free check at trueworks.co.nz/contact — or email hello@trueworks.co.nz

About Trueworks

Trueworks is built by Steve Parker — 20 years on the analytical side of NZ construction. Variation reviews, contract advisory, programme review, and document-heavy estimation work. Trueworks is the productisation of that practice for NZ trades and builders: the same defensible analysis, at a price and pace a working contractor can actually use.

Every report is checked and signed off by me personally before it goes out. If you have a quote or tender you want a second opinion on, the easiest way to find out if Trueworks is useful is to send it.

hello@trueworks.co.nz · trueworks.co.nz