I need #4 answered at the bottom. This is a case study regarding excel sheets and NPV's. Stuck on this an and answer to this case study would mean the world and a 5 star review

Customer Lifetime Value for Homeschoolers Segment Customer Lifetime Value for Intermittent Shoppers Segment Customer I ifetime V alue for Organization Seoment Customer Lifetime Value for Homeschoolers Segment Customer Lifetime Value for Intermittent Shoppers Segment Definition of Customer's Lifetime Value (CLV): The net present value of cash flows produced over a customer's life, i.e., purchase periods a customer is retained by the focal company. Example: Oriental Trading Company Oriental Trading Company (OTC) is the nation's largest direct merchant of value-priced party supplies, arts and crafts, toys and novelties, and a leading provider of school supplies and affordable home dcor and giftware. This company serves its customers through colorful catalogs and its website (www.orientaltrading.com), offering more than 40,000 products to meet the needs of businesses, notfor-profit organizations, individuals, schools, churches and teachers. OTC sends a catalog each month to every customer who has made a previous purchase, which results in a mailing/printing cost of $10 per year per customer. There is no formal contract between the company and customer, and the company does not know it when a customer leaves. As a result, even when a customer leaves, the company continues to mail catalogs and incurs the expense of $10/ year. There are three segments of customers: Organizations, Homeschoolers, and Intermittent. These segments vary greatly in both the frequency of purchases and basket sizes. Organizations make six purchases per year with an average order size of $200; Homeschoolers make one purchase per year with an average order size of $150; Intermittent customers make three purchases per year with an average order size of $100. Furthermore, the three segments differ in their probabilities to repurchase from one year to the next. Both Organizations and Homeschoolers have an annual retention rate of 80 percent (i.e., percentage of customers who will continue to make purchases the next year), whereas the percent (i.e., percentage of customers who will continue to make purchases the next year), whereas the retention rate of Intermittent customers is 30%. Finally, both Organizations and Homeschoolers are relatively price sensitive, purchasing products that result in OTC receiving a margin of 50%. In contrast, since Intermittent customers tend to splurge on special-occasion products, the margin earned by OTC on their purchases is 75%. Customer Lifetime Value When evaluating the CLV, cash flows must be discounted to reflect the time value of money. For the table that follows, we will use a discount rate of 10%. Furthermore, for the purposes of this assignment, we will use a time-horizon of 10 years. (However, you can see from the table on the next page, the Net Present Value (NPV) of annual profit becomes quite small by the end of this time horizon, or even slightly negative.) The table on the last page gives the CLV for each of the three segments. An Excel spreadsheet is posted on BlackBoard in order to help you see where the equations came from. Annual Sales \$ = (\# of purchases per year) * (Average Order Size) Annual Net Margin =( Annual Sales \$) * (Profit Margin) Expected Annual Profit =( Survival Rate )( Annual Net Margin ) Catalog Mailing Cost NPV Annual Profit for year t=( Expected Annual Profit in year t)/(1+DiscountRate)t In these computations, we assume that the annual net margin and the mailing cost for a year are realized at the end of the year. The NPV is computed for the beginning of the first year. For example, the net margin and the mailing cost for the first year are realized one year from the beginning of the first year and are therefore discounted by 10%. The cumulative profit NPV gives the maximum amount a firm should expend to acquire a customer. The OTC has determined that Organizations are more difficult to acquire, thus resulting in an expected acquisition cost of $500 per organization acquired. In contrast, Homeschoolers and Intermittent customers can be obtained at a cost of $200 per new customer. The acquisition cost is incurred at the beginning of the first year. Thus, for the company, it is profitable to acquire a customer if and only if the NPV of the customer is not less than the acquisition cost. Discussion Questions 1. After accounting for acquisition costs, which segment is the most profitable? Which is the least profitable? Are all of the segments worth acquiring, i.e., have a Cumulative Profit NPV which exceeds the acquisition cost? 2. If the discount rate were 20%, what happens to CLV's? 3. Suppose you had the opportunity to utilize a new recruiting tool that would enable you to capture a new customer from the Intermittent segment at a cost of $300 (rather than $200 ). Assuming this new customer would not be reachable through any other means, would this be a worthwhile investment? If so, how long would it take to recoup your $300 acquisition cost? The cumulative profit NPV gives the maximum amount a firm should expend to acquire a customer. The OTC has determined that Organizations are more difficult to acquire, thus resulting in an expected acquisition cost of $500 per organization acquired. In contrast, Homeschoolers and Intermittent customers can be obtained at a cost of $200 per new customer. The acquisition cost is incurred at the beginning of the first year. Thus, for the company, it is profitable to acquire a customer if and only if the NPV of the customer is not less than the acquisition cost. Discussion Questions 1. After accounting for acquisition costs, which segment is the most profitable? Which is the least profitable? Are all of the segments worth acquiring, i.e., have a Cumulative Profit NPV which exceeds the acquisition cost? 2. If the discount rate were 20%, what happens to CLV's? 3. Suppose you had the opportunity to utilize a new recruiting tool that would enable you to capture a new customer from the Intermittent segment at a cost of $300 (rather than $200 ). Assuming this new customer would not be reachable through any other means, would this be a worthwhile investment? If so, how long would it take to recoup your $300 acquisition cost? 4. Explain why, for the later years, the Intermittent shopper segment has a negative NPV Annual Profit. Do you have a recommendation for Oriental Trading Company as to how to avoid this situation