Suppose that in addition to the membership fee, the company can also profit from the delivery fee and the commission fee. For orders made at regular restaurants, the commission rate is about 10%. For the selected restaurants, the commission rate is 3% when the 25%-off discount is applied, but it becomes 10% if the discount is not applied. Furthermore, for every delivery, the average cost of delivery is about $15. Lastly, the yearly interest rate is 5%. Problem 1 - CLV analysis For the CLV analysis, the time unit is every year. Please complete the following tasks by filling out the corresponding tables in the Excel file Assignment 1.xlsx": 1. What is the CLV of each type of customers for the first five years? Please enter your answer in each cell of the table in the worksheet "CLV. If the numbers are not directly given in the above description, you will need to provide the formula used to calculate them, so that we know how you come up with the numbers. 2. What is the CLV for each type of customers if we take the horizon to infinity? Please provide your answers in the last column of the same table in the worksheet CLV. 3. Suppose Foodpanda has spent $50 millions on mass advertising to acquire customers and eventually converts 70 thousand new customers. What is the average customer acquisition cost (CAC)? Starting from which year, Foodpanda can profit from the regular customer? Please provide your answers in the same worksheet. Background Suppose you are a marketing analyst at the online food delivery company, Foodpanda.1 You are asked to evaluate the lifetime value of customers. The company adopts a tiered pricing structure for its service. For customers signing up with the PandaPro program, they need to pay HK$99 per month. The key benefits include free delivery for the first 5 orders each month, and 25% off from selected restaurants for orders greater than $120. For regular membership, which is free, customers on average pay $20 for each delivery. After some preliminary analysis on the data, you have found the basic purchasing patterns of these two groups of customers. PandaPro Customers On average, the customers make 5.8 orders per month. Among these monthly orders, an average of 1.5 orders are from the selected restaurants eligible for the 25%-off discount. The average spending per order is $165. The remaining 4.3 orders come from regular restaurants, with an average spending of $90 per order. The retention rate of these customers is about 80% every year. Regular Customers On average, the customers make 2.6 orders per month. Among these monthly orders, an average of 0.5 order is from the selected restaurants. The average amount of spending for these orders is $135. The remaining 2.1 orders are from regular restaurants, with an average amount of $85 per order. The retention rate is about 60% every year. Calculating Customer Lifetime Value (CLV) 1. Please complete the following two tables with the information described in the assignment. PandaPro Customers Year 1 Year 2 Year 3 Year 4 Year 5 Infinite Horizon CLV A. B. C. D E F. G. Commission profit from regular restaurants Commission profit from selected restaurants with 25% off Profit from delivery fee Profit from membership fee Survival rate Total discounted profit Cumulative profits per customer Regular Customers Year 1 Year 2 Year 3 Year 4 Year 5 Infinite Horizon CLV A. B. C. D. E. F. Profit from regular restaurants Profit from selected restaurants Profit from delivery fee Survival rate Total profit Cumulative profits per customer 2. 3. Suppose Foodpanda has spent $50 millions on mass advertising to acquire customers and eventually converts 70 thousand new customers. What is the average customer acquisition cost (CAC)? Starting from which year, Foodpanda can profit from the regular customer? CAC: Year of profit: Suppose that in addition to the membership fee, the company can also profit from the delivery fee and the commission fee. For orders made at regular restaurants, the commission rate is about 10%. For the selected restaurants, the commission rate is 3% when the 25%-off discount is applied, but it becomes 10% if the discount is not applied. Furthermore, for every delivery, the average cost of delivery is about $15. Lastly, the yearly interest rate is 5%. Problem 1 - CLV analysis For the CLV analysis, the time unit is every year. Please complete the following tasks by filling out the corresponding tables in the Excel file Assignment 1.xlsx": 1. What is the CLV of each type of customers for the first five years? Please enter your answer in each cell of the table in the worksheet "CLV. If the numbers are not directly given in the above description, you will need to provide the formula used to calculate them, so that we know how you come up with the numbers. 2. What is the CLV for each type of customers if we take the horizon to infinity? Please provide your answers in the last column of the same table in the worksheet CLV. 3. Suppose Foodpanda has spent $50 millions on mass advertising to acquire customers and eventually converts 70 thousand new customers. What is the average customer acquisition cost (CAC)? Starting from which year, Foodpanda can profit from the regular customer? Please provide your answers in the same worksheet. Background Suppose you are a marketing analyst at the online food delivery company, Foodpanda.1 You are asked to evaluate the lifetime value of customers. The company adopts a tiered pricing structure for its service. For customers signing up with the PandaPro program, they need to pay HK$99 per month. The key benefits include free delivery for the first 5 orders each month, and 25% off from selected restaurants for orders greater than $120. For regular membership, which is free, customers on average pay $20 for each delivery. After some preliminary analysis on the data, you have found the basic purchasing patterns of these two groups of customers. PandaPro Customers On average, the customers make 5.8 orders per month. Among these monthly orders, an average of 1.5 orders are from the selected restaurants eligible for the 25%-off discount. The average spending per order is $165. The remaining 4.3 orders come from regular restaurants, with an average spending of $90 per order. The retention rate of these customers is about 80% every year. Regular Customers On average, the customers make 2.6 orders per month. Among these monthly orders, an average of 0.5 order is from the selected restaurants. The average amount of spending for these orders is $135. The remaining 2.1 orders are from regular restaurants, with an average amount of $85 per order. The retention rate is about 60% every year. Calculating Customer Lifetime Value (CLV) 1. Please complete the following two tables with the information described in the assignment. PandaPro Customers Year 1 Year 2 Year 3 Year 4 Year 5 Infinite Horizon CLV A. B. C. D E F. G. Commission profit from regular restaurants Commission profit from selected restaurants with 25% off Profit from delivery fee Profit from membership fee Survival rate Total discounted profit Cumulative profits per customer Regular Customers Year 1 Year 2 Year 3 Year 4 Year 5 Infinite Horizon CLV A. B. C. D. E. F. Profit from regular restaurants Profit from selected restaurants Profit from delivery fee Survival rate Total profit Cumulative profits per customer 2. 3. Suppose Foodpanda has spent $50 millions on mass advertising to acquire customers and eventually converts 70 thousand new customers. What is the average customer acquisition cost (CAC)? Starting from which year, Foodpanda can profit from the regular customer? CAC: Year of profit
