How to audit your trading spreadsheet in an afternoon: the 5 faults that break the margin

If you run your deals in a spreadsheet, there are five faults that show up again and again and that share one trait: they do not break the file. No #REF!, no #VALUE!. The sheet keeps returning a respectable-looking number, and that number ends up in the monthly report, in the conversation with the bank, and in the decision to repeat a route that was actually losing money.

The five are: ranges that fall short when you insert rows, exchange rates fixed by hand, forgotten commissions, estimated freight that was never updated and overhead that is never allocated. Below you have what each one is, the exact check to catch it in your own file and what it costs when it slips through. One afternoon and your last twenty closed deals is all the audit takes.

Before you start: set the audit up in 20 minutes

Do not audit the whole file. Take a sample and check it against the documents: the invoice for each cost, and the bank statement to confirm it was paid. The order matters — deal, invoice, payment — because the bank alone will not tell you which deal a movement belongs to: you will see payments bundled across five operations, advances, later credit notes and booked invoices that have not left the account yet. The invoice says what it cost; the bank says it was settled.

1. 1Take the last 20 deals closed and collected. Closed, not in progress: you need the final invoice for every cost. Twenty is a first screen, not a full audit: if a systemic fault shows up, extend the sample back to the date the template or the process changed.
2. 2Copy the file and work on the copy. You are about to touch formulas and you do not want to do that on the original.
3. 3Add five new columns on the right: Sheet GP, Recalculated GP, Error € (Sheet GP − Recalculated GP), Error over sales (Error € / Sales) and Cause. Measure the error over sales, not over GP: when a deal’s margin is close to zero, dividing by it returns three-digit percentages that mean nothing.
4. 4For each deal, rebuild the margin from scratch with the real invoices in hand. If you would rather not build the formula, use the calculator at the end of the article: enter quantity, prices and cost lines and it gives you GP, GP% and GP per tonne.
5. 5Sort by Error € in absolute value, largest first. The top three will tell you the whole story.

1. The range that fell short when a row was inserted

It is the most common fault and the quietest. The sheet adds the cost lines with something like =SUM(D5:D16). One day you add a new line — an inspection, a congestion surcharge — and insert it below the last one, in row 17. Excel does not always extend the range, and the sum still stops at row 16. That cost exists, it gets paid, it shows on screen and it is not in the total.

The real fix is not watching the range: it is making it impossible for the range to fall short. Select the block of cost lines and press Ctrl+T to turn it into an Excel Table. The sum is then written like this:

=SUM(CostTable[Amount])

The reference grows on its own as rows are added: no range to update.

If you would rather not rebuild the template right now, Excel already warns you: when a formula leaves out adjacent cells that contain numbers, it flags the cell with a green triangle and the message “Formula omits adjacent cells”. Almost everyone has it switched off or ignores it. Turn it back on under File → Options → Formulas and go through the warnings in your twenty deals. Between the Table and that warning, this fault almost stops appearing: it is the easiest of the five to shut down.

2. The exchange rate somebody fixed by hand

This is the one that moves the most money, by a distance. You buy in dollars and sell in euros, or the other way round, and at some point somebody typed the exchange rate straight into a cell to close the month. That cell was copied into the next deal, and the next, and it is still there.

A container of in-shell walnuts, 24 tonnes, bought in dollars and sold in euros. EUR/USD 1.08 means one euro is 1.08 dollars, so the dollar purchase is divided by the rate:

The margin looks 53% higher than it is, and not a single number is mistyped: the sale is right, the purchase is right, the formula is right. Only the exchange rate is eight months old. If that cell was dragged across the forty containers of the year, that €5,179 per container adds up to more than €200,000 of margin that never existed. That is why, when you find one, you need to check the rest.

The check: find every FX cell in your sheet that is a number typed by hand. In Excel, Find & Select → Go To Special → Constants → Numbers on the exchange rate column marks them all. A hand-typed rate is not an error in itself: freezing the rate actually applied to a closed deal is good practice, and better than leaving it hanging from a table that updates on its own. The problem is the rate with no date and no source, because then nobody can say whether it is the right one. Add four columns — FX applied, FX date, FX source and Type (spot, pricing, hedge or settlement) — and recalculate only the deals you cannot justify.

3. The commissions nobody put in the sheet

