course bg
EduPristine>Blog>Financial Modeling Tutorial: Waterfall Graphs

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 Author

avatar EduPristine

EduPristine is a member of Adtalem Global Education (NYSE: ATGE), a global education provider headquartered in the United States. Adtalem is a 3 billion dollars (20,000 crores) company that has about 9 institutions and companies with more than 16,000 employees spread across 145 locations. Adtalem takes pride in training 142,000 degree-seeking students all over the world.The organization's purpose is to empower students to achieve their goals, find success and make inspiring contributions to our global community. EduPristine is one of India's leading training providers in Analytics, Accounting, Finance, Healthcare, and Marketing. Founded in 2008, EduPristine has a strong online platform and network of classrooms across India and caters to self-paced learning and online learning, in addition to classroom learning


Interested in this topic?

Our counsellors will get in touch with you with more information about this topic.

* Mandatory Field

Post ID = 42126