File Excel dự báo doanh thu: theo xu hướng, theo mùa vụ và theo danh sách cơ hội bán hàng
Ba cách lập file Excel dự báo doanh thu cho doanh nghiệp nhỏ: xu hướng kèm hệ số mùa vụ, hàm FORECAST.ETS có sẵn và danh sách cơ hội bán hàng có trọng số, cùng kịch bản và cách so sánh dự báo với thực tế.
File Excel dự báo doanh thu ước tính doanh thu các tháng tới dựa trên những gì bạn đã biết: doanh thu các năm trước, quy luật mùa vụ, các đơn đang đàm phán và những thay đổi đã lên kế hoạch như tăng giá hay ra sản phẩm mới. Với doanh nghiệp nhỏ, ba cách sau đủ dùng và có thể kết hợp: xu hướng kèm hệ số mùa vụ, hàm dự báo có sẵn của Excel, và danh sách cơ hội bán hàng có trọng số. Bài này trình bày từng cách kèm công thức, rồi hướng dẫn theo dõi chênh lệch giữa dự báo và thực tế.
Chuẩn bị dữ liệu
Mỗi tháng một dòng: tháng (ngày đầu tháng), doanh thu, và nếu được thì chia theo nhóm hàng hoặc kênh bán. Cần tối thiểu hai năm dữ liệu để thấy mùa vụ. Điều chỉnh các tháng bất thường (đóng cửa sửa chữa, một đơn sỉ lớn hiếm có) ở cột “doanh thu điều chỉnh” để không làm lệch dự báo. Số liệu tháng lấy từ file Excel quản lý bán hàng.
Cách 1: xu hướng kèm hệ số mùa vụ
- Hệ số mùa vụ từng tháng: doanh thu trung bình của tháng đó qua các năm chia cho doanh thu trung bình một tháng. Hệ số 1,6 ở tháng 12 nghĩa là tháng 12 bán hơn tháng bình thường 60%.
- Doanh thu năm dự kiến: doanh thu năm trước × (1 + tốc độ tăng trưởng), tốc độ dựa trên xu hướng gần đây và kế hoạch của bạn.
- Dự báo tháng: doanh thu năm dự kiến ÷ 12 × hệ số của tháng.
Tùy cài đặt vùng, Excel dùng dấu chấm phẩy hoặc dấu phẩy. Với tháng ở cột A, doanh thu ở cột B và cột C là =MONTH(A2):
- Hệ số tháng 12:
=AVERAGEIFS(B:B;C:C;12)/AVERAGE(B:B) - Dự báo một tháng:
=DoanhThuNam/12*XLOOKUP(MONTH(A30);HeSo[Thang];HeSo[HeSo])
Tổng 12 hệ số phải bằng 12; nếu không, chia mỗi hệ số cho trung bình của chúng.
Ví dụ: cửa hàng đồ gia dụng
Năm trước cửa hàng đạt 3,6 tỷ đồng và dự kiến tăng 8%, tức 3,888 tỷ đồng năm nay, trung bình 324 triệu đồng mỗi tháng. Với hệ số 1,6 cho tháng 12 (mùa sắm Tết) và 0,7 cho tháng 3, dự báo là khoảng 518 triệu đồng cho tháng 12 và 227 triệu đồng cho tháng 3. Chênh lệch này là điều file dòng tiền doanh nghiệp cần biết để chuẩn bị tiền nhập hàng trước Tết và cân đối những tháng thấp điểm.
Cách 2: hàm FORECAST.ETS
Từ Excel 2016, hàm FORECAST.ETS (làm trơn hàm mũ) tự nhận diện xu hướng và mùa vụ:
=FORECAST.ETS(A30;B$2:B$29;A$2:A$29;12)
Tham số cuối (12) là chu kỳ mùa vụ 12 tháng. FORECAST.ETS.CONFINT cho khoảng tin cậy, hữu ích để dựng kịch bản thấp và cao. Menu Data > Forecast Sheet làm cùng phép tính kèm biểu đồ chỉ vài cú nhấp. Cách này nhanh nhưng không biết kế hoạch của bạn: việc tăng giá hay ra dòng hàng mới phải tự cộng thêm.
Cách 3: danh sách cơ hội bán hàng có trọng số
Với doanh nghiệp bán cho doanh nghiệp hoặc bán theo báo giá, số liệu quá khứ ít nói lên quý tới; các cơ hội đang mở mới quan trọng. Liệt kê từng cơ hội gồm khách hàng, giá trị, giai đoạn và ngày dự kiến chốt, rồi gán xác suất theo giai đoạn:
| Giai đoạn | Xác suất tham khảo |
|---|---|
| Khách tiềm năng đã xác định nhu cầu | 10% |
| Đang làm báo giá | 30% |
| Đã gửi báo giá | 50% |
| Đàm phán cuối | 80% |
| Đã ký | 100% |
Doanh thu có trọng số của một tháng: =SUMPRODUCT((MONTH(CoHoi[NgayChot])=MONTH(A2))*(YEAR(CoHoi[NgayChot])=YEAR(A2))*CoHoi[GiaTri]*CoHoi[XacSuat]). Sau vài tháng, thay xác suất tham khảo bằng tỷ lệ chốt thực tế của bạn. Báo giá gửi khách quản lý trong mẫu báo giá Excel.
Kết hợp và lập kịch bản
Nhiều doanh nghiệp cộng phần doanh thu nền (cách 1 hoặc 2) với phần đơn mới từ danh sách cơ hội (cách 3). Giữ ba phương án cho giả định quan trọng (thận trọng, cơ sở, lạc quan) và chuyển bằng =CHOOSE(KichBan;ThanTrong;CoSo;LacQuan). Kịch bản thận trọng nên được so với chi phí cố định bằng file tính điểm hòa vốn.
So sánh dự báo với thực tế
- Chênh lệch:
=ThucTe-DuBao - Tỷ lệ chênh lệch:
=IFERROR(ThucTe/DuBao-1;"") - Sai số trung bình 12 tháng: trung bình của giá trị tuyệt đối tỷ lệ chênh lệch, tính ở một cột phụ.
Mỗi chênh lệch trên 10% ghi một câu giải thích. Sau vài tháng sẽ thấy phương pháp có thiên lệch không, chẳng hạn luôn quá lạc quan, để điều chỉnh.
Từ dự báo đến ngân sách
Dự báo doanh thu là dòng đầu tiên của mẫu ngân sách doanh nghiệp; với doanh nghiệp sắp thành lập, nó là đầu vào của mẫu kế hoạch kinh doanh. Xem thêm trong chuyên mục Tài chính doanh nghiệp nhỏ.
Câu hỏi thường gặp
Doanh nghiệp nhỏ nên dùng cách dự báo nào?
Có hai năm số liệu và khách lẻ thì dùng xu hướng kèm hệ số mùa vụ; bán theo báo giá thì dùng danh sách cơ hội có trọng số; thường là kết hợp cả hai.
Nên dự báo bao xa?
12 tháng cuốn chiếu, cập nhật mỗi tháng, là đủ cho dòng tiền và ngân sách.
Doanh nghiệp mới chưa có số liệu thì dự báo thế nào?
Bắt đầu từ công suất (số khách phục vụ mỗi ngày, số giờ làm), thị trường khu vực và kế hoạch bán hàng, rồi thay bằng số thực ngay khi có.
Google Sheets có hàm FORECAST.ETS không?
Không có hàm tương đương hoàn toàn; trên Google Sheets nên dùng cách hệ số mùa vụ hoặc hàm FORECAST cho xu hướng tuyến tính.
