Excel cases / Build a three-statement model: Brolga Drilling
Build a three-statement model: Brolga Drilling
Forecast a drilling contractor’s income statement, balance sheet and cash flows for five years, with a revolver that funds its fleet growth.
The brief
Brolga Drilling Services is a listed contract driller. Its 150 rigs drill for gold and copper miners in Australia and Canada: exploration holes, resource definition and grade control. Customers pay per metre drilled, so revenue follows the number of rigs at work and the rates Brolga charges.
Brolga has signed new multi-year contracts and will add 24 rigs over the next three years. The board wants to know whether it can fund the plan with its cash, its term loan and a $60m revolving credit facility while paying out 30% of its profit. Build the 2026–2030 income statement, balance sheet and cash flow statement from the 2023–2025 accounts, with a revolver that is drawn and repaid automatically, then answer the questions.
The assumptions
| Revenue per operating rig, yearly increasea year, from $3.20m in 2025 | 3.0% |
| Direct costs% of revenue | 70.0% |
| SG&A% of revenue | 9.0% |
| Depreciation and amortisation% of revenue | 9.0% |
| Cost of a new rig$m per rig, with its support equipment | 2.5 |
| Maintenance capex% of revenue | 7.0% |
| Receivable daysdays of revenue | 60 |
| Inventory daysdays of direct costs | 35 |
| Payable daysdays of direct costs | 40 |
| Corporate tax rate% of profit before tax | 30% |
| Term loan interest rateon the balance at the start of the year | 7.0% |
| Term loan repayment$m a year, until repaid | 15.0 |
| Revolver limit$m | 60.0 |
| Revolver interest rateon the balance at the start of the year | 6.5% |
| Interest earned on cashon the balance at the start of the year | 3.0% |
| Minimum cash$m, kept at all times | 20.0 |
| Dividend payout% of the year’s net income, paid the same year | 30% |
Drivers by year
| 2026E | 2027E | 2028E | 2029E | 2030E | |
|---|---|---|---|---|---|
| Rigs added | 12 | 8 | 4 | 0 | 0 |
| Utilisation | 70% | 72% | 74% | 75% | 75% |
The accounts, 2023–2025
| 2023A | 2024A | 2025A | |
|---|---|---|---|
| Rigs in the fleet | 140 | 145 | 150 |
| Utilisation | 68% | 69% | 70% |
| Revenue | 285.6 | 310.2 | 336.0 |
| EBITDA | 51.4 | 60.5 | 70.6 |
| Net income | 10.9 | 16.4 | 22.3 |
| Cash | 48.0 | 34.3 | 25.1 |
| PP&E, net | 195.0 | 201.3 | 207.1 |
| Term loan | 150.0 | 135.0 | 120.0 |
| Shareholders’ equity | 137.1 | 148.6 | 164.2 |
- New rigs join the fleet on 1 January and work the whole year.
- Interest is charged on balances at the start of the year, so the model has no circular reference.
- Tax is paid in the year it is due; dividends are paid in the year the profit is made.
- D&A is a percentage of revenue. There are no leases, provisions or deferred taxes.
- A year has 365 days.
The steps
Revenue 2026
What is Brolga’s revenue in 2026?
Hint
Start from the fleet: 150 rigs plus the 12 that arrive on 1 January. Multiply by utilisation, then by revenue per operating rig, which rises 3% from $3.20m.
EBITDA 2026
What is EBITDA in 2026?
Hint
Revenue less direct costs (70% of revenue) and SG&A (9%).
Net income 2026
What is net income in 2026?
Hint
Take off D&A (9% of revenue) to reach EBIT. Interest runs on the balances at the start of the year: the term loan was $120m and the revolver nothing, and the $25.1m of cash earns 3%. Tax is 30%.
Working capital 2026
How much cash does the increase in working capital absorb in 2026? Give it as a positive number.
Hint
Receivables are 60 days of revenue; inventory and payables are 35 and 40 days of direct costs. Compare each with its end-2025 balance: more receivables and inventory use cash, more payables bring cash.
Cash flow from operations 2026
What is cash flow from operations in 2026?
Hint
Start from net income, add back D&A, which is not cash, and take off the cash absorbed by working capital.
Capex 2026
How much does Brolga spend on capex in 2026? Give it as a positive number.
Hint
Two parts: the new rigs at $2.5m each, and maintenance capex at 7% of revenue.
Revolver at the end of 2026
How much is drawn on the revolver at the end of 2026?
Hint
Add up the cash that comes in and goes out before the revolver: cash flow from operations, capex, the $15m term-loan repayment and the dividend (30% of net income). The revolver covers whatever would take cash below $20m.
Peak revolver
What is the largest amount drawn on the revolver at any year-end of the forecast?
Hint
Copy the 2026 column across to 2030, then take the largest year-end revolver balance. The draw formula must also repay the revolver when there is a surplus.
Cash at the end of 2030
How much cash does Brolga hold at the end of 2030?
Hint
Cash stays at the $20m minimum while the revolver is drawn. Once the revolver is repaid, the surplus stays in cash.
Total assets at the end of 2030
What are Brolga’s total assets at the end of 2030?
Hint
Cash, receivables, inventory and net PP&E. Check first that your balance sheet balances in every year: the check row must show zero.
Leverage at the end of 2026
What is net debt to EBITDA at the end of 2026?
Hint
Net debt is the term loan plus the revolver, less cash.
Open questions
Brolga is profitable every year. Why does it still need its revolver in 2026 and 2027?
Show the model answer
Profit is not cash. In 2026 cash flow from operations ($53.8m) does not cover capex ($56.2m, of which $30m for 12 new rigs), the $15m term-loan repayment and the $7.8m dividend. Growth also ties up cash in working capital ($5.9m in 2026). Once the rigs are bought, capex falls to maintenance only and the surplus repays the revolver.
Exploration budgets fall and utilisation is 5 points lower in every forecast year. What is the peak revolver? Why does it barely move?
Show the model answer
The peak is $33.4m in 2028, against $32.7m. EBITDA falls, but less revenue also means fewer receivables (working capital releases cash), lower maintenance capex and less tax. The model treats every cost as variable, which flatters it: in practice crews, overheads and the rigs already ordered are largely fixed, so a real downturn would hurt more. That is the first thing to stress-test in the assumptions.
The board proposes to pay out 50% of net income instead of 30%. Is the plan still funded?
Show the model answer
Yes, but with less room. The revolver peaks at $49.4m in 2028, $10.6m below the $60m limit, and $30.4m is still drawn at the end of 2030 instead of nothing. A higher payout matters more to the funding plan than a 5-point fall in utilisation. A prudent answer: keep 30% until the rigs are paid for.
Your balance check shows the same non-zero amount in every forecast year. Where do you look? And if the gap grows every year?
Show the model answer
A constant gap that starts in 2026 comes from the link to the opening balance sheet: an item that does not carry over from 2025, or an opening balance typed instead of linked. A gap that grows each year means a flow is missing or has the wrong sign in one statement only: typically a balance-sheet item that moves but has no line in the cash flow statement, D&A not added back, or a dividend taken from cash but not from equity.
Interest is charged on opening balances. Why, and what would change with average balances?
Show the model answer
Interest on the year’s average balance depends on the revolver draw, which depends on cash flow, which depends on interest: a circular reference. Opening balances break the loop and keep the model stable. With average balances (and iterative calculation switched on), 2026 interest would rise by about half the year’s draw times 6.5%, less than $1m here. Most banks accept either convention in a test if you say which one you used.