進行情境分析：敏感度、情境比較與決策模擬

這篇要回答一個很實際的問題：小茶攤一杯 65 元，如果漲到 70 元，月淨利會變多還是變少？

Excel 在「資料」>「模擬分析」底下有情境管理員、目標搜尋、運算列表三項工具，另有規劃求解附加元件；Google 試算表沒有這個選單。所以今天全部用公式來做：SEQUENCE 加 ARRAYFORMULA 拉出敏感度表，再用下拉選單加 XLOOKUP 切換情境。

先說明一下，這個茶攤是我假設的，每個數字都是我自己設的，不是哪家店的真實資料。

先把模型縮成一格輸入、一格結果

我想先把整個生意壓成最小的樣子：賣幾杯、每杯賺多少、扣掉固定支出。參數放在 A1:B8，結果放在 B10、B11：

「參考售價」跟「參考日銷量」是指：售價定在 65 元的時候，一天大約賣 120 杯。售價離開 65 元，銷量就照「價格彈性」調整，-1.2 的意思是售價漲 1%，銷量少 1.2%。

先手算一次：26 天 × 120 杯 = 3,120 杯，每杯賺 65 − 24 = 41 元，3,120 × 41 = 127,920，扣掉固定成本 96,000，月淨利是 31,920。

B10 是預估日銷量，B11 是月淨利：

你應該會得到 B10 是 120，B11 是 31920，跟手算一樣。

單變數：售價從 50 到 80

先固定其他條件，只動售價。我想看 50、55、60……到 80 這 7 個價格，各自會賣幾杯、賺多少。

手動的話，要把 B2 改一次、抄一次 B11，改七次，很煩。這邊用 SEQUENCE 產生價格，再用 ARRAYFORMULA 一次算完整欄。

D1:F1 放標題「售價」「日銷量」「月淨利」，然後 D2 是：

E2 是日銷量：

F2 是月淨利：

來看看結果：

65 那一列跟前面手算的 31,920 一樣，這是我用來確認公式沒寫錯的方法。

把售價和月淨利兩欄畫成折線圖，會比看數字直覺：

圖：淨利最高點在 70 元附近，75 元只差一點點，50 元則掉到 7,428。

漲到 70 元只多賺 2,444 元（34,364 − 31,920），而且 70 和 75 差不到 500 元。這種曲線頂端很平的情形，代表定價在這一帶不用太糾結。

反推：要賣多少才不賠

前面是「給條件、看結果」，有時候想反過來問：每天至少要賣幾杯才不賠？

損益兩平不需要試錯，直接解：固定成本 ÷（營業天數 × 每杯毛利）。

手算是 96,000 ÷ (26 × 41) = 90.06，所以公式會得到 91。一天 90 杯還是小虧，91 杯才打平。

再來問另一種：我想每月至少賺 30,000，售價可以落在哪個範圍？這邊我把價格表拉細，K2 用 SEQUENCE(41,1,50,1) 產生 50 到 90 每 1 元一格，L 欄、M 欄的日銷量與月淨利公式跟上一節一樣。這張細表放在 K 欄以右，才不會蓋到下面龍捲風圖用的 F 到 H 欄。目標放 P1，然後：

P1 填 30000，會得到最低 64、最高 80。也就是說在這組假設下，售價落在 64 到 80 元之間，月淨利才有 30,000。

Google 另外出了一個叫「Goal Seek」的外掛，可以從「擴充功能」>「外掛程式」>「取得外掛程式」安裝，做的是類似 Excel 目標搜尋的事。我個人比較喜歡直接看整張表，因為可以同時看到下限和上限，而外掛一次只給一個答案。Excel 的規劃求解（Solver）在 Google 試算表則要另外安裝 Solver 類的外掛。

練習完成後，先把 B7 從 22 還原成 26，其餘參數維持第一節的基準：售價 65、參考售價 65、參考日銷量 120、彈性 -1.2、單杯成本 24、固定成本 96000。確認 B11 回到 31920；後面的龍捲風圖、雙變數表和情境比較都以每月 26 天計算。

龍捲風圖：哪個假設最要緊

參數這麼多，我該先把哪一個估準？做法是每個參數各自上下動 20%，看月淨利擺盪多大，再依擺盪幅度排序，這就是龍捲風圖。Google 試算表沒有原生的龍捲風圖，後面會用橫向堆疊長條圖湊出來。

A14:D14 放標題「變數」「-20% 淨利」「+20% 淨利」「影響幅度」。以「單杯成本」為例，-20% 是把成本乘 0.8：

「售價」那一列比較特別，因為售價變了，銷量也會跟著變，所以要把日銷量的算式整個帶進去：

最外面包 ROUND 是因為 0.8 這種小數乘出來會有浮點誤差。另外兩列的 -20% 公式也列在這裡：

+20% 欄把公式裡的 0.8 換成 1.2 即可。「影響幅度」是 =ABS(C15-B15)。我照影響幅度由大到小排好：

