The deal you thought was a winner that closed at a loss

A deal rarely loses money because of a disaster. It loses money because of eight small things that happen one after another, none of them big enough for anyone to raise a hand. In this article’s example, two containers of sesame leave the sheet with a GP of €7,800 (10.8 % of sales) and settle at −€1,212. The largest variance, a quality claim, takes €2,992: less than half the margin. The other seven do the rest.

Below is the deal rebuilt in the order it happened, with the date on which each thing could have been known, and the method for doing the same with a deal of your own: the bridge from budgeted margin to settled margin. The most useful part is not the final figure; it is seeing that by day 90 the deal was already at zero and the sheet went on saying €7,800 until day 145.

The deal on paper

A trader based in Spain buys 38 tonnes of sesame FOB at origin, in two containers, at $1,650 a tonne, and sells them delivered to a European customer’s warehouse at €1,900 a tonne. The supplier is paid at loading and the customer pays on credit terms. The budget uses a euro at 1.10 dollars. The figures are a constructed, rounded example, not a real deal or a quotation: they are there so you can follow the sum and redo it with yours.

It is a better sheet than most: it has certificates, insurance and bank charges, which many do not. It is missing the lines that do not happen every time —customs inspection, demurrage, a reserve for claims— and the cost of the money advanced. All of that counts in what follows.

What happened, in order

Eight things, none of them extraordinary. In brackets, what each one took out of the margin.

1. 1Day 20: an analysis has to be repeated. The first sample comes out at the limit of the specification and the inspector repeats the analysis. The certificates invoice goes from €400 to €550 (−€150).
2. 2Day 25: paying for the purchase. The contract was agreed with the euro at 1.10 dollars; on the day the $62,700 is paid it stands at 1.07. The purchase costs €58,598 instead of €57,000 (−€1,598).
3. 3Day 28: the freight invoice. The carrier applied a surcharge that was not in the quotation. Freight comes to €2,550 per container: €5,100 instead of €4,400 (−€700).
4. 4Days 62 to 71: arrival and inspection. The containers arrive on day 62 and customs sends the consignment for physical examination. Opening and repositioning the consignment costs €600 (€300 per container), and while it is sorted out both containers use up their five free days and pay demurrage from the sixth to the tenth: 2 × 5 days × €60 = €600. Destination charges go from €1,400 to €2,600 (−€1,200).
5. 5Day 74: the weight that arrives. The customer’s weighbridge shows 37.4 tonnes, not 38: a 1.6 % shortfall, above the half a point to one point usually seen. The contract invoices on landed weight, so sales drop to €71,060 (−€1,140).
6. 6Day 90: the claim. The analysis at destination shows more impurities than the contract allows. After two weeks of emails a discount of €80 a tonne on the 37.4 t is agreed (−€2,992).
7. 7Day 115: the bank statement. The payment due today does not arrive and, going through the charges for the documentary collection on the purchase and for the transfers, they come to €260 rather than the €200 average (−€60).
8. 8Day 145: getting paid. The customer was due to pay on day 115 and pays on day 145. Between paying for the purchase and collecting, 120 days have passed with €58,598 advanced: at 6 % a year, €1,172 of financing cost that the sheet did not have (−€1,172).

Insurance and delivery to the customer came in as budgeted. Added together, the eight variances are €9,012, and the margin was €7,800.

The bridge from budgeted to settled margin

A bridge is the same list laid out as a subtraction: it starts at the margin you budgeted, takes off each variance with its cause and ends at the margin you collected. If the last line does not match what the bank says, there is a variance still to be found.

Settled GP = Sales collected − Actual costs

Sales collected = 37.4 t × €1,900 − €2,992 = €68,068
Actual costs    = 58,598 + 5,100 + 2,600 + 900 + 550 + 100 + 260 + 1,172 = €69,280
Settled GP      = 68,068 − 69,280 = −€1,212

Bridge          = 7,800 − 9,012 = −€1,212

Both sums give the same result, and that is the proof that the bridge is complete. If they do not match, the difference is an invoice you have not seen or a payment that came in short.

What the right-hand column teaches

1. No single variance killed the deal

The largest is the claim: €2,992, 38 % of the margin. The second, the exchange rate, 20 %. Looking for "the" mistake in a deal that closed badly is nearly always a waste of time: usually it is several medium-sized things, which is why nobody saw them coming. A 10.8 % margin looks comfortable until you add up everything that can move.

2. By day 90 the deal was already at zero

On day 28, with the purchase paid and the freight invoiced, the margin was already €5,352: almost a third lower, with the goods still at sea. On day 90 it was €20. The sheet went on saying €7,800 until someone reconciled it after payment came in, on day 145. During those weeks, anyone looking at that sheet to decide the next deal with the same supplier, the same customer or the same route was deciding with a number that no longer existed.

3. Several could still be fixed when they became known

