Question

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. Chapter 5 Activity-Based Costing and Management 225hiL6956X_ch05_168-229.indd 225 06/17/16 08:10 PMRequired: 1. Prepare a customer profitability analysis for Trace Telecom and Caltex Computer. (Hint: Refer to Exhibit 5–13 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 Telecom’s sales revenue was $185,000 and Caltex Computer’s cost of goods sold was $59,000. Refer to the information given in the preceding problem for Fresno Fiber Optics and two of its custom-ers, Trace Telecom and Caltex Computer. Additional information for six of Fresno’s other customers for the most recent year follows:■ Problem 5–66Customer-Profitability Profile; Continuation of Preceding Problem (LO 5-9)Tele-Install, operating profit: $(18,000)Customer-Related ActivitiesCost Driver BaseCost Driver RateSales activity .................................................Sales visits .................................................$1,000Order taking .................................................Purchase orders ........................................200Special handling ...........................................Units handled ............................................50Special shipping ...........................................Shipments ..................................................500Cost-driver data for two of Fresno’s customers for the most recent year areCustomer-Related ActivitiesTrace TelecomCaltex ComputerSales activity ..................................................    8 visits ......................................................  6 visitsOrder taking ..................................................   15 orders ....................................................  20 ordersSpecial handling ............................................800 units handled ........................................600 units handledSpecial shipping ............................................ 18 shipments .............................................   20 shipmentsThe 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 TelecomCaltex ComputerSales revenue ...............................................$190,000 ...............................................$123,800Cost of goods sold .......................................   80,000 ...............................................62,000General selling costs ....................................   24,000 ...............................................18,000General administrative costs .......................   19,000 ...............................................16,000

Required: 1. Prepare a customer profitability analysis for Trace Telecom and Caltex Computer. (Hint: Refer to Exhibit 5–13 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 Telecom’s sales revenue was $185,000 and Caltex Computer’s cost of goods sold was $59,000.

Answer 1