如何应用Excel制作会讲故事的销售漏斗管理分析仪

此文将从销售漏斗管理分析的业务背景、Excel平台上的实施方式以及销售漏斗管理分析仪的实现过程三方面内容进行说明，希望帮助有此类业务分析需求的朋友拓展思路。

业务背景

销售漏斗是科学反映商机状态以及销售效率的一种重要的销售管理模型，此模型应用广泛，适用于多种不同类型的销售体系，其中尤其适用于关系型销售企业，也就是适用于需要长期维护客户关系的企业。销售漏斗指的是将从发现商业机会开始到最终与客户成交为止的销售过程按照不同的销售进度分为几个不同的销售阶段，每个销售阶段代表一个已经达成的某种销售进程。在销售漏斗中销售阶段的数量及设定方法依据企业的经营方式不同而不同。

比如，下图是某企业在销售管理业务中使用的销售漏斗模型。

此销售漏斗将从发现潜在商机到最后与客户成交的整个销售流程分为七种不同的销售阶段，商机数由上至下越来越少，但与此同时，商机的成功率却由上至下越来越高，就好像一个生活中使用的漏斗，当我们为漏斗注满水后，处在漏斗上部的水量最多，而越往漏斗下部则水量越少，但真正能供我们使用的只有从漏斗最底部漏出的少量的水。

销售漏斗的管理目的就是让每一个销售阶段的商机都尽可能的顺利过渡到下一个销售阶段，只有将每一个销售阶段的商机管理好才能最终实现从销售漏斗最底层获得的成交机会最多，也就是达成企业销售收入最大化的目的。销售漏斗管理的本质是企业经营方面的风险管理，它直接关系企业的盈亏状况，是企业销售环节的生命线。

了解了销售漏斗管理的目的后，我们再来了解一下销售漏斗管理分析的目的。针对销售漏斗模型的分析目的在于提前发现各销售阶段中潜在的商机风险，并通过分析结果指导销售管理人员及时做出销售策略上的调整，以期达到销售资源最优化、销售效率与商机转换率最大化的目的。销售管理分析在整个销售运营业务的多个环节中都发挥着重要作用，请参照下图了解销售管理分析所涉及到的销售运营业务环节（粉色圆圈标出的销售运营环节）。

想要做好销售漏斗管理分析我们需要在以下几个分析环节中分别解决一些针对该环节的关键问题：

分析环节1：数据获取

销售漏斗管理业务涉及的相关数据往往来自于多个不同的数据源（在下文中将把不同的数据源称为不同的数据维度），企业销售业务越复杂，参与协作的部门及关联企业就越多，所需的业务维度也就越多，在数据获取环节中，我们需要一种能够快速与不同外部数据源创建链接并保证当这些数据源的数据发生变化时能够随时与之同步的数据获取方式，这种方式应能够同时保证获取数据的时效性与准确性。除此之外，因为需要从不同数据源导入大量数据，所以需要具备能够获取百万行甚至千万行量级数据的能力。在Excel平台中，应用Power Query免费插件可以满足上述数据获取需求。

Power Query操作界面

分析环节2：数据处理

如果想要洞察到涉及多个不同数据维度的商机风险，就需要我们将获取到的多种不同维度的数据汇总到同一个多维数据模型中，并以此模型作为分析环境以便开展接下来的数据分析工作。除了创建多维数据模型外，我们还需要一种类似数据库一样的“表”数据批量处理方式，这种方式应以字段而非单元格作为数据的基本处理单位，应用此方式能够帮助我们实现灵活、高效且迅捷地处理海量数据的分析需求。在Excel平台上，应用Power Pivot免费插件可以满足创建多维数据模型的需求，应用Power Query免费插件可以满足灵活、高效地批量处理海量数据的分析需求。

Power Pivot操作界面搭建多维数据模型

分析环节3：数据分析

在此分析环节中，我们需要紧密结合业务需求将透视分析、多维分析运用到极致，从多维环境下深度透视关键指标，以完成及时洞察风险商机的目的。此时Excel提供的基本数据透视功能已经完全无法满足我们的业务需求，所以需要运用到更为强力的透视分析工具Power Pivot来实现上述两项分析需求。

应用Power Pivot实现关键指标透视分析

分析环节4：结果展现

在此分析环节中，我们需要应用到Excel基础制图以上的功能，根据业务分析实际需要，制作具有针对性的简洁、直观的自定义图表以及具备交互效果的动态图表。必要情况下，我们还要用到少量的VBA宏程序来解决实际问题。

用具备升维效果的折线图与饼图的嵌套图表来展现各时点商机的构成变化情况用动态嵌套柱形图来展现销售阶段的周变化情况

实施方式

在满足以上所有分析需求后，我们就可以在Excel平台上制作会讲故事的销售管理分析仪了，制作分析仪的目的在于有条理的、简单易懂地告诉销售管理者你手中的商机处在何种情况下、它们可能正面临何种问题、需要管理者及时做出何种响应来回避风险，达成销售目标。其实对于销售管理者而言，面对现有商机可能无法完成销售业绩的问题一般只能做出两大类举措来改善现状。第一类是命令手下销售人员找新的商机进来提高商机总量，下这种命令一般是出现了商机总量不足的情况，即便现有商机都在有条不紊的向前推进，在销售周期内也还是很难达到销售目标值时便只能通过补充新的商机进来提高商机总量的方式来应对了。

