WAYPOINT
Case study

Where numbers lose their lineage

Three and a half years inside one capital project’s forecast at completion. Six failures, the single defect underneath all of them, and the phased plan of action that puts the forecast on a governed spine — without changing the report its reader demands.

Joe Roberts·WayPoint Controls, LLC·~14 minute read
The situation

One workbook, four fiscal years

Every month a forecast at completion goes to a chief executive, and from him to a board and to the market. It is assembled in one spreadsheet, maintained across four fiscal years by more than one pair of hands — some of them no longer at the company.

The numbers in it are not wrong, exactly. That would be easier; wrong numbers get found. The problem is quieter and worse: for most of them, nobody can say where they came from.

What follows is not a thought experiment. Every failure below was found in real work, on a live project, by someone who then had to explain it to the person whose name is on the report. The client, the project and the dollars that would identify them are omitted. The mechanisms are not — because the mechanisms are the point, and if you run capital work in a spreadsheet, you have at least three of them right now.

One thing worth saying plainly at the start. The account structure in that workbook is not a data model — it is a presentation contract, inherited from the executive who reads it. Anyone who has consulted knows this shape: you can be entirely right about the model and still lose the client. A better structure was presented early on. It nearly ended the engagement. The format is the deliverable, and arguing with it is a losing move.

Hold that thought. It turns out to be the key to the solution rather than an obstacle to it.

Findings

Six failures

Presented as they were encountered: what it looked like, what it actually was, and what it cost. Each carries a reference used again in the plan of action below.

F1

A value pasted over a formula

What it looked like

A reconciliation tab, quietly doing its job for years.

What it was

Two cells that should have read from period sheets held typed numbers instead. Someone had pasted values over the formulas — and the pasted figures did not line up with the labels beside them.

What it cost

Roughly $2.3 million of movement in project-to-date cost, invisible for months. Nobody was careless; a spreadsheet renders a derived number and a typed one identically. There is no visual difference, no warning, no audit trail. Prior periods never reconciled against finance, and eventually we stopped trying — freezing the basis at a known date and carrying forward from the figure the chief executive had already given the board. That freeze was the right call. It should never have been necessary.

F2

A change that overwrote another

What it looked like

A clean rebuild of the period’s approved changes.

What it was

Two separate approved changes landed on the same forecast line. The second wrote over the first rather than accumulating with it.

What it cost

About $560,000, gone. The write succeeded. Nothing flagged, nothing errored, the page still footed. We caught it only because we had built an independent verifier that recomputed the sheet from the change list and compared — the spreadsheet itself had no opinion.

F3

Subtotals nested inside subtotals

What it looked like

A section total, and beneath it the detail that made it up.

What it was

One section subtotal spanned a range containing two further subtotals — one re-summarising territory the other already covered. Excel’s SUBTOTAL function ignores nested subtotals, so the column using it stayed honest. The adjacent committed and uncommitted columns were maintained by hand.

What it cost

Anyone summing the visible section lines double-counts by an enormous margin. More quietly: the hand-maintained split had drifted from the detail underneath it, so the total was right while the split was not. A right total hides a wrong answer better than a wrong total ever could.

F4

Contingency used as the plug

What it looked like

A contingency line, expressed as a percentage, on every issue.

What it was

The project total was fixed — politically, and in what had already been said publicly. Contingency was defined as the total minus everything else. It was not a provision. It was the balancing figure that made the page foot.

What it cost

Every scope growth silently consumed risk cover, and nobody could see it happening, because the line was defined to absorb whatever it had to. Ask “how much cover do we have against the risks we have identified?” and the honest answer is that the number on the page cannot tell you — it is an output of arithmetic, not an assessment of risk.

F5

Quotes with nowhere to live

What it looked like

An inbox, and a folder of PDFs.

What it was

Nearly $3 million of live vendor pricing for real, needed scope — none of it committed, none of it in the forecast, none of it anywhere a reader of the forecast would find it. One carried a fifteen-day validity, tracked in somebody’s head.

What it cost

A forecast that omits priced, imminent scope is not conservative. It is wrong in a direction that flatters. And a quote whose expiry lives in a human memory is a commercial exposure with no owner.

F6

A basis you cannot find

What it looked like

A vendor quote that came back high against the forecast.

What it was

To judge it we needed the estimate basis. That basis sat on a numbered tab of an older, superseded workbook. When we finally dug it out, the story inverted: the vendor’s unit rate was 23% below our basis. The overage was almost entirely a large quantity of lean concrete infill the basis had never priced.

