excel-formula - Excel 公式 - 基于月流失率的第 N 年后的总订阅量
问题描述
一家企业在订阅模式的基础上运作，第一年每个月的预期流失率（取消）为 5%，第二年的流失率为 2.5%，因为这些客户现在已成为老客户（长期）。
推出了一项新功能或服务，预计每个月的订阅量都会增加。我需要根据前 12 个月 5% 的流失率和第 2 年 2.5% 的流失率来计算 N 年内有多少订阅。
以下是目前如何在 excel 中计算此值，但是在尝试计算 5 - 10 年影响时，这确实是一个不雅的解决方案。
https://i.stack.imgur.com/yF5Rx.png
是否有任何会计公式或数学上的东西我可以在单个单元格中计算出来？鉴于我需要为每年和时间长度设置给定的流失率。
为方便起见，假设 95% 的时间我将致力于 3 年订阅模式，并且每年都需要自己的流失率。
我想也许 exp 和减半的价值可能会起作用，但还没有找到任何东西。
解决方案
这是构建模型的一种方法。我将参考这张图片：
首先我们建立流失模型。
第 1 行：由于流失率取决于订阅年龄，因此第 1 行具有订阅年龄（以月为单位）。这仅供参考，不用于计算。
第 2 行：流失率。
第 3 行：由于流失率始终以 输入计算(1-ChurnRate)，因此将该术语称为（单月）保留率。例如，在B3公式中是=1-B2。
第 4 行：由于流失的影响在多个月内是累积的，我们定义了另一个术语“多月留存率”（缩写为 MMRR）。在第一个月，这设置为1; 在随后的几个月中，这是之前单月保留率的乘积。所以在C4公式中是=B4*B3; 在C5,=C4*C3等
第 5 行：问题中描述的模型表明新订阅的固定比率 = 100/month。在订阅费率固定的情况下，方便总结 MMRRs。所以B5我们有=B4; 在C5, =B5+C4; 在D5, C5+D4; 等等
至此，我们基本上完成了。如果您将值乘以Row 5任何固定的新订阅率（例如100/month在原始问题中），我们将得到该月的订阅者数量。但是，如果您想对新订阅数量每月变化的情况进行建模，该怎么办？就是这样 ...
基本思想是将每月订阅率乘以适合年龄的 MMRR。请注意，Row 8前 3 个月有不同数量的新订阅（100、110、97）。例如，要获得总订阅量Mar-19，我们需要
- 将
100(Jan-19) 订阅（有 2 个月的年龄）乘以 2 个月大的订阅 (0.9025) 的 MMRR， - 将
110(Feb-19) 订阅（有 1 个月的年龄）乘以 MMRR 以获得 1 个月大的订阅 (0.95)， - 将
97(Mar-19) 订阅（年龄为 0 个月）乘以 MMRR 以获得 0 个月大的订阅 (1)，并且 - 将这三种产品加在一起。
这可以通过输入D9公式在工作表上计算=B8*D4+C8*C4+D8*B4。这种类型的计算（将两个数组的成对乘积相加）通常使用 Excel 的SUMPRODUCT函数完成。这里我们需要取其中一个数组倒序，Excel不直接支持。您可以按相反的顺序输入新订阅，但这是一个丑陋的组合。幸运的是，它可以通过OFFSET(),COLUMN()和N()worksheet 函数的组合来实现（详见此处）。所需的公式B9是
=SUMPRODUCT($B4:B4,N(OFFSET(B8,0,-(COLUMN($B8:B8)-COLUMN($B8)),1,1)))
然后可以将其复制/粘贴到第 9 行的其余部分。
这种模型方法非常灵活：流失率（第 2 行）和订阅率（第 8 行）都可以每月变化；每月保留的订阅总数是自动计算的。