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:

Chapter 2: Applying Excel Data B Markup on job cost 5 Machine-hours Direct labor-hours Total fixed manufacturing overhead cost Variable manufacturing overhead per machine-hour 0 Variable manufacturing overhead per direct labor-hour 75% Department Milling Assembly 60,000 3,000 8,000 80,000 $390,000 $500,000 $2.00 $3.75 1 2 3 Cost summary for Job 407 Department 4 Milling Assembly 5 Machine-hours 70 3 6 Direct labor-hours 8 40 7 Direct materials $520 $260 8 Direct labor cost $160 $680 9 0 Enter a formula into each of the cells marked with a ? below 1 Step 1: Calculate the estimated total manufacturing overhead cost for each department 2 Milling Assembly 3 Total fixed manufacturing overhead cost $390,000 $500,000 4 Variable manufacturing overhead per machine-hour or direct labor-hour $2.00 $3.75 5 Total machine-hours or direct labor-hours 60,000 80,000 Step 1: Download the Applying Excel form located on the left-hand side, under files. Step 2: Then enter formulas in all cells that contain question marks. For example, in cell B25 enter the formula "=B10". Step 3: Check your worksheet by changing the total fixed manufacturing overhead cost for the Milling Department in the Data area to $300,000, keeping all other data the same as in the original example. If your worksheet is operating properly, the total cost of Job 407 should now be $2,350. If you do not get this answer, find the errors in your worksheet and correct them. Step 4: Proceed to the requirements below only after completing your worksheet as it will be used to answer the following questions. 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 th other data the same as in the original example. Consider a new job, Job 408, with the following characteristics: A B 1 Chapter 2: Applying Excel 2 3 Cost summary for Job 408 4 Department Milling Assembly 70 3 8 40 5 6 Machine-hours Direct labor-hours Direct materials cost 7 $ 520 $ 260 8 Direct labor cost $ 160 $ 680 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.) Answer is complete but not entirely correct. Selling price for Job 408 $ 10,876.25 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.) Answer is complete but not entirely correct. Selling price for Job 408 $ 4,661.25 X 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.) Answer is complete but not entirely correct. Selling price for Job 408 $ 4,661.25 X 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 40,000 direct labor-hours? (Round your final answer to 2 decimal places.) X Answer is complete but not entirely correct. Selling price for Job 408 10,876.25