Why Spreadsheets Lie About Cost Per Unit

You calculate the cost per unit at each site. You average them across the company. You present the number to leadership. And it’s wrong.

Not a rounding error. Not a data entry mistake. The math itself is structurally broken, and spreadsheets can’t warn you because they don’t know the difference between additive metrics and ratio metrics. Nobody catches it until the error compounds across months and sites, and suddenly, your cost baseline is six figures off reality.

You can’t spreadsheet-formula your way out of this one. It’s a modelling problem that requires you to understand the metric you’re actually measuring.

The Hidden Flaw in Averaging Ratios

Most finance teams correctly calculate the company-level cost per unit from GL totals. Where the error creeps in is operational reporting: the dashboards, the rolling baselines, the multi-metric comparisons that operations teams build for themselves. That’s where ratios get averaged instead of recalculated, because it’s faster and the numbers look close enough.

Two sites. Simple numbers. Watch what happens.

The wrong way (averaging the ratios):

($10.00 + $11.00) ÷ 2 = $10.50 / tonne

The right way (recalculating from totals):

$160,000 ÷ 15,000 = $10.67 / tonne

That’s a $0.17/tonne understatement. On 15,000 tonnes, it’s $2,500 in phantom savings that never existed.

Now scale it up. Four sites instead of two, monthly rolling reporting, six key metrics (cost per unit, energy intensity, yield, OEE, scrap rate, labour hours per unit). Each metric compounds the error independently, and each one requires different aggregation logic. The errors don’t cancel. They stack. If a single metric on two sites produces a $2,500 error per month, six metrics across four sites over a rolling eight-month baseline can put you in six-figure territory. And nobody can locate the problem, because the error lives in the model, not in the data.

Your data is clean. Your formulas are correct. The model is broken.

Why Spreadsheets Can’t Catch This

Spreadsheets are additive by design. They sum rows, average columns, and treat all metrics as if they follow the same mathematical rules.

Cost per unit doesn’t work that way. Neither does yield percentage, energy intensity, OEE, or labour productivity. These are ratio metrics, and they only make sense when you recalculate them from their component parts at every level of aggregation.

When moving from site-level to plant-level to company-level reporting, a ratio metric must be recalculated from the underlying totals. You can’t just average it. Averaging breaks the math.

Here’s the maddening part: a spreadsheet has no concept of metric type. It sees a column of numbers and trusts that you know what you’re doing. If you write a formula that averages cost-per-unit values, it won’t flag it. It’ll calculate it, hand you a number, and move on. And because that number is close to reality (not wildly off, just quietly wrong), nobody questions it until it’s baked into a forecast or a contract negotiation or a year-end close.

Then the error becomes institutional. It propagates into downstream systems. Someone uses that number as a starting point for the next calculation, and the distortion compounds again.

The Three Modelling Problems

This is really three problems tangled together. You can solve each of them in a spreadsheet if you’re careful, but nothing in the spreadsheet enforces the correct approach. The discipline lives entirely in the head of whoever built the sheet.

Organisation Aggregation

You have sites, plants, divisions, regions, and the whole company. Each level needs the correct aggregated value. But the correct value at the Plant level isn’t the average of the correct values at the Site level. It’s the sum of the component totals, with the ratio recalculated. A spreadsheet has no way to track which metrics can be averaged, which need totalling, and which need component-based recalculation at each hierarchy level.

Time Aggregation

Daily yield percentage isn’t the average of hourly yield percentages. Weekly energy intensity isn’t the average of daily values. Monthly labour hours per unit aren’t the average of weekly labour hours per unit. Each time period aggregates differently depending on what the metric actually measures. Design a spreadsheet for daily reporting, and you’ll produce phantom metrics the moment you roll it forward to weekly, then monthly, then quarterly. The errors accumulate every time.

Formula Dependency

This one’s subtle. Cost per unit is usually Total Cost ÷ Total Units. But Total Cost might be the sum of labour, material, and overhead allocation. And overhead allocation might itself be derived from another ratio metric. Change the overhead calculation, and the cost per unit changes with it. In a spreadsheet, you have to manually trace dependencies to understand what breaks when something changes. There’s no audit trail and no version history. If someone updates a formula without touching the dependent calculations? Silent failure. The spreadsheet keeps producing numbers, but they’re built on outdated logic.

What a Correct Model Actually Does

The fix isn’t a better spreadsheet. It’s a data model that understands metric types and automatically enforces the correct aggregation rules.

