6.3 Named Ranges and Workbook Hygiene

Named Ranges for Assumptions — Not for Every Cell

A named range is a label Excel can use in formulas instead of Assumptions!$B$5. =C12*(1+Growth) is readable. =C12*(1+$B$5) is not, once B5 is off-screen.

Name the assumption block: tax rate, WACC, terminal growth, shares outstanding, revenue growth, COGS %, a scenario index. Do not name every forecast cell. A file with 400 names (IS_D12, IS_E12, Rev_2026) is slower to audit than a file with 15 names and a frozen stub column. Names are a map of drivers, not a second address system.

How to name (standard Excel): select B5, type Growth in the Name Box (left of the formula bar), Enter. Or Formulas → Define Name. Ctrl+F3 opens Name Manager (CFI's public shortcut sheet lists this on both Windows and Mac). F3 pastes a name into a formula while you are editing.

Worked example: WACC and tax

NOPAT: =EBIT*(1-Tax_rate)
Terminal value: =FCFF_N*(1+g_terminal)/(WACC-g_terminal)

If EBIT is $150,000 and FCFF in the last explicit year is $80,000:

• NOPAT = $150,000 × (1 − 0.25) = $112,500
• TV = $80,000 × 1.025 / (0.095 − 0.025) = $82,000 / 0.07 = $1,171,429

If you later change WACC to 10.5% in the yellow input cell, every formula that uses the name WACC updates. You do not hunt through DCF!F20. New WACC 0.105 → TV = $82,000 / (0.105 − 0.025) = $82,000 / 0.08 = $1,025,000. That $146,429 swing is the point of a named driver.

Scope: a name can be workbook-level (default) or sheet-level. FMVA models should keep driver names workbook-level so Tax_rate means the same thing on IS, BS, and DCF. Duplicate names with different scopes (IS!Tax_rate versus DCF!Tax_rate) are an exam-day trap: one sheet still uses 25% after you "changed tax to 21%."

Don't name every cell. If you name D12 Revenue_2025 and E12 Revenue_2026, you cannot fill a formula right with a relative year walk. Names of single forecast cells fight the one-formula-copied-across rule below. Name Growth, not Revenue_2026.

Name hygiene: no spaces (Tax_rate not Tax rate — Excel will convert a space to Tax_rate anyway), no names that look like cells (WACC1 is fine; FY24 can be read as a column-row address in some contexts), and delete names you no longer use. Name Manager's filter for Names with Errors is how you find a name still pointing at a deleted sheet (#REF!).

Excel Tables Versus Ordinary Ranges

An Excel Table (Insert → Table, or Ctrl+T) is a structured range with a header row, automatic expansion, and structured references such as [@Revenue] and Table1[Year].

Three-statement forecast grids (years across columns, line items down rows) usually stay as ordinary ranges with one formula filled right. Peer comps, lease schedules, and transaction lists are better as Tables because a new row should inherit the formula.

Do not convert the entire income statement into a Table "for hygiene." Tables want a single header row and columns-as-fields. A model wants years-as-columns. Mixing those layouts is how @ references break when you insert a 2029 column.

A Table does belong on a comps tab: tickers down the rows, EV, EBITDA, EV/EBITDA as columns. Adding Peer 12 should extend the EV/EBITDA calculated column without you remembering to copy row 11. That is the opposite layout from IS!D12:M12.

One Formula Per Row, Copied Across

The hygiene rule that saves cases: write the year-1 (or first forecast) formula once, then copy or fill right. Every year in that row is the same logic. Differences belong in the assumption row, not as a unique formula in 2028.

Bad: 2024–2027 use =C12*(1+Growth) but 2028 is =1200000 because "that's the management target."

Good: a row of growth rates (6%, 6%, 6%, 6%, 8%) sitting in the assumption block, and one formula =C12*(1+D5) filled across, where D5 is that year's growth.

Worked example: Apex revenue

Opening revenue (2024 actual) in C12 = $1,000,000. Growth in D5:M5 = 6% each year except 2028 (column H) = 8%.

Formula in D12: =C12*(1+D5) fill to M12.

• 2025 = 1,000,000 × 1.06 = $1,060,000
• 2026 = 1,060,000 × 1.06 = $1,123,600
• 2028 = prior × 1.08, not a typed $1,200,000

If 2028 is hardcoded at $1,200,000, Go To Special → Row differences will flag it, and a 50 bp WACC shock will not flow through 2028 revenue. That is a broken model, not a management overlay. Overlays belong in a separate assumption (a management add-on row) that the single formula references: =C12*(1+D5)+D6, with D6 = 0 in most years and a labeled $ amount in 2028.

The same rule applies down a column of products. Write C11 as =$B11*C$10 once, fill right and down. If Product C 2027 is a unique formula, you will forget it when price in B13 changes.

No Hardcoded Numbers in Formulas

CFI-style modeling: formulas contain references and operators, not buried constants. The exception list is short and must be explained in a comment or a nearby label:

Not allowed: =C12*1.06, =EBIT*(1-0.25), =FCFF/(0.095-0.025), =B20/1000000 as a silent millions scale.

Why 1 and 0 get a pass: *(1+g) is the growth identity, and a 0/1 switch is a flag, not a hidden tax rate. Why 365/12 gets a pass if labeled: there is no assumption sheet in the world that should hide "how long is a month" in three different places. Put Days_in_year = 365 and, if you need an average month, =Days_in_year/12 on the assumption sheet, then reference that cell. Typing 365/12 once next to a label Average month (actual/365) is the fallback when you are on a scratch sheet during the exam.

Worked example: days payable

COGS = $600,000. AP = $50,000. AP days = AP / (COGS / 365).

=B30/(B18/Days_in_year) with Days_in_year = 365 on the assumption sheet, labeled actual/365. Result = 50,000 / (600,000 / 365) = 30.4 days.

If you write =B30/(B18/365) in one place and =B30/(B18/360) in a credit schedule, the two statements disagree by about 1.4 days and nobody sees why. Putting 365 in a named cell makes the day-count policy visible. 365/12 ≈ 30.4167 is the standard average-month length when you convert annual interest to monthly without a real calendar. Label it. Do not type 30.

Exam trap: =EBIT*(1-0.21) because "we just cut the statutory rate." Six months later the assumption block still says 25% and the DCF uses 21%. The file now has two tax rates. Named Tax_rate = 0.21 would have updated NOPAT, deferred tax, and the WACC tax shield together.

Sheet Naming, Dates as Numbers, Frozen Headers

Sheet names should be short, unique, and boring: Assumptions, IS, BS, CFS, Debt, D&A, WACC, DCF, Comps. Not Sheet1, Copy of IS (2), or FINAL_v7_USE_THIS. Spaces work but make references clumsier ('Copy of IS (2)'!B5). Underscores or no spaces fill faster.

A name is not a sentence. Three_Statement_IS is worse than IS plus a cover sheet that says Income statement. CFI's exam article already wants extra windows and a scratch sheet; you cannot ALT W W through eight tabs named variations of "model."

Dates are numbers. Excel stores 31 December 2026 as a serial (under the Windows 1900 date system, 31-Dec-2026 is serial 46387). =EOMONTH(C8,12) and =YEAR(C8) work because C8 is a real date, not the text 2026. Text looks like a date until you subtract two "dates" and get #VALUE!.

For annual models, put year-end dates in the header row (12/31/2026) formatted as YYYY, then derive year with =YEAR(C8). Do not type the year as the integer 2026 in a date function and also as a label that is really text. Integer 2026 minus integer 2025 equals 1, which can masquerade as "one year" until you need EOMONTH or an actual/365 fraction.

Freeze the header row (and usually the stub column) as in Section 6.1. Hygiene includes being able to see that column M is 2033 while you edit M40. Freeze is not decoration; it is how you avoid writing a 2033 formula against a 2026 stub you scrolled off-screen.

Print Area Is Not the Point — Auditability Is

Print Area, page breaks, and "fit to 1 page wide" are presentation tools. They are not a model-quality test. A file can print on two pages and still be unauditable, or print badly and still be a clean model.

Auditability means a second person (or you on exam day) can answer:

1. Where are the inputs? (One assumption sheet, colored as inputs — color conventions are a later chapter.)
2. Is each forecast row one formula? (Fill-right test; Go To Special → row differences.)
3. Are constants named or at least referenced, not buried? (Go To Special → constants on formula sheets should be nearly empty except 1, 0, and labeled day-counts.)
4. Do dates, units, and sheet names tell the truth?
5. Does the balance sheet balance, and does ending cash on the CFS match cash on the BS?

Set a print area if you must export a PDF. Do not spend case-study minutes on print area while #REF! sits in the revolver. CFI's pacing (about 45 minutes per case, 60 at most) is for calculating and checking, not for landscape page setup.

Cover-sheet hygiene that does help: file name, date, currency (USD thousands), and a note that Excel 2016 or newer is required so IFS (and XLOOKUP, if you use it) are available. CFI's exam requires Excel 2016+; do not build the case in a function the exam environment cannot calculate.

A 60-second close-out on every case: Name Manager for #REF! names, Go To Special → constants on IS/BS/CFS, Ctrl+End on each sheet, and a look at whether column M still has the same formula as column D. That close-out is workbook hygiene. Print Area is not.