Question;Part 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 Pool "Budgeted;Overhead;Cost" Cost;driver "Budgeted Level;for cost Driver""Activity;Pool Rate;Material Handling 72,000 Number of parts 12,000 $6.00;Machine Setup18,000 Number of setups per 1,000 rims 30;Insertion of parts 2,88,000 Number of parts 12,000;Finishing 2,40,000 Finishing Direct labor hours 8,000;Total Budgeted overhead cost $6,18,000;Total Direct Labor hours 13,000;The company budgets to produce 1,000 units of;each rim model;Required: Round;all answers to the nearest cent;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;Standard Deluxe;Parts per rim 5 7;Setups per 1,000 rims 15 15;Finishing Direct labor hours per rim 3 5;Total direct labor hour per rim 5 8;If 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?;Standard Deluxe;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?;Standard Deluxe;Material Handling;Machine Setup;Insertion of parts;Finishing;Total Overhead cost for each rim using ABC;4. In addition to the;overhead cost above, the rims have the following prime costs;Standard Deluxe;Direct Materials 34 51;Direct Labor 48 54;Compute the Total product cost if the company;uses the predetermined rate?;Compute the Total product cost if the company;uses 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:Standard Deluxe;Sales price per rim $920.00;$1,100.00;Compute the profit for each rim using the total product cost;that you feel is more accurate.;Standard;Deluxe;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 2;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 in the Course Documents area;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 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 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 manufacturing overhead costs.;Week;Number;of;Batches;Direct;Manufacturing;Labor-Hours;Manufacturing;Overhead;Cost;1;12;30;$;1,190;2;15;72;1,211;3;13;36;1,004;4;11;20;917;5;12;45;1,456;6;16;44;1,180;7;7;38;710;8;14;70;1,316;9;12;30;1,032;10;7;29;752;11;8;33;800;12;10;35;865;Do not retype the information above. You should use this sheet as your data sheet;and your solution should be on separate;sheets.;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;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 Direct Manufacturing labor hours 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--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.;X1 Number of batches;X2 Direct;Manufacturing labor hours;3. Assuming the;following level of cost-driver volume, what is the estimated Manufacturing overhead;cost 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.;Cost Drivers (independent variables);Number of batches 14;Direct Manufacturing labor hours 40;4. Using the simple;and multiple regression analyses output tables, what independent variable(s) would;you recommend to estimate the Indirect Manufacturing Overhead Costs? Why?;Your response should be at least 75 words and discuss the R-Squared.
Paper#49734 | Written in 18-Jul-2015Price : $37