Question;Project 2 for MBA 642 Managerial Accounting. Part 1 covers chapter 3 using a predetermined overhead rate and chapter 5 using ABC method to allocate manufacturing overhead. Part 2 you must prepare 2 scattergraphs and 2 simple regression analysesPart 1--covers chapter 3 using a predetermined overhead rate and chapter 5 using ABC method to allocate manufacturing overhead.XYZ company manufactures wheel rims. The company had originally used direct labor hours as the cost driver to develop one predetermined overhead rate.You have been recently hired by this company to review the costing of this product 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 PoolMaterial HandlingMachine SetupInsertion of partsFinishingTotal Budgeted overhead costTotal Direct Labor hours The company budgets to produceRequired:1. Using the information above calculate the single predetermined overhead rate this company is using based on direct labor 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 activities.3. The company produces two wheel rims as follows along with the usage of activities:Parts per rimSetups per 1,000 rimsFinishing Direct labor hours per rimTotal direct labor hour per rimIf the company is using the predetermined overhead rate based on direct labor hours, what is the overhead cost for one rim of each wheel type?"Total Overhead cost per each rim using a predetermined overhead rate"If the company is using the ABC method, what is the overhead cost for one rim of each type?Material HandlingMachine SetupInsertion of partsFinishingTotal Overhead cost for each rim using ABC4. In addition to the overhead cost above, the rims have the following prime costs:Direct MaterialsDirect Labor"Compute the Total product cost if the companyuses the predetermined rate?""Compute the Total product cost if the companyuses the ABC rate?"5. If there is not enough experienced labor to produce more standard and deluxe rims, so the company is trying to maximize profits.Therefore the company should either produce 2,000 standard rims or 2,000 deluxe rims.Below is the sales price per rim:Sales price per rimCompute the profit for each rim using the total product cost that you feel is more accurate.Which rim would they produce and which method did you use to compute the profit? Why did you use either the predetermined rate or the ABC rate in the total product cost?Part 2Your answer will be graded based on your application of excel and the accuracy of your solution.Please make sure you have reviewed the demonstration in the Course Documents areafor chapter 6 before attempting this part of the project. I have an audio step by step excel explanationof multiple regression, simple regression, and the scattergraph. I do 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.Computer Company's ABC analysis indicates that Manufacturing overhead costs include large costs incurred for setups when a new batch of carpet is started.In the past the company has used Direct Manufacturing labor hours as the cost driver for Manufacturing Overhead costs, but you have been recentlyhired 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) formanufacturing overhead costs.Week123456789101112Do not retype the information above. You should use this sheet as your data sheet and your solution should be on separatesheets. Part 2 should contain 2-simple regression tables, 1-multiple regression table, and 2-scattergraphs when you are done.--I have done one of the scattergraphs and simple regression using direct labor for you.Required:1. Prepare 2 scattergraphs and 2 simple regression analyses***** to estimate the Manufacturing Overhead costs using each of thecost drivers above. Make sure you show the equation of the line and R-squared on each of your scattergraphs.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--which is Manufacturing overhead cost.2. Prepare a multiple regression table using batches and direct labor hours for the independent variables. Do not try to prepare a scattergraph, since you cannot make a chart with multiple X's.Using the output from the multiple regression, give the equation to estimate manufacturing overhead costs in terms of these 2 cost drivers. Type out the equation of the line on your multiple regression worksheet.X1X23. Assuming the following level of cost-driver volume, what is the estimated Manufacturing overheadcost using simple regression for each of the cost drivers and using multiple regression. You should have 3 computations for this answer. I have completed one of the answers for you. You must use cell referencing from your regression tables to compute the estimated cost to earn credit for part 3.
Paper#38490 | Written in 18-Jul-2015Price : $26