# 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,
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..