This assignment is based on a short case on Jupiter Gourmet Seafood (JGS) and involves estimating CLV for two customer segments. Please read the case and associated notes carefully.Jupiter Gourmet Seafood (JGS): Estimating CLVJGS is an online specialty retailer of gourmet seafood that offers premium seafood at high prices. JGS ships its products to addresses in the U.S. in sealed containers filled with dry ice to preserve the seafood's freshness. JGS caters to two primary use cases: gourmet food enthusiasts ("foodies") who purchase regularly for their own consumption and to serve to guests at parties, and less frequent purchasers ("non-foodies") who visit the company's website primarily to purchase gifts around the holidays.JGS is contemplating some expenditure to acquire new customers. They are thinking of purchasing a list of foodies from a list broker at $180 per name. The company is confident that based on its market knowledge, it would be able to put together a list of potential non-foodiecustomers also but will not have to pay for that list. The company plans to send each foodie and non-foodie customer a full-color catalog every month of a year, over the customer's lifetime as defined by the Marketing Manager. JGS' Marketing Manager has decided that once the NPV of annual profit (i.e., the discounted value of a specific year's net profit estimated from a customer) dips below $120, complete attrition has effectively occurred, i.e., customer lifetime has ended, and that customer will not receive catalogs subsequently1. Each catalog costs the company $48 to produce and $30 to mail. The company's marketing team estimates that such lists generally result in a first-time response rate of approximately 7.5% for foodie customers, i.e., 7.5% of foodie customers who receive an unsolicited catalog in this manner will make a purchase in the first year. Non-foodie customers are estimated to exhibit a first-time response rate of 5%. Also, foodie customers purchase more frequently than non-foodies, making three purchases a year, with an average order size of $6, 000; non-foodies make one large gift purchase annually with an average order size of $15, 000. Foodies also tend to remain customers longer, with an estimated annual retention rate (the percentage of customers who will continue to make purchases the next year) of 70% versus retention rate of 60% for non-foodies. Lastly, because they view gourmet products like JGS' as staples, foodie customers tend to purchase slightly less expensive products than their non-foodie counterparts, who view JGS as1Note that this caveat implies that a specific customer who does not make a purchase in a year will no longer receive catalogs, because based on the caveat, that customer's lifetime will have ended (because the net profit from that customer in that year, and hence its NPV, will be 0, i.e., less than $120). In fact, for a customer to continue to received a catalog, a minimum annual purchase has to be made such that the NPV of the net profit from that purchase does not fall below $120.

2 | D S B6 1 0 0I n d i v i d u a lA s s i g n m e n tspecial-occasion products on which to splurge. This behavior results in a margin to JGS of 60% for no-foodies versus a margin of 50% for foodies. [Note: This margin is based on cost of goods sold (COGS) and regular operational expenses and does not include catalog-related costs.]JGS has approached you to help them estimate the CLV for each customer of the two segmentsover a period of 10 years and draw possible conclusions from those estimates, using an annual discount rate of 10%. Notes:1. We do not know the number of customers on either list and so we have to estimated CLV for a typical individual customer on each list. 2. Note that this problem involves customer acquisition cost that needs to be factored into the computation of CLV. I give you a head start by computing this cost (see Appendix at the end of this document).3. Assume that JGS acquires the list at the very beginning of a certain year (e.g., January 1, 2018) and starts mailing catalogs to people on these lists in the very first month of that year and continues to mail a catalog to qualified customers every month from then on. Note that given what the case states, every person on either list must receive a catalog every month for at least the first year. 4. In the first year of catalog mailing, we will assume that if a person on a mailing list becomes a customer, that person purchases JGS products, at the estimated rate stated in the case, before the end of that year. 5. It is important to separate year of mailing catalogs from the year used in CLV computations. We will count the year of mailing catalogs usually, i.e., starting with 1 as the first year. However, for the purpose of computing CLV, the first year might be set to "0" or "1" as a matter of choice, but the relevant formula changes slightly, as stated in Chapter 2.2. You can set the first year of mailing catalogs as "0" or "1" and use the relevant formula, as you wish.

What you need to doYou will submit some of the work in a WORD document titled Individual Assignment Response Doc and some of it as an Excel spreadsheet. Here's what you need to do (following this sequence will help):

1. Compute the estimated net profit per customer of each segment for the first and second year of mailing catalogs. Clearly show your work, including any formula you use. Use the pre-formatted Individual Assignment Response Doc (attached with these instructions) to report this information.

2. Compute the estimated CLV per customer of each segment over the first two years of mailing catalogs. Clearly show your work, including any formula you use. Use the pre-formatted Individual Assignment Response Doc (attached with these instructions) to report this information.

3. Compute the estimated CLV for a customer from each segment for each year over a period of 10 years, using an Excel spreadsheet. Clearly label all variables and show all formulas. Please use a single spreadsheet for the computations of both customer segments but separate these computations clearly. You can format the spreadsheet in your own way, but at a minimum, it must show the following for each segment:

(a) The estimated annual net profit for each of the 20 years.

(b) The NPV of the estimated annual net profit for each of the 10 years.

(c) The estimated CLV for a typical customer for each segment over the 10 years. Thus, the spreadsheet should show the CLV for the first year, the CLV over the first 2 years, the CLV over the first 3 years, etc., all the way to the CLV over 10 years. For instance, if I want to know what the estimated CLV is for a customer who stays a customer for 5 years or 7 years, I should be able to tell that from the spreadsheet

2. Please remember to separate the computations for the two segments.

Appendix: Computation of Customer Acquisition Cost (CAC)

At the beginning of the very first year, the FOODIE list is purchased, and the nON-FOODIE list is prepared.

Soon after, the catalogs go out once to each customer in every month and an estimated 7.5% on the FOODIE list and 5% on the NON-FOODIE list respond by making purchases at the estimated rates stated in the case. This information helps us compute the CAC as follows.Let there be X names on the FOODIE list.

The total cost of reaching 1 person on this list in this first year =Cost of buying each name on the list + Total annual cost of producing and mailing catalogs to each person on the list = $180 + ($48 + $30) x 12..................

Remember the per name cost of buying the list= $1, 116The cost of reaching all X persons on the list =$1, 116X

Now, an estimated 7.5% of these X persons, i.e., 0.075X persons, would become customers, i.e., would be acquired.

The cost of acquiring 1 customer from the list =$1, 116X / 0.075X= $14, 880

Let there be Y persons on the NON-FOODIE list. The annual cost of reaching 1 person on this list =($48 + $30) x 12

[Note: There was no cost to create this list]= $936The estimated cost of acquiring 1 customer from the list =$936Y / 0.05Y = $18, 720

DSB 6100: Additional notes on the Individual Assignment1.CLV is the sum of future discounted net profits. The CLV formula maps onto this definition nicely. If we unpack this definition and the formula, we will see that to compute CLV, we need to:

(a) compute the expected net profit for each point in time (e.g., for the 5thyear);

(b) discount it; and

(c) add these numbers up to that point in time (i.e., over 5 years). The CLV formular has the following structure:Discounted net profit for the 1styear + Discounted net profit for the 2ndyear + Discounted net profit for the 3rd year + .....(for whatever time period you have chosen)The CLV formula has the required ingredients built into it. The numerator, including the probability bit, gives us the expected net profit. The denominator takes care of the discounting. The represents the summation.Also note that the Acquisition Cost (AC) is something that we incur in the first year only and does not apply to the subsequent years.Further, I included the catalog production and mailing costs in my computation of the AC. An alternative approach is to think of the $180 spent on acquiring each foodie customer to be strictly the AC (this would be 0 for a non-foodie customer) and the catalog costs for the first year as retention cost in that year, something I did not do.

2.The CLV formula uses the form Rn- Cnfor the raw net profit, i.e., before factoring in the probability. This might lead you to expect that you must have or arrive at a distinct number for Rnand a distinct number for Cnand you can subtract the latter from the former to arrive at the raw net profit. As is often the situation, the information provided in a case might not make this possible and we might have to go about the calculation differently. Based on the information provided in the present case, you will need to compute the final net profit for a point in time, i.e., a specific year, in two parts. In the first part, you should apply the profit margin to the revenue and in the second part you should subtract the catalog costs from the resulting number. To understand this better, consider the following form:Profit margin = (Profit after deducting COGS & operational costs/Revenue) x 100This implies that when we multiple the Revenue by the profit margin we get the $ amount of that portion of the net profit based on COGS and operational costs. Now, COGS and operational costs are not the only costs in the present situation. We have catalog costs to worry about. Therefore, subtracting the catalog costs from this first portion of the net profit will give us the final net profit.