Waterfall analysis in Excel: build a checked bridge

Waterfall analysis in Excel starts with one check: the opening balance plus signed movements must equal the closing balance. Build the chart only after that identity holds, and mark every balance column, including intermediate subtotals, as a total.

Start with the measure and its source rows

Choose one measure, one currency and two comparison points. This example explains a US distributor's operating profit change from August to September 2026, with both months measured under the same accounting policy. The monthly profit reports show an opening comparison value of $300,000 and a closing comparison value of $322,000.

The movement schedule explains the difference. Each row must represent a distinct effect, carry its source reference and have exactly one driver label. Increases in operating profit are positive; decreases are negative. An expense increase therefore has a negative sign, even when the expense ledger stores it as positive.

Put this reviewed schedule on a sheet named Movements, with headers in row 1:

The total is a check beneath the six data rows; exclude that total row from all formulas. Retain the underlying order, payroll or ledger references behind the schedule. If the commercial effects need decomposition, use the separate price-volume-mix formulas; this chart procedure starts with already calculated, mutually exclusive contributions.

Arrange balances, movements and a subtotal

On a sheet named Bridge, put the independently reported September operating profit, 322000, in E2. Lay out labels in A and values in B as follows. Spreadsheet formulas appear in the final column; enter them in the indicated B cells.

Microsoft documents SUMIFS as adding cells meeting the supplied criteria. Here, exact driver labels collect source movements into chart bars. Expand every range together when adding rows, and check that every source label appears in the bridge.

The subtotal is 300,000 + 65,000 - 22,000 = $343,000. It reports the running balance after those movements; it contributes no additional profit. The remaining effects bring that balance to 343,000 - 18,000 - 8,000 + 5,000 = $322,000.

Insert the chart and set its totals

Select A1:B9, including the headers, and choose Insert > Waterfall. Then select the individual August column, right-click and choose Set as Total. Repeat for the subtotal and September columns. Select a single column, not the whole series.

Microsoft's waterfall guide describes the running-total presentation and the Set as total setting. The opening, subtotal and closing bars should start at the horizontal axis; signed movement bars should float between successive balances. Covirage is not affiliated with Microsoft.

Add dollar data labels and a title naming the measure and periods. The subtotal should look like a balance, with a label describing which effects precede it. Preserve the business sequence instead of sorting all bars by size. A visually tidy bridge is still misleading if the sequence or signs imply a different calculation.

Prove the bridge outside the chart

Use a separate difference cell:

=B2+SUM(Movements!C2:C7)-$E$2

The result is 300,000 + 22,000 - 322,000 = 0. This compares the source movements with the independently reported closing figure, rather than testing a closing figure calculated from those same movements.

Check the chart's mapped movement bars too:

=SUM(B3:B4,B6:B8)-SUM(Movements!C2:C7)

That result must also be zero. Keep a source-reference review alongside this aggregate check: an omitted positive row and an omitted negative row can cancel. Dollar values here are whole dollars, so no rounding adjustment is needed. For another focused workbook investigation, use ad hoc analysis in Excel; for unusual source values, use outlier analysis in Excel.

Where it goes wrong

• Summing subtotal rows as movements. Opening, subtotal and closing balances are checkpoints. Adding them into a movement total counts the same profit repeatedly.
• Wrong expense signs. A higher cost lowers profit. Translate ledger debit and credit conventions into the bridge's economic sign before grouping.
• Overlapping effects. A new-account contribution already included in another sales effect cannot appear again as a separate driver.
• A calculated closing value posing as evidence. Deriving the ending bar from the movements makes a zero difference inevitable. Compare with the independent profit report.
• Mixing measures or periods. Cash collections cannot explain operating profit without the relevant accounting adjustments. Both comparison periods need the same scope.

Check your source movements in Covirage

Bring the movement workbook and the reports behind its opening and closing figures to Covirage. Covirage maps the columns, its tools compute and check the source-group comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.

See Covirage for FP&A reporting for the supporting reporting workflow.