Waterfall charts are a useful graph to show variances between a start and end point. In the following table we have the operating income of 2020FYE and the quarterly increase and decreases of operating income of the US and Canadian divisions of the company. 2021 operating income is the sum of 2020 operating income and the quarterly net changes to the operating income across both regions. We can show the quarterly variances in a waterfall chart.
To create the Waterfall chart using MS Excel charts, select the data, click on Insert from the top bar and under Charts, select Insert Waterfall chart. Excel automatic waterfall chart is inserted. Click on the start and end bars (202FYE and 2021FYE) and set them as total. Double click the chart for Format Chart Area window to appear. Double click the 2020FYE bar (starting point) for Format Data Point window to pop up and check the ‘Set as total’ box. This will change the bar color to grey indicating it is the total starting point. Click on the 2021FYE bar (ending point) and repeat the same process to set as Total.
This type of waterfall chart in excel does not allow for Stacked Waterfall charts in which case we insert both Canadian and US operating income divisions by quarter.
A Stacked Column chart will allow for creation of a Stacked Waterfall chart.
In order to use the Stacked Column chart, we prepare the data as below.
In order to prepare the data for the Stacked Waterfall chart, we add two columns for the starting and end data points (2020FYE and 2021FYE total operating income), a column for a beginning of period balance for each quarter (BOP_Balance), US and CA increases and decreases columns (Increase_US, Decrease_US, Increase_CA, Decrease_CA). The beginning balance of each period is the base or the invisible portion of the stacked column charts that includes the prior period increases but excludes the current period increases and includes the current period decreases. Once the stacked column chart is inserted, double click this bar for Format Data Point window to pop up and selected No fill under the Fill section to make the column invisible.
It is important to show increases and decreases of each data point separately so that they show up as separate columns in the stacked chart in each quarter. For example in cell I27, operating income increase for Q1/21 in US is displayed via the following formula: (D27>0)*D27. If the value in D27 is positive, the (D27>0) returns a TRUE or 1, which is then multiplied by the value of the cell D27. If it is negative, it returns a FALSE and multiplied for the value in D27, leads to a zero. The process is repeated for all increases and decreases to populated Increase_US, Decrease_US, Increase_CA, Decrease_CA columns. Increases and decreases are both shown as a positive number in separate columns. Hence the related formula for operating income decreases includes a minus sign such as formulas in column J and L.
Once the table is populated, delete the zeros so that they do not show as zeros in the data labels in the stacked column chart once data labels are added to the stacked column chart. Remove Start, End and BOP_Balance from the legend and adjust colors of bars as desired by clicking on the bars for Format Data Series window to pop up and change colors under Fill in the window.
Sources:
Thanks for this run through. If we want start (2020FYE) and end (2021FYE) figures broken up by US and Canada details as well, how can this be accomplished?