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.