Customer data analytics
maomaochou
Homework2021918.xlsxBackground MYWay Communications
| Background: MYWay Communications |
| MYWay Communications is a company that provides television programming and High Speed Internet access to residential customers. The VP of Marketing at MYWay Communications would like to better understand the Lifetime Value of 200,000 customers that were acquired five years ago as a result of a $30M television marketing campaign. In particular, she would like to understand the LTV of two types of customers: 1) Basic Customers, who only get access to Television programming, and 2) Premium Customers, who get access to both TV and High Speed Internet. After customers were acquired in Year 1 MYWay placed phone calls to customers in Year 2 to Year 5 to offer customers the opportunity to upgrade to bigger DVRs, subscribe to premium channels . They only contacted customers from the prior year (Ex. In Year 3 they called Year 2 customers). The costs of this communication are provided on the 'MYWay Communications LTV' tab. The VP of Marketing has asked the Database Analyst on her team to calculate some basic statistics on the customers that were acquired from that marketing campaign. The analyst's report is displayed on the next tab, 'MYWay Communications LTV', along with some additional information regarding costs and discount rate. The VP of Marketing wants you to do the following: 1) calculate the Lifetime Value of both the Basic and Premium customers after 5 years using the income statement method. 2) answer the questions listed on the 'MYWay Communications LTV' tab. |
MYWay Communiations LTV
| MYWay Communications | ||||||||
| Descriptive Statistics from MYWay Communications Customer Database | 0 | 1 | 2 | Table 1: Segment Evaluation | ||||
| Segment 1: Basic (TV Only) | Segment 2: Premium (TV + Internet) | Segment 1: Basic (TV Only) | Year 1 | Year 2 | Year 3 | |||
| Number of Customers | Annual Fees Paid | Number of Customers | Annual Fees Paid | Number of Customers | Segments | |||
| Year 1 | 85,000 | $ 480.00 | 115,000 | $ 900.00 | Average Expenditure | Basic (TV Only) | Premium (TV+Internet) | Total |
| Year 2 | 76,500 | $ 540.00 | 103,500 | $ 960.00 | ||||
| Year 3 | 65,025 | $ 600.00 | 82,800 | $ 1,020.00 | Revenues | Segment Size | ||
| Variable Costs | Segment Size (% of Total) | |||||||
| Additional Facts | Contribution Margin | |||||||
| Acquisition Costs | Lifetime Value (Year 3) | |||||||
| Annual Cost of Marketing Campaign | $ 30,000,000 | Total Acquisition Costs | ||||||
| Total Number of Customers Acquired from Campaign | 200,000 | Retention Spending Costs | Segment LTV | |||||
| Acquisition Costs per Customer (Assumed to be same for both segments) | Operating Profit | % of Total NPV Profit | ||||||
| Discount Rate | Index (% of NPV Profit/% Size) | |||||||
| Retention Spending on Current Customers in Year 2 to Year 5 (Phone Call to Upgrade to Bigger DVR, Pay Channels or HBO ) | NPV (Net Profit/Discount Rate) | |||||||
| Number of Phone Calls per Year | 4 | Cumulative NPV Profit | Question 1: For each segment, which year do they break even and become profitable? | |||||
| Cost per Outbound Call | $2.50 | Average Customer LTV | ||||||
| Total Annual Retention Costs per Customer | ||||||||
| 0 | 1 | 2 | ||||||
| Variable Costs as % of Revenues | Segment 2: Premium (TV+Internet) | Year 1 | Year 2 | Year 3 | ||||
| Basic (TV Only) | 55% | Number of Customers | ||||||
| Premium (TV + Internet) | 25% | Average Expenditure | ||||||
| d (Discount Rate) | 20% | Revenues | ||||||
| Variable Costs | ||||||||
| Contribution Margin | ||||||||
| Total Acquisition Costs | ||||||||
| Retention Spending Costs | Question 2: For each segment what is the retention rate in Year 3 (% customers retained from Year 2)? | |||||||
| Operating Profit | ||||||||
| Discount Rate | ||||||||
| NPV (Net Profit/Discount Rate) | ||||||||
| Cumulative NPV Profit | ||||||||
| Average Customer LTV | ||||||||
| Question 3: Considering the relative size of each segment, complete Table 1: 'Segment Evaluation' table. Which segment is more valuable? Support your point of view with evidence. | ||||||||
KC Steaks Background
| Background: KC Steaks KC Steaks is a company that sells and ships gourmet steaks and other perishable food products to customers around the country in sealed containers filled with dry ice to preserve freshness. For twenty years, KC Steaks operated through mail order, but now KC Steaks sells through multiple channels, including nearly one hundred retail stores, telemarketing, and its online e-commerce site. In recent meetings during the strategic planning process for next year, the management team wanted to know how the lifetime value of its customers would be impacted by some proposed changes to its marketing plan in order to increase the acquisition of new customers from two target segments: 'Foodies' and 'Non-Foodies'. KC Steaks is considering the purchase of a list of “foodies” from a list broker for $1.50 per name. They are able to obtain a list of ‘non-foodies’ for free. They intend to send each person on the list a catalog every month for a year. The catalog costs the company $0.40 to produce and $0.25 to mail. Those who do not purchase within one year will be removed from the mailing list. Historically, similar types of 'foodie' lists have generated response rates of approximately 7.5 percent—that is, 7.5 percent of customers who received an unsolicited catalog in this manner eventually made a purchase. KC Steaks has acquired a list of ‘non-foodies’ for free. The average response rate for ‘non-foodies’ customers has typically been 5 percent. KC Steaks has also made the following assumptions regarding revenue, costs and retention rates: 1) Foodie customers purchase more frequently than non-foodies. ‘Foodies’ make three purchases per year with an average order size of $50; non-foodies make one purchase annually with an average order size of $125. 2) ‘Foodies’ also tend to remain customers longer, with an annual retention rate of 70 percent versus a retention rate of 60 percent for ‘non-foodies’. 3) ‘Foodies’ purchase gourmet products regularly, they tend to purchase slightly less expensive products than their non-foodie counterparts, who view KC Steaks as special-occasion products on which to splurge. As a result, the contribution margin is assumed to be 50 % for ‘foodies’ and 60% for ‘non-foodies’. Using the assumptions that have been provided and the Strategic Planning Approach for Estimating LTV, estimate LTV for both segments by completing the Income Statement on the 'KC Steaks LTV Income Statement' tab. Afterward answer the questions on the 'KC Steaks Answers' tab. |
KC Steaks LTV Income Statement
| Assumptions | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |
| Foodies | Non-Foodies | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| List Cost | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | |
| Catalog Cost | Foodies | ||||||||||
| Shipping Cost | |||||||||||
| Annual Catalogs | # of Orders | ||||||||||
| Annual Catalog Cost | Order Size | ||||||||||
| Total Revenue | |||||||||||
| Total Cost to Target | |||||||||||
| Variable Costs | |||||||||||
| Response Rate | Annual Contribution Margin | ||||||||||
| Acquisition Cost per Customer | Customer Acquisition Cost | ||||||||||
| Orders per Year | Annual Operating Margin (OM) | ||||||||||
| Order Size | Survival Rate (Retention Rate) | ||||||||||
| Expected Value:Annual OM | |||||||||||
| Retention Rate | |||||||||||
| NPV of Expected Annual OM | |||||||||||
| Contribution Margin % | Cumulative NPV Profit to Date | ||||||||||
| Discount Rate | 10% | ||||||||||
| Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | ||
| Non-Foodies | |||||||||||
| # of Orders | |||||||||||
| Order Size | |||||||||||
| Total Revenue | |||||||||||
| Variable Costs | |||||||||||
| Annual Contribution Margin | |||||||||||
| Customer Acquisition Cost | |||||||||||
| Annual Operating Margin (OM) | |||||||||||
| Survival Rate (Retention Rate) | |||||||||||
| Expected Value:Annual OM | |||||||||||
| NPV of Expected Annual OM | |||||||||||
| Cumulative NPV Profit to Date | |||||||||||
KC Steaks Answers
| Question 1: What is LTV in Year 10 for an average 'Foodie' customer? |
| Question 2: What is LTV in Year 10 for an average 'Non-Foodie' customer? |
| Question 3: For each customer segment, how long does it take to recover the initial acquisition costs (what is the break even year?) ? |
| Question 4: What would be the effect to LTV in Year 10 of a 1% improvement in the response rate for ‘Non-Foodies’ ? |
| Question 5: What would be the effect to LTV in Year 10 of a 10% improvement in the retention rate for ‘Foodies’ ? |
Staplers LTV
| Background: Staplers Office Supply Staplers, an office supply store, is developing its strategic plan for next year. A recent segmentation analysis of their customers by the marketing team has revealed an underserved segment, Medium Size Business with 25-100 Employees. The marketing manager would like to acquire more customers in this segment, but she does not want to overpay to acquire them. Assume the following about ‘Medium Size Businesses’ and then answer the question below. Assumptions: Constant Annual Operating Profit (excluding Acquisition Costs) = $500 Constant Annual Retention Rate = 60% Constant Discount Rate = 20% |
| Question 1: What is the maximum amount that should be paid to acquire a new ‘Medium Size Business’ customer? |
Workbench List Evaluation
| Background: Workbench Home Improvment Stores Workbench, a regional home improvement store is in the midst of its planning process for next year. A recent segmentation analysis of those customers that have registered in their loyalty club revealed an important segment, Occasional Shoppers. The newly hired VP of Customer Analytics would like to further penetrate the ‘Occasional Shopper’ segment, but she wants to manage the Average Acquisition Costs per Customer. Given the availability of two possible lists of ‘Occasional Shoppers’, answer the following questions. | |||||
| Table 1: List Evaluation | |||||
| List | Costs per Name | Production and Mailing Costs per Mailing | Total Costs to Contact One Person | Expected Response Rate | Acquistion Costs per Customer |
| List 1 | $6 | $3 | 1% | ||
| List 2 | $23 | $3 | 2% | ||
| Question 1: What are the Acquisition Costs per Customer for List 1. (Show calculation) | |||||
| Question 2: What are the Acquisition Costs per Customer for List 2. (show calculation) | |||||
| Question 3: Which of the following lists should be purchased from the List Broker (company that sells lists of prospective ‘Occasional Shoppers’) to minimize the Acquisition Costs per Customer ? Explain your Answer. | |||||
