do it in excel

CUSTOMER LIFETIME VALUE SRENARIO - Tracy McCutcheon is a new subscription customer of The Farmer's Dog as of September 2023. - She obviously believes in the longterm health benefits for her dog. Abbey, loves the convenience, and the overall user experience-therefore will continue to be a customer. - She is an important target audience (due to the power of her WOM) so The Farmer's Dog wants to determine the CLV for a customer that fits the persona of Tracy McCutcheon. Watch this video Customer Lifetime Value PIV = Customer Lifetime Revenue Customer Acquisition Costs (CAC) Cost to Serve Watch this video Customer Lifetime Value OVERVIEW GISTOMERLIFEIMEVALUE - Customer Lifetime Value (CLV) refers to the expected financial contribution (or total profit generated) from a particular customer over the course of their entire relationship with the brand. - To estimate CLV, you first need to use past behaviors to forecast future purchases, the gross margin from these purchases, and the cost associated with servicing the customers. - Here are some of the costs associated with maintaining a customer relationship overtime: - Communication (acquisition, promotional, win-back) - Customer service - Distribution and delivery - Marketing campaigns - Answer the following questions: - How many years can we expect to do business with customer "Tracy"? - What can we expect the annual profits to be from an individual customer like Tracy? These profits are based on sales minus the costs of merchandise and the costs of serving and retaining the customer. - What is the retention rate-that is, the average percentage of customers (like Tracy) who continue to purchase from The Farmer's Dog from one time period to another? A 75% retention rate means that if we have 100 customers in the first year, we will have 75 customers at the beginning of year two