Question

estimate the following linear regression equation using the data below: y=B0+B1X In this exercise, you need to estimate ŷ values for given y and x values using Excel. What are the estimated values of B0 and B1 ? x = 2, 4, 6, 8, 10 y = 10, 12, 20, 25, 40 ŷ =

Answer 1

To solve this problem, we will move step by step,

So here we have to find out regression equation (y^{^})

and find out estimated values of B₀ and B₁.

So equation for regression line is

y^

B₁ - This is the SLOPE of the regression line. and this is number that the Y variable (dependent) will change for each 1 unit change in the X variable.
B₀ - This is the intercept of the regression line with the y-axis, and it is value of Y if the value of X = 0.

Now we will put above value of X and Y in Excel to perform Regression

Firstly you need 'Data Analysis' in your Excel tool
( if it is there then skip below step)
Enable the Analysis ToolPak add-in (Data Analysis)
Analysis ToolPak is available in all versions of Excel but is not enabled by default. So, you need to turn it on manually.

In your Excel, click File > Options.
In the Excel Options dialog box, select Add-ins on the left sidebar, make sure Excel Add-ins is selected in the Manage box, and click Go.

click on Data and Data Analysis tools to the Data tab of your Excel ribbon. )

Regression Analysis Steps in Excel:

Click on Data >> Data Analysis >> Select Regression

Then ( Note:  Be careful while selecting Y range and X range , and please do not select Y and X select only numerical data)

Here confidence level is not given so we will consider default 95%.

So to find B₀ and B₁

we will see in coefficient in the output,

B₀ = intercept of regression line = -0.5

B₁ = Slope regression line = 3.65

So Required estimate of y^

Putting above values in to given equation,

y^ = -0.5 + 3.65 *X