TRADE FINANCE - BLANK MODEL SET: THE BOOK'S OWN INPUTS

Type these back into the blank files and the book's results come back. Cell references are sheet!cell.
Day-count conventions (360 and 365), tenor ladders, sensitivity axes and bisection bounds are kept in the blank files.

The_Order_and_the_Six_Routes_Blank.xlsx

   Inputs!C6                 4200000       Contract value
   Inputs!C7                 0.815         Cost of goods sold, share of the price
   Inputs!C8                 0.112         Selling, general and administrative, share of the price
   Inputs!C9                 70            Production, order to ship-ready, days
   Inputs!C10                26            Transit, port to port and inland, days
   Inputs!C11                9             Shipment to presentation of documents, days
   Inputs!C12                0.084         Exporter's own cost of funds, a year
   Inputs!C15                0.041         Buyer one-year probability of default
   Inputs!C16                0.62          Loss given default on an unsecured trade claim
   Inputs!C17                0.0135        Issuing bank one-year probability of default
   Inputs!C18                0.018         Country transfer and convertibility event, one year
   Inputs!C21                350           Advising fee, flat
   Inputs!C22                0.00125       Document examination, share of the drawing
   Inputs!C23                425           Document examination, minimum
   Inputs!C24                0.0185        Confirmation, a year
   Inputs!C25                1500          Confirmation, minimum
   Inputs!C26                0.069         Discount, a year
   Inputs!C27                0.0009        Collection fee, share of the bill
   Inputs!C28                260           Collection fee, minimum
   Inputs!C29                0.076         Receivables purchase discount, a year
   Inputs!C30                0.0055        Receivables purchase commission, share of the invoice
   Inputs!C31                0.0062        Credit insurance premium, share of the invoice
   Inputs!C32                0.9           Credit insurance cover, share of a loss
   Inputs!C33                1180          Couriers, legalisation and certificates
   Inputs!C34                240           Amendment fee, and there is always one
   Inputs!C37                7             Collection cycle, presentation to remittance
   Inputs!C38                5             Handling under a credit, examination to settlement
   Inputs!C39                4             Settlement of a receivables purchase after documents
   Inputs!C42                0.62          First presentations refused on a discrepancy
   Inputs!C43                0.94          Of those refused, share the applicant waives
   Inputs!C44                11            Days lost per correction cycle
   Inputs!C45                1             Correction cycles expected on a refusal
   Inputs!C49                1.45          Refusal multiple on D/P, against the buyer's default probability
   Inputs!C50                0.29          Loss on a refused D/P consignment, share of contract value
   Inputs!C51                0.9           D/A expected loss as a share of the open-account loss
   Inputs!C52                0.0225        Dispute rate on a purchased receivable
   Inputs!C53                0.34          Loss on a disputed receivable, share of contract value
   Inputs!C54                60            Extra days of buyer exposure on an unwaived refusal
   Inputs!C57                90            Days of buyer credit, HELD across all six routes
   Inputs!C58                120           Days of buyer credit on the confirmed credit at its natural term

   Result: six routes at 90 days from 178,040 to 223,268, a spread of 45,228; the naive table spreads 129,456.

The_Credit_and_the_Documents_Blank.xlsx

   Inputs!C6                 4200000       Contract value
   Inputs!C7                 0.815         Cost of goods sold, per cent of the price
   Inputs!C8                 0.112         Selling, general and administrative, per cent
   Inputs!C9                 0.084         Exporter's own cost of funds a year
   Inputs!C12                70            Production, order to ship-ready
   Inputs!C13                26            Transit, port to port and inland
   Inputs!C14                9             Shipment to presentation of documents
   Inputs!C15                5             Handling under a credit, examination to settlement
   Inputs!C16                90            Buyer credit period, days
   Inputs!C17                120           Buyer credit period for the sensitivity table, days
   Inputs!C20                350           Advising fee, flat
   Inputs!C21                0.00125       Document examination, per cent of the drawing
   Inputs!C22                425           Document examination, minimum
   Inputs!C23                0.0185        Confirmation, per cent a year
   Inputs!C24                1500          Confirmation, minimum
   Inputs!C25                0.069         Discount, per cent a year
   Inputs!C26                1180          Couriers, legalisation and certificates
   Inputs!C27                240           Amendment fee, and there is always one
   Inputs!C30                0.041         Buyer one-year probability of default
   Inputs!C31                0.62          Loss given default on an unsecured trade claim
   Inputs!C32                0.0135        Issuing bank one-year probability of default
   Inputs!C33                0.018         Country transfer and convertibility event, one year
   Inputs!C36                0.62          First presentations refused on a discrepancy
   Inputs!C37                0.94          Of those refused, the share the applicant waives
   Inputs!C38                11            Days lost per correction cycle
   Inputs!C39                1             Correction cycles expected on a refusal
   Inputs!C40                60            Extra days of buyer exposure on an unwaived refusal
   Inputs!C43                0             Sensitivity rate, row 1
   Inputs!C44                0.01          Sensitivity rate, row 2
   Inputs!C45                0.03          Sensitivity rate, row 4
   Inputs!C46                0.045         Sensitivity rate, row 5
   Inputs!C49                360           Day count for money, actual/360
   Inputs!C50                365           Day count for probability annualisation

   Result: document risk 3.7200 per cent against 1.1833, expected losses 2,162 and 30,813; break-even issuing-bank probability 1.2172 per cent.

