Dự báo doanh số theo mùa trong Excel và chấm bằng MAPE

Một cửa hàng quạt và máy làm mát ở Cần Thơ phải chốt đơn nhập cho 12 tháng tới. Tháng 5 bán nhiều hơn hẳn tháng 1, khoảng 70% theo chỉ số mùa vụ ở dưới, và doanh số cả năm vẫn đang tăng. Chủ cửa hàng nên nhập bao nhiêu máy cho từng tháng, và sai số bao nhiêu thì chấp nhận được? Bài này làm từng bước phương pháp trong Dự báo theo chỉ số mùa vụ và xu hướng bằng Excel. Cửa hàng là ví dụ giả định, số liệu do tôi sinh bằng chương trình. Nếu doanh số của bạn bị một cú sốc như đại dịch thì xem bài về Starbucks, vì khi đó lịch sử không còn dùng để suy ra tương lai.

Bộ số liệu giả định gồm những gì?

Gồm 48 tháng doanh số, tính bằng số máy bán ra. Tôi dùng 36 tháng đầu để dựng mô hình và giữ 12 tháng cuối làm phần chấm điểm, chiếm 25% mẫu. Hyndman và Athanasopoulos (2021, mục 5.8) nêu phần giữ lại thường khoảng 20% mẫu, tuỳ độ dài mẫu và tầm dự báo, và lý tưởng là dài ít nhất bằng tầm dự báo xa nhất cần dùng (Hyndman và Athanasopoulos, 2021).

Cách tạo: mỗi tháng bằng đường xu hướng 200 + 3t (t là số thứ tự tháng), nhân một chỉ số mùa vụ tôi đặt trước, nhân thêm nhiễu ngẫu nhiên với độ lệch chuẩn 4%, rồi làm tròn. Tôi biết đáp án thật, nên sau này có thể hỏi phương pháp tìm lại đáp án đó tốt đến đâu. Với số liệu thật, bạn không có lợi thế này.

Tháng 1–12 (năm 1):   173 172 230 280 281 262 221 198 213 200 204 188
Tháng 13–24 (năm 2):  195 204 267 304 346 306 260 232 252 227 238 235
Tháng 25–36 (năm 3):  220 223 304 335 404 342 284 293 251 262 252 240
Tháng 37–48 (năm 4):  237 283 373 411 451 388 336 306 281 267 312 300

Dán 36 số đầu vào C2:C37. Mười hai số cuối là doanh số thật của năm 4, chỉ dùng để chấm điểm, đặt ở J38:J49.

Bước 1: tách chỉ số mùa vụ thế nào?

Theo phân rã nhân cổ điển, lấy trung bình trượt tâm 2×12 làm xu hướng, chia doanh số thật cho nó, rồi lấy trung bình các tỷ lệ cùng tháng (Hyndman và Athanasopoulos, 2021). Sách ghi các chỉ số này được chuẩn hoá để tổng bằng 12 (mục 3.4). Với chu kỳ chẵn như 12 tháng, sách dùng 2×m: trung bình 12 tháng, rồi trung bình hai lần liền nhau để kết quả đối xứng quanh một tháng. Chọn độ dài khác thì xu hướng thường còn lẫn mùa vụ (mục 3.3).

Trong Excel, đặt cột A là số thứ tự tháng, B là tháng trong năm (1 đến 12), C là doanh số; A và B kéo xuống hết dòng 49:

D8:  =(C2/2+SUM(C3:C13)+C14/2)/12         kéo xuống D31
E8:  =C8/D8                                kéo xuống E31
N2:  =AVERAGEIFS($E$2:$E$37,$B$2:$B$37,M2,$E$2:$E$37,">0")    M2:M13 là 1…12
O2:  =N2*12/SUM($N$2:$N$13)                kéo xuống O13
F2:  =INDEX($O$2:$O$13,B2)                 kéo xuống F49

