can you help me solving net present value for 10 years according to this case study and calculating the customer life time value using excel

DSB 6100: Individual Assignment (CLV)

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 CLV

JGS 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-foodie customers 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 lifetimeas 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 subsequently[1].

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.

Finally, 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 as special-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 segments over a period of 10 yearsand draw possible conclusions from those estimates, using an annualdiscount rate of 10%.

Notes:

1. We do not know the number of customers on either list and so we have to estimate the 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.

wwwwwwwwwwwwwwwwwwwwwwww5. It is important to separate year of mailing catalogsfrom 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 do

You will submit some of the work in a WORD document named Individual Assignment Response Docand some of it in an Excel spreadsheet that you will create.

Here's what you need to do (following this sequence will help):

1. Compute the estimated net profitper customer of each segmentfor 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 10 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.

4. Answer some questions and report some additional information, as instructed in the pre-formatted Individual Assignment Response Doc.

5. Submit the filled Individual Assignment Response Docto Canvas. Name the document using the format: Individual Assignment Response Doc - (your full name).Also submit your Excel spreadsheet named in the format: Individual Assignment Spreadsheet - (your full name).

The following two Appendices will help you complete the assignment and so please read them carefully.

Appendix 1: 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, 116

The 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 pers 8on on this list =

($48 + $30) x 12 [Note: There was no cost to create this list]

= $936

The estimated cost of acquiring 1 customer from the list =

$936Y / 0.05Y

= $18, 720

NOTE: It is logical to consider the catalog costs for the 1^st year as part of acquisition costs because we have to wait for the entire year to be over to know how many of those targeted with the catalogs became customers. Until then, all catalog costs were incurred with the aim of converting catalog receivers to customers. However, after the first year, the same catalog costs should be treated as retention costs because those catalogs will be mailed to acquired customers only. Finally, note that acquisition cost is something that applies to the 1^st year only.

Appendix 2: Some Helpful Hints

]121. CLV is the sum of future discounted net profits.The CLV formula maps onto this defi nition 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 5^th year); (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 1^st year + Discounted net profit for the 2^nd year + 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, giv`es 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 because it is logical to do so (see "Notes" after the Appendix in the assignment instructions).

2. The CLV formula uses the form R_n - C_n for 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 R_n and a distinct number for C_nand 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 secondpart 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 100

This implies that when we multiply 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 to factor in catalog costs also. Therefore, subtracting the catalog costs from this first portion of the net profit will give us the final net profit.

-