TREASURY MANAGEMENT - 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.
Kept in the blank files, because they are steps rather than data: the three z values, the three spots in twelve months, the neighbouring
limits and spreads, the shorter net terms, the illustrative haircut and payables stretch, the 4 / 6 / 8 days of float.

Bramwell_The_Five_Balances_Blank.xlsx

   Inputs!B11    1400000000            Revenue
   Inputs!B12    168000000             EBITDA
   Inputs!B13    56000000              Capital expenditure
   Inputs!B14    20000000              Cash tax
   Inputs!B15    250                   Business days in the year
   Inputs!B18    96000000              Eurozone entities
   Inputs!B19    18000000              United Kingdom
   Inputs!B20    41000000              United States
   Inputs!B21    12000000              Poland (joint venture, 50 per cent)
   Inputs!B22    24000000              Brazil
   Inputs!B23    19000000              China
   Inputs!B24    4000000               India
   Inputs!B25    9000000               Restricted cash (collateral for guarantees, escrow)
   Inputs!B30    6000000               Poland — the partner's half
   Inputs!B31    6                     Operating float, business days of disbursements (policy)
   Inputs!B32    68400000              Cleared on the concentration account this morning
   Inputs!B35    300000000             Term loan
   Inputs!B36    250000000             Bonds
   Inputs!B37    3                     Net leverage covenant
   Inputs!B41    131000000             Eurozone credit balances
   Inputs!B42    35000000              Eurozone overdrafts (positive)
   Inputs!B43    40000000              The treasury payment due at noon (Chapter 1)

   Result: 214,000,000 / 205,000,000 / 152,000,000 / 120,608,000 / 68,400,000; leverage 2.0000x / 2.3690x / 2.5559x.

The_Liquidity_Test_Blank.xlsx

   Inputs!B11    1400000000            Revenue
   Inputs!B12    168000000             EBITDA
   Inputs!B13    56000000              Capital expenditure
   Inputs!B14    20000000              Cash tax
   Inputs!B15    250                   Business days in the year
   Inputs!B16    2.5                   Operating leverage (EBITDA fall / revenue fall)
   Inputs!B18    152000000             Accessible cash (reading 3, from The Five Balances)
   Inputs!B19    6                     Operating float, business days (policy)
   Inputs!B20    62000000              Seasonal peak: third-quarter inventory build, net (annual plan)
   Inputs!B22    0.031                 Quarter 1
   Inputs!B23    -0.058                Quarter 2
   Inputs!B24    0.012                 Quarter 3
   Inputs!B25    0.047                 Quarter 4
   Inputs!B26    -0.026                Quarter 5
   Inputs!B27    0.069                 Quarter 6
   Inputs!B28    -0.041                Quarter 7
   Inputs!B29    0.008                 Quarter 8
   Inputs!B30    -0.033                Quarter 9
   Inputs!B31    0.055                 Quarter 10
   Inputs!B32    -0.019                Quarter 11
   Inputs!B33    0.024                 Quarter 12
   Inputs!B38    300000000             Term loan
   Inputs!B39    250000000             Bonds
   Inputs!B40    0.03                  Base rate
   Inputs!B41    0.0175                Term loan margin
   Inputs!B42    0.0425                Bond coupon
   Inputs!B43    250000000             Revolver commitment, undrawn
   Inputs!B44    0.015                 Revolver margin
   Inputs!B45    0.35                  Commitment fee, share of margin
   Inputs!B46    0.005                 Upfront / arrangement fee
   Inputs!B47    5                     Tenor over which the fee is amortised (years)
   Inputs!B48    0.026                 Deposit yield
   Inputs!B49    0.6                   Share of the term loan swapped
   Inputs!B50    0.032                 Swap fixed rate
   Inputs!B51    3                     Net leverage covenant
   Inputs!B52    4                     Interest cover covenant
   Inputs!B53    35000000              Eurozone overdrafts (positive)
   Inputs!B54    0.055                 Overdraft rate
   Inputs!B55    131000000             Eurozone credit balances
   Inputs!B56    0.021                 Credit interest rate
   Inputs!B58    0.1                   Revenue fall, full year
   Inputs!B59    12000000              Cash tax in the downside (assumption)
   Inputs!B60    58                    Days sales outstanding
   Inputs!B61    66049315.06849315     Supply chain finance release, if signed (from Working Capital)
   Inputs!B64    214000000             Reported cash (reading 1, from The Five Balances)
   Inputs!B65    0.029                 Money market fund yield
   Inputs!B66    980000000             Cost of goods sold

   Result: minimum 124,596,281.16; without the revolver 27,403,718.84 (5.2377 days); revenue fall to the drawstop 8.4127%; premium 4,062,500.

