Waterfall charts in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a financial analyst at a retail company.

📋 Request: Your manager wants a clear visual report showing how different factors contributed to the change in net profit from last year to this year.

📊 Data: You have data showing last year's net profit, various increases and decreases in revenue and expenses, and this year's net profit.

🎯 Deliverable: Create a waterfall chart in Excel that visually breaks down the changes from last year's net profit to this year's net profit.

Sample Data

Category	Amount
Last Year Net Profit	50000
Increase in Sales	15000
Decrease in Cost of Goods Sold	7000
Increase in Marketing Expenses	-4000
Increase in Administrative Expenses	-3000
Other Income	2000
This Year Net Profit	67000

1

Step 1: Organize your data in Excel with two columns: 'Category' and 'Amount' as shown in the sample data.

No formula needed for this step.

Expected Result

Data is clearly listed with categories and their corresponding amounts.

2

Step 2: Select the entire data range including headers (A1:B8).

No formula needed.

Expected Result

Data range is selected for chart creation.

3

Step 3: Go to the Insert tab, click on 'Insert Waterfall or Stock Chart', then choose 'Waterfall'.

No formula needed.

Expected Result

A basic waterfall chart appears showing the changes from last year to this year.

4

Step 4: Set the first and last bars as totals to represent the starting and ending net profit.

Right-click the first bar (Last Year Net Profit), choose 'Set as Total'. Repeat for the last bar (This Year Net Profit).

Expected Result

The first and last bars are shown as totals, anchoring the waterfall chart.

5

Step 5: Format the chart for clarity: add data labels, adjust colors for increases (green) and decreases (red).

Use Chart Tools to add data labels and change bar colors manually.

Expected Result

Chart clearly shows positive changes in green, negative changes in red, with data labels on each bar.

Final Result

Waterfall Chart

| Last Year Net Profit | +15000 | +7000 | -4000 | -3000 | +2000 | = This Year Net Profit |
|        50000         | 65000  | 72000 | 68000 | 65000 | 67000 |         67000         |

✓Net profit increased from 50,000 last year to 67,000 this year.

✓Sales and cost reductions contributed positively to profit.

✓Marketing and administrative expenses reduced profit.

✓Other income added a small positive amount.

Bonus Challenge

Add a slicer to the waterfall chart to filter changes by category type (e.g., Revenue, Expenses, Other).

Show Hint

Add a new column categorizing each row, then convert data to a table and insert a slicer based on that category.