The_Cost_of_the_Money_Blank.xlsx

   Inputs!C7                 4200000       Contract value
   Inputs!C8                 0.815         Cost of goods sold, per cent of the price
   Inputs!C12                0.112         Selling, general and administrative, per cent of the price
   Inputs!C15                0.084         Exporter's own cost of funds, a year
   Inputs!C18                70            Production, order to ship-ready
   Inputs!C19                26            Transit, port to port and inland
   Inputs!C20                9             Shipment to presentation of documents
   Inputs!C21                7             Collection cycle, presentation to remittance
   Inputs!C22                4             Receivables purchase settlement, presentation to funds
   Inputs!C23                90            Buyer credit period, stated term
   Inputs!C24                360           Money day count basis
   Inputs!C25                365           Probability annualisation basis
   Inputs!C28                0.041         Buyer one-year probability of default
   Inputs!C29                0.62          Loss given default on an unsecured trade claim
   Inputs!C30                0.0135        Issuing bank one-year probability of default
   Inputs!C31                0.018         Country transfer and convertibility event, one year
   Inputs!C34                0.0062        Credit insurance premium, per cent of the invoice
   Inputs!C35                0.9           Credit insurance cover, share of a loss the policy pays
   Inputs!C36                0.0009        Collection fee, per cent of the invoice
   Inputs!C37                260           Collection fee, minimum
   Inputs!C38                1180          Couriers, legalisation and certificates
   Inputs!C39                0.076         Receivables purchase discount, a year
   Inputs!C40                0.0055        Receivables purchase commission, per cent of the face
   Inputs!C41                0.069         Confirming bank's discount rate, a year
   Inputs!C44                1.45          Refusal to take up documents under D/P, multiple of the buyer's defaul
   Inputs!C45                0.29          Loss on a refused D/P consignment sold under duress
   Inputs!C46                0.9           D/A expected loss as a share of the open-account loss
   Inputs!C47                0.0225        Dispute rate on a purchased receivable
   Inputs!C48                0.34          Loss on a disputed receivable
   Inputs!C51                90            Short tenor, days
   Inputs!C52                120           Middle tenor, days
   Inputs!C53                180           Long tenor, days

   Result: 7.0211 / 7.0624 / 7.1466 per cent at 90 / 120 / 180 days; the discount dearer by 567; break-even rate 6.8460 per cent.

What_a_Payment_Term_Costs_Blank.xlsx

   Quote Calculator!C7       4200000       Contract value
   Quote Calculator!C8       0.815         Cost of goods sold, per cent of the price
   Quote Calculator!C9       0.112         Selling, general and administrative, per cent of the price
   Quote Calculator!C10      0.084         Your own cost of funds, a year
   Quote Calculator!C11      70            Production, order to ship-ready, days
   Quote Calculator!C12      26            Transit, port to port and inland, days
   Quote Calculator!C13      9             Shipment to presentation of documents, days
   Quote Calculator!C16      120           Days of buyer credit
   Quote Calculator!C19      0.0185        Confirmation rate a year
   Quote Calculator!C20      1500          Confirmation minimum
   Quote Calculator!C21      0.069         Discount rate a year
   Quote Calculator!C22      350           Advising fee, flat
   Quote Calculator!C23      0.00125       Document examination, per cent of the drawing
   Quote Calculator!C24      425           Document examination minimum
   Quote Calculator!C25      1180          Couriers, legalisation and certificates
   Quote Calculator!C26      240           Amendment fee, and there is always one
   Quote Calculator!C27      5             Bank handling, examination to settlement, days
   Quote Calculator!C30      0.62          First presentations refused on a discrepancy
   Quote Calculator!C31      0.94          Of those refused, share the applicant waives
   Quote Calculator!C32      11            Days lost per correction cycle
   Quote Calculator!C33      1             Correction cycles expected on a refusal
   Quote Calculator!C34      0.041         Buyer one-year probability of default
   Quote Calculator!C35      0.62          Loss given default on an unsecured trade claim
   Quote Calculator!C36      60            Extra days carried on an unwaived refusal
   Case B!C6                 0.015         Price premium the buyer paid for the longer terms
   Case B!C7                 180           Days of buyer credit conceded

   Result: 232,358 of cost and 74,242 of margin at 120 days; 1,031.76 a day; zero margin on day 192; Case B 72,354; operating-margin threshold 135,281.

