When and how to use a waterfall chart for variance
Use a waterfall chart when additive components explain the change from a starting total to an ending total. Quantify mutually exclusive drivers, ensure their signed sum reconciles exactly, keep start and end anchored, order bars by a logical causal or reporting sequence, label units and values directly, and retain an explicit residual instead of forcing unexplained variance into a weak category.
The decision hiding in the data
Waterfalls are often built from overlapping categories or disconnected commentary. The bars look explanatory even when drivers double-count the same movement or fail to reach the reported ending total.
When to use a waterfall
| Question | Recommended view | Why |
|---|---|---|
| What explains change in a total? | Waterfall | Shows additive bridge |
| Which category is largest? | Sorted bar | Supports ranking |
| How did a metric move over time? | Line | Preserves sequence |
| How do two measures relate? | Scatter | Shows paired observations |
From the field · Portfolio reporting manager
Build a portfolio return bridge for flows, fees, market movement, and currency
A portfolio's opening and closing values differ because of deposits, withdrawals, fees, asset performance, and exchange rates. Plotting the net change alone confuses investment return with investor cash flows.
Columns used in the example
account · date · opening_value · cash_flow · fee · market_pnl · currency_effect · closing_value
Follow it with your own file
- Choose a consistent valuation basis and reporting currency.
- Separate external cash flows from performance components.
- Order drivers logically from opening to closing value.
- Keep any unexplained remainder visible instead of forcing it into another bar.
Check: Opening value plus every signed bridge component must equal closing value within the approved rounding tolerance. Compare component totals with source valuations and cash records.
Build the result step by step
- Define endpoints: Fix the starting and ending values, units, scope, period, and sign convention.
- Calculate driver contributions: Use non-overlapping rules for volume, price, mix, timing, scope, or other mechanisms.
- Reconcile and order: Confirm start plus signed drivers equals end, then choose a stable logical sequence.
- Design for explanation: Direct-label values, distinguish totals, explain color semantics, and annotate the material drivers.
Waterfalls explain additive change
A waterfall is appropriate for a bridge: prior revenue to current revenue, budget to actual, opening to closing cash, or gross to net. It is not ideal for independent category ranking.
If driver interactions are material, state the allocation method because price and mix effects may not be uniquely separable.
Use color consistently
Increasing and favorable are not synonyms. Higher cost increases the numerical value but may be unfavorable. Decide whether color encodes direction or business favorability and say so.
Totals should have a distinct neutral treatment so readers can separate endpoints from drivers.
Before touching the file
- Drivers must be additive and mutually exclusive.
- The bridge must reconcile to the ending value.
- Order should follow mechanism or reporting logic, not visual convenience.
- Keep an unexplained residual visible.
Where to look for exceptions
- The final bar differs from the displayed actual.
- Several bars describe the same records.
- A residual is hidden in Other.
- Positive colors mean favorable on one page and merely increasing on another.
Before the result leaves your desk
- Endpoints fixed
- Drivers exclusive
- Signs correct
- Bridge reconciles
- Residual visible
- Colors explained
Doing these steps in Prepyr
Upload a CSV working copy in Prepyr. Describe the result you need: Use a waterfall chart to reconcile an opening value, quantified drivers, and a closing value. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Waterfall charts · Variance analysis · Annotations · PDF reporting
A compact visual explanation of how quantified drivers move a total from one state to another, with no arithmetic gap hidden by design.
Questions readers usually ask next
Can a waterfall contain percentages?
Yes if components are truly additive on that percentage scale; percentage-point changes are often clearer.
How many bars should it have?
Keep material drivers and group the long tail transparently; too many bars defeat the explanatory purpose.
What is the residual bar?
It is the portion not explained by quantified drivers and should remain visible until resolved.