Financial modeling: what a financial model needs to contain

Start with the question, not the spreadsheet

Before opening the file, settle three things: which decision the model must support, how far to project and at what granularity. A model to assess a five-year expansion can be annual. A model to negotiate the grace period on a loan probably needs to be monthly for the first two years.

Who will use it matters too. A model a lender will audit needs traceable logic. A model the finance team will update every month needs to be easy to maintain.

1. Inputs in one place

All assumptions sit in their own area, separate from the calculations: volumes, prices, costs, payment terms, capex, taxes and financing terms. A reader should be able to see everything that was assumed on one screen.

A scenario selector in the same area switches the whole set of assumptions without touching a single formula.

2. Organized, reconciled historicals

Projections start from history. Recent years' financial statements, restated into the same structure as the forecast and reconciled to the accounts, provide the basis for calibrating assumptions and show when a projection departs from what the company has actually done.

3. An integrated three-statement model

This is the core of the model. The three statements are projected together and linked:

• Net income from the income statement flows into equity on the balance sheet.
• The cash flow statement starts from net income and adjusts for non-cash items, working capital, capex and debt movements.
• Closing cash on the cash flow statement is the cash on the balance sheet.

If the balance sheet does not balance, there is an error. Forcing it with a plug line hides the problem instead of solving it.

4. Working capital and capex

This is where many models underestimate the cash required. Receivable, payable and inventory days turn revenue growth into cash consumption. Capex needs a schedule, depreciation and, where relevant, recurring maintenance spend.

5. Debt with a schedule by facility

Each facility has a balance, rate, tenor, grace period and amortization profile. Projecting debt as a single line, with no schedule, hides when cash gets tight. The model also calculates the ratios lenders and investors will look at, such as net debt to EBITDA and debt service coverage.

6. Scenarios and sensitivity

A base, a downside and an upside case, each built from assumptions that are consistent with one another. Sensitivity tables show the effect of the variables that matter most, such as price, volume, cost and discount rate. Break-even analysis shows how much assumption error the plan can absorb.

7. Consistency checks

A good model warns you when something is wrong: a balance sheet that does not balance, negative cash, reversed signs, totals that do not match their parts. The checks sit on a single visible panel so anyone can confirm the model is sound before using its results.

The most common mistakes

• Hard-coded numbers inside formulas that cannot be traced.
• Assumptions scattered across several tabs.
• A balance sheet forced to balance with a plug.
• Working capital ignored or held flat while revenue grows.
• Debt projected without a schedule.
• An upside case presented as the base case.
• A model only its author can use.

The outputs the decision needs

In the end, a model exists to answer questions. The most common outputs are EBITDA and margins, cash generation, funding needs, net debt to EBITDA, debt service coverage and, for projects, NPV, IRR and payback. An executive dashboard with those answers, by scenario, is what reaches the decision-maker.