Excel에서 신뢰할 수 있는 영업 파이프라인 예측 생성 – 단계별 가이드

판매 파이프라인 예측은 기업이 판매 유입 경로의 거래 상태를 기반으로 미래 수익을 예측하는 중요한 프로세스입니다. 이 모델은 향후 판매를 예측, 분석 및 추적하여 판매에 집중하는 데 도움이 됩니다.  이 글에서는 Excel에서 영업 파이프라인 예측 모델을 구축하는 방법을 보여드리겠습니다.

1단계:Excel에서 데이터 준비

예측 모델을 구축하려면 구조화된 판매 데이터를 보장해야 합니다. 판매 파이프라인에는 다음 데이터가 포함되어야 합니다.

• 거래 ID :각 거래의 고유 식별자
• 거래 가치 :거래로 인한 예상 수익입니다.
• 판매 단계 :거래 상태(예:전망, 협상, 제안서 전송, 성사 완료, 실패 마감)
• 폐쇄 가능성 :과거 데이터를 바탕으로 거래 성공 가능성을 추정합니다.
• 예상 마감일 :거래가 완료될 예상 날짜입니다.

계산된 필드:

• 전환율에 따른 예상 수익 :확률과 거래 가치를 고려하여 수익을 예측합니다.
• 누적 가중 수익 :시간 경과에 따른 가중 수익 누계
• 예상 마감일 카테고리 :기간을 기준으로 거래를 분류합니다.
• 승/패 상태 :거래의 성사 여부를 나타냅니다.

고객 이름, 영업 담당자, 업종, 전환율, 다양한 단계 등을 포함할 수도 있습니다.

2단계:예측 필드 계산

2.1. 예상 수익:

• F2 셀을 선택하고 다음 수식을 삽입하세요.
• 수식을 아래로 드래그하여 나머지 셀에 적용하세요.

수식:

이 공식은 거래 가치에 예상 수익을 반환할 확률을 곱합니다.

2.2. 누적 가중 수익:

• G2 셀을 선택하고 다음 수식을 삽입하세요.
• 수식을 아래로 드래그하여 나머지 셀에 적용하세요.

수식:

이 공식을 사용하면 각 행이 이전의 모든 값을 합산하여 누적 합계를 생성합니다.

3.2. 예상 마감일 카테고리:

• H2 셀을 선택하고 다음 수식을 삽입하세요.
• 수식을 아래로 드래그하여 나머지 셀에 적용하세요.

수식:

=IF(E2<=TODAY()+30, "Short-Term", IF(E2<=TODAY()+90, "Medium-Term", "Long-Term"))

이 공식은 예상 마감 날짜가 다음 범위 내에 있는지 확인합니다.

• 30일>> 단기
• 31-90일>> 중기
• 90일 이상>> 장기

4.2. 승패 상태 계산:

• I2 셀을 선택하고 다음 수식을 삽입하세요.
• 수식을 아래로 드래그하여 나머지 셀에 적용하세요.

수식:

=IF(C2="Closed Won", "Won", IF(C2="Closed Lost", "Lost", "Open"))

이 공식은 판매 단계를 확인하여 승패 상태를 확인합니다.

• 거래가 성사된 경우 '성공'
• 거래가 종료된 경우 '실패'입니다.
• 다른 상태는 '공개'입니다.

3단계. 피벗 테이블을 삽입하여 판매 수익 예측

월별 수익을 예측하려면 피벗 테이블을 사용할 수 있습니다.

• 데이터 범위를 선택하세요.
• 삽입으로 이동 탭>> 피벗 테이블 선택 .
• 새 워크시트를 선택합니다.>> 확인을 클릭하세요.

피벗 테이블 필드 목록 :

• 예상 종료 날짜를 드래그하세요. 행 섹션.
• 예상 수익을 드래그하세요. 값에서 섹션.
• 승/패 상태를 드래그하세요. 열 섹션.
• 판매 단계를 드래그하세요. 필터에서 섹션.

4단계:예측 시각화

• 피벗 테이블 데이터를 선택하세요.
• 피벗 테이블 분석으로 이동 탭>> 피벗 차트 선택 .

출력:

수익 분배:

• 총 예상 수익: $30,568
• 분실: $9,688
• 열기: $10,480
• 승리: $10,400

주요 통계:

•   2월의 수익이 가장 높았습니다($10,400).
•   3월과 4월에는 진행 중인 거래가 많아 향후 종료를 의미합니다.
•   6월과 7월은 낮은 수익을 보여 파이프라인 격차를 나타냅니다.
•   차트에서는 실패, 미결, 성사 거래를 구분합니다.

5단계:추세 분석 및 조정

판매 파이프라인 예측의 정확성을 분석하고, 주요 지표를 추적하고, 실제 데이터 통찰력을 바탕으로 조정할 수 있습니다.

추적할 주요 통계:

• 파이프라인 병목 현상 :  예상 마감 날짜와 성패 상태를 분석하여 느리게 진행되는 거래를 식별합니다. 60일 이상 진행 중인 거래는 둔화를 의미할 수 있습니다.
• 전환율 추세 :시간이 지남에 따라 개시에서 마감으로 전환되는 거래의 비율을 모니터링합니다. 다양한 영업 단계의 성공률을 비교하여 약점을 파악하세요.
• 수익 예측 정확도 :예상 수익과 성사된 거래의 실제 수익을 비교합니다. 실제 수익이 지속적으로 부족할 경우 확률 추정치를 조정해야 할 수도 있습니다.

예측 정확도를 높이려면:

• 과거의 승패 데이터를 기반으로 정기적으로 전환율을 업데이트하여 수익 예측을 개선할 수 있습니다.
• 마감 날짜 카테고리(단기, 중기, 장기)를 사용하여 다양한 기간의 추세를 이해할 수 있습니다.
• 승패 비율을 면밀히 모니터링하여 영업 전략의 효율성을 평가하고 이에 따라 파이프라인을 조정합니다.

결론

위 단계를 수행하면 Excel에서 기능적 판매 파이프라인 예측 모델을 구축하여 파이프라인 데이터, 전환 확률 및 거래 가치를 기반으로 향후 판매를 예측할 수 있습니다. 이 모델은 미래 가치를 예측하고 파이프라인 성과를 식별하여 판매 전략을 최적화하는 데 도움이 됩니다. 정기적인 업데이트와 개선을 통해 영업 중심 조직의 의사 결정을 위한 강력한 도구가 될 수 있습니다. 특정 요구 사항에 따라 모델에 추가 사용자 정의를 적용할 수 있습니다.