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.