how to do regression in excel with this data given

Project 1: 50 points 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 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 Units Produced 864 822 1074 900 900 846 912 768 948 1152 780 750 672 810 900 888 660 936 678 804 912 960 852 900 Batches Processed 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 Machine Hours 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 DL Hours 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 Estimated Activity Sep-15 792 1,791 24,005 49,006 Total Maintenance 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 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. 1 of 2 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. Grading: [50 points] Excel Spreadsheet Format = 10 points (printing, number formats, appearance) Content = 10 points Graphs = 10 points Memo Format = 10 points Content = 10 points The purpose of this assignment is to provide an introduction to Excel's statistical and graphing functions. In cost accounting regression analysis will assist the accountant by determining the effect different activities have on resource usage. This can be extremely useful when trying to (1) estimate the total cost of a cost object and (2) determine the activity base to be used to allocate overhead. This assignment will have you use the data analysis tools menu option and the chart wizard. 2 of 2