5 Awesome Data Analysis Tricks on Excel

from forecasting Revenue to multiple regression analysis in this video we cover five awesome data analysis tools on Excel so let's get into it coming in at number five we've got forecasting and for this we can use what's known as a forecast sheet on Excel so let's take a look over here you can see that we have this data set where it's got the dates and the revenue for a hotel resort business you can download the same Excel file in the video description that being said let's go ahead and find the trend so we're first going to select these we're just gonna go over to insert and we're just going to go on the recommended charts and we're okay with that very first line chart here you can see that we clearly have some seasonality where in the summer months it seems like our Revenue Peaks then it goes down in the winter so what we want to do here is based on these past figures we want to be able to project into the future so we're gonna delete this chart and instead I'm gonna head over to data and then click on forecast sheet and now you can see that we're able to forecast into the future wow and it's accounting for that seasonality as well here so that thick line is the forecast Revenue then we've got the upper bound and the lower bound of confidence which basically means that 95 of your revenue is going to fall Within These two lines now if we head over down towards options we have a lot more customization so you can see here that we can change that confidence interval from 95 to something else and similarly you could see that it's detected that seasonality automatically but we can hit that manually as well for now we're just gonna hit on create you can see here that it creates a separate sheet where we have historical Revenue alongside all of the future Revenue that it's been able to forecast next up we've got correlation analysis which helps us determine if there is a relationship between variables for example over here let's suppose that we're looking to invest in some stocks we've got Apple Microsoft Amazon and EXO mobile and we have some of their historical share prices now based on that we want to see if there's a big relationship here and if so maybe it's not worth investing in very similar companies as that could be a bit risky for this we'll use the data analysis tool over here now if this isn't enabled for you you can just head over it to file then towards the bottom you'll find options so click on that and then towards the bottom again under add-ins you want to click on go and you want to take that analysis tool pack if you haven't already hit on OK and then it should show up there so I'm simply going to click on it from here we want to look for correlation that's the one for us and I'm just gonna hit on OK so the input ranges for us is going to be all of the stocks so we're gonna start with apple up here Ctrl shift down Ctrl shift right and we do have the labels in the first row so we'll take on that and for the output this is basically where we want the results to show let's say I want them to show right over here to the side and I'm just gonna hit on ok now this table may not seem all that intuitive so let me quickly explain basically you can have a correlation from -1 all the way to one minus one means that it has an inverse correlation while one means that it has a positive correlation so you can see over here that apple to Apple obviously has a correlation of one because they're the exact same company now Microsoft to Apple also seem to have quite a big correlation and that probably makes sense as they're both tech companies now if we go lower towards EXO mobile which is an oil and gas company now you can see that it doesn't have that much of a correlation with Apple while understanding the data is important so is being able to visualize it and that's where chart templates like the ones HubSpot the sponsor of this video is kindly providing us come handy using the link in the description below you can get multiple chart templates completely for free excel in the download you'll find the Excel file with instructions on using the template alongside all of the chart types you might need to visualize your data from here you can easily modify your data and the charts will automatically change these templates can either have one column of data or multiple depending on your needs I personally find this template useful for deciding which chart showcases my data best as it's not very common to find templates that show multiple charts simultaneously so if you want to check this out head over to the link in the description below to download these completely free templates from HubSpot to level up your Excel game alright back to data analysis in number three we've got descriptive statistics and this is usually a good starting point for understanding your data so here's the data set we've got the online ads and next to that we've got the revenue figures for them so maybe it would make sense here to do things like calculating average the sum the maximums and so on now we can use formulas for that or the other faster way to go about this is using the data analysis tool again but this time we're gonna look for descriptive statistics down over here we're just going to hit on OK there the input range for us is going to be the whole table so Ctrl shift down Ctrl shift right there and we want the labels in the first row because that's what we have and the output range we're just gonna select it so that it's over here to the side of the table let's say in E2 and I'm just gonna click on summary statistics down below when hit on OK here's the data and let me just quickly reformat it awesome so now you can see that we have all of the essential information so things like what's the mean what's the mode what's the minimum and the maximum the count and so on in number two we've got a simple linear regression and over here let's suppose that our manager wants to know if there is a relationship between running online ads and generating sales for that what we can do is first visualize it so we're just going to select the table by going to Ctrl a and then we'll go to insert we're just gonna insert a scatter plot so this one right over here and just looking at the Dots here you can see that there seems to be a bit of a trend where the more online ads that you run the more Revenue you get now from here we can do further analysis so first let's remove the grid lines so we're just gonna click on those and hit on delete same thing over here and then we're gonna add a trend line so we'll go over to click click on any of these dots and then right click go to add trendline once we've done that we can do a few more things like displaying the equation and displaying the r squared don't worry I'm just going to explain these in a second so this top part is the equation of a line and the three one one here basically says that for every additional ad that we have that we run we get 311 dollars in revenue and then the 9000 over here is basically The Intercept so basically at zero odds this is the revenue that we would have then right below that we have the r squared which basically explains what percentage in this case 69 of the variance in Revenue can be explained by the online ads finally we've got multiple regression which is for analyzing more than one variable so before we only had online ads but now we also have newspaper ads we want to see how these two contribute to revenue here the revenue would be the dependent variable because it depends on these two and so the online ads and the newspaper ads would be the independent variables so let's analyze these by going over to data and then to data analysis here we're going to look for a regression and just click on that when you find it the input y range is going to be the revenue because that's our dependent variable so Ctrl shift down there and the X input range for us is going to be the two independent variables so both the newspaper adds and the online ads so Ctrl shift down just select both of them we do have the labels up top so we're going to tick on that for our output just click on that there we're gonna select the top part of this table so let's suppose I just put it over here to F2 and then I'm just gonna hit on OK there and it's gonna generate this table for us I realize that this might look a bit overwhelming so let's go over some of the key points over here in this top part we want to be looking at the adjusted r squared not the regular r squared so this number over here because it's basically adjusted for the number of independent variables which in this case is more than one now in this scenario it's 0.99 it's almost 0.7 which basically means that 69 of the variation in the revenue is explained by these two independent variables so generally the higher at the number the better here then the other important figures here are down below where you can find the coefficients so the intercept and both of these odds over here for the newspaper ads you can see that this coefficient is negative so for every increase in newspaper ads we actually have the decrease in revenue on the other hand for online ads we have a positive 365 so for every online ad it seems like Revenue increases now there's a ton of other Statistics over here to the side if you want me to cover those that would be a whole new video so let me know in the comments below hopefully this gives you a good overview of the data analysis tools available on Excel if you want to learn more about data analysis check out this video over here for this link here to take our Excel course hit the like and that subscribe and I'll catch you in the next one

Channel: Kenji Explains

Views: 149,689

Keywords: regression analysis, correlation analysis, linear regression, multiple regression, regression excel, descriptive statistics excel, excel correlation analysis, excel forecasting, forecast chart excel, excel forecast chart, forecasting seasonality excel, multiple regression excel, excel linear regression, p value, r squared, adjusted r squared, independent and dependent variable, statistics on excel, data analysis, excel data analysis, data analysis on excel, data analyst

Id: 0yvemzEzt-E

Length: 10min 12sec (612 seconds)

Published: Sun Mar 19 2023