By Arnaud De Bruyn
1. Before beginning any case, students should familiarize themselves with the model being used. Marketing Engineering for Excel comes with tutorials that demonstrate the capability of each model. The tutorial can be found under each model within the ME►XL menu after starting Excel. These tutorials are designed to work with our OfficeStar examples which are located in the My Marketing Engineering directory, usually installed in My Documents during software installation. The data required for this case is located in the My Marketing Engineering directory (usually located within My Documents): SyPhone Data (CLV).xls
SyPhone 1 is a cell phone company that sells cell phone contracts to small to medium-sized companies, usually to equip their sales representatives and managers with cell capabilities. A typical contract covers 5 to 500 cell phones and includes phone equipment, support, service, maintenance, and unlimited phone calls for $80 to $100 per month per user. SyPhone finds itself in a highly competitive B2B market, one in which major service providers compete alongside several large resellers. The customer base is very sensitive to price, but only if service quality is high. Typically, a substantial barrier stands against customers switching to competitors, because such a switch would involve changing equipment and retraining users. However, customer switching is a possibility when contracts are about to expire. SyPhone needs to understand the profitability of its customers before generating a renewal bid for them. For new customers, SyPhone also needs to understand their potential profitability before offering them a contract. Historically, SyPhone has offered various discount rates to prospective customers. According to the company database, it appears that those customers who obtained the largest discounts are the least profitable in the short-term (as expected), but they have a somewhat lower churn rate. These customers tend to stay with the company longer and do not switch as much to competitors as do other customers. These case data may be used to explore whether offering large discounts is really profitable for the company in the long term or whether it is simply 1
The case describes a real situation using hypothetical data and a fictitious company.
Copyright © 2008 by DecisionPro, Inc. To order copies or request permission to reproduce materials, go to www.decisionpro.biz. No part of this publication may be reproduced, stored in a retrieval system, used in a spreadsheet, or transmitted in any form or by any means – electronic, mechanical, photocopying, recording or otherwise – without the permission of DecisionPro, Inc.
shooting itself in the foot. The case also involves an analysis of the appropriate discount factors to apply to future revenue.
Understanding the data
The SyPhone case study uses the standard Customer Lifetime Value template (for more information, please refer to the CLV tutorial). SyPhone customers may be grouped into two segments: Large accounts represent contracts that cover between 100 and 500 cell phones. A contract of 500 cell phones is pretty large for a small company like SyPhone; a large account covers about 150 cell phones on average. Small accounts represent contracts that cover between 5 and 100 cell phones. A small account covers about 20 cell phones on average. When negotiating contracts, some customers receive bigger discounts than others. For simplicity, they may be grouped into two subgroups: No rebate customers pay $100 per month per user. This price includes phone equipment, support, service, maintenance, and unlimited calling. Rebate customers pay about $80 per month per user for the same service. The Excel spreadsheet that contains the data you will need to evaluate the SyPhone situation and develop appropriate recommendations follows:...