Hình ảnh bởi Editor | Midjourney
Dự báo phễu bán hàng là một quy trình quan trọng cho doanh nghiệp để dự đoán doanh thu tương lai dựa trên trạng thái của các giao dịch trong phễu bán hàng. Mô hình này giúp dự đoán, phân tích và theo dõi doanh số tương lai, từ đó tập trung vào bán hàng. Trong bài viết này, chúng ta sẽ chỉ cách xây dựng mô hình dự báo phễu bán hàng trong Excel.
Bước 1: Chuẩn bị Dữ liệu trong Excel
Để xây dựng mô hình dự báo, bạn phải đảm bảo dữ liệu bán hàng có cấu trúc. Phễu bán hàng của bạn nên bao gồm các dữ liệu sau.
- ID Giao dịch: Mã định danh duy nhất cho mỗi giao dịch.
- Giá trị giao dịch: Doanh thu ước tính từ giao dịch.
- Giai đoạn bán hàng: Trạng thái của giao dịch (ví dụ: Tìm kiếm khách hàng, Đàm phán, Đã gửi đề xuất, Đã thắng, Đã mất).
- Xác suất thành giao: Khả năng ước tính thành công của giao dịch dựa trên dữ liệu lịch sử.
- Ngày đóng dự kiến: Ngày dự kiến khi giao dịch sẽ được hoàn tất.
Trường tính toán:
- Doanh thu kỳ vọng dựa trên tỷ lệ chuyển đổi: Dự báo doanh thu xem xét xác suất và giá trị giao dịch.
- Doanh thu trọng số tích lũy: Tổng chạy của doanh thu trọng số theo thời gian.
- Danh mục Ngày đóng ước tính: Phân loại giao dịch dựa trên khung thời gian.
- Trạng thái Thắng/Thua: Cho biết giao dịch được thắng hay thua.
Bạn cũng có thể bao gồm tên khách hàng, nhân viên bán hàng, ngành nghề, tỷ lệ chuyển đổi, các giai đoạn khác nhau, v.v.
Bước 2: Tính toán Các Trường Dự báo
2.1. Doanh thu Kỳ vọng:
- Chọn ô F2 và chèn công thức sau.
- Kéo công thức xuống để áp dụng cho các ô còn lại.
Công thức:
Công thức này nhân giá trị giao dịch với xác suất để trả về doanh thu kỳ vọng.
2.2. Doanh thu Trọng số Tích lũy:
- Chọn ô G2 và chèn công thức sau.
- Kéo công thức xuống để áp dụng cho các ô còn lại.
Công thức:
Công thức này đảm bảo mỗi hàng tính tổng tất cả các giá trị trước đó, tạo thành tổng tích lũy.
3.2. Danh mục Ngày đóng ước tính:
- Chọn ô H2 và chèn công thức sau.
- Kéo công thức xuống để áp dụng cho các ô còn lại.
Công thức:
=IF(E2<=TODAY()+30, "Ngắn hạn", IF(E2<=TODAY()+90, "Trung hạn", "Dài hạn"))
Công thức này kiểm tra xem Ngày đóng dự kiến có nằm trong các khoảng sau không.
- 30 ngày >> Ngắn hạn
- 31-90 ngày >> Trung hạn
- Hơn 90 ngày >> Dài hạn
4.2. Tính toán Trạng thái Thắng/Thua:
- Chọn ô I2 và chèn công thức sau.
- Kéo công thức xuống để áp dụng cho các ô còn lại.
Công thức:
=IF(C2="Đã thắng", "Thắng", IF(C2="Đã thua", "Thua", "Đang mở"))
Công thức này kiểm tra Giai đoạn bán hàng để xác định trạng thái Thắng/Thua.
- "Thắng" nếu giao dịch là Đã thắng.
- "Thua" nếu giao dịch là Đã thua.
- Các trạng thái khác sẽ là "Đang mở".
Bước 3. Chèn Bảng Pivot để Dự báo Doanh thu Bán hàng
Để dự báo doanh thu theo tháng, bạn có thể sử dụng Bảng Pivot.
- Chọn phạm vi dữ liệu của bạn.
- Vào tab Chèn >> chọn Bảng Pivot.
- Chọn Sheet mới >> nhấp OK
Trong Danh sách Trường Bảng Pivot:
- Kéo Ngày đóng dự kiến vào phần Hàng.
- Kéo Doanh thu kỳ vọng vào phần Giá trị.
- Kéo Trạng thái Thắng/Thua vào phần Cột.
- Kéo Giai đoạn bán hàng vào phần Bộ lọc.
Bước 4: Trực quan hóa Dự báo
- Chọn dữ liệu bảng pivot.
- Vào tab Phân tích Bảng Pivot >> chọn Biểu đồ Pivot.
Đầu ra:
Phân bố Doanh thu:
- Tổng Doanh thu Kỳ vọng: $30,568
- Đã thua: $9,688
- Đang mở: $10,480
- Đã thắng: $10,400
Thông tin chi tiết quan trọng:
- Tháng 2 có Doanh thu Thắng cao nhất ($10,400).
- Tháng 3 & 4 có nhiều giao dịch đang mở, cho thấy khả năng đóng giao dịch trong tương lai.
- Tháng 6 & 7 cho thấy doanh thu thấp, báo hiệu khoảng trống trong phễu.
- Biểu đồ tách biệt các giao dịch Thua, Đang mở và Thắng.
Bước 5: Phân tích Xu hướng & Điều chỉnh
Bạn có thể phân tích độ chính xác của dự báo phễu bán hàng, theo dõi các chỉ số quan trọng và điều chỉnh dựa trên thông tin thực tế.
Thông tin chi tiết cần theo dõi:
- Nút thắt cổ chai trong phễu: Phân tích Ngày đóng dự kiến và Trạng thái Thắng/Thua để xác định các giao dịch di chuyển chậm. Các giao dịch vẫn đang mở sau 60 ngày có thể cho thấy sự chậm trễ.
- Xu hướng Tỷ lệ chuyển đổi: Theo dõi tỷ lệ phần trăm các giao dịch chuyển từ đang mở sang Đã thắng theo thời gian. So sánh tỷ lệ thắng qua các giai đoạn bán hàng khác nhau để xác định điểm yếu.
- Độ chính xác Dự báo Doanh thu: So sánh Doanh thu Kỳ vọng với Doanh thu Thực tế từ Giao dịch Thắng. Nếu doanh thu thực tế thường xuyên thiếu hụt, các ước tính xác suất có thể cần điều chỉnh.
Để Cải thiện Độ chính xác Dự báo:
- Bạn có thể cập nhật tỷ lệ chuyển đổi thường xuyên dựa trên dữ liệu Thắng/Thua lịch sử để tinh chỉnh dự báo doanh thu.
- Bạn có thể sử dụng Danh mục Ngày đóng (Ngắn hạn, Trung hạn, Dài hạn) để hiểu xu hướng trong các khung thời gian khác nhau.
- Theo dõi sát sao tỷ lệ Thắng/Thua để đánh giá hiệu quả của chiến lược bán hàng và điều chỉnh phễu cho phù hợp.
Kết luận
Bằng cách làm theo các bước trên, bạn có thể xây dựng một mô hình dự báo phễu bán hàng chức năng trong Excel để ước tính doanh số tương lai dựa trên dữ liệu phễu, xác suất chuyển đổi và giá trị giao dịch. Mô hình này sẽ giúp bạn dự đoán các giá trị tương lai và xác định hiệu suất phễu để tối ưu hóa chiến lược bán hàng. Với cập nhật và tinh chỉnh thường xuyên, nó có thể là một công cụ mạnh mẽ cho việc ra quyết định trong bất kỳ tổ chức nào hướng tới bán hàng. Bạn có thể áp dụng thêm tùy chỉnh vào mô hình dựa trên yêu cầu cụ thể của mình.
Nhận bài tập Excel nâng cao miễn phí kèm lời giải!