Not all of them: a surcharge already invoiced and a shortfall already weighed are seldom recovered in full. But the exchange rate on the purchase could have been fixed on day 0 with a forward contract from the bank, at a rate close to 1.10 (the FX gap article explains it); the demurrage could have been cut by lodging the customs entry before arrival and negotiating more free days when booking (which dates to watch); and a margin of €3,012 on day 74 is a fact that changes how you negotiate a claim and how hard you chase a late payment.

How much a deal can take before you sign it

The same sum can be done the other way round, before signing: what one unit of each thing that can move costs. With this deal’s figures:

Read this way, the table says how much of each thing fits inside the margin: €7,800 is thirteen US cents (a euro at 0.97: $62,700 ÷ €64,800), or a 10.8 % weight shortfall, or a discount of €205 a tonne. Taken one at a time, that looks like a lot. Together, the three in the example —three cents, a 1.6 % shortfall and €80 a tonne— already add up to €5,730, and no logistics invoice has arrived yet. If you want to run the exchange-rate sum with your own figures, the FX risk calculator does it for you.

How to rebuild one of your own deals in an hour

1. 1Recover the budget as it stood on the day the deal was agreed, not today’s sheet. If the sheet has been corrected over the top, look for the version you emailed or the offer to the customer.
2. 2Gather everything that was invoiced and collected: invoices from the supplier, the forwarder, the customs agent and the inspector, bank statements, the final invoice to the customer and any credit notes.
3. 3Match each budget line with its actual amount and note the difference. Anything that had no line goes in a new row: do not bury it inside another one to make the sum work.
4. 4Give each difference the date on which it could have been known —the date of the invoice or the email, not today’s— and sort by that date. That is the running-margin column.
5. 5Give each one a cause, with few categories and always the same ones: price, weight, quality, logistics, exchange rate, bank and payment term.
6. 6Check that the bridge closes: budgeted margin minus variances equals sales collected minus costs paid.

With one deal you have an anecdote. With ten, added up by cause, you have a pattern: if quality takes 3 % of sales from one origin and nothing from another, or if a customer’s real payment term is always thirty days longer than the contract says, that is no longer a variance but a cost to budget for. How to turn it into cost lines is in the 9 costs that are not in your spreadsheet.

What Granel does with this, and what it does not

In Granel, half of this bridge is a subtraction that is already done. Every cost line keeps the estimated amount and the actual one, and the settlement tab shows them side by side with the difference per line. That tab also adjusts sales for landed weight once every container has both weights entered, shipped and landed (unless the customer is already claiming the shortfall), and applies agreed claims; customer claims that are still open count as a provision. Wherever you have entered an actual amount, analytics uses it instead of the estimate, without waiting for the whole deal to be settled.

What Granel does not do, and it is worth knowing before you try it (the general list is in what it does and does not do): it does not read your invoices or your bank statements —AI capture reads negotiation messages to create the deal, not cost documents— so you enter the actual amount, and if you do not, the deal goes on showing the estimate, just like your sheet. The settlement tab opens once the deal is delivered: an invoice that arrives while the goods are at sea only shows earlier if you correct the line’s estimate, and then you lose that line’s original budget, which stays in the audit log and not on the tab. Lines that were not in the budget, such as demurrage or the financing cost, are added by editing the deal before you mark it as settled; Granel does not calculate that financing cost, it goes in with the amount you have worked out. The purchase price has no actual column, and the deal has a single exchange rate for both purchase and sale: if you paid for the purchase at a different rate, add the difference as a cost line. The weight adjustment and claims count in the settled GP on that tab, not in analytics. And although on the Growth and Enterprise plans a daily check flags an open deal whose margin, with the costs entered so far, turns negative, or whose freight runs more than 40 % above the route average, it does not sort variances by date or assign them a cause: the bridge is a method, and deciding why each thing happened is still your job.

Frequently asked questions

Why can a deal with a positive margin close at a loss?

Because the margin on the sheet is a budget, worked out with the expected price, weight, costs and exchange rate. Between agreeing the deal and getting paid several of them change at once: the rate on the day you pay, the freight invoiced, the landed weight, a claim, the days until you are paid. It is seldom a single cause; in the example there are eight and the largest is less than half the margin.

How do you reconcile the actual costs of an import or export deal?

Match each budget line with what was actually invoiced or paid, note the difference with its cause and its date, and check that budgeted margin minus variances equals sales collected minus costs paid. Anything that had no line in the budget gets a row of its own.

What is a margin bridge?

A table that starts at the budgeted margin and arrives at the settled margin by subtracting each variance separately: exchange rate, freight, weight, quality, bank, payment term. It shows how much each cause weighs and, if you sort the variances by date, at what point the deal stopped making money.

When should you update the margin of a deal?

Every time an actual figure arrives: the purchase payment, each invoice, the weight at destination, an agreed claim, the customer’s payment. Waiting for final settlement has a cost: in the meantime, decisions about the next deals are taken with the budgeted margin.