Working_Capital_and_the_Discount_Blank.xlsx

   Inputs!B11    1400000000            Revenue
   Inputs!B12    980000000             Cost of goods sold
   Inputs!B13    168000000             EBITDA
   Inputs!B14    58                    Days sales outstanding
   Inputs!B15    71                    Days inventory outstanding
   Inputs!B16    49                    Days payables outstanding (group average)
   Inputs!B17    336000000             Net debt on reported cash (from The Five Balances)
   Inputs!B18    398000000             Covenant net debt, accessible cash (from The Five Balances)
   Inputs!B19    3                     Net leverage covenant
   Inputs!B20    370608000             Liquidity headroom (from The Liquidity Test)
   Inputs!B21    0.045                 Revolver drawn rate (base + margin)
   Inputs!B22    1562500               Cost of the undrawn revolver, per year (from The Liquidity Test)
   Inputs!B24    0.02                  Discount
   Inputs!B25    10                    Paid by day
   Inputs!B26    60                    Net terms, days
   Inputs!B27    0.4                   Share of purchases carrying the offer
   Inputs!B29    90                    Extended terms, days
   Inputs!B30    0.6                   Share of purchases on the programme
   Inputs!B31    0.042                 Rate the bank charges the supplier (on the buyer's name)
   Inputs!B32    0.085                 Supplier's own funding rate (assumption)
   Inputs!B34    131000000             Credit balances
   Inputs!B35    35000000              Overdrafts (positive)
   Inputs!B36    0.055                 Overdraft rate
   Inputs!B37    0.021                 Credit interest rate
   Inputs!B41    27403718.84127806     Headroom over the minimum without the revolver (from The Liquidity Test)
   Inputs!B42    2.5                   Operating leverage (from The Liquidity Test)

   Result: net working capital 281,534,246.58; the discount 14.8980%, net gain 5,471,890.41; release 66,049,315.07, leverage 2.7622x; pool 1,190,000.

Hedging_Blank.xlsx

   Inputs!B11    168000000             EBITDA
   Inputs!B12    300000000             Term loan, floating
   Inputs!B13    0.03                  Base rate today
   Inputs!B14    0.0175                Term loan margin
   Inputs!B15    250000000             Bonds, fixed
   Inputs!B16    0.0425                Bond coupon
   Inputs!B17    250000000             Revolver commitment
   Inputs!B18    0.005249999999999999  Commitment fee rate
   Inputs!B19    0.6                   Share of the term loan swapped
   Inputs!B20    0.032                 Swap fixed rate
   Inputs!B21    0.02                  Rate shock
   Inputs!B22    4                     Interest cover covenant
   Inputs!B23    120608000             Available cash (from The Five Balances)
   Inputs!B24    18000000              Cash in the United Kingdom
   Inputs!B25    41000000              Cash in the United States
   Inputs!B26    6000000               Bramwell's half of the Polish venture's account
   Inputs!B27    35000000              Eurozone overdrafts (positive)
   Inputs!B29    120000000             Net long USD, per year
   Inputs!B30    1.08                  Spot, USD per unit of home currency
   Inputs!B31    0.03                  Home interest rate
   Inputs!B32    0.045                 USD interest rate
   Inputs!B36    1                     Layered programme: hedge ratio, quarter 1 ahead
   Inputs!B37    0.75                  Layered programme: hedge ratio, quarter 2 ahead
   Inputs!B38    0.5                   Layered programme: hedge ratio, quarter 3 ahead
   Inputs!B39    0.25                  Layered programme: hedge ratio, quarter 4 ahead
   Inputs!B42    27403718.84127806     Headroom over the minimum without the revolver (from The Liquidity Test)

   Result: the swap saves 3,240,000; cover 5.8036x at +200 bp; forward 1.095728; hedged proceeds 109,516,214.78; one cent 1,019,367.99.

Banks_Payments_and_the_Report_Blank.xlsx

   Inputs!B11    168000000             EBITDA
   Inputs!B12    1400000000            Revenue
   Inputs!B13    5232000               Disbursements per business day (from The Five Balances)
   Inputs!B14    214000000             Reported cash (reading 1)
   Inputs!B15    9000000               Restricted cash
   Inputs!B16    53000000              Trapped cash (Brazil, China, India, the partner's half of Poland)
   Inputs!B17    47000000              ... of which Brazil, China and India
   Inputs!B18    6                     Operating float, business days (policy)
   Inputs!B19    68400000              Cleared on the concentration account this morning (reading 5)
   Inputs!B22    31204281.15872194     Forecast-error reserve at 99 per cent
   Inputs!B23    62000000              Seasonal peak
   Inputs!B24    250000000             Undrawn revolver
   Inputs!B25    66049315.06849315     Supply chain finance release, if signed
   Inputs!B26    2.5                   Operating leverage
   Inputs!B27    0.1                   Downside case: revenue fall for a full year
   Inputs!B30    550000000             Gross debt
   Inputs!B31    3                     Net leverage covenant
   Inputs!B32    4                     Interest cover covenant
   Inputs!B33    26547500              Total interest (from The Liquidity Test)
   Inputs!B34    1925000               Overdraft interest the total leaves out (from Working Capital)
   Inputs!B35    300000000             Term loan
   Inputs!B36    0.0175                Term loan margin
   Inputs!B37    0.015                 Revolver margin
   Inputs!B38    0.35                  Commitment fee, share of margin
   Inputs!B39    0.005                 Upfront fee
   Inputs!B40    5                     Tenor over which the fee is amortised (years)
   Inputs!B43    -608000               Net floating exposure after available cash
   Inputs!B44    64392000              ... on home-currency cash, at most
   Inputs!B45    0.7818181818181819    Fixed share of gross debt
   Inputs!B46    0.7350427350427351    ... counting the netted overdrafts
   Inputs!B47    5.803609983590983     Interest cover at +200 basis points, with the swap
   Inputs!B48    5.219417475728155     ... without the swap
   Inputs!B49    3240000               The swap's saving in the shock year
   Inputs!B50    120000000             Net long USD, a year
   Inputs!B51    1.08                  Spot, USD per unit of home currency
   Inputs!B52    0.625                 Layered programme: average hedge ratio
   Inputs!B55    0.024                 Error in the quarter just ended
   Inputs!B56    327000000             Quarterly disbursements
   Inputs!B57    0.04102576795580597   Sample standard deviation of the error
   Inputs!B60    0.029                 Money market fund yield
   Inputs!B61    0.0275                Treasury bill yield
   Inputs!B62    0.026                 Bank deposit yield
   Inputs!B63    0.41                  Largest bank's share of reported cash
   Inputs!B64    0.25                  Concentration limit (policy)
   Inputs!B67    4200000               Bank fees a year
   Inputs!B68    47                    Bank accounts
   Inputs!B69    11                    Banks
   Inputs!B70    0.0005                FX spread on the USD flow (ASSUMED by the book)
   Inputs!B72    50000000              One lender's commitment to the revolver

   Result: surplus 120,608,000; excess at the largest bank 34,240,000; wallet 11,068,055.56; the nineteen lines of Chapter 18.

