Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need some help completing this homework assignment in excel it needs to be done by 11:45 pm eastern time tonight though. NAME: Cheryl Johnson,
I need some help completing this homework assignment in excel it needs to be done by 11:45 pm eastern time tonight though.
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. Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 Number of Households 39.5 39.8 40.4 43.1 43.2 44.0 44.5 46.2 46.5 46.8 47.0 47.1 47.3 47.7 48.0 48.1 48.4 48.5 49.0 Average Household Income 34,000 34,600 41,000 38,000 39,000 42,000 44,000 49,000 46,000 51,000 48,000 47,000 50,000 51,000 53,000 54,000 55,000 53,000 57,000 Widgets Sold 5,500 5,600 5,900 5,800 6,100 6,900 6,300 7,300 6,700 6,900 7,200 7,100 7,100 7,400 7,600 7,900 7,300 8,000 8,500 Question #1 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) Estimated Year Population Prediction 1999 52.0 2000 52.1 2001 52.3 (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) Estimated Year Income Prediction 1999 58,000 2000 62,000 2001 65,000 (D) Is this a good representational model? Why or why not? (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) Estimated Estimated Year Income Population Prediction 1999 58,000 52.0 2000 62,000 52.1 2001 65,000 52.3 (C) Is this a good representational model? Why or why not? (Answer): Question #1 1. (A) Perform a regression analysis on the data to see if the population can be used to predict future sales. (B) Create a graph that shows the historical data and the regression line. (C) Predict sales for the next three years based on the population projections listed below. (fill in the blanks on the first sheet) Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 Number of Households 39.5 39.8 40.4 43.1 43.2 44.0 44.5 46.2 46.5 46.8 47.0 47.1 47.3 47.7 48.0 48.1 48.4 48.5 49.0 Average Household Income 34,000 34,600 41,000 38,000 39,000 42,000 44,000 49,000 46,000 51,000 48,000 47,000 50,000 51,000 53,000 54,000 55,000 53,000 57,000 Widgets Sold 5,500 5,600 5,900 5,800 6,100 6,900 6,300 7,300 6,700 6,900 7,200 7,100 7,100 7,400 7,600 7,900 7,300 8,000 8,500 Question #2 2. (A) 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 and the regression line. (C) Predict sales for the next three years based on the income projections listed below. (fill in the blanks on the first sheet) Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 Number of Households 39.5 39.8 40.4 43.1 43.2 44.0 44.5 46.2 46.5 46.8 47.0 47.1 47.3 47.7 48.0 48.1 48.4 48.5 49.0 Average Household Income 34,000 34,600 41,000 38,000 39,000 42,000 44,000 49,000 46,000 51,000 48,000 47,000 50,000 51,000 53,000 54,000 55,000 53,000 57,000 Widgets Sold 5,500 5,600 5,900 5,800 6,100 6,900 6,300 7,300 6,700 6,900 7,200 7,100 7,100 7,400 7,600 7,900 7,300 8,000 8,500 rical data and the ctions listed below. Question #3 3. (A) 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 on the first sheet) Year 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 Number of Households 39.5 39.8 40.4 43.1 43.2 44.0 44.5 46.2 46.5 46.8 47.0 47.1 47.3 47.7 48.0 48.1 48.4 48.5 49.0 Average Household Income 34,000 34,600 41,000 38,000 39,000 42,000 44,000 49,000 46,000 51,000 48,000 47,000 50,000 51,000 53,000 54,000 55,000 53,000 57,000 Widgets Sold 5,500 5,600 5,900 5,800 6,100 6,900 6,300 7,300 6,700 6,900 7,200 7,100 7,100 7,400 7,600 7,900 7,300 8,000 8,500 ears based onStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started