1 00:00:06,270 --> 00:00:13,230 Hello, everyone, in this video, we will discuss how to create an elaborate waterfall charts, waterfall 2 00:00:13,230 --> 00:00:19,410 charts are used to visualize sequential, positive or negative changes from your starting point or the 3 00:00:19,410 --> 00:00:26,490 base value, what are commonly used in forensic analysis to discuss how values arrived through gains 4 00:00:26,730 --> 00:00:29,840 or losses time, but to create a waterfall? 5 00:00:29,970 --> 00:00:31,320 There are two different ways. 6 00:00:32,050 --> 00:00:38,400 Creating a waterfall chart is much easier in 2016 because Microsoft has included Waterfall as a starting 7 00:00:38,400 --> 00:00:38,820 option. 8 00:00:39,870 --> 00:00:42,750 You can see that option and insert. 9 00:00:44,660 --> 00:00:47,840 And there is this intervertebral, Chuck. 10 00:00:49,780 --> 00:00:52,210 But this option is not available in earlier versions. 11 00:00:53,520 --> 00:00:59,490 So in this video, we will show you how to build a waterfall chart both ways, first by using this option 12 00:00:59,910 --> 00:01:01,680 and then without using this option. 13 00:01:02,340 --> 00:01:05,580 So let us do it the easier way first using this example. 14 00:01:07,080 --> 00:01:10,980 In this example, we have a profit and loss statement of a small company. 15 00:01:11,960 --> 00:01:16,980 This company has a revenue of 100000 and a net profit of eighteen thousand five hundred fifty. 16 00:01:17,730 --> 00:01:22,170 But how did we reach from one hundred thousand to eighteen thousand five hundred fifty? 17 00:01:22,650 --> 00:01:24,420 This table contains that information. 18 00:01:24,900 --> 00:01:30,540 But to make it more visually appealing and easy to understand, we will select the table and create 19 00:01:30,540 --> 00:01:31,140 a waterfall. 20 00:01:33,120 --> 00:01:36,810 We got to insert the word level. 21 00:01:39,750 --> 00:01:45,780 We'll have to specify which of these are the base values, so we will then click on this chart, go 22 00:01:45,780 --> 00:01:51,800 to format materials, and then to let this base value go to trading options. 23 00:01:53,420 --> 00:01:58,940 And click the total, so we will have to select 70000 and also. 24 00:02:01,210 --> 00:02:02,590 So this is the total value. 25 00:02:05,390 --> 00:02:09,770 Again, thirty thousand twenty six thousand five hundred. 26 00:02:11,790 --> 00:02:13,680 And eighteen thousand five hundred fifty. 27 00:02:16,640 --> 00:02:25,310 You can see from one hundred thousand there was a fall of 30000 and we reached the stage of seventy 28 00:02:25,310 --> 00:02:29,330 thousand lost of it, then there was a fall of 40000. 29 00:02:29,330 --> 00:02:35,470 You do operating expenses and we reached a bit or the operating income of 30000. 30 00:02:36,350 --> 00:02:38,870 Similarly, in the next part. 31 00:02:38,900 --> 00:02:43,540 There is an increase of 3000 interest income, which is shonen bluegill. 32 00:02:44,330 --> 00:02:51,170 So you get a lot of Foulds and raise from current orange or blue to some other color, whichever you 33 00:02:51,200 --> 00:02:53,660 want to do using this format adoption's. 34 00:02:54,650 --> 00:03:01,880 This is wonderful in itself to 2016, if we want to make this chart in earlier versions of Excel, we'll 35 00:03:01,880 --> 00:03:04,550 have to manually created using other types of charts. 36 00:03:05,920 --> 00:03:13,210 You can see here that if you have to create this site, will have to use bar charts at the final values 37 00:03:13,210 --> 00:03:14,830 are the DiFonzo bars. 38 00:03:18,170 --> 00:03:26,060 One of the problem that we will face is with regards to this 30000 value, you can notice that 30000 39 00:03:26,060 --> 00:03:30,110 entry is not starting from zero, but it is starting from 70000. 40 00:03:31,250 --> 00:03:35,630 Similarly, the lost value, seven thousand nine hundred fifty. 41 00:03:36,590 --> 00:03:39,890 It is starting from eighteen thousand five hundred fifty mark. 42 00:03:40,790 --> 00:03:47,300 Now, if you assume another 70000 in invisible column, bloody 30000 mark. 43 00:03:48,500 --> 00:03:57,240 Similarly, imagine 30000 column bloody 40000 mark and twenty five thousand five hundred bloody three 44 00:03:57,350 --> 00:03:57,920 thousand more. 45 00:03:59,000 --> 00:04:04,250 You can see that we have converted this waterfall into a style column chart. 46 00:04:05,410 --> 00:04:11,790 That column chart adds two or more trees into one column, in our case, we will hate this extra series 47 00:04:11,980 --> 00:04:12,750 to decide. 48 00:04:14,190 --> 00:04:17,280 Let's start with grading the data for our START column. 49 00:04:21,350 --> 00:04:22,640 I'll have to see this. 50 00:04:23,890 --> 00:04:26,710 So this one will be the invisible cities. 51 00:04:29,590 --> 00:04:32,320 And the second series will be decided, I want to display. 52 00:04:36,620 --> 00:04:44,410 So for all the base values, the dispensaries will be the exact value of that base value. 53 00:04:45,140 --> 00:04:49,310 So invisibility, this will be zero for all the values and displays. 54 00:04:49,330 --> 00:04:53,750 This will be deep value that that base value has. 55 00:05:01,100 --> 00:05:12,080 So for growth to seventy thousand for a better, it is thirty thousand for profit before tax, thirty 56 00:05:12,080 --> 00:05:20,960 six thousand five hundred and for profit after tax you eighteen thousand five hundred to be. 57 00:05:22,580 --> 00:05:27,380 Now, I applaud this dad column for these values only. 58 00:05:29,940 --> 00:05:31,080 I go to insert. 59 00:05:33,640 --> 00:05:34,540 Down columns. 60 00:05:36,630 --> 00:05:40,830 You can see that we have the base values for our child. 61 00:05:41,910 --> 00:05:49,560 Next, what we have to do is got the second point, we'll have to show the tape in this valley of hundred 62 00:05:49,560 --> 00:05:51,150 thousand to seventy thousand. 63 00:05:51,150 --> 00:05:52,800 So there the depth of 30000. 64 00:05:53,490 --> 00:06:02,070 If you see the waterfall chart of 2016, there is a small bar of 30000 at the second place. 65 00:06:03,750 --> 00:06:08,940 So this is what we will create a decision point and go back. 66 00:06:10,280 --> 00:06:14,590 So for this, the invisible value is actually this a little. 67 00:06:15,930 --> 00:06:22,210 Which is already there with the debased value and the display series will be 30000. 68 00:06:22,620 --> 00:06:23,910 So let's create the. 69 00:06:27,550 --> 00:06:30,220 Seventy thousand and thirty thousand. 70 00:06:34,600 --> 00:06:44,430 So you can see that the 30000 that we wanted to show is now in orange color and this blue series, if 71 00:06:44,440 --> 00:06:49,810 we make it invisible by going to the child auctions and selecting norful. 72 00:06:52,600 --> 00:06:53,440 So that is gone. 73 00:06:53,710 --> 00:07:00,450 You can see from the base value of a hundred thousand, there is a fall of 30000 and we have reached 74 00:07:00,460 --> 00:07:03,300 a base value of 70000. 75 00:07:04,080 --> 00:07:05,500 This is how we will be creating. 76 00:07:07,120 --> 00:07:13,780 Each and every change in our best value, so we also can learn the best value. 77 00:07:13,820 --> 00:07:14,350 Two, great. 78 00:07:17,100 --> 00:07:20,130 So that we can easily identify which are the best values. 79 00:07:23,150 --> 00:07:27,770 We have to individually select all these best values and. 80 00:07:29,120 --> 00:07:39,620 Make them brief and we have our first fall of 30000, the next one is a fall of 40000, and after this 81 00:07:39,620 --> 00:07:41,160 fall it will reach 30000. 82 00:07:41,240 --> 00:07:45,710 So the Invisible Kids will have a rally of 30000. 83 00:07:49,860 --> 00:07:52,530 And the display series will have a value of 40000. 84 00:07:55,310 --> 00:07:57,920 You can see this is what we intended to create. 85 00:07:59,690 --> 00:08:00,350 Next. 86 00:08:01,810 --> 00:08:04,210 We are a nation of 30000. 87 00:08:05,480 --> 00:08:13,190 And there is a depth of 4500 after this day, there'll be a base value of twenty five thousand five 88 00:08:13,190 --> 00:08:13,550 hundred. 89 00:08:14,610 --> 00:08:20,340 So that base value will be invisible to this value, twenty five thousand five hundred. 90 00:08:22,320 --> 00:08:33,990 And the visibilities will be 4500, you can see from 30000, we have decreased 4500 and we have reached 91 00:08:33,990 --> 00:08:36,300 to a point of twenty five thousand five hundred. 92 00:08:38,910 --> 00:08:41,880 After this point, there is an increase of 3000. 93 00:08:43,020 --> 00:08:51,690 This increase will have a base value of 5500 an acre based value to the Daytona 500. 94 00:08:52,500 --> 00:09:00,020 So the best value for this increase is twenty five thousand five hundred and there is a visible series 95 00:09:00,030 --> 00:09:00,810 of 3000. 96 00:09:03,950 --> 00:09:12,140 And now the new best value is greater than 500, so that twenty eight thousand five hundred value will 97 00:09:12,140 --> 00:09:16,920 be decreased by 2000 and it will reach to a new base of twenty six thousand five hundred. 98 00:09:17,750 --> 00:09:23,870 So this interest expense of 2000 rupees will have an invisible value of twenty six thousand five hundred. 99 00:09:29,120 --> 00:09:34,910 Which it will reach by a dip of 2000 from the Olympics rally of 20000 500. 100 00:09:36,210 --> 00:09:39,510 This increase, we will change the color to blue. 101 00:09:40,800 --> 00:09:41,940 The market separately. 102 00:09:43,980 --> 00:09:47,830 So to go through it again, we started at 30000. 103 00:09:48,630 --> 00:09:51,010 There was a dip of 4500. 104 00:09:51,570 --> 00:09:59,520 So this 4500 column started at the point of twenty five thousand five hundred, and it reached up to 105 00:09:59,520 --> 00:10:00,240 30000. 106 00:10:01,750 --> 00:10:08,080 From this rally of twenty five thousand five hundred, there was an increase due to interest income 107 00:10:08,080 --> 00:10:12,490 of three thousand and we reached a point of twenty eight thousand five hundred. 108 00:10:13,390 --> 00:10:19,720 At this point or two thousand five hundred, there was a dip of 2000 and we reached a final rally of 109 00:10:19,720 --> 00:10:21,340 twenty six thousand five hundred. 110 00:10:22,550 --> 00:10:26,120 Now, there is a last party meeting of including tax. 111 00:10:28,040 --> 00:10:36,020 We have a base value of twenty six thousand five hundred, and it will reach to eighteen thousand five 112 00:10:36,020 --> 00:10:40,250 hundred fifty eighteen thousand five hundred fifty will become an invisible value. 113 00:10:44,900 --> 00:10:48,470 And seven thousand nine hundred fifty will be the visible value. 114 00:10:50,480 --> 00:10:59,080 And this will be Vasti final profit after tax of eighteen thousand five hundred fifty, even copied 115 00:11:00,140 --> 00:11:04,190 and pasted in the sheet to compare. 116 00:11:09,190 --> 00:11:10,710 Zoom out a little bit. 117 00:11:14,830 --> 00:11:18,250 You can see that these two charts are identical. 118 00:11:18,970 --> 00:11:26,410 You can change the little values, the names of these columns to get exactly the same chart. 119 00:11:29,320 --> 00:11:35,660 So these are the two ways that you can create a waterfall chart if you are on Excel twenty sixteen hours 120 00:11:35,680 --> 00:11:36,100 later. 121 00:11:36,130 --> 00:11:42,570 It is really simple, but if you are using Excel 2013 or earlier, you will have to do this the longer 122 00:11:42,580 --> 00:11:42,820 we. 123 00:11:43,980 --> 00:11:50,790 That's it for this video, if you find this visualization technique interesting and want to learn more 124 00:11:50,790 --> 00:11:53,920 about visualization techniques and excel in general. 125 00:11:54,510 --> 00:11:56,790 Please go check out our lecture series. 126 00:11:57,830 --> 00:12:00,260 On YouTube and Udemy, thanks.