The margin in your spreadsheet on the day you close a deal is a budgeted margin: sale price minus purchase price minus the costs you estimated. The one that reaches your account weeks later is the settled margin: what the customer actually paid minus what you were actually invoiced. At almost no trading house do those two numbers match, and in most spreadsheets only the first one exists.
The gap is not noise. In the example below —a quinoa container with no disaster at all, just the ordinary deviations of a shipment— GP drops from 3,120 to 1,560 dollars: half. Here is the line-by-line account, the five leaks it goes through, and a way to reconcile your closed deals in an afternoon.
Two margins with the same name
The budgeted margin is for deciding: whether the deal is worth it, what price to offer, how much risk to take. The settled margin is for learning: which routes, forwarders and customers really make money. The trouble starts when the first is used for the second. If the sheet is never updated after closing, the year-end report adds up margins nobody collected, and the "most profitable" route may simply be the one that was budgeted worst.
One container, line by line
Twenty tonnes of quinoa, bought FOB at $1,700/t and sold CIF at $2,000/t. Everything in dollars so the maths can be followed without an exchange rate. The figures are a constructed example, rounded so you can check them with a calculator, but every deviation is one you will see on any shipment.
| Line | Budgeted | Settled | Difference |
|---|---|---|---|
| Sales | 40,000 | 39,640 | −360 |
| Quality claim | 0 | −400 | −400 |
| Purchase | −34,000 | −34,000 | 0 |
| Ocean freight | −1,800 | −1,950 | −150 |
| Origin charges | −600 | −720 | −120 |
| Insurance | −80 | −80 | 0 |
| Documents and certificates | −250 | −310 | −60 |
| Bank charges | −150 | −260 | −110 |
| Demurrage | — | −360 | −360 |
| GP | 3,120 (7.8%) | 1,560 (4.0%) | −1,560 |
No line is outrageous. A bunker surcharge on the freight, an extra inspection at origin, documents sent twice, a correspondent bank fee, three days of demurrage, 0.9% weight loss and a 1% moisture discount. Each one on its own gets forgotten. Together they take half the profit, and the sheet still says 3,120.
Settled GP = Sale price × Landed weight
- Agreed claims
- Purchase price × Purchased weight
- Σ Invoiced costs (actual, not estimated)The five leaks between budget and payment
1. Costs invoiced differently from how they were estimated
The freight you were quoted six weeks ago is not the freight the forwarder invoices: bunker, peak-season or congestion surcharges that the quote left out, or that changed along the way. Origin charges behave the same. The deviation almost always runs in the same direction, so it does not cancel out across shipments: it piles up.
2. Lines that were never in the budget
Demurrage, a repeated phytosanitary inspection or an urgent courier are not in the deal because nobody expected them. When the invoice arrives it has no line to go to, and in many sheets it ends up in an "overheads" tab that is never allocated back to a deal. The deal looks better than it was and overheads look worse.
3. The weight that arrives is not the weight that left
You buy and pay on origin weight; if the sales contract settles on destination weight, you get paid on what arrives. In grains and pulses, half a point to a point of loss from moisture or handling is normal. In the example that is 180 kg: 360 dollars that no sheet built on "quantity × sale price" will ever show.
4. Quality claims
A destination analysis showing one point more moisture or impurities than the contract allows opens a claim. It gets negotiated, a discount is agreed and deducted from the payment. Because it arrives weeks after closing, by email, it rarely makes it back to the deal row.
5. The money itself: bank, timing and exchange rate
Charges on a documentary collection or from a correspondent bank depend on the customer’s bank, not yours, and are deducted from what you receive. If you also sell in one currency and keep your books in another, the exchange rate moves between the day you fix the price and the day you are paid. That effect is real, but it is worth measuring apart from the commercial margin so you do not mix what the trader did with what the market did.
How to reconcile your closed deals in an afternoon
You do not need a new system to start. You need one more column per cost and the habit of filling it in. The last ten settled deals are enough to see the pattern:
- Gather the real invoices for each deal: forwarder, origin agent, shipping line, bank, and the credit note or email for every agreed claim.
- Next to each estimated cost, write the invoiced one. Do not overwrite the estimate: the difference between the two is exactly what you want to learn.
- Add the lines that did not exist (demurrage, inspections, couriers) instead of sending them to overheads.
- Recalculate sales with the invoiced weight and price, and subtract agreed claims.
- Compare budgeted and settled GP and sort the deals by the difference. The top five tell you where to look.
What Granel does with this, and what it does not
In Granel every cost line on a deal keeps two amounts: the estimate at closing and the actual at settlement. The settlement tab shows them side by side with the difference per line, adjusts sales for landed weight and deducts agreed claims, so settled GP comes from the same calculation as the budgeted one and the two cannot drift apart. Wherever you have entered the actual amount, analytics and exports use it instead of the estimate.
What Granel does not do is read your invoices: you, or someone on your team, enter the actual amount when it arrives. If nobody does, the deal keeps showing the estimate, just like your sheet. The difference is that the column already exists and the gap is visible.
With or without a tool, the conclusion is the same: you do not know a deal’s margin on the day it closes, you know it on the day it settles. As long as your sheet only holds the first, you are measuring your ability to budget, not your business.