Module 2: Decision Lab Calculating Customer Lifetime Value
Module 2: Decision Lab - Calculate simple, net/customized, and discounted CLV for two donor types
Learning Objectives: Calculate and interpret simple, net/customized, and discounted customer lifetime value, then use those results to make a managerial decisions about donor targeting, acquisition spending, and retention strategy.
Instructions:
In this decision lab, you will evaluate two potential donor segments for the Isabella Stewart Gardner Museum. Your task is to calculate three types of customer lifetime value and then make a strategic recommendations. This assignment is designed to move you beyond definitions and into managerial decision making.
SCENARIO: Tess is the development manager for the museum and is working on a donor growth campaign. Her budget is limited, so she cannot assume that every potentially profitable donor should be pursued in the same way. She wants to compare two donor types.
The first donor type is an Emerging Arts Patron. This donor gives $300 per year and is expected to continue donating for 12 years. The museum spends $120 to acquire this donor, and ongoing retention costs are estimated at $0.03 per dollar donated.
The second donor type is an Established Community Patron. This donor gives $900 per year and is expected to continue donating for 4 years. The museum spends $500 to acquire this donor, and ongoing retention costs are estimated at $0.06 per dollar donated.
You will use the attached EXCEL file for all of your calculations. The most challenging one will be the tab marketed "Discounted CLV." You will use a discount rate of 6 percent. To understand Discount Rate, you need to understand time value of money (also called “present value”). It basically means that money obtained NOW is worth more than money obtained later. Here are some videos and resources if you need more understanding
Kahn Academy “Introduction to Present Value” https://www.khanacademy.org/economics-finance-domain/macroeconomics/monetary-system-topic/macroeconomics-interest-rates-and-the-time-value-of-money/v/introduction-to-present-value
Kahn Academy “Present value 4 (and discounted cash flow)” https://www.khanacademy.org/economics-finance-domain/core-finance/interest-tutorial/present-value/v/present-value-4-and-discounted-cash-flow
Blog post: https://corporatefinanceinstitute.com/resources/valuation/time-value-of-money/
INSERT YOUR ANSWERS TO THE FOLLOWING QUESTIONS INSIDE THE WORKSHEET TEMPLATE.
In your template responses, you will calculate the following CLV values in three TABS in the excel template:
Simple CLV: Simple CLV = Annual Gift × Years of Giving.
Report the Simple CLV for each donor and briefly explain what this number represents.
Calculate Net CLV for both donor types after accounting for acquisition and retention costs.
Use the following logic: Total Retention Cost = Annual Gift × Retention Cost per Dollar × Years of Giving.
Net or Customized CLV = Simple CLV - Acquisition Cost - Total Retention Cost.
Report the Net CLV for each donor and explain how the rankings of each donor changed, or did not change, once costs were added.
Discounted CLV: Calculate Discounted CLV for both donor types using a 6 percent discount rate.
See videos and blog above for additional help with this tab. First calculate the annual net contribution for each donor:
Annual Net Contribution = Annual Gift - (Annual Gift × Retention Cost per Dollar).
Then discount each year’s annual net contribution back to present value and subtract the acquisition cost once at the beginning of the relationship.
Report the Discounted CLV for each donor and explain why the results differ from the Simple and Net CLV calculations. You will then provide answers to 6 managerial questions
In the AI C-P-M TAB, provide the tool you used to help you, the prompts, a sample portion (not all) of the AI output, and details of how you modified the output.