9.1 Model Structure and Layout

Why layout is a scored modeling skill

Quick Answer: A CFI-aligned model segregates inputs, calculations, and outputs (separate sheets or clearly marked blocks), uses one column per time period aligned across every sheet, and copies one formula across each forecast block. Design backward from the outputs the decision needs, then build supporting schedules. A tiny exam-ready architecture is Cover | Assumptions | IS | BS | CF | Checks, with corkscrew waterfalls for PP&E, debt, and equity.

Financial Modeling is about 30% of CFI's published FMVA topic-weight graphic. After the 27 February 2026 program refresh, Financial Modeling Guidelines is a required core course — not an elective style lecture. The final (CFI Help Center, 4 March 2026) is 50 randomized questions in 3 hours, including Excel modeling case studies, with a 70% passing score. A file that produces a plausible net income and then fails to balance, or that hides 8% growth inside an income-statement formula, fails this course's rules even when the arithmetic is locally correct.

CFI's public Financial Modeling Guidelines and the FMVA course of the same name are the CFI-aligned modeling code tested on the core path. They are design rules: simple, clear, transparent files that a stakeholder can audit. They are not a secret color palette and not a license to invent unpublished RGB codes.

Design backward from outputs

Many workbooks start in cell B2 with a revenue guess. CFI's preferred design approach is the opposite: start with the outputs. What decision does this file support?

• A lender wants leverage and interest coverage.
• An investment committee wants unlevered free cash flow, WACC, and equity value.
• A board wants a three-statement forecast they can print and a Checks sheet that reads zero.

Once the dashboard (or the three statements plus Checks) is sketched, you know which schedules and which inputs you actually need. You do not build a 40-tab SKU model to answer a three-year cash question.

Apex Components (figures in $ thousands) is the running example in this chapter. The decision: can the 2025–2027 forecast fund capex from operations without new debt? Required outputs are net income, ending cash, and a balancing balance sheet. That implies an income statement, a working-capital link, a PP&E waterfall, a cash-flow statement, and a Checks sheet. It does not imply a 12-product operating model.

CFI contrasts horizontal time (years across columns — the standard) with vertically stacked schedules (PP&E waterfall under working capital under debt, still sharing the same column years). FMVA three-statement files are typically stacked schedules on each statement sheet, with time going right. Navigational stub columns and row grouping (not hiding) let you jump a stacked IS without burying a corkscrew line.

Segregate inputs, calculations, and outputs

Every cell is one of three things:

1. Input — a hardcoded number or switch the user is allowed to type (growth 8%, tax 25%, 2024A revenue 10,000).
2. Calculation — a formula that transforms inputs (COGS = revenue × 60%).
3. Output — a result a decision-maker reads (NI, ending cash, a chart, a check flag).

CFI-aligned practice is to keep those three families apart: separate sheets, or clearly marked sections on a sheet. Never leave a typed 8% sitting in the middle of a formula row for 2026.

Do not type 2025 revenue on the IS. Type 2024A revenue and the 8% growth on Assumptions. The IS formula links to those cells and fills right.

A one-sheet case file is not automatically wrong. If the exam workbook is a single sheet, mark an Inputs block at the top, calculations in the middle, and a Checks block at the bottom. Segregation is the rule; multiple sheets are the usual implementation.

Exam trap: compact is not the same as mixed. Putting every driver next to its IS line looks convenient and destroys the audit. A reviewer (and you, 20 minutes later) cannot Go To Special → Constants and see only the Assumptions block.

One column equals one time period

In an annual model, column C is 2024A on every sheet. Column D is 2025E everywhere. Column E is 2026E. You never put 2025 on the IS in column D and 2025 on the BS in column E. That alignment is how you trace cash: IS NI in D20, CF NI in D8, BS retained earnings using D20 — all in column D.

Apex column map:

Periodicity must match the column. Do not drop a monthly revolver schedule into column D of an annual IS. If you need months, use a separate monthly block whose annual totals land in the annual columns. Mixing a January column with a FY2025 column in the same block is how interest is annualized incorrectly.

