Income statements, or Profit and Loss statements, are an essential part of any business. However, when displayed as a large table of numbers, it can be difficult to draw out the information needed to answer simple questions such as “how has my revenue changed compared to last year?” or “which region has the largest contribution to labour expenses?”.
Waterfall charts are a great solution for this. In our last blog, we discussed how to use waterfalls for time analysis. Today, we will demonstrate how to use waterfalls for profit and loss statements, with intuitive scenario and account formatting.
The workbook for the example described in this article can be downloaded here:
What is an income statement waterfall chart
An income statement waterfall chart is a visual representation of income and expenditures over a specific period, typically used to illustrate the components of a company's profit and loss (P&L) statement. The chart starts with a beginning net revenue or income value and displays floating bars to represent different expense categories.
The purpose of the income statement waterfall chart is to provide a clear and sequential depiction of how net revenue is affected by various expenses, leading to the final outcome of total cash flow. Each bar in the chart represents a specific category of expenses, and the length of the bars corresponds to the magnitude of the expenses.
At the end of the waterfall chart, there is usually a total cash flow bar, representing the overall financial result or bottom line for the business. This type of visualization helps stakeholders and decision-makers understand the key contributors to the company's financial performance over the specified time frame.
For high-growth companies, especially those backed by venture capital, where profitability might not be achieved immediately, this type of chart is sometimes referred to as a "cash burn bridge." It highlights how much money is being expended or "burned" during the given period, emphasizing the company's cash flow dynamics.
How to create a vertical waterfall chart
Insert a dynamic chart and set the chart type to a bar waterfall. Add the relevant accounts on to categories.
This has drawn a waterfall that accumulates the amount for each account. In this case, some accounts are actually expenses, and should be plotted as having a negative contribution to the total. Other accounts are totals and should not be added on as an additional contribution.
There are two ways the chart can be configured to plot as we desire.
Right-click options
Firstly, right-clicking on a bar gives several options under the waterfall sub-menu. Here we can define an account as a total or subtotal. If the account is an expense then we can set it to negate the value.
This is a simple method for making a small number of changes to a single chart. However, in most cases, defining business rules will be a significantly better approach overall.
Business rules for automatic P&L waterfall chart formatting
You may find that you have several charts in the workbook that need to be configured, or you use this account structure in multiple reports. For this case, XLCubed provides ‘Business Rules’ as a way of defining how members should be plotted. These rules are automatically applied to all charts in a workbook without the need to manually click and edit individual chart elements.
Business rules are inserted from the business chart menu in the ribbon. This opens a dialog from which you can define a rule and which members it applies to. Once you click OK, a new sheet is inserted into the workbook. This is where all the rules are stored and can be edited if needed. Once fully set up, this sheet can also be shared across workbooks.
For further help using business rules, please see our wiki.
Refresh the chart to apply these rules.
Comparing scenarios
The resulting chart is looking great, but becomes more useful when we add a comparison scenario. Open the chart taskpane and add the Scenario hierarchy on to columns. Here you can select the scenarios you wish to compare (or this could even be driven from a slicer selection).
We can also add business rules to define these scenarios. Use the dialog to add new business rules, or directly edit the XLCubedBusinessRules sheet to define which members are Actuals, Plan and Forecast values. You can also specify the current year, meaning all earlier years will use the Previous Year formatting.
The chart for each scenario now applies the standard IBCS Solid, Outlined, Hatched (= Actual, Plan, Forecast) formatting.
Variance charts
Now that we are comparing two different scenarios, we want an easy way to read the variance between the two. To do this, we will add both an absolute and a relative variance chart alongside.
Copy the waterfall chart, placing the copy to the right of the original. Change the chart type to a bar variance chart. Set the base value as the actual scenario and comparison value as the second scenario being displayed. If using slicers to make the display dynamic, you will need to make the slicers output to a range and then drive the comparison value selection from the range.
We do not need the member names displayed on the category axis on this chart. To hide them, set the font colour to ‘None’ and drag to resize so that it does not take up any space.
Now make a copy of this chart and place it alongside with the relative variance chart type.
Maintaining visual consistency with shared scales
An important concept in IBCS is to ensure information is presented in the most truthful and easily understood way possible. So, to properly make visual comparisons, identical scales should be used for identical measure units.
In our case, the absolute variance chart is currently very misleading. The lengths of its bars are not on the same scale as the waterfalls, and so indicates a much larger variance than is the truth.
We can set the variance chart to use the same scale as the waterfall charts. In the chart properties, on the Axis Scales tab, set the scale to ‘Match To’ the waterfall chart.
Formatting a waterfall chart for profit and loss statements
To add headers to the variance charts, add the measure on to the chart’s columns. Right click on the column header and ‘Rename’.
You can quickly switch to the IBCS style in the dynamic chart ribbon. This applies some default formatting, left aligning category labels and removing the value axis in place of data labels.
This gives the final profit and loss statement below.
