MBA 642 Managerial Accounting Project Part 1 & 2

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 analyses

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
Material Handling
Machine Setup
Insertion of parts
Finishing
Total Budgeted overhead cost

Total Direct Labor hours
The company budgets to produce

Required:
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 rim
Setups per 1,000 rims
Finishing Direct labor hours per rim
Total direct labor hour per rim

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?

"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 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:

Direct Materials
Direct Labor

"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:
Sales price per rim

Compute 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 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
1
2
3
4
5
6
7
8
9
10
11
12

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.

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
X2

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.