Problem 565Customer-Profitability Analy-sis; Activity-Based Costing (LO 5-9) 1. Operating income, Trace Telecom: $7,000

Fresno Fiber Optics, Inc. manufactures fiber optic cables for the computer and telecommunications industries. At the request of the company vice president of marketing, the cost management staff has recently completed a customer-profitability study. The following activity-based costing information was the basis for the analysis.

Customer-Related Activities	Cost Driver Base	Cost Driver Rate
Sales activity	Sales visits	$1,000
Order Taking	Purchase orders	$200
Special handling	Units handled	$50
Special shipping	Shipments	$500

Cost-driver data for two of Fresnos customers for the most recent year are

Customer-Related Activities	Trace Telecom	Caltex Computer
Sales activity	8 visits	6 visits
Order Taking	15 orders	20 orders
Special handling	800 units handled	600 units handled
Special shipping	18 Shipments	20 shipments

The following additional information has been compiled for Fresno Fiber Optics for two of its cus-tomers, Trace Telecom and Caltex Computer, for the most recent year:

	Trace Telecom	Caltex Computer
Sales revenue	$190,000	$123,800
Cost of goods sold	$80,000	$62,000
General selling costs	$24,000	$18,000
General administrative costs	$19,000	$16,000

Required:

1. Prepare a customer profitability analysis for Trace Telecom and Caltex Computer. (Hint: Refer to Exhibit 513 for guidance.)

2. Build a spreadsheet: Construct an Excel spreadsheet to solve requirement (1) above. Show how the solution will change if the following information changes: Trace Telecoms sales revenue was $185,000 and Caltex Computers cost of goods sold was $59,000.

Show all calculations done in Excel.