Model structure and best practice

0:00 A model only its author understands is a liability

Picture this. You've inherited a project finance model with forty sheets. Inputs are scattered across calculation pages. And you find a formula like this: B twelve, times one point oh three five to the power of C four minus twenty twenty-six, times nought point nine two. What's one point oh three five? What's nought point nine two? Nobody knows, and the person who built it has left. That model might drive a decision worth hundreds of millions. In this lecture you'll learn how to structure a model so that someone else can read it, change it and trust it: separating inputs, calculations and outputs, building a timing sheet with flags, handling circularity safely, and adding checks and version control. By the end, you'll be able to score any model on its structure and name the three changes that would help it most.

1:02 Why it matters

Why does structure matter so much? Because a project finance model isn't a personal calculation. It's read by lenders, model auditors, tax advisers and boards, and it often lives for the whole life of the project, maintained by people who didn't build it. A model that only its author understands is a liability: errors hide, changes break things silently, and every question takes days to answer. Good structure makes a model transparent, flexible, accurate and auditable. And here's a practical benefit: lender model audits on well-structured models tend to produce far fewer findings, which saves time and fees on the path to financial close.

1:47 The concept: a recipe, a kitchen and a plate

Here's the key idea. Think of a model like cooking. Inputs are the recipe card: every assumption in one place, clearly formatted, with a source and a date. Calculations are the kitchen: formulas only, no numbers typed into them, flowing top to bottom and left to right. Outputs are the plate: summaries, ratios, returns and charts that only reference calculations, with nothing new calculated there. Then a handful of principles make it readable. Use the same timeline columns across every calculation sheet. Keep one formula per row, the same formula all the way across the timeline. Never hard-code numbers like nought point nought five or three sixty-five inside formulas; put them in inputs with labels. Keep formulas short, and break complex logic into steps. Label every row with its units. And use consistent formatting to distinguish inputs, calculations, links and checks.

2:48 Timing and flags

Projects move through phases: development, construction, operations and decommissioning. So every good model has a timing sheet. Across the top, period start and end dates. Beneath them, flags: a construction flag that's one during construction and zero otherwise, an operations flag, a debt repayment flag that starts after the grace period, and days in the period. Then you multiply calculations by the relevant flag. Revenue only appears when the operations flag is one. Construction costs only appear when the construction flag is one. The payoff is flexibility: change the construction length input from twenty-four months to thirty, and every flag, and therefore every calculation, shifts automatically. No hunting through sheets to move formulas by hand. It's the single most useful structural habit in project finance modelling.

3:43 Worked example one: breaking up a monster formula

Let's fix that monster formula. B twelve times one point oh three five to the power of C four minus twenty twenty-six, times nought point nine two. First, lift out the hard-coded numbers. One point oh three five is an escalation rate of three and a half per cent, so it becomes a labelled input with a source. Twenty twenty-six is the base year of the tariff, another input. Nought point nine two is an availability assumption, another input. Then break the logic into rows. Row one: escalation factor, calculated from the rate and the years since the base date. Row two: available capacity, equal to capacity times availability. Row three: revenue, equal to available capacity times the base tariff times the escalation factor. Three short, readable rows instead of one clever one. Anyone can now check it, and anyone can change the escalation assumption in one place.

4:47 Worked example two: an Abu Dhabi restructure

Now the realistic example from the lesson. A fictional developer in Abu Dhabi inherited a model with forty sheets, inputs scattered across calculation pages, and formulas like the one we just fixed. The modelling team restructured it. All inputs moved to two input sheets, each with a source. Formulas were broken into escalation, availability and revenue steps. A timing sheet with flags drove every calculation. And eighteen automated checks were added, summarised on one checks sheet with a master check on the output page. The lender's model audit on the old version had produced sixty findings. On the restructured version, it produced nine. That's weeks of back-and-forth avoided on the path to financial close, and a model the asset management team could actually maintain afterwards.

5:41 Watch me do it: a checks sheet

Let me show you how I build a checks sheet. One row per check. Balance sheet balances every period: the maximum absolute difference between assets and liabilities plus equity, across all periods. Sources equal uses at the end of construction. Cash balance never negative. Debt fully repaid by final maturity. Ratios meet covenants in the base case. And periodic values sum to annual totals. Each check returns one if it fails and zero if it passes, using a small tolerance for rounding. A master check sums them all, and that master check appears at the top of every output page, formatted green at zero and red otherwise. Then I test it: I deliberately break an input, say, making debt too large to repay. The master check turns red. If it doesn't, the check itself is broken.

6:40 Circularity and version control

Project finance models contain natural circularities. Interest during construction depends on debt drawn, which depends on total uses, which include interest. Fees based on debt size, and reserve funding based on future debt service, create more loops. Common solutions are a copy-paste macro that iterates to convergence, an algebraic solution, or careful ordering of calculations. What I'd avoid is simply switching on the spreadsheet's iterative calculation setting and hoping, because errors can become hidden and self-reinforcing. Then version control. Name versions consistently and keep a log of what changed, why and by whom. Keep a model book, or databook, listing every input with its source. And commission an independent model audit before financial close, which lenders usually require anyway.

7:32 Common mistakes, recap and try this now

The common mistakes: hard-coded numbers inside formulas, inconsistent timelines between sheets, hidden rows, sheets or links to external files, no checks, or checks nobody looks at, and unlabelled units and signs, so nobody knows whether a cost is positive or negative. So, to recap. Separate inputs, calculations and outputs. Drive timing with flags. Keep one formula per row and no hard-codes. Handle circularity deliberately. Build a checks sheet with a master check on every output. And keep a version log and model book. Here's a great test: ask a colleague who didn't build your model to change construction length and tell you what happens to equity IRR. If they can do it in minutes, your structure works. Your try-this-now: open a model you use, score it against the principles table in the lesson, and list the three changes that would most improve transparency.