Reference no: EM131571734
Project:
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 Processed
|
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 R2 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.
4. Submit both memo and spreadsheet through blackboard.
|
Develop a network diagram using microsoft visio
: Develop a 1-page network diagram using Microsoft Visio, or one of the free network diagramming programs you researched.
|
|
What is capital budgeting
: What is capital budgeting? Discuss the five main capital budgeting techniques used in evaluating projects.
|
|
How can product rule be used to find the number of functions
: How many functions are there from a set with five elements to a set with 10 elements?
|
|
Analyse the strategic positioning of easy jet
: Core competencies of Easy Jet informs its strategic planning- Analyse the factors that have to be considered by Easy Jet when formulating strategic plans
|
|
Create a cost equation for each
: Prepare an Excel spreadsheet to complete the following requirements. Run regression for each of the potential cost drivers, and create a cost equation for each
|
|
Calculate the opportunity cost
: Calculate the opportunity cost (not keeping capital in the company earning 10%) of each transaction. Calculate the cost of the lease after taxes.
|
|
Distribute five balls into three boxes
: How many ways are there to distribute five balls into three boxes if each box must have at least one ball.
|
|
Case study of the basketball league
: Suppose that a basketball league has 32 teams, split into two conferences of 16 teams each. Each conference is split into three divisions.
|
|
What is the net income
: Lifeline, Inc., has sales of $602,000, costs of $256,000, depreciation expense of $62,500, interest expense of $29,500, and a tax rate of 40 percent.
|