이미지 출처 | Midjourney
영업 파이프라인 예측은 영업 퍼널에 있는 거래들의 진행 상태를 바탕으로 미래 매출을 예측하는 핵심 업무 프로세스입니다. 이 모델을 활용하면 향후 매출을 예측·분석·추적할 수 있어 영업팀이 중요한 거래에 집중하는 데 큰 도움이 됩니다. 이 글에서는 Excel만으로 영업 파이프라인 예측 모델을 직접 구축하는 방법을 단계별로 소개합니다.
1단계: Excel에서 판매 데이터 준비하기
예측 모델을 구축하려면 먼저 체계적으로 정리된 판매 데이터가 필요합니다. 영업 파이프라인에는 다음과 같은 데이터가 포함되어야 합니다.
- 거래 ID: 각 거래를 식별하는 고유 번호
- 거래 금액: 해당 거래에서 발생할 것으로 예상되는 매출액
- 영업 단계: 거래의 현재 진행 상태(예: 잠재 고객 발굴, 협상 중, 제안서 전송, 성사, 실패)
- 성사 확률: 과거 데이터를 기준으로 추정한 거래 성공 가능성
- 예상 마감일: 거래가 최종 확정될 것으로 예상되는 날짜
추가로 계산할 필드:
- 전환율 기반 예상 매출: 성사 확률과 거래 금액을 반영해 산출한 예측 매출
- 누적 가중 매출: 가중 매출을 시간순으로 누적한 값
- 마감 시점 카테고리: 기간별로 거래를 분류한 그룹
- 성사/실패 여부: 거래가 성사되었는지 실패했는지 나타내는 지표
여기에 고객명, 영업 담당자, 업종, 전환율, 세부 단계 정보 등을 추가하면 더욱 정교한 분석이 가능합니다.
2단계: 예측 필드 계산하기
2-1. 예상 매출(Expected Revenue)
- F2 셀을 선택하고 아래 수식을 입력합니다.
- 수식을 아래쪽으로 드래그하여 나머지 셀에도 적용합니다.
=D2*E2
이 수식은 거래 금액에 성사 확률을 곱해 예상 매출을 계산합니다.
2-2. 누적 가중 매출(Cumulative Weighted Revenue)
- G2 셀을 선택하고 아래 수식을 입력합니다.
- 수식을 아래쪽으로 드래그하여 나머지 셀에도 적용합니다.
=SUM($F$2:F2)
이 수식은 이전 행까지의 값을 모두 더해 누적 합계를 만들어 줍니다.
2-3. 예상 마감일 카테고리(Estimated Close Date Category)
- H2 셀을 선택하고 아래 수식을 입력합니다.
- 수식을 아래쪽으로 드래그하여 나머지 셀에도 적용합니다.
=IF(E2<=TODAY()+30, "Short-Term", IF(E2<=TODAY()+90, "Medium-Term", "Long-Term"))
이 수식은 예상 마감일이 어느 기간 범위에 속하는지 판별합니다.
- 30일 이내 → 단기(Short-Term)
- 31~90일 → 중기(Medium-Term)
- 90일 초과 → 장기(Long-Term)
2-4. 성사/실패 여부(Win/Loss Status) 계산
- I2 셀을 선택하고 아래 수식을 입력합니다.
- 수식을 아래쪽으로 드래그하여 나머지 셀에도 적용합니다.
=IF(C2="Closed Won", "Won", IF(C2="Closed Lost", "Lost", "Open"))
이 수식은 영업 단계를 확인해 성사/실패 여부를 자동으로 판별합니다.
- 거래가 'Closed Won'이면 → "Won"
- 거래가 'Closed Lost'이면 → "Lost"
- 그 외의 경우 → "Open"
3단계: 피벗 테이블로 매출 예측하기
월별 매출을 전망하려면 피벗 테이블(Pivot Table)을 활용하면 됩니다.
- 데이터 범위를 선택합니다.
- 삽입 탭 >> 피벗 테이블을 선택합니다.
- 새 워크시트를 선택한 후 확인을 클릭합니다.
피벗 테이블 필드 목록에서 다음과 같이 필드를 배치합니다:
- 예상 마감일(Expected Close Date) → 행(Rows) 영역으로 드래그
- 예상 매출(Expected Revenue) → 값(Values) 영역으로 드래그
- 성사/실패 여부(Win/Loss Status) → 열(Columns) 영역으로 드래그
- 영업 단계(Sales Stage) → 필터(Filters) 영역으로 드래그
4단계: 예측 결과 시각화하기
- 피벗 테이블 데이터를 선택합니다.
- PivotTable Analyze(피벗 테이블 분석) 탭 >> PivotChart(피벗 차트)를 선택합니다.
결과 확인:
매출 분포:
- 총 예상 매출: $30,568
- 실패(Lost): $9,688
- 진행 중(Open): $10,480
- 성사(Won): $10,400
핵심 인사이트:
- 2월의 성사 매출이 가장 높습니다($10,400).
- 3월과 4월에는 진행 중인 거래가 많아 향후 성사 가능성이 높습니다.
- 6월과 7월은 매출이 낮아 파이프라인 공백이 예상됩니다.
- 차트에서 실패, 진행 중, 성사 거래가 구분되어 한눈에 확인됩니다.
5단계: 추세 분석 및 조정하기
영업 파이프라인 예측의 정확도를 점검하고, 핵심 지표를 추적하며, 실제 데이터에서 얻은 인사이트를 바탕으로 지속적으로 개선할 수 있습니다.
추적해야 할 핵심 지표:
- 파이프라인 병목 구간: 예상 마감일과 성사/실패 여부를 분석해 진행이 더딘 거래를 찾아냅니다. 60일 이상 진행 중인 거래가 많다면 파이프라인 정체 신호일 수 있습니다.
- 전환율 추세: 일정 기간 동안 진행 중 거래가 '성사'로 전환된 비율을 모니터링합니다. 영업 단계별 승률을 비교하면 취약 지점을 파악할 수 있습니다.
- 매출 예측 정확도: 예상 매출과 실제 성사 거래의 매출을 비교합니다. 실제 매출이 지속적으로 예상치에 못 미친다면 성사 확률 추정치를 재조정해야 합니다.
예측 정확도를 높이는 방법:
- 과거 성사/실패 데이터를 기반으로 전환율을 주기적으로 업데이트하면 매출 예측의 정밀도를 높일 수 있습니다.
- 마감 시점 카테고리(단기·중기·장기)를 활용하면 기간별 추세를 명확하게 파악할 수 있습니다.
- 성사/실패 비율을 꾸준히 모니터링해 영업 전략의 효과를 평가하고 파이프라인을 그에 맞게 조정합니다.
결론
위 단계를 따라 하면 Excel만으로도 파이프라인 데이터, 성사 확률, 거래 금액을 기반으로 미래 매출을 추정하는 영업 파이프라인 예측 모델을 구축할 수 있습니다. 이 모델은 미래 매출을 예측하고 파이프라인 성과를 진단해 영업 전략을 최적화하는 데 큰 도움이 됩니다. 정기적인 업데이트와 보완을 거치면 영업 중심 조직이라면 어디서든 강력한 의사결정 도구로 활용할 수 있으며, 필요에 따라 자유롭게 커스터마이징할 수도 있습니다.
지금 무료 고급 Excel 실습 문제와 해답을 받아보세요!