Preparing an Action Analysis Report using Excel

Fairway Golf Corporation produces private label golf clubs for pro shops throughout North America. The company uses activity-based costing to evaluate the profitability of serving its customers. This analysis is based on categorizing the company's costs as follows, using the ease of adjustment color coding scheme.

	Ease of Adjustment Code
Direct Materials	Green
Direct Labor	Yellow
Indirect Labor	Yellow
Factory Equipment Depreciation	Red
Factory administration	Red
Selling & general wages and salaries	Red
Selling & administrative depreciation	Red
Marketing expenses	Yellow

Management would like to evaluate the profitability of a particular customer - Shaker Run Golf Club of Lebanon, Ohio. Over the last twelve months this customer submitted one order for 100 golf clubs that had to be produced in four batches due to differences in product labeling requested by the customer. Summary data concerning the order appear below.

Number of clubs	100
Number of orders	1
Number of batches	4
Direct labor-hours per club	.3
Selling price per club	$50.00
Direct materials cost per club	$29.50
Direct labor rate per hour	$20.50

A cost analyst working in the controller's office at the company has already produced the action analysis cost matrix for the Shaker Run Golf Club that appears below:

	Supporting Direct Labor	Batch Processing	Order Processing	Customer Service	Total
Activity	30 DL Hours	4 batches	1 order	1 customer
Manufacturing Overhead:
Indirect Labor	$ 35.40	$ 53.70	$ 6.80	$ 0.00	$ 95.90
Factory equip depreciation	102.80	.90	0.00	0.00	103.70
Factory Administration	18.50	.50	12.00	228.00	259.00
Selling & administration OH:
Wages & salaries	11.00	0.00	36.00	382.00	429.00
Depreciation	0.00	0.00	6.00	24.00	30.00
Marketing expenses	117.70	0.00	54.00	369.00	540.70
Total	$ 285.40	$ 55.10	$ 114.80	$1003.00	$1458.30

REQUIRED:

Prepare an action analysis report showing the profitability of the Shaker Run Golf Club. Include direct materials and direct labor hour costs in the report. Use Excel, save it to your desktop and then upload and submit your assignment.