Please answer the following questions:

What is the expected lifetime value of a customer whose initial purchase is less than $50?

What is the expected lifetime value of a customer whose initial purchase is $50 or greater?

Based on these expected lifetime values, what marketing plans might be advisable?

Do you have any suggestions for additional lifetime value analyses?

Write-up Instructions: Please label your calculations, footnote any assumptions, etc.

Page 1: Spreadsheet showing CLV calculations for

Page 2: Spreadsheet showing CLV calculations for $50+ group

Page 3: Discussion (one page maximum)

Hints: (1) When calculating the CLV, you should count the initial purchase as Year 0 and then do the calculation for 5 further years, where Year 1 is the first year that gets discounted. (2) Any acquisition cost should be included in the calculation at time 0.

Some help:

Youll need the following to calculate CLV:

Acquisition Cost

Initial Investment per customer

Yearly average number of orders per customer

Annual Development/Retention Costs

There are two ways to do the assignment

Do the calculations on an individual customer basis using:

where Q_t = the quantity purchased in period t _t = the margin on purchases in period t d^t = the discount rate (cost of capital) where d = [1+(interest raterisk factor)] D_t = costs to develop the relationship in period t R_t = costs to retain the customer in period t A = initial acquisition cost n = the number of periods

The first term in this equation captures the net revenues received from a customer discounted over time. The second term captures the ongoing costs to develop and retain the relationship discounted over time. The final term reflects the initial cost to acquire the customer.

Calculate CLV on an aggregate basis for a group of customers and then divide by the number of initial customers to get CLV.