What it cost

We were within an hour of negotiating a price that was not the problem. A pricing problem and a scope-omission problem look identical from the outside, and they have opposite remedies — one is a conversation with a vendor, the other is a change to your own estimate. You cannot tell them apart without the basis, and the basis was archaeology.

Root cause

Every one of them succeeded.

That is the part worth sitting with. The paste succeeded. The second change succeeded. The sum succeeded. Contingency footed. The quote sat quietly in an inbox. Not one of these was an error condition. Every one passed its own test.

They share a single defect wearing six costumes: an operation that looks successful while quietly discarding something. Which is possible because a spreadsheet has no concept of provenance. A cell holds a value. Whether that value was calculated, typed, pasted over a formula, or inherited from somebody who left two years ago is not recorded and cannot be recovered.

Excel is not a bad tool. It is a superb one, and it is why any of this was possible at all. But it was never built to answer the question that governs capital reporting: where did this number come from?

Before any software

What we did first, by hand

We did not start by buying a system. We imposed the discipline manually, on the live report, under a deadline — the only honest way to learn whether a discipline survives contact with real work. Four moves, in order:

  1. 1

    The change list became the record

    Every change for the period became an entry in one governed file — basis, source, dollar effect, explicit disposition. Not a note in a cell. An entry.

  2. 2

    The workbook became an output

    The spreadsheet stopped being the thing we edited and became the thing we generated — same format, to the cell. You cannot paste over a formula in a file you no longer type into.

  3. 3

    An independent verifier ran on every build

    Nearly a thousand assertions, recomputing the sheet from the change list and comparing. This is what caught the $560,000 overwrite that manual review had passed.

  4. 4

    A quarter’s cost was proved three ways

    Coded entries, ledger debits less credits, and the sum of period subtotals — three independent routes landing on the same figure, with the reconciliation difference against finance holding constant across the quarter.

That last one matters more than it reads. It was the first time in years the number could be defended rather than asserted. Not because anyone had been sloppy — because until then there had been nowhere to stand.

Plan of action

Eight phases from spreadsheet to governed spine

Each phase names the failures it closes, what actually changes, and — most importantly — the test that proves it worked. A remedy with no acceptance test is an intention. Phases run in order because each rests on the one before, and every phase leaves the report shippable: nothing here requires a big-bang cutover in the middle of a reporting cycle.

Phase 0

Stop editing the report. Start generating it.

Closes F1, F2Proven in the field
What changes

The period’s changes become entries in one governed list — each with basis, source, dollar effect and an explicit disposition. The workbook stops being the thing you edit and becomes the thing you generate from that list, reproducing the inherited format to the cell. An independent verifier recomputes the sheet from the list on every build and refuses to ship on a mismatch.

How you know it worked

Run it against a period you have already issued and confirm it reproduces your own numbers. On this project the verifier carried nearly a thousand assertions — and caught the $560,000 overwrite that every manual review had passed.

Phase 1

Adopt the project as it actually is.

Closes F1Built
What changes

Stand the project up on the spine mid-flight: actual cost and change history, with no invented baseline. Where the basis was frozen at a known date, it is recorded as customer-declared — stamped as declared rather than derived, with no fabricated estimate class dressed up to look rigorous.

How you know it worked

The declared basis appears on every account it touches, and the forecast reconciles to the figure already communicated publicly. Nothing is quietly restated behind the executive’s back.

Phase 2

Make cost a ledger, not a cell.

Closes F1, F2Built
What changes

Actual cost enters as dated ledger entries through a single write path; account totals are recomputed from them. A correction supersedes its predecessor with a stamp rather than editing it, so the original fact survives beside the correction. There is no cell to paste over, because there is no cell.

How you know it worked

Post a period, then re-post the same figures: the ledger absorbs the repeat without double-counting. Change one entry and the account total moves — with the entry that moved it named.

Phase 3

One change register, and nothing but.

Closes F2, F3Built
What changes

All budget change originates in one register. Changes accumulate as rows, so a second change against an account is a second row and can never overwrite the first. Every other module identifies the moment a change happens and hands you into the register — none of them grows its own private change form.

How you know it worked

The F2 test, run directly: two approved changes against one account in one period. Both must survive, and the account must show their sum with both entries traceable.

Phase 4

Derive committed and uncommitted from the awards.

Closes F3Built
What changes

