ANSWER ON EXCEL SHEET AND SHOW FORMULAS PLESAE ONLY USE A SPREADSHEET AND SHOW THE FORMULAS SHOW ONE SPREADSHEET WITH THE NUMBERS AND ONE USING THE SHOW FORMULA FUNCTION TO SHOW THE FORMULAS USED AND PASTE SCREENSHOTS OF SPREADSHEETS. BELOW I POSTED A SPREADSHEET FOR REFERENCE. USE EXACT CELLS AS BOTTOM. THANK YOU.

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 for 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.):