用Excel/PowerPivot/PowerBI 开展预测分析

预测分析的重要性

很多BI分析报告仅仅局限于对历史数据进行多维交叉，得到的只是空洞的简单报表。而财务团队通常月底做预测，年中做预测，年底做预算。那么如何将预测分析和BI结合起来，做到实时更新，第二天就能看到基于昨天数据出来的分析预测能？必然会使得接下来的计划目标制定和预算编制轻松很多。

美国管理会计师（IMA）旗下的《战略财务》杂志中的一篇文章，提到要使用Power BI，Office 365 Excel工具开展分析，并在文中应用Gartner的报告指出，预测分析能为企业的ROI投资回报率，提升20%！当然普遍适用的预测分析方案是不存在的，必须结合业务实际情况。

另外，《美国管理会计师协会（IMA）管理会计胜任能力框架》，也将预测、信息系统和软件应用作为其中重要的两项能力。

图中可以看到，财务、会计，都已经从胜任能力框架的一级分类中消失了，取而代之的就是领导力、决策、规划、运营与技术能力。

在业务财务融合的背景下，财务分析已经同业务分析深度结合，这点毫不奇怪。

其中：

预测：未来财务和运营资源及要求(例如，现金、销售、存货、人员，等等)

信息系统和软件应用：

使用技术解决问题，分析数据并提高经营业绩

使用电子表格、数据库和商业智能软件（BI）分析数据

那么，该如何利用Excel内置的BI工具，和Power BI等工具开展预测分析呢？

首先不是一开始就用工具和算法，而是深入了解业务。先定性，后定量，先理解业务增长曲线并预判未来一段时间的增长曲线形状，然后才是工具选型，算法选择以及定量分析等等。在开展定量分析时选择正确的DAX函数和算法。预测过程中通常会遇到循环引用的计算。也就是说，后年的销售要基于明年来计算，明年的销售要基于今年来计算。如果你感觉到预测像是进了个套，就是个循环，让人焦虑和无从下手，那是因为你还没有掌握一个重要的函数：Power()，当然更复杂的还有PRODUCTX()函数。Power()函数的主要参数是底数和幂数，可以开展类似“利滚利”的复利计算。如果每期对应的增长率底数不固定，这时可以采用PRODUCTX()函数。

这里给出一些开展多维交互的预测分析的建议：

• （1）选择适当的预测方法。决定预测所用算法的，是销售增长曲线的形状，决定销售增长曲线形状的，是企业所处的生命周期阶段。

同时，销售预测的方法选择还取决于预测的目的，预测的期数等因素。根据生命周期的不同阶段，判断不同的增长曲线，作为选择不同预测方法/算法的依据。

企业生命周期理论的提出

美国人伊查克.爱迪斯曾用20多年的时间研究企业如何发展、老化和衰亡。他写了《企业生命周期》 一书，试图为处于不同生命周期阶段的企业找到能够与其特点相适应、并能不断促其发展延续的特定组织结构形式。企业的生命周期曲线形状，和主营业务销售收入曲线、客户数累计增长曲线必然有所不同，但相同点是都以时间作为X轴，规模指标作为Y轴。在开展预测分析时可以考虑不同周期阶段的曲线形态作为预测的依据。

那么，如何判断处于生命周期的哪个阶段呢？一般而言，传统企业中的主营业务收入增长率超过8%以上视为成长期。

对于企业增长而言，各个不同的责任中心，产品销售收入的叠加，构成了其生命周期曲线。

导入期（试销期）： 线性增长，线性回归。 斜线型。在拟合程度非常好的情况下，得到截距和斜率。进行预测预测。

成长期：指数型增长。指数预测曲线。以(1+增长率)为底数,以向前预测的期间数为幂数，最终得到（1+增长率）的n次方，进行预测。

成熟期（上半场）：边际递减型增长曲线。以增长率为底数，以向前预测的期间数为幂数，最终1+（增长率）的n次方，进行预测。

衰退期（成熟期下半场+衰退期）：S形曲线。在拟合程度非常好的情况下（R平方接近1），得到截距和斜率。进行预测。

例如：

下图：刚刚上市的旅行自行车（橙色曲线），可以使用回归分析，来得到一条线性回归曲线。

对于已经上市多年的公路自行车（蓝色），可以使用多项式回归，来得到一条S型的预测曲线。

和Tabelau中的趋势线类似，Excel数据透视图内置的趋势线，就有多种形状对应于不同的曲线。

数据透视图中的预测分析非常简单方便，但缺点是只是输出曲线，而无法输出未来某个时点的预测数值。因此我们需要利用Power函数来建立预测的数学模型。

另外，直接采用历史销售额，会导致拟合时R平方值并不是很理想，因此可以使用移动平均来对曲线进行平滑处理。

•（2）让增长曲线平滑起来。通过移动平均对历史数据进行平滑处理，提高预测线的拟合程度（让R平方尽可能接近1）

通常情况下，基于过去3个月，得到的移动平均销售额，作为进一步开展预测的销售额平滑线。

另外一种更加灵活的方式，时也可以基于过去3个月的销售增长率，得到的移动平均增长率，作为未来增长计算的依据。

（3）让预测滚动起来。向后滚动时间跨度，作为滚动预测的增长率计算基础，从历史数据中选择合适的向后期间数(如过去3、6、9、12或者24个月的历史数据)，作为平滑的基础。

•（4）叠加更多影响因素。季节性因素、付费广告带来的流量因素会改变预测曲线的形状，应该将这些因素添加到模型当中。

例如，在不考虑付费流量，而仅靠自然传播增长的情况下，处于成熟期的产品的增长曲线通常是边际递减的，趋于水平的增长曲线。

可以在自然增长的基础上，将付费流量所带来的预测新增客户数，相互叠加，添加到预测增长的曲线中。

•（5）变预测线为预测扇型图。在Power BI中，选择适合的置信区间，必要的话，再出选择不同的期间数来滚动预测，以得到一个相对准确的预测结果。

（6）将机器学习同可视化的预测模拟相结合。

MicroLong(王丹)曾给出过一个基于微软机器学习的Power BI开展销售预测分析的案例。

可以利用SQL Server的机器学习算法，配合将Power BI的可视化，来开展更加高阶的预测分析。

上图显示了在开展饮料销售过程中，所采用的微软机器学习模型

上图利用相关性可以识别出对销售产生影响的因子，并利用敏感性分析，开展销售模拟得到预测的结果。

结论：销售预测几乎是商业智能研究的终极问题。

预测时除了熟练掌握PowerPivot DAX函数中的Power()函数和PRODUCTX()函数以外，需要将定性分析和定量分析相结合，运用多种手段平滑数据，通过滚动方法选取更加适合的数据区间，识别影响因素并开展敏感性分析，综合应用多种方法来提升销售预测的精度。

---

作者：R3eE9y2OeFcU40
来源：CSDN
原文：https://blog.csdn.net/r3ee9y2oefcu40/article/details/79887458
版权声明：本文为博主原创文章，转载请附上博文链接！