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 brieftutorial on PivotTables in Excel. Click here fora 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 2 2. Using PivotTabIe and Charts: 3. Calculate the company's total variable costs and total xed 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. Calculate the company's total variable costs and total fixed costs. The total variable costs are The total fixed costs areCalculate 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 Calculate the portions of the total fixed costs that would be classified as product costs and period costs. The total fixed product costs are The total fixed period costs ared. Prepare a bar chart that includes a total of two bars. The first bar will depict the total fixed costs and the amounts ofthat 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.) D The fixed period costs are greater than the variable product costs. D The fixed period costs are greater than the variable period costs. D The variable product costs are greater than the fixed product costs