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 K18 Open recovered workbooks? Your recent changes were saved. Do you want to continue Xv fx 1 2 3 4 Sales 5 Cost of goods sold Font A Crispin Company Traditional Format Income Statement Administrative 10 Net operating income 11 12 13 6 Gross margin 7 Selling and administrative expenses: 8 Selling 9 Alignment Number 14 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 25 Net operating income 26 27 28 29 30 31 32 33 34 35 Fixed administrative PivotTable B Crispin Company Contribution Format Income Statement Charts Fill In Auto-Calc $ SumIF $ $ $ $ $ $ Cell Styles $ $ C 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 8,000,000 Cost Summary D E Income Paste 1 A24 Open recovered workbooks? Your recent changes were saved. Do you want to continue X fx Utilities: Heating and cooling A 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 0 Production supervision: Assembly Font 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 6 Indirect labor: Packaging & Shipping 7 Indirect materials: Molding 8 Indirect materials: Fabrication 9 Indirect materials: Assembly 20 Utilities: Molding 1 Utilities: Fabrication 2 Utilities: Assembly 23 Utiltiies: Plant lighting and other equipment 24 Utilities: Heating and cooling 25 Property insurance 6 Liability insurance 27 Medical insurance 5 Payroll taxes: Direct labor 6 Payroll taxes: Other 7 Direct materials 8 Workers' compensation insurance 9 Other salaries: Plant manager 0 Other salaries: Accounting 1 Other salaries: Administrative support 2 Other salaries: Janitorial 3 Other salaries: Security and grounds maintenance 4 Property taxes 8 Direct labor 39 Accounting manager's salary 10 Administrative office insurance -1 Advertising -2 Depreciation on office equipment 13 Heat & electricity for the general office 14 Janitorial services for administrative offices 15 Salaries of accounts receivable/payable clerks 6 Sales commissions 7 Sales manager's salary -8 Supplies for administrative office 9 Customer billing supplies 50 1 2 53 Alignment Number PivotTable $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Charts B Amount 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 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 C SumIF Cell Styles Product Period Period Period Period Period Period Period Period Period Period Period D Cost Classifications: Manufacturing / Nonmanufacturing Fixed/Variable MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH Fixed MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH Direct materials Direct labor Administrative Administrative Selling Administrative Administrative Administrative Administrative Selling Selling Administrative Administrative E Cost Summary 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 Income