FiberCom, 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.

Cost-driver data for two of FiberCom€™s customers for the most recent year are:

The following additional information has been compiled for FiberCom for two of its customers, Caltex Computer and Trace Telecom, for the most recent year:

Required:
1. Prepare a customer-profitability analysis for Caltex Computer and Trace Telecom.
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 Telecom€™s sales revenue was $250,000 and Caltex Computer€™s cost of goods sold was$155,000.

Customer-Related Activities Sales actvity Order taking . Special handing Special shipping Cost Driver Base Cost Driver Rate $2,000 400 100 1,000 Sales visits Units handled .. Shipments Customer-Related Activities Sales activity. Order taking Special handing Special shipping18 shipments20 shipments Caltex Computer Trace Telecom 8 visits 6 visits 20 orders .. 800 units handled 600 units handled Sales revenue. Cost of goods sold General selling costs.. General administrative costs.. Caltex Computer $380,000 160,000 48,000 38,000 Trace Telecom $247,600 124,000 36,000 32,000