18.2 Dashboards and Data Visualization
Key Takeaways
- Excel Data Visualization and Dashboards is 2026 core course 13 (about 3 hours); the course builds two dashboards with waterfalls, area charts, sparklines, football fields, and variance formatting.
- One question per view: Apex's $2.65 million gross-profit variance waterfall is not the same view as Alder's football field.
- KPI tiles sit above actual versus budget versus rolling forecast; a trend-versus-last-year chart is not an FP&A control pack.
- Traffic lights flag exceptions — Apex's $0.50 per unit COGS rate miss is red even when revenue is green — and the signed number or arrow must remain for colorblind readers.
- Dashboards must refresh from the model; paste-as-values without a timestamped freeze is how $86.1 million stays on the exhibit after volume changes.
Why Dashboards Are Core, Not Decoration
Excel Data Visualization and Dashboards (CFI, led by Tim Vipond, about 3 hours, NASBA 2 CPE credits on the public course page) is a required core. The 2026 program page lists it as course 13 of 15. The course has you build two complete dashboards from scratch. Dashboard 1 is an FP&A performance pack: gross-profit chart, profit-margin chart, waterfall, expense area chart, sparklines, variance analysis, conditional formatting, and a P&L and balance-sheet summary. Dashboard 2 is a valuation and market pack: stock-price chart, football field, stacked revenue, growth-rate line, camera tool, and concatenated titles. That is the exam's working definition of a dashboard: linked Excel views that answer a business question, not a pasted screenshot of the entire model.
Prep courses (Excel for Data Preparation & Visualization, Data Visualization & Dashboards — The Basics) can still appear as prep content on the final. CFI's final-exam article says the exam covers core and prep; electives are not tested. Do not study Data Visualization for Long-Term Planning (the planned elective replacement for Advanced Excel Formulas & Functions, effective date not determined as of the 3 June 2026 refresh article) as if it were core.
One Question Per View
A view is one screen, one slide, or one dashboard panel. It should answer one question. If you cannot write that question in one sentence, split the view. Apex's FY2026 pack versus Alder's valuation pack:
| View | The one question | Not also |
|---|---|---|
| KPI header | Did we beat the lock on revenue, gross profit, and cash? | Twelve cost-center pies |
| Variance waterfall | Why is gross profit $2.65 million above budget? | Peer trading multiples |
| Landing strip | Will remaining-year forecast still hit the annual lock? | WACC build |
| Valuation football field | Where does DCF sit versus comps and the market? | Monthly SKU units |
CFI's reviewing Dashboard 1 and reviewing Dashboard 2 lessons are that discipline: each chart earns its pixels. Putting Apex's SG&A traffic lights on Alder's football field is two questions, two audiences, and one unreadable sheet.
KPI Tiles
A KPI tile is a large number, a comparison, and a direction. Four tiles that belong on Apex's header (FY2026 actual versus the $80 million lock from Chapter 17):
| KPI | Actual | Budget / lock | Variance | Label |
|---|---|---|---|---|
| Revenue | $86.1 m | $80.0 m | +$6.1 m | Favorable |
| Gross profit | $34.65 m | $32.0 m | +$2.65 m | Favorable |
| Gross margin | 40.2% | 40.0% | +20 bp | Favorable |
| COGS / unit | $24.50 | $24.00 | +$0.50 | Unfavorable |
The tiles are not the analysis. They are the headline. The waterfall and the price/volume split (Chapter 17) sit underneath so a CFO cannot stop at +$6.1 million revenue and miss the $1.05 million COGS rate miss (100,000 extra units is not the rate miss; $0.50 × 2,100,000 units is).
Display units and decimals: revenue in $ millions to one decimal; per-unit cost in dollars and cents; margin in percent to one decimal. Mixing $86,100,000 on one tile and $34.65 m on the next is a unit error. CFI's adjusting display units lesson exists for this reason.
Actual Versus Budget Versus Forecast
FP&A dashboards need three series, not two:
- Actual — what happened (Apex FY2026 revenue $86.1 million).
- Budget (lock) — what was authorized ($80.0 million). Variances are scored here.
- Forecast (rolling) — what we now think. On 31 March 2026 the rolling 12-month forecast is April 2026 through March 2027, not a shrinking stub of the calendar year (Chapter 17.3).
A chart that shows only actual versus last year is a trend chart. It cannot tell you whether you beat the board-approved plan. A chart that restates the budget to actual volume and then shows a zero variance has replaced the lock with a flexible budget — legal as a second column (Chapter 17), illegal as a silent overwrite of the lock.
Investment-banking models usually have forecast versus prior forecast, not a corporate lock. Do not force a budget column onto a DCF football field. Do not hide the lock on an FP&A pack.
Worked landing view as of 31 March 2026 (teaching stub, not a CFI published calendar): year-to-date actual plus remaining-year forecast versus the $80 million lock. If Q1 actual is $15.5 million against an 18% seasonal weight on $86.1 million ($15.5 million is close to $15.498 million), the dashboard still scores Q1 against the lock, not against the new full-year outlook. The rolling column answers a different question.
Traffic Lights and Conditional Formatting
Traffic lights are conditional-formatting color rules: green / amber / red against a threshold. CFI's Dashboard 1 lessons include conditional formatting and special symbols (up/down arrows, checks) on a variance table.
Apex rules you can defend:
| Metric | Green | Amber | Red |
|---|---|---|---|
| Revenue variance | ≥ 0 versus lock | −2% to 0% | < −2% |
| COGS rate variance | ≤ 0 (cost at or below standard) | 0 to +$0.25 / unit | > +$0.25 / unit |
| Cash versus $2.0 million minimum | ≥ $2.0 m | $1.5–$2.0 m | < $1.5 m |
Apex's $0.50 per unit COGS rate miss is red even though revenue is green. That is the point of a traffic light: it stops the reader from averaging a good tile with a bad one.
Exam trap: coloring every cell. If twelve lines are red, nothing is red. Color exceptions, not the entire grid. Do not use red and green as the only encoding — colorblind readers need the arrow or the signed number as well. CFI's using special symbols lesson is that backup encoding.
Do not invent a CFI-required red/amber/green template. The exam tests whether the color matches a stated rule and whether a favorable revenue tile can coexist with an unfavorable cost tile.
Do Not Chart Junk
Junk is ink that does not answer the view's question:
- 3-D columns, pie explosion, shadows, gradient fills
- Gridlines on every dashboard when data labels already give the number
- A rainbow legend of 12 series
- Plotting the entire trial balance
- A dual-axis of revenue and WACC
- Sparklines on a 2-period series (a sparkline needs a history; CFI uses them on a performance-summary table with several periods)
CFI's chart-formatting lessons — display units, profit-margin formatting, waterfall formatting, area-chart formatting — are subtraction drills: strip chart junk until the series and the labels remain. A dashboard that looks busy is not more executive. It is harder to audit.
Refreshable from the Model
A dashboard that is paste-as-values from last Monday is a photograph. It will drift from the model the first time WACC or volume changes. CFI's professional standard — and the FMVA case-study standard — is a dashboard linked to the model:
- A calculation sheet holds actuals, budget, and forecast as formulas (or an Excel Table).
- Charts use those cells as their source data.
- KPI tiles are formulas (
=Actual!B12), not86100000typed on the dashboard. - Traffic-light rules reference the same variance cells.
- If you must paste values (a client PDF pack, a frozen monthly close), document the timestamp and the file name, and keep a linked live tab.
The camera tool in CFI's Dashboard 2 is a linked picture of a range — it refreshes when the source range changes. It is not a screenshot. Concatenate (or TEXTJOIN / &) builds chart titles from cells so the title cannot disagree with the tile: a title that reads Revenue $86.1 million vs budget $80.0 million should be built from the same two cells as the KPI tiles.
Exam trap: a case that says update volume to 2,200,000 while your dashboard still shows $86.1 million because you pasted values. The model flexed; the exhibit did not. That is an integrity fail (section 18.4).
Paste-as-values is not banned. A process is required: freeze date, source file, who signed off, and a live file that remains formula-driven. Without that process, paste-as-values is how last week's $80 million lock and this week's $86.1 million actual silently coexist on the same tile.
Putting Apex on a CFI-Style Dashboard
Wire Chapter 17 into Dashboard 1:
- KPI row: the four tiles above.
- Clustered column: regional actual versus budget (section 18.1).
- Waterfall: $2.65 million gross-profit bridge (volume +4.0, price +2.1, COGS quantity −2.4, COGS rate −1.05).
- Area or stacked: product mix Core / Premium / Seasonal.
- Sparkline on a 12-month revenue stub if the case gives months; skip if the case is annual only.
- Variance table with arrows and conditional formatting on the COGS rate line.
- P&L summary — compact, not 80 rows. CFI Dashboard 1 includes a P&L and balance-sheet summary for that reason.
Dashboard 2, if the case is Alder, is the valuation pack in section 18.4: stock price versus $26.02 implied, football field, stacked revenue, growth line. Do not mix the two workbooks' questions.
Exam traps on dashboards
- Treating Excel Data Visualization and Dashboards as optional because Presentation is only about 8%. It is core course 13.
- Studying the elective visualization replacement as if it were tested.
- One sheet that tries to answer every question at once.
- Actual versus last year with no budget column on an FP&A control pack.
- Coloring every cell, or color without a signed number.
- Paste-as-values dashboards that do not update when the case changes a driver.
- Chart junk: 3-D, twelve-slice pies, dual-axis of dollars and WACC.
What does one question per view mean on a CFI-style dashboard?
A case changes volume to 2,200,000 units. Which dashboard treatment stays consistent with the model?
Apex revenue is $6.1 million above the lock and COGS is $0.50 per unit above standard. Traffic-light treatment that matches CFI-style exception formatting is: