CH-6 Managing Customer Profitability – Cost Management: Strategies for Business Decisions : Book Guide

Chapter 6 Managing Customer Profitability ANSWERS TO REVIEW QUESTIONS 6.1 Refer to the list of key terms at the end of Chapter 6 and the glossary. 6.2 Total profitability is the same using either a customer or product-line profitability perspective – total revenues and total expenses must be the same regardless of how they are split up. Although both approaches are important and useful, the major differences between the two are how revenues and expenses are reported. Traditional accounting systems measure and report revenues and expenses by product line, so re-programming the system or additional analyses are necessary to produce customer profitability measures. ABC approaches may be very useful for this purpose because tracing costs by activities, many of which are customer oriented, will lead directly to customer profitability. 6.3 Exhibit 6-1 displays ranked customer profitability that is typical of organizations before taking measures to improve customer profitability. The major point of this exhibit is that relatively few customers often generate most of the organization’s profits. This minority of customers is extremely important to the organization – they should be encouraged to be loyal customers. On the other hand, many customers are unprofitable to serve. The organization should either transform or drop them, if they cannot be made profitable to serve. 6.4 Typical objectives for customer profitability analysis include a) measuring customer profitability and b) identifying effective and ineffective customer-related activities. Major customer-related resources and activities include: Customer-related resources Customer-related activities Resources to produce goods and services Manufacturing, customizing Selling Making sales calls, ordering, processing, administration Marketing Research, planning, advertising, promotion, developing and distributing catalogues Ordering Taking and processing orders (direct, mail, telephone, internet) Distribution Packing, shipping (internal or outsourced) Research and development Research on new products, development of feasible products General administration Supporting customer activities 6.5 Tradeoffs almost always include timeliness, relevance, accuracy, and cost of information. Managers would like to have information accurately focused on specific customers, yesterday and at no additional cost. Because this is not possible, analysts often must make judgments about tradeoffs, which usually result in less accurate information than would be ideal. 6.6 Because customer profitability analysis seeks to uncover uses of resources to serve customers, ABC is a natural tool to use. Identifying current customer activities can be the first step in the analysis. After understanding current uses of resources to serve customers, analysts then can use ABM to identify value-added and non-value added activities to serve customers. Enhancing value-added and eliminating non-value added activities can improve customer and overall profitability. 6.7 Qualitative factors include a) status of serving certain customers, b) potential for future profitability, c) entry to new markets, d) transfer of knowledge from innovative customers, e) potential for cost savings from shared services. Any of these factors may be sufficient to convince a company to retain an otherwise unprofitable customer. 6.8 Typical improvements include refining the ordering process to eliminate errors and reduce ordering time and effort, increasing the flexibility of processes to meet customers’ variable needs, improving the reliability and responsiveness of distributing products to customers. Other changes include charging unprofitable customers for services rendered; they will either pay for these services or go elsewhere – improving profitability in either case. 6.9 Actually, considering the eight recommendations for leading change given at the end of Chapter 1 would be wise. The focus company of Chapter 6 used this set of guidelines with good outcomes. To review, the recommendations are: a. Identify a Need for Change b. Create a Team To Lead and Manage the Change c. Create a Vision of the Change and a Strategy for Achieving the Vision d. Communicate the Vision and Strategy for Change and Have the Change Team be a Role Model e. Encourage Innovation and Remove Obstacles to Change f. Ensure that Short-term Achievements are Frequent and Obvious g. Use Successes to Create Opportunities for Improvement in the Entire Organization h. Reinforce a Culture of More Improvement, Better Leadership, and More Effective Management ANSWERS TO CRITICAL ANALYSES 6.10 There is some truth in this statement because until a customer purchases a product, there is no revenue and no profit. A traditional focus on product profitability could lead to a myopic emphasis on managing manufacturing costs, which is the typical emphasis of traditional accounting systems. This could lead to in-discriminant lumping of customer-related costs into selling, general and administrative costs, which in many organizations are significant components of profit (or loss). 6.11 It is difficult to verify the claims and usefulness of most internet advertising, but several guidelines may help. First, have a thorough understanding of the problems you are seeking to solve and the available literature on the subject – you should be an informed consumer. Second, obtain word-of-mouth information from peers. Third, check with organizations that have used specific services (reputable companies seeking your business should be willing to share their customer list with you). 6.12 This argument might be dangerous because it implies marginal returns from improvements in customer satisfaction will always be positive. If so, customer satisfaction would be an unusual economic good. In general, we expect decreasing marginal returns at some point from all economic activity. It also is true, however, that indiscriminate cost cutting can be dangerous because this will cut both value-adding and non-value adding activities. Managing customer profitability usually involves both enhancing value-added and eliminating non-value added activities. 6.13 Higher contribution margins and growing revenues can be good things, certainly. However, as many technology firms have learned recently, bottom-line profitability is what separates successful from unsuccessful companies. This bottom line includes costs of R&D and customer service, both of which can be very large relative to revenues and contribution margins. Commitments to innovation and customer service must be matched by profitability – which must be achieved at some point or all those commitments may be wasted. 6.14 Education level is a major predictor of future income, so offers to college students are attempts to capture customers who will be profitable in the future. One could be quite cynical about this by observing that many students are unaware of the high costs of credit-card debt and quickly become dependent on this high-cost lifestyle support. Though write-offs for delinquent accounts may be high (with adverse effects for both students and credit-card companies), enough students pay their debts and become profitable customers to justify the practice. 6.15 Segmenting and charging customers differentially may be distasteful to some, but it is not illegal as long as companies can justify the practice on a cost basis. The defense of this practice would require a credible customer-profitability analysis. 6.16 Telecommunication customers learned to switch among service providers often to earn these bonuses. Service providers learned that the costs of excessive switching were too high to justify paying switching bonuses. These costs include the bonuses and the administrative costs associated with changing and maintaining billing information. 6.17 Although many consumers might disagree, many of these service companies had over-invested in customer satisfaction. They were wooing too many customers that were not profitable. Refined approaches involve keeping profitable customers satisfied, but giving less service to unprofitable customers. Thus, profits can improve while overall (average) customer satisfaction declines. 6.18 You should ask questions about the profitability of both delighted and satisfied customers before approving a campaign to raise the levels of either or both. The costs of improving these statistics might exceed the benefits. It seems wise to find out one way or the other before committing valuable resources. 6.19 The experience of many large companies is that customer profitability analysis does cost millions to implement and maintain. But that should not automatically rule it out. Investment in information, as well as equipment and technology, is required to stay abreast of the competition. Factors to consider might include a) actions by competitors, b) changes in customer loyalty, retention, and services, c) declining overall profitability, d) feedback from sales and manufacturing personnel about customer orders. SOLUTIONS TO EXERCISES 6.20 (30 min) Answers will vary, but good answers will cover presentation, understandability, and credibility of information presented on these websites. There will be some overlap of sites with those found in exercise 6-23. 6.21 (30 min) Answers will vary, but good answers will describe the nature of information provided by the Census Bureau, including types of industries, locations, population, and so on. 6.22 (30 min) Answers will vary, but good answers will cover presentation, understandability, and credibility of information presented on these websites. One can use this information to learn about the tools available for building customer information. There will be some overlap of sites with those found in exercise 6-21. 6.23 (30 min) Customer profitability. This exercise requires an analysis similar to that in Exhibit 6-5. Facts Total Sportsters Pickups Contribution margin ratio 46.7% 40.0% 60.0% Revenues $ 9,000,000 $ 6,000,000 $ 3,000,000 Variable cost of goods sold [revenues x (1- CM ratio)] 4,800,000 3,600,000 1,200,000 Contribution margin 4,200,000 2,400,000 1,800,000 Operating costs 3,100,000 Operating income $ 1,100,000 Return on sales (operating income ÷ revenues) 12.2% Customer sales to: Urban Rural Sales of: Sportsters Sportsortsters 100% 90% 10% Pickups 100% 20% 80% Customer Profitability Total Urban Rural Revenues traced to customers $6,000,000 = (0.90 x $6,000,000) + (0.20 x $3,000,000) $3,000,000 = (0.10 x $6,000,000) + (0.80 x $3,000,000) $ 9,000,000 $ 6,000,000 $ 3,000,000 Variable cost of goods sold $3,480,000 = (0.90 x $3,600,000) + (0.20 x $1,200,000) $1,320,000 = (0.10 x $3,600,000 + (0.80 x $1,200,000) 4,800,000 3,480,000 1,320,000 Contribution margin 4,200,000 2,520,000 1,680,000 Operating costs traced to customers (given) 3,100,000 2,400,000 700,000 Operating income $ 1,100,000 $ 120,000 $ 980,000 Return on sales 12.2% 2.0% 32.7% EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.24 (30 min) Customer profitability. This exercise requires an analysis similar to that in Exhibit 6-5. Facts Total Sportsters Pickups Contribution margin ratio 46.7% 40.0% 60.0% Revenues $ 9,000,000 $ 6,000,000 $ 3,000,000 Variable cost of goods sold [revenues x (1- CM ratio)] 4,800,000 3,600,000 1,200,000 Contribution margin 4,200,000 2,400,000 1,800,000 Operating costs 3,100,000 Operating income $ 1,100,000 Return on sales (operating income ÷ revenues) 12.2% Customer sales to: Urban Rural Sales of: Sportsters Sportsortsters 100% 90% 10% Pickups 100% 20% 80% Customer Profitability Total Urban Rural Revenues traced to customers $6,000,000 = (0.90 x $6,000,000) + (0.20 x $3,000,000) $3,000,000 = (0.10 x $6,000,000) + (0.80 x $3,000,000) $ 9,000,000 $ 6,000,000 $ 3,000,000 Variable cost of goods sold $3,480,000 = (0.90 x $3,600,000) + (0.20 x $1,200,000) $1,320,000 = (0.10 x $3,600,000 + (0.80 x $1,200,000) 4,800,000 3,480,000 1,320,000 Contribution margin 4,200,000 2,520,000 1,680,000 Operating costs traced to customers (given) 3,100,000 2,400,000 700,000 Operating income $ 1,100,000 $ 120,000 $ 980,000 Return on sales 12.2% 2.0% 32.7% EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.24 Bonneville 6.25 (10 min) Return on customer sales. Target return on sales 30% Current sales revenue $ 9,000,000 Target profit 2,700,000 Target cost 6,300,000 Currently feasible costs 7,900,000 Target cost reduction $ 1,600,000 Comment: This is a substantial cost reduction that might not be achievable, at least in the short run. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.26 (30 min) Customer profitability. This exercise requires an analysis similar to that in Exhibit 6-5. Facts Total Sunshades Windshield Wipers Contribution margin ratio 70.0% 60.0% Revenues $ 11,000,000 $5,000,000 $ 6,000,000 Variable cost of goods sold [revenues x (1-CM ratio)] 3,900,000 1,500,000 2,400,000 Contribution margin 7,100,000 3,500,000 3,600,000 Operating costs 4,500,000 Operating income $ 2,600,000 Return on sales (operating income ÷ revenues) 23.6% Customer sales to: Hardware Auto Parts Sales of: Windshield Wipers 100% 40% 60% Sunshades 100% 30% 70% Customer Profitability Total Hardware Auto Parts Revenues traced to customers $3,900,000 = (0.40 x $6,000,000) + (0.30 x $5,000,000) $7,100,000 =(0.60 x $6,000,000) + (0.70 x $5,000,000) $ 11,000,000 $3,900,000 $ 7,100,000 Variable cost of goods sold $1,410,000 = (0.40 x $2,400,000) + (0.30 x $1,500,000) $2,490,000 = (0.60 x $2,400,000) +(0.70 x $1,500,000) 3,900,000 1,410,000 2,490,000 Contribution margin 7,100,000 2,490,000 4,610,000 Operating costs traced to customers 4,500,000 2,100,000 2,400,000 Operating income $ 2,600,000 $ 390,000 $ 2,210,000 Return on sales 23.6% 10% 31.1% EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.26 Manning 6.27 (10 min) Return on customer sales. Target return on sales 30% Current sales revenue $ 11,000,000 Target profit 3,300,000 Target cost 7,700,000 Currently feasible costs 8,400,000 Target cost reduction $ 700,000 EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.27 Manning 6.28 (30 min) Customer profitability. This requires an analysis similar to that in Exhibit 6-5. Facts Total Personnel checks Supplier reliability Contribution margin ratio 61.8% 65.0% 60.0% Revenues $ 11,000,000 $4,000,000 $ 7,000,000 Variable cost of services sold [revenues x (1-CM ratio)] 4,200,000 1,400,000 2,800,000 Contribution margin 6,800,000 2,600,000 4,200,000 Operating costs 5,500,000 Operating income $ 1,300,000 Return on sales (operating income ÷ revenues) 11.8% Customer sales to: Government Agencies Corporations Sales of: Personnel checks 100% 80% 20% Supplier reliability 100% 20% 80% Customer Profitability Total Government Agencies Corporations Revenues traced to customers $4,600,000 = (0.80 x $4,000,000) + (0.20 x $7,000,000) $6,400,000 = (0.20 x $4,000,000) + (0.80 x $7,000,000) $ 11,000,000 $4,600,000 $ 6,400,000 Variable cost of services sold $1,680,000 = (0.80 x $1,400,000) + (0.20 x $2,800,000) $2,520,000 = (0.20 x $1,400,000) + (0.80 x $2,800,000) 4,200,000 1,680,000 2,520,000 Contribution margin 6,800,000 2,920,000 3,880,000 Operating costs traced to customers 5,500,000 1,400,000 4,100,000 Operating income $ 1,300,000 $1,520,000 $ (220,000) Return on sales 11.8% 33.0% (3.4)% EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.28 Homeland Research Services 6.29 (10 min) Return on customer sales. Target return on sales 20% Current sales revenue $ 11,000,000 Target profit 2,200,000 Target cost 8,800,000 Currently feasible costs 9,700,000 Target cost reduction $ 900,000 EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.29 Homeland Research Services 6.30 (30 min) Customer level costs Customer Total Alpha Beta Chili Dog Shipments per year 128,000 40,000 20,000 60,000 8,000 Percent delivered by ME 80% 90% 25% 80% Percent outsourced delivery 20% 10% 75% 20% total 100% 100% 100% 100% Shipments delivered by ME 71,400 32,000 18,000 15,000 6,400 Percent ME deliveries by customer* 100% 44.81793% 25.21008% 21.00840% 8.96359% ME distribution costs** $ 6,700,000 $ 3,002,801 $ 1,689,076 $ 1,407,563 $ 600,560 Outsourced shipments 56,600 8,000 2,000 45,000 1,600 Percent outsourced deliveries by customer* 100% 14.13428% 3.53336% 79.50530% 2.82686% Outsourced shipment costs** $ 120,000 $ 16,961 $ 4,240 $ 95,406 $ 3,392 Total distribution costs by customer** $ 6,820,000 $ 3,019,762 $ 1,693,316 $ 1,502,969 $ 603,952 *Percents rounded to five places. **Amounts for Alpha, Beta, Chili and Dog are rounded to nearest dollar. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.31 (25 min) Customer level costs Customer Total Grocery Drug Stores Discount Stores Promotions per year 210 60 80 70 Percent designed by OP 85% 82% 95% Percent outsourced design 15% 18% 5% total 100% 100% 100% Promotions designed by OP (ave) 183.1 51 65.6 66.5 Percent OP designs by customer* 100% 27.85% 35.83% 36.32% OP design costs $ 112,000 $ 31,196 $ 40,127 $ 40,677 Outsourced designs (ave) 26.9 9 14.4 3.5 Percent outsourced designs by customer 100% 33.46% 53.53% 13.01% Outsourced design costs $ 27,000 $ 9,033 $ 14,454 $ 3,513 Total design costs by customer $ 139,000 $ 40,229 $ 54,581 $ 44,190 *Percents rounded to two decimal places. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.32 (20 min) Customer level costs Marketing costs and effort Total Record Clubs Retail stores Major account managers 8 8 - Sales representatives 150 - 150 Average salary/yr $ 200,000 $ 50,000 Marketing personnel costs $ 9,100,000 $1,600,000 $ 7,500,000 Percent marketing staff effort 40% 60% Marketing staff cost $ 480,000 $ 192,000 $ 288,000 Total marketing and sales costs $ 9,580,000 $1,792,000 $ 7,788,000 EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE SOLUTIONS TO PROBLEMS 6.33 (30 min) Customer-level costs Service costs and effort Total A B C Number of customers 5,500 3,000 2,000 500 Customer representatives 10 10 2 Average salary/yr $ 50,000 $ 40,000 $ 30,000 Service personnel costs $ 960,000 $ 500,000 $ 400,000 $ 60,000 Percent support service staff effort 100% 60% 30% 10% Support service staff cost $ 600,000 $ 360,000 $ 180,000 $ 60,000 Total marketing and sales costs $ 1,560,000 $ 860,000 $ 580,000 $ 120,000 EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.34 (40 min) Customer profitability a. Analysis Annual service resources Total Premium Standard Number of customers 100,000 30,000 70,000 Average revenue per customer (before discount) $ 440 $ 50 Price discount 10% 0% Sales representative coverage per 1,000 10,000 Average sales salary/yr $ 35,000 $ 35,000 Service bonus percentage of revenue 0.1% 0.1% Promotion costs $ 2,000,000 Relative promotion spending per customer group 2/3 1/3 General and administrative costs $ 2,400,000 Sales representatives (30,000÷1,000, 70,000÷10,000) 30 7 Promotion cost proportions 2/3 1/3 Sales $ 16,700,000 $ 13,200,000 $ 3,500,000 less sales discount 1,320,000 1,320,000 - Net sales 15,380,000 11,880,000 3,500,000 Service salary costs 1,295,000 1,050,000 245,000 Service bonus 15,380 11,880 3,500 Promotion costs 2,000,000 1,333,333 666,667 Total service and promotion costs 3,310,380 2,395,213 915,167 Customer margins 12,069,620 $ 9,484,787 $ 2,584,833 General and administrative costs 2,400,000 Operating profit $ 9,669,620 b. Both sets of customers generate large margins. Premium customers generate more than three times as much revenue as standard customers do. Premium customers also generate more than three times as much total margin as standard customers do. Even so, standard customers are profitable. Based on these data, the company should continue selling to its standard customers. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.35 (40 min) Customer profitability a. Analysis Annual service resources Total Bell Toshi Annual number of orders 5,000 3,000 2,000 Average cost of good sold per order $ 4,200 $ 1,800 Price markup 60% 60% Average deliveries per order 5 10 Warehousing costs per year $ 1,000,000 Delivery costs per year $ 6,000,000 General administrative costs $ 2,400,000 Percent of G&A devoted to orders 50% Order percentages 100% 60% 40% Deliveries per year 35,000 15,000 20,000 Delivery percentages 100% 42.86% 57.14% Sales price per order $6,720 $2,880 Sales $ 25,920,000 $ 20,160,000 $ 5,760,000 Cost of goods sold 16,200,000 12,600,000 3,600,000 Gross margin 9,720,000 7,560,000 2,160,000 Operating costs Order costs (1/2 of gen. admin. costs) 1,200,000 720,000 480,000 Delivery costs* 6,000,000 2,571,429 3,428,571 Warehousing costs 1,000,000 600,000 400,000 Total service and promotion costs* 8,200,000 3,891,429 4,308,571 Customer margins* $ 1,520,000 $ 3,668,571 $ (2,148,571) General administrative costs (untraced) 1,200,000 Operating profit $ 320,000 *Totals are accurate. Amounts summed to get the totals are rounded to nearest dollar. b. This analysis shows that Toshi is not a profitable customer. Toshi causes disproportionately higher delivery costs. The value of each order by Toshi is lower, yet Toshi requires higher frequency of deliveries. The markup charged does not reflect this greater level of service, so the company should re-evaluate its pricing for TS deliveries or negotiate less frequent deliveries. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.36 (50 min) Customer profitability a. Analysis Design activities Total Residential Commercial Designs submitted 2,500 200 Designs sold 2,000 100 Average direct cost per design (all) ($) 4,000 25,000 Marketing effort 100% 20% 80% Marketing cost $ 2,400,000 Annual activities and resources Total Regional National Traced sales of to: Residential designs 100% 60% 40% Commercial designs 100% 20% 80% Administrative effort 100% 60% 40% Administrative cost $ 3,000,000 Residential Commercial Design sales revenue $16,000,000 = 2,000 x 4,000 x 2.00 $ 6,250,000 = 100 x 25,000 x 2.50 $ 22,250,000 $ 16,000,000 $ 6,250,000 Cost of designs prepared $ 15,000,000 $ 10,000,000 $ 5,000,000 Customer profitability Total Regional National Sales $10,850,000 = 16,000,000x.6 + 6,250,000x.2 $11,400,000 = 16,000,000x.4 + 6,250,000x.8 $ 22,250,000 $ 10,850,000 $ 11,400,000 Cost of sales 7,000,000 = 10,000,000x.6 + 5,000,000x.2 8,000,000 = 10,000,000x.4 + 5,000,000x.8 15,000,000 7,000,000 8,000,000 Gross margin 7,250,000 3,850,000 3,400,000 Operating costs Marketing costs 672,000 = 2,400,000x(0.2x0.6 + 0.8x0.2) 1,728,000 = 2,400,000x(0.2x0.4 + 0.8x0.8) 2,400,000 672,000 1,728,000 Administrative costs 1,800,000 = 0.60 x 3,000,000 1,200,000 = 0.40 x 3,000,000 3,000,000 1,800,000 1,200,000 Total operating costs 5,400,000 2,472,000 2,928,000 Operating profit $ 1,850,000 $ 1,378,000 $ 472,000 Return on sales 8.3% 12.7% 4.1% b. National customers are less profitable for BHA; however, they can be desirable customers because of the prestige of winning national bids and because of the innovative designs that may be created. Both reputation and designs can be carried over to regional business. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.37 (50 min) Customer profitability. a. Analysis Excel solutions are found on the chapter solutions manual opening screen Annual activities and resources Total Custom Homebuilders General Contractors Bids submitted - residential 32 22 10 Bids submitted - commercial 13 3 10 Successful bids - residential 24 20 4 Successful bids - commercial 9 3 6 Success rate 92% 50% Average direct cost of residential job $ 10,500 $ 410,000 Average direct cost of commercial job $ 45,000 $ 250,000 Average markup over cost 100% 50% Contract management cost $ 800,000 Contract management effort 100% 20% 80% General administration cost $ 410,000 Bidding cost 630,000 Residential Commercial Total cost of bids submitted - custom 366,000 231,000 135,000 Total cost of bids submitted - general 6,600,000 4,100,000 2,500,000 Estimated total cost of bids submitted $ 6,966,000 $ 4,331,000 $ 2,635,000 Customer profitability Total Custom Homebuilders General Contractors Sales 690,000 = (20x10,500 + 3x45,000)x2 4,710,000 = (4x410,000 + 6x250,000)x1.5 $ 5,400,000 $ 690,000 $ 4,710,000 Cost of sales (sales less markup) 3,485,000 345,000 3,140,000 Gross margin 1,915,000 345,000 1,570,000 Operating costs Contract management cost (20%, 80%) 800,000 160,000 640,000 Bidding cost (allocated on relative total cost of bids submitted; $391,693 = $4,331,000/$6,966,000 x $630,000) 630,000 391,693 238,307 Total traced costs 1,430,000 551,693 878,307 Customer margin 485,000 $ (206,693) $ 691,693 General and administrative costs 410,000 Operating profits $ 75,000 Return on sales 1.4% (30.0)% 14.7% b. The relative profitability of the two types of customers hinges on the allocation of bidding costs, which may not be accurate, and G&A costs, which were not allocated. These two items really deserve more analysis. Other estimates seem reasonable. 6.38 (75 min) Customer profile. This analysis should not be attempted without spreadsheet software. Assuming this is good market information about drivers of customer profitability, this still is a subjective analysis because cutoffs and numerical scores are judgments. The analysis follows these steps: a) rank customers by each of the three attributes, b) plot each ranked attribute, c) award a score from 1 to 4 to each customer on each attribute, d) equally weight each score, e) sum scores of all equally weighted attributes, and f) classify customers as A, B, or C depending on total scores. Is this subjective? Are there other ways of doing this? Absolutely. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE a. Numerical Customer Classification Trades per year Other services b. Total scores and categories Customer Category Total score Balance Trades Services 17 C 3 1 1 1 27 C 3 1 1 1 14 C 4 2 1 1 20 C 4 2 1 1 24 C 4 2 1 1 7 C 5 2 1 2 10 C 5 1 2 2 1 B 6 2 3 1 3 B 6 2 2 2 4 B 6 3 2 1 6 B 6 2 1 3 13 B 6 2 2 2 23 B 6 2 2 2 28 B 6 1 2 3 30 B 6 2 2 2 2 B 7 4 2 1 8 B 7 2 2 3 11 B 7 3 3 1 12 B 7 2 2 3 15 B 7 2 1 4 16 B 7 3 3 1 21 B 7 2 2 3 9 A 8 2 4 2 18 A 8 3 3 2 26 A 8 3 3 2 5 A 9 2 4 3 29 A 9 3 3 3 22 A 10 4 4 2 25 A 10 4 2 4 19 A 12 4 4 4 c. The biggest concerns are whether a) these overall scores and category placements reliably measure customer profitability and b) decisions based on these rankings will improve overall profitability better than decisions made without this information. It should be apparent that this type of analysis requires reliable data and sound judgment. One consideration is whether the scores on each attribute should be equally weighted. It is easy to construct the last spreadsheet so that the total score is the sum of weighted individual scores. See how sensitive rankings and categories are to changes in the relative weights. 6.39 (30 min – if Pivot Table is used) Customer activities. a. Pivot table results Customer rating Data A B C Grand Total Percent of Customer rating 26.7% (8.0) 50% (15) 23.3% (7) 100% (30) Average account balance $ 51,951 $ 31,816 $ 13,938 $ 33,014 Average of Trades/yr 137.8 51.2 10.4 64.8 Average of Other services 8.5 5.9 1.1 5.5 b. Customer revenue data. Use the count data from part a to generate sample percentages (e.g., percentage of A customers = 26.7% = 8/30) and multiply these percentages by the population (1,000,000 customers) to obtain population estimates. Carry the average account balances, trades per year, and other services to each type of customer. Then add the data given for revenue spread, trading fees, and other fees. Customer revenue data Total A B C Customer sample percentages 100.0% 26.7% 50.0% 23.3% Customer population estimates 1,000,000 266,667 500,000 233,333 Average account balance* $ 51,951 $ 31,816 $ 13,938 Average annual trades* 137.8 51.2 10.4 Average other services* 8.5 5.9 1.1 Account revenue (spread) 2% 2% 2% Trading fee per trade $ 3.00 $ 3.00 $ 3.00 Other fees per service $ 200.00 $ 100.00 $ 50.00 * Answers are rounded. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.40 (50 min) Customer costs and profitability. Use the data given in this problem with the information generated in the previous problem to compute customer revenues, costs, and profitability. Each revenue item is the estimated number of customers (by type) x revenue driver (e.g., account balance) x revenue per unit of revenue driver (e.g., 2% spread). Tracing each cost item is somewhat more complex, but is in each case the total service cost multiplied by the weighted amount of service effort provided to each type of customer). Example calculations follow the table. Customer cost data Total A B C Intensity of service per unit of service Account management per account 1 1 1 Trading management per trade 1 1 1 Other services management per service 3 2 1 General administrative 3 2 1 Service costs Account management $ 250,000,000 Trading management $ 310,000,000 Other services management $ 440,000,000 General and administrative $ 360,000,000 Customer profitability Total A B C Revenues Account interest revenue $ 660,276,000 $ 277,071,333 $ 318,162,667 $ 65,042,000 Trading fees 194,300,000 110,200,000 76,800,000 7,300,000 Other service fees 763,333,333 453,333,333 296,666,667 13,333,333 Total revenue 1,617,909,333 $ 840,644,667 $ 691,629,333 $ 85,675,333 Traced operating costs Account management 250,000,000 66,666,667 125,000,000 58,333,333 Trading management 310,000,000 175,820,896 122,532,167 11,646,938 Other service management 440,000,000 230,153,846 200,820,513 9,025,641 General administrative 360,000,000 188,307,692 164,307,692 7,384,615 Total traced operating costs 1,360,000,000 660,949,101 612,660,372 86,390,527 Operating income $ 257,909,333 $ 179,655,566 $ 78,968,961 $ (715,194) Return on sales 15.9% 21.4% 11.4% -0.8% Note: Answers are based on actual, not rounded, numbers from previous example. Account interest revenue for type-C customers is computed as: $65,042,000 = 233,333 type-C customers x $ 13,938 x 0.02 Account management costs for type-A customers is computed as follows: $66,666,667 = ($250,000,000) x [(266,667x1) ÷ (266,667x1 + 500,000x1 + 233,333x1)] Other service management costs for type-B customers is computed as: $200,820,513 = ($440,000,000) x [(500,000x2x5.9) ÷ (266,667x3x8.5 + 500,000x2x5.9 + 233,333x1x1.1)] General administrative costs for type-A customers is computed as: 188,307,692 = ($360,000,000) x [(266,667x3x8.5) ÷(266,667x3x8.5 + 500,000x2x5.9 + 233,333x1x1.1)] EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.41 (60 min) Customer-level improvements. Solving this problem is easiest if you simply modify the spreadsheet used to solve the previous problem. EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE For alternative 1, set the proportion of type-C customers to zero and reduce each of the total service costs by 15%. All other factors should remain the same, and the spreadsheet will automatically compute profitability of the remaining customers, A and B. Alternative A - drop least profitable customers Customer revenue data Total A B C Customer sample proportions 100% 35% 65% 0% Customer population estimates 766,667 266,667 500,000 0 Average account balance $ 51,951 $ 31,816 $ 13,938 Average annual trades 137.8 51.2 10.4 Average other services 8.5 5.9 1.1 Account revenue (spread) 2% 2% 2% Trading fee per trade $ 3.00 $ 3.00 $ 3.00 Other fees per service $ 200.00 $ 100.00 $ 50.00 Customer cost data Total A B C Intensity of service per unit of service Account management per account 1 1 1 Trading management per trade 1 1 1 Other services management per service 3 2 1 General administrative 3 2 1 Service costs Management cost change -15% Account management $ 212,500,000 Trading management $ 263,500,000 Other services management $ 374,000,000 General administrative $ 306,000,000 Customer profitability Total A B Revenues Account interest revenue $ 595,234,000 $ 277,071,333 $ 318,162,667 Trading fees 187,000,000 110,200,000 76,800,000 Other service fees 750,000,000 453,333,333 296,666,667 Total revenue 1,532,234,000 $ 840,604,667 $ 691,629,333 Traced operating costs Account management 212,500,000 73,913,043 138,586,957 Trading management 263,500,000 155,281,818 108,218,182 Other services management 374,000,000 199,727,749 174,272,251 General administrative 306,000,000 163,413,613 142,586,387 Total traced operating costs 1,156,000,000 592,336,223 563,663,777 Operating income $ 376,234,000 $ 248,268,444 $ 127,965,556 Return on sales 24.6% 29.5% 18.5% For alternative 2, change only the data indicated and the spreadsheet should again compute profitability of each type of customer, A, B, and C. Alternative B change fees and proportions Customer revenue data Total A B C Customer sample proportions 100.0% 45.0% 50.0% 5.0% Customer population estimates 1,000,000 450,000 500,000 50,000 Average account balance $ 51,951 $ 31,816 $ 13,938 Average annual trades 137.8 51.2 10.4 Average other services 8.5 5.9 1.1 Account revenue (spread) 1.5% 2.0% 3.0% Trading fee per trade $ 2.5 $ 3.0 $ 4.0 Other fees per service $ 180.0 $ 120.0 $ 150.0 Customer cost data A B C Intensity of service per unit of service Account management per account 1 1 1 Trading management per trade 1 1 1 Other services management per service 3 2 1 General administrative 3 2 1 Service costs Management cost change 0% Account management $ 250,000,000 Trading management $ 310,000,000 Other services management $ 440,000,000 General administrative $ 360,000,000 Customer profitability Total A B C Revenues Account interest revenue $ 689,737,430 $ 350,668,406 $ 318,162,667 $ 20,906,357 Trading fees 233,854,464 154,968,750 76,800,000 2,085,714 Other service fees 1,053,071,429 688,500,000 356,000,000 8,571,429 Total revenue 1,976,663,323 $1,194,137,156 $ 750,962,667 $ 31,563,500 Traced operating costs Account management 250,000,000 112,500,000 125,000,000 12,500,000 Trading management 310,000,000 218,095,093 90,070,327 1,834,580 Other services management 440,000,000 289,084,589 149,475,837 1,439,575 General administrative 360,000,000 236,523,754 122,298,412 1,177,834 Total traced operating costs 1,360,000,000 856,203,436 486,844,575 16,951,988 Operating income $ 616,663,323 $ 337,933,720 $ 264,118,091 $ 14,611,512 Return on sales 31.2% 28.3% 35.2% 46.3% On the face of it, Alternative #2 appears to be more profitable (ROS is higher and operating income is nearly twice as large). However, many more assumptions that depend on marketing research went into this alternative. I don’t know about you, but these large numbers, which are common in large firms, make me quite nervous, because small percentage errors in assumptions can lead to large dollar differences. One may conduct additional analyses of customer profitability analysis (and you would if this were your fulltime job and big bucks depended on it). Some of these analyses are covered in detail in Chapter 12, which is devoted to general principles of financial modeling. SOLUTIONS TO CASES 6.42 (30 min) Customer profitability a. You would like to have information regarding the following: • The costs of marketing and providing each service, independently and in a package. • The characteristics of customers that buy each type of service, alone or self-created packages of services. • The correlations or clustering of customers who currently buy multiple services. That is, do current savings or certificate of deposit account customers also have home equity lines of credit? b. Though Smith and Jones are equally profitable given their checking accounts, equal profitability may not result from selling each a home equity loan. The primary differences between the two customers are: • Account balances – Smith may have a higher income and may have less demand for a line of credit. Jones, on the other hand, may benefit from an official line of credit rather than regularly overdrawing his account. • Returned checks – Smith has not overdrawn her account, but Jones does so often. Though Jones has made these checks good, this behavior implies a difference in risk to the bank. If Jones has access to a line of credit, will he always repay on schedule, or will the bank assume more bad debt? The instructor may give the information in the Revenue(cost) column below in advance and require additional analysis for part “b” of the case. Following are example data that generate the annual profitability of Smith and Jones. Changes in any of the data can change the profitability of each customer. Putting these data into a spreadsheet, where profit is computed as shown, easily demonstrates this sensitivity. Annual customer information Ms. Smith Mr. Jones Revenue (cost) Profit from checking account $76 $76 Average checking account balance $14,000 $900 4.0% spread Number of checks written 230 60 $ (2.00) Number of ATM transactions 24 36 $ (1.00) Number of returned checks 0 8 $(12.00) Overdraft fees paid 0 8 $ (35.00) Profit from Smith, $100 = $14,000 x 4% – 230 x $2 – 24 x $1 – 0 x $12 – 0 x $35 Profit from Jones, $100 = $900 x 4% – 60 x $2 – 36 x $1 – 8 x $12 – 8 x $35 EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE 6.43 (90 min.) Customer profitability a. This analysis requires a few judgments, which may vary across students and groups. This variation should reflect different assumptions and should generate class discussion about the reasonableness of these assumptions. The analysis shown following the discussions of the case is just one possible combination of reasonable assumptions, which are footnoted. b. The opportunity cost of displacing one large customer to serve ten small customers appears to be high ($87,750 per displaced large customer). Although this cost depends on a number of assumptions about costs of services, it does appear that Corporate Express is better off serving large customers (which is its strategy). c. Small business customers, which appear to be unprofitable, may be attractive to Corporate Express by some combination of the following changes: • Small customers have the potential to grow large enough to be served profitably • They can be encouraged to make fewer, larger orders • Delivery costs can be reduced, say by delivering to a central location rather than to individuals within the organization • Marketing, advertising and promotion costs can be reduced dramatically. d. Several of the costs assigned to small customers appear to be rather crude. Internet access costs, marketing, advertising and promotion costs seem to be rough approximations that could be improved with a modest amount of effort (following the guidelines of Chapter 4). This ABC effort could lead to overall improvements in serving all customers, as indicated by ABM techniques (Chapter 5). EXCEL SOLUTIONS ARE FOUND IN EXCEL SOLUTIONS FILE Assumes the cost is the same regardless of customer size. Assumes that the same total number of marketing employees will manage both large and small-company accounts. This may not be sustainable in the long run if many small customers are added because each requires a disproportionate (relative to sales) marketing effort. Thus, including this allocation increases the visibility of this future problem (This is the full-ABC argument from Chapter 5). This cost is the hourly cost multiplied by the number of hours. This assumes the same level of advertising and promotion regardless of customer size. Assumes that the total cost is unchanged and will not change; thus, no allocation is included. Assumes that the total cost is unchanged and will not change; thus, no allocation is included. Solution Manual for Cost Management: Strategies for Business Decisions Ronald W. Hilton, Michael W. Maher, Frank H. Selto 9780073526805, 9780072430332, 9780072830088, 9780072299021, 9780072881820, 9780072882551, 9780070874664, 9780072388404, 9780072343533