單位經濟效益（LTV/CAC 與同群分析）
使用時機
當你需要判斷客戶的價值是否高於取得成本時使用：包括 CAC、毛利貢獻、留存率、LTV、LTV/CAC 比值以及 CAC 回收期（月數）。適用於 SaaS 與訂閱制商業模式、董事會審查以及需要以同群留存率網格支撐關鍵比率的盡職調查。此技能會在 Claude 應用程式中透過 openpyxl 建立一個可下載的 .xlsx 檔案；不涉及任何 Excel 增益集或 Microsoft 365 連線。
建立內容
一個包含六個工作表的活頁簿：
- 封面：標題、顏色圖例、情境選擇器、關鍵的 LTV/CAC 與回收期數據。
- 假設：藍色輸入欄位（行銷業務支出、新客戶數、ARPU、毛利率%、流失率、折現率、情境倍數）。
- 同群：留存率網格，同群在列、月份在行，包含各同群的留存百分比與營收。
- 單位經濟效益：CAC、ARPU、每位客戶貢獻、回收期。
- LTV-CAC：透過封閉公式與折現同群貢獻計算的 LTV，以及比值。
- 檢查：一致性、留存率單調性與回收期合理性標記。
建立流程
- 建立活頁簿，並依上述順序建立六個工作表。
- 在「假設」工作表中，以藍色字型列出所有輸入項目並附上單位；新增一個名為
scn的情境儲存格（1=基本, 2=樂觀, 3=悲觀）。 - 在「單位經濟效益」工作表中，根據支出與客戶輸入數據計算 CAC 與每位客戶貢獻。
- 建立同群網格：第 0 個月為 100%（或起始客戶數），並依留存率逐月遞減。
- 在每個同群的留存率網格下方新增營收與貢獻列。
- 在「LTV-CAC」工作表中以兩種方式計算 LTV：封閉公式與折現同群總和；並計算比值與回收期。
- 在「檢查」工作表中建立一致性、單調性與回收期測試。
- 以無頭模式（LibreOffice）重新計算活頁簿，使所有公式正確運算。
- 檢查無任何公式錯誤（#REF!、#DIV/0!、#VALUE!、#NAME?）；若有錯誤則修正並重複計算，直至無誤後交付。
各工作表規格
封面
- 標題儲存格、模型用途說明、建立日期。
- 顏色圖例區塊：藍色色塊標示「輸入」、黑色標示「公式」、綠色標示「跨工作表連結」。
- 情境回顯：
=CHOOSE(scn,"基本","樂觀","悲觀")，從「假設」工作表擷取當前情境。 - 關鍵輸出以綠色跨工作表連結顯示：LTV/CAC
=LTV-CAC!<比值儲存格>、CAC 回收期（月數）與折現 LTV。 - 一行備註說明活頁簿可在 Excel 中即時重新計算，並已在建立時通過無錯誤驗證。
假設
- B2 情境選擇器（藍色，1/2/3），命名為
scn。 - B5 每期行銷業務支出；B6 取得新客戶數。
- B7 每月 ARPU；B8 毛利率%。
- B9 每月流失率%（故每月留存率
=1-B9）；B10 每月折現率。 - 流失率與 ARPU 的情境倍數（基本/樂觀/悲觀）：B12 有效流失率
=CHOOSE(scn,...)，B13 有效 ARPU=CHOOSE(scn,...)。
單位經濟效益
- B3 CAC
=假設!B5/假設!B6。 - B4 有效 ARPU
=假設!B7*假設!B13。 - B5 每客戶月毛利
=B4*假設!B8。 - B6 每月留存率
=1-假設!B9*假設!B12。 - B7 CAC 回收期（月數）
=CAC/每客戶月毛利=B3/B5。若偏好考慮同群的回收期，則計算每同群累積毛利直到超過 CAC，並回報該月份索引。 - B8 客戶平均壽命（月數）
=1/(假設!B9*假設!B12)。
同群
- 第 3 列月份標題 0,1,2,...,N 橫跨各欄。
- A 列同群標籤（同群 1、同群 2、...）。
- 留存率%：第 0 個月儲存格 = 1（100%）；之後各月
=前月儲存格*'單位經濟效益'!$B$6（套用每月留存率）。同一個公式套用至整列。 - 每同群活躍客戶數：
=留存率% * 假設!$B$6。 - 每同群每月營收：
=活躍客戶數 * '單位經濟效益'!$B$4。 - 每同群每月貢獻：
=營收 * 假設!$B$8。
LTV-CAC
- B3 封閉公式 LTV
='單位經濟效益'!B4*假設!B8/('單位經濟效益'!B6 壽命流失率)即=ARPU*GM% / 流失率；儲存格中為='單位經濟效益'!B4*假設!B8/(假設!B9*假設!B12)。 - B4 折現同群 LTV：計算各月份每位客戶貢獻折現後總和，
=SUMPRODUCT(每位客戶貢獻列, 1/(1+假設!B10)^月份索引)，使用第 0 個月同群列除以起始客戶數，使其成為每客戶值。 - B5 LTV/CAC
=B4/'單位經濟效益'!B3（以折現 LTV 為主要依據）。 - B6 回收期回顯
='單位經濟效益'!B7。
檢查
- 一致性：
=IF(ABS(LTV_封閉公式-LTV_折現)/LTV_折現<=假設!tol,"通過","失敗")。 - 每個同群內留存率單調遞減：
=IF(SUMPRODUCT(--(本月儲存格>前月儲存格))=0,"通過","失敗")，確保無月份高於前月。 - 回收期為正數且在設定的上限內（藍色輸入）。
- LTV/CAC 使用與同群網格相同的 ARPU、毛利率與流失率計算。
公式與格式慣例
- 藍色字型用於輸入項（僅限假設工作表）。黑色用於工作表內公式。綠色用於跨工作表連結。
- 公式中不得出現硬編碼數字；常數一律放在假設工作表。使用絕對參照指向留存率與 ARPU 驅動變數，以便同群列能順利填滿。
- 每個同群列使用同一公式，以便跨月份複製時無需修改。
- 留存率與毛利率以百分比表示；CAC、LTV、ARPU 以貨幣格式表示；回收期與壽命以月數表示，保留一位小數。
- 命名
scn、tol、CAC 以及留存率儲存格，以便於在檢查與 LTV 公式中閱讀使用。
檢查項目
- LTV/CAC 計算一致（封閉公式與折現值在容差範圍內）。
- CAC 回收期以月數表示且為正數。
- 每個同群內的留存率單調非遞增（無月份高於前月）。
- 毛利率與留存率介於 0 與 1 之間；CAC 與 LTV 為非負值。
重新計算與驗證
寫入後，以無頭模式（LibreOffice）重新計算活頁簿，使 openpyxl 的公式文字轉換為計算值。掃描每個工作表是否有 #REF!、#DIV/0!、#VALUE!、#NAME?。請注意 LTV 與回收期會除以流失率，因此需防護流失率為 0 時產生 #DIV/0! 錯誤。修正任何有問題的公式或遺失的命名範圍，並以迴圈方式再次重新計算。僅當檢查工作表顯示所有項目均為「通過」且無任何錯誤字串時，方可交付。
需收集的輸入資料
- 同一期間的行銷業務支出與新客戶數（用於計算 CAC）。
- 每月 ARPU 與毛利率%。
- 每月流失率（或留存率）與折現率。
- 網格中要模型化的同群數與月份數。
- 流失率與 ARPU 的情境倍數（基本/樂觀/悲觀）以及容差範圍。
範例
假設：行銷業務支出 200,000，400 位新客戶，故 CAC 為 500。每月 ARPU 為 50，80% 毛利率得到每位客戶每月毛利 40，回收期 12.5 個月。每月流失率 4% 意味著平均客戶壽命為 25 個月；封閉公式 LTV 約為 1,000，折現同群 LTV 略低；LTV/CAC 比值約 2.0 倍。同群網格中每個同群以 100%、96%、92.2%、... 的留存率逐月遞減，貢獻總和即為折現 LTV。