Year-Over-Year analysis in Excel using a PivotTable

hello i'm chris menard in today's video i'm going to cover year-over-year analysis also called year-over-year growth i'm going to use microsoft excel for this but you don't have to use excel i just find it really easy to use excel so let's discuss first what is year-over-year analysis obviously if i'm comparing one year the year 2021 versus the year 2020 that would be year-over-year analysis but it's not just years it's any time period you can compare one year to the other so this could be quarters quarter three of one year versus quarter three of another it could be a certain month i want to look at the month of november for the current year versus november of the previous year you could even do weeks or just one day if you wanted to so what is it you would analyze well if you're doing investments you might do your stock portfolio or your investment portfolio if you're a company maybe you're doing revenue or possibly net income possibly new customers it could be a variety of items if you're a youtuber you may do your watch time your views or your subscribers so let's go ahead and get started looking at this i'm going to go ahead and give you one example first from microsoft's financials and then i'm going to dive into excel with some data that i made up showing multiple multiple years and multiple months with revenue but again it could be any of those other items that i mentioned so let me go ahead and pull up microsoft's financials first i'm in the year 2021 i'm on microsoft's website and i'm in quarter two just so you know this microsoft's fiscal year which is the letters fy21 q2 right there for earnings their fiscal year starts on july 1 and it ends on june 30th so their fiscal year for quarter 2 that would be october of 2020 through december 31st of 2020 and look this came out on january 26th quarter ended december 31st 2020 a lot of information in here but if you keep reading down here or if you don't want to read let's just type in year over and there you go so they're comparing quarter two of this fiscal year versus quarter two of the previous fiscal year and just reading from that something's up 34 percent revenue is i'm assuming that that's good so now we pop back into excel and let's actually do some analysis here just to show you this i started here in march 2017 and we went through february 2021 with these revenue numbers just to make this official i'm going to freeze the pains or else that will drive me crazy there we go so i want to compare i'm making this up i want to compare the quarters first for the year 2020 versus the four quarters for the year 2019. we're going to do this with a pivot table so click inside your data insert pivot table you know this is five clicks to make a pivot table new worksheet sounds good it picked up my entire range i will make this file available for you down below in the description click ok right so we only had two header rows the word date and the word revenue watch how cool pivot tables are i've shown this a million times i've only got months and years i'm going to drag revenue to the values so there's the total revenue for all the months and all the years i'm going to drag date to the rows watch what happens when i drop it in the row area it shows me every year which is good and it gives me a grand total which is okay it summed up the revenue which is perfect but if you notice there are plus symbols next to each year and i'm going to zoom in a little bit so you can see this much better so if i expand it's 2020 and 2019 that i'm interested in if i expand 2019 there are my quarters one through four 20 quarters one through four you can expand and collapse couple quick tips here if you want to expand them all right click a year there is expand and collapse and if you look over to the right it says expand entire field so it did it for every year again i'm going to right click on any year expand and collapse collapse entire field if you want to do one obviously i could hit the plus sign which i find easy but i could right click 2018 and hit expand collapse and then hit expand here that does just that one so there you go a little tip there so here we go i only want to do the 2018 and 2019 quarter one i'm sorry all quarters click here and just do 2019 and 2020 i'm doing a filter click ok there they are but i want to see the quarters year quarter and date this is what's so cool about pivot tables in excel if you recall from my initial data all i had was the month and the year so it grouped them by year and it also grouped them by quarter that's why you see quarters here i'll put the quarters drag it up to the columns and there you go so i'm comparing that is year over year analysis for every quarter for the years 2020 and 2019. if you only wanted to see a certain quarter you could click here let's do microsoft microsoft was only doing i think quarter one i can't recall now to be honest with you quarter one another tip i really don't need this grand total that is in cell c4 i'm going to right click on c4 and remove it and i don't need the grand total down here right click and remove it if you're wondering what percentage increase is that for quarter one for those two years it is going to be equals cell b6 minus lb5 that will be the numeric value divided by the the earliest year so that is a 114.6 percent increase from 2019 quarter one to 2020 quarter one one of the huge advantages of doing year over year analysis is as they say you're comparing apples to apples because if you look for example a quarter if you look at quarter one versus quarter two in quarter two versus quarter three or quarter three versus quarter four uh maybe you have a lot of fluctuations in seasons as an example if you do landscaping probably the spring and summer or your best months or if you have a pool supply company but if you're a retailer you may be basing everything on the christmas season or the holiday season so that's some reasons now if you want to see this monthly i'm going to do this again from scratch real quick but before i do it from scratch let me show you another example from another company i'm going to pull up coca-cola this time coca-cola so if you do a search for year over year and you don't see it on someone's financial year over i don't see it you most likely they've called it same period sure enough whoops if i can spell period there you go so here's coca-cola the first quarter of 2019 were impacted by one less day as compared to the same period again that is year-over-year analysis in 2018. so they're saying the first quarter of 2019 had one last day what does that tell you because those are not those are not leap years that tells me that my that coca-cola has fiscal quarters so january through march may not be quarter one it may be january through march 25th or march 26th they have fiscal quarters and to prove this one less day quarter one 2019 coca-cola quarter one march 29th 2018 quarter one march 30th so that is absolutely correct there was one last day they're just letting you know it but that is still year over year analysis all right let me finish this up this is supposed to be a short video but this is good stuff right one more pivot table from scratch i'm going to do every month for 2020 versus every month for 2019 so january 2020 versus january 2019. same steps insert pivot table new hit good um we're going to do date i just checked it i'm going to drag revenue down here good again i'm gonna click in here i'm gonna go tell microsoft excel on pivot table analyze tab contextual tab i'm just gonna go take a look at what we're grouping by here i'm gonna go to group selection and it's got everything in there so that's good i want to take i don't want quarters also i'ma lose quarters so now i've got the year and i've got the month i dragged quarterback out of here i'm going to take the years and put them over here 2017 remember i'm going to get rid of the grand total first let's do this right and then here's my filter for the years we only want to see 2019 and 2020. there you go and i don't need to see that grand total at the bottom either again right click and remove and then you'd come over here and do your calculations but you can see you can see clearly that the 20 20 months are all higher than the 20 19 months which in this case is good so i hope that helps if you have any questions about year over year analysis please let me know in case you're wondering one last item if you wanted to compare one month to the previous month so we had a that is called month to month analysis so m t m month to month against year over year which is sometimes y over y and you can see here well this isn't a bad one you can see here where you could have those fluctuations seasonal fluctuations thank you for your time feel free to subscribe to my youtube channel if you have any questions about this video put them in the comments thank you bye you

Channel: Chris Menard

Views: 1,639

Rating: 4.7714286 out of 5

Keywords: year over year, year over year comparison excel, year over year explained, year over year growth in excel, year over year percentage change excel, year over year growth, year over year analysis excel, personal finance, year over year monthly comparison, year over year quarterly growth, chris menard training, chris menard, chris menard excel, excel tips, microsoft excel, yoy growth formula in excel, yoy growth, Year-Over-Year analysis in Excel using a PivotTable

Id: dBW7uvTeDC4

Length: 12min 22sec (742 seconds)

Published: Thu Mar 11 2021