estimated regression equation from excel data analysis

#Estimated regression equation from excel data analysis how to#

The next thing you will be asked is how much these two variables are related. It is like giving a certificate of your equation. You must give a percentage of reliability on your estimate.

In analytics, nothing is just said and believed. Now you know that you need 116 salesmen to get 2000 sales done. How many sales will increase if you increase your salesmen? Make Your Estimate More Reliable: How much workforce do you need to increase sales?

Just change the number of sales and it will show you many salesmen will it take to get that sales target achieved. Now using this you can easily conduct What-If analysis in excel. Hence, to achieve the target of 2000 Soft Drink Sales, you need an estimate of 115.71 salesmen or say 116 since it is illegal to cut humans into pieces. In cell D13 you have your required number of salesmen. Select D2 to D13 and press CTRL+D to fill down the formula in the range D2:D13 Step 4: In D2 write the formula below = C2* $B$16 + $B$17 (Regression Equation) Now we can predict possible y depending on the target x easily. Our Linear Regression Equation is = x*0.06 + (-1.11). Step 3: Find the Intercept of Regression LineĮxcel function for the intercept is = INTERCEPT(known_y’s, known_x’s) Round up to 2 decimal digits and you will get 0.06. ( Note: Slope is also called coefficient of x in the regression equation) In cell B16, write the formula below =SLOPE(B2:B12, C2:C12) Your known_y’s are in range B2:B12 and known_x’s are in range C2:C12 Step 2: Find the slope of the regression lineĮxcel Function for slopes is =SLOPE(known_y’s,known_x’s) These are some important data analytics tools. If you want, I will prepare a separate tutorial for that.

#Estimated regression equation from excel data analysis how to#

You do not need to learn how to find the slope and intercept it manually. Don’t worry, excel has functions for them. Now you must be wondering where the stat will you get the slope and intercept. The predicted y (number of salesmen) also called Regression Equation, would be x* Slope+ Intercept (relax, I’ve got it covered) We need to predict the required number of salesmen for all 11 cases to get the 12th closest prediction. The “Simple” Math of Linear Regression Equation: If we want to know how many soft drinks will be sold if we appoint 200 salesmen, then the scenario will be vice-versa. In our case, soft drinks are sold x and the number of salesmen is y. The independent variable is mostly denoted as x and dependent variable as y. So here, the dependent variable is the number of salesmen and the independent variable is sold soft drinks. You want to know the number of salesmen required to achieve 2000 sales. Now let’s compare this with your scenario. And the independent variable is often pronounced as a Driving, Predictor or simply Independent variable. The dependent variable is often called by names such as Driven, Response, and Target variable. Where one variable depends on the other independent variable. The study of the relationship between two variables is called Simple Linear Regression. This part will make you understand regression better than just telling excel regression procedure. REGRESSION ANALYSIS USING EXCEL FUNCTIONS (MANUAL REGRESSION FINDING)

To do so, we need to know Regression first. The regression equation is a tool to make such close estimates. Now based on this data you want to predict the number of salesmen required to achieve 2000 sales of soft drinks. You have 11 records of salesmen and soft drinks sold. And how can you increase your sales by changing your strategy?.How many soft drinks will be sold next year based on previous year's data?.REGRESSION ANALYSIS USING EXCEL’S ANALYSIS TOOLPAK ADD-IN.REGRESSION ANALYSIS USING EXCEL FUNCTIONS (MANUAL REGRESSION FINDING).To predict future estimates, we will study: We are into numbers and we will learn regression analysis in Excel today. Want to predict the future? No, we are not going to learn astrology. Regression is an Analysis Tool, which we use for analyzing large amounts of data and making forecasts and predictions in Microsoft Excel.