CLV=t=1ndt(Qtt)t=1ndt(Dt+Rt)A \begin{tabular}{c|cc|c} Year 1 & & & \\ \hline Number of Orders & Frequency & Percent & Cumulative Porcent \\ \hline 0 & 611 & 13.1 & 13.1 \\ \hline 1 & 3508 & 75.3 & 88.4 \\ \hline 2 & 416 & 8.9 & 97.4 \\ \hline 3 & 94 & 2 & 99.4 \\ \hline 4 & 21 & 0.5 & 99.8 \\ \hline 5 & 7 & 0.2 & 100 \\ \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline Year 4 & & & \\ \cline { 2 - 4 } Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 3730 & 80.1 & 80.1 \\ \hline 1 & 661 & 14.2 & 94.3 \\ \hline 2 & 185 & 4 & 98.3 \\ \hline 3 & 56 & 1.2 & 99.5 \\ \hline 4 & 14 & 0.3 & 99.8 \\ \hline 5 & 9 & 0.2 & 100 \\ \hline 6 & 2 & 0 & 100 \\ \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{c|ccc|} \hline Year 2 & & & \\ \hline Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 2739 & 58.8 & 58.8 \\ \hline 1 & 1441 & 30.9 & 89.8 \\ \hline 2 & 332 & 7.1 & 96.9 \\ \hline 3 & 100 & 2.1 & 99 \\ \hline 4 & 30 & 0.6 & 99.7 \\ \hline 5 & 9 & 0.2 & 99.9 \\ \hline 6 & 3 & 0.1 & 99.9 \\ \hline 7 & 1 & 0 & 100 \\ \hline 8 & 1 & 0 & 100 \\ \hline 9 & 1 & 0 & 100 \\ \hline \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{c|ccc|} \hline Year 3 & & & \\ \hline Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 3687 & 79.2 & 79.2 \\ \hline 1 & 671 & 14.4 & 93.6 \\ \hline 2 & 207 & 4.4 & 98 \\ \hline 3 & 68 & 1.5 & 99.5 \\ \hline 4 & 19 & 0.4 & 99.9 \\ \hline 5 & 1 & 0 & 99.9 \\ \hline 6 & 2 & 0 & 100 \\ \hline 7 & 1 & 0 & 100 \\ \hline 10 & 1 & 0 & 100 \\ \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 2535 & 76.9 & 76.9 \\ \hline 1 & 548 & 16.6 & 93.5 \\ \hline 2 & 151 & 4.6 & 98.1 \\ \hline 3 & 37 & 1.1 & 99.2 \\ \hline 4 & 20 & 0.6 & 99.8 \\ \hline 5 & 3 & 0.1 & 99.9 \\ \hline 6 & 2 & 0.1 & 100 \\ \hline Total & 3296 & 100 & \\ \hline \hline & & \\ \hline \end{tabular} Example, out of the 3,296 customers with an initial order of more than $50 only 37 ordered three times in year 4. Again, number of customers. For example, out of 3,296 customers with an initial order >$50, in Year 1 only 2,875 customer reorder (can be more than one order per customer) CLV=t=1ndt(Qtt)t=1ndt(Dt+Rt)A \begin{tabular}{c|cc|c} Year 1 & & & \\ \hline Number of Orders & Frequency & Percent & Cumulative Porcent \\ \hline 0 & 611 & 13.1 & 13.1 \\ \hline 1 & 3508 & 75.3 & 88.4 \\ \hline 2 & 416 & 8.9 & 97.4 \\ \hline 3 & 94 & 2 & 99.4 \\ \hline 4 & 21 & 0.5 & 99.8 \\ \hline 5 & 7 & 0.2 & 100 \\ \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline Year 4 & & & \\ \cline { 2 - 4 } Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 3730 & 80.1 & 80.1 \\ \hline 1 & 661 & 14.2 & 94.3 \\ \hline 2 & 185 & 4 & 98.3 \\ \hline 3 & 56 & 1.2 & 99.5 \\ \hline 4 & 14 & 0.3 & 99.8 \\ \hline 5 & 9 & 0.2 & 100 \\ \hline 6 & 2 & 0 & 100 \\ \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{c|ccc|} \hline Year 2 & & & \\ \hline Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 2739 & 58.8 & 58.8 \\ \hline 1 & 1441 & 30.9 & 89.8 \\ \hline 2 & 332 & 7.1 & 96.9 \\ \hline 3 & 100 & 2.1 & 99 \\ \hline 4 & 30 & 0.6 & 99.7 \\ \hline 5 & 9 & 0.2 & 99.9 \\ \hline 6 & 3 & 0.1 & 99.9 \\ \hline 7 & 1 & 0 & 100 \\ \hline 8 & 1 & 0 & 100 \\ \hline 9 & 1 & 0 & 100 \\ \hline \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{c|ccc|} \hline Year 3 & & & \\ \hline Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 3687 & 79.2 & 79.2 \\ \hline 1 & 671 & 14.4 & 93.6 \\ \hline 2 & 207 & 4.4 & 98 \\ \hline 3 & 68 & 1.5 & 99.5 \\ \hline 4 & 19 & 0.4 & 99.9 \\ \hline 5 & 1 & 0 & 99.9 \\ \hline 6 & 2 & 0 & 100 \\ \hline 7 & 1 & 0 & 100 \\ \hline 10 & 1 & 0 & 100 \\ \hline Total & 4657 & 100 & \\ \hline \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline Number of Orders & Frequency & Percent & Cumulative Percent \\ \hline 0 & 2535 & 76.9 & 76.9 \\ \hline 1 & 548 & 16.6 & 93.5 \\ \hline 2 & 151 & 4.6 & 98.1 \\ \hline 3 & 37 & 1.1 & 99.2 \\ \hline 4 & 20 & 0.6 & 99.8 \\ \hline 5 & 3 & 0.1 & 99.9 \\ \hline 6 & 2 & 0.1 & 100 \\ \hline Total & 3296 & 100 & \\ \hline \hline & & \\ \hline \end{tabular} Example, out of the 3,296 customers with an initial order of more than $50 only 37 ordered three times in year 4. Again, number of customers. For example, out of 3,296 customers with an initial order >$50, in Year 1 only 2,875 customer reorder (can be more than one order per customer)