Problem -
XYZ Company has been using Units Produced to allocate Maintenance Costs. Cal Q. Lator, the accounting manager, has been noticing wild shifts in the amount of maintenance being charged, so he wants you to look into it. Data has been gathered for several possible cost drivers along with total maintenance. Using regression, determine which of units produced, batches processed, machine hours, or direct labor hours is the best cost driver for maintenance costs. Prepare a memo to Cal to choose the best cost driver and predict what September maintenance costs will be based on the selected cost driver.
|
Data: Month
|
Units Produced
|
Batches Processes
|
Machine Hours
|
DL Hours
|
Total Maintenance Costs
|
|
Sep-13
|
864
|
1,737
|
23,400
|
45,468
|
$ 38,159
|
|
Oct-13
|
822
|
1,734
|
28,600
|
56,887
|
$ 42,312
|
|
Nov-13
|
1074
|
1,752
|
27,950
|
95,014
|
$ 47,119
|
|
Dec-13
|
900
|
1,777
|
30,680
|
69,094
|
$ 44,190
|
|
Jan-14
|
900
|
1,792
|
31,850
|
74,941
|
$ 45,825
|
|
Feb-14
|
846
|
1,790
|
22,880
|
50,310
|
$ 39,867
|
|
Mar-14
|
912
|
1,839
|
26,000
|
71,314
|
$ 44,549
|
|
Apr-14
|
768
|
1,853
|
20,540
|
34,777
|
$ 36,321
|
|
May-14
|
948
|
1,731
|
23,725
|
49,153
|
$ 37,962
|
|
Jun-14
|
1152
|
1,737
|
27,950
|
78,947
|
$ 42,132
|
|
Jul-14
|
780
|
1,729
|
26,000
|
43,540
|
$ 38,955
|
|
Aug-14
|
750
|
1,785
|
26,650
|
41,683
|
$ 38,894
|
|
Sep-14
|
672
|
1,678
|
23,140
|
31,774
|
$ 36,821
|
|
Oct-14
|
810
|
1,566
|
26,585
|
45,556
|
$ 39,060
|
|
Nov-14
|
900
|
1,604
|
26,884
|
49,588
|
$ 38,774
|
|
Dec-14
|
888
|
1,612
|
23,205
|
43,467
|
$ 37,237
|
|
Jan-15
|
660
|
1,696
|
21,060
|
21,609
|
$ 33,158
|
|
Feb-15
|
936
|
1,930
|
22,880
|
33,061
|
$ 33,380
|
|
Mar-15
|
678
|
1,543
|
23,985
|
31,813
|
$ 36,731
|
|
Apr-15
|
804
|
1,492
|
23,530
|
35,396
|
$ 36,006
|
|
May-15
|
912
|
1,683
|
31,980
|
69,548
|
$ 44,064
|
|
Jun-15
|
960
|
1,785
|
35,399
|
83,162
|
$ 46,656
|
|
Jul-15
|
852
|
1,530
|
30,498
|
63,007
|
$ 43,488
|
|
Aug-15
|
900
|
1,870
|
29,302
|
70,367
|
$ 44,454
|
|
Estimated Activity
|
|
|
|
|
|
Sep-15
|
792
|
1,791
|
24,005
|
49,006
|
|
Required: Prepare an Excel spreadsheet to complete the following requirements. The information above is your data section.
1. Run regression for each of the potential cost drivers, and create a cost equation for each.
2. Prepare scatterplots, graphing the total maintenance cost and each of the potential cost drivers. Include a linear trendline on the graph, with le and the equation.
3. Excel spreadsheet should be formatted to print so that the page breaks make sense. Numbers should be formatted to make the report easy to read.