Waterfall chart

The Waterfall Chart is a report that is often used in controlling, and is ideal for displaying inflows and outflows of money over a given period of time. On this occasion, it usually shows the situation at the beginning and at the end of the period. Earlier it was done as a combination of two series of data, one of which was specially formatted in order to get the effect of the “waterfall”, and from the 2016 version it can be made directly from the Excel.

They say that no company has failed because it lacked profit, and many failed because they did not have enough cash. For this reason, in order to illustrate the application of the “Waterfall” chart, we will use the flow and cash flow diagram for a period of one year.

The table on the basis of which the chart is drawn should have two columns. In the first there are dates, and in the second inflow or outflows. The first one will be filled out with the starting dates for the month in a year (01.01.2015, 01.02.2015, etc.) plus the first day of the next year (01.01.2016). The first value in the inflow / outflow table (01.01.2015) should be the initial value of the cash in hand or in the account. Then the monthly values of inflow and outflow are followed. The last value is the sum of the starting value with everyone else, or how much money we had at the end of the period (the first day of the next year). In our example, we calculate it using the formula:

=SUM(B2:B13)

To create a Waterfall chart we need to select the values in the table, then from the Insert ribbon we select the Recommended Charts option, and in the dialog window that opens click on All Charts. At the bottom of the list of chart types we will find the Waterfall, which we need to select and then click on the OK button.

Once the chart is displayed, we can change it by adding a title, legend ор море elements to improve its look. We can do this manually or choose some of the predefined styles. In the end, we just need to emphasize which values represent the beginning and the end of the period. We do this by first clicking on the column representing the start of the period, and then right-clicking the context menu in which the Format Data Point option is located. By activating this option on the right side of the window, a panel opens to select the Set As Total option. In the end, this action should only be repeated by the column that marks the end of the period.