A proper operational model does four things that spreadsheets simply can’t do without manual intervention at every step.

First, it classifies metrics by type. Every metric is either additive (summed across sites or time periods) or a ratio (recalculated from components). The model knows the difference and applies the right aggregation rule at every level. You don’t have to think about it. When you aggregate cost per unit from site to plant level, the system sums the site-level total costs and total units, then calculates the plant-level cost per unit from those sums. It doesn’t average. It never averages ratios.

Second, it explicitly maintains the organisational hierarchy. Sites belong to Plants, Plants to Divisions, Divisions to the Company. The model knows the structure. If a Site is added or removed, the hierarchy updates, and the aggregation logic stays intact, because the hierarchy isn’t maintained in a spreadsheet cell. It’s part of the data structure itself.

Third, it preserves formula versioning and audit trails. When someone changes how overhead is allocated to costs, the system captures the change, timestamps it, and marks which metrics are affected. You can see which cost-per-unit values were calculated under the old logic and which under the new. You can roll back. In a spreadsheet, you lose this completely. Someone changes a cell, and six months later, nobody remembers whether that number was always that way.

Fourth, it separates data capture from calculation. You capture raw totals (actual cost, actual units produced) at the site and period levels where they’re measured. The system calculates all ratio metrics at all hierarchy levels from there. When you need the company-level cost per unit for Q2, you don’t build a formula. You ask for it. One definition of what cost per unit means, not dozens of slightly different spreadsheet interpretations scattered across the business.

What This Looks Like in Practice

We’ve seen teams spend weeks trying to reconcile company cost-per-unit numbers against the GL, only to discover that the “error” wasn’t in the data at all. It was in the aggregation. The site-level numbers were fine. The company-level numbers looked reasonable. But they’d been averaged instead of recalculated, and that small distortion had been compounding quietly for months, embedded in forecasts and comparisons and board reports. Fixing it meant rerunning three months of analysis.

With a proper model, that reconciliation problem disappears. The company-level number is always derived from the underlying totals. The variance month-over-month is real, not an artifact of how someone averaged the numbers. And when you need to explain why the cost per unit changed, you can drill down to the component totals that actually drove it.

That’s the difference: a number you can trust versus a number that happens to be close.

Why This Matters Now

Companies used to tolerate this. When material price swings and labour rate changes were moving the P&L by 10%, a 1-2% aggregation error was noise. You could get away with sloppy aggregation because larger forces drowned it out.

That’s not where most manufacturers are today.

Margins are tight. The difference between $10.50/tonne and $10.67/tonne can be the difference between breaking even and losing money on a contract. When you’re competing on operational efficiency, a six-figure phantom cost variance isn’t noise. It’s a bad decision waiting to happen.

The problem also scales badly. What was manageable with a spreadsheet at four sites becomes unmanageable at twelve. The error surface grows faster than you can manually validate it.

And if the error is material, there’s a governance question too. When your operational cost reports feed into financials or regulatory filings, the aggregation methodology matters. If an auditor asks how the cost per unit is calculated at the company level versus the site level, “we average the site numbers, which is technically wrong, but it’s close enough” is not a comfortable answer. It might not trigger a formal finding on its own, but it’s the kind of undocumented methodological shortcut that erodes confidence when it surfaces.

The Path Forward

The brute-force option is to hire more analysts and add more spreadsheet checks. More formulas, more manual validation steps. That scales slowly and eventually stops scaling altogether because human validation requires more people, leading to diminishing returns.

BI tools like Power BI or Tableau can help with presentation, but they don’t solve the underlying problem unless someone configures the correct aggregation logic for each metric type. Same with ERP modules: they can enforce rules, but only if the rules are defined correctly in the first place.

The real fix is a proper data model. Define which metrics are additive and which are ratios. Specify the correct aggregation logic for each hierarchy level and time period. Separate data capture from calculation. Maintain formula versioning to audit what changed and when.

This isn’t a technology problem. It’s a modelling problem. The model needs to live in an environment that fosters metric-awareness, whether that’s a well-governed data warehouse or a purpose-built operational platform. Spreadsheets don’t cut it because they have no concept of metric type. You can build the right formulas, but nothing stops the next person from averaging what should be recalculated.

NxGN Capstone was built for exactly this: once you classify your metrics by type at setup, it maintains the organisational hierarchy, recalculates ratios at every aggregation level, and preserves formula versioning with audit trails. Learn more → | Talk to our team →