Question

***PLEASE SHOW HOW TO SOLVE IN EXCEL***

Case Problem 3:        Consumer Research, Inc.

(Copy the worksheet named “Consumer” in QMB3200-Homework#10Data.xlsx into your file for this problem)

Consumer Research, Inc., is an independent agency that conducts research on consumer attitudes and behaviors for a variety of firms. In one study, a client asked for an investigation of consumer characteristics that can be used to predict the amount charged by credit card users. Data were collected on annual income, household size, and annual credit card charges for a sample of 50 consumers and are provided in the worksheet named “Consumer.”

Income ($1000s)	Household Size	Amount Charged ($)
54	3	4016
30	2	3159
32	4	5100
50	5	4742
31	2	1864
55	2	4070
37	1	2731
40	2	3348
66	4	4764
51	3	4110
25	3	4208
48	4	4219
27	1	2477
33	2	2514
65	3	4214
63	4	4965
42	6	4412
21	2	2448
44	1	2995
37	5	4171
62	6	5678
21	3	3623
55	7	5301
42	2	3020
41	7	4828
54	6	5573
30	1	2583
48	2	3866
34	5	3586
67	4	5037
50	2	3605
67	5	5345
55	6	5370
52	2	3890
62	3	4705
64	2	4157
22	3	3579
29	4	3890
39	2	2972
35	1	3121
39	4	4183
54	3	3730
23	6	4127
27	2	2921
26	7	4603
61	2	4273
30	2	3067
22	4	3074
46	5	4820
66	4	5149

Managerial Report

1. Use methods of descriptive statistics to summarize the data. Comment on the findings.
2. Develop estimated regression equations, first using annual income as the independent variable and then using household size as the independent variable. Which variable is the better predictor of annual credit card charges? Discuss your findings.
3. Develop an estimated regression equation with annual income and household size as the independent variables. Discuss your findings.
4. What is the predicted annual credit card charge for a three-person household with an annual income of $40,000?
5. Discuss the need for other independent variables that could be added to the model. What additional variables might be helpful?

Answer 1

Descriptive Statistic

The descriptive statistic is obtained in excel by following these steps,

Step 1: Write the data values in excel.

Step 2: DATA > Data Analysis > Descriptive Statistic. The screenshot is shown below,

Step 3: Input Range: All the data column, Grouped By: Columns, tick Summary Statistic.

The result is obtained. The screenshot is shown below,

	Income ($1000s)	Household Size	Amount Charged ($)
Mean	43.48	3.42	3964.06
Standard Deviation	14.55	1.74	933.49

For Income variable,

The mean is approximately equal to the median => approximately symmetric distribution.

For Household Size variable,

The mean is greater the median => skewed distribution (skewed to right)

For Amount Charged variable,

The mean is approximately equal to the median => approximately symmetric distribution.

Regression analysis (Amount Charged~ Income)

The regression analysis is done in excel by following steps

Step 1: Write the data values in excel.

Step 2: DATA > Data Analysis > Regression > OK. The screenshot is shown below,

Step 3: Select Input Y Range: 'Amount Charged' column, Input X Range: 'Income' column then OK. The screenshot is shown below,

The result is obtained. The screenshot is shown below,

Regression analysis (Amount Charged~ Household Size)

The result is obtained. The screenshot is shown below,

Comparison of two models

	IV: Income	IV: Household Size
Overall significance	Significant	Significant
Independent variable significance	Significant	Significant
R square value	0.39813	0.56677
Standard Error	731.7132	620.793

The overall significance of the model is given by the Significance F value. Both have a significance F value < 0.05 at a 5% significance level which means the model fits the data value at the predefined significance level = 0.05.

Both the independent variables are significant at a 5% significance level in their respective models.

The R-square value tells, how well the regression model fits the data values. The R square value for the model with the household size is greater compared to a model with income as an independent variable.

The standard error for the model with the household size is less compared to a model with income as an independent variable.

Hence the model with the household size is a better fit.

Regression analysis (Amount Charged~ Income + Household Size)

Now, the regression analysis is done by taking both the independent variables.

The result is obtained. The screenshot is shown below,

The regression equation is,

For

Household Size = 3 and Income (in $'000) = 40

Other variables,

Age, level of education, current debt etc.