1
00:00:00,180 --> 00:00:10,590
Now, in this lecture, we will perform the vertical analysis on our 2018 2019 BNL statement and 2019

2
00:00:10,590 --> 00:00:12,960
2020 B in a statement.

3
00:00:14,860 --> 00:00:23,980
Now, as discussed in our Turin Lecture to perform vertical analysis, we divide all the values by revenue.

4
00:00:26,120 --> 00:00:29,130
So let's start we'll start with this cell.

5
00:00:30,130 --> 00:00:34,840
We will divide this value by the revenue value, which is same.

6
00:00:36,950 --> 00:00:44,990
And you will find one now if you want to convert this value and percentage below, then you can click

7
00:00:44,990 --> 00:00:47,460
on this button over here.

8
00:00:47,570 --> 00:00:50,330
This will convert the value and percentage from.

9
00:00:52,120 --> 00:00:58,780
Now we want to divide this revenue value from all the values that we have listed over here.

10
00:00:59,850 --> 00:01:08,760
So for B6, we will again write the same formula, which is this number divided by the revenue number.

11
00:01:11,250 --> 00:01:18,530
Now, if we have to calculate this value for all the categories here, it will be a little bit time-Consuming.

12
00:01:18,540 --> 00:01:20,810
We have to write formula in Etzel.

13
00:01:21,750 --> 00:01:30,390
So what you can do is just select the box where you have applied the formula and click on this plus

14
00:01:30,390 --> 00:01:38,160
icon at the bottom right corner and then hold your mouse, click and drag it towards the bottom.

15
00:01:40,780 --> 00:01:47,260
Now, you can straightaway notice that we are not getting our desired result if we select this second

16
00:01:47,260 --> 00:01:56,590
cell and click on this formula, but we can see that we are dividing C six by C six itself.

17
00:01:58,290 --> 00:02:04,590
But we want to divide the C six cell with C five, that's about our new cell.

18
00:02:05,220 --> 00:02:06,660
Similarly in this.

19
00:02:07,950 --> 00:02:17,190
Third sell as well, we are diverting C7 by C7, so XLE is identifying that if we are moving down and

20
00:02:17,190 --> 00:02:21,720
if we are dragging our formula, it is changing the reference of cell.

21
00:02:22,950 --> 00:02:28,170
So for the first set, we do see five with five for the second cell.

22
00:02:29,100 --> 00:02:32,460
Excel identified that we are dragging our formula down.

23
00:02:32,850 --> 00:02:42,800
That's what it means to see five, to see six, and for the third said it changed C five to see seven.

24
00:02:44,280 --> 00:02:48,680
But here we want to make our second term concern.

25
00:02:48,690 --> 00:02:57,030
So, for example, we want to divide all the cells by C five and while dragging our formula xle changing

26
00:02:57,030 --> 00:03:01,170
the C five to see six or seven or any other said.

27
00:03:02,420 --> 00:03:08,360
So to fix this cell reference, we can use dollar symbols.

28
00:03:10,680 --> 00:03:13,920
Just select the cell, you want to stay sane.

29
00:03:14,670 --> 00:03:19,320
So, for example, in this case, we want to divide all the cells by C five.

30
00:03:19,350 --> 00:03:28,050
So we want this second cell address to remain constant, just like this cell and press F for.

31
00:03:29,360 --> 00:03:37,730
When you press effort, you will see these dollar symbols in front of rule number and column name,

32
00:03:38,210 --> 00:03:45,770
so you will find dollars C dollars five dollars symbols means that if you are dragging your formula,

33
00:03:46,340 --> 00:03:49,580
this cell or this address will remain constant.

34
00:03:51,770 --> 00:03:59,870
So once we have fixed our cell reference, now, if we try to drag the formula by going to this corner

35
00:03:59,870 --> 00:04:00,950
and dragging it.

36
00:04:02,710 --> 00:04:08,510
You can see that now we are getting the correct result if we click on this formula about four seconds,

37
00:04:08,530 --> 00:04:14,800
but you can see that we are dividing C six by C five.

38
00:04:16,010 --> 00:04:23,150
We have used other symbols that sway this denominator is not changing, only the numerator is changing.

39
00:04:26,960 --> 00:04:30,500
Similarly, we can drag this formula towards the bottom.

40
00:04:32,630 --> 00:04:38,630
And you will find that we are getting percentages in front of each category.

41
00:04:39,500 --> 00:04:40,700
So, for example.

42
00:04:41,620 --> 00:04:47,740
If you see in advertising, we are spending around two percent of our revenue and depreciation.

43
00:04:48,430 --> 00:04:51,920
We are spending three percent and interest repayments.

44
00:04:52,030 --> 00:04:55,270
We are spending around four percent in payroll.

45
00:04:55,510 --> 00:04:57,700
We are spending around 14 percent.

46
00:04:58,330 --> 00:05:02,020
And in income tax, we are spending around eight percent.

47
00:05:03,620 --> 00:05:11,330
And once you get all this percentage values, you have to identify which expenditure is contributing

48
00:05:11,330 --> 00:05:12,080
the most.

49
00:05:13,600 --> 00:05:22,060
So, for example, and all of this, our expenditures, cost of goods amounts for around 52 percent

50
00:05:22,060 --> 00:05:25,480
of our revenue so we can highlight this value.

51
00:05:28,610 --> 00:05:36,920
Similarly, the next biggest expenditure we have is payroll, which contributes for around 14 percent

52
00:05:36,920 --> 00:05:38,390
of our revenue.

53
00:05:39,920 --> 00:05:47,330
Similarly, we can do a similar analysis for year 2019, 2020, PNL as well.

54
00:05:48,860 --> 00:05:59,640
So here we will just write E five divided by five now for this denominator, we will select the denominator

55
00:06:00,170 --> 00:06:02,960
cell and press F4.

56
00:06:04,060 --> 00:06:06,120
In this way, we will fix this cell.

57
00:06:08,820 --> 00:06:09,490
Sutanto.

58
00:06:12,970 --> 00:06:14,860
Again, we will change.

59
00:06:16,480 --> 00:06:18,760
The tape from accounting to percentage.

60
00:06:19,400 --> 00:06:20,920
Now we are getting the correct result.

61
00:06:22,300 --> 00:06:27,970
Now, again, go to the bottom right corner of this cell and drag it down.

62
00:06:31,870 --> 00:06:38,740
Now you can see that we are getting the correct result now here also you can see that.

63
00:06:40,080 --> 00:06:49,080
The COAG or costs of good sold amounts for around 61 percent of our revenue and payroll accounts for

64
00:06:49,080 --> 00:06:51,150
around 13 percent of our revenue.

65
00:06:52,920 --> 00:06:57,510
So that so we do vertical analysis and exit.