SHOW WORK IN EXCEL PLEASE

M Steak, an online specialty retailer that serves the southern California region market with its gourmet black-haired wagyu steak from its website. The steak is priced at more than $300 per pound. As a result, M Steak caters to only gourmet food enthusiasts (foodies) who purchase regularly for their own consumption, and other customers (non-foodies) who purchase less frequently as holiday gifts. M steak is contemplating the purchase of a customer list from a list broker for $10 per name. The company will send each person on the list a full-color catalog, which costs the company $10 to produce and $1 to mail every month for a year. Generally, such a customer list results in a response rate of approximately 15% for foodies; that is, 15% of the customers who receive an unsolicited catalog in this manner will make a purchase. On the other hand, the response rate for non-foodies is at 25%. After a year, those who do not purchase the product will be removed from the mailing list while the remaining customers continue to receive catalogs every month. Their foodie customers will make on average four purchases per year with an average order size of $200. Foodies usually have an annual retention rate (the percentage of customers who will continue to make purchase the next year) of 85%. The non-foodies will only make two gift purchase per year with an average order size of $250. The retention rate for non-foodies is only 65%. M Steak ships its product in a sealed containers filled with dry ice to preserve the meat freshness. It is able to make 50% margin from its foodie customers and 60% from its non-foodie customers. M Steak assumes a 9% discount rate for its time value of the money.

Questions:

M Steak considers that the customer lifetime has ended when the NPV of the annual net profit dips below $50. How much is the customer lifetime value for each type of customers?

M Steak believes that the customer lifetime is 6 years for foodies and 3 years for non-foodies. If that is the case, should M Steak purchase the customer list from the list broker, and why?