如何用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程序等相關功能。有興趣的朋友可以嘗試結合自己業務需要製作相應的有商業洞察力的商業智能分析儀，幫助企業創收的同時也提高自己的個人價值。

李奇：現任德勤中國數據分析團隊高級諮詢顧問，專精於企業數據分析、制定商業智能業務解決方案、軟體開發及Excel培訓等。

推薦閱讀：