Excel – Vertical Analysis – Financial Statement Analysis – Income Statement and Balance Sheet

Hello, I'm Chris Menard. In today's Excel 
video, we're going to get into some financial   stuff. I'm going to do vertical analysis on an 
income statement, but I will discuss vertical   analysis of the balance sheet. So I have 
a sample income statement in front of me. For vertical analysis, there is always 
a base number for the income statement. It is your revenue. If you're looking at the 
balance sheet, the base number for assets would   be total assets.

And the base number for your 
liabilities and owner's equity would be total   liabilities and owner's equity. Technically, you 
can use total assets because they have to match. Let's talk about the income statement first. So vertical analysis is always going to give 
us percentages. I want to know what percentage   these numbers are of the base number, which is my 
revenue in cell B5. Before I even do this video,   just a quick tip for you. If you want to know 
what formulas were used on this income statement,   Control G pulls up Go To. Go to Special and 
just select Formulas and click OK. Three cells   are selected. The 320,000 is the revenue minus 
the cost of goods sold. Then down the 200,000,   I just simply summed up the other expenses. 
And then I took the gross profit of 320 minus   the total expenses to get to 120.

Those are the 
only formulas I have here, just so you know that. One other tip, and this actually drives 
me crazy. I typed in other expenses,   but for these five expenses, I did not do 
space bar, space bar, space bar, Payroll.   The way you put these in here is I typed 
in Payroll through Rent. I came to the   alignment group and I just moved them over 
and it's up to you, how far you indent them. So that's how I did that. So here we go now with 
the vertical analysis. We've already mentioned   this the 600,000 is going to be the base number. 
Equals B5 divided by B5. That should give me 100%   or the number 1. I do want to make this a mixed 
reference, so it's going to be B dollar sign 5. I'm freezing row 5, but I'm not freezing column 
B. I'll tell you why in one second. Control Enter   and just pull this down. If you get some zeros, 
you can get rid of them, not a problem. If you   don't like the zeros, you could write an 
if statement, but since I've just got one,   I'm going to get rid of it.

I could also do 
Control H. Find the zero in place with the blank. Now, before I even make these percentages, which 
is right here in the number group. Let me get rid   of that, by the way. I'm going to undo, put them 
back. I'll tell you why. I want to take these   borders that I have over in column B and move 
them over here. So just simply highlight and copy,   and we're going to do a Paste Special, 
and I want to paste just the formats. There I go. Now I'm going to go make 
them percentages and get rid of that   zero.

So there is vertical analysis 
of an income statement. Everything is   based on the revenue of 600,000. 
Our cost of goods sold is 47%,   leaving us with a gross profit of 53%. Our net 
income or our earnings are 20% of our revenue. Now, vertical analysis, you only need one 
period. Whatever this period is, it can be month,   it can be quarter, it can be year. A lot of times 
with vertical analysis though, we'll compare one   company with another company. So just to show you 
this, I've already got this hidden. So let me just   unhide it. Here's Company B. Do you recall 
in this formula, I used a mixed reference? If I was looking at just Company A, I could have 
used absolute cell reference, but the reason I   used mixed is I can now copy this and paste it 
over into E.

And notice it's D5 divided by D5. If I didn't use the mixed reference, 
I could not have done that.   So an example of Company A and Company B, we 
could be looking at Lowe's and Home Depot,   possibly Delta Airlines versus American Airlines, 
Microsoft and Apple, you can go on and on.   Usually, it's companies within the same industry, 
you would be comparing vertical analysis to. So what's interesting about this is 
even though Company B has more revenue   and it has more revenue, but its net 
income is less, and also if you notice,   quite their cost of good salt 
is actually a lot 5% higher.

So there is vertical analysis. 
It's always going to give you   percentages right there, but it can be one period. Your horizontal analysis, which I'm going to 
do in another video, that would be multiple   periods. I'm going to compare from the 
year 2018 to the year 2023 as an example.   So that would be horizontal analysis, the 
same company, multiple periods for horizontal. Back to that balance sheet. If I had a balance 
sheet and I've got assets, total assets would be   that base number. And then we've got assets 
of cash, accounts receivable, inventory,   prepaid. If I see inventory is 70 or 80% of my 
total assets, that may be a red flag, because   that would be a high percentage of inventory to 
total assets. So you can see the advantage and   you can see why vertical analysis is so great 
when you're analyzing financial statements. I hope you enjoyed this video. Feel 
free to subscribe and ring the bell.   Thank you for your support.

Have a great day..

test attribution text

Add Comment