Vertical Analysis of an Income Statement in Excel

by | Aug 11, 2017

Vertical analysis, also called common-size analysis, takes the Sales and looks at each line items so that you can easily compare the income statements and balance sheets. Vertical analysis on an income statement will show the sales number (sometimes listed as Revenue) as 100%, and every other account will show as a percentage of the total sales number.

Audience: Accounting students, department managers, CEOs, finance students, MBA students, accountants, and Executive MBA students.

Excel skills used in this vertical analysis

  1. Mixed reference
  2. Format Painter
  3. Percentage formatting
  4. Fill without formatting
  5. Copy and Paste

YouTube Video on Vertical Analysis of financial statement by Chris Menard

Coming up next week:

  1. Rank function in Excel along with a comparison of Vlookup vs Index and Match functions.
  2. Horizontal analysis of financial statements.

Transcription of YouTube Video

Use CTRL + F to search the transcription

0:00
hey this is Chris Menard let me show you
0:03
today how to do vertical analysis on an
0:07
income statement so I’ve got the years
0:08
2016 in 2017 I’m going to right click on
0:13
column C and do an insert and a vertical
0:17
analysis is basically taking the 600,000
0:20
in 2016 and figuring out what percentage
0:24
these other numbers are up to 600,000
0:27
and if you don’t like the word revenue
0:29
we can change it there you go
0:31
so here’s your formula equals B 6
0:35
divided by B 6 and most people I know
0:39
will tell you you need to make this
0:40
absolute reference by pressing the f4
0:43
function key if you’re a laptop person
0:48
you may need to hold down the FN key and
0:51
then press the f4 function key but you
0:55
don’t want to make it absolute what you
0:56
want to do is stop right there B dollar
0:58
sign 6 that’s called a mixed reference
1:00
don’t worry that I got the number 1 for
1:03
$1 autofill that down there your numbers
1:06
I’m about to make and percentages I
1:08
would highlight this this is this is my
1:11
method go to the Home tab use the format
1:15
painter and drag down so it copied the
1:20
formatting including the borders and now
1:22
go make it percentage so I make a
1:24
percentage last so 100 percent is
1:29
600,000 divided by 600,000 47% is 280
1:35
divided by 600 and you can see I’m going
1:39
to get rid of that zero right there so
1:41
we ended up with net income of 23% for
1:44
the year 2016 now because I did not use
1:49
absolute reference I can simply
1:52
highlight copy I’m gonna go to cell East
1:57
6th and paste and look look at that 47%
2:04
it is d7 divided by D dollar sign 6 so a
2:09
little tip from me is use mix reference
2:12
if
2:13
multiple years don’t use absolute
2:15
reference so this is called vertical
2:18
analysis one more way to do it we just
2:21
save this in case I want to come back to
2:23
it I’m going to delete what I just did
2:26
you can either put it the percentages
2:29
next to the numbers but sometimes and I
2:31
sometimes prefer this I’m gonna simply
2:34
copy my data up top and then paste it
2:40
down below and now we pulled my let me
2:45
shrink my screen in just a little bit
2:49
I’m in cell b20 this is going to be
2:54
simply equals V 6 divided by B 6 and
3:00
once again I’m gonna press the f4
3:02
function key whoops went too far right
3:05
there
3:06
I still got that one dollar don’t worry
3:09
about it and pull it down so this is
3:11
just like before except I’m keeping all
3:13
my percentages down here highlight I’m
3:20
gonna undo one time my bad
3:23
autofill down and then just tell it
3:27
right here to fill without formatting
3:31
now go make a percentage there you go
3:35
and once again you get rid of those now
3:39
one more time just simply copy and paste
3:47
so there’s there’s vertical analysis on
3:50
an income statement feel free to share
3:52
that with your MBA students your
3:55
accounting students or any one
3:56
department managers probably should know
3:59
how to do this next week I’ll cover
4:02
horizontal analysis and I’m using an
4:05
income statement but you could also do
4:07
this with the balance sheet also coming
4:09
up next week and you need to see this
4:11
I’m going to discuss the difference
4:14
between a vlookup versus using index and
4:19
match so I’m gonna set this up with you
4:21
on Monday from scratch and here’s what
4:23
you’re going to learn if I come over
4:25
here
4:26
if you notice I’m gonna teach you how to
4:28
use the rank function so watch this I’m
4:32
going to sort A to Z so I’m sorry when
4:40
we sort the other way sort largest to
4:42
smallest
4:42
so China which has 1 billion four
4:46
hundred three millions at the top India
4:48
USA on and on but let’s test this notice
4:51
it’s number nine and I’m going to change
4:54
this number to 1500 and watch a ten go
4:59
to the number one so I’ll teach you how
5:01
to use the rank function but I’ll also
5:03
teach you I’m over in cell g2 if I pick
5:10
a different country it doesn’t matter
5:11
what country I pick so I’ll teach you
5:13
how to make this drop-down arrow here
5:15
it’s pulling notice that my index and
5:19
match function and my vlookup or giving
5:22
you the exact same so next week I’ll
5:23
explain why index and match are much
5:26
better than vlookup and here’s just one
5:28
example if I insert a column for some
5:32
reason my vlookup no longer works but my
5:37
index and match continue to work so but
5:42
there’s other reasons to use index and
5:43
match just besides this one anyway thank
5:47
you for your time have a good weekend

 

 

 

chris menard on Linkedinchris menard on Youtube
chris menard
Microsoft Office Master Instructor
I train corporate clients in Microsoft Office Excel, PowerPoint, Word, and Outlook. When I'm not training, I blog about technology and create training videos on YouTube. I currently have over 240 technology videos available. Most of the videos I've created come from questions asked during MS Office training.

Pin It on Pinterest