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. (Using Excel)

1.Go to the "Cost Summary" tab. Using PivotTable and Charts:

a.Calculate the company's total product costs and total period costs.

b.Calculate the portions of the total product costs that would be classified as direct materials, direct labor, and manufacturing overhead (MOH).

c.Calculate the portions of the total period costs that would be classified as selling expense and administrative expense.

Show transcribed image text

BIU H > J34 Open recovered workbooks? Your recent changes were saved. Do you want to continu fx Paste A B D E F 1 Cost Classifications: 2 Account Amount Product/Period Manufacturing/ Nonmanufacturing Fixed / Variable 3 Equipment depreciation: Molding $ 400,000 Product MOH Fixed 4 Equipment depreciation: Fabrication $ 300,000 Product MOH Fixed 5 Equipment depreciation: Assembly $ 50,000 Product MOH Fixed 6 Equipment depreciation: Materials Handling $ 15,000 Product MOH Fixed 7 Equipment depreciation: Other $ 10,000 Product MOH Fixed 8 Production suprvision: Molding $ 60,000 Product MOH Fixed 9 Production supervision: Fabrication $ 65,000 Product MOH Fixed 10 Production supervision: Assembly $ 58,000 Product MOH Fixed 11 Indirect labor: Maintenance & Repairs $ 72,000 Product MOH Fixed 12 Indirect labor: Materials Handling $ 68,000 Product MOH Fixed 13 Indirect labor: Engineering $ 170,000 Product MOH Fixed 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 17 Indirect materials: Molding $ 18,000 Product MOH Variable 18 Indirect materials: Fabrication $ 13,000 Product MOH Variable 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 22 Utilities: Assembly $ 6,000 Product MOH Variable 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 27 Medical insurance 28 Workers' compensation insurance 29 Other salaries: Plant manager $ 18,000 Product MOH Fixed $ 45,000 Product MOH Fixed $ 13,000 Product MOH Fixed $ 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 Fixed 33 Other salaries: Security and grounds maintenance $ 44,000 Product MOH Fixed 34 Property taxes $ 18,000 Product MOH 35 Payroll taxes: Direct labor $ 79,000 Product MOH Fixed Variable 36 Payroll taxes: Other $ 165,000 Product MOH Fixed 37 Direct materials 38 Direct labor 39 Accounting manager's salary 40 Administrative office insurance 41 Advertising 42 Depreciation on office equipment $ 2,220,000 Product Direct materials Variable $ 1,365,000 Product Direct labor Variable $ 88,000 Period Administrative Fixed $ 2,000 Period Administrative Fixed $ 400,000 Period Selling Fixed $ 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 46 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 49 Customer billing supplies 50 $ 2,000 Period Administrative Administrative Fixed Variable 51 52 PivotTable Charts SumIF Cost Summary Income