Committed cost is derived from the procurement register; uncommitted is budget less committed. Neither is ever typed, so the split cannot drift from the awards that justify it. Where the ledger and the register disagree, the disagreement is displayed rather than absorbed. Totals are computed from detail, so a nested subtotal cannot double-count.

How you know it worked

Award a package and watch committed rise and uncommitted fall by the same amount, with no hand entry. Then force a mismatch on purpose and confirm the drift is reported, not swallowed.

Phase 5

Give quotes a home, and an expiry.

Closes F5, F6Built
What changes

Bids live in the procurement register alongside awards, tagged for what they are — firm, budgetary, quoted or judgment. Quoted amounts carry their validity date and age out of it, so a stale quote stops presenting as money. Award supersedes the quote it came from automatically. Each package carries the estimate basis it is judged against.

How you know it worked

Every dollar of live vendor pricing appears in the forecast with its nature and its expiry visible. The F6 question — is this quote high on price, or is it scope we never carried? — is answerable on screen instead of by excavation.

Phase 6

Reconcile contingency instead of plugging it.

Closes F4Built
What changes

Approved contingency, drawn to date, open exposure against identified risks, and remaining cover become four separate, visible figures. Draws pass through an approval that writes a change with its own basis. Contingency is never defined as the difference that makes a total foot.

How you know it worked

Scope growth now consumes cover visibly, on a line that says so. If cover is being spent on growth rather than risk, that becomes a conversation in the month it happens — not a discovery two quarters later.

Phase 7

Render the executive’s format from the spine.

Closes F1 – F6The remaining work
What changes

The last mile, and the one that decides adoption: the governed spine generates the inherited report exactly as its reader expects it — awful account structure, hidden backup tabs, roll-forward columns and all. Governance underneath; the reader’s format on top, unchanged.

How you know it worked

The executive receives the same workbook they have always received and notices nothing. Every number in it can now be traced to an entry, an owner and a basis. That is the whole objective, stated as a single test.

Why Phase 7 is the one that decides everything

Most reporting systems fail in the field not because their model is wrong, but because they require the person receiving the report to change how they read it. That person is usually the one paying for the system, and they are entitled to say no.

So the hard version was proven first, by hand, before any of it was built: a governed change list generating that exact inherited workbook — awful account structure, hidden tabs, roll-forward columns and all — down to the cell. The reader’s format is an output, never an input. Governance underneath, their report on top, unchanged. Those two things are not in tension. Believing they were is what kept this problem alive for years.

And projects arrive mid-flight

A platform that only accepts clean projects — born inside it, with a defensible baseline — is useless to most of the work that needs help. Real projects get adopted halfway through, carrying a cost position and no baseline anyone can defend. WayPoint takes that case directly (Phase 1): it accepts a customer-declared basis and stamps it as exactly that, with no invented estimate class dressed up to look rigorous. Honest beats tidy. A frozen basis recorded as frozen is worth far more than a clean-looking number nobody can trace.

Honestly

What this plan does not fix

A case study that only lists what it solves is a sales sheet. Here is the other half.

  • It does not fix data quality upstream. If the finance system’s reconciliation is corrupted, a platform can flag that cost moved without a corresponding entry — it cannot repair a workbook it does not own.
  • A frozen basis stays frozen. Nothing reconstructs periods that were never reconciled. The honest move is to record the freeze, name it as declared rather than derived, and stop pretending otherwise.
  • One user is not a platform. The value compounds when the people who originate numbers enter them, rather than one person reverse-engineering them from PDFs at nine at night. That is an organisational question, not a software one.
  • Migration is real work. Years of history, dozens of accounts, prior-period columns. It is not free and should not be sold as free.
  • The executive still wants the spreadsheet. Probably permanently. Plan for that as a standing requirement, not a transitional one.

The honest summary is narrower than the brochure version, and more useful: this does not make capital reporting easy. It makes it auditable. Those are different claims, and the second is the one worth paying for when the number goes to a board.

Try this

A one-minute test on your own forecast

Open the report you send upward. Pick any number on it — not a hard one, any one. Now answer: where did it come from, who owns it, when did it last change, and on what basis?

Time yourself. If the answer takes more than a minute, or ends in “I’d have to ask someone,” you have the problem described above. You simply have not been billed for it yet.

That bill arrives late, and it arrives in public — in a board meeting, in a reconciliation that will not close, in a contingency line that turns out to have been spent two quarters ago.

Talk through your own forecastHow we hold the spine

Have a project that needs its numbers to hold?

Thirty minutes, in your project’s language. No deck.

Start a conversation

Or email admin@waypoint-controls.com