Please use this template providedto solve project 3 which has 3 parts on separate worksheets. You must use excel cell referencing in all of your calculations and use the data analysis tool indevelopment of your regression tables. The project solution is worth a total of 20 pts.

Project 3 Name: TYPE in your name here Please use this template provided to solve project 3 which has 3 parts on separate worksheets and save your file using your first initial, last name, and project 3 Upload your solution to the project 3 link. Make sure you type your name in cell A2 You should always verify that your project file has been uploaded correctly. Remember it is your responsibility to take the time to verify that your file has uploaded correctly. If there are problems, please contact me immediately. You will be graded on the accuracy of your answer and the usage of excel. You must use excel cell referencing in all of your calculations and use the data analysis tool in development of your regression tables. The project solution is worth a total of 20 pts. I have a demonstration exercise that demonstrates the traditional and the ABC method for allocating MOH and an audio PowerPoint of this process. in the chapter 5 folder and an extensive regression demonstration in the chapter 6 folder. Also, there is support material on cost-profit-volume analysis in the chapter 7 folder. Grading Rubric: Part 1 Worth 5 pts. Part 2 Worth 10 pts. Simple regression worth 2 1/2 pts., Scattergraph 2 pts., Multiple regression 2 1/2 pts., each estimation worth 1 pt. each, and comments 1 pt. Part 3 Worth 5 pts. Each requirement worth 1 pt. Project 3 Objectives: 1. Analyze manufacturing overhead 2. Develop a comparison of application of manufacturing overhead using a traditional and an ABC approach. 3. Recommend a course of action on how to apply manufacturing overhead. 4. Develop scattergraphs, simple regression output tables, and multiple output table. 5. Develop cost estimation based on your regression analysis. 6. Analyze results and recommend the best approach for the cost estimation. 7. Perform Breakeven Analysis Part 1--covers chapter 3 using a predetermined overhead rate and chapter 5 using ABC XYZ company manufactures chemicals used in radiological imaging systems. The company had originally used machine hours as the cost driver to develop one predetermined overhead rate. You have been recently hired by this company to review the costing of chemicals and realize that maybe a predetermined overhead rate does not give an accurate cost picture. You remember discussing overhead cost issues in MBA642 and want to implement ABC, so you have done some research and come up with the following activity cost pools and cost drivers. Activity cost Pool Material handling Quality Assurance Hazardous waste control Other overhead costs Total Budgeted overhead cost Budgeted Overhead Cost Cost driver 30,000 Weight of raw materials in pounds 20,000 Number of quality checks Weight of hazardous chemical 100,000 used in pounds 500,000 Machine hours $650,000 Budgeted Level Activity for cost Driver Pool Rate 30,000 $1.00 1,000 8,000 100,000 Required: 1. Using the information above calculate the single predetermined overhead rate this company is using based on machine hours as the cost driver. Predetermined overhead rate: 2. If the company uses the ABC approach, what would be the pool rates for the activities you have identified above. I have computed the first activity pool rate above in column E and you should compute the remainder of the rates for the other 3 activities. 3. An order for a special chemical mix has the following production requirements: Material handling Quality Assurance Hazardous waste control Other overhead costs 2000 pounds 10 inspections 200 pounds 300 machine hours If the company is using the predetermined overhead rate based on machine hours, what is the overhead cost for this order? Total Overhead cost of this order using a predetermined overhead rate If the company is using the ABC method, what is the overhead cost for this order? Material handling Quality Assurance Hazardous waste control Other overhead costs Total Overhead cost of this order using ABC Part 3 Write a short report (at least 75 words) that identifies factors that explain why the two product-costing systems result in differing costs. Which system do you recommend? Why? Part 2 Your answer will be graded based on your application of excel and the accuracy of your solution. Please make sure you have reviewed the demonstration for chapter 6 before attempting this part of the project. I have an audio step by step excel explanation of multiple regression, simple regression, and the scattergraph. I do have a word tutorial and my excel solution is also available in the chapter 6 folder. You need to have the data analysis tool available. It would be good practice for you to try to prepare the demonstration problem using my tutorial before attempting to solve this problem. This problem focuses on chapter 6, but ties in chapter 5 by trying to choose an independent variable that relates to the cost we are trying to measure. You have just been hired as a consultant to a local hospital and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for estimating the monthly overhead cost for the operating rooms in the hospital. There are multiple operating rooms being used. Month January February March April May June July August September October November December Operating Room hours used during month Number of VIP patients during month Operating Room Setup Hours used during Month 590 460 440 290 230 320 390 480 560 700 590 740 279 235 172 126 103 115 183 217 265 355 312 354 Total Overhead costs for the operating rooms during month 6 $ 558,000 3 433,000 3 408,000 1 283,000 0 245,500 1 308,000 2 358,000 3 445,500 5 533,000 7 658,000 5 558,000 7 693,000 Do not retype the information above. You should use this sheet as your data sheet and your regression tables and scattergraphs should be on separate sheets. When you have completed the requirements for part 2 you should have 3-simple regression tables, 1-multiple regression table, and 3-scattergraphs when you are done. I already did a simple regression and scattergraph using operating room hours as the independent variable for you. Required: 1. Prepare 3 scattergraphs and 3 simple regression analyses***** to estimate the overhead costs using each of the cost drivers above. Make sure you show(insert) the equation of the line and R-squared on each of your scattergraphs. The output for your simple regression table should be on a separate sheet and you should type out the equation of the line below the table output. I have prepared the operating room hours scattergraph and regression for you, therefore, you actually only have to do 2. You may want to redo this scattergraph and regression analysis just to make sure you understand the process. If you have done your scattergraph and simple regression correctly the equation of the line and the R-squared should match the respective output. 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 control (ctrl) key and highlight the Y data (i.e. the dependent variable-cost). 2. Prepare a multiple regression table using all three of the activities as the independent variable. Using the output from the multiple regression, give the equation of the line to estimate factory cost in terms of the 3 cost drivers below the table output. Also, make sure you identify which cost driver relates to X1, X2, and X3--I have done the first one for you.(excel reads left to right) Identify the 2 other X variables(I have identified the X1 variable for you, which will be the first column highlighted): X1 Operating room hours X2 X3 3. Assuming the following level of cost-driver volume for month, what is the estimated cost using simple regression for each of the cost drivers and using multiple regression. You should have 4 computations for this answer.**** I am preparing the answer using operating room hours to estimate the cost, therefore, you only have to prepare 3 computations. You must use cell referencing of the applicable regression table to earn credit for this requirement. Operating room hours Operating room setup hours Number of VIP Patients Operating room hours to estimate overhead cost 585 270 5 549,828.54 I used the simple regression output to cell reference and used the intercept as the fixed cost and the X variable as the variable cost. Operating room setup hours Number of VIP Patients Multiple regression 4. Using the simple and multiple regression analyses above, what independent variable(s) would you recommend to estimate the Overhead Costs? Why? Your response should be at least 75 words and discuss the R-Squared. Guidance: Remember the equation of the line is based on Y being the dependent variable--which is overhead cost and we are trying to estimate that cost based on usage of the cost driver and in this example it is operating room hours. Part 3 relates to chapter 7 DATA ABC, Inc. is a newly organized manufacturing business this year. The following company's costs and expenses are: Sales price per unit $50 Manufacturing costs: Fixed Costs Direct materials Direct labor Variable Manufacturing overhead Fixed Manufacturing overhead Period expenses: Variable Selling and administrative expenses Fixed Selling and Administrative expenses Totals Units produced 5,800 units Units sold 5,600 units Variable Costs $8 7 4 $100,000 5 24,020 $124,020 $24 Required: Use the information in the DATA field above using cell referencing to answer the following requirements. 1. Calculate the breakeven point in units. Reference page 271. 2. Calculate the breakeven point in sales dollars. 3. Calculate the safety margin. What does the margin of safety mean? Reference page 277. understand the multiplier impact of changes in sales volume that occurs based on DOL. 4. What if the direct labor cost per unit increases from $7 a unit to $9, what will be the new breakeven in units? Explain why it changed. You should only have to change the direct labor in the data area and actually all your answers should be updated. Please put the direct labor cost back to the original number once you have answered the question? 5. What if the manufacturing overhead cost decreases from 100,000 to 90,000, what will be the new breakeven in units? Explain why it changed. You should only have to change the fixed MOH in the data area and actually all your answers should be updated. Please put the fixed MOH cost back to the original number once you have answered the question? Solution: 1. 2. Break even in units Units Break even in sales $ 3. Safety Margin On page 277 I realize the author uses budgeted sales revenue in the margin of safety calculation, but if you are given the actual sales revenue you can replace budgeted sales with actual sales. What does the margin of safety mean? Be very explicit in your answer, so I know you understand this concept and the importance of it to managers. 4. What if the direct labor cost per unit increases from $7 a unit to $9, what will be the new breakeven in units? Explain why it changed. You should only have to change the direct labor in the data area and actually all your answers should be updated. Please put the direct labor cost back to the original number once you have answered the question? 5. What if the manufacturing overhead cost decreases from 100,000 to 90,000, what will be the new breakeven in units? Explain why it changed. You should only have to change the fixed MOH in the data area and actually all your answers should be updated. Please put the fixed MOH cost back to the original number once you have answered the question? SUMMARY OUTPUT Regression Statistics Multiple R 0.9982317134 R Square 0.9964665536 Adjusted R Square 0.996113209 Standard Error 9061.7374171 Observations 12 ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 231572599150 572599150 2820.0981476 1.357480E-013 10 821150850.174 82115085.017 11 232393750000 Coefficients Standard Error t Stat P-value Lower 95% 18599.795246 8655.46122839 2.1489086203 0.0571696249 -685.777536 908.08332591 17.0999007482 53.104596295 1.35748E-013 869.982366099 Y=18,599.795+908.08X X=Operating hours Upper 95% Lower 95.0% Upper 95.0% 37885.368028 -685.777536 37885.368028 946.18428573 869.9823661 946.18428573 Scattergraph $800,000 $700,000 f(x) = 908.0833259147x + 18599.7952461498 R = 0.9964665536 $600,000 $500,000 Overhead cost Total Overhead costs for the operating rooms during month $400,000 Linear (Total Overhead costs for the operating rooms during month) $300,000 $200,000 $100,000 $200 300 400 500 600 Operating room hours 700 800