Twelve months of cash flow, working capital from its drivers, two stresses that hit the trough in different ways, and what the headroom costs.
Size a revolving credit facility on the monthly cash trough, not on the annual accounts: run twelve months of cash flow with working capital built from its drivers, find the largest shortfall below minimum cash, then stress the season. For an illustrative distributor with 120.0 million of sales, the base case needs 17.9 million in April, a season in which customers pay a month late needs 24.7 million, and one in which sales also miss by 15 per cent needs 26.5 million, so a 30.0 million commitment is the defensible size. The extra headroom costs 0.106 million a year in commitment fees.
Worked in full in Credit Analysis by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
The year-end balance sheet of the same company shows 9.6 million of cash and an undrawn revolver. Nothing in the audited accounts says that, eight months earlier, it was 17.9 million short.
| Input | Value |
|---|---|
| Annual sales, peaking March to June | 120.0 |
| Cost of goods sold, share of sales | 60% |
| Fixed operating costs per month | 2.5 |
| Capex per month | 0.3 |
| Term loan interest per month | 0.4 |
| Tax, paid in June and December, each | 1.5 |
| Receivables: this month's sales plus half of last month's | about 45 days |
| Inventory: cost of the next two months' sales | 2 months |
| Payables: one month of purchases | 30 days |
| Opening cash / minimum operating cash | 3.0 / 2.0 |
| Revolver margin; commitment fee as share of margin | 2.50%; 35% |
Annual EBITDA is 18.0 million. After 3.6 of capex, 4.8 of interest and 3.0 of tax, the business generates 6.6 million of free cash flow a year. It is a healthy borrower. It still needs a revolver, because inventory for the spring has to be bought in winter.
| Month | Sales | EBITDA | Working capital | Cash flow | Cash | Revolver need |
|---|---|---|---|---|---|---|
| Jan | 5.0 | −0.5 | 12.1 | 0.2 | 3.2 | 0.0 |
| Feb | 6.0 | −0.1 | 15.7 | −4.4 | −1.2 | 3.2 |
| Mar | 12.0 | 2.3 | 25.8 | −8.5 | −9.7 | 11.7 |
| Apr | 18.0 | 4.7 | 36.0 | −6.2 | −15.9 | 17.9 |
| May | 20.0 | 5.5 | 38.6 | 2.2 | −13.7 | 15.7 |
| Jun | 16.0 | 3.9 | 32.0 | 8.3 | −5.4 | 7.4 |
| Jul | 10.0 | 1.5 | 22.2 | 10.6 | 5.2 | 0.0 |
| Aug | 7.0 | 0.3 | 15.6 | 6.2 | 11.4 | 0.0 |
| Sep | 6.0 | −0.1 | 13.1 | 1.7 | 13.1 | 0.0 |
| Oct | 6.0 | −0.1 | 13.2 | −0.9 | 12.2 | 0.0 |
| Nov | 7.0 | 0.3 | 14.2 | −1.4 | 10.8 | 0.0 |
| Dec | 7.0 | 0.3 | 13.5 | −1.2 | 9.6 | 0.0 |
Working capital = receivables + inventory − payables
Cash flow = EBITDA − increase in working capital − capex − interest − tax
Revolver need = MAX(0, minimum cash − cumulative cash)
Peak need = MAX over the twelve months = 17.9 in April
Excel: =MAX(0,MinCash-(OpenCash+SUM($H$2:H2))) down the months, then =MAX(I2:I13)
Working capital swings by 26.5 million between its January low of 12.1 and its May high of 38.6, which is 32.2 per cent of annual sales at the peak against 11.3 per cent at the year end. The revolver need is smaller than the swing because the business earns EBITDA through the season and starts the year with cash. Notice too that the need peaks in April, a month before working capital does: by May, collections from the strongest sales month are already arriving.
A facility sized at 17.9 million is fully drawn in an average year. Lenders and treasurers size for a bad one, and the two obvious ways a season goes wrong do very different things to the trough.
| Case | Peak need | Month | Increase | Months drawn |
|---|---|---|---|---|
| Base | 17.9 | Apr | 0.0 | 5 |
| Sales 15% below plan, stock already bought | 18.9 | May | 1.0 | 9 |
| Customers pay a month late | 24.7 | May | 6.8 | 5 |
| Both | 26.5 | May | 8.6 | 9 |
A sales miss barely moves the peak, because lower sales also mean lower receivables. Its damage is duration: unsold stock sits on the balance sheet and the revolver is still drawn by 5.5 million in July and 2.3 million in December, so the clean-down month disappears. Late payment is the opposite: the peak jumps by 6.8 million and then clears as the cash arrives. A facility has to survive both shapes.
The average base case drawing is 4.66 million over five months. The commitment fee is 35 per cent of a 2.50 per cent margin, so 0.875 per cent on every undrawn euro. The mechanics are in how a commitment fee is calculated on a revolver.
| Commitment | Margin on drawings | Commitment fee | Total | Headroom, combined stress |
|---|---|---|---|---|
| 17.9 | 0.116 | 0.116 | 0.232 | −8.6 |
| 20.0 | 0.116 | 0.134 | 0.251 | −6.5 |
| 25.0 | 0.116 | 0.178 | 0.294 | −1.5 |
| 30.0 | 0.116 | 0.222 | 0.338 | 3.5 |
| 35.0 | 0.116 | 0.265 | 0.382 | 8.5 |
Moving from the bare base case peak to 30.0 million costs 0.106 million a year, 0.59 per cent of EBITDA. Each further 5.0 million of commitment adds 0.0437 million. That is cheap insurance against a liquidity event in May, which is the month a seasonal business has the least room to negotiate. Ancillary costs, upfront fees and utilisation fees would add to these figures; they do not change the ranking.
Size to the stressed trough, price on the average drawing. The commitment follows the worst month you can defend; the cost follows the average month, and most of the commitment is never used.
Build the month, find the trough, stress it two ways, and buy the commitment that survives the worse shape. Here that is 30.0 million against a base peak of 17.9, for 0.106 million a year. The monthly cash and liquidity tests behind a credit view of this borrower are in the free workbooks for Credit Analysis, and the same build can be run in the corporate liquidity model.
They model monthly cash flow with working capital driven by days of receivables, inventory and payables, find the largest shortfall below a minimum cash balance, and add headroom for a stressed season. In the illustrative case the base trough is 17.9 million and the combined stress 26.5 million, so the commitment is set at 30.0 million.
Because cash goes out for inventory before customers pay for it. The distributor here earns 18.0 million of EBITDA and 6.6 million of free cash flow a year, yet working capital rises from 12.1 million in January to 38.6 million in May, leaving it 17.9 million short of its minimum cash in April.
The commitment fee on the undrawn amount, often a share of the margin. At 35 per cent of a 2.50 per cent margin that is 0.875 per cent, so each 5.0 million of extra commitment costs about 0.0437 million a year. Raising the facility from 17.9 to 30.0 million costs 0.106 million a year.
This article is one calculation from Credit Analysis. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: credit, private credit and distressed debt → · All 1614 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.