Using the excel document:

*Please show formulas in addition to answers*

- Final Exam: TBD - One case study on customer lifetime value, similar to the example problem described below. - Answer all the questions and show your work in an Excel sheet. Submit the Excel file with answers by the due time. No late submission. Sample Final - EBZ 3030 The Company Store, a specialty retailer that features home fashions, bed and bath items, uses a full-color catalog to cater to frequent buyers who purchase regularly for their own needs and occasional buyers who purchase less frequently as holiday gifts. The Company Store is considering the purchase of a customer list from a list broker for $2 per name. The company will send each person on the list a full-color catalog, which costs the company $1 to produce and $0.50 to mail every month for a year. Generally, such a customer list results in a response rate of approximately 12% for frequent buyers, that is, 12% of the customers who receive an unsolicited catalog in this manner will make a purchase. On the other hand, the response rate for occasional buyers is only at 8%. After a year, those who do not make any purchase will be removed from the mailing list while the remaining customers continue to receive catalogs every month. The frequent buyers will make on average three purchases per year with an average order size of $100. Frequent buyers usually have an annual retention rate (the percentage of customers who will continue to make purchase the next year) of 80%. The occasional buyers will only make one gift purchase per year with an average order size of $240. The retention rate fon occasional buyers is only 70%. The Company Store is able to make 60% margin from its frequent buyers and 50% from its occasional buyers. A 10% discount rate is assumed for the time value of the money. It also assumes that when the NPV of expected annual net profit dips below $10, the customer lifetime has ended. Please answer the following questions using an Excel spreadsheet (clearly write down your answer to each of the following four questions in your spreadsheet.): 1. How much is the acquisition cost for each type of customer? Acquisition cost = total marketing costs/\# customers acquired; Y ear 1's marketing costs/\# new customers acquired in Year 1. 2. How long will it take The Company Store to break even for each type of customer? For each type (either frequent or occasional customers) of customers, find which year has NPV of net profit >=0. 3. How much is the customer lifetime value for each type of customer? Find the latest year when NPV >=$10. 4. The Company Store believes that the customer lifetime is 2 years for both types of customers. If that is the case, should the Company Store purchase the customer list from the list broker? - Final Exam: TBD - One case study on customer lifetime value, similar to the example problem described below. - Answer all the questions and show your work in an Excel sheet. Submit the Excel file with answers by the due time. No late submission. Sample Final - EBZ 3030 The Company Store, a specialty retailer that features home fashions, bed and bath items, uses a full-color catalog to cater to frequent buyers who purchase regularly for their own needs and occasional buyers who purchase less frequently as holiday gifts. The Company Store is considering the purchase of a customer list from a list broker for $2 per name. The company will send each person on the list a full-color catalog, which costs the company $1 to produce and $0.50 to mail every month for a year. Generally, such a customer list results in a response rate of approximately 12% for frequent buyers, that is, 12% of the customers who receive an unsolicited catalog in this manner will make a purchase. On the other hand, the response rate for occasional buyers is only at 8%. After a year, those who do not make any purchase will be removed from the mailing list while the remaining customers continue to receive catalogs every month. The frequent buyers will make on average three purchases per year with an average order size of $100. Frequent buyers usually have an annual retention rate (the percentage of customers who will continue to make purchase the next year) of 80%. The occasional buyers will only make one gift purchase per year with an average order size of $240. The retention rate fon occasional buyers is only 70%. The Company Store is able to make 60% margin from its frequent buyers and 50% from its occasional buyers. A 10% discount rate is assumed for the time value of the money. It also assumes that when the NPV of expected annual net profit dips below $10, the customer lifetime has ended. Please answer the following questions using an Excel spreadsheet (clearly write down your answer to each of the following four questions in your spreadsheet.): 1. How much is the acquisition cost for each type of customer? Acquisition cost = total marketing costs/\# customers acquired; Y ear 1's marketing costs/\# new customers acquired in Year 1. 2. How long will it take The Company Store to break even for each type of customer? For each type (either frequent or occasional customers) of customers, find which year has NPV of net profit >=0. 3. How much is the customer lifetime value for each type of customer? Find the latest year when NPV >=$10. 4. The Company Store believes that the customer lifetime is 2 years for both types of customers. If that is the case, should the Company Store purchase the customer list from the list broker