Question

1. Use data in BUSI1013 Credit Card Balance.xlsx to complete the following. You will need to use a statistical package such as StatTools or the Regression program within Excel’s Data Analysis Add-in to generate the estimated regression equation and the ANOVA etc. (12 points)
   What is the estimated regression equation using Account Balance as the dependent variable, and Income, Years of Education, as well as Size of Household as the independent variable?
2. a.Comment on the goodness of fit of the model using the coefficient of determination.
3. b.Conduct an F test with the critical value approach to see whether the overall model is significant. Use α = 0.01.
4. c.Perform a t test with p-value approach for the significance of the Income variable. Use α = 0.05.
5. d.Perform a t test with the p-value approach for the significance of the Size of Household variable. Use α = 0.05.
6. e.Estimate the Account Balance of a customer who has an income of $62 thousand, 14 years of education, and a household size of 3.

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

Answer 1

Following is the output of regression analysis:

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.878541582
R Square	0.771835311
Adjusted R Square	0.75472296
Standard Error	552.6878046
Observations	44
ANOVA
	df	SS	MS	F	Significance F
Regression	3	41332908.42	13777636.14	45.1039885	6.6487E-13
Residual	40	12218552.37	305463.8093
Total	43	53551460.8
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	2095.365223	841.1043503	2.491207211	0.016979296	395.4299369	3795.300509
Income	51.95408819	5.100340752	10.18639552	1.13077E-12	41.64591512	62.26226126
Years of Education	288.9485028	56.13511891	5.147374912	7.39444E-06	175.4951965	402.401809
Size of Household	207.4655622	121.5575607	1.706726929	0.095626051	-38.21142968	453.142554

The estimated regression equation is

Account-balance = 2095.3652 + 51.9541 * Income + 288.9485 * Years-of-education + 207.4656 * Size-of-household

----------------------------------------------------

(a)

The coefficient of determination is : 0.7718

That is 77.18% of variation in dependent variable is explained by independent variable.

(b)

The critical value of F using excel function "=FINV(0.01,3,40)" is 4.313.

Since F = 45.104 and it is greater than critical value 4.313 so we model is significant at 0.01 level of significance.

(c)

The p-value of income is 0.0000. Since p-value is less than α = 0.05 so we can conclude that income is significant to the model.

(d)

The p-value of size of household is 0.0956. Since p-value is not less than α = 0.05 so we cannot conclude that size of household is significant to the model.

(e).

The predicted value of Account Balance is

Account-balance = 2095.3652 + 51.9541 * 62 + 288.9485 * 14 + 207.4656 * 3 = 9984.1952

Answer: $9984.20