Trung bình trượt chỉ có từ tháng 7 đến tháng 30, vì cần sáu tháng ở mỗi phía. Vì thế mỗi tháng trong năm chỉ có hai tỷ lệ. Điều kiện ">0" trong AVERAGEIFS bỏ qua các ô trống. Kết quả:

Tháng 5 cao hơn mức trung bình khoảng 41%, tháng 1 thấp hơn khoảng 19%. Tháng 4 lệch nhiều nhất: 1,21 so với 1,30. Hai tỷ lệ mỗi tháng không đủ để nhiễu 4% triệt tiêu nhau.

Tôi chọn mô hình nhân vì biên độ mùa vụ tăng theo mức doanh số: chênh lệch giữa tháng cao nhất và thấp nhất là 109 máy ở năm 1 và 184 máy ở năm 3.

Bước 2 và 3: bỏ mùa vụ, rồi kẻ đường xu hướng ra sao?

Chia doanh số từng tháng cho chỉ số của tháng đó, rồi hồi quy tuyến tính doanh số đã bỏ mùa vụ theo số thứ tự tháng. Hyndman và Athanasopoulos (2021, mục 5.7) mô tả việc dự báo hai phần riêng: phần mùa vụ và phần đã bỏ mùa vụ. Với phần sau, sách cho phép dùng bất kỳ phương pháp không mùa vụ nào, ví dụ phương pháp drift, Holt hay ARIMA không mùa vụ (Hyndman và Athanasopoulos, 2021). Đường thẳng bình phương tối thiểu là lựa chọn của tôi, khớp với bước hai của framework trong thư viện.

G2:  =C2/F2                                kéo xuống G37
a:   =INTERCEPT($G$2:$G$37,$A$2:$A$37)     cho 204,16
b:   =SLOPE($G$2:$G$37,$A$2:$A$37)         cho 2,653

Đường xu hướng là 204,16 + 2,653 × t, tức mức nền tăng khoảng 2,65 máy mỗi tháng. Đường thật tôi đặt là 200 + 3t. Chênh lệch này đến từ nhiễu và từ các chỉ số ước lượng còn lệch.

Bước 4: dự báo 12 tháng kế tiếp và so với thực tế?

Dự báo tháng t bằng (a + b × t) × chỉ số của tháng đó. Sách ghi phần mùa vụ của tương lai thường lấy bằng phần của năm cuối, và chỉ số cố định theo tháng làm đúng việc này.

I38: =(INTERCEPT($G$2:$G$37,$A$2:$A$37)+SLOPE($G$2:$G$37,$A$2:$A$37)*A38)*F38    kéo xuống I49
K38: =ABS(J38-I38)/J38                                                              kéo xuống K49

Tổng dự báo là 3.798 máy so với 3.945 máy thực tế, thấp hơn 3,7%. Dự báo thấp hơn thực tế ở 9 tháng trong 12.

MAPE bằng bao nhiêu, và so với cái gì thì mới có nghĩa?

MAPE là trung bình của sai số tuyệt đối chia cho doanh số thực, ở đây bằng 6,6%: =AVERAGE(K38:K49). Một con số riêng lẻ chưa nói gì. Sách dùng các phương pháp đơn giản làm mốc so sánh, trong đó "seasonal naïve" lấy giá trị cùng tháng của năm trước làm dự báo (Hyndman và Athanasopoulos, 2021). Tôi thêm một mốc nữa của riêng mình: cùng tháng năm trước nhân mức tăng doanh số cả năm 3 so với năm 2 (11,2%).

Hai kết quả cuối gần như hoà. Năm bước chỉ đáng công khi thắng cách làm vài giây này, và ở đây nó thắng rất ít. Cách thứ hai không cần mô hình nào, nhưng nó lấy cả năm 3 làm nền nên mang theo nhiễu của đúng năm đó.

