Hello, everyone, in this video, we will discuss how to create an elaborate waterfall charts, waterfall
charts are used to visualize sequential, positive or negative changes from your starting point or the
base value, what are commonly used in forensic analysis to discuss how values arrived through gains
or losses time, but to create a waterfall?
There are two different ways.
Creating a waterfall chart is much easier in 2016 because Microsoft has included Waterfall as a starting
option.
You can see that option and insert.
And there is this intervertebral, Chuck.
But this option is not available in earlier versions.
So in this video, we will show you how to build a waterfall chart both ways, first by using this option
and then without using this option.
So let us do it the easier way first using this example.
In this example, we have a profit and loss statement of a small company.
This company has a revenue of 100000 and a net profit of eighteen thousand five hundred fifty.
But how did we reach from one 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 got to insert the word level.
We'll have to specify which of these are the base values, so we will then click on this chart, go
to format materials, and then to let this base value go to trading options.
And click the total, so we will have to select 70000 and also.
So this is the total value.
Again, thirty thousand twenty six thousand five hundred.
And eighteen thousand five hundred fifty.
You can see from one hundred thousand there was a fall of 30000 and we reached the stage of seventy
thousand lost of it, then there was a fall of 40000.
You do operating expenses and we reached a bit or the operating income of 30000.
Similarly, in the next part.
There is an increase of 3000 interest income, which is shonen bluegill.
So you get a lot of Foulds and raise from current orange or blue to some other color, whichever you
want to do using this format adoption's.
This is wonderful in itself to 2016, if we want to make this chart in earlier versions of Excel, we'll
have to manually created using other types of charts.
You can see here that if you have to create this site, will have to use bar charts at the final values
are the DiFonzo bars.
One of the problem that we will face is with regards to this 30000 value, you can notice that 30000
entry is not starting from zero, but it is starting from 70000.
Similarly, the lost value, seven thousand nine hundred fifty.
It is starting from eighteen thousand five hundred fifty mark.
Now, if you assume another 70000 in invisible column, bloody 30000 mark.
Similarly, imagine 30000 column bloody 40000 mark and twenty five thousand five hundred bloody three
thousand more.
You can see that we have converted this waterfall into a style column chart.
That column chart adds two or more trees into one column, in our case, we will hate this extra series
to decide.
Let's start with grading the data for our START column.
I'll have to see this.
So this one will be the invisible cities.
And the second series will be decided, I want to display.
So for all the base values, the dispensaries will be the exact value of that base value.
So invisibility, this will be zero for all the values and displays.
This will be deep value that that base value has.
So for growth to seventy thousand for a better, it is thirty thousand for profit before tax, thirty
six thousand five hundred and for profit after tax you eighteen thousand five hundred to be.
Now, I applaud this dad column for these values only.
I go to insert.
Down columns.
You can see that we have the base values for our child.
Next, what we have to do is got the second point, we'll have to show the tape in this valley of hundred
thousand to seventy thousand.
So there the depth of 30000.
If you see the waterfall chart of 2016, there is a small bar of 30000 at the second place.
So this is what we will create a decision point and go back.
So for this, the invisible value is actually this a little.
Which is already there with the debased value and the display series will be 30000.
So let's create the.
Seventy thousand and thirty thousand.
So you can see that the 30000 that we wanted to show is now in orange color and this blue series, if
we make it invisible by going to the child auctions and selecting norful.
So that is gone.
You can see from the base value of a hundred thousand, there is a fall of 30000 and we have reached
a base value of 70000.
This is how we will be creating.
Each and every change in our best value, so we also can learn the best value.
Two, great.
So that we can easily identify which are the best values.
We have to individually select all these best values and.
Make them brief 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 Kids will have a rally of 30000.
And the display series will have a value of 40000.
You can see this is what we intended to create.
Next.
We are a nation of 30000.
And there is a depth of 4500 after this day, there'll be a base value of twenty five thousand five
hundred.
So that base value will be invisible to this value, twenty five thousand five hundred.
And the visibilities 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 5500 an acre based value to the Daytona 500.
So the best value for this increase is twenty five thousand five hundred and there is a visible series
of 3000.
And now the new best value is greater than 500, so that twenty eight thousand five hundred value will
be decreased by 2000 and it will reach 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 Olympics rally of 20000 500.
This increase, we will change the color to blue.
The 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
30000.
From this rally of twenty five thousand five hundred, there was an increase due to interest income
of three thousand and we reached a point of twenty eight thousand five hundred.
At this point or two thousand five hundred, there was a dip of 2000 and we reached a final rally of
twenty six thousand five hundred.
Now, there is a last party meeting of including tax.
We have a base value of twenty six thousand five hundred, and it will reach to eighteen thousand five
hundred fifty eighteen thousand five hundred fifty will become an invisible value.
And seven thousand nine hundred fifty will be the visible value.
And this will be Vasti final profit after tax of eighteen thousand five hundred fifty, even copied
and pasted in the sheet to compare.
Zoom out a little bit.
You can see that these two charts are identical.
You can change the little values, the names of these columns to get exactly the same chart.
So these are the two ways that you can create a waterfall chart if you are on Excel twenty sixteen hours
later.
It is really simple, but if you are using Excel 2013 or earlier, you will have to do this the longer
we.
That's it for this video, if you find this visualization technique interesting and want to learn more
about visualization techniques and excel in general.
Please go check out our lecture series.
On YouTube and Udemy, thanks.