BA201 Regression Analysis: Excel and Equations

Return to

BA201 and BA321

Regression Analysis: Excel and Equations

Excel Commands - in Office 97 and Office 2000

§ Scatter Diagram § Simple Linear Regression §

§ Regression line Slope, b₁ § Regression line Intercept, b₀ §

§ Regression § Trend for Regression § Trend line for Regression §

§ Forecast § Standard Error § t* and p-values and Hypothesis testing §

§ Confidence Interval for b1 § Coefficient of Determination §

§ Coefficient of Determinations using Paste Function §

§ Equations §

1. Scatter Diagram: Click Chart Wizard-XY Scatter.

2. Simple Linear Regression

(a) Regression line Slope, b₁- Click Paste Function-Statistical-Slope

(b) Regression line Intercept, b₀- Click Paste Function-Statistical-Intercept

(c) Regression- Click Tools-Data Analysis-Regression If Data Analysis does not appear, then click Tools-Add-Ins and click the top two boxes, Analysis Toolpak and Analysis Toolpak-VBA.

<####) Trend for Regression: Click Paste Function-Statistical-Trend

(e) Trend line for Regression: Click Chart Wizard-XY Scatter

(f) Forecast: Click Paste Function-Statistical-Forecast

3. Standard Error: Click Tools-Data Analysis-Regression (look at the Standard Error in the first two blocks of numbers)

Problem: What is the relationship between the number of punts and the number of points scored in data gathered from 5 football games?

4. t* and p-values and Hypothesis testing: Click Tools-Data Analysis-Regression (look at t Stat and P-value in last block of numbers)

Ho: b₁ => 0

Ha: b₁ < 0

a = 0.10

5. Confidence Interval for b1: Click Tools-Data Analysis-Regression (look at last group of numbers)

6. Coefficient of Determination: Click Tools-Data Analysis-Regression (look at first two groups of numbers). Also click Paste Function-Statistical-RSQ. (see below spreadsheet).

(a) Coefficient of Determinations using Paste Function: Click Paste Function-Statistical-RSQ

Equations

§ Sd² § b₁ § § b_o § Yhat § Y model § s² § t* with b₁ §

§ Two Tail Hypothesis Test on the Slope of Regression Line, b₁ §

§ One Tail Hypothesis Test (Right) on the Slope of Regression Line, b₁ §

§ One Tail Hypothesis Test (Left) on the Slope of Regression Line, b₁ §

§ CI for b₁ § t* with r § SS_y § r² § Sums of Squares §

1. SS(y - yhat)² = SSE = SS_y - [SCP_xy]² / SS_x

(a) SCP_xy = [Sxy - (Sx)(Sy) / n])

(b) SS_x = [Sx²- (Sx)² / n]

(c) SS_y = [Sy²- (Sy)² / n]

2. b₁ = D y / D x

= [Sxy - (Sx)(Sy) / n] / [Sx²- (Sx)² / n]

= SCP_xy / SS_x

3. b_o = ybar - b₁(xbar)

(a) xbar = Sx / n

(b) ybar = Sy / n

4. Yhat = b_o + b₁[x]

5. Y = b_o + b₁[X] + e

6. s² = s²e(hat) = estimate of s²e = SSE / [n- 2 ] = MSE

s = Ös² = Ö(SSE / [n - 2]) = ÖMSE

7. t* = [b₁ - b₁] / S_b1 = [b₁ - b₁] / [ s / ÖSS_x ]

S_b1 = s / ÖSS_x

8. Two Tail Hypothesis Test on the Slope of the Regression Line, b₁

H_o: b₁= 0

H_a: b₁¹ 0

Reject H_o if |t*| > t _{a / 2,(n
- 2)}

FTR(Support) H_o if |t*| £ t _{a / 2,(n - 2)}

9. One Tail Hypothesis Test (Right) on the Slope of the Regression Line, b₁

H_o: b₁£ 0

H_a: b₁> 0

Reject H_o if t* > t _{a, (n
- 2)}

FTR(Support) H_o if t* £ t _{a, (n - 2)}

10. One Tail Hypothesis Test (Left) on the Slope of the Regression Line, b₁

H_o: b₁³ 0

H_a: b₁< 0

Reject H_o if t* < - t _{a, (n
- 2)}

FTR(Support) H_o if t* ³ - t _{a, (n - 2)}

11. CI for b₁= b₁ ± [t _{a / 2,(n - 2)}]S_b1

12. t* = r / Ö[(1 - r²) / (n - 2)]

13. SS_y = SSR + SSE

14. r² = SSR / SS_y

= 1 - SSE / SS_y

= [SCP_xy]² / (SS_x)( SS_y)

= (correlation coefficient)²= (r)²

15. SS_y = S( y - ybar)² = Sy² - (Sy)² / n

SSR = S(yhat - ybar )² = (SCP_xy)² / [SS_x]

SSE = S(y - yhat )² = SS_y - [SCP_xy]²/SS_x

Put "BA201- your section number " or BA321 and your last name somewhere in the subject line for your e-mail.

e-mail and

Back to top

Copyright 2001NAU and CBA
ALL RIGHTS RESERVED Dr. James V. Pinto