Chấm mô hình trên chính dữ liệu đã dựng nó cho MAPE 2,6%, thấp hơn 2,5 lần. Hyndman và Athanasopoulos (2021, mục 5.8) viết rằng độ lớn của phần dư không phản ánh đáng tin cậy sai số dự báo thật, và mô hình khớp tốt dữ liệu cũ chưa chắc dự báo tốt (Hyndman và Athanasopoulos, 2021). Kết quả của tôi khớp với nhận xét đó.

Hai điều MAPE không nói:

• Mức sàn. Không dự báo nào đưa MAPE về 0. Với số liệu thật bạn không biết sàn nằm đâu. Với bộ số liệu giả, tôi tính được: dùng đúng cả chỉ số lẫn xu hướng tôi đã đặt, MAPE trên 12 tháng này vẫn là 4,4%, do nhiễu tôi thêm vào.
• Hướng và chi phí của sai số. Dự báo thấp hơn thực tế ở 9 tháng trong 12 nghĩa là thiếu hàng ở những tháng đó nếu chủ cửa hàng nhập đúng theo con số. Mất doanh số vì hết hàng và vốn nằm trong kho là hai khoản khác nhau, xem chi phí sai lệch bất đối xứng. Sách nêu sai số phần trăm phạt dự báo quá cao nặng hơn dự báo quá thấp, nhưng MAPE vẫn không phân biệt chi phí thật của hai hướng.

Khi nào cách này không hợp?

Khi lịch sử ngắn, khi mùa vụ đổi hình, và khi có cú sốc. Cụ thể:

• Dưới hai năm dữ liệu thì có tháng không có tỷ lệ nào để lấy trung bình (phép tính của tôi với trung bình trượt 2×12), và 36 tháng như bài chỉ cho hai tỷ lệ mỗi tháng. Bảng chỉ số ở trên cho thấy hậu quả.
• Phân rã cổ điển giả định mùa vụ lặp lại như nhau mỗi năm. Sách nêu ví dụ nhu cầu điện, đỉnh dịch từ mùa đông sang mùa hè khi máy lạnh phổ biến. Nó cũng không chống chịu được các giá trị bất thường như tháng có tranh chấp lao động, và sách ghi rằng dù còn được dùng rộng rãi, phương pháp này không được khuyến nghị vì nay đã có nhiều phương pháp tốt hơn hẳn (Hyndman và Athanasopoulos, 2021). Theo suy luận của tôi, một đợt khuyến mãi lớn trong năm 2 sẽ đẩy chỉ số của tháng đó lên.
• Xu hướng thẳng đến mãi mãi là giả định mạnh. Nếu thị trường bão hoà, đường thẳng nhân với chỉ số sẽ cho số quá cao. Dự báo cuốn chiếu cập nhật đều hơn, xem Chu trình dự báo cuốn chiếu.
• Quyết định nhập hàng khó rút lại cần khoảng dự báo thay cho một con số, như trong Một con số hay nhiều kịch bản?. Chọn phương pháp theo Chọn phương pháp dự báo.

Đào sâu: tôi đã đọc gì, kiểm gì, chưa làm gì?

Tôi đọc các đoạn liên quan trong mục 3.1 đến 3.4 (biến đổi, thành phần, trung bình trượt, phân rã cổ điển), 5.2, 5.7 và 5.8 của Hyndman và Athanasopoulos (2021), bản trực tuyến; tôi bỏ qua các đoạn mã R. Tôi chưa đọc chương 7 về hồi quy, nơi sách trình bày xu hướng tuyến tính, và chưa đọc các bài gốc về MAPE mà sách dẫn. Cách đặt năm bước thành một quy trình là của framework trong thư viện, không phải một công thức nguyên văn trong sách. Việc chọn đường thẳng bình phương tối thiểu cho phần xu hướng và mốc "nhân mức tăng năm ngoái" là của tôi.

Tôi tính toàn bộ bằng Python và cho chạy các công thức trên trong LibreOffice Calc, hai nơi cho cùng kết quả. Tôi chưa chạy trong Microsoft Excel.