Landed cost in a spreadsheet: when it works and when it breaks

What spreadsheets are genuinely good at

I want to be fair to the spreadsheet, because most importers cost their shipments in one and many of them are right to. Excel and Google Sheets are extraordinary tools. They are free or nearly free, they run everywhere, everyone can read them, and they impose no structure on you at all. For a single container of one product, with a duty rate your broker has confirmed and a tax rate you know, a spreadsheet will give you a correct landed cost in ten minutes.

That last property — imposing no structure — is exactly what makes them so useful early and so treacherous later. A spreadsheet will let you write anything in any cell. It has no opinion about whether a levy belongs in the tax base. It will not stop you allocating freight evenly across three products of wildly different value. It cannot tell you that the rate you typed in March was changed in the June budget.

What the audit research actually found

This is one of the few areas of business software where there is a real research literature, and it is not reassuring. The European Spreadsheet Risk Interest Group summarises the position bluntly: the majority of spreadsheets contain errors, and roughly half of the models actively used in large organisations have material defects. The reason given is simple and hard to argue with — spreadsheets are rarely tested.

The underlying field audits are worth reading directly. Panko’s review of the evidence, Spreadsheet Errors: What We Know, What We Think We Can Do, collects audits of operational spreadsheets in real use. The earlier studies, using weaker methods, found errors in 11 to 21 per cent of the sheets they examined. The later and more rigorous audits found errors in at least 86 per cent. Where auditors counted errors per cell rather than per file, the rates ran from about 0.4 per cent to 2.5 per cent of formula cells.

Those cell rates sound small, and that is the trap. Errors do not need to be frequent to be near-certain, because a model has many cells. A landed cost sheet for a three-product container — charge rules, allocation, tax base, per-unit maths, a pricing block — will carry a couple of hundred formula cells without feeling large. Applying the published audit rates to two hundred formula cells, the chance that at least one is wrong runs from roughly 55 per cent at the lowest observed rate to above 99 per cent at the highest.

That arithmetic is an illustration, not a measurement of import costing specifically. No one has audited a representative sample of importers’ landed cost sheets. But the direction is clear enough, and it matches what the consequences look like when they surface. EuSpRIG maintains a catalogue of documented cases where spreadsheet errors caused real financial loss, including a Canadian power company that lost about 24 million dollars in 2003 to a cut-and-paste error in a bidding spreadsheet. These are not people who were bad at Excel. They were people whose spreadsheet had no way to catch them.

The four places landed cost specifically breaks

Generic spreadsheet risk is one thing. What matters here is that import costing has four structural properties that spreadsheets model badly. This is not Excel being poor software; it is a genuine mismatch between the shape of the problem and the shape of the tool.

1. Charges are rules, not numbers

An importer’s charges are not a column of figures. Each one is a rule with its own base, method, and often a floor or a ceiling. A destination inspection fee of “1.2 per cent of CIF or US$190, whichever is higher” behaves completely differently on a small shipment than a large one. An excise charged per litre behaves differently again as volume moves, which is the subject of the guide on ad valorem versus specific excise methods.

In a spreadsheet, that rule becomes a formula in a cell — and the rule itself, the thing you would need to check against the tariff next quarter, exists only in the author’s memory. Six months later nobody can tell whether the minimum was applied because the rule says so or because someone typed a number.

2. The tax base is a decision, and it is invisible

Import VAT or GST is almost never charged on the supplier invoice. It is charged on a defined base, typically the customs value plus duty plus certain levies — and which levies are inside that base is a country-specific decision, covered in the guide on how the import VAT or GST base is assembled.

In a spreadsheet this decision disappears into a SUM range. Whether the trade levy sits inside the tax base is expressed as whether one cell reference made it into the parentheses. That is a consequential policy choice recorded as a typing accident, and it is invisible to anyone reviewing the file, including you.

3. Allocation across products changes who is profitable

The moment a container holds more than one product, shared costs have to be split, and the basis you choose changes which product looks worth importing. Splitting freight evenly flatters cheap heavy goods and punishes expensive light ones. Splitting by purchase value does the reverse. The guide on allocating freight across multiple products works the same three-product container through four different bases and the answers genuinely differ.

Spreadsheets can do this. What they do badly is let you change your mind — switching the allocation basis usually means rebuilding the sheet, so in practice people pick one early and never test another.

4. Rounding, applied in the wrong order

This one is quiet and it is my favourite, because it catches careful people. If you compute a landed cost per unit, round it to cents, and then multiply back by the unit count to get a total, the rounding error is multiplied by the number of units. On a shipment of 1,800 units, a fraction of a cent per unit becomes a visible discrepancy in the total, and the report stops reconciling: revenue minus landed cost minus selling costs no longer equals the profit line.

The rule is to carry full precision through the calculation and round once, at the end, for display — and to treat the total as authoritative and the per-unit figure as derived, never the other way round. Spreadsheets make the wrong order the natural one, because you build a per-unit column and then sum it. Anyone who has had a finance reviewer ask why a total is four dollars out has met this.

Spreadsheet or dedicated tool: an honest comparison

Stay in the spreadsheet if this describes you

There is no virtue in buying software you do not need. A spreadsheet remains the correct answer when you import one or two products at a time, your duty and tax rates are stable and confirmed, you are the only person who uses the file, and nothing downstream depends on the number being auditable. Add a dated note beside every rate recording where it came from, and you have covered the largest single risk at no cost.

If you do stay, fix these five things first

• Write each charge as a rule in its own row: base, method, rate, minimum, maximum. Not as a bare formula.
• Make the tax base explicit. A visible list of what is inside it beats a SUM range nobody can audit.
• Record where every rate came from and the date you checked it, next to the rate itself.
• Carry full precision and round once at the end. Treat the total as authoritative and per-unit as derived.
• After clearance, put the actual invoices beside your estimate and keep the difference. That variance is the most useful number you will produce all year.

The full sequence those five points sit inside is written out in the step-by-step landed cost method, and you can run a single shipment through the free landed cost calculator without signing in to check your sheet against something built for the job.

Where TrueCost fits

TrueCost exists because the four problems above are structural rather than a matter of being careful. Charges are stored as rules with their own bases and limits. The tax base is an explicit setting rather than a SUM range. Allocation basis is a switch, so you can see how the answer moves. Totals are carried at full precision and rounded once, so the report reconciles. HS codes never imply a duty rate — you record the tariff line, the source and the date, and if those are missing the report says so.

It does not publish live duty rates, and it will not tell you what your rate is. Every figure is one you entered or verified. If you want to see what that produces before deciding anything, the read-only demo workspace and the sample report are both public and filled with illustrative figures. TrueCost Pro adds saved multi-product projects, allocation across lines, reconciliation against actuals and CSV or PDF export.

The takeaway

The question is not whether spreadsheets are good software. It is whether your costing problem has outgrown a tool that will not hold a rule, will not record a source, and cannot tell you when it is wrong. For one shipment of one product, it has not. By the time a mixed container, a tax base you are unsure about and a partner who wants to see your working are all in play, it has — and the research suggests the errors are already there, quietly, whether or not anyone has found them yet.

All figures and rates in this note are illustrative estimates. The error statistics are drawn from the published spreadsheet-audit literature linked above and describe spreadsheets in general, not import costing specifically. This article explains general method; it is not customs, tax, accounting or legal advice. Confirm classification, rates and tax bases with the destination customs authority or a licensed clearing agent before you commit to a purchase or a price. Our calculation methodology and editorial policy explains how we produce and review these figures, and how to tell us about a correction.