The info following the questions are from the original example.

Change the total fixed manufacturing overhead cost for the Milling Department in the 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:

	A	B	C
1	Chapter 2: Applying Excel
2
3	Cost summary for Job 408	Department
4		Milling	Assembly
5	Machine-hours	80	4
6	Direct labor-hours	9	20
7	Direct materials cost	$760	$330
8	Direct labor cost	$171	$400
9

In your worksheet, enter this new data in the cells for Job 407.

2. What is the new selling price for Job 408? (Round your final answer to 2 decimal places.) ___________

3. 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 4,000 machine-hours? (Round your final answer to 2 decimal places.) ___________

4. 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 40,000 direct labor-hours? (Round your final answer to 2 decimal places.)_______

Data
Markup on job cost	75%
	Department
	Milling	Assembly
Machine-hours	60,000	3,000
Direct labor-hours	8,000	80,000
Total fixed manufacturing overhead cost	$390,000	$500,000
Variable manufacturing overhead per machine-hour	$2.00
Variable manufacturing overhead per direct labor-hour		$3.75
Cost summary for Job 407	Department
	Milling	Assembly
Machine-hours	90	4
Direct labor-hours	5	20
Direct materials	$800	$370
Direct labor cost	$70	$280
Enter a formula into each of the cells marked with a ? below
Step 1: Calculate the estimated total manufacturing overhead cost for each department
	Milling	Assembly
Total fixed manufacturing overhead cost	$390,000	$500,000
Variable manufacturing overhead per machine-hour or direct labor-hour	$2.00	$3.75
Total machine-hours or direct labor-hours	60,000	80,000
Total variable manufacturing overhead	$120,000	$300,000
Total manufacturing overhead	$420,000	$800,000
Step 2: Calculate the predetermined overhead rate in each department
	Milling	Assembly
Total manufacturing overhead	$420,000	$800,000
Total machine-hours or direct labor-hours	60,000	80,000
Predetermined overhead rate per machine-hour or direct labor-hour	$7.00	$10.00
Step 3: Calculate the amount of overhead applied from both departments to Job 407
	Milling	Assembly
Predetermined overhead rate per machine-hour or direct labor-hour	$7.00	$10.00
Machine-hours or direct labor-hours for the job	90	20
Manufacturing overhead applied	$630.00	$200.00
Step 4: Calculate the total job cost for Job 407
	Milling	Assembly	Total
Direct materials	$800.00	$370.00	$1,170.00
Direct labor cost	$70.00	$280.00	$350.00
Manufacturing overhead applied	$630.00	$200.00	$830.00
Total cost of Job 407			$2,350.00
Step 5: Calculate the selling price for Job 407
Total cost of Job 407			2,350.00
Markup			75.00
Selling price of Job 407			4,112.50

EXHIBIT 2-5 Dickson Company: An Example Using Multiple Predetermined Overhead Rates Step 1: Calculate the estimated total manufacturing overhead cost for each department. Milling Department Overhead Cost (Y): Assembly Department Overhead Cost (Y): = $390,000 + ($2.00 per MH x 60,000 MHs) = $390,000 + $120,000 = $510,000 = $500,000 + ($3.75 per DLH x 80,000 DLHS) = $500,000 + $300,000 = $800,000 Step 2: Calculate the predetermined overhead rate in each department. Milling Department Overhead Rate: Assembly Department Overhead Rate: $510,000 60,000 machine-hours $8.50 per machine-hour $800,000 80,000 direct labor-hours $10.00 per direct labor-hour Step 3: Calculate the amount of overhead applied from both departments to Job 407. Milling Department: Overhead Applied to Job 407 Assembly Department: Overhead Applied to Job 407 = $8.50 per MH x 90 MHs = $765 = $10.00 per DLH x 20 DLHS = $200 Step 4: Calculate the total job cost for Job 407. Milling Assembly $370 $280 $200 Total $800 $ 70 Direct materials $1,170 Direct labor 350 Manufacturing overhead applied $765 965 Total cost of Job 407 $2,485 Step 5: Calculate the selling price for Job 407. $2,485.00 1,863.75 Total cost of Job 407 Markup ($2,485 x 75%) Selling price of Job 407 $4,348.75