Three-Statement Forecast and Funding Risk

The business question

A packaging manufacturer had revenue of US$400 million in 2026 and EBITDA of US$60 million. It owes US$160 million on a term loan, repaid at US$15 million a year, and has a US$60 million revolving credit facility, undrawn. The banks test two covenants every year end: net debt no more than 3.0 times EBITDA, and EBITDA at least 4.0 times interest.

The company pays out 35% of its profit as dividends while leverage is below 2.5 times. Management's plan, with 4% growth a year and a 15% margin, takes leverage from 2.4 times today to about 1.1 times in 2031.

But sales growth is volatile and persistent — a bad year tends to be followed by another — margins fall when volumes fall, and working capital, capital spending and interest rates are uncertain. The model simulates the three statements together and tests the covenants and the company's liquidity in every year.

Who it is for

CFOs, treasury and FP&A teams, lenders and credit analysts, boards approving a financing plan.

Main outputs

The chance of breaching the leverage or interest-cover covenant and of a liquidity shortfall, year by year and over five years · leverage by year with its range, against the plan and the covenant · peak leverage and revolver use · the drivers of a breach.

How the model works

Three sheets after this one, read from left to right. Each one feeds the next.

1. 1

   Inputs sheet

   The 2026 balance sheet and results. The expected growth, its yearly shock and persistence; the margin and how it moves with growth. Working-capital days, capex and the interest rate (PERT); the loan terms, revolver, dividend policy and covenants.

2. 2

   Forecast sheet

   VoseTimeAR1 draws five years of revenue whose growth rates are persistent. The income statement follows from the margin, depreciation, interest on the opening debt and tax. The cash flow after working capital, capex, loan repayment and dividends is funded by the revolver when it is negative and repays it when it is positive. The balance sheet balances in every year.

   Each year end, the covenants are tested as the banks word them: net debt above the maximum × EBITDA, or EBITDA below the minimum × interest. The plan repeats the statements with growth at the expected rate and every PERT at its most likely value.

3. 3

   Results sheet

   The chance of a covenant breach and of a liquidity shortfall in each year and over the five years. Leverage by year against the plan and the covenant; peak leverage and revolver use; what drives a breach.

ModelRisk techniques

• Autoregressive revenue VoseTimeAR1
• Margins linked to growth
• PERT working capital and capex
• A balance sheet that balances
• Revolver for cash gaps
• Dividend block on leverage
• Covenant tests as outputs
• Plan at most likely values
• Fan chart with the covenant
• Tornado of breach chance

Find similar models

Product

• ModelRisk

Type

• Simulation

Application

• Forecasting
• Capital and tail risk

Uses

• PERT
• Time series
• Autoregressive (AR, MA)
• Sensitivity analysis (tornado)

Inputs and assumptions

Every assumption in the model, with its uncertainty and where it comes from. The figures are illustrative.

Units and conventions

Money

US$ million (US$m).

Years

2026 is the last actual year; 2027 to 2031 are forecast; balances at year end.

Leverage

Net debt (term loan + revolver − cash) ÷ EBITDA. The covenant is tested as worded, net debt above the maximum × EBITDA, so a year with negative EBITDA is a breach.

Interest cover

EBITDA ÷ interest paid in the year. Tested as EBITDA below the minimum × interest, which also works in a year without interest.

Liquidity shortfall

The revolver is fully drawn and cash falls below the minimum the company holds.

Key assumptions and limitations

Interest

Charged on the debt at the start of each year, at one base rate for the five years.

Working capital

Receivables, inventory and payables in days of revenue.

Tax

25% of positive pre-tax profit; no loss carry-forward.

Dividends

35% of net income when the previous year end's leverage was below 2.5 times.

Plan

Growth at the expected rate every year and every PERT at its most likely value: the 15% margin and the days, capex and rate the plan assumes.

Data

The company and all figures are invented for this example.

How to use it

1. Enter your company's 2026 position, its plan assumptions and their ranges, and your financing terms.
2. Press F9 on the Forecast sheet to watch the statements and covenant tests move together.
3. Run a simulation from the ModelRisk ribbon (10,000 samples with a fixed seed are set).
4. Negotiate covenant headroom, the repayment profile or the dividend policy until the chance of a breach is acceptable.

The tornado chart fills in after a simulation with ModelRisk’s classic engine.

ModelRisk functions used

VoseTimeAR1

Five years of revenue: log growth follows a first-order autoregressive process (persistent).

VosePERT

The margin, working-capital days, capex and the base interest rate.

VoseInput, VoseOutput

Name the drivers, and the covenant tests and leverage of every year as outputs.

VoseSimMean, VoseSimPercentile

Statistics of the simulation for the tables and charts on the Results sheet; the mean of a 1/0 output is a probability.

VoseSimSensitivityMean

The tornado chart: every VoseInput ranked by how far the chance of a covenant breach moves across its range.

Further reading

• Sensitivity analysis in Excel: a tornado chart step by step (Vose guide)
• Benninga, S. & Mofkadi, T. (2022). Financial Modeling, 5th edition. MIT Press.
• Vose, D. (2008). Risk Analysis: A Quantitative Guide, 3rd edition. Wiley — time series.