Pivot Tables: Pivot tables allow us to summarize data sets quickly and easily agnostic of size. The table allows us to summarize on a specific attributes and using specific measures such as sum, average, maximum and so on. When utilizing Pivot Tables you should proceed as follows: F'P'PP'P!' 9?\" 10. 11. Go to the \"Insert\" tab Click on "PivotTable" and the "Create PivotTable\" Wizard will pop up: Under "Choose the data that you want to analyze" make sure the radio button for "Select a table or range\" is selected Ensure the TablefRange is set as the entire range of cells you want to summarize Under "Choose where you want the PivotTable report to be placed\" choose the radio button for "New Worksheet\" Leave the box unchecked next to "Add this data to the Data Model\" a. Click OK Within the \"PivotTable Fields\" on the right of the screen, do the following actions: Click on and drag any attribute (typically non-numerical] you want to summarize by such as an account name or a location to the "Rows\" quadrant Click on and drag the numerical value such as sales or quantity to the "Values" quadrant a. Note that this will default to \"Sum\" of whatever value you chose. If you want to have a measure other than sum, you should click on the drop down of that value in the "Values\" quadrant and click "Value field settings\" to modify the summarization type. Your table will now show you a summarization of the numerical value in correspondence with the non-numerical attribute you chose such as sales by state, If you want to add additional dimensions such as the Year you can drag those values to the "Columns\" quadrant and now you will have a cross sectional summarization such as sales by year by state. B D E Cost Classifications: Manufacturing / Account Amount Product / Period Nonmanufacturing Fixed / Variable Equipment depreciation: Molding 400,000 Product MOH Fixed Equipment depreciation: Fabrication 300,000 Product MOH Fixed Equipment depreciation: Assembly 50,000 Product MOH Fixed Equipment depreciation: Materials Handling 15,000 Product MOH Fixed Equipment depreciation: Other 10,000 Product MOH ixed Production suprvision: Molding 60,000 Produ MOH Fixed Production supervision: Fabrication 65,000 Product MOH Fixed 10 Production supervision: Assembly 58,000 Product MOH Fixed Indirect labor: Maintenance & Repairs 72,000 MOH Fixed 11 Product 12 Indirect labor: Materials Handling 68,000 Product MOH Fixed Indirect labor: Engineering 170,000 Product MOH Fixed 13 14 Indirect labor: Raw Materials Purchasing 93,000 Product MOH Fixed 15 Indirect labor: Quality Control 38,000 Product MOH Fixed 16 Indirect labor: Packaging & Shipping 99,000 Product MOH Fixed Variable 17 Indirect materials: Molding 18,000 Product MOH Indirect materials: Fabrication Variable 18 13,000 Product MOH 19 Indirect materials: Assembly 8,000 Product MOH Variable 20 Utilities: Molding 24,000 Product MOH Variable 21 Utilities: Fabrication 20,000 Product MOH Variable Utilities: Assembly 6,000 Product MOH Variable 22 23 Utilties: Plant lighting and other equipment 12,000 Product MOH Fixed 24 Utilities: Heating and cooling 24,000 Product MOH Fixed 25 Property insurance 26,000 Product MOH Fixed 26 Liability insurance 18,000 Product MOH Fixed Fixed 27 Medical insurance 45,000 Product MOH 28 Workers' compensation insurance 13,000 Product MOH Fixed Other salaries: Plant manage 165,000 Product MOH Fixed 30 Other salaries: Accounting 89,000 Product MOH Fixed 31 Other salaries: Administrative support 56,000 Product MOH Fixed 32 Other salaries: Janitorial 48,000 Product MOH ixed 33 Other salaries: Security and grounds maintenance 44,000 Product MOH Fixed Fixed 34 Property taxes 18,000 Product MOH 35 Payroll taxes: Direct labor 79,000 Product MOH Variable 36 Payroll taxes: Other 165,000 Produ MOH fixed 37 Direct materials 2,220,000 Product Direct materials Variable 38 Direct labor 1,365,000 Product Direct labor Variable 39 Accounting manager's salary 88,000 Period Administrative Fixed 40 Administrative office insurance 2,000 Period Administrative Fixed 41 Advertising 400,000 Period Selling Fixed 42 Depreciation on office equipment 15,000 Period Administrative Fixed 43 Heat & electricity for the general office 4,000 Period Administrative Fixed 44 Janitorial services for administrative offices 26,000 Period Administrative Fixed 45 Salaries of accounts receivable/payable clerks 78,000 Period Administrative Fixed Sales commissions 480,000 Period Selling Variable 47 Sales manager's salary 120,000 Period Selling Fixed 48 Supplies for administrative office 1,000 Period Administrative ixed 49 Customer billing supplies 2,000 Period Administrative VariableA B C 1 Crispin Company 2 Traditional Format Income Statement 3 4 Sales $ 8,000,000 5 Cost of goods sold 6 Gross margin $ 8,000,000 7 Selling and administrative expenses: 8 Selling 9 Administrative 10 Net operating income 8,000,000 11 12 13 Crispin Company 14 Contribution Format Income Statement 15 Sales $ 8,000,000 16 Variable expenses: 17 Cost of goods sold 18 Variable selling 19 Variable administrative 20 Contribution margin 8,000,000 21 Fixed expenses: 22 Fixed manufacturingad 23 Fixed selling 24 Fixed administrative 25 Net operating income 8.000.000 26 27 28 Fill In 29 Auto-CalcRequired information Excel Analytics 02-01 (Static) Cost Concepts [L01, L02, L03, L05] [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 for a brief tutorial on PivotTables in Excel. Click here for a brief tutorial on PivotCharts in Excel. Click here for a brieftutorial on SUMIF in Excel. Note: depending on browser settings, it may be necessary to rightclick on the link and click "Save link as..." to download. Excel Analytics 02-01 (Static) Part 3 3. 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? 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 I I Based on the contribution format income statement, what is the contribution margin? Based on the contribution format income statement, what is the net operating income