Reference no: EM133079343

For this assignment, let's assume the following: 

• Gross Margin for the firm is 30%. We need this to calculate the margin the firm would earn on the revenue generated from their customers.
• Assume annual marketing costs are $300,000 total.
• A customer is considered retained if they have purchased from the firm in the past year.
• The firm spent $500,000 to acquire all of these customers in 2019-2020.
• The firm's discount rate is equal to its weighted average cost of capital, which is 10%.

1. Calculate Churn in terms of customer numbers and revenue.
2. Calculate the CLV for each customer. Use 1-churn to be the retention rate.
3. Create CLV categories with cutpoints at the 25th percentile and 75th percentile. This should give you three groups (0-24.99, 25-74.99, 75 +). Calculate customer equity for these groups. What is average customer equity in each group?
4. Assume we have a Markov transition matrix that looks like this:

		High Effort		$250000				Low Effort		$50000
		Future CLV Grouping						Future CLV Grouping
		CLV Low	CLV Med	CLV High				CLV Low	CLV Med	CLV High
Current CLV grouping	CLV Low	0.65	0.25	0.1		Current CLV grouping	CLV Low	0.8	0.15	0.05
	CLV Med	0.25	0.5	0.25			CLV Med	0.2	0.6	0.2
	CLV High	0.1	0.15	0.75			CLV High	0.1	0.2	0.7

How many customers would be in the respective groups based on the above Markov matrices? Would what would the return on the marketing investment be for low effort and high effort marketing, if we base CLV on the average of the different groupings (calculated above). Should the firm invest in low effort or high effort? Explain. 

5. Create two Pivot Tables to show: a) How average CLV changes across customer frequency groups, and b) How average CLV changes across customer recency groups. (You will need to create grouping variables on frequency and recency. What would be good cut points for these data? Recall that frequency is numerical and recency relates to days since purchase, so your categories should make sense from that perspective). 

6. If you were working for the firm where this data was generated, how would you advise them to react to this information in terms of marketing activities? Provide a one-page memo to your fictitious manager outlining your rationale for how the firm should react to your analysis.