스프레드시트에서 영업 수수료를 계산하는 방법 (구간별 요율 및 분할)
스프레드시트에서 sales commission을 정확하게 계산하는 것은 결국 네 가지 결정으로 귀결됩니다. 구간(tier)이 progressive인가요, flat인가요? 그리고 요율은 어떻게 조회하나요? 그 다음, 공동 딜은 어떻게 배분하며, clawback은 어디에 적용하나요? 첫 번째 단계를 놓치면 그 이후의 모든 숫자가 틀어지게 됩니다.
산식 자체는 어렵지 않습니다. 어려운 이유는 그 규칙들이 다른 사람이 작성한 계획서 문서에 들어있기 때문입니다. 스프레드시트는 동료가 검증할 수 있는 형태로 이 규칙들을 구현해내야 합니다.
이 가이드에서는 이 작업이 스프레드시트를 망가뜨리는 이유, 사람들이 사용하는 세 가지 접근 방식, 그리고 계획이 변경될 때 모델이 버티지 못하는 지점을 다룹니다. 이는 데이터 워크플로우에 대한 설명일 뿐, 급여나 법률 자문이 아니므로 실제 결과는 계획을 담당하는 사람과 확인하시기 바랍니다.
sales commission 계산이 스프레드시트를 망가뜨리는 이유
첫 번째 문제는 "구간별(tiered)"이라는 말이 두 가지 서로 다른 의미를 가질 수 있으며, 계획서 문서에는 어떤 의미인지 명시되어 있지 않은 경우가 많다는 점입니다.
In a flat tier plan, reaching a band applies that band's rate to the whole amount. In a progressive tier plan, each portion of the amount earns the rate of the band it falls into, the way income tax brackets work. On $120,000 of bookings across bands at 5%, 7% and 9%, those two readings differ by thousands of dollars.
두 번째 문제는 딜 데이터가 오랫동안 단일 행으로 유지되지 않는다는 점입니다. 공동 딜은 두 개의 행이 되고, accelerator는 기간 중간에 요율을 변경하며, 환불은 지급액의 일부를 취소하고, cap은 총액을 제한합니다.
세 번째 문제는 감사 가능성(auditability)입니다. 수수료는 수령하는 사람에게 설명이 가능해야 합니다. 6개의 중첩된 IF 문이 들어 있는 단일 셀은 설명이 불가능하며, 대부분의 모델이 결국 이러한 형태로 작성됩니다.
반올림 오차는 조용히 누적됩니다. 최종 지급 시점에 한 번만 반올림하지 않고 모든 중간 단계마다 반올림을 하면 행 수가 늘어날수록 오차가 커져 결국 급여 대장과 일치하지 않게 됩니다.
이로 인해 발생하는 비용
신속하게 해결할 수 없는 분쟁. 영업 담당자(rep)가 수치에 의문을 제기할 때, 딜에서 지급에 이르는 경로를 보여줄 수 있어야 합니다. 중첩된 수식은 말로 설명하기 어렵기 때문에, 결국 모델을 처음부터 다시 만드는 상황에 이르게 됩니다.
설명할 수 없는 sales commission 수치는 다음 분기에 또다시 의구심을 낳을 것입니다.
매년 반복되는 모델 재구축. 요율, 구간, accelerator는 매년 변경되며 때로는 담당자별로 달라지기도 합니다. 수식 내부에 요율을 하드코딩한 모델은 설정 변경만으로는 해결되지 않고 수식 자체를 새로 작성해야 합니다.
조정(Reconciliation)의 번거로움. 급여는 센트 단위까지 정확해야 합니다. 계산 중간에 반올림을 하는 모델은 수백 개의 행 전체에서 미세한 차이를 발생시키며, 그 원인을 찾는 데 처음 모델을 만드는 것보다 더 많은 시간이 걸립니다.
사람들이 시도하는 임시방편들
옵션 1: 수식에서 요율 분리하기
구간과 요율을 작은 테이블에 넣은 다음, 요율을 하드코딩하는 대신 조회하여 가져옵니다. 테이블이 오름차순으로 정렬되어 있다면, 범위 조회(range lookup) 옵션이 TRUE로 설정된 VLOOKUP을 통해 값이 속한 구간을 찾을 수 있습니다.
XLOOKUP도 "정확히 일치하거나 다음으로 작은 항목"이라는 명시적인 일치 모드를 사용하여 동일한 작업을 수행하므로, 6개월 후에 다시 보아도 이해하기 쉽습니다. 로직이 실제로 짧은 조건 체인으로 구성된 경우, 가독성 측면에서 IFS가 중첩된 IF 문보다 훨씬 낫습니다.
이는 가장 가치 있는 단일 변화입니다. 내년도 계획이 변경되더라도 수식을 다시 작성할 필요 없이 테이블만 수정하면 되기 때문입니다. 이 방법은 flat tier 문제를 완전히 해결하지만, progressive tier 문제는 전혀 해결하지 못합니다.
옵션 2: progressive tier를 올바르게 계산하기
progressive 계획에서 수수료는 각 구간에 해당하는 금액에 해당 구간의 요율을 곱한 값들의 합계입니다. 구간당 하나의 행을 두고 그 안에 포함되는 딜의 비율을 보여주는 보조 테이블을 만들면, 이 과정을 시각화하고 검증할 수 있습니다.
단일 셀에서 이를 계산하고 싶다면, 구간 임계값과 연속된 요율 간의 차이에 대해 SUMPRODUCT를 사용하면 동일한 결과를 얻을 수 있습니다. 어떤 방식을 선택하든 보조 테이블은 어딘가에 남겨두는 것이 좋습니다. 이견을 제기하는 영업 담당자에게 보여주어야 할 자료이기 때문입니다.
ROUND는 중간 단계가 아닌 최종 지급액에 단 한 번만 적용하십시오. 이 접근 방식의 한계는 유지보수입니다. 구간이 변경될 때마다 요율 테이블뿐만 아니라 보조 테이블의 구조도 함께 수정해야 합니다.
옵션 3: split, cap, clawback을 원장(ledger) 행으로 처리하기
기존의 딜 행을 직접 수정하고 싶은 유혹을 뿌리치십시오. 대신 모든 이벤트를 고유한 행으로 기록하고 유형(원래 실적, split 배분, accelerator 조정, cap 차감, clawback)을 지정하십시오.
이렇게 하면 split은 백분율 합계가 100%가 되어야 하는 두 개의 배분 행이 되며, 이 합계를 검증함으로써 가장 흔한 오류를 잡아낼 수 있습니다. 환불은 발생한 기간의 날짜가 지정된 음수(-) 행이 되므로, 이전 기간의 명세서를 그대로 유지할 수 있습니다.
이를 통해 한 줄씩 검증할 수 있는 모델이 완성되며, 이것이 바로 핵심입니다. 다만 행 수가 4배로 늘어나고, 파일을 다루는 모든 사람이 규칙을 준수해야 합니다. 이 작업의 기반이 되는 딜 데이터를 준비하는 방법은 CRM 내보내기 데이터를 파이프라인 보고서로 변환하는 방법 가이드에서 다루고 있습니다.
공통의 한계. 세 가지 방식 모두 해당 기간 동안 계획이 안정적으로 유지된다고 가정합니다. 하지만 실제로는 연중 변경 사항, 일회성 보장, 담당자별 예외 사항 등이 이메일로 전달되며, 이러한 사항들은 아무도 문서화하지 않은 채 수동으로 수정되곤 합니다.
Powerdrill Bloom으로 sales commission을 계산하는 방법
1단계: 딜 데이터 및 요율 테이블 업로드하기
종결된 딜(closed-deal) 내보내기 파일과 계획의 요율 테이블을 함께 업로드합니다. Powerdrill Bloom이 두 데이터를 모두 분석하므로, 누락된 소유자, 빈 금액, 합계가 100%가 되지 않는 split 백분율 등의 문제가 지급액을 계산하기 전에 먼저 드러납니다.
2단계: 자연어로 계획 규칙 설명하기
계획을 직접 구축하는 대신 말로 설명해 보세요. 구간이 progressive 방식임을 명시하고, 구간과 요율을 제공하며, accelerator 임계값과 cap을 지정합니다.
그런 다음 동일한 단계에서 검증을 요청하십시오. 어떤 딜의 split 합계가 100%가 되지 않는지, 어떤 담당자가 기간 중간에 accelerator 임계값을 초과했는지 물어보세요. 그리고 어떤 환불 건이 원래의 딜과 다른 기간에 속해 있는지도 질문해 보십시오.
3단계: 차트, 보고서 또는 프레젠테이션 내보내기
딜에서 지급에 이르는 경로를 보여주는 담당자별 명세서, 할당량 대비 달성률 차트 또는 재무팀용 요약 보고서를 추출합니다.
매 분기마다 모델을 새로 만드는 것보다 이 방식이 더 나은 이유
| 수동 방식 | Powerdrill Bloom | |
|---|---|---|
| 새로운 연도 계획 요율 | 테이블 수정 후 수식 재검증 | 새로운 구간 및 요율 입력 |
| Progressive 대 flat tier | 보조 구조 재구축 | 계획에 사용되는 방식 지정 |
| 합산되지 않는 split 백분율 | 수동 검증 열 추가 | 검증에 실패한 딜 조회 요청 |
| 담당자에게 수치 설명하기 | 수식 경로 재구성 | 딜에서 지급까지의 상세 내역 요청 |
마지막 행이야말로 실질적인 시간을 절약해 주는 부분입니다. 수수료 관련 업무에서 가장 많은 노력이 들어가는 부분은 계산이 아니라 설명이며, 중첩된 수식은 이러한 설명을 불가능하게 만듭니다.
자주 하는 실수들
progressive 계획에서 전체 금액에 단일 요율을 적용하는 것. 이는 이 분야에서 가장 비용이 많이 드는 오류이며, 항상 실적이 가장 좋은 사람들에게 과다 지급되거나 과소 지급되는 결과를 초래합니다.
수식 내부에 요율을 하드코딩하는 것. 이는 한 해 동안만 유효하며, 내년에 계획이 변경될 때 수식을 새로 작성해야 하는 원인이 됩니다. 요율은 재무팀에 전달할 수 있는 테이블 형태로 관리하십시오.
모든 단계에서 반올림하는 것. 반올림은 최종 지급 시점에 한 번만 하십시오. 중간 단계의 반올림은 오차를 발생시켜 급여 대장과 일치하지 않게 만듭니다.
환불 처리를 위해 기존 행을 수정하는 것. 이는 이미 합의된 이전 명세서를 망가뜨립니다. 환불이 발생한 기간의 날짜로 음수(-) 행을 추가하십시오.
split 백분율의 합계가 100%가 되어야 함을 잊는 것. 각각 60%씩 두 번 배분하면 수수료의 120%가 지급되지만, 시트상에서는 전혀 이상해 보이지 않습니다.
계획 규칙을 이메일에만 남겨두는 것. 이메일 스레드에만 규칙이 존재하는 sales commission 모델은 검증하거나 인수인계할 수 없습니다. 규칙을 워크북에 직접 기록해 두십시오.
기간 정의를 혼용하는 것. 딜 종결일, 인보이스 발행일, 대금 수령일은 각각 다른 결과를 만들어냅니다. 하나를 선택하여 기록하고 모든 행에 동일하게 적용하십시오. 이는 예산 대비 실적 보고서를 작성할 때와 동일한 원칙입니다.
결론
계획이 progressive인지 flat인지 결정하고, 요율을 테이블로 이동하고, 구간을 명확하게 계산하고, split, cap, clawback을 별도의 행으로 기록하십시오. 이러한 구조는 감사와 계획 변경에도 무너지지 않고 버틸 수 있습니다. sales commission 모델은 다른 사람이 이를 쉽게 이해하고 따라갈 수 있는지에 따라 평가됩니다.
많은 비용이 드는 이유는 계획이 바뀔 때마다 모델을 재구축해야 하고, 그 이후에 설명하는 과정이 필요하기 때문입니다. 매 분기마다 이러한 작업으로 시간을 보내고 있다면, 딜 내보내기 파일과 요율 테이블을 활용해 Powerdrill Bloom을 사용해 보세요. 또한 스프레드시트에서 고객 획득 비용(CAC)을 계산하는 방법 가이드와 Excel AI assistant 및 AI financial analysis 페이지도 참고해 보시기 바랍니다.
자주 묻는 질문
flat과 progressive sales commission 구간(tier)의 차이점은 무엇인가요?
flat tier는 특정 구간에 도달하면 전체 금액에 하나의 요율을 적용합니다. 반면 progressive tier는 소득세 구간과 마찬가지로 각 구간에 해당하는 금액 부분에만 해당 구간의 요율을 적용합니다.
중첩된 IF 문 없이 수수료 요율을 조회하려면 어떻게 해야 하나요?
정렬된 테이블에 구간과 요율을 넣은 다음, 유사 일치(approximate matching)를 사용하는 VLOOKUP 또는 정확히 일치하거나 다음으로 작은 항목으로 설정된 XLOOKUP을 사용하십시오. 두 방법 모두 수식을 건드리지 않고도 요율을 변경할 수 있게 해줍니다.
공동 딜(shared deal)은 어떻게 처리해야 하나요?
담당자별로 명시적인 백분율이 포함된 배분 행을 하나씩 기록하고, 백분율 합계가 100%가 되는지 검증하는 단계를 추가하십시오. 대신 기존의 딜 행을 직접 수정하면 split 내역을 검증하는 것이 불가능해집니다.
clawback과 환불은 어디에 기록해야 하나요?
환불이 발생한 기간에 원래의 딜을 참조하는 음수(-) 행으로 기록하십시오. 기존 행을 소급하여 수정하면 이미 합의되고 지급이 완료된 명세서가 변경됩니다.
수치는 언제 반올림해야 하나요?
최종 지급액 단계에서 단 한 번만 반올림해야 합니다. 중간 단계에서 반올림을 하면 여러 행에 걸쳐 오차가 발생하며, 이는 수수료 모델이 급여 대장과 일치하지 않게 되는 가장 흔한 원인입니다.