Topic: Customer profitability analysis; Activity based costing
The following activity based costing information was the basis for the analysis
Customer-Related Activity Cost Driver Base Cost Driver Rate
Sales activity Sales visit...............................$2,000
Order taking..................................... Purchase order.................... $400
Special handling............................... Units handled...................... $100
Special shipping................................. Shipments........................... $1,000
Cost driver data for two of FiberCom's customers for the most recent year are:
Customer Related Activity Caltex Computer Trace Telecom
Sales activity..................................... 8 visit ............................. 6 visits
Order taking.................................... 15 order ....................... 20 order
Special handling............................. 800 units handled................ 600 units handled
Special shipping.............................. 18 shipments......................... 20 shipments
The following additional information has been complied for the FiberCom for two of its customers,
Caltex Computer and Trace Telecom, for the most recent year:
Caltex Computer Trace Telecom
Sales revenue................................. $380,000........................................ $247,600
Cost of goods sold........................ $160,000......................................... $124,000
General selling costs...................... $48,000.......................................... $36,000
General administrative costs........ $38,000......................................... $32,000
Required:
Question 1. Prepare a customer profitability analysis for Caltex Computer and Trace Telecom.
Question 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.