Use the Customer Lifetime Value module of Marketing Engineering for Excel, and taking into account a 15% discount rate, compute the following figures as of today (Current spreadsheet):
The lifetime value of a typical customer in each segment. (You can copy and paste the table from the results section and highlight the lifetime value of a customer in each segment)
The value, in todays dollars, of the existing customer database (after deduction of the costs of the loyalty program). In other words, if the existing customers were to be economically valued, as an asset of the company, how much would they be worth? (HINT: Calculate the CLV for each ACTIVE segment and sum them up to arrive at total value of all active customers. Dont forget to take into account the number of customers in each active segment.)
Go back to Question 1, but this time use a very severe discount factor of 40%. In other words, answer Question 1 again as if $1 a year from now were worth only 60 cents today, which gives a very short-term focus to all the financial figures and computations.
The lifetime value of a typical customer in each segment. (You can copy and paste the table from the results section and highlight the lifetime value of a customer in each segment)
The value, in todays dollars, of the existing customer database (after deduction of the costs of the loyalty program). In other words, if the existing customers were to be economically valued, as an asset of the company, how much would they be worth? (HINT: Calculate the CLV for each ACTIVE segment and sum them up to arrive at total value of all active customers. Dont forget to take into account the number of customers in each active segment.)
What key differences can you see between the scenarios in question 1 and 2? Explain.
| Segment Description | | | | | | | | | | | Segment labels and number of customers currently in each segment. | | | | | | | | | | | Gross margins relate to current period, while marketing costs are anticipated expenses for next period based on current segment membership. | | | | | | | | | | | Segments / Segment description | Number of customers | Gross margins | Marketing costs, next period | | Average Miles per Year | Passenger Yield per Mile | Passenger Yield per Year | Loyalty Program Costs | | | Platinum (top 20%) | 200 | $4,410 | $0 | | 240,000 | 1.9 | $4,560 | $150 | | | Platinum (low 80%) | 800 | $2,145 | $0 | | 135,000 | 1.7 | $2,295 | $150 | | | Gold (top 20%) | 1100 | $1,100 | $0 | | 75,000 | 1.5 | $1,125 | $25 | | | Gold (low 80%) | 4300 | $430 | $0 | | 35,000 | 1.3 | $455 | $25 | | | Silver (top 20%) | 6800 | $86 | $0 | | 8,000 | 1.2 | $96 | $10 | | | Silver (low 80%) | 26600 | $16 | $0 | | 2,400 | 1.1 | $26 | $10 | | | Inactive customers | 18200 | $2 | $0 | | - | 0.0 | $0 | $2 | | | Lost customers | 0 | $0 | $0 | | - | 0.0 | $0 | $0 | | | | | | | | | | | | | | | | | | | | | | | | | Transition Matrix | | | | | | | | | | | Transition probabilities of customers from one segment to another across periods. | | | | | | | | | | | Each line sums up to 100%. | | | | | | | | | | | The very last segment represents customers who are assumed to be lost forever. | | | | | | | | | | | Last period / Next period | Platinum (top 20%) | Platinum (low 80%) | Gold (top 20%) | Gold (low 80%) | Silver (top 20%) | Silver (low 80%) | Inactive customers | Lost customers | | | Platinum (top 20%) | 39% | 13% | 10% | 3% | 2% | 1% | 31% | 0% | | | Platinum (low 80%) | 11% | 33% | 11% | 8% | 3% | 2% | 33% | 0% | | | Gold (top 20%) | 3% | 10% | 31% | 10% | 8% | 3% | 36% | 0% | | | Gold (low 80%) | 1% | 3% | 10% | 30% | 10% | 8% | 38% | 0% | | | Silver (top 20%) | 0% | 1% | 3% | 11% | 34% | 11% | 40% | 0% | | | Silver (low 80%) | 0% | 0% | 1% | 2% | 9% | 28% | 60% | 0% | | | Inactive customers | 0% | 0% | 0% | 1% | 3% | 8% | 0% | 88% | | | Lost customers | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 100% | | | Segment Description | | | | | | | | | | | Segment labels and number of customers currently in each segment. | | | | | | | | | | | Gross margins relate to current period, while marketing costs are anticipated expenses for next period based on current segment membership. | | | | | | | | | | | Segments / Segment description | Number of customers | Gross margins | Marketing costs, next period | | Average Miles per Year | Passenger Yield per Mile | Passenger Yield per Year | Loyalty Program Costs | | | Platinum (top 20%) | 200 | $4,560 | $0 | | 240,000 | 1.9 | $4,560 | $0 | | | Platinum (low 80%) | 800 | $2,295 | $0 | | 135,000 | 1.7 | $2,295 | $0 | | | Gold (top 20%) | 1100 | $1,125 | $0 | | 75,000 | 1.5 | $1,125 | $0 | | | Gold (low 80%) | 4300 | $455 | $0 | | 35,000 | 1.3 | $455 | $0 | | | Silver (top 20%) | 6800 | $96 | $0 | | 8,000 | 1.2 | $96 | $0 | | | Silver (low 80%) | 26600 | $26 | $0 | | 2,400 | 1.1 | $26 | $0 | | | Inactive customers | 18200 | $0 | $0 | | - | 0.0 | $0 | $0 | | | Lost customers | 0 | $0 | $0 | | - | 0.0 | $0 | $0 | | | | | | | | | | | | | | | | | | | | | | | | | Transition Matrix | | | | | | | | | | | Transition probabilities of customers from one segment to another across periods. | | | | | | | | | | | Each line sums up to 100%. | | | | | | | | | | | The very last segment represents customers who are assumed to be lost forever. | | | | | | | | | | | Last period / Next period | Platinum (top 20%) | Platinum (low 80%) | Gold (top 20%) | Gold (low 80%) | Silver (top 20%) | Silver (low 80%) | Inactive customers | Lost customers | | | Platinum (top 20%) | 19% | 9% | 8% | 5% | 5% | 4% | 50% | 0% | | | Platinum (low 80%) | 8% | 23% | 8% | 6% | 2% | 1% | 52% | 0% | | | Gold (top 20%) | 2% | 7% | 22% | 7% | 6% | 2% | 54% | 0% | | | Gold (low 80%) | 1% | 2% | 7% | 22% | 7% | 6% | 55% | 0% | | | Silver (top 20%) | 0% | 1% | 2% | 8% | 24% | 8% | 57% | 0% | | | Silver (low 80%) | 0% | 0% | 1% | 2% | 9% | 26% | 62% | 0% | | | Inactive customers | 0% | 0% | 0% | 1% | 3% | 8% | 0% | 89% | | | Lost customers | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 100% | | | | Segment Description | | | | | | | | | | | Segment labels and number of customers currently in each segment. | | | | | | | | | | | Gross margins relate to current period, while marketing costs are anticipated expenses for next period based on current segment membership. | | | | | | | | | | | Segments / Segment description | Number of customers | Gross margins | Marketing costs, next period | | Average Miles per Year | Passenger Yield per Mile | Passenger Yield per Year | Loyalty Program Costs | | | Platinum (top 20%) | 200 | $4,200 | $0 | | 240,000 | 1.9 | $4,560 | $360 | | | Platinum (low 80%) | 800 | $2,070 | $0 | | 135,000 | 1.7 | $2,295 | $225 | | | Gold (top 20%) | 1100 | $1,051 | $0 | | 75,000 | 1.5 | $1,125 | $74 | | | Gold (low 80%) | 4300 | $426 | $0 | | 35,000 | 1.3 | $455 | $29 | | | Silver (top 20%) | 6800 | $81 | $0 | | 8,000 | 1.2 | $96 | $15 | | | Silver (low 80%) | 26600 | $26 | $0 | | 2,400 | 1.1 | $26 | $0 | | | Inactive customers | 18200 | $0 | $0 | | - | 0.0 | $0 | $0 | | | Lost customers | 0 | $0 | $0 | | - | 0.0 | $0 | $0 | | | | | | | | | | | | | | | | | | | | | | | | | Transition Matrix | | | | | | | | | | | Transition probabilities of customers from one segment to another across periods. | | | | | | | | | | | Each line sums up to 100%. | | | | | | | | | | | The very last segment represents customers who are assumed to be lost forever. | | | | | | | | | | | Last period / Next period | Platinum (top 20%) | Platinum (low 80%) | Gold (top 20%) | Gold (low 80%) | Silver (top 20%) | Silver (low 80%) | Inactive customers | Lost customers | | | Platinum (top 20%) | 54% | 18% | 5% | 3% | 1% | 1% | 18% | 0% | | | Platinum (low 80%) | 17% | 35% | 11% | 8% | 3% | 2% | 24% | 0% | | | Gold (top 20%) | 5% | 13% | 35% | 10% | 8% | 2% | 27% | 0% | | | Gold (low 80%) | 1% | 3% | 11% | 29% | 10% | 8% | 38% | 0% | | | Silver (top 20%) | 0% | 1% | 4% | 10% | 36% | 9% | 40% | 0% | | | Silver (low 80%) | 0% | 0% | 1% | 2% | 9% | 26% | 62% | 0% | | | Inactive customers | 0% | 0% | 0% | 1% | 3% | 8% | 0% | 89% | | | Lost customers | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 100% | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
