Crispin Companys sales from last year were $8 million. The company maintains no beginning or ending inventories. It has assembled all of its costs from last year and would like your assistance in sorting these costs into various categories, depicting them graphically, and preparing traditional and contribution format income statements.
PLEASE SOLVE ALL 3 PROBLEMS THAT WOULD BE SUPER HELPFUL :))
HERE IS THE EXCEL
Required information Excel Analytics 02-01 (Static) Cost Concepts [LO2-2, LO2-3, LO2-4) [The following information applies to the questions displayed below.] Crispin Company's sales from last year were $8 million. The company maintains no beginning or ending inventories. It has assembled all of its costs from last year and would like your assistance in sorting these costs into various categories, depicting them graphically, and preparing traditional and contribution format income statements. Click here to download the Excel template, which you will use to answer the questions that follow. Excel Analytics 02-01 (Static) Part 4 4. Assume the company wants to adapt the information in the Cost Summary" tab to prepare a contribution format income statement. Within the "Income Statements tab of your template, and using the Sum IFS function to quantify all variable and fixed expenses: a. Prepare a contribution format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, and variable manufacturing overhead. The fixed manufacturing overhead costs should be included within the "Fixed expenses portion of the income statement.) b. Based on the contribution format income statement, what is the contribution margin? c. Based on the contribution format income statement, what is the net operating income? Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 40 Prepare a contribution format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, and variable manufacturing overhead. The fixed manufacturing overhead costs should be included within the "Fixed expenses" portion of the income statement.) The cost of goods sold in this income statement is e cost of goods sold in this income statement is Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 4C Based on the contribution format income statement, what is the contribution margin? The contribution margin is Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 4C Based on the contribution format income statement, what is the net operating income? The net operating income is Account Equipment depreciation: Molding Equipment depreciation: Fabrication Equipment depreciation: Assembly Equipment depreciation: Materials Handling Equipment depreciation: Other Production suprvision: Molding Production supervision: Fabrication Production supervision: Assembly Indirect labor: Maintenance & Repairs Indirect labor: Materials Handling Indirect labor: Engineering Indirect labor: Raw Materials Purchasing Indirect labor: Quality Control Indirect labor: Packaging & Shipping Indirect materials: Molding Indirect materials: Fabrication Indirect materials: Assembly Utilities: Molding Utilities: Fabrication Utilities: Assembly Utilties: Plant lighting and other equipment Utilities: Heating and cooling Property insurance Liability insurance Medical insurance Workers' compensation insurance Other salaries: Plant manager Other salaries: Accounting Other salaries: Administrative support Other salaries: Janitorial Other salaries: Security and grounds maintenance Property taxes Payroll taxes: Direct labor Payroll taxes: Other Direct materials Direct labor Accounting manager's salary Administrative office insurance Advertising Depreciation on office equipment Heat & electricity for the general office Janitorial services for administrative offices Salaries of accounts receivable/payable clerks Sales commissions Sales manager's salary Supplies for administrative office Customer billing supplies Amount 400,000 300,000 50,000 15,000 10,000 60,000 65,000 58,000 72,000 68,000 170,000 93,000 38,000 99,000 18,000 13,000 8,000 24,000 20,000 6,000 12,000 24,000 26,000 18,000 45,000 13,000 165,000 89,000 56,000 48,000 44,000 18,000 79,000 165,000 2,220,000 1,365,000 88,000 2,000 400,000 15,000 4,000 26,000 78,000 480,000 120,000 1,000 2,000 Product/ Period Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Period Period Period Period Period Period Period Period Period Period Period COST Classifications: Manufacturing/ Nonmanufacturing Fixed / Variable Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Variable MOH Variable MOH Variable MOH Variable MOH Variable MOH Variable MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Variable MOH Fixed Direct materials Variable Direct labor Variable Administrative Fixed Administrative Fixed Selling Fixed Administrative Fixed Administrative Fixed Administrative Fixed Administrative Fixed Selling Variable Selling Fixed Administrative Fixed Administrative Variable MOH Crispin Company Traditional Format Income Statement $ 8,000,000 $ 8,000,000 Sales Cost of goods sold Gross margin Selling and administrative expenses: Selling Administrative Net operating income $ 8,000,000 8,000,000 Crispin Company Contribution Format Income Statement Sales $ Variable expenses: Cost of goods sold Variable selling Variable administrative Contribution margin $ Fixed expenses: Fixed manufacturnig overhead Fixed selling Fixed administrative Net operating income $ 8,000,000 8.000.000 Fill In Auto-Calc Required information Excel Analytics 02-01 (Static) Cost Concepts [LO2-2, LO2-3, LO2-4) [The following information applies to the questions displayed below.] Crispin Company's sales from last year were $8 million. The company maintains no beginning or ending inventories. It has assembled all of its costs from last year and would like your assistance in sorting these costs into various categories, depicting them graphically, and preparing traditional and contribution format income statements. Click here to download the Excel template, which you will use to answer the questions that follow. Excel Analytics 02-01 (Static) Part 4 4. Assume the company wants to adapt the information in the Cost Summary" tab to prepare a contribution format income statement. Within the "Income Statements tab of your template, and using the Sum IFS function to quantify all variable and fixed expenses: a. Prepare a contribution format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, and variable manufacturing overhead. The fixed manufacturing overhead costs should be included within the "Fixed expenses portion of the income statement.) b. Based on the contribution format income statement, what is the contribution margin? c. Based on the contribution format income statement, what is the net operating income? Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 40 Prepare a contribution format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, and variable manufacturing overhead. The fixed manufacturing overhead costs should be included within the "Fixed expenses" portion of the income statement.) The cost of goods sold in this income statement is e cost of goods sold in this income statement is Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 4C Based on the contribution format income statement, what is the contribution margin? The contribution margin is Complete this question by entering your answers in the tabs below. Req 4A Req 4B Req 4C Based on the contribution format income statement, what is the net operating income? The net operating income is Account Equipment depreciation: Molding Equipment depreciation: Fabrication Equipment depreciation: Assembly Equipment depreciation: Materials Handling Equipment depreciation: Other Production suprvision: Molding Production supervision: Fabrication Production supervision: Assembly Indirect labor: Maintenance & Repairs Indirect labor: Materials Handling Indirect labor: Engineering Indirect labor: Raw Materials Purchasing Indirect labor: Quality Control Indirect labor: Packaging & Shipping Indirect materials: Molding Indirect materials: Fabrication Indirect materials: Assembly Utilities: Molding Utilities: Fabrication Utilities: Assembly Utilties: Plant lighting and other equipment Utilities: Heating and cooling Property insurance Liability insurance Medical insurance Workers' compensation insurance Other salaries: Plant manager Other salaries: Accounting Other salaries: Administrative support Other salaries: Janitorial Other salaries: Security and grounds maintenance Property taxes Payroll taxes: Direct labor Payroll taxes: Other Direct materials Direct labor Accounting manager's salary Administrative office insurance Advertising Depreciation on office equipment Heat & electricity for the general office Janitorial services for administrative offices Salaries of accounts receivable/payable clerks Sales commissions Sales manager's salary Supplies for administrative office Customer billing supplies Amount 400,000 300,000 50,000 15,000 10,000 60,000 65,000 58,000 72,000 68,000 170,000 93,000 38,000 99,000 18,000 13,000 8,000 24,000 20,000 6,000 12,000 24,000 26,000 18,000 45,000 13,000 165,000 89,000 56,000 48,000 44,000 18,000 79,000 165,000 2,220,000 1,365,000 88,000 2,000 400,000 15,000 4,000 26,000 78,000 480,000 120,000 1,000 2,000 Product/ Period Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Period Period Period Period Period Period Period Period Period Period Period COST Classifications: Manufacturing/ Nonmanufacturing Fixed / Variable Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Variable MOH Variable MOH Variable MOH Variable MOH Variable MOH Variable MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Variable MOH Fixed Direct materials Variable Direct labor Variable Administrative Fixed Administrative Fixed Selling Fixed Administrative Fixed Administrative Fixed Administrative Fixed Administrative Fixed Selling Variable Selling Fixed Administrative Fixed Administrative Variable MOH Crispin Company Traditional Format Income Statement $ 8,000,000 $ 8,000,000 Sales Cost of goods sold Gross margin Selling and administrative expenses: Selling Administrative Net operating income $ 8,000,000 8,000,000 Crispin Company Contribution Format Income Statement Sales $ Variable expenses: Cost of goods sold Variable selling Variable administrative Contribution margin $ Fixed expenses: Fixed manufacturnig overhead Fixed selling Fixed administrative Net operating income $ 8,000,000 8.000.000 Fill In Auto-Calc
