Lakeway Enterprises Regression Analysis
Georganna Russell, the controller of Lakeway Enterprises, a fabricator specializing in the manufacture of copper pipe, has undertaken a project to study the behavior of overhead cost. She has assembled data for this month from each of the firm's twenty-eight manufacturing facilities. In addition, all the allocated components of the factory overhead account, such as head office costs, have been eliminated from the factory overhead account balances.
Georganna has asked you to develop a regression model to predict the level of manufacturing overhead based on the data in Table 1.
Required:
- Determine the best regression equation possible given the data in Table 1 and the fact that overhead is to be the dependent variable. You should indicate why the regression model which you selected was determined to be the best and why other models were rejected. Factors which you should consider in your assessment of potential regression models include but are not limited to the following items: (1) whether the independent variable(s) can be used to predict the dependent variable; (2) the greatest amount of confidence which can be placed in the regression equation; and (3) the significance of the coefficients of determination and correlation.
- Discuss key assumptions and limitations of the regression models which you considered.
- Make a cost prediction using the regression equation which you selected if Susan expects to incur 2,000 labor-hours, 1,000 machine-hours, 500 tons of raw materials handled, and 70 production line setups. Next, if management desires to be at least 95 percent certain that actual costs will not exceed their estimate for overhead costs, what would be their estimate of overhead? Lastly, if management desires to be 90 percent certain of the actual overhead cost, what would be their estimate of overhead?
- Discuss the problems of estimating overhead cost at the following projected activity level: 3,000 labor hours, 500 machine hours, 700 tons of raw materials handled, and
100 production line setups.
You are touse the Excel computer program and include the printout for all regression models considered.
|
Plant Number
|
Factory Overhead
|
Labor Hours
|
Machine Hours
|
Tons of Raw Material Handled
|
Production Line Setups
|
|
1
|
137,896
|
2,092
|
959
|
414
|
67
|
|
2
|
174,342
|
1,617
|
1,227
|
623
|
88
|
|
3
|
168,896
|
2,215
|
1,351
|
437
|
50
|
|
4
|
178,059
|
1,584
|
1,480
|
479
|
89
|
|
1
|
166,605
|
1,930
|
952
|
678
|
52
|
|
2
|
165,320
|
1,717
|
986
|
666
|
50
|
|
7
|
157,585
|
2,319
|
931
|
585
|
75
|
|
8
|
165,667
|
2,312
|
1,439
|
479
|
90
|
|
9
|
155,657
|
1,880
|
945
|
619
|
94
|
|
10
|
144,605
|
1,723
|
869
|
489
|
60
|
|
11
|
157,608
|
1,992
|
1,171
|
445
|
56
|
|
12
|
171,700
|
2,476
|
1,228
|
581
|
75
|
|
13
|
140,686
|
2,087
|
928
|
446
|
61
|
|
14
|
171,982
|
2,256
|
950
|
688
|
99
|
|
15
|
155,252
|
2,179
|
1,016
|
580
|
78
|
|
16
|
140,793
|
1,806
|
902
|
464
|
60
|
|
17
|
154,377
|
1,671
|
948
|
610
|
61
|
|
18
|
150,886
|
2,019
|
1,130
|
532
|
89
|
|
19
|
159,198
|
1,585
|
1,335
|
415
|
98
|
|
20
|
145,379
|
1,747
|
1,052
|
517
|
91
|
|
21
|
152,614
|
1,618
|
860
|
640
|
64
|
|
22
|
159,450
|
2,122
|
1,188
|
548
|
61
|
|
23
|
160,983
|
1,697
|
1,254
|
425
|
56
|
|
24
|
175,393
|
2,406
|
1,187
|
695
|
58
|
|
25
|
153,031
|
1,917
|
948
|
468
|
97
|
|
26
|
166,110
|
1,658
|
1,015
|
660
|
74
|
|
27
|
150,041
|
2,042
|
971
|
478
|
51
|
|
28
|
170,419
|
1,757
|
1,111
|
652
|
85
|
Table 1: Lakeway Enterprises
Attachment:- Assignment.rar