Project 3
Name: TYPE in your name here Please use this template provided to solve project 3 which has 3 parts on separate worksheets and save your file using your first initial, last name, and project 3
Upload your solution to the project 3 link. Make sure you type your name in cell A2
You should always verify that your project file has been uploaded correctly. Remember it is your
responsibility to take the time to verify that your file has uploaded correctly. If there are problems, please contact me immediately.
You will be graded on the accuracy of your answer and the usage of excel.
You must use excel cell referencing in all of your calculations and use the data analysis tool in development of your regression tables.
The project solution is worth a total of 20 pts.
I have a demonstration exercise that demonstrates the traditional and the ABC method for allocating MOH and an audio PowerPoint of this process.
in the chapter 5 folder and an extensive regression demonstration in the chapter 6 folder.
Also, there is support material on cost-profit-volume analysis in the chapter 7 folder. Grading Rubric:
Part 1 Worth 5 pts.
Part 2 Worth 10 pts. Simple regression worth 2 1/2 pts., Scattergraph 2 pts., Multiple regression 2 1/2 pts., each estimation worth 1 pt. each, and comments 1 pt.
Part 3 Worth 5 pts. Each requirement worth 1 pt.
Project 3 Objectives:
1. Analyze manufacturing overhead
2. Develop a comparison of application of manufacturing overhead using a traditional and an ABC approach.
3. Recommend a course of action on how to apply manufacturing overhead.
4. Develop scattergraphs, simple regression output tables, and multiple output table.
5. Develop cost estimation based on your regression analysis.
6. Analyze results and recommend the best approach for the cost estimation.
7. Perform Breakeven Analysis Part 1--covers chapter 3 using a predetermined overhead rate and chapter 5 using ABC
XYZ company manufactures chemicals used in radiological imaging systems. The company had originally used machine hours as the cost driver to develop one predetermined overhead rate.
You have been recently hired by this company to review the costing of chemicals 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
Quality Assurance
Hazardous waste
control
Other overhead costs
Total Budgeted overhead cost Budgeted
Overhead
Cost
Cost driver
30,000 Weight of raw materials in pounds
20,000 Number of quality checks
Weight of hazardous chemical
100,000 used in pounds
500,000 Machine hours
$650,000 Budgeted Level
Activity
for cost Driver
Pool Rate
30,000
$1.00
1,000
8,000
100,000 Required:
1. Using the information above calculate the single predetermined overhead rate this company is using based on machine 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 3 activities.
3. An order for a special chemical mix has the following production requirements:
Material handling
Quality Assurance
Hazardous
waste
control
Other overhead costs 2000 pounds
10 inspections
200 pounds
300 machine hours If the company is using the predetermined overhead rate based on machine hours, what is the overhead cost for this order?
Total Overhead cost of this order using a
predetermined overhead rate
If the company is using the ABC method, what is the overhead cost for this order?
Material handling
Quality Assurance
Hazardous
waste
control
Other overhead costs
Total Overhead cost of this order using ABC
Part 3
Write a short report (at least 75 words) that identifies factors that explain why the two product-costing systems result in differing costs.
Which system do you recommend? Why? 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 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 chapter 6 folder. 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 problem 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.
You have just been hired as a consultant to a local hospital and have decided to use your new MBA skills to make a recommendation to management on what should be the cost
driver(s) for estimating the monthly overhead cost for the operating rooms in the hospital.
There are multiple operating rooms being used. Month
January
February
March
April
May
June
July
August
September
October
November
December Operating Room hours
used during month Number of VIP
patients during
month Operating Room Setup
Hours used during Month
590
460
440
290
230
320
390
480
560
700
590
740 279
235
172
126
103
115
183
217
265
355
312
354 Total Overhead
costs for the
operating rooms
during month
6 $
558,000
3
433,000
3
408,000
1
283,000
0
245,500
1
308,000
2
358,000
3
445,500
5
533,000
7
658,000
5
558,000
7
693,000 Do not retype the information above. You should use this sheet as your data sheet and your regression tables and scattergraphs should be on separate sheets.
When you have completed the requirements for part 2 you should have 3-simple regression tables, 1-multiple regression table,
and 3-scattergraphs when you are done. I already did a simple regression and scattergraph using operating room hours as the independent variable for you.
Required:
1. Prepare 3 scattergraphs and 3 simple regression analyses***** to estimate the overhead costs using each of the
cost drivers above. Make sure you show(insert) the equation of the line and R-squared on each of your scattergraphs.
The output for your simple regression table should be on a separate sheet and you should type out the equation of the line below the table output.
I have prepared the operating room hours scattergraph and regression for you, therefore, you actually only have to do 2.
You may want to redo this scattergraph and regression analysis just to make sure you understand the process.
If you have done your scattergraph and simple regression correctly the equation of the line and the R-squared should match the respective output.
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).
2. Prepare a multiple regression table using all three of the activities as the independent variable.
Using the output from the multiple regression, give the equation of the line to estimate factory cost in terms of the
3 cost drivers below the table output.
Also, make sure you identify which cost driver relates to X1, X2, and X3--I have done the first one for you.(excel reads left to right)
Identify the 2 other X variables(I have identified the X1 variable for you, which will be the first column highlighted):
X1
Operating room hours
X2
X3
3. Assuming the following level of cost-driver volume for month, what is the estimated
cost using simple regression for each of the cost drivers and using multiple regression. You should have
4 computations for this answer.**** I am preparing the answer using operating room hours to estimate the cost, therefore, you
only have to prepare 3 computations.
You must use cell referencing of the applicable regression table to earn credit for this requirement.
Operating room hours
Operating room setup hours
Number of VIP Patients Operating room hours to estimate overhead cost 585
270
5 549,828.54 I used the simple regression output to cell reference
and used the intercept as the fixed cost
and the X variable as the variable cost. Operating room setup hours Number of VIP Patients Multiple regression
4. Using the simple and multiple regression analyses above, what independent variable(s) would you recommend
to estimate the Overhead Costs? Why? Your response should be at least 75 words and discuss the R-Squared. Guidance:
Remember the equation of the line
is based on Y being the dependent
variable--which is overhead cost
and we are trying to estimate that
cost based on usage of the cost
driver and in this example it is
operating room hours. Part 3 relates to chapter 7
DATA
ABC, Inc. is a newly organized manufacturing business this year.
The following company's costs and expenses are:
Sales price per unit $50 Manufacturing costs: Fixed Costs Direct materials
Direct labor
Variable Manufacturing overhead
Fixed Manufacturing overhead
Period expenses:
Variable Selling and administrative expenses
Fixed Selling and Administrative expenses
Totals
Units produced
5,800 units
Units sold
5,600 units Variable
Costs
$8
7
4 $100,000
5
24,020
$124,020 $24 Required:
Use the information in the DATA field above using cell referencing to answer the following requirements.
1. Calculate the breakeven point in units. Reference page 271.
2. Calculate the breakeven point in sales dollars.
3. Calculate the safety margin. What does the margin of safety mean? Reference page 277.
understand the multiplier impact of changes in sales volume that occurs based on DOL.
4. What if the direct labor cost per unit increases from $7 a unit to $9, what will be the new breakeven in units? Explain why it changed.
You should only have to change the direct labor in the data area and actually all your answers should be updated. Please put the direct labor cost back to the original number once you have answered the question?
5. What if the manufacturing overhead cost decreases from 100,000 to 90,000, what will be the new breakeven in units? Explain why it changed.
You should only have to change the fixed MOH in the data area and actually all your answers should be updated. Please put the fixed MOH cost back to the original number once you have answered the question?
Solution:
1. 2. Break even in units
Units
Break even in sales $ 3.
Safety Margin On page 277 I realize the author uses budgeted sales revenue in the margin of safety calculation, but if you are
given the actual sales revenue you can replace budgeted sales with actual sales. What does the margin of safety mean? Be very explicit in your answer, so I know you understand this concept and the importance of it to managers. 4. What if the direct labor cost per unit increases from $7 a unit to $9, what will be the new breakeven in units? Explain why it changed.
You should only have to change the direct labor in the data area and actually all your answers should be updated. Please put the direct labor cost back to the original number once you have answered the question? 5. What if the manufacturing overhead cost decreases from 100,000 to 90,000, what will be the new breakeven in units? Explain why it changed.
You should only have to change the fixed MOH in the data area and actually all your answers should be updated. Please put the fixed MOH cost back to the original number once you have answered the question? SUMMARY OUTPUT
Regression Statistics
Multiple R
0.9982317134
R Square
0.9964665536
Adjusted R Square
0.996113209
Standard Error
9061.7374171
Observations
12
ANOVA
df
Regression
Residual
Total Intercept
X Variable 1 SS
MS
F
Significance F
1 231572599150
572599150 2820.0981476 1.357480E-013
10 821150850.174 82115085.017
11 232393750000 Coefficients Standard Error
t Stat
P-value
Lower 95%
18599.795246 8655.46122839 2.1489086203 0.0571696249
-685.777536
908.08332591 17.0999007482 53.104596295 1.35748E-013 869.982366099 Y=18,599.795+908.08X
X=Operating hours Upper 95% Lower 95.0% Upper 95.0%
37885.368028 -685.777536 37885.368028
946.18428573 869.9823661 946.18428573 Scattergraph $800,000 $700,000
f(x) = 908.0833259147x + 18599.7952461498
R² = 0.9964665536
$600,000 $500,000
Overhead cost Total Overhead costs for the operating rooms
during month $400,000
Linear (Total Overhead costs for the operating rooms
during month) $300,000 $200,000 $100,000 $200 300 400 500 600 Operating room hours 700 800

 

• 1492737780-2017SpringProject3_MBA641_MSF641.xlsx

SuperTutor

(15)

Status NEW Posted 21 Apr 2017 01:04 AM My Price 20.00

-----------

Attachments

file 1492737789-Solutions file.docx preview (56 words )

S-----------olu-----------tio-----------ns -----------fil-----------e -----------Hel-----------lo -----------Sir-----------/Ma-----------dam----------- T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------onÂ----------- th-----------is -----------web-----------sit-----------e. ----------- H-----------YPE-----------RLI-----------NK -----------&qu-----------ot;-----------htt-----------p:/-----------/wo-----------rkb-----------ank-----------247-----------.co-----------m/&-----------quo-----------t; -----------\t -----------&qu-----------ot;-----------_bl-----------ank-----------&qu-----------ot;----------- -----------Tha-----------nk -----------you----------- -----------

Not Rated(0)

• Buy Answer