Multiple regression analysis
Please see attached file for full problem description.
Ques. 1: Suppose that the sales manager of a large automotive parts distributor wants to estimate as early as April, the total annual sales of a region. Based on regional sales, the total sales for the company can also be estimated. If, based on past experience, it is found that the April estimates of annual sales reasonably accurate, then in future years the April forecast could be used to revise production schedules and maintain the correct inventory at the retail outlets.
Several factors appear to be related to sales, including the number of retail outlets in the region stocking the company’s parts, the number of automobiles in the region registered as of April 1, and the total personal income for the first quarter of the year. A total of five independent variables were finally selected as being the most important (according to the sales manager). Then the data were gathered for a table that for region 1 were 1.139 retail outlets stocking the company’s automotive parts, there were 9,270,000 registered automobiles in the region as of April 1, and sales for that year were $ 37, 702, 000
Annual Sales ($Mil) # of Retail outlets # of automobiles ($Mill) income ($Bill) avg. age of autos # of supervisors
Y X1 X2 X3 X4 X5
37.702 1739 9.27 85.4 3.5 9.0
24.196 1221 5.86 60.7 5.0 5.0
32.055 1646 8.81 68.1 4.4 7.0
3.611 120 3.81 20.2 4.0 5.0
17.625 1096 10.31 33.8 3.5 7.0
45.919 2290 11.62 95.1 4.1 13.0
29.600 1687 8.96 69.3 4.1 15.0
8.114 241 6. 28 16.3 5.9 11. 0
20.116 649 7.77 34.9 5.5 16.0
12.994 1427 10.92 15.1 4.1 10.0
Ques: What are the predicted Annual Sales for 1,946 retail outlets, 8.65 million registered automobiles, personal income of 93.2 billion, average age of automobile of 4.8 years, and number of supervisors 12?
NB: on the worksheet, list Annual Sales and the Independent variables beginning with X5. Be sure to highlight 3 rows and 6 columns before you type your LINEST function.
Quest. 2: The administrator of a new paralegal program at Seagate Technical College wants to estimate the grade point average in the new program. He thought that high school GPA, the verbal score on the Scholastic Aptitude Test (SAT), and the mathematics score on the SAT would be good predictors of paralegal GPA. The data on nine students are:
Student High Sch. GPA SAT (verbal) SAT (math) Paralegal GPA
1 3.25 480 410 3.21
2 1.80 290 270 1.68
3 2.89 420 410 3.58
4 3.81 500 600 3.92
5 3.13 500 490 3.00
6 2.81 430 460 2.82
7 2.20 320 490 1.65
8 2.14 530 480 3.30
9 2.63 469 440 2.33
QUES: What is the predicted Paralegal GPA for a high School GPA of 3.53, a Verbal SAT of 560, and a Math SAT of 450?
NB: On your worksheet, list Paralegal GPA last and the independent variables beginning with the math SAT. Be sure to highlight 3 rows and 4 columns before you type your LINEST function.
