ROXY08
Oct 7, 2014, 05:32 PM
I am so lost and my teacher is of no use. I am wondering if someone would be able to guid e me through, or if you have a similar example email it to me to follow. It would be so appreciated.
Cost of goods manufactured
Original Data
3 departments Cutting Assembly Finishing
Material used 30,000ft 0 10,000 gallons
material cost $2.00 per foot 0 $.20 per gallon
Labor incurred 300 hrs 600 hrs 1,200 hours
Labor cost $8.00/hour $10.00 per hour $5.00 per hour
Machine Hours 150m hrs 100 m hours 0
Overhead:
Base Machine Hrs Labor Cost Labor Hours
Budgeted 10,000 mach hrs $6,000 labor cost 1,000 labor cost
Budgeted Overhead $20,000 $12,000 $6,000
Profit Margin: 30% of cost
Required: Total cost by department
total cost by element
total cost by job
Test Data
3 departments Cutting Assembly Finishing
Material used 40,000ft 0 12,000 gallons
material cost $1.40 per foot 0 $.15 per gallon
Labor incurred 500 hrs 800 hrs 1,500 hours
Labor cost $9.00/hour $12.00 per hour $6.00 per hour
Machine Hours 2000m hrs 150 m hours 0
Overhead:
Base Machine Hrs Labor Cost Labor Hours
Budgeted 20,000 mach hrs $10,000 labor cost 2,000 labor cost
Budgeted Overhead $42,000 $30,000 $12,000
Profit Margin: 35% of cost
Required: Total cost by department
total cost by element
total cost by job
JOB COSTING
Cutting Assembly Finishing Total
Material $60,000 0 $2,000 $62,000
Direct Labor 2,400 $6,000 $6,000 14,400
Factory Overhead 300 12,000 7,200 19,500
Total cost $62,700 $18,000 $15,200 $95,900
Profit Margin (30%) 28,770
Selling Price $124,670
Cutting $20,000/10,000 = $2 per machine hour *150 machine hours =$300
Assembly $12,000/6,000 = 200% of labor cost*$6,000 =$12,000
Finishing $6,000/1,000 =$6 per labor hour * 1,200 labor hours = $7,200
TEST DATA
Cutting Assembly Finishing Total
Material $56,000 0 $1,800 $57,800
Direct Labor 4,500 $9,600 $9,000 23,100
Factory Overhead 420 28,800 9,000 38,220
Total cost $60,920 $38,400 $19,800 $119,120
Profit Margin (30%) 41,692
Selling Price $160,812
Cutting $42,000/10,000 = $2.10 per machine hour *200 machine hours =$420
Assembly $30,000/10,000 = 300% of labor cost*$9,600 =$28,800
Finishing $12,000/2,000 =$6 per labor hour * 1,500 labor hours = $9,000
Prepare an Excel using formulas in all calculations. Schedule/statement is to be in one worksheet and data on another worksheet. Use IF statement to display Original or the Test Data in schedule/statement.
I am so lost and in need of serous help. If anyone is able to help it will mean the world to me. It will also be greatly appreciated. As this is the only help I am able to get. As all tutoring hours are while I am at work. Thank you for your time and help.
If you have an example of what I need to do that would be great to.
Make it a super night! I believe the second part my be the solutions, if you find they are how would I put it in a spreadsheet and do the IF function. THANK YOU
Cost of goods manufactured
Original Data
3 departments Cutting Assembly Finishing
Material used 30,000ft 0 10,000 gallons
material cost $2.00 per foot 0 $.20 per gallon
Labor incurred 300 hrs 600 hrs 1,200 hours
Labor cost $8.00/hour $10.00 per hour $5.00 per hour
Machine Hours 150m hrs 100 m hours 0
Overhead:
Base Machine Hrs Labor Cost Labor Hours
Budgeted 10,000 mach hrs $6,000 labor cost 1,000 labor cost
Budgeted Overhead $20,000 $12,000 $6,000
Profit Margin: 30% of cost
Required: Total cost by department
total cost by element
total cost by job
Test Data
3 departments Cutting Assembly Finishing
Material used 40,000ft 0 12,000 gallons
material cost $1.40 per foot 0 $.15 per gallon
Labor incurred 500 hrs 800 hrs 1,500 hours
Labor cost $9.00/hour $12.00 per hour $6.00 per hour
Machine Hours 2000m hrs 150 m hours 0
Overhead:
Base Machine Hrs Labor Cost Labor Hours
Budgeted 20,000 mach hrs $10,000 labor cost 2,000 labor cost
Budgeted Overhead $42,000 $30,000 $12,000
Profit Margin: 35% of cost
Required: Total cost by department
total cost by element
total cost by job
JOB COSTING
Cutting Assembly Finishing Total
Material $60,000 0 $2,000 $62,000
Direct Labor 2,400 $6,000 $6,000 14,400
Factory Overhead 300 12,000 7,200 19,500
Total cost $62,700 $18,000 $15,200 $95,900
Profit Margin (30%) 28,770
Selling Price $124,670
Cutting $20,000/10,000 = $2 per machine hour *150 machine hours =$300
Assembly $12,000/6,000 = 200% of labor cost*$6,000 =$12,000
Finishing $6,000/1,000 =$6 per labor hour * 1,200 labor hours = $7,200
TEST DATA
Cutting Assembly Finishing Total
Material $56,000 0 $1,800 $57,800
Direct Labor 4,500 $9,600 $9,000 23,100
Factory Overhead 420 28,800 9,000 38,220
Total cost $60,920 $38,400 $19,800 $119,120
Profit Margin (30%) 41,692
Selling Price $160,812
Cutting $42,000/10,000 = $2.10 per machine hour *200 machine hours =$420
Assembly $30,000/10,000 = 300% of labor cost*$9,600 =$28,800
Finishing $12,000/2,000 =$6 per labor hour * 1,500 labor hours = $9,000
Prepare an Excel using formulas in all calculations. Schedule/statement is to be in one worksheet and data on another worksheet. Use IF statement to display Original or the Test Data in schedule/statement.
I am so lost and in need of serous help. If anyone is able to help it will mean the world to me. It will also be greatly appreciated. As this is the only help I am able to get. As all tutoring hours are while I am at work. Thank you for your time and help.
If you have an example of what I need to do that would be great to.
Make it a super night! I believe the second part my be the solutions, if you find they are how would I put it in a spreadsheet and do the IF function. THANK YOU