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 Direct Applied Total Job Gross Job Sales Materials Overhead Cost Margin $2,400 ? $5,400 $9,000 $1,450 2 $2,200 ? $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 1 00 OWN Labor ? 2 ? ? ? 7 ? 2 ? 7 ? 2 ? ? ? ? 7 7 ? ? ? 7 2 ? 2 ? 7 ? ? ? ? 7 ? 7 2 ? 2 ? 7 2 ? 7 ? 2 2 ? 9 10 11 12 7 7 7 ? ? Click here to download the Excel template, which you will use to answer the questions that follow, Click here for a brief tutorial on Pivot Tables 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)? . B D F G Last Year 1 2 Account 3 Equipment depreciation: Molding 4 Equipment depreciation: Fabrication 5 Equipment depreciation: Assembly 6 Equipment depreciation: Materials Handling 7 Equipment depreciation: Other 8 Production suprvision: Molding 9 Production supervision Fabrication 10 Production supervision Assembly 11 Indirect labor Maintenance & Repairs 12 Indirect labor Material Handling 13 Indirect labor Engineering 14 Indirect labor Raw Materials Purchasing 15 Indirect labor Quality Control 16 Indirect labor Packaging & Shipping 17 Indirect materials: Molding 18 Indirect materiale: Fabrication 19 Indirect materials: Assembly 20 Utilities: Molding 21 Utilities: Fabrication 22 Utilities: Assembly 23 Utilties: Plantlichting and other equipment 24 Utilities: Heating and cooling 25 Property insurance 26 Lability insurance 27 Medical insurance 28 Workers' compensation Insurance 29 Other salaries: Plant manager 30 Other salaries: Accounting 31 Other salaries: Administrative support 32 other salaries: Janitorial 33 Other salaries: Security and grounds maintenance 34 Property taxes 35 Payroll taxes: Direct labor 36 Payroll taxes: Other 37 Total Manufacturing Overhead 38 39 Total direct labor hours 40 Plantwide predetermined overhead rate Actual Amount Cost Behavior $ 400,000 Fixed $ 300,000 Fixed $ 50,000 Fixed $ 15,000 Fixed $ 10,000 Fixed $ 60,000 Fixed $ 65,000 Fived 5 58,000 Fixed s 72.000 Fooed 68,000 Fored 3 170,000 Fixed 93,000 Fored $ 38,000 Fixed $ 99,000 Fixed $ 18,000 Variable $ 13,000 Variable $ 8,000 Variable $ 24,000 Variable $ 20,000 Variable $ 6,000 Variable $ 12,000 Fixed $ 24,000 Fixed $ 26,000 Fixed $ 18,000 Fixed $ 45,000 Fixed $ 13,000 Fixed $ 165,000 Fixed $ 89,000 Fored 56,000 Fixed $ 48,000 Fixed $ 44,000 Fixed 18,000 Fbed $ 79,000 Variable $ 165,000 Fixed $ 2,389,000 This Year Estimated Amount Cost Behavior $ 400,000 Fixed $ 300,000 Fixed $ 50,000 Fixed $ 15,000 Fixed $ 10,000 Fixed $ 63,000 Fixed $ 68,250 Fixed $ 60,900 Fixed S 75,600 Fixed 5 71,400 Fixed $ 178,500 Fixed 97,650 Fixed 39.900 Fixed 104.950 Fixed Variable Variable Variable Variable Variable Variable 240 Fixed 24480 Fixed $ 26,520 Fixed $ 18,360 Fixed $ 51,750 Fixed $ 14,300 Fixed 173,250 Fixed S 93.450 Fixed $ 58,800 Fixed $ 50,400 Fixed $ 46,200 Fixed 18,540 Fixed Variable $ 173,250 Fixed $ 2,295,690 u u 41400 42,500 41 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 Direct Applied Total Job Gross Job Sales Materials Overhead Cost Margin $2,400 ? $5,400 $9,000 $1,450 2 $2,200 ? $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 1 00 OWN Labor ? 2 ? ? ? 7 ? 2 ? 7 ? 2 ? ? ? ? 7 7 ? ? ? 7 2 ? 2 ? 7 ? ? ? ? 7 ? 7 2 ? 2 ? 7 2 ? 7 ? 2 2 ? 9 10 11 12 7 7 7 ? ? Click here to download the Excel template, which you will use to answer the questions that follow, Click here for a brief tutorial on Pivot Tables 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)? . B D F G Last Year 1 2 Account 3 Equipment depreciation: Molding 4 Equipment depreciation: Fabrication 5 Equipment depreciation: Assembly 6 Equipment depreciation: Materials Handling 7 Equipment depreciation: Other 8 Production suprvision: Molding 9 Production supervision Fabrication 10 Production supervision Assembly 11 Indirect labor Maintenance & Repairs 12 Indirect labor Material Handling 13 Indirect labor Engineering 14 Indirect labor Raw Materials Purchasing 15 Indirect labor Quality Control 16 Indirect labor Packaging & Shipping 17 Indirect materials: Molding 18 Indirect materiale: Fabrication 19 Indirect materials: Assembly 20 Utilities: Molding 21 Utilities: Fabrication 22 Utilities: Assembly 23 Utilties: Plantlichting and other equipment 24 Utilities: Heating and cooling 25 Property insurance 26 Lability insurance 27 Medical insurance 28 Workers' compensation Insurance 29 Other salaries: Plant manager 30 Other salaries: Accounting 31 Other salaries: Administrative support 32 other salaries: Janitorial 33 Other salaries: Security and grounds maintenance 34 Property taxes 35 Payroll taxes: Direct labor 36 Payroll taxes: Other 37 Total Manufacturing Overhead 38 39 Total direct labor hours 40 Plantwide predetermined overhead rate Actual Amount Cost Behavior $ 400,000 Fixed $ 300,000 Fixed $ 50,000 Fixed $ 15,000 Fixed $ 10,000 Fixed $ 60,000 Fixed $ 65,000 Fived 5 58,000 Fixed s 72.000 Fooed 68,000 Fored 3 170,000 Fixed 93,000 Fored $ 38,000 Fixed $ 99,000 Fixed $ 18,000 Variable $ 13,000 Variable $ 8,000 Variable $ 24,000 Variable $ 20,000 Variable $ 6,000 Variable $ 12,000 Fixed $ 24,000 Fixed $ 26,000 Fixed $ 18,000 Fixed $ 45,000 Fixed $ 13,000 Fixed $ 165,000 Fixed $ 89,000 Fored 56,000 Fixed $ 48,000 Fixed $ 44,000 Fixed 18,000 Fbed $ 79,000 Variable $ 165,000 Fixed $ 2,389,000 This Year Estimated Amount Cost Behavior $ 400,000 Fixed $ 300,000 Fixed $ 50,000 Fixed $ 15,000 Fixed $ 10,000 Fixed $ 63,000 Fixed $ 68,250 Fixed $ 60,900 Fixed S 75,600 Fixed 5 71,400 Fixed $ 178,500 Fixed 97,650 Fixed 39.900 Fixed 104.950 Fixed Variable Variable Variable Variable Variable Variable 240 Fixed 24480 Fixed $ 26,520 Fixed $ 18,360 Fixed $ 51,750 Fixed $ 14,300 Fixed 173,250 Fixed S 93.450 Fixed $ 58,800 Fixed $ 50,400 Fixed $ 46,200 Fixed 18,540 Fixed Variable $ 173,250 Fixed $ 2,295,690 u u 41400 42,500 41