Reference no: EM131570307

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.