Question: please help on how to do regression in excel. the question is attached here. XYZ Company has been using Units Produced to allocate Maintenance Costs.
please help on how to do regression in excel. the question is attached here.

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. Units Batches Data: Machine DL Hours Total Month Produced Hours 864 822 1074 900 900 846 912 768 948 1152 780 750 672 810 900 888 660 936 678 804 912 960 852 900 Processe d 1,737 1,734 1,752 1,777 1,792 1,790 1,839 1,853 1,731 1,737 1,729 1,785 1,678 1,566 1,604 1,612 1,696 1,930 1,543 1,492 1,683 1,785 1,530 1,870 Sep-13 Oct-13 Nov-13 Dec-13 Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Jul-15 Aug-15 Estimated Activity Sep-15 23,400 28,600 27,950 30,680 31,850 22,880 26,000 20,540 23,725 27,950 26,000 26,650 23,140 26,585 26,884 23,205 21,060 22,880 23,985 23,530 31,980 35,399 30,498 29,302 45,468 56,887 95,014 69,094 74,941 50,310 71,314 34,777 49,153 78,947 43,540 41,683 31,774 45,556 49,588 43,467 21,609 33,061 31,813 35,396 69,548 83,162 63,007 70,367 792 1,791 24,005 49,006 Maintena nce Costs $ 38,159 $ 42,312 $ 47,119 $ 44,190 $ 45,825 $ 39,867 $ 44,549 $ 36,321 $ 37,962 $ 42,132 $ 38,955 $ 38,894 $ 36,821 $ 39,060 $ 38,774 $ 37,237 $ 33,158 $ 33,380 $ 36,731 $ 36,006 $ 44,064 $ 46,656 $ 43,488 $ 44,454
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
