1. (4 points) - Street Research Inc. conducts research on consumer behaviors. In one study, a client asked them to investigate if consumer characteristics can be used to predict the amount charged by credit cards users. Data was collected to determine the relationship between consumers' actual annual credit card charges and annual household income. A random sample of 13 consumers is presented on Problem \#1 Sheet in "Exam \#3 Problems" Excel File. a) (0.5 points) - Create a Scatter Chart for these data with actual annual amount charged on y-axis and annual household income on x-axis, including axis title and chart title. Does a linear relationship appear to be reasonable? b) (1.0 point) - Use the data to develop an estimated linear regression equation that could be used to predict the annual amount charged given the consumer's household annual income: Y=b+bcX You must use the equations for b0 and b1 presented under "Notes +Comments" on textbook's page 336 and use the approach presented on tables 7.2 and 7.3 to calculate: SSE, SST, SSR and RSquare. For this topic, you MUST NOT use the Regression feature in the Data Analysis tools box in MS-Excel. It will not be accepted. c) ( 0.5 points) - Use Excel Regression Tool to calculate the estimated linear regression equation with 99% Confidence Level. Present the R-square value and compare it to your calculations in topic (b). d) (1 point) - Now that you have verified the R-square value by calculating it with Excel Regression Tool, what is your conclusion about how the estimated linear regression explains the variability of Y4? e) (1 point) - Street Research Inc. interviewed a consumer, and he shared his annual household income as $90,000. Use the estimated linear regression equation that you obtained and estimate how much he would spend on his credit card. If this consumer had an actual annual credit card charge of $7,000, calculate the error associated to your estimation and discuss it. UPLOAD THE EXCEL SPREADSHEET FOR ALL CALCULATION IN ORDER TO GET POINTS FOR THIS PROBLEM. \begin{tabular}{|c|c|c|} Consumer \# & X1: S annual income in thousands & Y: S charged by credit card per year \\ \hline 1 & 50 & 1,455 \\ \hline 2 & 63 & 2,678 \\ \hline 3 & 74 & 3,456 \\ \hline 4 & 88 & 4,220 \\ \hline 5 & 95 & 4,100 \\ \hline 6 & 98 & 4,680 \\ \hline 7 & 99 & 5,190 \\ \hline 8 & 105 & 8,090 \\ \hline 9 & 109 & 10,021 \\ \hline 10 & 115 & 9,800 \\ \hline 11 & 78 & 4,590 \\ \hline 12 & 82 & 2,490 \\ \hline 13 & 45 & 1,345 \\ \hline \end{tabular}