Excel制作动态WaterFall-Analysis-方法详解.docxVIP

Excel制作动态WaterFallAnalysis方法详解〔全〕-包含VBA代码例如

SupplyChainSOP相关工程中往往会涉及Fcst准确性分析,WaterFallAnalysis是一个很流行的分析手段。

通常Fcsttracking有两个方向，一个是销售预测者和真实销售值的比照，另外一个是给供给商的预测订单数量和实际订单数量的比照。本文介绍后者在Excel中的作法。

1-首先建立两个表格放数据，MonthlyFcst,MonthlySale,MonthlyFcst表格存放历史上每个月Next12MonthFcst,MonthlySales那么是每个月ActualSale。高级一点的这两个表格也可以Link到数据库到达自动更新的效果。

2-设计一个新Sheet如上图，行和列名分别列出月份，旁边打1到100做辅助数据。

观察WaterFall中规律有三点，

〔1〕行数和列数相等时为表格对角线的单元格，里面放销售值。

〔2〕列数比行数大时为上半局部表格，放预测值。

〔2〕列数比行数小时放空值。

有心的人可能会发现这是大学线性代数矩阵那一章节根本概念。

如果要显示不同供给商的WaterFall，在左上角加一个单元格叫Selection，里面放供给商名字给用户选择，这样就是一个动态Waterfall了。

在第一个单元格输入类似以下的公式，填充至所有，动态waterFall就大功告成了，如果你选择供给商A，waterFall就会刷新成A的，如果选择〔All〕就是所有供给商的，因为〔All〕公式中是不识别的，所以要用Vlookup把〔All〕转成*，*在sumif的条件中表示所有。

=IF(L$1$A12,,IF(L$1=$A12,SUMIF(Actual_Order!$A:$A,$B12L$2$A$1,Actual_Order!$G:$G),SUMIF(Total_Fcst!$A:$A,$B12L$2$A$1,Total_Fcst!$F:$F)))

公式中L$1$A12是判断单元格位置的，SUMIF(Actual_Order!$A:$A,$B12L$2$A$1,Actual_Order!$G:$G)是为了动态引用不同供给商。

3-加上些条件格式，如让对角线的值变红色等等，可以让分析结果更加一目了然。

4-添加双击可以DrillDown到detailData功能。

在分析的时候，经常看到一个Fcst或那么ActualOrder很异常，想查下更加DetailLevel的数据，看看什么原因。解决方法如下：

添加一张链接到Access的表格保护Detail的数据，在Excel中添加一个双击事件，把双击的单元格所对应的条件转化到Detail数据表格的筛选条件中。参考代码如下：

PrivateSubWorksheet_BeforeDoubleClick(ByValTargetAsRange,CancelAsBoolean)

IfTarget.Row=12AndTarget.Column=11AndLen(Target.Value)1Then

DimData_Month

DimETD_month

Dimcatogory

WithThisWorkbook.Worksheets(Water_Fall)

Data_Month=.Cells(Target.Row,2).Value

ETD_month=.Cells(2,Target.Column).Value

catogory=.Range(A1).Value

EndWith

WithThisWorkbook.Worksheets(Detail_Data).ListObjects(Table_Monthly_Fcst_Database.accdb).Range

.AutoFilter

IfTarget.Row=Target.ColumnThen

‘如果双击单元格在对角线，那么筛选Openorder,否那么筛选Fcst.

.AutoFilterField:=1,Criteria1:=Data_Month+0

.AutoFilterField:=2,Criteria1:=OpenPO

.AutoFilterField:=10,Criteria1:=ETD_month

.AutoFilterField:=14,Criteria1:=catogory

Else

.AutoFilterField:=1,Criteria1:=Data_Month

.AutoFilterField:=10,Criteria1:=ETD_