Required information Chapter 2: Applying Excel 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 the other data the same as in the original example. Consider a new job, Job 408, with the following characteristics: B A 1 Chapter 2: Applying Excel 2 3 Cost summary for Job 408 4 Machine-hours 5 Direct labor-hours 6 Direct materials cost 7 Direct labor cost 8 Department Milling Assembly 90 6 20 $ 780 $ 300 $ 120 $ 380 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 $ 2,545.00 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 4,000 machine-hours? (Round your final answer to 2 decimal places.) Selling price for Job $ 408 2,545.00 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.) Selling price for Job 408 $ 4,453.75 A B C D E F 1 Chapter 2: Applying Excel 2 Data Markup on job cost 75% 5 6 Department 7 Milling Assembly 8 Machine-hours 60,000 3,000 9 Direct labor-hours 8.000 80,000 10 Total fixed manufacturing overhead cost $390,000 $500,000 11 Variable manufacturing overhead per machine-hour $2.00 12 Variable manufacturing overhead per direct labor-hour $3.75 13 14 Cost summary for Job 407 Department 15 Milling Assembly 16 Machine-hours 90 4 17 Direct labor-hours 5 20 18 Direct materials $800 $370 19 Direct labor cost $70 $280 20 21 Enter a formula into each of the cells marked with a ? below 22 23 Step 1: Calculate the estimated total manufacturing overhead cost for each department 24 Milling Assembly 25 Total fixed manufacturing overhead cost ? ? 26 Variable manufacturing overhead per machine-hour or direct labor-hour 2 ? 27 Total machine-hours or direct labor-hours ? ? 28 Total variable manufacturing overhead ? ? 29 Total manufacturing overhead ? 30 31 Step 2: Calculate the predetermined overhead rate in each department 32 Milling Assembly 33 Total manufacturing overhead ? ? 34 Total machine-hours or direct labor-hours ? ? 35 Predetermined overhead rate per machine-hour or direct labor-hour ? ? 36 37 Step 3: Calculate the amount of overhead applied from both departments to Job 407 38 Milling Assembly 39 Predetermined overhead rate per machine-hour or direct labor-hour ? ? 40 Machine-hours or direct labor-hours for the job ? ? 41 Manufacturing overhead applied ? ? 42 43 Step 4: Calculate the total job cost for Job 407 44 Milling Assembly 45 Direct materials ? 46 Direct labor cost ? ? 47 Manufacturing overhead applied ? ? 48 Total cost of Job 407 49 50 Step 5: Calculate the selling price for Job 407 51 Total cost of Job 407 2 Total ? ? ? ? 52 Markup ? ? ? 53 Selling price of Job 407 54
