Hello everyone.
In this video we will discuss how to create and interpret waterfall charts waterfall charts are used
to visualize sequential positive or negative changes from your starting point or the base value what
Tata commonly used in financial analysis to discuss how net values arrived through gains or losses over.
But to create a waterfall there are two different ways.
Creating a waterfall chart is much easier in Excel 2016 because Microsoft has included waterfall as
a starting option.
You can see that option and insert and there is this internal waterfall chart but this option is not
available in earlier versions so in this video we will show you how to create a waterfall chart both
ways.
First by using this option and then without using this option let us do it the easy way first.
Using this example in this example we have a profit and loss statement offers small company.
This company has a revenue of hundred thousand and a net profit of eighteen thousand five hundred fifty.
But how did we reach from hundred thousand to eighteen thousand five hundred fifty.
This table contains that information.
But to make it more visually appealing and easy to understand we will select the table and create a
waterfall.
We go to insert select the waterfall table
will have to specify which of these are the base values.
So we will click on this chart go to format materials and intellect.
This base value go to charting options and content as total.
Similarly will have to select 70000.
Also this is the total value
again thirty thousand twenty six thousand five hundred and eighteen thousand five hundred fifty
you can see from a hundred thousand.
There was a fall of 30000 and we reached the stage of 70000 growth profit.
Then there was a fall of 40000 due to operating expenses and we reached an embittered or the operating
income of 37.
Similarly in the next part.
There is an increase of of 3000 interest income which is shown in blue collar.
So you can take a lot of folds and raise from current orange or blue to some other color whatever you
want to do.
Using this format chart options this is where the fallen Excel to 2015.
If we wanted to make this job in a little versions of Excel we'll have to manually created using other
types of chart.
You can see here that if you have to create this site we'll have to use bar or column charts as the
final values the deform of bars
one of the problem that we will faces with regards to this 30000 value you can note is that 30000 entry
is not starting from zero but it is starting from 70000.
Similarly the last value seven thousand nine hundred fifty it is starting from eighteen thousand five
hundred fifty mark.
Now if you assume another 70000 in the invisible column bloody 30000 mark.
Similarly imagine 30000 column below the forty golden Mark and twenty five thousand five hundred below
the 3000 more.
You can see that we have converted this waterfall into style column chart stat column chart and then
two or more series into one column.
Not case we will hide this extra cities to get the desired.
Let's start with creating the data for our stat column.
I'll have two cities cities one will be the invisible cities
and second to this will be the series I want to display.
So for all the base values these dispensaries will be the exact value of their base value.
So invisibility this will be zero for all these values and dispensaries will be deep value that that
base value has
so what cross property to 70000 for every dollar it is quite detailed in for profit before tax twenty
six thousand five hundred for profit after tax you be thousand five hundred.
Now I applaud this tack column for these values only a go to insert select down columns.
You can see that we have defined base values for our chart next what we have to do is for the second
point we'll have to show the dip in this value of hundred thousand to seventy.
So the little dip of 30000 if you see the waterfall chart often 2016 there is a small bar of 30000.
And the second place so this is what we will create at the second one.
I'll go back.
So what does the invisible value is actually the 70000 which is already there with the base value.
I am dead these places will be 30000.
So let's create the
won't be told 32 million
so you can see that the 30000 the day that we wanted to show is now in orange color and this blue series.
If we make it invisible by going to the chart options and selecting novel
so that is gone we can see from the base value of hundred thousand.
There is a fall of 30000 and we have reached the base value of 70000.
So this is how we will be creating each and every change in our base value.
So we all talk already base value to great
so that we can easily identify which are the base values
we have to individually select all these base values and make them great.
And we have our first fall of 30000.
The next one is a fall of 40000.
And after this fall it will reach 30000.
So the Invisible Cities will have a value of 30000
and they display cities will have a value of 40000
Lindsay.
This is what we intended to create.
Next we are at a position of 30000 and there is a dip of 4500 after this dip.
There will be a base value of twenty five thousand five hundred so that base value will be at invisible
street value twenty five thousand five hundred and divisible cities will be 4500.
You can see from 30000 we have decreased 4500 and we have reached to a point of twenty five thousand
five hundred
after this point.
There is an increase of 3000 this increase will have a base value of twenty five thousand five hundred
an acre based value to twenty eight thousand five hundred.
So the base value for this increase is twenty five thousand five hundred.
And it is a visible cities of 3000
and now the new base value is twenty eight thousand five hundred.
So that twenty eight thousand five hundred value will be decreased by 2000 and it will lead to a new
base of twenty six thousand five hundred.
So this interest expense of 2000 rupees will have an invisible value of twenty six thousand five hundred
which it will reach by a dip of 2000 from the earlier base value of 28 500 hundred this increase.
We will change the color to blue to market separately so to go through it again.
We started at 30000.
There was a dip of 4500 so this 4500 column started at the point of twenty five thousand five hundred
and it reached up to 20000 from this value of twenty five thousand five hundred.
There was an increase due to interesting commentary told him and we reached a point of twenty eight
thousand five hundred at this point of 28 thousand five hundred.
There was a dip of 2000 and we reached the final value of twenty six thousand five hundred.
Now there's a lost pardon meaning of including tax.
We had a base value of twenty six thousand five hundred and it will reach to eighteen thousand five
hundred fifty eight in total and 550 will become our invisible value
and seven thousand nine hundred fifty will be the visible value and this will give us the final profit
after tax of eighteen thousand five hundred fifty.
You can copy this chart and pasted in the little sheet to compare
loom out a little bit.
You can see that these two charts are identical.
You can change the login values the name of these columns to get exactly the same jar
So these are the two ways that you can create a waterfall chart.
If you are on Excel 2016 or later it is really simple but if you are using Excel 2013 or earlier you'll
have to do this.
The longer we that for this with you.
If you find this visualization technique interesting and want to learn more about visualization techniques
and excel in general please go check out our radiology cities.
On YouTube and Udemy thanks.