12-2. Egghead.com is a large computer discount store that sells computers and an
ID: 408512 • Letter: 1
Question
12-2. Egghead.com is a large computer discount store that sells computers and ancillary equipment and software in the
US and Canada. It has collected historical data on computer sales and printer sales for the past 10 years as follows:
Year
Personal Computer Sales
Printers Sold
1
1040
406
2
1600
616
3
861
335
4
1203
533
5
980
319
6
1118
445
7
1063
571
8
1300
628
9
1505
644
10
1240
503
a. Use the SLOPE and INTERCEPT functions to develop a linear trend line model to forecast printer demand in year 11.
b. Use the SLOPE and INTERCEPT functions to develop a linear regression model relating printer sales to computer sales to forecast printer demand in year 11 if 1250 computers are sold.
Egghead.com believes its printer sales are also related to the average price of its printers. It has collected historical data on average printer prices for the past 10 years, as follows:
Year
Average Printer Prices ($)
1
480
2
495
3
529
4
424
5
419
6
376
7
354
8
303
9
282
10
253
c. Use the Data Analysis Toolpak in Excel to develop a multiple regression equation for these data. (Hint: multiple regression means that you use more than one x variable to predict changes in the y variable. Look at Step d, below. Which variable are you predicting? Which variables – or values – are you given in order to predict it?)
d. Identify the coefficient of determination, the slope coefficients and the intercept value in the multiple regression summary output that was produced by Excel.
e. Determine a forecast for printer sales based on personal computer sales of 1250 units and an average printer price of $275.
f. Compute the coefficient of determination for the linear trend line and linear regression equations (note that there are Excel functions that do this), and compare them to the one obtained for the multiple regression. What does this tell you? Of the three methods, which appears to yield the best forecast? Explain your answer.
g. Would it be reasonable to use the multiple regression model to forecast the number of printers that would be sold if you had computer sales of 2000units and an average printer price of $200? Explain your answer.
Year
Personal Computer Sales
Printers Sold
1
1040
406
2
1600
616
3
861
335
4
1203
533
5
980
319
6
1118
445
7
1063
571
8
1300
628
9
1505
644
10
1240
503
Explanation / Answer
Answer:
Year Personal Computer Sales Printers Sold 1 1040 406 2 1600 616 3 861 335 4 1203 533 5 980 319 6 1118 445 7 1063 571 8 1300 628 9 1505 644 10 1240 503 Slope of the function = 0.436847344 Interceptof the function = -20.28518646 Trendline = 434.0360511Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.