Predictive Analysis in Excel: Forecasting and Regression

1. Introduction: Hi, my name is Ruben, and in this class we will be diving into forecasting and regression in Excel. So in this class, I'll be teaching you how you can use Excel to forecast data like your inventory, your sales, or other financials using Excel. We will also go over regression, or we can look, for example, into what drives your sales are, what drives your inventory. I've gained my experience with Excel, working for different large organizations like ING Bank, Pfizer, where I've been helping people and teaching people how they can make better decisions using Excel. So before we dive into this glass, it's good to know that this glass requires you to know a little bit how Excel works. Like we're not really going to dive into basic formulas, but it's also not going to be science. You don't need to be a mathematician to understand the formulas that we are applying. We're mainly going to look at it from a practical point of view. So you can start forecasting your data. Also, I think it will be a good idea if you make sure you have Excel 2016. Since not all formulas in this class are available in older versions in Excel. So I hope you get excited as much as I am to dive into the subject and to dive into these quantitative forecasting features that haven't been very known within Excel. So I hope I see you in the next video where we will be going over to the outline and the project of this class. Thank you. 2. Class Outline & Project : Hello and welcome to the video where we will be looking into the Class Overview and the project description of our class predictive analysis in Excel, forecasting regression. So our class will be, as the title says, basically split up into parts. We will talk about forecasting and we'll talk about regression. And therefore, we'll start off with an introduction to forecasting and this class. The main purpose of this class is to make those functions who can't seem a little bit intimidating, especially regression, to make it easy to understand without necessarily being an expert in statistics. It's going to help you understand class better if you do know a thing or two about statistics. But my goal is that even if you don't, you'll still be able to use these tools after you have been watching these glass. So the first function we're going to dive into is linear forecasting. The second one will be seasonal and exponential forecasting. Or we are going to discuss recurring patterns and a little bit more advanced forecasting. And then we're going to calculate the confidence interval. So based on a percentage of security, we're gonna give our worst-case and our best-case scenario off ourselves forecasts. In number 234, it will be diving hands-on into Excel, showing you how these features are being done. And in class one, I will merely be just explaining a couple of the basics, one or two minutes about forecasting. And then the second part will be regression. So again in this class and it's video introduction to regression will be looking into what are the things into tools and handles you need to know before diving into Excel and performing a regression yourself. So we're basically going to talk about what does it mean to do a regression analysis and the nafta, that video, we'll dive into Excel and we'll be performing a regression analysis ourselves. In this video, we'll also explain and cover how you interpret the results off your regression analysis. After we have done video six, we will go into our project review. After this list, after talking about the class will go in this video to the project and what it means. And then V0 is going to be the optimal. And I will get to the class projects. So the class project will be a forecast and analysis of our sales data. And it will be a report you will give to your company's higher management, including choosing between the linear or seasonal functions to forecast the coming through years of sales data. All these assignments for a class project will also be covered in our previous lessons. So before less than seven, you should have all the know-how of performing the next four topics. So the second one is calculating the confidence intervals in your forecasting. Third, the project has given some extra data that could possibly influence our sales. And therefore, it's going to be up to you to see if you can find out what variables, what independent variables is affecting our sales. And N4, I attached, I will show you this in a little bit. Excel file, or you can present your findings in a nice report and give some extra key metrics with it. So we're going to use two files. One file, which is a different data set I'm going to use for the classes. I will upload this one as well so you can review how I've done that. And the other one is going to be for the project. And it contains data and a template that you are going to use for assignments. So let's dive into the file that we're going to use for the project. Alright, so this is the Excel file, but we will be using for our project. So right here you'll find the instructions. And we're going to use this outline to fill into graphs that we're going to need for our analysis. So we're not just gonna calculate beta up or else are going to convert those into the graphs. And these graphs we're going to put into those fields. And then right here, we're going to have some extra regression data. We're going to evaluate our tour regression analysis. And those data we can put into here. So let me quickly show you the raw data. This is a rod data that we're be using. And this is a sort of Home Depot kind of store with seals entries. So here we have the sales, the profit quantity discount, and some other data we're not gonna use all of the data. The most important one is kinda be sales, order, date and discount. And I've already, you don't have to do anything with the raw data. I have already summarized the data for you here. So per month we can see two sales reported. Your goal is to add the sales forecasts that the confidence interval, the lower bound and the upper bounds. And also to translate this into the graph that we are seeing right here. And then for the data analysis, regression will have the same thing. So we already have the data. You don't have to source data anymore. Your goal is to perform the regression analysis right here. And then also convert this regression analysis with the data that we have here in the appropriate graphs that we eventually can copy and paste and put in our instruction step. Please read the descriptions per graph, what we will need to provide in order to give the right representation of what we are looking for, a what we want to tell. And the companies hire management. And right here you can fill out to date up to regression analysis. So again, everything has a description. I helped you with some tips and tricks through in order to create this template for the companies hire management. And I wish you good luck with the project. I wish you good luck with the next seven videos about forecasting and bug and regression analysis and about reporting. And in case you have any questions, feel free to leave the questions below on this patient Skill Share. And I will help you as soon as possible. Good luck with the classes. 3. Introduction to Forecasting: Alright, so before we dive into applying our forecasting technique in Excel, I want to briefly covered Reetu what the different definitions are within forecasting. So in this video we'll go into that. And what's forecasting actually meets. So forecasting is the process of making predictions of the future based and past and present data. Most commonly by analysis of trends. Meaning that we can look into different epics transfer go look in linear trends and, and seasonal trends. And some industry examples are for marketing. Forecasting. The demand and supply often is highly seasonal. Characteristics. Think about an ice cream store having to buy more inventory for ice creams in the summer because more people eat ice cream going into winter, lower inventory. They really can benefit from forecasting techniques in order to decide how much ice cream they should buy. So for the finance department, it could be a great in order to predict sales or revenue, to allocate the right budgets for derived purposes, and to do a market analysis, for example, for finance that are more involved in stock markets on our financial markets. And then HR, you can use it for planning. And how many people will you need to put on MDL office are put on the working grounds. You can predict absence, productivity. All these things need to be tied to a certain trends. And again, I wanna emphasize that in forecasting, we will be looking at the quantitative part of forecasting and predicting our data. However, every Trent also has a qualitative part to it. And so we also need to know, I'm not just blindly go for our quantitative forecasts. It always helps for you to investigate and research why certain trends are happening. And this can optimize your forecasting. So then we get to linear versus exponential. Linear is a very simple way of forecasting. It basically takes the average growth rate and you're going to extend this growth rate into the future. So if today I sell one ice cream, tomorrow I sell to ice cream stand in two days, I will sell three ice creams. This is basically linear. Exponential is more advanced and there's features in Excel that use complicated formulas that has got detective, there is a seasonal trends to it. So for recurring betters, like we said with the ice cream sales, this is a great way of predicting your, your data and besides, it will also smoothen out deviations. So if there are certain spikes that we don't see in the other recurring patterns, it will leave it out, out of the prediction and tries to give you the most accurate at exponential or seasonal prediction possible. So there's a couple of functions to use. For the first one is forecast or forecast dot linear. I recommended to use forecast of NINR does is basically more a compatibility issue. The older excels will use forecasts and newer Excel school use forecasts or linear forecast dot ETS predicts your, your values from the surgeon months in the future based on the trends or patterns. Then forecast dot ETS, dot seasonality is going to look into, is gonna give us, returns us the length of the pattern. So every year we have a spike of sales or a spike of beta. Seasonality will give us close steps for dependent through recur forecast confidence interval. This is something we will be discussing into videos as well. This helps you to create an interval, so a lower and upper bound of how certain we can be that our forecast is going to be in between this lower and upper bound. There's always goes with a confidence level, which in the standard case is 95%. So we can say with 95% certainty based on the past data, our future data is going to be in-between a certain interval. And then last one, this is a more advanced one which we will not be discussing in this class and its forecast dot ETS Darmstadt. And this is going to give you one of the required are one of the preferred returning metrics. So if we want to know the alpha, beta, gamma, we can get those or even more advanced MAC S MAP, MAE or RMSE or to step size. But as I said, these are highly advanced and complicated statistical metrics at, and again, this purpose of this video is to get to some tools, people for people in office, for people in business did and unnecessarily statistic experiments. So we're going to dive into this. Alright, so that was it for this video. In the next three videos, we're going to dive into how you can actually forecast data. So we're actually kind of get to the exciting part of applying all these Fordham and us, not all of these, but most of these formulas and techniques that we have discussed in this video. And I'll see you hopefully in the next video. 4. Forecasting: Linear : Hello and welcome to the listen work. We're going to start off creating our forecasting formulas. So right in front of me, I have r beta sheet. And in our data sheet we have units sold of two different kinds of products. So we have beverages and snacks. In this lesson, we're going to look into how you can see which data you should use linear forecasting for and why linear forecast is a great tool to predict the future of your sales. So right here we have data from 2013 all the way to 2090. And we wanna predict everything beyond 2019, two thousand twenty two thousand twenty one in our case. So what we will do is we start extending our data. By the way, I use sales as an example. But obviously, that linear forecasting, it is applicable for any type of numbers that you can actually forecast in a linear way. I'll be showing you what I mean with that right now. So as you can see, we have two different types of data. So beverage soul and snakes sold. What I'm gonna do now is I'm going to quickly create a graph of the data. So we select the data, we click on insert. And here we can click on a scatter plot, which we see now is right here. We open it up a little bit. And as you can see, there's two very different type of and lines that we see right now. So what I want to explain to you is how you can see if you should apply linear forecasting. Obviously, data always comes with qualitative information as well. So knowing where your data's coming from, his health is going to help you to decide what kind of type of forecasting you should use. But in this case, where we have the orange dots, which you can see here is coming from the amount of snacks sold. We can clearly see it has a very straight line, is not like the beverages or we have a recurring pattern. And since this is a straight line, and we can use a linear forecasts. So linear forecasts can be done in two ways. The first way would be a more visual weight. Or we select our graph. We go to chart designed, Add Chart Elements. And we click here on trend line. If we now click on the linear trendline, we wanna predict it first, next sold the orange one. We can see how it progresses. So as I said, we have created two extra years, which is two thousand and twenty and two thousand and twenty one. Therefore, we have two extra years here on the X axes as well. And we can see how Excel is calculating our linear trends to continue going in the next two years. So meaning that if we are going to apply linear forecasting, aren't dots should be around this line as well. So let's start forecasting in a more precise way. So we're gonna do, is we're going to create here and other. And we're going to create another column. And this one we're going to call snacks forecasted. And then right here, where we don't have any number for snakes yet, which is the first of January 2020. We're going to start adding our formula. So we start type forecasts. But linear, older Excel introduced forecasts, which is just forecast. But for compatibility reasons, I recommend to you to just use forecasts linear, some new feature. It's basically the same mist and forecast future, but it's just more updated. So I recommend you to continue with the forecast but linear. And also how should not confuse with other forecast features to add are present. So we start with four kinds linear. If you need help, you can click on this button right here. And then on the right-hand side is going to help you to build a formula. So we have to start off with the next number. In this case, it's going to be the dates. So we want to know the date of which we want to forecast. And the second would be our known y's, which is our known values. Which in this case it's going to be this next sold, which is from the past. But we make this c2. And for that, we need to have the same rows as being our y's. But then in this case we're gonna use the Dates. Again. We're gonna make this a two. And that's it. And now we have our data entered. And now if we click on done, we get our value right here. Before starting base class, I haven't yet. And a number for many serverless create URLS. I'm sorry, that's opponents materials. We've gotten into decimals. And now we have created our first denier forecasted value, which is based on the value before. So now we want to pull this through for the rest of the months of the next two years. Before we do that, make sure we look. Both are Daydream off the recent data which we are using to create our forecast. And once we've done that, we can continuing to the nth power our database. Okay? So now we have our values forecasted. To visualize this, what we can do is we can create a new graph, which in this case it's going to be, and let's use the same graph that we have already been using. We go to chart design. We could click on select data and we're going to add another column, which in this case is going to be column D. So let's increase our data range. And now we get those forecasted values plotted here as well. And as you can see that this is exactly on the trend line. So in linear forecasts basically means that from the first dot to the last belt of theory results we already have. Excel is calculating the average road rage overtime. So every time we move one value to x, that means to be a standardized value of y or how much it's calling up. So for every move to the right, we need to have the same value up so that we keep a consistent growth. And eventually that turns out into this line. And this is basically using the average growth of the past data that we've been using. Which basically means that this is a very simple way of forecasting. But yeah, for linear data at sold your forecasting needs. So that was it for now for our linear forecasting. So in the next class we are going to look into seasonal forecasting, also called exponential forecasting. It's a little bit more advanced, but still a very easy way of forecasting in Excel. And I hope I'll see you in the next video. 5. Forecasting: Seasonal / Exponential: Welcome to the next video about forecasting. In this video we're going to discuss seasonal, also called exponential forecasting. So in the last video, we've been looking into denier forecasting, which is continuous with the average growth rate that we be having in the best and put a future assumes the same average growth rate. I make basically just follows the trend line. That's in short what we have been talking about in our next few, in our last video. But in this video is going to be a little bit more advanced forecasting since we're going to look into recurring patterns, seasonal, an exponential record casting. So as you can see in this line, it's a straight line. But we wouldn't be to plot our beverages sold and our beverages forecasted. You will see that for this one will need a different type of forecasting real quick. So let me show you why. I mean, organize these properly and put it next to our other graph. Alright? So as you can see already at this one is a little less straightforward than our first paragraph. And if we would add a trend line to this chart, so we don't do chart design and we click on Trendline. I'll go for beverages sold. You'll see that if we would apply linear forecasting is just kind of follow along this trend line. And it's definitely not in line with what we're seeing here. It looks like it's more like waves going up and going down. So in order to properly forecasters, we're gonna use a formula, exponential forecasting. So what that looks like is forecast thought ETS. We're gonna open up our parenthesis. We can click here again on these formula button to help us a little bit with the data that we have to add. And it's just like the linear one. We start off with our target date, which in this case it's the first of January 2020. Our values, some are passed known Beta, which is like a polygon, which is this range. Starting from V2. And our timeline. It's going to be the same amount of ROS as we have for our values, but then it's going to be Holland a. You can add if there is already a known seasonality. So a recurrent battery. You can add the amount of data points are x values in here, or refer to completion. So when there's, for example, twice, I'm sorry. If there's missing ordinary isn't really an unreadable for advice, you can give Excel the option to how it should interpret that as 0 or as the average. And an aggregation. You can decide upon literate. Twice. For example, beta for it a second of January. What to do with that summit? Every kid counted. So that's what you can do it every nation for now we're gonna leave this blank. These are optional. You don't need them right now. And right now we have calculated our first value on first seasonal value. And just like we should do with linear, we need to make sure that we keep those fixed so our areas still move. Let one we're going to drag down the formula. And once we've done that. So right now we have all our forecasts of data. Now let's see how that looks like. So when we look at our graph, we can already see the yellow dots that have been added. And as I already explained this trendline, boastful loaded trends. So according to this trend line, over the years, our values are going up slightly. But more evident is how it forecasts that are recurring pattern. To make it even more clear, let's create a line chart out of this instead of the dots. So we're gonna go through change chart type and we're going to look to line, are going to select the two lines right here. And as you can see now, we can even more clearly see the movement of our data and how nicely it is predicted. If we would have used linear, again, this would just be a straight line going up slightly, which in this case would definitely not be the right prediction for this type of data. To fill up this gap. Just a little trick. What you should do is at this data. So to 1349, added here as well. And then our chart should look like one line. And it does. So here is touch now. So let's quickly how you apply at a seasonality forecasts. Super useful for recurring patterns. In this case it's beverages. And if we look at our data and we can probably see, but it's also something we will be diving and later on in this class. But it's probably going to be based on temperature, based on the year. Before we're going to dive into regression analysis, which will reveal why our data base calling up and down, we are first going to dive into confidence intervals. So in the next video, I'm gonna show you how certain you can be about these predictions. At an Excel gives you the tools to visualize, calculate, and visualize a range of riotous forecasts should be falling in between based on the surgeon confidence level. So I hope I'll see you in the next video. Thank you. 6. Forecasting: Confidence Interval: Hello and welcome into our next video. So in this video we're going to calculate the confidence interval. So I'm first going to explain to you what a confidence interval is and why you want to apply it. And then I'm going to demonstrate to you how you can apply it and also how you can visualize it in the graphs that we have been creating in the previous classes. So what I mean time in-between this video and the last video, I've just formatted our sheet and little bit of added some extra columns. And I've colored smacks like orange and I've called colored beverages blue for us to get a better overview. And I've made things through Plessy. Alright, so we see here to forecast values, which we have done in the last two videos. In this one, we're going to calculate our confidence interval. So confidence interval basically is the range within our predicted values should fall. This is based on how confident are we that certain values who fall into a rage. So if we add to this formula, For example, there we want a range in which we are 95% sure. Future values will fall into statistically C. We will get a range which we do minus arm forecasted value and plus our forecasted value. And that should be where our values should be forecasted within based on these certain percentage. Sounds a little bit intimidating maybe, but we're gonna keep it simple. The whole purpose of this class is to keep these Excel features simple and easy to understand. For therefore, I'm just going to dive into it and explained you along the way when I just have been explaining to you. So we're gonna go through beverages limited and freeze the first row a little quick, just for us to have a better overview and then see the first column and the first real. Oh, that's not what I wanted to be. Free stuff role. All right. So we're going to have to confidence interval. This one is much guesswork as simpler if we use this value to create a better line in a charter wasn't in the last video, so we're gonna start up in the second value. This is the first forecasted value. What we do is we are going to write the forecast. Ets. Thus, thought it stands for a confidence interval. And we're gonna open up the parentheses. And like we've been doing in our other classes, we can click here on this button. And again, we can just fill out today are right here. So we go to park a debate which would be the case against the first of January 2020. We're going to select the range of values, which is just like the previous formulas that we had been doing. That we're going to select our timeline. And then we can select our confidence level. So this is what I've been explaining. This is kind of beat a percentage. The percentage of security we want to have that our data is going to fall within this range that we are about to calculate. Our spend. That value is 95%. For now we're just going to keep it that way so we can leave this blank. And like we've also seen in our previous video here we can add the seasonality, data completion and aggregation, which we have explained in the last video. And for now we're just going to leave that blank too. We don't meet those those steps. If you're still not sure what they mean, please go back to our last video and check on the definitions of these, or scroll down here. And you can also read what they mean and what they do. So now we click on Done, and now we get a range of our value. So 95% sure that our forecast is either going to be within 150 set X6 lower, or higher than our forecasted value. So like we have been doing in our last videos, we're going to look our range. And I were going to pull this one through. There we go. We can create our lower and upper bound, which is going to be our forecasted value minus our confidence interval. And R4 base value plus our confidence interval. So this is our range of which we are 95% sure that the real forecasted value or future value is going to be within him. Alright? So now we're gonna go back to our graph and we're going to visualize these confidence interval. So our graph should be right up here. This one. We are going to chart design up here. We're gonna select data. And we will be adding some data which is going to be until Column F, which is upper bound them. You should already be seeing our lines like we did in last class. We have to add another value to make sure that line continues nicely from our results, which we are doing by copying this one. I'm sorry, accompanying this one and pacing a year. And our graph could be a little bit and look a little bit more nice. Which is right now, let's just format it real quick and tends to callers off our lines. So we click on our length are Millenials is orange. So let's use for both the lower and the upper bound a little bit lighter and color orange, which would be this one. And now we're going to take the same one. For a lower bound. We are getting a great visualization of our range in which our forecast could be. So now, if we would increase our confidence level, just think for yourself for a second, what would happen with these two lighter lines? So I'll already tell you our forecasted value will stay the same. But something happened with the Live School Legal wider. A little, they go thinner. Alright. You still want to think about it? Just pause the video real quick. So if we increase our confidence, so if you want to be more sure about having to write prediction, which means that if it would be 98%, for example, or 99% instead of 95, are Lang should become wider. So if the lines become wider, we can say with more certainty like hey, yeah, I think we have more chance now of our forecasting value, forecasted value. The eventual results of our forecasted value will fall within the range. So the bigger range, the more certain you can be that is kinda be within the range. If we're going to decrease our confidence level to, let's say 85%. We should get a smaller range. Because pressure is kinda fade within a range. But it could also be smaller. Right? So this gives you a great overview for forecasting values. And I hope this was a useful lesson for you. And besides, it's a great way to visualize, especially if you need to report stuff and also for our project. And it's a great way to visualize the future forecasting based on the certainty that you'd need to Font Awesome. So in the next video, we're going to look into the individual forecasting data that we can get out of the formulas. Some heavy data that we can use for explaining our forecasts a little bit better and to know a little bit more about our forecasts. So thank you for watching this video, and I'll see you in the next lesson. 7. Introduction to Regression: Hello and welcome to the introduction to regression. In this video, we are going to discuss what it means, what a regression means. And this is going to give us the tools that we need in order to dive into our next two videos where we dive into making the regression analysis. So the key things in a regression analysis is that it always starts with an hypothesis. And so you'd create hypotheses to, to say, for example, I think are the hypotheses that when the temperature goes up, we sell more ice creams. Having this qualitative part of the equation, having this qualitative offer your regression after regression analysis is very important because just analyzing data doesn't necessarily give you why there is a certain relationship between two sets of data. So it's very important to also look into why there might be a certain correlation between two data points. Then the hierarchy is gonna be, which we will also be diving into our next video, progression, strength. So first of all, we want to look and see how strong is this correlation. So if we go up one degree in temperature, one degree Celsius, how much more ice cream do we think we will sell? And the last one is two. And it's going to show how certain are we that this is actually a correlation and regression that we are seeing in our data. So examples, thus, customer H affect our sales or if the US dollar value goes up, does this also affected the stock price of Google or thus employee absenteeism effect delivery periods. Those three questions can be answered on a quantitative aspects using the regression analysis. But again, we cannot directly assume that these hypotheses will be true. These questions will be true, primarily doing the quantitative part of the research. You will also have to dive into yet whether there is actually a relationship between those variables that we are seeing and dose questions. So this is how regression loops like here on the right-hand side, you see a trend line surrounded by debits. And you can see that on how hired to bought is in the y value. The more likely it is that it, I'm going to be high and the x value. So this is basically what a regression meets there closer it stays to this line. The more certain we can be about our aggression. And a more scattered and a more everywhere in a graph it is, the less significant regression will be. The slope of our line is going to determine how powerful our regression and these are things we will be looking into an annex video as well. So basically, regression analysis analyzes the relationship between the independent and dependent variables. So we have different variables like temperature, people outside and, and the people and what are the shops are open. These three variables can influence our deepen and V2 could be distills cry, scream for example. And regression analysis is going to see if, if C goes up, b goes up, or goes up if our dependent variable also goes up or down, obviously. So there was this pretty introduction of regression analysis. We are going to dive into performing a regression analysis in our next video. And I hope I'll see you there. 8. Regression: Analysis: Hello and welcome to the next video where we are going to look into regression analysis. In this video, I will be shortly expanding your wide we are contexts one of regression analysis. I'll be showing you how you can enable it in Excel. Students, it is not a default feature in Excel. I'll also show you how you can do them. How you can do the regression analysis. So what are the steps you take to create it? And lastly, we will be reviewing what to look at the output of the regression analysis. So before we start, this is one of the videos where some prior knowledge about statistics is recommended. I will try to keep it as light as possible, showed that people who don't necessarily have experienced which statistics can follow along and still apply regression analysis for their own purpose. But again, to beta, we will be looking at, you probably have a better idea of what it means if you have some experience with statistics. Alright, so before we start, we are going to look into our data. And I want to show you why you might want to do a regression analysis. So we have the beverage is sold and we have Sinek sold. We all should have to date up temperature in our previous videos. In column N. However, I have put them next to each other so you can see why we might want to consider it. So if we go to our graph that we created in our forecasting class and you can see that there's a very strong recurring pattern into beverages being sold. In order to find out why it is so fluctuating and what determines our beverages to go up and what determines them to go down. We want to do a regression analysis to see what the relationship is. So right here, we have beverages sold, and as you can see, temperature kindness seems to fluctuate along with beverages sold. So before doing our regression analysis, we want to set the hypothesis that temperature affects the number of feverishly sold. Although this is merely quantitative and you regression analysis, you're going to find out in a quantitative manner whether this is true. It doesn't necessarily mean this is correlated and this is directly related. A quick example would be when it's when a lot of ice creams are being sold. There's also a lot of shark bites. At the C. It sounds like it has nothing to do is shut each other and it doesn't. When you would do a regression analysis with those two events, you would find probably that there is regression. Although eating more ice cream has nothing to do with more people getting bitten by sharks or more bytes by sharks is not going to cause more people to buy ice cream snow. What is the independent variable? And it's just the weather. So when there is, when the weather is good and the temperatures are high and more people are going to beach where people are at risk of being bitten by sharks and more people buy ice creams. I'm just trying to show you that it's very important to think about why there might be a correlation between your data. So it's not just quantitative. There is also a qualitative part in finding relationships between data. What in this class we're merely focusing on the quantitative part. Alright, so with that said, let's start creating our regression analysis. We'll have to enable it first. It depends on the Excel version that you use, and it depends on the operating system that you use. I use a mech and I have Office 365. So for me, it would mean I have to go up here, click on tools, click on Excel Add-ins. And here you can add the analysis back. For people that are using Windows or different versions of Excel, go to options and they're good to edit. And for some people that is probably going to be right here. And here you can click on options and you can eventually select edits and then Analysis ToolPak. If you're getting stuck here, ever convince you to give it a quick Google look-up your Excel version, you've got your operating system, and you should definitely be able to find it. There's just a variety of options, how you can get there. So we are now going to de down once we have enabled our Analysis ToolPak. And we go all the way to the right, we click on data analysis, and here we can select regression. So we click on OK. And now we can start, start selecting our data. So my data is already selected. I have here a1, sorry, that should be b1 until the 85. And this one should be c1 until 85. We wanna select the labels because in row one we have labels. And our confidence level is going to be 95%, meaning that we're going to be 95% sure. And that water regression is going to give the right result. Right here we have our output of the regression analysis. And right now we're just going to highlight a few of the data that we are seeing right now to interpret our regression analysis. So we're gonna start up here. Up here we have the r square and the Adjusted R-square. So these two numbers identify the predictability of our regression. So in this case, we're looking how temperature affects beverages sold. And according to our data, It's around 85% influenced by temperature. The Adjusted R-square takes out the extremes and is a little bit more conservative than the R-square. And each case, in our case, it's almost similar. So we can say according to its aren't RSquared that are beverages sold, is being able to be predicted by temperature. Our observations right here is the number of rows that we basically had an EHR data. So if we go to our data right here, you can see that there's 84 entries from here. All the way to here to here. So that's a number of observations. Then we get to our significance level. So we're gonna significance level gives us the percentage that what we have obtained is obtained by pure randomness. So right now it's scientifically written. Um, but if we want to read this a little bit more easily, we can go to General. We can select number. And then we get 0.0.0. And then if we keep on clicking to expand, we shouldn't want point and start seeing some numbers. But this just shows us how strong and how much, how low the percentage who said this is obtained by pure random. We have seen the graph and the graph definitely indicates some strong recurring pattern. And here we start seeing that it's 0 and then 35 zeros later, 30 to 1%, that this is obtained by pure randomness. So we can basically say that what we are observing is not being obtained by random luck. And then if we go down here, we get to temperature, which is one of our dependent, independent variables. And we can see how strong our beverages sold increases per one temperature debt goes up. So if our temperature goes up with one-degree, our average expected beverages sold per month increases with 74. And our p-value is also giving us the percentage of how much chance we have that this is obtained by pure randomness. So again, if we would expand this one, we get the same result is here. Here is we have to add 40 zeros. And here we have to add 35 zeros in order to get a number. So the chances of this being obtained by pure randomness district very, very, very small. So overall, we can conclude that there is a pretty strong correlation between our temperatures and a beverages sold enter chances. The security that we have in this incredibly high. So based on this, we could say yes, various relationship between temperature and beverage resource. Alright, that was it for this video. Hope this regression analysis gave you some idea of how you can find correlations between dependent and independent variables. If you, in case you want to learn more about this and let me know in the comments below. And I could expand our classes more into statistics and a little bit more in depth on how to read each of these values. And the most important ones you know right now. But just knowing a little bit more about statistics is going to help you understand everything that you can better. Alright, thank you for watching this class and I hope I will see you in the next video. 9. Project Review: Hello and welcome to the video where we will be reviewing our project. But quick notes before you dive into this video and you haven't done the project yet, please stop the video, pause the video, and go do the project yourself first. And if you're ready to review, if you went into the right direction doing the project, then sit along with the video and we'll go over how to project could have been done. Alright, so we'll start off with our forecasting. I've already filled out the project right here. And and we'll start off with how to do the 20212022 sills forecasts. So we go to that, that data analysis. And before we can start creating artificial r chart. And we wanna forecasts are data. I have used a seasonal forecasting critics. How you could have found this out, that you should have used forecasts, thought ETS, instead of linear base by plotting the graph yourself first and looking at a pattern, you can see that this is, this must have some seasonality in it. As to say months are spiking every year, which are September, November, and December, and March also wants to spike a little bit. So with that in mind, you can already see like, hey, let's use the ETS version of forecasting to make sure that we aren't actually forecasting this property. If you wouldn't have done that, then in this graph, you should've seen a straight line following the trend line. Then if you have that, you simply create the chart. You add good trend line in case you don't know that yet, you edit by clicking on the chart, going to chart the xi1. And up here you can choose at the trend line. Then when you select a trend line and linear in this case, you get the option to base your trend line on the sales forecasted and assumes reported. But since we want to compare to linear transfers is the seasonal trends, you don't want to base it on the forecasted beta. That wouldn't make sense because if you use it and forecasts of data, you're basing your basic trends on already forecasts of beta. I'm basing that strengthen the results gives you a better comparison of your seasonal forecasting and in this case, your linear forecasting. Then we go to the 20212022, lower and higher boss. In its very similar way of getting there, Dan, the poor crass dot ETS formula. But for this one you would have to calculate first the confidence interval. In this case, we get to 70 thousand for January 21. And then we're going to use our forecasts, that data. We're going to subtract our confidence interval. And for our upper bound, we are going to plus it for from our forecasted value. And then you have the data for your upper and lower bound. And the 90 thousand failures that you see right here is copied and pasted from are already forecasted data. And this is going to help you to get rid of the gap that you might have in between your two data lines. So in between the resultant and the forecasted data lines, if you don't add this 90 thousand, you'll get a gap. And now to align nicely flows through a forecast of data lines. And what I'm referring to is this part. Right here. If you don't add to 90 thousand, then and bowed your green and your red line will start after a get. Just a little touch. It's another very groundbreaking if you don't add that. So for this one, also very important to base your trend line on the results data, reported sales data if you don't, and you're going to, first of all, having a trend line that is linear hired. It should be because it starts from 0 and it assumes that it goes from 0. And it starts out with 90,229 is going to a higher transmitted shoot half. So really base it on your sales recorded. Alright, there was a first part. Then we get to our regression analysis, something she should have taken into account here. And instead, there's two different types of analysis that we have done. So we have both our analysis like we have discussed in the class and wanted to feature from Excel with the very specific data. We also have the visual part. First of all, your and output should look like this. And we can use our data to paste it in our field right here. If you get different answers, also with the graphs that have loops different for you, please go back to the video and review which step has been going rural or open up this Excel file and see if there's any difference in formulas for the forecasting part than the ones you use? If you read it, kinda figure it out and leave a comment below this video, and I'll help you out. So our adjusted strength is 52.52. This means that our regression strength isn't that strong, like per discount or PR and marketing budgets are still, doesn't go up one on one, but it's more for 50%. So it's a mediocre regression. However, our significance is incredibly high. So 0.0.0 00 and a couple of zeros, 3-5 percent means that with this percentage visits, the percentage that are data or regression is obtained by pure randomness, by pure luck and bases incredibly low. And sort of regression seems to be pretty strong. And then we have to choose which variables. So discount or marketing budgets actually is driving this regression. I am selected marketing budget. And the reason for that is that if we go to our regression tab and you go up here, you can actually see into P value that our average, this KM given is far from bank giving us a significant to get 65%. The average discount given AS having a regression effects. And on our sales data. However, our marketing budget is zero-point, again, 00 something. I'll show you a little bit better. So 0.0.0 and a couple of 0's for 7% chance that this marketing budget progression is obtained by luck, by pure luck. So again, this is very low. And therefore you can also say that most of these numbers for significance here and our R-squared adjusted and our offscreen here, and is mainly driven by marketing bucket. So what I have done, I've created another regression analysis just to see, see that. And as you can see, without the average discount given our strength and our aggression, almost stays the same, it doesn't change much. Significance is even stronger. And our q value and our strength of that correlation is also very strong. So we can kind of already say that our regression is coming from marketing budget. The key factor in this is this number to 65% that the average discount given as actually having a regression effect correlation with Suez data. So therefore, we cannot assume that our average discount given hazard correlation, but our marketing budget does. I went through visualize this. I have also created two plus scatters. And what you can see is the here you can very visually, very nicely see whether areas or regression going on, whether there is a correlation. And here you can already see this trend line does not really go up. It does not really go down, which basically means no matter how high the discount given is, it doesn't really change the sales, as you can see. And in this one, you can see that in our marketing budget, it's clearly says that the hired a marketing budget. So to hire our thoughts. Also in most cases, the higher our Celsius. And as you can see by also this trend line going up, sort of trend line takes the average of all our different data vaults right here. So this is a great visual way to illustrate correlations and as a result of your regression analysis. However, this one is actually not created from these regression analysis. I've just used it from the raw data. So these are two different ways to analyze the regression. However, this one is more visual and more clear if you wanna briefly provide information to someone else, but this one goes a little bit more in depth whether there is really a regression going on. So that was about it. We have created our graphs are already put them in here. I upload this file as oil into class. So if you want to review it, feel free to download it and going in, going into yourself. And I have both uploaded to finish MV unfinished Persian. So yeah, in case again, any questions let me know in the video below and I'll help you out. And I'll see you in the next video where we're going to wrap up our class. Thank you for watching. 10. Outro: Great. You've made it to the end of the class. So in this class we have learned how we can do a linear and seasonal forecasts. And you've also learned how to make and analyze a regression analysis. If you still have any questions about what we've been learning in this class, feel free to leave a common below, and I will reply to you as quick as possible with a solution. In case you enjoyed this class. And you think you learned something from this class really helped me out a lot. If you would leave a review on my profile. And I hope I'll see you in any of my other classes where I, for example, teach about how to create an interactive dashboard in Excel. And also a class about the basic data analytics tools that we have an Excel. So I hope I'll see you in any of my other classes. And thank you for following this one.