要畫圖的話，F14:H18 另外放「與基準淨利的差」：F14 填「變數」，F15:F18 填上表四個變數名稱（=A15 往下拉即可），G15 是 =B15-$B$11，H15 是 =C15-$B$11。選取 F14:H18，「插入」>「圖表」，圖表類型選「長條圖」（橫向的那種），並到「堆疊方式」選「標準」。

圖：日銷量往下猜錯 20%，月淨利少約 2.6 萬（25,584）；上下兩端的落差合計 5 萬多。售價動 20%，影響反而最小。

售價排最後有點意外，原因是 65 元離最高點 70 元很近，往上往下都不會差太多（+20% 時只少 156 元）。但這不代表可以亂定價：售價是單邊的風險，往下降價 20% 會少賺 19,448 元，只有往上漲才幾乎沒差。它只是說「在現在這個位置」，銷量估準比價格微調重要。

雙變數：售價 × 單杯成本

單變數看不到兩個東西一起動。茶葉、牛奶漲價，單杯成本會跟著上去，這時候最佳售價會不會變？

做一張矩陣：列是售價（50 到 80），欄是單杯成本（20 到 30，每次加 2）。A22 放「售價＼成本」，A23 用 SEQUENCE(7,1,50,5)，B22 用 SEQUENCE(1,6,20,2)，B23 是：

這邊的重點是 A23:A29（直的）減 B22:G22（橫的），ARRAYFORMULA 會把它們交叉成 7 × 6 的格子，不用一格一格拉。

成本 24 那一欄跟上一節的單變數表一樣，可以對一下。

數字多的時候，我會套色階看哪裡高哪裡低：

1. 選取 B23:G29。
2. 點「格式」>「條件式格式設定」，右側面板切到「色階」。
3. 最小值設淡灰色，最大值設 #5B7A52（避免紅綠對比，色盲也看得出來）。

← 左右滑動看完整的圖，點圖可放大

圖：深綠集中在成本低、售價 65 到 75 之間；右上角成本 30 配售價 50，已經是負的。

成本在 20 到 24 的時候，最佳售價是 70；成本升到 26 以上，最佳售價移到 75。也就是成本越高，越值得漲價，只是賺的錢本來就比較少。

三種情境與期望值

前面都是「一組假設」。實際上我不確定客人會不會買單，所以想設三組：悲觀、基準、樂觀，每一組不只銷量不同，成本和客人對價格的敏感度也不同。放在 A32:D36：

機率也是我猜的，加起來要是 100%。

接著算每個情境下，售價 65、70、75 各賺多少。A39:E39 放標題，A40:A42 填 65、70、75，B40 是：

E40 是期望值，也就是依機率加權平均：

期望值最高的是 70 元，但悲觀情境會小虧 970 元。75 元的期望值反而低，悲觀情境虧到快一萬。把 70 元攤開成決策樹會比較清楚：

← 左右滑動看完整的圖，點圖可放大

圖：70 元的期望值比維持 65 元多 2,223，代價是最差情境從賺 2,800 變成虧 970。

要不要漲，看你吃不吃得下那 970 元的虧損。這邊我個人傾向先漲到 70，因為悲觀情境的虧損不大，但這是我設的數字，換成你的店就要重算。

下拉選單切換情境

三欄並排很適合比較，但有時候想把整個模型「切」到某個情境，看 B10、B11 怎麼變，甚至讓圖表跟著變。做法是在 B38 放一個下拉選單：

1. 選取 B38。
2. 點「資料」>「資料驗證」，新增規則，條件選「下拉式選單」，選項填「悲觀」「基準」「樂觀」。

圖：選項的文字要跟 A32:D32 的標題完全一樣，下一步的查找才找得到。

然後把 B4、B5、B6 從手動輸入改成公式，由 XLOOKUP 到情境表找對應的值。B4 是：

B5 把 B34:D34 換成 B36:D36，B6 換成 B35:D35。如果你習慣 CHOOSE，=CHOOSE(MATCH(B38,B32:D32,0),B34,C34,D34) 也可以。

如果之後想把這個模型搬到 Excel：XLOOKUP、SEQUENCE、FILTER 只有 Microsoft 365、Excel 2021、Excel 2024 才有，Excel 2019 以前會出現 #NAME?；Excel 也不需要 ARRAYFORMULA。

選「基準」，B10 是 120、B11 是 31920；選「悲觀」，B10 變成 100、B11 變成 2800：

圖：基準情境，跟第一節手算的結果一樣。

圖：同一個售價 65 元，悲觀情境一個月只剩 2,800 元。

整個模型的流向長這樣，灰色是手動輸入，綠色是公式算出來的：

← 左右滑動看完整的圖，點圖可放大

圖：三情境比較不經過下拉選單，所以切換情境時它不會動。

要新增情境，在情境表右邊多一欄，再把 XLOOKUP 與 SUMPRODUCT 的範圍拉長。我是把這些都放同一張工作表，如果模型變大，把情境表獨立成一張工作表會比較好維護。

想把這個模型拿來套自己的店，先換掉 A1:B8 的參數和情境表的三組假設就可以了，試著用用看吧！我是喜特先生，Mr. Sheet，我們下個教學見！