Question

Chapter 2: Applying Excel: Exercise 1. Change the total fixed manufacturing overhead cost for the Milling Department in Data area back to $390,000, keeping all of the other data the same as in the original example. Consider a new job, Job 408, with the following characteristics: А B С Book 1 Chapter 2: Applying Excel 2 Files 3 Cost summary for Job 408 4 5 Department Muling Assembly 70 4 0 20 840 $ 300 171 380 Print Machine-hours Direct labor-hours Direct materials cost Direct labor cost 6 7 $ $ References In your worksheet, enter this new data in the cells for Job 407. What is the new selling price for Job 408? (Round your final answer to 2 decimal places.) Selling price for Job 408 2 Without changing the data for the job from requirement 2 above, what is the selling price for Job 408 if the total number of machine. hours in the Assembly Department increases from 3,000 machine-hours to 6.000 machine-hours? (Round your final answer to 2 decimal places.) Selling price for Job 408 3. Restore the total number of machine-hours in the Assembly Department to 3.000 machine-hours. And keep the job data the same as it was in Requirement 2. What is the selling price for Job 408 if the total number of direct labor hours in the Assembly Department decreases from 80,000 direct labor-hours to 50,000 direct labor-hours? (Round your final answer to 2 decimal places.) Selling price for Job 400 < Prev

Here are the instructions, my apologies

Answer 1

Answer-1:

45	B	C	D	E
46	Markup on job cost	75%
47
48	Step-1 Calculate the estimated total manufacturing overhead cost for each department
49		Milling	Assembly
50	Total fixed manufacturing overhead cost	$     390,000	$    500,000
51	Variable manufacturing overhead per machine hour or DLH	$           2.00	$           3.75
52	Total machine hours or DLH	60,000	80,000
53	Total variable manufacturing overhead	$     120,000	$    300,000
54	Total manufacturing overhead	$     510,000	$    800,000
55
56	Step-2 Calculate the predetermined overhead rate in each department
57		Milling	Assembly
58	Total manufacturing overhead	$     510,000	$    800,000
59	Total machine hours or DLH	60,000	80,000
60	Predetermined overhead rate per machine hour or DLH	$           8.50	$        10.00
61
62	Step-3 Calculate the amount of overhead applied from both department to Job 408
63		Milling	Assembly
64	Predetermined overhead rate per machine hour or DLH	$           8.50	$        10.00
65	Machine hours or DLH for the job	70	20
66	Manufacturing overhead applied	$            595	$            200
67
68	Step-4 Calculate the total job cost for Job 408
69		Milling	Assembly	Total
70	Direct material	$            840	$            300	$ 1,140.00
71	Direct labor	$            171	$            380	$    551.00
72	Manufacturing overhead applied	$            595	$            200	$    795.00
73	Total cost of Job 408			$ 2,486.00
74	Markup @75%			$ 1,864.50
75	Selling price for Job 408			$ 4,350.50

Formula:

D E Assembly 500000 3.75 80000 =D51*D52 =D50+D53 A B с 46 Markup on job cost 0.75 47 48 Step-1 Calculate the estimated total manufacturing overhead cost for each department 49 Milling 50 Total fixed manufacturing overhead cost 390000 51 Variable manufacturing overhead per machine hour or DLH 2 52 Total machine hours or DLH 60000 53 Total variable manufacturing overhead =C51*C52 54 Total manufacturing overhead =C50+C53 55 56 Step-2 Calculate the predetermined overhead rate in each department 57 Milling 58 Total manufacturing overhead =C54 59 Total machine hours or DLH =C52 60 Predetermined overhead rate per machine hour or DLH =C58/C59 61 62 Step-3 Calculate the amount of overhead applied from both department to Job 408 63 Milling 64 Predetermined overhead rate per machine hour or DLH =C60 65 Machine hours or DLH for the job 70 66 Manufacturing overhead applied =C64*C65 67 68 Step-4 Calculate the total job cost for Job 408 69 Milling 70 Direct material 840 71 Direct labor 171 72 Manufacturing overhead applied =C66 73 Total cost of Job 408 74 Markup @75% 75 Selling price for Job 408 Assembly =D54 =D52 =D58/D59 Assembly =D60 20 =D64*D65 Assembly Total 300 =C70+D70 380 =C71+D71 =D66 =C72+D72 =SUM(E70:E72) =E73*C46 =SUM(E73:E74)

Answer-2:

If the total number of machine hours in the Assembly Department increases from 3,000 machine hours to 6,000 machine hours, the selling price for Job 408 = $4,350.50

Answer-3:

If the total number of direct labor hours in the Assembly Department decreases from 80,000 direct labor hours to 50,000 direct labor hours, the selling price for Job 408 = $4,481.75