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: Total Dob Sales Materials Direct Labor Cost Marcin $2,400 $5,400 $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 2 $4,100 $3,200 $2,800 Direct Gross Job 2 3 4 5 6 2 ? Applied Overhead 2 2 ? ? 2 ? ? ? ? 2 ? ? ? 2 2 ? 2 2 2 ? ? ? 2 ? ? ? 7 ? 7 ? ? 2 2 ? 2 2 ? ? > ? ? ? ? 11 12 2 ? Click here to download the Excel template which you will use to answer the questions that follow. Click here for a brief tutorial on PlvotTables In Excel. Click here for a brief tutorial on VLOOKUP in Excel Required: 5. Go to the tab titled "Job Profitability": a. 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 9 The applied overhead is A B 0 0 H Job 10 12 1 8 Dieet Average Direct Labor Direct Labor Hours Labor Cost 1700 201 340 3.25 3 205 65 2503 20 5 50 0759 20$ 15 225 203 45 3150 203 70 15011 20$ 30 250$ 20$ 50 375 $ 203 75 4.50 + 20 1 90 26 50 $ 200 530 1100 20 + 220 27.00$ 20 $ 540 100 20 3 20 22:00 $ 20 S 240 2255 20 5 45 30.000 20 600 20.50 $ 20$ 410 8.000 203 150 5.00 $ 20 100 5.00 $ 20 $ 300 31000 20$ 620 3.75 + 20 75 10.50 20 210 2.755 205 SS 10.00 $ 20 200 4.000 80 6.50 130 20 65 440 3 6 11 12 2 3 12 Employee Department Alison Varson Assembly Alison Veneto Assembly 4 Cathy Smithe Assemble 6 Cathy Smic Assomb 6 Cathy Smithco Assomit 7 Cathy Smithson Assemble & Christopher Tanel Arrombly $ Christopher Tanal Assembly 10 Christopher Tanel Assembly 11 Darien Chades Fabrication 12 Damien Chawles Fabric win 12 Darien Chades Fabricion 14 Damien Chates Fabion 15 David Chen Moldng 16 David Chen Moldng 7 David Chen Molding Emily Evans Assembly Emily Evans Assembly 20 Emby Evans Assembly 21 Emby Evans Assembly 22 Emes Kroyman Assembly 20 Ernest Kroman Assembly 24 Emestkoman Assembly 26 Jeff Dund Molding 26 Jeht Durand 27 Jeft Durand Molding 28 Jell Durand Molding 2. Julia Rhyner Assembly 30 i us mer Assemble 31 Quan Thompson Molding 32 dan Thompson Molding 33 Juvan Thompson Molding 34 KaBurke Assembly 35 Loretta How Assembly 06 Loretta Hoy Assembly 27 Loota Hoy Ascombe 182 Locotta Hoy Assembly 19 Madison Rodrique Fabection 40 Madison Rodriguo Fabec si 41 Marshall Tolver Molding 42 Marshal Tolver Molding 43 Mary Jo Havley Fabion 4.May Jo Harley Fabio 45. Mary Fabrication 46 Mary Jo Havley Fabrication 47 Michael Korshaw Fabrication 48. Michael Kershaw Fabrication 43 Bebecow As 50 Rot WILL Asrombo 61 Rebecoa WIL: Asset 52 Rebeo WIL Assom 53 Rebecca Wik Astel 54 Rebecca Nils Assent 55 Mamaks Ascent 56 Wiliam Maks Assert! 57 WiMuke Alb 53 59 Molding 500 65 270 370 420 80 90 65 is 120 225 8 31 2 20 11 12 4 Marley 21000 400 4.50 3.25$ 0.75 $ 6.00 1125 $ 8.00 11.00 6.00 $ 100 $ 4.00 325 12.753 26.000 3500 32.00 200 15503 1000 1250 20 20 20 20$ 20 $ 20 $ 20$ 20 205 205 20 20 20 20 20 20 20 220 120 20 80 65 255 520 70 640 140 310 20 25 20 1 200 20 i in Deitian B D E F G H Direct Applied Materials Direct Labor Overhead Total Job Cost Gross Margin Job Sales 1 $ 2,400.00 2 $ 5,400.00 3 $ 9,000.00 4 $ 1,450.00 5 $ 2,200.00 6 $ 7,000.00 7 $ 1,700.00 8 $ 1,600.00 9 $ 8,000.00 10 $ 4,100.00 11 $ 3,200.00 12 $ 2,800.00 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: Total Dob Sales Materials Direct Labor Cost Marcin $2,400 $5,400 $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 2 $4,100 $3,200 $2,800 Direct Gross Job 2 3 4 5 6 2 ? Applied Overhead 2 2 ? ? 2 ? ? ? ? 2 ? ? ? 2 2 ? 2 2 2 ? ? ? 2 ? ? ? 7 ? 7 ? ? 2 2 ? 2 2 ? ? > ? ? ? ? 11 12 2 ? Click here to download the Excel template which you will use to answer the questions that follow. Click here for a brief tutorial on PlvotTables In Excel. Click here for a brief tutorial on VLOOKUP in Excel Required: 5. Go to the tab titled "Job Profitability": a. 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 9 The applied overhead is A B 0 0 H Job 10 12 1 8 Dieet Average Direct Labor Direct Labor Hours Labor Cost 1700 201 340 3.25 3 205 65 2503 20 5 50 0759 20$ 15 225 203 45 3150 203 70 15011 20$ 30 250$ 20$ 50 375 $ 203 75 4.50 + 20 1 90 26 50 $ 200 530 1100 20 + 220 27.00$ 20 $ 540 100 20 3 20 22:00 $ 20 S 240 2255 20 5 45 30.000 20 600 20.50 $ 20$ 410 8.000 203 150 5.00 $ 20 100 5.00 $ 20 $ 300 31000 20$ 620 3.75 + 20 75 10.50 20 210 2.755 205 SS 10.00 $ 20 200 4.000 80 6.50 130 20 65 440 3 6 11 12 2 3 12 Employee Department Alison Varson Assembly Alison Veneto Assembly 4 Cathy Smithe Assemble 6 Cathy Smic Assomb 6 Cathy Smithco Assomit 7 Cathy Smithson Assemble & Christopher Tanel Arrombly $ Christopher Tanal Assembly 10 Christopher Tanel Assembly 11 Darien Chades Fabrication 12 Damien Chawles Fabric win 12 Darien Chades Fabricion 14 Damien Chates Fabion 15 David Chen Moldng 16 David Chen Moldng 7 David Chen Molding Emily Evans Assembly Emily Evans Assembly 20 Emby Evans Assembly 21 Emby Evans Assembly 22 Emes Kroyman Assembly 20 Ernest Kroman Assembly 24 Emestkoman Assembly 26 Jeff Dund Molding 26 Jeht Durand 27 Jeft Durand Molding 28 Jell Durand Molding 2. Julia Rhyner Assembly 30 i us mer Assemble 31 Quan Thompson Molding 32 dan Thompson Molding 33 Juvan Thompson Molding 34 KaBurke Assembly 35 Loretta How Assembly 06 Loretta Hoy Assembly 27 Loota Hoy Ascombe 182 Locotta Hoy Assembly 19 Madison Rodrique Fabection 40 Madison Rodriguo Fabec si 41 Marshall Tolver Molding 42 Marshal Tolver Molding 43 Mary Jo Havley Fabion 4.May Jo Harley Fabio 45. Mary Fabrication 46 Mary Jo Havley Fabrication 47 Michael Korshaw Fabrication 48. Michael Kershaw Fabrication 43 Bebecow As 50 Rot WILL Asrombo 61 Rebecoa WIL: Asset 52 Rebeo WIL Assom 53 Rebecca Wik Astel 54 Rebecca Nils Assent 55 Mamaks Ascent 56 Wiliam Maks Assert! 57 WiMuke Alb 53 59 Molding 500 65 270 370 420 80 90 65 is 120 225 8 31 2 20 11 12 4 Marley 21000 400 4.50 3.25$ 0.75 $ 6.00 1125 $ 8.00 11.00 6.00 $ 100 $ 4.00 325 12.753 26.000 3500 32.00 200 15503 1000 1250 20 20 20 20$ 20 $ 20 $ 20$ 20 205 205 20 20 20 20 20 20 20 220 120 20 80 65 255 520 70 640 140 310 20 25 20 1 200 20 i in Deitian B D E F G H Direct Applied Materials Direct Labor Overhead Total Job Cost Gross Margin Job Sales 1 $ 2,400.00 2 $ 5,400.00 3 $ 9,000.00 4 $ 1,450.00 5 $ 2,200.00 6 $ 7,000.00 7 $ 1,700.00 8 $ 1,600.00 9 $ 8,000.00 10 $ 4,100.00 11 $ 3,200.00 12 $ 2,800.00