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
|
mce_markernbsp; 35.40
|
mce_markernbsp; 53.70
|
mce_markernbsp; 6.80
|
mce_markernbsp; 0.00
|
mce_markernbsp; 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.