Question

Account Balance	Income	Years of Education	Size of Household
8976	63	12	2
8308	37	14	2
10028	52	16	2
11256	64	15	4
9869	47	17	2
10194	74	15	2
8706	49	12	2
9557	58	14	2
10565	70	16	3
9434	69	11	3
9687	25	18	3
9490	57	15	1
8806	46	14	3
9561	48	16	2
11757	80	15	3
9406	66	14	2
11150	46	15	3
7671	28	12	2
8803	53	13	1
9571	52	15	2
9566	77	12	3
7885	32	14	3
9773	55	11	1
9121	52	15	2
9298	43	14	3
10285	65	15	2
7801	38	12	1
9323	52	14	2
8643	36	16	3
12466	85	15	2
9447	64	14	2
10727	86	15	2
9243	57	15	3
9311	68	12	2
11033	74	14	3
11721	82	16	2
8727	24	15	3
8438	37	15	3
8317	55	12	2
8617	50	14	1
9052	39	16	3
10889	73	15	3
7766	26	14	1
9189	47	15	2

2. The data in BUSI1013 Credit Card Balance.xlsx is collected for building a regression model to predict credit card balance of retail banking customers in a Canadian bank. Use this data to perform a simple regression analysis between Account balance and Income (in thousands). (12 points)
   
   1. Develop a scatter diagram using Account Balance as the dependent variable y and Income as the independent variable x.
   2. Develop the estimated regression equation.
   3. Use the estimated regression equation to predict the Account Balance of a customer with Income of $58 thousands.
   4. Use the critical-value approach to perform an F test for the significance of the linear relationship between account balance and Income at the 0.05 level of significance.
   5. What percentage of the variability of Account Balance can be explained by its linear relationship with Income?
   6. Use the p-value approach to perform a t test for the significance of the linear relationship between Account Balance and Income at the 0.05 level of significance.

Answer 1

Excel > Data > Data Analysis > Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.744973573
R Square	0.554985625
Adjusted R Square	0.544390045
Standard Error	753.2654288
Observations	44
ANOVA
	df	SS	MS	F	Significance F
Regression	1	29720290.93	29720290.93	52.37897369	6.66571E-09
Residual	42	23831169.86	567408.8062
Total	43	53551460.8
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	6804.237015	393.7127881	17.28223522	1.05681E-20	6009.692441	7598.781588	6009.692441	7598.781588
Income(X)	49.99857199	6.908425974	7.237331946	6.66571E-09	36.05680394	63.94034005	36.05680394	63.94034005

a)

b)

Regression Equation

Y = 6804.2370 + 49.9986 * X

or

Account balance = 6804.2370 + 49.9986 * Income

c)

If Income = $58 thousands

Account balance = 6804.2370 + 49.9986 * 58 = 9704.1558

d)

Hypothesis:

H0 : β1 = 0

Ha : β1 not = 0

Test:

F stat = 52.3791

df1 = 1 and df2 = 42 and alpha = 0.05

FL critical = 0.001

FU critical = 5.404

Fstat > FU critical, Reject H0

There is a significant linear relationship between account balance and Income at the 0.05 level of significance.

e)

R^2 = 0.5551

55.51% of the variability of Account Balance can be explained by its linear relationship with Income

f)

Hypothesis:

H0 : β1 = 0

Ha : β1 not = 0

Test:

t stat = 7.2373

P value = 0

P value < 0.05, Reject H0

There is a significant linear relationship between account balance and Income at the 0.05 level of significance.

Note: All values from Excel output