Freeze the year row and the stub column so you never type a 2027 formula while looking at 2024 labels (Chapter 6). The layout rule is prior: if the years are not in the same columns, freeze panes cannot save the cash tie.

One formula across a block

CFI's consistent-formula rule: write the forecast formula once and copy it across the block. Every forecast year for revenue should be the same relative/absolute pattern. If 2027 is a special year, put the special in an input (growth of 4% in 2027) rather than rewriting the 2027 formula.

Apex 2024A revenue is a hardcoded 10,000 (actual). Growth on Assumptions is 8% in D13:F13. IS 2025E:

=C12*(1+Assumptions!D13) copied across E12:F12.

2024A = 10,000. 2025E = 10,000 × 1.08 = 10,800. 2026E = 10,800 × 1.08 = 11,664. 2027E = 11,664 × 1.08 = 12,597.12 (a $000s format with zero decimals displays 12,597). If 2026 shows 11,664 but 2027 shows a typed 13,000, Go To Special → Row differences lights up 2027. That is the guidelines miss a case study is hunting.

Never type =C12*1.08 in D12. The 1.08 is an input wearing a formula costume. Change growth to 6% on Assumptions and D12 will not move.

The same copy rule applies to COGS (=revenue × COGS%), tax (=EBT × tax rate), and NWC (=revenue × NWC%). Unique nested IFs in each year are how 2026 gets a 21% tax rate you cannot find later.

Positioning precedents, waterfalls, and corkscrews

Position precedents above or to the left of the cells that depend on them. Time flows left to right. Detail flows top to bottom. A reviewer should never have to look down the sheet past NI to find the tax rate NI already used. Drivers live on Assumptions or in a schedule above the three statements, not in a footer.

Waterfall (corkscrew) schedules make a closing balance transparent:

Opening net PP&E

• Capex

• Depreciation = Closing net PP&E

Apex 2024A: opening 3,900 + capex 500 − D&A 400 = closing 4,000. 2025E: 4,000 + 500 − 400 = 4,100. Each line is visible. A single cell that jumps from opening to closing with no corkscrew may calculate, but it is not CFI-transparent.

Use the same corkscrew shape for debt (opening + draws − repayments), retained earnings (opening + NI − dividends), and NWC (closing = % × revenue, with ΔNWC on the cash-flow statement).

Totals sit immediately under their section, in the same column years. Do not park a grand total in a random column G that no other sheet uses.

CFI's guidelines course also covers building-block schedules: a small, reusable block (the corkscrew, the revenue build, the tax calc) that stacks. If you can audit one block, you can audit the stack.

Worked mini-architecture: Apex 2024A ties

Opening (end-2023): cash 800, NWC 1,350, net PP&E 3,900, debt 2,200, equity 3,850. Assets 6,050 = L+E 6,050.

2024A IS: revenue 10,000, COGS 60% = 6,000, OpEx 20% = 2,000, D&A 400, EBIT 1,600, interest 6% × 2,200 = 132, EBT 1,468, tax 25% = 367, NI 1,101.

2024A CF: NI 1,101 + D&A 400 − ΔNWC 150 (NWC to 1,500) − capex 500 = Δcash 851. Ending cash 1,651.

2024A BS: cash 1,651 + NWC 1,500 + PP&E 4,000 = 7,151. Debt 2,200 + equity 3,850 + 1,101 = 7,151. Checks reads 0.

That file is small on purpose. An FMVA case will be larger. The sheet map, column years, and copy-one-formula rule do not change.

Layout traps on a timed case

• Off-by-one year: formula filled through 2026; 2027 left as a stale paste value.
• Column drift: IS 2025 in D, BS 2025 in E — cash will not tie.
• Inputs on the IS: typing 10,800 instead of linking growth.
• Hidden rows instead of grouping — a corkscrew line disappears.
• Merged cells across years — fills and data tables break.
• Macros to navigate — CFI's guidelines course treats macros as something to avoid in this style of model; use grouping, custom views, and a cover-sheet index.

If the case gives you a messy file, the first two minutes are structural: freeze the year row, confirm column C is the same year everywhere, and find the Assumptions block. Do not start forecasting until the grid is aligned.