347 647 9001+1 714 797 8196Request a Call
Call Me

Financial Modeling Tutorial: Waterfall Graphs

December 9, 2013

Ever wondered about how changes in some parameters are best represented by any MS-Excel plot? For instance, consider a commodity whose prices change annually. The question constantly troubling my mind was how to represent the changes in the most suitable form.

As it were, I stumbled upon the best possible solution to my problem. The waterfall graph in MS-Excel!

As per our example, consider the following data table. Price of a commodity at launch was $200 and changed annually as below. $200 is the initial price. Subsequent changes are shown in the following intervals. $225 is the final price of the commodity.

#Financial Modeling using Excel

Make the elaborate table required for the waterfall chart.

#Financial Modeling using Excel

Use following formulas:

For C2: =B2

For D3: =MIN(SUM(B$2:B2),SUM(B$2:B3))

#Financial Modeling using Excel

For E3: =MAX(B3,0)

For F3: =-MIN(B3,0)

Copy the above formulas up to D7,E7 and F7.

Next select the data omitting price column and plot the stacked column chart.

#Financial Modeling using Excel

Make the following formatting:

1) Remove the grid lines.

2) Format the blank column and use white color both in shape fill and shape outline.

3) Remove the legend.

4) The axis title can be inserted by going to layout menu and choosing title axis option.

#Financial Modeling using Excel

Eventually, the required graph will be obtained as below.

#Financial Modeling using Excel

This approach of using the formulas for making the data table and plotting the corresponding graph seems to be tedious as it involves using some cumbersome formulas. Thus, an alternate method has been suggested with much simpler formulas for data creation.

So, step in my boots and be ready to steal the skills for another method. Consider the same data table as used above.

#Financial Modeling using Excel

Elaborate the data table as shown in the figure below for plotting the graph.

#Financial Modeling using Excel

Use the following formulas in data creation.

C2: =B2

C8: =SUM(B2:B7)

D3: =SUM(B$2:B2)

E3: =SUM(B$2:B3)

Now, copy the formulas of D3 and E3 up to D7 and E7 respectively.

Select the data omitting the price column and insert a line graph.

#Financial Modeling using Excel

In the legend, right click on the End and change the graph type to column.

#Financial Modeling using Excel

Next, select any line. Go to layout up/down bars

#Financial Modeling using Excel

Make the following formatting:

1) Remove grid lines.

2) Delete the legend.

3) Format the lines and remove the color. Right click on any line shape outline no outline.

4) Change the color of up/down bars. Right click on bar shape fill

5) Insert the axis title as explained above.

Eventually, the graph obtained will look like the image below.

#Financial Modeling using Excel But then, I came across a situation where I had to plot the similar compound graph for two different items. The compound stacked column or compound line graph may also be plotted using similar method. I am demonstrating a method to plot compound graph using line graph method.

Consider data table as shown below.

#Financial Modeling using Excel

Formulas used are as under.

E3: =sum(B$2:C2)

F3: =sum(B$2:C3)

G3: =sum(B$2:C3)

H3: =sum(B$2:D3)

Copy the above formula up till E7, F7, G7 and H7.

Next, select the A1-B8 and E1-H8. Insert the line graph.

#Financial Modeling using Excel

Then, transfer the before B and after B to the secondary axis. Right click on the line format data series series options secondary axis. Delete the secondary axis.

#Financial Modeling using Excel

Next, select after B or before B line and insert up/down bars from layout menu.

Then, select before A or after A line and insert up/down bars from layout menu.

#Financial Modeling using Excel

Make the following formatting:

1) Remove grid lines.

2) Delete the legend.

3) The plot lines should have no outline.

4) Change color of the up/down bars.

#Financial Modeling using Excel

Depending upon the type of data that is required to be presented and user’s interest, the method of approach can be altered.


About the Author

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 42126