***PLEASE SHOW HOW TO SOLVE IN EXCEL***
Case Problem3:Â Â Â Â Â Â Â Consumer Research, Inc.
(Copy the worksheet named “Consumer†inQMB3200-Homework#10Data.xlsx into your file for this problem)
Consumer Research, Inc., is an independent agency that conductsresearch on consumer attitudes and behaviors for a variety offirms. In one study, a client asked for an investigation ofconsumer characteristics that can be used to predict the amountcharged by credit card users. Data were collected on annual income,household size, and annual credit card charges for a sample of 50consumers 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
- Use methods of descriptive statistics to summarize the data.Comment on the findings.
- Develop estimated regression equations, first using annualincome as the independent variable and then using household size asthe independent variable. Which variable is the better predictor ofannual credit card charges? Discuss your findings.
- Develop an estimated regression equation with annual income andhousehold size as the independent variables. Discuss yourfindings.
- What is the predicted annual credit card charge for athree-person household with an annual income of $40,000?
- Discuss the need for other independent variables that could beadded to the model. What additional variables might behelpful?
