1 00:00:06,340 --> 00:00:07,400 Hello everyone. 2 00:00:07,660 --> 00:00:14,350 In this video we will discuss how to create and interpret waterfall charts waterfall charts are used 3 00:00:14,800 --> 00:00:20,960 to visualize sequential positive or negative changes from your starting point or the base value what 4 00:00:21,040 --> 00:00:27,820 Tata commonly used in financial analysis to discuss how net values arrived through gains or losses over. 5 00:00:28,430 --> 00:00:31,920 But to create a waterfall there are two different ways. 6 00:00:32,080 --> 00:00:37,870 Creating a waterfall chart is much easier in Excel 2016 because Microsoft has included waterfall as 7 00:00:37,870 --> 00:00:39,940 a starting option. 8 00:00:39,940 --> 00:00:50,810 You can see that option and insert and there is this internal waterfall chart but this option is not 9 00:00:50,810 --> 00:00:56,790 available in earlier versions so in this video we will show you how to create a waterfall chart both 10 00:00:56,790 --> 00:00:57,950 ways. 11 00:00:57,960 --> 00:01:04,380 First by using this option and then without using this option let us do it the easy way first. 12 00:01:04,530 --> 00:01:12,030 Using this example in this example we have a profit and loss statement offers small company. 13 00:01:12,030 --> 00:01:17,220 This company has a revenue of hundred thousand and a net profit of eighteen thousand five hundred fifty. 14 00:01:17,830 --> 00:01:22,700 But how did we reach from hundred thousand to eighteen thousand five hundred fifty. 15 00:01:22,710 --> 00:01:24,700 This table contains that information. 16 00:01:24,950 --> 00:01:30,600 But to make it more visually appealing and easy to understand we will select the table and create a 17 00:01:30,600 --> 00:01:31,430 waterfall. 18 00:01:33,190 --> 00:01:36,810 We go to insert select the waterfall table 19 00:01:39,840 --> 00:01:43,590 will have to specify which of these are the base values. 20 00:01:43,590 --> 00:01:48,140 So we will click on this chart go to format materials and intellect. 21 00:01:48,210 --> 00:01:55,010 This base value go to charting options and content as total. 22 00:01:56,180 --> 00:01:58,460 Similarly will have to select 70000. 23 00:01:58,490 --> 00:02:02,650 Also this is the total value 24 00:02:05,560 --> 00:02:13,680 again thirty thousand twenty six thousand five hundred and eighteen thousand five hundred fifty 25 00:02:16,730 --> 00:02:19,890 you can see from a hundred thousand. 26 00:02:19,970 --> 00:02:27,170 There was a fall of 30000 and we reached the stage of 70000 growth profit. 27 00:02:27,260 --> 00:02:33,740 Then there was a fall of 40000 due to operating expenses and we reached an embittered or the operating 28 00:02:33,740 --> 00:02:35,500 income of 37. 29 00:02:36,410 --> 00:02:38,880 Similarly in the next part. 30 00:02:38,880 --> 00:02:43,590 There is an increase of of 3000 interest income which is shown in blue collar. 31 00:02:44,390 --> 00:02:51,180 So you can take a lot of folds and raise from current orange or blue to some other color whatever you 32 00:02:51,180 --> 00:02:51,920 want to do. 33 00:02:52,010 --> 00:02:57,920 Using this format chart options this is where the fallen Excel to 2015. 34 00:02:58,100 --> 00:03:03,770 If we wanted to make this job in a little versions of Excel we'll have to manually created using other 35 00:03:03,770 --> 00:03:05,810 types of chart. 36 00:03:05,980 --> 00:03:12,420 You can see here that if you have to create this site we'll have to use bar or column charts as the 37 00:03:12,420 --> 00:03:14,860 final values the deform of bars 38 00:03:18,210 --> 00:03:26,480 one of the problem that we will faces with regards to this 30000 value you can note is that 30000 entry 39 00:03:26,480 --> 00:03:30,170 is not starting from zero but it is starting from 70000. 40 00:03:31,340 --> 00:03:38,990 Similarly the last value seven thousand nine hundred fifty it is starting from eighteen thousand five 41 00:03:38,990 --> 00:03:40,800 hundred fifty mark. 42 00:03:40,850 --> 00:03:47,300 Now if you assume another 70000 in the invisible column bloody 30000 mark. 43 00:03:48,590 --> 00:03:56,380 Similarly imagine 30000 column below the forty golden Mark and twenty five thousand five hundred below 44 00:03:56,450 --> 00:03:58,840 the 3000 more. 45 00:03:59,090 --> 00:04:07,160 You can see that we have converted this waterfall into style column chart stat column chart and then 46 00:04:07,200 --> 00:04:09,400 two or more series into one column. 47 00:04:09,600 --> 00:04:13,090 Not case we will hide this extra cities to get the desired. 48 00:04:14,250 --> 00:04:17,540 Let's start with creating the data for our stat column. 49 00:04:21,500 --> 00:04:26,710 I'll have two cities cities one will be the invisible cities 50 00:04:29,710 --> 00:04:32,350 and second to this will be the series I want to display. 51 00:04:36,700 --> 00:04:44,460 So for all the base values these dispensaries will be the exact value of their base value. 52 00:04:45,190 --> 00:04:52,080 So invisibility this will be zero for all these values and dispensaries will be deep value that that 53 00:04:52,100 --> 00:04:53,740 base value has 54 00:05:01,170 --> 00:05:12,040 so what cross property to 70000 for every dollar it is quite detailed in for profit before tax twenty 55 00:05:12,060 --> 00:05:21,390 six thousand five hundred for profit after tax you be thousand five hundred. 56 00:05:22,630 --> 00:05:36,570 Now I applaud this tack column for these values only a go to insert select down columns. 57 00:05:36,690 --> 00:05:45,030 You can see that we have defined base values for our chart next what we have to do is for the second 58 00:05:45,030 --> 00:05:51,170 point we'll have to show the dip in this value of hundred thousand to seventy. 59 00:05:51,210 --> 00:05:59,590 So the little dip of 30000 if you see the waterfall chart often 2016 there is a small bar of 30000. 60 00:06:00,240 --> 00:06:08,120 And the second place so this is what we will create at the second one. 61 00:06:08,310 --> 00:06:10,120 I'll go back. 62 00:06:10,340 --> 00:06:18,490 So what does the invisible value is actually the 70000 which is already there with the base value. 63 00:06:19,350 --> 00:06:22,220 I am dead these places will be 30000. 64 00:06:22,700 --> 00:06:23,900 So let's create the 65 00:06:27,510 --> 00:06:30,330 won't be told 32 million 66 00:06:34,500 --> 00:06:44,170 so you can see that the 30000 the day that we wanted to show is now in orange color and this blue series. 67 00:06:44,290 --> 00:06:49,840 If we make it invisible by going to the chart options and selecting novel 68 00:06:52,650 --> 00:06:56,720 so that is gone we can see from the base value of hundred thousand. 69 00:06:56,830 --> 00:07:03,500 There is a fall of 30000 and we have reached the base value of 70000. 70 00:07:03,940 --> 00:07:10,220 So this is how we will be creating each and every change in our base value. 71 00:07:10,780 --> 00:07:14,380 So we all talk already base value to great 72 00:07:17,150 --> 00:07:20,120 so that we can easily identify which are the base values 73 00:07:23,230 --> 00:07:30,110 we have to individually select all these base values and make them great. 74 00:07:31,020 --> 00:07:34,960 And we have our first fall of 30000. 75 00:07:34,960 --> 00:07:38,380 The next one is a fall of 40000. 76 00:07:38,450 --> 00:07:41,230 And after this fall it will reach 30000. 77 00:07:41,290 --> 00:07:45,700 So the Invisible Cities will have a value of 30000 78 00:07:50,010 --> 00:07:52,470 and they display cities will have a value of 40000 79 00:07:55,440 --> 00:07:56,460 Lindsay. 80 00:07:56,480 --> 00:07:57,970 This is what we intended to create. 81 00:07:59,750 --> 00:08:10,260 Next we are at a position of 30000 and there is a dip of 4500 after this dip. 82 00:08:10,320 --> 00:08:17,640 There will be a base value of twenty five thousand five hundred so that base value will be at invisible 83 00:08:17,640 --> 00:08:27,880 street value twenty five thousand five hundred and divisible cities will be 4500. 84 00:08:28,080 --> 00:08:35,670 You can see from 30000 we have decreased 4500 and we have reached to a point of twenty five thousand 85 00:08:35,700 --> 00:08:36,290 five hundred 86 00:08:39,030 --> 00:08:39,780 after this point. 87 00:08:39,780 --> 00:08:48,180 There is an increase of 3000 this increase will have a base value of twenty five thousand five hundred 88 00:08:48,590 --> 00:08:52,560 an acre based value to twenty eight thousand five hundred. 89 00:08:52,560 --> 00:08:56,120 So the base value for this increase is twenty five thousand five hundred. 90 00:08:57,690 --> 00:09:00,780 And it is a visible cities of 3000 91 00:09:04,060 --> 00:09:08,150 and now the new base value is twenty eight thousand five hundred. 92 00:09:08,810 --> 00:09:15,170 So that twenty eight thousand five hundred value will be decreased by 2000 and it will lead to a new 93 00:09:15,170 --> 00:09:16,910 base of twenty six thousand five hundred. 94 00:09:17,840 --> 00:09:23,900 So this interest expense of 2000 rupees will have an invisible value of twenty six thousand five hundred 95 00:09:29,210 --> 00:09:37,470 which it will reach by a dip of 2000 from the earlier base value of 28 500 hundred this increase. 96 00:09:37,470 --> 00:09:45,800 We will change the color to blue to market separately so to go through it again. 97 00:09:45,930 --> 00:09:48,690 We started at 30000. 98 00:09:48,720 --> 00:09:57,270 There was a dip of 4500 so this 4500 column started at the point of twenty five thousand five hundred 99 00:09:58,290 --> 00:10:06,030 and it reached up to 20000 from this value of twenty five thousand five hundred. 100 00:10:06,040 --> 00:10:11,230 There was an increase due to interesting commentary told him and we reached a point of twenty eight 101 00:10:11,230 --> 00:10:16,170 thousand five hundred at this point of 28 thousand five hundred. 102 00:10:16,180 --> 00:10:22,660 There was a dip of 2000 and we reached the final value of twenty six thousand five hundred. 103 00:10:22,740 --> 00:10:27,680 Now there's a lost pardon meaning of including tax. 104 00:10:28,100 --> 00:10:36,050 We had a base value of twenty six thousand five hundred and it will reach to eighteen thousand five 105 00:10:36,050 --> 00:10:40,220 hundred fifty eight in total and 550 will become our invisible value 106 00:10:45,020 --> 00:10:53,450 and seven thousand nine hundred fifty will be the visible value and this will give us the final profit 107 00:10:53,570 --> 00:10:58,280 after tax of eighteen thousand five hundred fifty. 108 00:10:58,290 --> 00:11:04,200 You can copy this chart and pasted in the little sheet to compare 109 00:11:09,050 --> 00:11:10,700 loom out a little bit. 110 00:11:14,950 --> 00:11:19,060 You can see that these two charts are identical. 111 00:11:19,060 --> 00:11:26,430 You can change the login values the name of these columns to get exactly the same jar 112 00:11:29,270 --> 00:11:33,490 So these are the two ways that you can create a waterfall chart. 113 00:11:33,490 --> 00:11:41,170 If you are on Excel 2016 or later it is really simple but if you are using Excel 2013 or earlier you'll 114 00:11:41,190 --> 00:11:42,020 have to do this. 115 00:11:42,040 --> 00:11:45,510 The longer we that for this with you. 116 00:11:45,710 --> 00:11:52,280 If you find this visualization technique interesting and want to learn more about visualization techniques 117 00:11:52,550 --> 00:11:56,990 and excel in general please go check out our radiology cities. 118 00:11:57,950 --> 00:12:00,260 On YouTube and Udemy thanks.