2. Main Chapter
2.1 Project Setup
The ‘CLV Model with Simulation’ worksheet illustrates a total of six simulations of the CLV model of 1,000 iterations each. The ‘CLV Model No Simulation’ worksheet describes a traditional CLV calculation using no simulation. In this section the project setup is described and includes a detailed description of the inputs, how they were obtained and what assumptions underlie their calculation, as well a description of the logic behind the implementation of the customer lifetime value formula using simulation in excel, and a description of the outputs of the model.
2.2 Inputs
The simplified mathematical algebraic formula for the CLV calculation assumes that the margin and retention rates remain constant over time. In our model we do assume that retention rates remain constant over time, even though we run different simulations for different retention rates, each of those simulations assumes a constant retention rate for all periods. The margin also remains constant over time since we don’t have a way of calculating how the margin might change over time for Netflix, but we do model the mean profit with a normal probability distribution. The idea behind the CLV formula is simple: Netflix should invest in a customer when the CLV is positive, that is, when the net present value (NPV) of future cash flows is greater than the acquisition cost per customer.
Customer Lifetime Value Mathematical Formula (simplified):
where:
M: margin per customer
D: Discount rate R: Retention rate Ao: Acquisition cost per customer In
our spreadsheet model, the inputs are in cells A4:B13. Next I will
describe how the key inputs were obtained and the assumptions underlying
the calculations.
Margin/Profit per Customer
The margin or profit per customer is the first crucial input in estimating the CLV. To calculate the mean profit per customer I first calculated the gross margin for Netflix in 2019. To calculate the gross margin I first obtained the annual revenue for 2019, then subtracted the cost of those revenues. The difference between revenue and cost is the margin on subscription revenues. Since the CLV model is at the customer level, I then divided the difference between revenue and cost by revenue to get the gross margin percentage. Gross Margin percentage is listed in cell B9. I then obtained the average monthly revenue per subscription as stated on the report which is $10.82 (listed in cell B6). Because our model is annual, we only check whether a customer left during the year but do not account for the particular time of the year the customer left. Thus, to annualize the revenue I multiply the average monthly subscription revenue by the number of purchases per year, which is 12, to get the average annual revenue per subscription. Finally, I multiply this number by the gross margin to get the Profit per Active Customer in cell B12. This mean profit per active customer per year has a normal probability distribution with mean of $49.70 and a standard deviation of 5%.
Gross Margin calculation (cell B9):
Total revenues: $20,156,447
Cost of revenues: -$12,440,213
Gross Margin: $7,716,234
Gross Margin(%): $7,716,234 / $20,156,447 = 38.38% (cell B9)
Mean Profit per Active Customer (cell B12):
Average yearly revenue per subscription (B8) * Gross Margin (B9) = Mean Profit per Active Customer $129.84 * .3828 = $49.70 (cell B12)
The revenues, cost of revenues, and average subscription revenue per customer were obtained directly from Netflix 2019 financial annual report, specifically the Consolidated
Statements of Operations listed below:
Retention rate
The second crucial input in the CLV model is the retention rate. The retention rate is the fixes probability that a customer will remain loyal at the end of the year. However, one minus the Retention rate is the probability that the customer will switch to another company, or simply cancel the service, known as churn rate. There is no publicly available data about Netflix’s retention rate, however in the past analyst have estimated that the retention rate for Netflix is probably between the range of 80% to 90%. In this model we used a @RiskSimtable (D4:I4) in cell B4 to test six different retention rates ranging from 70% to 95% and we do one simulation of 1,000 iterations each to obtain the relevant outputs.
Acquisition Cost
The third crucial input in the CLV model is the acquisition cost of a new customer. To estimate the cost to acquire a new customer I looked at the marketing expenses for 2019 which equaled $2.6B and divided it by the # of new subscribers that year. This formulation assumes that the entire marketing budget was devoted to acquiring new customers. The description of the marketing line item from the financial disclosure mentions that the “marketing expenses consist primarily of advertising expenses and certain payments made to marketing partners….advertising expenses include promotional activities such as digital and television advertising”. In addition, there were about 15 million members that were not active paying customers. As part of their acquisition strategy Netflix may subsidize usage of the streaming service for a limited period of time to get the customer to join before charging them. Thus it is a reasonable assumption that the line item can be used to calculate the acquisition cost of a new customer.
Acquisition Cost calculation (cell B10):
(in thousands)
Marketing Expenses: $2,652,462
# of New Susbscriptions: 27,831
Acquisition cost per customer: $95.30.2.3 Simulation Model Development
The model assumes a 30-year time horizon, however the profits stop if the customer switches loyalties or simply cancels the service before the end of the year. Based on the 2019 annual report we estimate the mean profit per active customer for a customer in its Nth year to be normally distributed with mean of $49.70 and standard deviation of 5%. This implies that the profits and the variability of profits remain relatively constant over time. For discounting purposes this model assumes that profits are generated at the middle of the respective years using a discount rate of 10%. This is not a crucial assumption, other assumptions on the timing of profits could be made instead. The crucial endpoint in the model is the retention rate, the probability that a customer remains loyal at the end of any given year. To see how much this key input affects the results, the model uses a RiskSimtable function in cell B4 to try the six possible retention rates shown in the retention table. This means that the number of simulations must be set to 6, one for each retention rate.
The model is straightforward. Column B uses the RAND function inside IF functions to generate a sequence of “No” values until a “Yes” is eventually generated, and then blanks are recorded every year after that, meaning that the customer has switched or cancelled the subscription and no further profits are obtained.
Column B is titled “Quits End of Year?”
Column B is titled “Quits End of Year?”The formula for the first year (cell B17) is: IF(RAND()<$B$4,“No”,“Yes”)
Cell B4 is the retention rate for that particular simulation and the logical statement tests a randomly generated number between 0.0 and 1.0 and if that number is less than the retention rate the statement “No” fills the cell to indicate that the customer did not quit at the end of the year and the calculation can continue to next year, otherwise if the randomly generated number is higher than the retention rate that means the customer churned or cancelled the service and the statement “Yes” indicates that. For later years (cells B18:B46), the formula checks whether the customer has already switched, by checking for values of “Yes” or blanks like “” that signify that the customer already switched and no more calculations are needed. However, if the value of the previous cell is still “No” then the same RAND function inside an IF statement continues to generate values and determine if the value is within the retention rate or not to determine whether the customer remains loyal or not at the end of the year.
Cells(B18:B46) = IF(OR(B17=““,B17<>”No”),““,IF(RAND()<$B$4,”No”,“Yes”))
*Notice B17 is a relative address that increases by one as the year increases by one.
Column C generates normally distributed profits, until the customer switches or cancels and then it records zeros. The normally distributed profits have a mean of $49.70 and standard deviation of 5% as mentioned in the inputs section. The ABS function is used for the standard deviation in case the mean profit is negative because the standard deviation cannot be negative.
Column D discounts each non-zero value from the middle of the corresponding year after the beginning of year one.
For example: the actual profit in year 5 is divided by (1 + Discount rate) raised to the 4.5 power.
Simulation Output
Two cells have been designated as @Risk output cells:
The Net Present Value (NPV) of profits in cell G16 =@RiskOutput(“NPV”) + SUM(D17:D46)-Acquisition_Costs
The Number of Years of Loyalty in cell G17 = @RiskOutput(“Years loyal”) + COUNTIF(B17:B46,“No”)+COUNTIF(B17:B46,“Yes”)
The NPV of profits is the sum of the discounted profits for each year the customer remains active subscriber for Netflix minus the Acquisition Cost. According to the CLV formula the acquisition cost must be deducted from the NPV of profits. The Number of Years of Loyalty is simply the sum of the years that the customer is an member of Netflix (Sum of No’s and Yes’s in Column C).
Descriptive Statistics
A table with descriptive statistics is shown in cells F21:I26 with @Risk’s RiskMean function entered in cells H21:I26 using a second argument equal to the simulation number 1 to 6.
ex: Cell H16 = @RiskMean($G\(16,F21), with G16 being the NPV Output ex: Cell I25 = @RiskMean(\)G$17,F25), with G17 being the Years Loyal Output