还有一类举措是命令手下销售人员尽可能快地将手头商机向前推进，下达这种命令主要是因为虽然商机总量足够大，但其中很多商机在低销售阶段停留过久，出现了商机无法在销售周期内完成的风险。在这种情况下，如果做出补充新商机的错误判断，就会更加分散销售资源，降低销售效率，造成本来能在销售周期内完成的商机无法完成的严重后果。摆在销售管理人员面前的就像是一道二选一的选择题，正确率是50%，但一旦错误，就会造成无法挽回的毁灭性后果。

所以销售管理分析仪正是帮助销售管理者提高选择题正确率而存在的，众多指标都只在为销售管理者标明一条宽广且正确的销售策略之路。

在使用销售管理分析仪之前，很多销售管理分析报表是这样的：这份报表只是将关键指标进行了罗列，虽然用不同颜色标注了重点值，但离一目了然的发现问题，并洞察问题原因的目的还相去甚远。为了解决这样的业务痛点，我将原始数据进行了再加工整理，于是便有了以下的销售管理分析仪：这份分析仪设计着眼点在故事性上，我试图用自上而下的图表排列顺序来展现由大到小的业务问题，当阅读者从上至下将所有图表读下来后，在脑中可以形成一个由现象到原因、由整体到细节的完整故事。故事脉络清晰完整，读完后阅读者便能快速理解其内容并做出响应。那么这个故事究竟是怎样的呢，接下来让我们逐章阅读一下：

序章：仪表盘展现总体趋势问题

通过分析仪上方的六张仪表盘，可以快速发现在大趋势中哪类销售角色管理的商机出现问题最大。在很多复合型业务企业，销售任务不是由单一角色完成的，而是由不同维度的不同销售角色共同完成的。

比如地域维度的销售人员主要负责按地区划分的销售片区，产品维度的销售人员主要负责自己手头负责的单类产品，而行业领域维度的销售人员主要负责自己精通的行业或者领域中的企业客户。然后阶段4以上和阶段5以上的商机总金额一般是最为关注的指标。在案例中阶段4为“明确”，即已经明确了客户的购买意向，而阶段5为投入，既公司已经决定为了能够成功与客户成交，要投入一定的销售成本了。

在案例中，销售阶段4以上的商机总金额需达到销售目标值得1.2倍以上，而销售5以上的销售目标值需达到销售目标值的1.5倍以上，才能预测在销售周期内有可能完成销售目标。所以六个仪表盘就分别代表了在不同销售维度中销售阶段4以及销售阶段5以上商机总金额与目标值之间的占比情况。我们从中可以迅速发现最有问题的销售维度在哪里。

发现大问题后，故事就算拉开了序章，接下来就是要找出问题的原因，制定解决方案环节了。在故事序章中应用到的Excel技巧为仪表盘的制作技巧及动态图表展现技巧。仪表盘是用环形图与饼图嵌套而来，动态图表是由下拉列表控件与offset公式组合应用实现的。

开展：了解各阶段商机占比

故事的开展是由表现各销售阶段商机占比情况的瀑布图展现的，通过瀑布图我们可以了解到低阶段商机与高阶段商机的占比情况，以及商机总量的数值情况。如果低阶段商机多，说明商机向高阶段转变乏力，需要销售人员积极推进，如果商机总值低，则需要补充新商机进来。此阶段的瀑布图是通过excel基本函数rept得来的。

高潮：了解单个销售阶段商机构成

故事的高潮部分是用一个动态的组合柱形图展现的。通过此图我们可以清楚地了解到需要重点关注的销售阶段是由上周的哪些销售阶段转变而来的。比如上图反映的就是当周“投入”阶段的商机就是由上周“潜在”阶段贡献81、“明确”阶段贡献91……组合而来的。了解了商机的周变化情况，就可以判断销售人员对手头商机的推进力度情况，为是否该做出让销售人员积极推动商机的决策提供了更深层的数据依据。

尾声：了解细节

故事的尾声结束在对不同销售维度的细节展现上，通过前边的内容，精明的销售管理人员已经可以大致做出大方向上的决策判断了，再通过阅读尾声的细节图表信息，销售人员便可以对某个具体销售人员做出更细致的销售指导，以便大的销售策略能够得到顺利的开展。尾声部分的内容是通过少量VBA程序、切片器、Power Pivot数据透视图表、KPI指标设定等功能实现的。

到这里一个会讲故事的销售管理分析仪就做完了，在前台展现界面的后台是应用Power Query与Power Pivot加工处理过的数据模型，它们提供了前台分析所需的底层环境，它们还保证了每次外部数据更新后分析仪中使用的数据都能得到快速地同步更新。在整个分析仪的制作过程中分别应用到了Power Query、Power Pivot、Excel 基本功能、动态及自定义图标制作技巧、简单VBA程序等相关功能。有兴趣的朋友可以尝试结合自己业务需要制作相应的有商业洞察力的商业智能分析仪，帮助企业创收的同时也提高自己的个人价值。

另外，也欢迎关注对此内容感兴趣的朋友加我QQ或微信共同交流进步。作者简介：李奇（QQ：2105780995 微信：Dio20009）

现任德勤中国数据分析团队高级咨询顾问，有5年在日工作经验及4年国内工作经验。在日时曾担任软件工程师及日本再就业支援中心的中文及Excel讲师。归国后曾先后在IBM中国担任销售管理团队数据分析项目组长及德勤北京所的数据分析高级咨询顾问，专精于企业数据分析、制定商业智能业务解决方案、软件开发及Excel培训等。

来源：电子表格应用大会、CDA数据分析师（本文已获得授权）

作者：李奇