\begin{tabular}{|c|c|c|c|c|c|} \hline 4. & & B & c & D & E \\ \hline 1 & Chapter 2: Applying Excel & & +2 & +2 & \\ \hline 2 & + & & +2 & & \\ \hline 3 & Data & & & +2 & \\ \hline 4 & Markup on job cost & 75% & +2 & & \\ \hline 5 & & & & +2 & \\ \hline 6 & & Depart & ment & +2 & \\ \hline 7 & & Milling & Assembly & +2 & \\ \hline 8 & Machine-hours & 60,000 & 3,000 & +2 & \\ \hline 9 & Direct labor-hours & 8,000 & 80,000 & +2 & \\ \hline 10 & Total fixed manufacturing overhead cost & $390,000 & $500,000 & +2 & \\ \hline 11 & Variable manufacturing overhead per machine-hour & $2.00 & & +2 & \\ \hline 12 & Variable manufacturing overhead per direct labor-hour & & $3.75 & +2 & \\ \hline 13 & & & & +2 & \\ \hline 14 & Cost summary for Job 407 & Depart & ment & +2 & \\ \hline 15 & & Milling & Assembly & & \\ \hline 16 & Machine-hours & 90 & 4 & & \\ \hline 17 & Direct labor-hours & & 20 & & \\ \hline 18 & Direct materials & $800 & $370 & & \\ \hline 19 & Direct labor cost & $70 & $280 & & \\ \hline 20 & & & & & \\ \hline 21 & Enter a formula into each of the cells marked with a ? below & & & & \\ \hline 22 & & & & & \\ \hline 23 & Step 1: Calculate the estimated total manufacturing overhead cos & r each depa & rtment & & \\ \hline 24 & & Milling & Assembly & & \\ \hline 25 & Total fixed manufacturing overhead cost & ? & ? & & \\ \hline 26 & Variable manufacturing overhead per machine-hour or direct labor-hour & ? & ? & & \\ \hline 27 & Total machine-hours or direct labor-hours & ? & ? & & \\ \hline 28 & Total variable manufacturing overhead & ? & ? & & \\ \hline 29 & Total manufacturing overhead & ? & ? & & \\ \hline \end{tabular} Download the Applying Excel form and enter formulas in all cells that contain question marks. The Chapter 2 Form worksheet is to be used to create your own worksheet version of the example in the text. Enter formulas in the cells that contain question marks. For example, in cell B25 enter the formula " B10 After entering formulas in all of the cells that contained question marks, verify that the amounts match the example in the text. You should proceed to the requirements below only after completing your worksheet. Required: 1. Check your worksheet by changing the total foxed 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. Sove your completed Applying Excel form to your computer ond then uplood it here by clicking "Browse." Next, click "Sove." You will use this worksheet to onswer the questions in Port 2. EXHIBIT 2 - 5 Dickson Company: An Example Using Multiple Predetermined Overhead Rates Step 1: Calculate the estimated total manufacturing overhead cost for each department MillingDepartmentOverheadCost(Y)=$390,000+($2.00perMH60,000MHs)=$390,000+$120,000=$510,000 Assembly Department Overhead Cost (Y)=$500,000+($3.75 per DLH 80,000 DLHs ) =$500,000+$300,000=$800,000 Step 2: Calculate the predetermined overhead rate in each department. Milling Department Overhead Rate =$510,000/60,000 machine-hours =$8.50 per machine-hour Assembly Department Overhead Rate =$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 =$8.50perMH90MHs=$765 Assembly Department: Overhead Applied to Job 407 Step 4: Calculate the total job cost for Job 407. Step 5: Calculate the selling price for Job 407 . \begin{tabular}{|c|c|c|c|c|c|} \hline 31 & Step 2: Calculate the predetermined overhead rate in each departr & ment & & & \\ \hline 32 & & Milling & Assembly & & \\ \hline 33 & Total manufacturing overhead & & ? & & \\ \hline 34 & Total machine-hours or direct labor-hours & & ? & & \\ \hline 35 & Predetermined overhead rate per machine-hour or direct labor-hour & ? & & & \\ \hline 36 & & & & & \\ \hline 37 & Step 3: Calculate the amount of overhead applied from both depar & rtments to Job & 407 & & \\ \hline 38 & +2 & Milling & Assembly & & \\ \hline 39 & Predetermined overhead rate per machine-hour or direct labor-hour & & & & \\ \hline 40 & Machine-hours or direct labor-hours for the job & & & & \\ \hline 41 & Manufacturing overhead applied & ? & & & \\ \hline 42 & & & & & \\ \hline 43 & Step 4: Calculate the total job cost for Job 407 & & & & \\ \hline 44 & & Milling & Assembly & Total & \\ \hline 45 & Direct materials & ? & & ? & \\ \hline 46 & Direct labor cost & ? & & ? & \\ \hline 47 & Manufacturing overhead applied & ? & ? & ? & \\ \hline 48 & Total cost of Job 407 & x2=2 & & ? & \\ \hline 49 & & & & & \\ \hline 50 & Step 5: Calculate the selling price for Job 407 & & & & \\ \hline 51 & Total cost of Job 407 & & & ? & \\ \hline 52 & Markup & & & & \\ \hline 53 & MarkupSellingpriceofJob407 & & & & \\ \hline 54 & Selling price or Joo 40r & +2 & & & \\ \hline \end{tabular}