I am stuck working through these can you please help me :)

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: I will take off 10 pts. if you do not use the excel application appropriately. Part 1 Worth 5 pts. Each computed number is worth .25 pts. And the recommendation is 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 Industries Inc. manufactures 2 products: A and B. A review of the company's accounting records revealed the following per-unit costs and production volume: Product A Product B Production volume(units) 2500 5000 Direct labor hours used 2.0 3 Direct material cost per unit $32 Direct Labor cost: $14 per hour 2 hours at $14 3 hours at $14 $28 $50 $42 Manufacturing overhead 2 hours at $86.10 3 hours at $86.10 $172.20 $258.30 Manufacturing overhead is budgeted at : $1,722,000 Required: a. Using direct labor hours as the base for assigning manufacturing overhead cost to products, compute the direct labor needed and the predetermined overhead rate using direct labor hours. Hint: the predetermined overhead rate you compute should be $86.10, since I have given you the answer but I want to make sure you know how to determine the $71.75. Direct labor hours, budget hours Guidance: Product A Look at exhibit 5-2 on page Product B 168 Total direct labor hours Predetermined Overhead rate per direct labor hour b. Using the predetermined overhead rate you determined in "a" above, determine the unit product cost of each product. Hint: Don't forget that unit product cost includes direct material, direct labor, and Manufacturing overhead. Don't make this complicated, since the numbers are already there for you. Product A Direct material cost per unit Direct labor cost per unit Manufacturing overhead per unit Product B $32 Guidance: Make sure you are using cell references, by referencing the cells above. Total product cost per unit c. You are a recent hire to Industries Inc. and you have made it known that you just recently completed an MBA program that had a management accounting course that taught you about another costing system that might be appropriate. During a meeting, you suggested that an ABC system might be a more accurate costing system. Management has asked you to recompute the unit cost using the ABC costing approach, since the company is showing declining profits. First complete the following chart that is set up like exhibit 5-7 on page 173, and there is a more detailed explanation of the 3 step calculation approach example on page 172 exhibit 5-6. I have highlighted the columns in red you need to complete and I have started the table to guide you. Activity Setups General Factory Machine processing Grand Total Activity Cost pool Cost driver $202,000 Number of setups 1,300,000 Direct labor hours 220,000 Machine hours $1,722,000 Cost driver quantity for Cost Driver Quantity Pool rate Product line product line 100 $2,020.00 Product A Product B Total 0 Product A Product B Total 2,200 Product A Product B Total d. Complete the following table to determine the unit product costs from ABC, just like the exhibit 5-8 on page 174. Product A Product B Direct material cost per unit Direct labor cost per unit Total direct costs per unit Manufacturing overhead(based on ABC) Setups General Factory Machine processing Total ABC overhead cost per unit Total product cost per unit e. Write a short report (at least 75 words) that identifies factors that may account for the company's declining profits. Product Activity cost line for product production line volume 90 $181,800 10 100 0 0 0 1600 600 2200 Activity cost per unit of product 2,500 $72.72 5,000 2,500 5,000 2,500 5,000 Part 2 Please make sure you have reviewed the demonstration for chapter 6 before attempting this problem. I have an audio step by step excel explanation of multiple regression, simple regression, and the scattergraph. I have guidance for Mac users, too. I have a word tutorial and my excel solution is also available in the course documents area. 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 project 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. Food services company is looking at the costs involved with their catering services. In the past the company has used meals produced as the cost driver for Overhead costs, but you have been recently hired as a consultant and have decided to use your new MBA skills to make a recommendation to management on what should be the cost driver(s) for overhead costs. You have decided to include number of deliveries in your analysis, too. Month Meals produced Deliveries 1 2 3 4 5 6 7 8 9 10 1,340 1,180 1,050 930 840 780 700 630 680 760 12,690 11,980 10,950 10,280 9,020 8,130 7,540 6,980 8,930 9,800 Total Overhead Cost $ 69,094 64,927 60,332 57,953 55,984 53,119 52,706 53,874 53,445 54,869 Do not retype the information above. You should use this sheet as your data sheet and your solution should be on separate worksheets. When you have completed the requirements you should have this data sheet, 2-simple regression tables, 1-multiple regression table, and 2-scattergraphs when you are done. I already did a simple regression and scattergraph using meals produced as the independent variable for you. Required: 1. Prepare 2 scattergraphs and 2 simple regression analyses***** to estimate the Overhead costs using each of the cost drivers above. Make sure you show the equation of the line and R-squared on each of your scattergraphs. I have gone ahead and developed the simple regression table and the scattergraph using Meals produced as the cost driver (I.e. independent variable) 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. The output and chart for your simple regression should be on separate sheets that should be named appropriately. Type the equation of the line under the regression output on each of the simple regression worksheets. 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). 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. (i.e. R-squared, Intercept, and X Variable.) Hint: the Y(dependent) variable is the same in all your scattergraphs and regression analyses. 2. Prepare a multiple regression table 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. 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. Type out the equation of the line on your multiple regression worksheet. 3. Assuming the following level of cost-driver volume, what is the estimated overhead cost using simple regression for each of the cost drivers and using multiple regression to estimate the cost of overhead. You should have 3 computations for this answer (you need to determine 2 since I have calculated the first one for you). 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. Cost Drivers (independent variables): Meals produced 10,795 Deliveries 1,005 Solution: Meals Produced Answer: $ 60,820.31 You need to answer: Deliveries Answer: Guidance: I have estimated the overhead costs using the simple regression table output and the estimated cost driver level. Please click on the cell with my answer and you will see that I have referenced the simple regression output worksheet that uses meals as the independent variable. Multiple regression 4. Using the simple and multiple regression analyses above, what would you recommend to estimate the Overhead Costs? Why? Make sure you discuss the Coefficient of Determination in your response. Your comments should be at least 75 words. 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,700 units Variable Costs $9 7 4 $100,000 5 24,000 $124,000 $25 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.9218936642 R Square 0.8498879282 Adjusted R Square 0.8311239192 Standard Error 2286.5034903 Observations 10 ANOVA df Regression Residual Total Intercept X Variable 1 Y=31261.39+2.74X X=Meals SS MS F Significance F 1 236798946.412 236798946.41 45.293515319 0.0001480556 8 41824785.6885 5228098.2111 9 278623732.1 Coefficients Standard Error t Stat P-value Lower 95% 31261.386836 3984.24716921 7.8462468587 5.02014E-005 22073.6963955 2.7382048976 0.4068627486 6.7300457145 0.0001480556 1.7999777177 Upper 95% Lower 95.0% Upper 95.0% 40449.077276 22073.696396 40449.077276 3.6764320776 1.7999777177 3.6764320776 Total Overhead Cost $80,000 $70,000 $60,000 f(x) = 2.7382048976x + 31261.3868357893 R = 0.8498879282 Total Overhead Cost Linear (Total Overhead Cost) $50,000 $40,000 $30,000 $20,000 $10,000 $6,000 7,000 8,000 9,000 10,000 11,000 12,000 13,000 14,000