Question

> Paste B I Uv TITE V ab Open recovered workbooks? Your recent changes were saved. Do you want to continu J34 ху fх A B D E F 1 $ $ S Fixed / Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed $ $ $ $ $ Fixed $ S $ $ $ S $ $ $ S Amount 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 $ $ 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 suprision: 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 Utilties: 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 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 52 Fixed Fixed Fixed Fixed Fixed Fixed Variable Variable Variable Variable Variable Variable Fixed Fixed Fixed Fixed Fixed Fixed Fixed $ S Cost Classifications: Manufacturing / Product / Period Nonmanufacturing Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product MOH Product Product MOH Product Direct materials Product Direct labor Period Administrative Period Administrative Period Selling Period Administrative Period Administrative Period Administrative Period Administrative Period Selling Period Selling Period Administrative Period Administrative $ $ S S $ $ S Fixed $ $ $ S MOH $ $ Fixed Fixed Fixed Fixed Variable Fixed Variable Variable Fixed Fixed Fixed Fixed Fixed $ $ $ $ S $ $ 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 Fixed $ S Fixed Variable Fixed $ $ $ Fixed Variable Pivot Table Charts SumlF Cost Summary Income
  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.

Answer 1

a.

Total Product Costs = $ 5,974,000

Total Period Costs = $ 1,216,000

b.

Direct Materials = $ 2,220,000

Direct Labor = $ 1,365,000

Manufacturing Overhead = $ 2,389,000

c.

Selling Expenses = $ 1,000,000

Administrative Expenses = $ 216,000

Crispin Company
Traditional Format Income Statement
		$
Sales		8000000
Cost of Goods Sold		5974000
Gross Margin		2026000
Selling & Administrative Expenses:
Selling	1000000
Admnistrative	216000	1216000
Net Operating Income		810000
Crispin Company
Contribution Format Income Statement
		$
Sales		8000000
Variable Expenses:
Cost of Goods Sold	3753000
Variable Selling	480000
Variable Administrative	2000	4235000
Contribution Margn		3765000
Fixed Expenses:
Fixed MOH	2221000
Fixed Selling	520000
Admnistrative	214000	2955000
Net Operating Income		810000

Workings:

Fixed Manufacturing overhead
Equipment Depreciation
	Molding	400000
	Fabrication	300000
	Assembly	50000
	MH	15000
	Other	10000
Production Supervision
	Molding	60000
	Fabrication	65000
	Assembly	58000
Indirect Labor	M&R	72000
	MH	68000
	Engineering	170000
	RM purchasing	93000
	QC	38000
	p&s	99000
Utilities
	PL	12000
	H&C	24000
Property Insurance		26000
Liability Insurance		18000
Medical Insurance		45000
WC Insurance		13000
Other Salaries
	PM	165000
	Accounting	89000
	AS	56000
	Janitorial	48000
	Security	44000
Property Taxes		18000
Payroll Taxes - other		165000
Fixed Manufacturing overhead		2221000
Variable Expenses:
Indirect Materials
	Molding	18000
	Fabrication	13000
	Assembly	8000
Utilities
	Molding	24000
	Fabrication	20000
	Assembly	6000
Payroll Taxes - DL		79000
Variable Manufacturing overhead		168000
Direct Materials		2220000
Direct Labor		1365000
Total Variable Cost		3753000
Total Product Cost		5974000
MOH (Fixed + Variable)		2389000
Am Salary		88000
AO Insurance		2000
Dep on OE		15000
H&E for office		4000
Janitorial		26000
Salaries		78000
Supplies		1000
Billing (V)		2000
Total Administrative Expenses		216000
Advt.		400000
SM Salary		120000
Sales Commission(V)		480000
Total Selling Expenses		1000000
Total Period Costs		1216000