ANSWER IN EXCEL$\backslash$table$[[$NAME:$,,,]]$

Cheryl Johnson, the director of marketing for the Thingamajig Company, has collected data on the annual number of households in the state, the adjusted average annual household income, and Thingamajig's sales of widgets. She would like you to analyze these data for developing a forecast of future widget sales.

$\backslash$table$[[,,$Average,$],[,$Number of$,$Household,Widgets$],[$Year$,$Households,Income,Sold$],[1980,39\mathrm{.}5,34,000,5,500],[1981,39\mathrm{.}8,34,600,5,600],[1982,40\mathrm{.}4,41,000,5,900],[1983,43\mathrm{.}1,38,000,5,800],[1984,43\mathrm{.}2,39,000,6,100],[1985,44\mathrm{.}0,42,000,6,900],[1986,44\mathrm{.}5,44,000,6,300],[1987,46\mathrm{.}2,49,000,7,300],[1988,46\mathrm{.}5,46,000,6,700],[1989,46\mathrm{.}8,51,000,6,900],[1990,47\mathrm{.}0,48,000,7,200],[1991,47\mathrm{.}1,47,000,7,100],[1992,47\mathrm{.}3,50,000,7,100],[1993,47\mathrm{.}7,51,000,7,400],[1994,48\mathrm{.}0,53,000,7,600],[1995,48\mathrm{.}1,54,000,7,900],[1996,48\mathrm{.}4,55,000,7,300],[1997,48\mathrm{.}5,53,000,8,000],[1998,49\mathrm{.}0,57,000,8,500],[$Question #$1,,]]$

Question #$1$

$($A$)$ In sheet Question #$1,$Perform a regression analysis on the above data to see if the population can be used to predict future sales. $($B$)$ Create a graph that shows the historical data, the regression line and $95\%$ confidence interval lines. $($C$)$ Predict sales for the next three years based on the population projections listed below.

$($fill in the blanks below$)$

$($D$)$ Is this a good representational model? Why or why not? $($Answer$)$:

Question ##$2$

$2.($A$)$ In sheet Question #$2$ perform a regression analysis on the above data to see if household income can be used to predict future sales. $($B$)$ Create a graph that shows the historical data, the regression line and $95\%$ confidence interval lines. $($C$)$ Predict sales for the next three years based on the income projections listed below.

$($fill in the blanks below$)$

$\backslash$table$[[,$Estimated,,,$],[$Year$,$Income,Prediction,,$],[,58,000,,,],[1999,,,],[2001,62,000,,,],[,65,000,,,],[,,,,]]$

$($D$)$ Is this a good representational model? Why or why not?

$($Answer$)$:

$($Answer$)$:

Question #$3$

$3.($A$)$ In sheet Question #$3$ perform a multiple regression analysis on the above data to see if household income and population can be used to predict future sales. $($B$)$ Predict sales for the next three years based on the income and population projections listed below.

$($fill in the blanks below$)$

$\backslash$table$[[$IEfiamins$,,],[,$Estimated,Estimated,$],[$Year$,$Income,Population,Prediction$],[1999,58,000,52\mathrm{.}0,],[2000,62,000,52\mathrm{.}1,],[2001,65,000,52\mathrm{.}3,]]$

$($C$)$ Is this a good representational model? Why or why not?

$($Answer$)$:

PLEASE SHOW EXCEL FORMULAS