Tutorial: Vertical Waterfalls with Variances

This tutorial will explain the steps to create the charts shown below. This is example based on the vertical waterfall IBCS template.

A copy of the workbook can be downloaded here.

Data

This tutorial will use simple Excel data and on which a Pivot View has been created.

Business Rules

Add business rules to set which accounts are expenses or totals, and which year is the current.

The resulting rules sheet should look like the below. Be sure the year is entered as text rather than a number (i.e. using an apostrophe in front).

Creating the Waterfall Chart

On a new sheet, insert a new waterfall chart.

In the taskpane, add the Account hierarchy to Catgories, and select each account in the relevant order. Add the Year hierarchy to Columns and select the two years.

Use the range picker to size the chart. Sizing the chart to an Excel range will help align the variance charts later.

Expense accounts are being plotted correctly as having a negative contribution to the total. The previous year is formatted in a lighter colour and the totals and subtotals are formatted in bold with a top border. This has been applied automatically because of the business rules.

The subtotal count needs to be corrected on the 'Operating expenses' member. Right-click on the waterfall bar and select 'Custom subtotal count'. Set this to 5.

Formatting

In the Dynamic Chart Ribbon, set the style to 'IBCS'. This will apply several standard formatting settings:

• Left align category labels
• Remove values axis
• Hide gridlines
• Show data labels

Apply any other desired formatting. In this case:

• Remove chart border, either in the ribbon or chart properties
• Turn off back and forward controls and zoom on charts in the chart properties. This removes the the arrows and reset icons in the top left of the chart which aren't being used.

Creating the Variance Charts

Copying charts

Right click on the waterfall chart to copy it, moving the copy to the right of the original. A new chart could be created instead, but copying means all the account members are already selecting and reduces the amount of formatting to be applied.

On the new chart, change the type to absolute variance. Move the Year hiearchy to the 'Compare by' section and set the current year as the Base value and the previous year as the Comparison value.

Layout settings

In the chart properties, hide the categories labels. This can done by selecting the 'X' next to the label font editor under Titles and Axes.

Reduce the 'Y Axis Text Width' either in the layout properties or by resizing on the chart.

Matching scale and alignment

Again in chart properties, under the Axis Scales tab, set the scale to match to the first chart. This ensures that both the waterfall and absolute variance chart are plotted on the same scale, which is vital for accurate analysis.

Because the variance chart doesn't have a hierarchy set in Columns, it is not displaying a header, causing the bar size and spacing not to match with the waterfall chart. To fix this, move the Measures hierarchy on to Columns. This will display the header 'Amount' - change this by right clicking on the header and renaming.

Alternatively, hide the column labels in the waterfall (Properties > Title and Axes > Column Font. Select the 'X' to make hidden) and add text in the cells above the charts. This will allow for a dynamic header over the variance charts if needed.

Relative Variance

Copy the absolute variance chart to the space to the right, and set the chart type to relative variance on the new chart.

Remove the 'Match to' scale setting on the relative variance chart and update the header label to show "%".