Study guide

6.3 Named Ranges and Workbook Hygiene

Financial Modeling & Valuation Analyst FMVA

Track your progress as you read

Finished sections get a check mark.

1 Introduction

Current Exam Facts Program Path and How to Use This Guide

2 Chapter 1: Accounting Foundations

1.1 Accounting Principles 1.2 Reading Financial Statements 1.3 Accounting Decisions in Models

3 Chapter 2: Time Value and Capital Budgeting

2.1 Time Value of Money 2.2 NPV, IRR, and Decision Rules 2.3 Capital Budgeting Cash Flows

4 Chapter 3: Cost of Capital

3.1 Capital Structure 3.2 CAPM, Beta, and Cost of Equity 3.3 WACC

5 Chapter 4: Profitability and Returns

4.1 Profitability and Margins 4.2 Returns and DuPont Analysis 4.3 Per-Share Metrics and Growth

6 Chapter 5: Liquidity, Leverage, and Cash Flow

5.1 Liquidity and Working Capital Metrics 5.2 Leverage, Coverage, and Credit 5.3 Cash Flow Statement Analysis

7 Chapter 6: Excel Fundamentals

6.1 Shortcuts, Setup, and Navigation 6.2 Core Formulas and Error Handling 6.3 Named Ranges and Workbook Hygiene

8 Chapter 7: Excel Lookups and Financial Functions

7.1 Lookup Functions 7.2 Conditional Aggregations 7.3 Financial Functions

9 Chapter 8: Excel What-If Analysis and Auditing

8.1 Goal Seek and Data Tables 8.2 Formula Auditing and Protection

10 Chapter 9: Financial Modeling Guidelines

9.1 Model Structure and Layout 9.2 Color Conventions and Formatting 9.3 Integrity Checks and Documentation

11 Chapter 10: Three-Statement Modeling

10.1 Articulating the Three Statements 10.2 Building the Income Statement 10.3 Building the Balance Sheet and Cash Flow Statement

12 Chapter 11: Circularity and Model Audit

11.1 Circular References and Iteration 11.2 Auditing and Balancing a 3-Statement Model

13 Chapter 12: Operational Modeling

12.1 Revenue and Operating Drivers 12.2 Working Capital Modeling 12.3 Capex and Depreciation

14 Chapter 13: Debt Schedules, Scenarios, and Sensitivity

13.1 Debt and Interest Schedules 13.2 Scenario Analysis 13.3 Sensitivity Analysis

15 Chapter 14: Valuation Foundations

14.1 Valuation Methods and Enterprise Value 14.2 Unlevered Free Cash Flow

16 Chapter 15: DCF Valuation

15.1 DCF Structure, WACC, and Mid-Year Convention 15.2 Terminal Value and the Equity Bridge

17 Chapter 16: Comparable Valuation

16.1 Trading Comps and Multiples 16.2 Precedent Transactions and the Football Field

18 Chapter 17: Budgeting and Forecasting

17.1 Budget Types 17.2 Forecasting Techniques 17.3 Rolling Forecasts and Variance Analysis

19 Chapter 18: Presentation and Visuals

18.1 Chart Types and Selection 18.2 Dashboards and Data Visualization 18.3 PowerPoint and Pitchbooks 18.4 Presenting Model Outputs

20 Chapter 19: Professional Ethics and Qualitative Analysis

19.1 Professional Ethics 19.2 Qualitative Business Analysis

6.3 Named Ranges and Workbook Hygiene

Key Takeaways Explain This Topic AI

Name the assumption drivers (Tax_rate, WACC, g_terminal), not every forecast cell — =FCFF_N*(1+g_terminal)/(WACC-g_terminal) should have three names, not 200 named Revenue_2025 cells that cannot fill right.

Write one formula per row and copy it across years; a hardcoded 2028 revenue of $1,200,000 fails a row-difference audit and will not respond to a growth-rate shock.

Do not bury numbers in formulas except explained 1, 0, 365, 12, or 365/12; write =EBIT*(1-Tax_rate), never =EBIT*(1-0.25).

Excel Tables fit lists (comps, invoices, tranches); three-statement year-across grids stay as ordinary ranges so fill-right still works.

Dates are serial numbers (31-Dec-2026 is a date, not the text 2026); freeze the header row; auditability — visible inputs and formulas that copy — beats Print Area on a timed case.

Last updated: August 2026

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

  • Name Cell Value
  • Tax_rate Assumptions!B4 0.25
  • WACC Assumptions!B5 0.095
  • g_terminal Assumptions!B6 0.025
  • 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] .

  • Ordinary range Excel Table
  • Expand when you add a row Manual Automatic
  • Formula language =D12/D11 =[@EBIT]/[@Revenue]
  • Fill behavior You copy/fill Calculated column fills itself

Best for Forecast grids you copy right across years Data lists: comps, G/L dump, debt tranches, monthly actuals

Risk Forgotten last-row formula Structured refs that do not copy "across years" the way a model grid does

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:

  • Allowed inside a formula Why Example
  • 1 and 0 Identity, flags, keep a number unchanged =C12*(1+Growth) , =Draw*Switch
  • 365 or 360 Day-count, labeled =AR/(Revenue/365) for AR days
  • 12 Months in a year, labeled =Annual_interest/12
  • 365/12 Average month length, labeled monthly interest or stub periods
  • Display scales Prefer actual units in the model Better: store 0.25, not 25 and /100

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:

  • Where are the inputs? (One assumption sheet, colored as inputs — color conventions are a later chapter.)
  • Is each forecast row one formula? (Fill-right test; Go To Special → row differences.)

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.)

Do dates, units, and sheet names tell the truth?

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.

Loading diagram... Names on drivers, ranges on forecast grids, Tables on lists