Companion files
Letters of Credit, Collections and Receivables Finance, Priced on One Export Order
These are the four Excel workbooks that go with the book. Harrowgate Instruments ships 4,200,000 of process instrumentation to Meridian Process Holdings, and the operating margin before any finance cost is 306,600, being 7.3 per cent of the price. Hold the buyer’s payment period at 90 days so that the instrument is the only thing that moves, and six routes to payment cost between 178,040 and 223,268: a spread of 45,228, which is 14.8 per cent of the whole operating margin. Every figure the book prints is reproduced here by a live formula rather than a typed constant. Change the cost of goods sold, the days of credit granted, the discrepancy rate or your own cost of funds, and every dependent number moves. Each workbook ends with a Checks sheet setting the printed figure beside the computed one: 555 checks in all, every one passing on delivery. If a check ever reads FAIL, the workbook is wrong, not the book.
Free to download. No sign-up, no email address, nothing to fill in.
Everything described below is inside it, with the read-me.
Chapters 1 to 3
The order’s economics, and all six routes priced at held terms. The five-line build-up from 4,200,000 through 3,423,000 of cost of goods sold to a gross margin of 777,000, then 470,400 of selling, general and administrative cost carried by the order, and the 306,600 that is what the order is actually worth before a dollar of finance. Every route finances the cost rather than the price, so move the cost of goods sold and the financing column moves on all six rows at once.
Then the calendar, built out of its parts rather than asserted: 70 days of production, 26 in transit, 9 to presentation, and only then the buyer’s payment period. The sheet ends on the column every comparison needs and almost none carries, the days of buyer credit measured from the bill of lading, which is 90.0 on four routes, 106.0 on documents against acceptance and 110.8 on an unconfirmed credit held to maturity, because those extra days are the instrument’s own machinery and not a longer term conceded to anybody. With the terms held at 90 days the six rows run from 178,040 on open account with credit insurance to 223,268 on a non-recourse receivables purchase, and margin after finance from 128,560 down to 83,332. A seventh sheet runs the same machinery with each instrument at the terms it is naturally quoted with, where documentary collection lands at 102,902 and a sight credit at 112,089, so that the reason the table everyone builds flatters the short instruments can be read rather than asserted.
Chapters 6, 7 and 8
The documentary credit priced in every form it is sold in, and the risk it removes set against the risk it introduces. The fee schedule prices the credit’s own charges with their minimums live, so an order small enough for the 425 examination minimum or the 1,500 confirmation minimum to bite shows it rather than hiding it inside a percentage. Three columns then carry the sight credit, the unconfirmed usance credit held to maturity and the confirmed usance credit discounted at presentation, each with its days to cash, its fee stack and its expected loss, which is the comparison the book argues is three instruments and not three variants of one.
The sheet worth the download on its own is the document risk. Over the same 136.8 days of exposure, the probability that a presentation is refused and the applicant does not waive is 3.7200 per cent, against 1.1833 per cent for the issuing bank and the country combined: 3.1 times. The workbook prints that ratio with the two expected losses locked to the cell beside it, 2,162 against 30,813, because the document risk is three times more likely and an order of magnitude less costly, and the ratio quoted alone overstates the case. Confirmation is then solved rather than argued: the issuing-bank default probability at which the 26,979 fee exactly equals the 28,166 of loss removed is 1.2172 per cent, against the 1.35 per cent the model assumes, so at these inputs confirmation is bought below its actuarial price. Each figure carries the period it is measured over on the same row, because the two are not measured over the same one. The rate then runs from 0.00 to 4.50 per cent a year, with a note on the top row saying what it does not mean: at a rate of nothing the minimum still applies and the credit is still confirmed.
Chapters 4, 5, 9, 10 and 11
A bank discount is computed on the face and earned on the proceeds, so the quoted rate is never the cost. The same 6.90 per cent is taken to three tenors with the discount, the proceeds and the equivalent yield at each: 72,450 and 7.0211 per cent at 90 days, 96,600 and 7.0624 at 120, 144,900 and 7.1466 at 180, with the gap widening from 12.1 to 24.7 basis points. The next sheet is the one that surprises people: the exporter’s own facility at 8.4 per cent on a cost of 3,423,000 costs 71,883 over 90 days, and a discount at 6.90 per cent on a price of 4,200,000 costs 72,450. The cheaper rate is the dearer arrangement by 567, because a facility funds the cost and a discount funds the price. The break-even discount rate is solved rather than searched for: your cost of funds multiplied by your cost-to-price ratio, 81.5 per cent on this order, giving 6.8460 per cent.
Then the three routes that are not a credit. Open account and insurance prices the policy in full, the 26,040 premium, the 90.0 per cent cover and the uncovered tenth, which takes the expected loss from 34,416 to 3,442 and the total from 182,974 to 178,040, the cheapest of the six. Collections prices both the payment and the acceptance instruction and exposes the judgment inputs behind them as blue cells rather than burying them in formulas. Receivables purchase separates what is sold from what stays: commission at 0.55 per cent and discount at 7.60 per cent on one side, dilution at 2.25 per cent and 34.0 per cent on the other, and the exposures that survive the word non-recourse, which is why the fastest route to cash at 109.0 days is also the dearest at 223,268.
Chapters 12 to 16
This one is the one to open first, and it does not reproduce the book. Its front sheet is a calculator you drive with your own order. Type in your contract value, your cost of goods sold, your overhead on the order, your production, transit and presentation days, your cost of funds and the terms you are about to offer, and it returns two figures: what one day of buyer credit costs you, and the tenor at which your margin after finance reaches zero. On the Meridian order those answers are 1,031.76 a day, being the 185,716 span of the terms table divided by the 180 days it runs over, and the 192nd day on a confirmed discounted credit. That is the argument of the book in two numbers, and they can both be computed before the quotation leaves the building.
Around the calculator sit the tables it comes from. The terms run from zero to 180 days with the instrument held still, which is the rule that makes it a stress test and not a re-pricing: 108,543 of cost at sight against 294,258 at 180 days, and margin after finance from 198,057 down to 12,342. Both break-evens are solved on a bracket that tests the root is interior, because a solver that runs out of room returns the edge of its range and an edge looks exactly like an answer: 192 days on a confirmed discounted credit and 204 on open account. The premium table gives what each length of credit must carry in price to pay for itself, seven rows from 0.74 per cent at 30 days, through 2.27 at 90, to 9.73 per cent at 360. Case B is the order actually won on 180 days at a price premium of 1.5 per cent: 63,000 collected against 125,704 of margin destroyed, leaving 36.5 per cent of the sight margin, on terms that needed 4.64 per cent and were sold for one and a half, a shortfall of 3.14 percentage points. All seven sensitivities follow, each moving one input, with the ranking by span at the foot: 185,716 for the days of credit granted, which is a line in the quotation, down to 9,583 for the discrepancy rate.
It ships filled in with the Meridian order, so the Checks sheet can prove it reproduces the book before you trust it with anything of your own. Overtype the blue cells with your own figures and those checks will fail. That is the workbook working, not breaking.
| Blue text | an input: change it and everything recomputes |
| Yellow fill | an assumption that decides the answer rather than merely feeding it. In the fourth workbook every input cell carries the fill, because on an order that has not been quoted yet every one of them is an assumption somebody is making |
| Black text | a formula: do not overtype these |
| Grey text | a note |
| Green text | on the Checks sheet, a link to the computed cell |
| Checks sheet | the printed figure beside the computed one, the difference, a PASS or a FAIL, and the tolerance being applied |
There are no macros, no external links, no protection and no circular references anywhere, and nothing is locked or watermarked. The files behave identically in Excel, LibreOffice and Google Sheets.
A workbook that agrees with a book proves nothing on its own, since the author wrote both. What the Checks sheets do is different: they force the model to reproduce a number that was printed before the model existed, from a formula rather than from the number itself. 555 checks across the four files, being 169, 97, 95 and 194, all passing on delivery.
On this book the discipline earns its keep on the identity that catches a discount subtracted twice. Six of the checks in the first workbook do nothing but assert that on every route, margin after finance equals 306,600 less the total cost. A discount is easy to charge once in the fee column and again in the financing column, and the row still looks plausible because both entries belong there; the identity is what refuses it.
The second thing the checks enforce is that the figures which look like each other’s complements are not. The 28,166 of expected loss that confirmation removes is measured over the 125 days a confirmation runs on 90-day terms. The 30,813 attributed to bank and country is measured over the 136.8-day exposure of an unconfirmed credit. Subtracting the confirmed row’s 1,652 from 30,813 gives 29,161, which is a third number and belongs to neither. Every one of those figures carries its period on the same row, and a check holds it there.
Where an input is a judgment rather than a measurement, it is a blue cell and it is named as one. The five behind the collection and purchase routes are judgments, and no sensitivity table in the book stresses them, which is a gap and is printed as one. The assumption most worth arguing with is the discrepancy rate of 62.0 per cent on first presentation, which sits at the severe end of the range a documentary desk would recognize. Type 30 instead, or 80, and watch what moves and what does not: the span of that whole table is 9,583, the smallest of the seven, and the reason why is the point of the chapter.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers. They use no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks to update links on opening, decline, because there are none.
The other books with companion files. The full list of titles is on the author page.