M 1 Part 2 2 Food services company is looking at the costs involved with the catering services they provide 3 In the past the company has used meals produced as the cost driver for overhead costs, but you have been recently 4 hired as a consultant and have decided to use your new accounting skills to make a recommendation to management on what should be the cost driver's) for 5 overhead costs. You have decided to include number of deliveries in your analysis, too. 6 5 Total Meals Overhead 7 Month Deliveries produced Cost 8 1 1,340 12,690 $ 69,094 9 9 2 1,180 11,980 64,927 10 3 1,050 10,950 60.332 11 4 930 10,280 57,953 12 840 9,020 55,984 13 6 780 8,130 53,119 14 7 700 7,540 52.706 15 8 630 6,980 53,874 16 9 680 8,930 53,445 17 10 760 9,800 54,869 18 19 Do not retype the information above. You should use this worksheet as your data sheet and your solution should be on separate worksheets. 20 When you have completed the requirements you should have this data sheet, 2-simple regression tables, 1-multiple regression table, 21 and 2-scattergraphs when you are done. I already did a simple regression and scattergraph using meals produced as the independent variable for you. 22 23 Required: There are 4 areas to this part that are listed below with specific requirements. 24 1. I have developed the simple regression table and the scattergraph using Meals produced as the cost driver (I.e. independent variable) 25 You do not need to redo these, but you may want to use these as a verification that you know how to develop a scattergraph and a simple regression table 26 Prepare the scattergraph and simple regression analysis if deliveries is the independent variable to estimate the Overhead costs. 27 Make sure you show the equation of the line and R-squared on each of your scattergraphs. 28 The output and chart for your simple regression should be on separate sheets that should be named appropriately 29 Type the equation of the line under the regression output on each of the simple regression worksheets. 30 To make a scattergraph with data from nonadjacent columns highlight the X data (i.e. independent variable-cost driver) first and then hold down the 31 control (ctrl) key and highlight the Y data (1.e. the dependent variable-cost), 32 If you have done your scattergraphs correctly the equation of the line and the R-squared should match the output you have on your regression table. 33 (1.e. R-squared, Intercept, and X Variable.) Hint: the Y(dependent) variable is the same in all your scattergraphs and regression analyses. 34 35 36 2. Prepare a multiple regression table using the 2 independent variables-Deliveries and meals produced. Do not try to prepare a scattergraph, since you can make a chart with multiple X's. 37 Using the output from the multiple regression, give the equation to estimate overhead costs in terms of these 2 cost drivers. Excel reads left to right, so X1 will be Deliveries and X2 will be meals produced. 38 Type out the equation of the line on your multiple regression worksheet. 39 40 3. Assuming the following level of cost-driver volume, what is the estimated overhead 41 cost using simple regression for each of the cost drivers and using multiple regression to estimate the cost of overhead. You should have 42 3 computations for this answer (you need to determine 2 since I have calculated the first one for you). 43 Your calculation should only use cell references, since you should be estimated the cost by referencing the cost drivers below and the output from the regression tables. 44 Cost Drivers (independent variables): 45 Meals produced 10,794 46 Deliveries Guidance 1,002 I have estimated the overhead costs using the simple regression 47 table output and the estimated cost driver level. Please click on 48 Solution: the cell with my answer and you will see that I have referenced the simple regression Output worksheet that uses meals as the Meals Produced independent variable 50 Answer: 51 $ $ 60.817.57 52 53 You need to answer. 54 Deliveries 55 Answer 56 57 58 Multiple regression 59 50 51 52 4. Using the simple and multiple regression analyses above, what would you recommend 53 to estimate the Overhead Costs? Why? Make sure you discuss the Coefficient of Determination in your response. Your comments should be at least 50 words. 54 55 56 57 19