Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Carpet 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
Carpet 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 Manufactuirng 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 | ||||||||||||||
Solution: | |||||||||||||||
Direct Manufacturing labor hours | |||||||||||||||
Answer: | |||||||||||||||
$ 1,034.65 | |||||||||||||||
You need to answer: | |||||||||||||||
Number of batches | |||||||||||||||
Answer: | |||||||||||||||
Multiple regression | |||||||||||||||
Answer | |||||||||||||||
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. | |||||||||||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started