단위 경제성 (LTV/CAC 및 코호트)
사용 시기
고객을 획득하는 데 드는 비용보다 고객이 더 가치 있는지 알아야 할 때 사용합니다: CAC, 총이익 기여도, 유지율, LTV, LTV/CAC 비율, CAC 회수 기간(월). 코호트 유지율 표가 핵심 비율을 뒷받침하는 SaaS 및 구독 비즈니스, 이사회 검토, 실사에 적합합니다. 이 스킬은 openpyxl을 사용하여 Claude 앱에서 다운로드 가능한 .xlsx를 구축합니다. Excel 추가 기능이나 Microsoft 365 연결을 사용하지 않습니다.
구축 내용
여섯 개의 탭이 있는 워크북:
- 표지: 제목, 색상 범례, 시나리오 선택기, 주요 LTV/CAC 및 회수 기간.
- 가정: 파란색 입력 (S&M 지출, 신규 고객, ARPU, 총이익률 %, 이탈률, 할인율, 시나리오 승수).
- 코호트: 유지율 표, 행 방향으로 코호트, 열 방향으로 월, 유지율 % 및 코호트별 수익.
- 단위 경제성: CAC, ARPU, 고객당 기여도, 회수 기간.
- LTV-CAC: 단순 공식 및 할인된 코호트 기여도에 의한 LTV와 비율.
- 검증: 일관성, 단조 유지율, 회수 기간 이상 플래그.
구축 워크플로
- 워크북을 만들고 위 순서대로 여섯 개의 탭을 생성합니다.
- 가정 탭에 모든 입력 값을 파란색으로 단위와 함께 배치하고, 시나리오 셀(1=기본, 2=강세, 3=약세)을 추가하고
scn으로 이름을 지정합니다. - 단위 경제성 탭에 지출 및 고객 입력으로부터 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으로 이름 지정. - 기간당 S&M 지출 B5; 획득한 신규 고객 B6.
- 월별 ARPU B7; 총이익률 % B8.
- 월별 이탈률 % B9 (따라서 월별 유지율
=1-B9); 월별 할인율 B10. - 이탈률 및 ARPU에 대한 시나리오 승수 (기본/강세/약세): 활성 이탈률
=CHOOSE(scn,...)in B12, 활성 ARPU=CHOOSE(scn,...)in B13.
단위 경제성
- CAC B3
=Assumptions!B5/Assumptions!B6. - 유효 ARPU B4
=Assumptions!B7*Assumptions!B13. - 고객-월당 총이익 B5
=B4*Assumptions!B8. - 월별 유지율 B6
=1-Assumptions!B9*Assumptions!B12. - CAC 회수 기간(월) B7
=CAC/고객-월당 총이익=B3/B5. 코호트 인식 회수 기간을 선호하는 경우, CAC를 초과할 때까지 코호트 월별 누적 총이익을 계산하고 해당 월 인덱스를 보고합니다. - 평균 고객 수명(월) B8
=1/(Assumptions!B9*Assumptions!B12).
코호트
- 3행 월 헤더 0,1,2,...,N 열에 걸쳐.
- A열 코호트 레이블 (코호트 1, 코호트 2, ...).
- 유지율 %: 0개월 셀 = 1 (100%); 이후 각 월
=이전_월_셀*'Unit Economics'!$B$6(월별 유지율 적용). 행 전체에 걸쳐 일관된 수식. - 코호트별 활성 고객:
=유지율% * Assumptions!$B$6. - 코호트-월별 수익:
=활성 고객 * 'Unit Economics'!$B$4. - 코호트-월별 기여도:
=수익 * Assumptions!$B$8.
LTV-CAC
- LTV 폐쇄형 B3
='Unit Economics'!B4*Assumptions!B8/('Unit Economics'!B6 수명 이탈률)즉=ARPU*GM% / 이탈률; 셀='Unit Economics'!B4*Assumptions!B8/(Assumptions!B9*Assumptions!B12). - LTV 할인-코호트 B4: 할인된 고객당 기여도의 월별 합계,
=SUMPRODUCT(고객당_기여도_행, 1/(1+Assumptions!B10)^월_인덱스)시작 고객 수로 나눈 0개월 코호트 행을 사용하여 고객당 기준. - LTV/CAC B5
=B4/'Unit Economics'!B3(할인된 LTV를 기본으로 사용). - 회수 기간 에코 B6
='Unit Economics'!B7.
검증
- 일관성:
=IF(ABS(LTV_폐쇄형-LTV_할인)/LTV_할인<=Assumptions!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에 대한 시나리오 승수 (기본/강세/약세) 및 허용 오차 범위.
예시
가상: S&M 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로 합산됩니다.