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: Job Direct Sales Materials Direct Labor Applied Overhead Total Job Cost Gross Margin 1 $2,400 ? ? 7 ? ? 2 $5,400 2 2 2 2 2 3 $9,000 ? ? ? 2 ? 4 $1,450 2 ? 2 2 2 5 $2,200 2 2 2 ? 2 6 $7,000 2 ? ? 2 7 $1,700 ? ? ? 2 2 8 $1,600 ? ? 2 2 9 $8,000 2 2 2 2 ? 10 $4,100 ? ? 2 2 ? 11 $3,200 2 ? 2 12 $2,800 2 ? ? 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 PivotTables in Excel. Click here for a brief tutorial on VLOOKUP in Excel, Required: Go to the tab titled "Estimated MOH.": Direct Materials Requisitions Direct Labor Summary Average Direct Labor Direct Labor Direct Labor Employee Department Job Hours Wage Rate Cost Allison Vanston Assembly 10 17.00 $ 20 $ 340 Allison Vanston Assembly 12 3.25 $ 20 $ 65 Cathy Smithson Assembly 1 2.50 $ 20 $ 50 Cathy Smithson Assembly 4 0.75 $ 20 $ 15 Cathy Smithson Assembly 7 2.25 $ 20 $ 45 Cathy Smithson Assembly B 3.50 5 20 $ 70 Christopher Tanaka Assembly 1 1.50 5 20 $ 30 Christopher Tanaka Assembly 7 2.50 $ 20 $ 50 Christopher Tanaka Assembly B 3.75 $ 20 $ 75 Damien Charles Fabrication 1 4.50 $ 20 $ 90 Damien Charles Fabrication 3 26.50 $ 20 $ 530 Damien Charles Fabrication 6 11.00 $ 20 5 220 Damien Charles Fabrication 9 27.00 $ 20 5 540 David Chen Molding 4 1.00 $ 20 5 20 David Chen Molding 6 12.00 $ 20 $ 240 David Chen Molding 7 2.25 $ 20 $ 45 Emily Evans Assembly 3 30.00 $ 20 $ 600 Emily Evans Assembly 6 20.50 $ 20 $ 410 Emily Evans Assembly 11 8.00 $ 20 $ 160 Emily Evans Assembly 12 5.00 $ 20 $ 100 Ernest Kroyman Assembly 2 15.00 $ 20 $ 300 Ernest Kroyman Assembly 3 31.00 $ 20 $ 620 Ernest Kroyman Assembly 12 3.75 $ 20 $ 75 Jeff Durand Molding 2 10.50 $ 20 $ 210 Jeff Durand Molding 5 2.75 $ 20 $ 55 Jeff Durand Molding Jeff Durand Molding Julia Rhymer Assembly Julia Rhymer Assembly Juwan Thompson Molding Juwan Thompson Molding Juwan Thompson Molding Kayla Burke Assembly Loretta Hoyt Assembly Loretta Hoyt Assembly Loretta Hoyt Assembly 22511325269 10 10.00 $ 20 $ 200 4.00 $ 20 $ 80 6.50 $ 20 $ 130 1.00 $ 20 $ 20 3.25 $ 20 $ 65 22.00 $ 20 $ 440 25.00 $ 20 $ 500 3.25 $ 20 $ 65 13.50 $ 20 $ 270 18.50 $ 20 $ 370 21.00 $ 20 $ 420 Loretta Hoyt Assembly 11 4.00 $ 20 $ 80 Madison Rodriquez Fabrication 5 4.50 $ 20 $ 90 Madison Rodriquez Fabrication 7 3.25 $ 20 $ 65 Marshall Toliver Molding 8 0.75 $ 20 $ 15 Marshall Toliver Molding 11 6.00 $ 20 $ 120 Mary Jo Hawley Fabrication 2 11.25 $ 20 $ 225 Mary Jo Hawley Fabrication 10 8.00 $ 20 S 160 Mary Jo Hawley Fabrication 11 11.00 $ 201 S 220 Mary Jo Hawley Fabrication 12 6.00 $ 20 S 120 Michael Kershaw Fabrication 4 1.00 $ 20 $ 20 Michael Kershaw Fabrication 8 4.00 $ 20 $ 80 Rebecca Wills Assembly 1 3.25 $ 20 $ 65 Rebecca Wills Assembly 2 12.75 $ 20 $ 255 Rebecca Wills Assembly 6 26.00 $ 20 $ 520 Rebecca Wills Assembly 8 3.50 $ 20 $ 70 Rebecca Wills Assembly 9 32.00 $ 20 $ 640 Rebecca Wills Assembly 10 7.00 20 $ 140 William Marks Assembly 3 15.50 $ 20 $ 310 William Marks Assembly 4 1.00 $ 20 S 20 William Marks Assembly 7 1.25 $ 20 $ 25 Estima Conaborate Job Profitability Waterfall Chart Direct Materials Requisitions Direct Labor Summary Estimated MOH Last Year This Year Estimated Account Actual Amount Cost Behavior Amount Cost Behavior Equipment depreciation: Molding Equipment depreciation: Fabrication $ 400,000 Fixed $ 400,000 Fixed $ 300,000 Fixed S 300,000 Fixed Equipment depreciation: Assembly $ 50,000 Fixxed $ 50,000 Fixed Equipment depreciation: Materials Handling $ 15,000 Fixed $ 15,000 Fixed Equipment depreciation: Other $ 10,000 Fixed $ 10,000 Fixed Production suprvision: Molding $ 60,000 Fixed 63,000 Fixed Production supervision: Fabrication $ 65,000 Fixed $ 68,250 Fixed Production supervision: Assembly $ 58,000 Foxed $ 60,900 Fixed Indirect labor: Maintenance & Repairs $ 72,000 Fixed $ 75,600 Fixed Indirect labor: Materials Handling S 68,000 Fixed S 71,400 Fixed Indirect labor: Engineering $ 170,000 Fixed $ 178,500 Fixed Indirect labor: Raw Materials Purchasing S 93,000 Fixed S 97,650 Fixed Indirect labor: Quality Control $ 38,000 Fixed $ 39,900 Fixed Indirect labor: Packaging & Shipping $ 99,000 Fixed $ 103,950 Fixed Indirect materials: Molding $ 18,000 Variable Variable Indirect materials: Fabrication $ 13,000 Variable Variable Indirect materials: Assembly $ 8,000 Variable Variable Utilities: Molding $ 24,000 Variable Variable Utilities: Fabrication $ 20,000 Variable Variable Utilities: Assembly $ 6,000 Variable Variable Utiltlies: Plant lighting and other equipment $ 12,000 Fixed $ 12,240 Fixed Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 Fixed Property insurance $ 26,000 Fixed $ 26,520 Fixed Liability insurance S 18,000 Fixed $ 18,360 Fixed Medical insurance $ 45,000 Fixed $ 51,750 Fixed Workers' compensation insurance $ 13,000 Fixed $ 14,300 Fixed Other salaries: Plant manager $ 165,000 Fixed $ 173,250 Fixed Other salaries: Accounting $ 89,000 Fixed $ 93,450 Fixed Other salaries: Administrative support $ 56,000 Fixed $ 58,800 Fixed Other salaries: Janitorial $ 48,000 Fixed $ 50,400 Fixed Other salaries: Security and grounds maintenance $ 44,000 Fixed S 46,200 Fixed Property taxes $ 18,000 Fixed $ 18,540 Fixed Payroll taxes: Direct labor $ 79,000 Variable Variable Payroll taxes: Other $ 165,000 Fixed S 173.250 Fixed Total Manufacturing Overhead $ 2.389.000 $2.295.690 Total direct labor hours Plantwide predetermined overhead rate 41,400 42,500 E Job 00 Zoom Direct Materials Requisitions Add Category Direct Labor Summary Insert Table Chart Estimated MOH Text Shape Media Comment Job Profitability C Waterfall Chart Sales Direct Materials Applied Direct Labor Overhead Total Job Cost Gross Margin 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 Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on VLOOKUP in Excel. Required: 1. Go to the tab titled "Direct Materials Requisitions.": a. Using PivotTable, calculate the total direct materials cost for each of the 12 jobs. b. What is the total direct materials cost for Jobs 5 and 11? Job 5 Job 11 The total direct materials cost is Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on VLOOKUP in Excel. 2. Go to the tab titled "Direct Labor Summary.": a. Using PivotTable, calculate the total direct labor cost and total direct labor-hours for each of the 12 jobs. b. What is the total direct labor cost for Jobs 3 and 10? c. What are the total direct labor-hours for Jobs 3 and 10? Complete this question by entering your answers in the tabs below. Req 2B Req 2C What are the total direct labor-hours for Jobs 3 and 10? The total direct labor hours are Job 3 Job 10 2. Go to the tab titled "Direct Labor Summary.": a. Using PivotTable, calculate the total direct labor cost and total direct labor-hours for each of the 12 jobs. b. What is the total direct labor cost for Jobs 3 and 10? c. What are the total direct labor-hours for Jobs 3 and 10? Complete this question by entering your answers in the tabs below. Req 2B Req 2C What is the total direct labor cost for Jobs 3 and 10? The total direct labor cost is Job 3 Job 10 Req 201 Req 20 > $9,000 $1,450 5 $2,200 7 $7,000 7 7 $1,700 8 $1,600 2 ? 9 $8,000 ? 2 10, $4,100 2 11 12 $3,200 2 $2,800 Click here to download the Excel template, which you will use to answer the questions that follow. Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on VLOOKUP in Excel. Required: 3. Go to the tab titled "Estimated MOH.": a. For each of the seven variable manufacturing overhead expenses (highlighted in yellow), create a formula that calculates the estimated amount of the expense for this year. (Hint: Use last year's actual amount of each expense and the actual total labor hours [cell B39] as the basis for this year's estimates.) 3b. and 3c. What is the estimated indirect materials cost in the Molding Department (cell E17) and the estimated utilities cost in the Fabrication Department (cell E21)? 3-b. The estimated indirect materials cost in Molding is 3-c. The estimated utilities cost in Fabrication is Required: 4. Within the "Estimated MOH" tab. a. Create a formula in cell E40 that calculates a plantwide predetermined overhead rate this year. b. What is the plantwide predetermined overhead rate for this year? Answer is complete but not entirely correct. The plantwide predetermined overhead rate is $ 54.02 Required: 5. Go to the tab titled "Job Profitability.": a. Using formulas that refer to your answers from requirements 1-4, 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 Required: 6. Within the "Job Profitability": a. create formulas that compute the total job cost and gross margin for each of the 12 jobs. b. What is the total cost of Jobs 3 and 12? c. What is the gross margin Jobs 1 and 8? Complete this question by entering your answers in the tabs below. Req 6B Req 6C What is the total cost of Jobs 3 and 12? Job 3 Job 12 The total cost is Reg GB Req 6C > Required information Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on VLOOKUP in Excel. Required: 6. Within the "Job Profitability": a. create formulas that compute the total job cost and gross margin for each of the 12 jobs. b. What is the total cost of Jobs 3 and 12? c. What is the gross margin Jobs 1 and 8? Complete this question by entering your answers in the tabs below. Req 6B Req 6C What is the gross margin Jobs 1 and 8? The gross margin is Job 1 Job B < Req 68 Reg BC