Direct 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 Job Sales Materials Labor Overhead Margin 1 $2,480 ? 2 $5,400 ? $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 2 ? 7 ? 2 7 2 7 7 ? ? 2 7 7 ? 2 2 3 4 5 6 7 B 9 10 11 12 Cost ? 2 ? ? 2 2 ? 7 ? 2 7 7 ? ? 7 7 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 PlyotTables 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)? 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 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-0 The estimated indirect materials cost in Molding is The estimated utilities cost in Fabrication is Upen recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? KIS 1 x fc A C D E F 1 Last Year This Year Estimated 2 Account Actual Amount Cost Behavior Amount Cost Behavior 3 Equipment depreciation: Molding S 400,000 Fixed $ 400,000 Fixed 4 Equipment depreciation: Fabrication $ 300,000 Fixed $ 300,000 Fixed 5 Equipment depreciation: Assembly $ 50,000 Fixed $ 50,000 Fixed 6 Equipment depreciation: Materials Handling $ 15,000 Fixed $ 15,000 Fixed 7 Equipment depreciation: Other $ 10,000 Fixed $ 10,000 Fixed 8 Production suprvision: Molding $ 60,000 Fixed $ 63,000 Fixed 9 Production supervision: Fabrication $ 65,000 Fixed $ 68,250 Fixed 10 Production supervision: Assembly $ 58,000 Fixed $ 11 Indirect labor: Maintenance & Repairs 60,900 Fixed $ 72,000 Fixed S 75,600 Fixed 12 Indirect labor Material Handling $ 68,000 Fixed $ 71,400 Fixed 13 Indirect labor. Engineering $ 170,000 Fixed $ 178,500 Fixed 14 Indirect labor: Raw Materials Purchasing $ 93,000 Fixed S 97,650 Fixed 15 Indirect labor: Quality Control $ 38,000 Fixed $ 39,900 Fixed 16 Indirect labor: Packaging & Shipping $ 99,000 Fred S 103,950 Fixed 17 Indirect materials: Molding $ 18,000 Variable Variable 18 Indirect materials: Fabrication $ 13,000 Variable Variable 19 Indirect materials: Assembly $ 8,000 Variable Variable 20 Utilities: Molding $ 24,000 Variable Variable 21 Utilities: Fabrication $ 20,000 Variable Variable 22 Utilities: Assembly $ 6,000 Variable Variable 23 Utilties: Plant lighting and other equipment $ 12.000 Fixed 12,240 Fixed 24 Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 Fixed 25 Property insurance $ 26,000 Fixed $ 26,520 Fixed 26 Liability Insurance $ 18,000 Fored $ 18,360 Fixed 27 Medical insurance 5 45,000 Fored $ 51,750 Fixed 28 Workers' compensation insurance $ 13,000 Forced $ 14,300 Foved Pivottable VL | Direct Materials Requisitions Sheet Direct Labor Summary Estimated MOH Job Profitability Water F Fixed Fixed Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? K15 * fx A B D E 14 Indirect labor: Raw Materials Purchasing 93,000 Fixed $ 97,650 15 Indirect labor: Quality Control $ 38,000 Fixed $ 39,900 16 Indirect labor: Packaging & Shipping $ 99,000 Fixed $ 103,950 17 Indirect materials: Molding $ 18,000 Variable 18 Indirect materials: Fabrication $ 13,000 Variable 19 Indirect materials: Assembly $ 8,000 Variable 20 Utilities: Molding $ 24,000 Variable 21 Utilities: Fabrication $ 20,000 Variable 22 Utilities: Assembly $ 6,000 Variable 23 Utilties: Plant lighting and other equipment $ 12,000 Fixed $ 12,240 24 Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 25 Property insurance $ 26,000 Fixed $ 26,520 26 Liability insurance S 18,000 Fixed $ 18,360 27 Medical insurance $ 45,000 Fixed $ 51,750 28 Workers' compensation insurance $ 13,000 Fixed $ 14,300 29 Other salaries: Plant manager $ 165,000 Fixed $ 30 Other salaries: Accounting 173,250 S 89,000 Fixed $ 31 Other salaries: Administrative support 93,450 $ 56,000 Fixed $ 58,800 32 Other salaries: Janitorial $ 48,000 Fixed $ 33 Other salaries: Security and grounds maintenance 50,400 $ 44,000 Fixed $ 46,200 34 Property taxes $ 18,000 Fixed $ 35 Payroll taxes: Direct labor 18,540 S 79,000 36 Payroll taxes: Other Variable $ 165,000 Fixed $ 173,250 37 Total Manufacturing Overhead $ 2,389,000 $ 2,295,690 38 39 Total direct labor hours 41,400 40 Plantwide predetermined overhead rate 42,500 41 42 Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fored Fixed Fixed Variable Fixed Pivot Table VO | Direct Materials Requisitions Sheet1 Direct Labor Summary Ready Estimated MOH Direct 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 Job Sales Materials Labor Overhead Margin 1 $2,480 ? 2 $5,400 ? $9,000 $1,450 $2,200 $7,000 $1,700 $1,600 $8,000 $4,100 $3,200 $2,800 2 ? 7 ? 2 7 2 7 7 ? ? 2 7 7 ? 2 2 3 4 5 6 7 B 9 10 11 12 Cost ? 2 ? ? 2 2 ? 7 ? 2 7 7 ? ? 7 7 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 PlyotTables 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)? 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 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-0 The estimated indirect materials cost in Molding is The estimated utilities cost in Fabrication is Upen recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? KIS 1 x fc A C D E F 1 Last Year This Year Estimated 2 Account Actual Amount Cost Behavior Amount Cost Behavior 3 Equipment depreciation: Molding S 400,000 Fixed $ 400,000 Fixed 4 Equipment depreciation: Fabrication $ 300,000 Fixed $ 300,000 Fixed 5 Equipment depreciation: Assembly $ 50,000 Fixed $ 50,000 Fixed 6 Equipment depreciation: Materials Handling $ 15,000 Fixed $ 15,000 Fixed 7 Equipment depreciation: Other $ 10,000 Fixed $ 10,000 Fixed 8 Production suprvision: Molding $ 60,000 Fixed $ 63,000 Fixed 9 Production supervision: Fabrication $ 65,000 Fixed $ 68,250 Fixed 10 Production supervision: Assembly $ 58,000 Fixed $ 11 Indirect labor: Maintenance & Repairs 60,900 Fixed $ 72,000 Fixed S 75,600 Fixed 12 Indirect labor Material Handling $ 68,000 Fixed $ 71,400 Fixed 13 Indirect labor. Engineering $ 170,000 Fixed $ 178,500 Fixed 14 Indirect labor: Raw Materials Purchasing $ 93,000 Fixed S 97,650 Fixed 15 Indirect labor: Quality Control $ 38,000 Fixed $ 39,900 Fixed 16 Indirect labor: Packaging & Shipping $ 99,000 Fred S 103,950 Fixed 17 Indirect materials: Molding $ 18,000 Variable Variable 18 Indirect materials: Fabrication $ 13,000 Variable Variable 19 Indirect materials: Assembly $ 8,000 Variable Variable 20 Utilities: Molding $ 24,000 Variable Variable 21 Utilities: Fabrication $ 20,000 Variable Variable 22 Utilities: Assembly $ 6,000 Variable Variable 23 Utilties: Plant lighting and other equipment $ 12.000 Fixed 12,240 Fixed 24 Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 Fixed 25 Property insurance $ 26,000 Fixed $ 26,520 Fixed 26 Liability Insurance $ 18,000 Fored $ 18,360 Fixed 27 Medical insurance 5 45,000 Fored $ 51,750 Fixed 28 Workers' compensation insurance $ 13,000 Forced $ 14,300 Foved Pivottable VL | Direct Materials Requisitions Sheet Direct Labor Summary Estimated MOH Job Profitability Water F Fixed Fixed Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? K15 * fx A B D E 14 Indirect labor: Raw Materials Purchasing 93,000 Fixed $ 97,650 15 Indirect labor: Quality Control $ 38,000 Fixed $ 39,900 16 Indirect labor: Packaging & Shipping $ 99,000 Fixed $ 103,950 17 Indirect materials: Molding $ 18,000 Variable 18 Indirect materials: Fabrication $ 13,000 Variable 19 Indirect materials: Assembly $ 8,000 Variable 20 Utilities: Molding $ 24,000 Variable 21 Utilities: Fabrication $ 20,000 Variable 22 Utilities: Assembly $ 6,000 Variable 23 Utilties: Plant lighting and other equipment $ 12,000 Fixed $ 12,240 24 Utilities: Heating and cooling $ 24,000 Fixed $ 24,480 25 Property insurance $ 26,000 Fixed $ 26,520 26 Liability insurance S 18,000 Fixed $ 18,360 27 Medical insurance $ 45,000 Fixed $ 51,750 28 Workers' compensation insurance $ 13,000 Fixed $ 14,300 29 Other salaries: Plant manager $ 165,000 Fixed $ 30 Other salaries: Accounting 173,250 S 89,000 Fixed $ 31 Other salaries: Administrative support 93,450 $ 56,000 Fixed $ 58,800 32 Other salaries: Janitorial $ 48,000 Fixed $ 33 Other salaries: Security and grounds maintenance 50,400 $ 44,000 Fixed $ 46,200 34 Property taxes $ 18,000 Fixed $ 35 Payroll taxes: Direct labor 18,540 S 79,000 36 Payroll taxes: Other Variable $ 165,000 Fixed $ 173,250 37 Total Manufacturing Overhead $ 2,389,000 $ 2,295,690 38 39 Total direct labor hours 41,400 40 Plantwide predetermined overhead rate 42,500 41 42 Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fored Fixed Fixed Variable Fixed Pivot Table VO | Direct Materials Requisitions Sheet1 Direct Labor Summary Ready Estimated MOH