Now, in this lecture, we will perform the vertical analysis on our 2018 2019 BNL statement and 2019
2020 B in a statement.
Now, as discussed in our Turin Lecture to perform vertical analysis, we divide all the values by revenue.
So let's start we'll start with this cell.
We will divide this value by the revenue value, which is same.
And you will find one now if you want to convert this value and percentage below, then you can click
on this button over here.
This will convert the value and percentage from.
Now we want to divide this revenue value from all the values that we have listed over here.
So for B6, we will again write the same formula, which is this number divided by the revenue number.
Now, if we have to calculate this value for all the categories here, it will be a little bit time-Consuming.
We have to write formula in Etzel.
So what you can do is just select the box where you have applied the formula and click on this plus
icon at the bottom right corner and then hold your mouse, click and drag it towards the bottom.
Now, you can straightaway notice that we are not getting our desired result if we select this second
cell and click on this formula, but we can see that we are dividing C six by C six itself.
But we want to divide the C six cell with C five, that's about our new cell.
Similarly in this.
Third sell as well, we are diverting C7 by C7, so XLE is identifying that if we are moving down and
if we are dragging our formula, it is changing the reference of cell.
So for the first set, we do see five with five for the second cell.
Excel identified that we are dragging our formula down.
That's what it means to see five, to see six, and for the third said it changed C five to see seven.
But here we want to make our second term concern.
So, for example, we want to divide all the cells by C five and while dragging our formula xle changing
the C five to see six or seven or any other said.
So to fix this cell reference, we can use dollar symbols.
Just select the cell, you want to stay sane.
So, for example, in this case, we want to divide all the cells by C five.
So we want this second cell address to remain constant, just like this cell and press F for.
When you press effort, you will see these dollar symbols in front of rule number and column name,
so you will find dollars C dollars five dollars symbols means that if you are dragging your formula,
this cell or this address will remain constant.
So once we have fixed our cell reference, now, if we try to drag the formula by going to this corner
and dragging it.
You can see that now we are getting the correct result if we click on this formula about four seconds,
but you can see that we are dividing C six by C five.
We have used other symbols that sway this denominator is not changing, only the numerator is changing.
Similarly, we can drag this formula towards the bottom.
And you will find that we are getting percentages in front of each category.
So, for example.
If you see in advertising, we are spending around two percent of our revenue and depreciation.
We are spending three percent and interest repayments.
We are spending around four percent in payroll.
We are spending around 14 percent.
And in income tax, we are spending around eight percent.
And once you get all this percentage values, you have to identify which expenditure is contributing
the most.
So, for example, and all of this, our expenditures, cost of goods amounts for around 52 percent
of our revenue so we can highlight this value.
Similarly, the next biggest expenditure we have is payroll, which contributes for around 14 percent
of our revenue.
Similarly, we can do a similar analysis for year 2019, 2020, PNL as well.
So here we will just write E five divided by five now for this denominator, we will select the denominator
cell and press F4.
In this way, we will fix this cell.
Sutanto.
Again, we will change.
The tape from accounting to percentage.
Now we are getting the correct result.
Now, again, go to the bottom right corner of this cell and drag it down.
Now you can see that we are getting the correct result now here also you can see that.
The COAG or costs of good sold amounts for around 61 percent of our revenue and payroll accounts for
around 13 percent of our revenue.
So that so we do vertical analysis and exit.