AutoSave OFF Esu = ExcelAnalytics_CostConcepts_Template (1) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calibri (Body) "A Insert 11 = = ab Wrap Text Custom 48- 0 DX Delete Paste B I U Av * Merge & Center C%) Ideas Cell Styles Conditional Format Formatting as Table Sensitivity Sort & Filter A Format Find & Select G16 fx A B D E F G 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 9 Production supervision: Fabrication 10 Production supervision: Assembly 11 Indirect labor: Maintenance & Repairs 12 Indirect labor: Materials Handling 13 Indirect labor: Engineering 14 Indirect labor: Raw Materials Purchasing 15 Indirect labor: Quality Control 16 Indirect labor: Packaging & Shipping 17 Indirect materials: Molding 18 Indirect materials: Fabrication 19 Indirect materials: Assembly 20 Utilities: Molding 21 Utilities: Fabrication 22 Utilities: Assembly 23 tilties: Plant lighting and other pruinment Pivot Table Charts SumIF Sheet1 Cost Classifications: Manufacturing / Amount Product / Period Nonmanufacturing $ 400,000 Product MOH $ 300,000 Product MOH $ 50,000 Product MOH $ 15,000 Product MOH $ 10,000 Product MOH $ 60,000 Product MOH $ 65,000 Product MOH $ 58,000 Product MOH $ 72,000 Product MOH $ 68,000 Product MOH $ 170,000 Product MOH $ 93,000 Product MOH $ 38,000 Product MOH $ 99,000 Product MOH $ 18,000 Product MOH $ 13,000 Product MOH $ 8,000 Product MOH $ 24,000 Product MOH $ 20,000 Product MOH $ 6,000 Product MOH 12 000 Product MOH Cost Summary Income Statements + Fixed / Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Variable Variable Variable Variable Variable Fixed Sheet2 Ready + 173% AutoSave OFF Esu = ExcelAnalytics_CostConcepts_Template (1) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) X La 11 I Insert = = a Wrap Text Custom WE 48- O 5 DX Delete Paste I B Uv A 3 Merge & Center * LE % Ideas Cell Styles Conditional Format Formatting as Table Sensitivity Sort & Filter Find & Select A Format G16 x fx A B D E F G $ $ $ $ $ $ $ $ $ $ 15 Indirect labor: Quality Control 16 Indirect labor: Packaging & Shipping 17 Indirect materials: Molding 18 Indirect materials: Fabrication 19 Indirect materials: Assembly 20 Utilities: Molding 21 Utilities: Fabrication 22 Utilities: Assembly 23 Utiltiies: Plant lighting and other equipment 24 Utilities: Heating and cooling 25 Property insurance 26 Liability insurance 27 Medical insurance 28 Workers' compensation insurance 29 Other salaries: Plant manager 30 Other salaries: Accounting 31 Other salaries: Administrative support 32 Other salaries: Janitorial 33 Other salaries: Security and grounds maintenance 34 Property taxes 35 Payroll taxes: Direct labor 36 Payroll taxes: Other 37 Direct materials 38 Direct labor 39 Accounting manager's salary Pivot Table Charts SumIF Sheet1 Sheet2 $ $ $ $ $ 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 Income Statements Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product Product MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH MOH Direct materials Fixed Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Variable $ $ $ $ $ $ $ $ $ Product Period + Direct labor Administrative Variable Fixed Cost Summary Ready + 173% AutoSave OFF Esu = ExcelAnalytics_CostConcepts_Template (1) Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calibri (Body) Insert 11 = = . A a Wrap Text Custom WE 48- O 5 DX Delete Paste B I U AY 3 Merge & Center * LE % Ideas Cell Styles Conditional Format Formatting as Table Sensitivity Sort & Filter Find & Select A Format G16 fx A B D E F G $ $ MOH MOH MOH $ $ $ $ $ $ Product Product Product Product Product Product Product Product Product MOH MOH MOH MOH MOH Direct materials Fixed Fixed Fixed Fixed Fixed Fixed Variable Fixed Variable $ 29 Other salaries: Plant manager 30 Other salaries: Accounting 31 Other salaries: Administrative support 32 Other salaries: Janitorial 33 Other salaries: Security and grounds maintenance 34 Property taxes 35 Payroll taxes: Direct labor 36 Payroll taxes: Other 37 Direct materials 38 Direct labor 39 Accounting manager's salary 40 Administrative office insurance 41 Advertising 42 Depreciation on office equipment 43 Heat & electricity for the general office 44 Janitorial services for administrative offices 45 Salaries of accounts receivable/payable clerks 46 Sales commissions 47 Sales manager's salary 48 Supplies for administrative office 49 Customer billing supplies 50 51 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 Variable Fixed Fixed Fixed $ $ $ $ $ $ $ $ $ $ $ $ Fixed Product Period Period Period Period Period Period Period Period Period Period Period Direct labor Administrative Administrative Selling Administrative Administrative Administrative Administrative Selling Selling Administrative Administrative Fixed Fixed Fixed Variable Fixed Fixed Variable 52 Pivot Table Charts SumIF Sheet1 Sheet2 Cost Summary Income Statements + Ready + 173% 1 N 8,000,000 8,000,000 9 8,000,000 A B Crispin Company Traditional Format Income Statement 3 4 Sales $ 5 Cost of goods sold 6 Gross margin $ 7 Selling and administrative expenses: 8 Selling Administrative $ 10 Net operating income $ 11 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 27 28 Fill in 29 Auto-Calc 8,000,000 8,000,000 8,000,000 Excel Analytics 02-01 (Static) Part 3 3. Refer to the "Income Statements tab within your template. Using the SumlF function to quantify the cost of goods sold, the total selling expense, and the total administrative expense: a. Prepare a traditional format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, variable manufacturing overhead, and fixed manufacturing overhead costs.) b. Based on the traditional format income statement, what is the gross margin? C. Based on the traditional format income statement, what is the total selling and administrative expense? Complete this question by entering your answers in the tabs below. Req Req 3B Req 3C Prepare a traditional format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, variable manufacturing overhead, and fixed manufacturing overhead costs.) The cost of goods sold in this income statement is Excel Analytics 02-01 (Static) Part 3 3. Refer to the Income Statements tab within your template. Using the SumIF function to quantify the cost of goods sold, the total selling expense, and the total administrative expense: a. Prepare a traditional format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, variable manufacturing overhead, and fixed manufacturing overhead costs.) b. Based on the traditional format income statement, what is the gross margin? c. Based on the traditional format income statement, what is the total selling and administrative expense? Complete this question by entering your answers in the tabs below. Req Req 3B Req 30 Based on the traditional format income statement, what is the gross margin? The gross margin is Excel Analytics 02-01 (Static) Part 3 3. Refer to the "Income Statements" tab within your template. Using the SumIF function to quantify the cost of goods sold, the total selling expense, and the total administrative expense: a. Prepare a traditional format income statement. (Hint: The cost of goods sold should include all direct materials, direct labor, variable manufacturing overhead, and fixed manufacturing overhead costs.) b. Based on the traditional format income statement, what is the gross margin? c. Based on the traditional format income statement, what is the total selling and administrative expense? Complete this question by entering your answers in the tabs below. Req Req 3B Req 3C Based on the traditional format income statement, what is the total selling and administrative expense? The total selling and administrative expense is