Excel商業數據分析案例：產品銷售市場選擇決策實例分析

來自專欄數據化營銷3 人贊了文章

某海產品批發商每天需要採購500斤的海產品，一直在城市的A市場銷售海，每天都能賣完，價格也基本不變，成本也相對固定為1000元，如下圖所示：

現在管理部門為了方便市民，準備在另外一個地方設置B市場，老闆僱傭了一名市場調查員對B市場做了一段時間的調研，發現新市場的該海產品的市場需求符合一定的概率分布，如下圖所示，該海產品的價格是不固定的，但是價格的平均值基本在3.75元，並且基本符合標準差為3元的正太分布規律，同時新市場的固定成本為950元。

由於人手有限，該批發商只能在一個市場銷售，老闆希望通過以上的調研數據，對未來200天各市場的銷售利潤做分析，在考慮風險的情況下合理選擇產品所在的銷售市場。

下面是具體的操作步驟：

1、建立產品市場選擇決策分析模型表格

在excel中產品市場選擇決策模型表格，分為A地和B地市場利潤分析2個板塊，其中B市場利潤分析是重點，主要有不同概率下的需求量、收益風險分析、B地價格分布、最大銷量及固定成本、隨機數產生區域這幾個板塊構成，以上的數據在上面的需求分析中都有講到。具體如下圖所示：

第二步：根據需求量概率分布生成需求量隨機數，根據價格正太分布產生隨機價格數。

1、根據不同概率下的需求量來隨機生成不同天數的市場需求量

在【數據】菜單欄下選擇「數據分析」工具，在「數據分析」工具中選擇「隨機數發生器」，在「隨機數發生器」中具體設置和結果如下圖所示。

這一步的目的是在市場研究的前提下，通過有條件限制的情況下，隨機生成市場的需求量。雖然是隨機的，但是是基於條件的，因此這些隨機數其實是在模擬真實的發生場景的。

2、根據B地價格分布特點隨機生成對應的市場價格

在【數據】菜單欄下選擇「數據分析」工具，在「數據分析」工具中選擇「隨機數發生器」，在「隨機數發生器」中具體設置和結果如下圖所示。

同樣的，這一步是在條件限制情況下，模擬真實的市場價格。

3、銷量公式設計

由於每天的最大進貨量是500斤，所以批發商最大的市場銷量是500斤，如果市場需求是大於500斤，那銷量只能為500斤；如果市場需求量小於500斤，那銷量就是市場需求量；在excel中，用的是MIN函數來表示二者對比之後的最小值，所以銷量的公司如下：

在D22中輸入=MIN(C22,$B$19)，並複製公式到D23:D221即可，其中$B$19是，最大銷量所在單元格，完成後得到下圖：

4、每天收益公式設計

我們知道收益=銷量*價格-成本，所以在這裡每天收益的計算公司是：D22*E22-$C$19，並且複製到剩餘的單元格，即可得到如下圖所示的結果。

5、A與B市場收益分析表中的公式設計

（1）A地收益的計算公司是銷量*價格-減去成本，因此在每天收益的單元格中輸入B3*C3-D3即可，結果如下圖所示：

（2）B地的收益計算公司如下圖所示：

其中求平均值、方差、標準差的函數都是相對來說比較好理解的，MAX()和MIN()函數分別是用來求所有數的最大值和最小值，countif()函數是用來計算滿足某個條件的個數，T和ΔX都是比較常用的統計數值，round()函數是求取數值小數點的個數的。

B地收益最終計算結果如下圖所示：

第三步：決策思路和決策選擇

在做決策分析的時候，只要B地市場的平均收益（804.82）>A地市場的平均收益（625），就可以選擇在B地市場銷售，但是這樣決策存在很多的風險。

從上圖中可以看出B地的最小收益是-305.43，並且虧損的概率是4.5%，虧損的概率較低，這時決策的風險不大。

但是還必須考慮B地的收益情況，比如最小收益，以及大於A地市場收益的概率，從上圖可知，B地市場收益大於A地市場收益的概率82.5%，這時候市場還是存在風險的，為此還要進一步研究B地的收益。

假設分析的置信度是95%，在此基礎上計算出B地收益的置信區間為758.62-851.02，最小收益大於A地收益的625，這時基本確定可以選擇B地市場，這時候風險就小的很多。

我整理了一份《Excel商業數據分析和挖掘案例實踐120例》，將會在我的小密圈裡面逐步分享出來，學會用最簡單、便捷的工具成為數據達人。

推薦閱讀：