Required: 5. Go to the tab titled "Job Profitability": o. Using formulas that refer to your answers from requirements 14. calculate the direct materials, direct labor, and applied overhead cost for each of the 12 jobs. (Hint: Use VLOOKUP to reference values from the pivot tables you created in requirements 1 and 2.) b. What is the amount of applied overhead for Jobs 4 and 9? Job 4 Job D The applied overhead is Required information The following information applies to the questions displayed below) Ozuna Company uses a job order costing system with a plantwide predetermined overhead rate based on direct labor hours. For job costing purposes. It uses an average direct labor wage rate of $20 per hour. The company has been struggling financially, accordingly, it has asked you to conduct a job profitability study beginning with a thorough critique of its existing cost system. To keep the scope of your project manageable, you have chosen a subset of 12 jobs from the many jobs completed by the company during the year. Your goal is to complete the table shown below and comment on the insights that it provides: Direct Applied Total Job Gross Sales Materials Direct Labor Overhead Cost Marcin $2,400 $5,400 $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 ? $8,00 $4,100 $3,200 $2,Bee 2 Job 1 2 3 2 ? ? ? ? > ? ? ? 2 2 ? ? ? 2 2 ? ? ? ? ? ? 2 ? 2 ? ? ? ? ? ? ? ? ? ? 2 2 ? 2 ? 2 2 11 12 B C G H Vage Job 10 12 1 4 7 8 1 7 8 1 3 6 9 4 6 7 3 6 11 2 3 12 2 E F Direct Direct Direct Labor- Labor Labor Hours Cost 17.00 $ 20$ 340 3.25 $ 20+ 65 250 205 50 0.751 20 $ 15 2.25 20 5 45 0 1 20 70 15011 20 $ 30 2.50 $ 50 3751 20 $ 25 4581 20 $ 90 26.50 $ 20+ 530 11.00 $ 20 $ 220 27.00 1 20 540 100 $ 20 t 20 12.00 20 $ 240 2.25 + 20 $ 45 30.000 20 1 600 20.50 S 20 $ 410 8.00 $ 20 160 5.00 20 130 15006 20 % 300 0100 + 20$ 520 3.75 $ 20 $ 75 10.50 20$ 200 2751 20 $ 55 10:00 1 20 + 200 400 20$ 80 6.50 $ 20 130 100 + 20 $ 20 325 20 65 22.000 200 440 25001 20 $ 500 3.25 20$ 65 1750 20 $ 270 1650 $ 20 $ 370 2100 5 20 $ 420 400 20$ 80 480 20 90 20 + 65 0.75 200 15 100 20 + 120 125 20 225 BOU 20 100 TO) 20 $ 220 6,000 20 $ 20 L000 201 20 4.003 201 80 1253 20 3.75 + 203 255 26,00 520 501 20 70 32.00 $ 201 7.00 $ 200 140 15.50 $ 20 $ 010 100 + 20$ 20 125 20 25 1 Employee Departmen 2 Allison Vanston Assembly 3 Allison Vanston Assembly 4 Cathy Smithson Assembly 5 Cathy Smitheon Assembly 6 Cathy Stron Assembly 7 Cathy Smithson Assembly 8 Christopher Tunak. Assembly 9 Christopher Tanaki Assembly 10 Christopher Tanaka Assembly 11 Damien Charles Fabrication 12 Darren Charles Fabrication Damien Charles Fabrication 14 Damien Charles Fabrication 15 David Chen Molding 16 David Chen Molding 17 David Chen Molding 18 Em Evans Assembly 19 Em Evi Assembly 20 Emily Evans Assembly 21 Emily Evans Assembly 22 Ernest Kroyman Assembly 23 Ernest Kroyman Assembly 24 Emest Koyman Assembly 25 e Durand Molding 26 Jeff Durand Molding 27 Jer Durand Molong 28 Jeft Durand Molding 29 Julia Rhymer Assembly 30 Julia Rhymes Assembly 31 Juvan Thompson Molding 32. Juuan Thoriton Molding 39 Win Thompson Molding 36 Ral Burke Assembly 35 Loretta Hoyt Assembly 36 Loretta Hoy! Assembly 07 Loretta Hot Assembly 38 Loretta Hoyt Assembly 39 Madison Poliquez Fattoon 40 Madison Rodriguez Fabion 41 Marshal Toliver Molding 42. Marshall Toliver Molting 43 Mary Jo Haules Fabucation 4+ Mary Jo Haley Fabion 45 Mary Jo Hawley Fabnahon 48 Mary Jo Hawley Fabrication 47 Michaekershav Fabrication 48 Michael Korshav Fabrication 49 Pebecca Vill Assembly 50 Pebeoca Vill Assembly 51 Rebeoca Vills Assembly 52 Rebecca Vills Assembly 53 Rebecca Wills Arently 54 Rebecca Wils Assembly 55 Via Maes Assembly 56 bin Mans Assembly 57 Viliam Mark Assembly 58 59 50 1 62 59 14 65 56 10 12 5 11 3 3 2 10 12 4 8 1 2 205 3 1 $40 4