Buying and selling commissions are agreed by phone, settled by bank transfer weeks later and booked in the accounts, not in the deal sheet. The result: the spreadsheet margin is the margin before commissions, but it is read as if it were the final one.

With a $25/t selling commission and a $20/t buying commission on 24 tonnes, that is $1,080 per container. In the example above that is around 10% of the gross margin, every month, on every deal.

The check is not in the sheet, it is in the settlements: ask each broker for the detail of what was charged on your twenty deals and match it deal by deal against the commissions column in your spreadsheet. Do not compare totals by quarter: because commissions are paid weeks later, one quarter’s payments belong to the previous quarter’s deals and that subtraction never adds up, whether or not you have a problem. The bank statement confirms that the settlement was paid, not which deal it belongs to.

4. The freight that was estimated once and stayed there

Ocean freight is budgeted when you quote, months before shipping, and it is one of the most volatile cost lines in the deal. When the forwarder’s invoice arrives with the real amount, almost nobody goes back to the deal sheet to correct the estimate: the deal is already “closed” in everyone’s head.

The symptom is easy to spot: if the freight on several different deals in your sheet is exactly the same round figure, suspect a copied estimate. It may not be one — with a fixed-rate contract per route that repetition is correct — but it deserves a check against the invoice.

The check: add a Real freight column next to the freight one, fill it in from the forwarder’s invoices for those twenty deals and calculate the deviation.

=IF(Estimated_freight=0,"",(Real_freight-Estimated_freight)/Estimated_freight)

Sort largest first and look at the top five.

That same column serves the next thing you will want to do: keep a historical average per route and per port, which is the only way to tell whether a specific invoice has gone off-price or whether it is the market that moved.

5. Overhead that is not allocated to any deal

Office, salaries, software, general insurance. They do not belong to any specific deal, so they do not appear in any deal sheet. The gross margin per operation comes out fine, and yet the year closes worse than the sheets added together said it would.

There is no formula error here: it is a difference between things that have similar names. The commercial spread is the sale minus the purchase. GP, or gross margin, is what is left after also subtracting the deal’s own direct costs: freight, commissions, fees, insurance. And EBIT also subtracts the overhead — office, salaries, software — that no deal sheet captures. If your overhead costs €18,000 a month and you close eight containers, each one carries €2,250 of capacity cost: 23% of that €9,689 spread.

The check: take the monthly fixed spend from your income statement, divide it by the number of operations that month and subtract it from each one’s margin. Splitting it equally is a first approximation, not an accounting truth: a container with an inspection and two claims does not use the same overhead as one that ships without a hitch. When you want to refine it, allocate by tonnes, by turnover or by days of capital employed. And watch the interest on the credit line: it is a financing cost, it sits below EBIT, and it is better allocated as capital employed × annual cost × days / 365 than equally.

What to do with the result

When you finish you will have your twenty deals sorted by deviation. What you do next depends on what you see:

• If the fault repeats across all deals (typical of the exchange rate and the commissions), it is a design problem in the sheet. You fix it once, in the template, and recalculate the history.
• If the fault appears only on specific deals (typical of the broken range and the freight), it is a process problem. You need a control point: nobody closes a deal without the forwarder’s real invoice loaded.
• If the average error is material, tell whoever makes decisions with that number before touching anything else. Look at two figures, not one: the mean absolute error — the average of ABS(error) — tells you whether the process is reliable, and the bias — the average of the signed error — tells you which way it fails. A signed average of zero can hide a +20% and a −20%. Here the bias tends to come out positive, and not by chance: all five faults omit or understate costs, so they all push the margin up.

And one rule worth more than the five checks put together: always keep the estimated cost and the real cost in two separate columns. Most trading spreadsheets have a single cost column that gets overwritten, which is why they can never close the loop or learn from what happened. With two columns, the audit stops being an afternoon of work and becomes an automatic subtraction.

Method and limits of this article

The five families of fault come from reviewing real export spreadsheets for agricultural goods between LATAM and Europe, with the usual structure of the sector: one tab per deal, purchase and sale prices, and between ten and fifteen cost lines with different bases (per container, per tonne and per shipment).

The figures in the article are a reproducible example, not a market average: you can redo every calculation with the numbers in the tables and get the same result. The prices and costs are typical orders of magnitude for in-shell walnuts in a 24-tonne container, not a quotation. When we publish aggregate error percentages, it will be with a declared sample and a published method; with a handful of customers, any percentage would be armchair statistics.