Required information (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. Click here for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on PivotCharts in Excel. Click here for a brief tutorial on SUMIF in Excel. 2. Using PivotTable and Charts: a. Calculate the company's total variable costs and total fixed costs. b. Calculate the portions of the total variable costs that would be classified as product costs and period costs. c. Calculate the portions of the total fixed costs that would be classified as product costs and period costs. Paste Font Alignment Number Cell Styles v Open recovered workbooks? Your recent changes were saved. Do you want to continue A24 X V fx Utilities: Heating and cooling A D E 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 Production supervision: Fabrication LO Production supervision: Assembly 1 Indirect labor: Maintenance & Repairs 2 Indirect labor: Materials Handling 3 Indirect labor: Engineering 4 Indirect labor: Raw Materials Purchasing 5 Indirect labor: Quality Control L6 Indirect labor: Packaging & Shipping -7 Indirect materials: Molding 8 Indirect materials: Fabrication -9 Indirect materials: Assembly CO Utilities: Molding 1 Utilities: Fabrication -2 Utilities: Assembly 3 Utiltiies: Plant lighting and other equipment -4 Utilities: Heating and cooling 5 Property insurance 6 Liability insurance 7 Medical insurance 8 Workers' compensation insurance 9 Other salaries: Plant manager o Other salaries: Accounting 1 Other salaries: Administrative support 2 Other salaries: Janitorial 3 Other salaries: Security and grounds maintenance -4 Property taxes 5 Payroll taxes: Direct labor 6 Payroll taxes: Other 7 Direct materials 8 Direct labor 9 Accounting manager's salary 0 Administrative office insurance 1 Advertising 2 Depreciation on office equipment 3 Heat & electricity for the general office 4 Janitorial services for administrative offices 5 Salaries of accounts receivable/payable clerks -6 Sales commissions -7 Sales manager's salary 8 Supplies for administrative office -9 Customer billing supplies 0 1 2 3 $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ 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 Cost Classifications: Manufacturing / Product / Period Nonmanufacturing Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product Direct materials Product Direct labor Period Administrative Period Administrative Period Selling Period Administrative Period Administrative Period Administrative Period Administrative Period Period Selling Period Administrative Period Administrative Fixed / Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Fixed Variable $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Selling 1,000 $ 2,000 Pivot Table Charts SumIF Cost Summary Income Paste Font Alignment Number Cell Styles v Open recovered workbooks? Your recent changes were saved. Do you want to continue K18 fx A B D E 8,000,000 1 Crispin Company 2 Traditional Format Income Statement 3 4 Sales $ 5 Cost of goods sold 6 Gross margin $ 7 Selling and administrative expenses: 8 Selling 9 Administrative $ 10 Net operating income $ 8,000,000 8,000,000 11 8,000,000 12 13 Crispin Company 14 Contribution Format Income Statement 15 Sales $ 16 Variable expenses: 17 Cost of goods sold 18 Variable selling 19 Variable administrative $ 20 Contribution margin $ 21 Fixed expenses: 22 Fixed manufacturnig overhead 23 Fixed selling 24 Fixed administrative $ 25 Net operating income $ 26 8,000,000 8.000.000 27 28 Fill In Auto-Calc 29 30 31 32 33 34 35 Pivot Table Charts SumIF Cost Summary P Income Complete this question by entering your answers in the tabs below. Req 2A Req 2B Reg 20 Calculate the portions of the total variable costs that would be classified as product costs and period costs. The total variable product costs are The total variable period costs are $ 482,000 d. Prepare a bar chart that includes a total of two bars. The first bar will depict the total fixed costs and the amounts of that grand total that are categorized as product costs and period costs. The second bar will depict the total variable costs and the amounts of that grand total that are categorized as product costs and period costs. Which of the following statements are true according to this bar chart. (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) The fixed period costs are greater than the variable product costs. The fixed period costs are greater than the variable period costs. The variable product costs are greater than the fixed product costs