--- title: "Model structure and best practice | Optimize All Academy" description: "Why structure matters A project finance model may drive investment decisions worth hundreds of millions, be read by lenders, auditors and advisers, and…" url: https://optimizeall.com/learn/project-finance-and-financial-modelling/model-structure updated: 2026-10-05 --- Project Finance & Financial Modelling · Financial modelling structure and best practice · lesson 7 of 20 · 15 min # Model structure and best practice ## Why structure matters A project finance model may drive investment decisions worth hundreds of millions, be read by lenders, auditors and advisers, and evolve for years. A model that only its author understands is a liability. Good structure makes models **transparent, flexible, accurate and auditable**. ## The standard flow ``` INPUTS ──► CALCULATIONS ──► OUTPUTS (assumptions, (timing, construction, (summary, ratios, scenarios) revenue, opex, tax, returns, charts, debt, waterfall) checks) ``` Keep these separate: - **Inputs:** every hard-coded assumption in one place (or a small set of input sheets), clearly formatted, sourced and dated. - **Calculations:** formulas only; no hard-coded numbers. - **Outputs:** summaries that reference calculations; nothing calculated here that is not traceable. ## Widely used modelling principles Several published standards and conventions exist (for example, the FAST Standard and other industry best-practice guides). Common principles include: | Principle | Practice | |---|---| | Consistency | Same timeline columns across all calculation sheets | | One formula per row | A row uses the same formula across the timeline | | No hard-codes in formulas | Numbers like 0.05 or 365 live in inputs, labelled | | Short, simple formulas | Break complex logic into steps | | Flow direction | Calculations flow top-to-bottom and left-to-right where possible | | Clear formatting | Distinguish inputs, calculations, links and checks by style | | Flags | Use 1/0 flags for periods (construction, operations, debt repayment) | | Units | Label every row with units (USD 000, MWh, %) | | Checks | Balance sheet balances, sources = uses, cash never negative unless intended | ## Timing and flags Projects move through phases: development, construction, operations, decommissioning. Build a **timing sheet** with period start and end dates and flags: ``` Period end date 31-Mar 30-Jun 30-Sep 31-Dec ... Construction flag 1 1 1 0 Operations flag 0 0 0 1 Debt repayment flag 0 0 0 0 (starts after grace period) Days in period 90 91 92 92 ``` Multiply calculations by flags so that, for example, revenue only appears when the operations flag = 1. Changing the construction length input then shifts everything automatically. ## Periodicity Construction is often modelled monthly or quarterly; operations semi-annually (aligned with debt repayment dates) or quarterly. Mixed periodicity adds complexity; keep it only if it adds real value. ## Circularity Project finance models contain natural circularities: interest during construction depends on debt drawn, which depends on total uses, which include interest. Other examples: fees based on debt size, DSRA funding based on future debt service. Approaches include copy-paste macros that iterate to convergence, algebraic solutions, or careful ordering of calculations. Avoid relying on the spreadsheet's iterative-calculation setting without controls, because errors can become hidden and self-reinforcing. ## Checks A robust model has a **checks sheet** summarising all checks with a master check on the output page: - Balance sheet balances every period. - 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. - Sum of periodic values equals annual totals. ## Version control and documentation - Name versions consistently and log changes (what changed, why, by whom). - Keep a **model book** or databook listing every input with its source. - Commission an independent **model audit** before financial close; lenders usually require it. ## Worked example: a structure review *Illustrative.* A fictional developer in Abu Dhabi inherited a model with 40 sheets, inputs scattered across calculation pages, and formulas like =B12*1.035^(C4-2026)*0.92. The modelling team restructured it: all inputs moved to two input sheets with sources; formulas broken into escalation, availability and revenue steps; a timing sheet with flags; and 18 automated checks. A lender's model audit that previously produced 60 findings produced 9 on the restructured version. ## 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. - Unlabelled units and signs (is cost positive or negative?). ## Quick self-check Ask a colleague who did not build your model to change one input, such as construction length, and tell you what happens to equity IRR. If they can do it in minutes, find the input easily and trust the checks, your structure works. If they need you to explain where things are, invest in restructuring before the model grows further; the cost of poor structure rises with every new sheet. ## Hands-on: a timing sheet with flags in Excel ```text Inputs: Start_date, Constr_months, Ops_years, Grace_months, Period_months (e.g. 6) Row 5 Period number 1, 2, 3 … Row 6 Period start F6 =Start_date ; G6 =F7+1 Row 7 Period end F7 =EOMONTH(F6, Period_months-1) Row 8 COD =EOMONTH(Start_date, Constr_months-1) Row 9 Construction flag =--(F7<=COD) Row 10 Operations flag =--AND(F6>COD, F6<=EDATE(COD, Ops_years*12)) Row 11 Repayment flag =--AND(F10=1, F6>EDATE(COD, Grace_months)) Row 12 Days in period =F7-F6+1 ``` Every operating row then multiplies by row 10, for example `Revenue =Output*Tariff*Ops_flag`. Change `Constr_months` and the whole model moves consistently. ## Hands-on: a checks sheet ```text Tolerance (named) = 0.001 Balance sheet =IF(MAX(ABS(BS_Assets-BS_LiabEquity))>Tolerance,1,0) (enter as a dynamic-array or with SUMPRODUCT) Sources = uses =IF(ABS(Total_sources-Total_uses)>Tolerance,1,0) Cash ≥ 0 =IF(MIN(Cash_balance_row)<-Tolerance,1,0) Debt repaid =IF(ABS(INDEX(Debt_balance_row, Final_maturity_col))>Tolerance,1,0) MASTER CHECK =SUM(Check_range) show on every output page; 0 = all pass ``` In older Excel versions without dynamic arrays, use `=SUMPRODUCT(--(ABS(BS_Assets-BS_LiabEquity)>Tolerance))>0` for the balance check. ## How to measure success - Zero hard-coded numbers in calculation sheets (a model review add-in or a simple formula search will find them). - The master check is zero in every scenario. - A new reviewer can trace any output back to its inputs in minutes. ## Video lecture: Model structure and best practice Lecture coming soon · 9 chapters · about 8 minutes. Read the full transcript below. 1. A model only its author understands is a liability 2. Why it matters 3. The concept: a recipe, a kitchen and a plate 4. Timing and flags 5. Worked example one: breaking up a monster formula 6. Worked example two: an Abu Dhabi restructure 7. Watch me do it: a checks sheet 8. Circularity and version control 9. Common mistakes, recap and try this now ## Lecture transcript ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ### 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. ## Key takeaways - Separate inputs, calculations and outputs; keep all assumptions sourced in inputs. - Use consistent timelines, one formula per row, flags for phases and labelled units. - Handle circularities deliberately and keep a checks sheet with a master check. - Document versions, keep a databook and obtain an independent model audit before close. ## Try it Open a spreadsheet model you use and score it against the principles table. List the three changes that would most improve transparency. - [Previous: Contracts and procurement basics](https://optimizeall.com/learn/project-finance-and-financial-modelling/contracts-and-procurement) - [Next: Model standards, review and audit](https://optimizeall.com/learn/project-finance-and-financial-modelling/model-standards-and-review) - [All lessons of Project Finance & Financial Modelling](https://optimizeall.com/learn/project-finance-and-financial-modelling)