Vertical analysis of an Income Statement in Excel

Posted on:  08/11/2017
Vertical analysis of an Income Statement in Excel

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

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

Hey, this is Chris Menard let me show you today how to do vertical analysis on an income statement so I’ve got the years 2016 in 2017.

I’m going to right-click on column C and do an insert and a vertical analysis is basically taking the 600,000 in 2016 and figuring out what percentage these other numbers are up to 600,000 and if you don’t like the word revenue, we can change it. There you go, so here’s your formula equals B6 divided by B6 and most people I know will tell you, you need to make this absolute reference by pressing the f4 function key.

if you’re a laptop person you may need to hold down the FN key and then press the f4 function key but you don’t want to make it absolute. What you want to do is stop right there B dollar sign 6 - that’s called a mixed reference.

Don’t worry that I got the number 1 for $1 autofill that down there your numbers I’m about to make and percentages I would highlight this-this is this is my method go to the Home tab.

Use the format painter and drag down so it copied the formatting, including the borders and now go make it a percentage. So I make a percentage last so 100 percent is 600,000 divided by 600,000 - 47% is 280 divided by 600 and you can see I’m going to get rid of that zero right there. So we ended up with a net income of 23% for the year 2016.

Now because I did not use absolute reference, I can simply highlight copy I’m gonna go to cell E6 and paste and look at that 47% - it is D7 divided by D$6, so a little tip from me is use mix reference if multiple years don’t use an absolute reference. This is called vertical analysis. One more way to do it, we just save this in case I want to come back to it.

I’m going to delete what I just did - you can either put it the percentages next to the numbers but sometimes - and I sometimes prefer this - I’m gonna simply copy my data up top and then paste it down below - and now we pulled my... Let me shrink my screen in just a little bit - I’m in cell B20, this is going to simply equal V6 divided by B6 and once again I’m gonna press the F4 function key.

Whoops, went too far, right there, I still got that one dollar, don’t worry about it and pull it down, so this is just like before except I’m keeping all my percentages down. Here highlight - I’m gonna undo one time, my bad - autofill down and then just tell it right here to fill without formatting. Now go make a percentage - there you go and once again you get rid of those.

Now one more time - just simply copy and paste so there’s vertical analysis on an income statement. Feel free to share that with your MBA students, your accounting students or anyone.

Department managers probably should know how to do this. Next week I’ll cover horizontal analysis and I’m using an income statement but you could also do this with the balance sheet. Also coming up next week, and you need to see this, I’m going to discuss the difference between a vlookup versus using index and match so I’m gonna set this up with you on Monday from scratch.

And here’s what you’re going to learn if I come over here, if you notice I’m gonna teach you how to use the rank function so watch this: I’m going to sort A to Z so I’m sorry when we sort the other way sort largest to smallest so China, which has 1 billion four hundred, three million at the top - India, the USA on and on but let’s test this.

Notice it’s number nine and I’m going to change this number to 1500 and watch a ten go to the number one so I’ll teach you how to use the rank function. I’ll also teach you I’m over in cell G2.

If I pick a different country, it doesn’t matter what country I pick - so I’ll teach you how to make this drop-down arrow here. It’s pulling notice that my index and match function and my vlookup are giving you the exact same - so next week I’ll explain why index and match are much better than vlookup.

And here’s just one example - if I insert a column for some reason, my vlookup no longer works but my index and match continue to work - but there are other reasons to use index and match - just beside this one.

Anyway, thank you for your time, have a good weekend!

Chris Menard

Chris Menard is a Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.

Categories