Hướng Dẫn Excel Từng Bước: Áp Dụng Làm Mượt Exponential Holt-Winters Cho Dự Báo Chính Xác

Excel là công cụ được sử dụng rộng rãi nhất khi nói đến việc xử lý các tập dữ liệu lớn. Chúng ta có thể thực hiện vô số tác vụ nhiều chiều trong Excel. Trong bài viết này, tôi sẽ giải thích cách áp dụng Làm Mượt Exponential Holt-Winters trong Excel. Phương pháp này hữu ích cho dự báo.

Tải Xuống Sổ Làm Việc

Tải xuống sổ làm việc này và thực hành trong khi đọc bài viết này.

Giới Thiệu Làm Mượt Exponential Holt-Winters

Phương pháp Holt-Winters là một phương pháp tiên tiến để dự báo giá trị. Nó xem xét tính mùa vụ và hiệu ứng xu hướng trong khi dự đoán dự báo. Đó là lý do tại sao các giá trị đại diện cho các giá trị thực tế trừ đi một số ngẫu nhiên.

Công thức để tính giá trị dự báo sử dụng Làm Mượt Exponential Holt-Winters trong Excel là

Ft+k = (Lt+k*Tt)*St-m+k

Trong đó, F = Giá Trị Dự Báo
L = Mức Độ
T = Xu Hướng
M = 4 cho kỳ quý, 12 cho kỳ tháng
S = Chỉ Số Mùa Vụ

11 Bước Dễ Dàng Thực Hiện Làm Mượt Exponential Holt Winters Trong Excel

Đây là tập dữ liệu cho bài viết hôm nay. Chúng ta có doanh số quý đến năm 2022.

Chúng ta sẽ tính các giá trị dự báo cho năm 2023.

Bước 1: Gán Giá Trị Alpha, Beta & Gamma Ngẫu Nhiên

Nhiệm vụ đầu tiên là gán một số giá trị ngẫu nhiên cho các hằng số alpha, beta, và gamma.

Chúng ta sẽ tối ưu hóa các giá trị này sau.

Bước 2: Tính Chỉ Số Mùa Vụ Ban Đầu

Sau đó, chúng ta sẽ xác định chỉ số mùa vụ ban đầu cho 4 quý đầu tiên. Chúng ta sẽ xác định các chỉ số mùa vụ ban đầu bằng cách chia doanh số của mỗi quý cho doanh số trung bình của 4 quý đầu tiên. Chúng ta sẽ sử dụng hàm AVERAGE để làm như vậy.

• Đi đến F11 và viết công thức sau.

• Bây giờ, nhấn ENTER. Excel sẽ trả về đầu ra.

• Sau đó, sử dụng Fill Handle để AutoFill lên đến F14.

Bước 3: Xác Định Mức Độ và Xu Hướng Ban Đầu

Đã đến lúc tính mức độ ban đầu và xu hướng cho tập dữ liệu.

Mức độ ban đầu là mức độ cho quý thứ 5 vì có 4 quý trong một năm.

Công thức cho Mức Độ Ban Đầu là,

L5 = Y5/S1

Trong đó, Y5= Doanh số cho Quý Thứ 5.

S1= Chỉ Số Mùa Vụ cho Quý Thứ 1.

Để xác định nó,

• Đi đến D15 và viết công thức

• Bây giờ, nhấn ENTER. Excel sẽ trả về đầu ra.

Lần này tôi sẽ tính xu hướng ban đầu (cũng là cho quý thứ 5). Công thức cho xu hướng ban đầu là,

T5 = L5-Y4/S4

Trong đó, L5 = Mức Độ cho Quý Thứ 5.

Y4 = Doanh Số cho Quý Thứ 4.

S4 = Chỉ Số Mùa Vụ cho Quý Thứ 4.

Để tính nó,

• Đi đến E15 và viết công thức,

• Nhấn ENTER để lấy đầu ra.

Bước 4: Tính Các Chỉ Số Mùa Vụ Tiếp Theo

Bây giờ, chúng ta sẽ tính các chỉ số mùa vụ tiếp theo sử dụng công thức chung của chúng ta. Công thức chung để tính chỉ số mùa vụ là,

St = ɣ(Yt/Lt)+(1-ɣ)St-m

Trong đó

L = Mức Độ.
T = Xu Hướng.
M = 4 cho kỳ quý, 12 cho kỳ tháng.
S = Chỉ Số Mùa Vụ.
Ɣ = Hệ Số.
Để tính chỉ số mùa vụ,

• Đi đến F15 và viết công thức sau

• Nhấn ENTER.

• AutoFill lên đến F22.

Lưu Ý: Bỏ qua lỗi trong lúc này, nó sẽ ổn khi bạn đo lường Mức Độ tiếp theo và Xu Hướng tiếp theo.

Bước 5: Xác Định Các Mức Độ Tiếp Theo

Bây giờ tôi sẽ chỉ cách xác định các mức độ tiếp theo sử dụng công thức,

Lt = α(Yt/St-m)+(1-α)(Lt-1+Tt-1)

Trong đó

L = Mức Độ
T = Xu Hướng
M = 4 cho kỳ quý, 12 cho kỳ tháng
S = Chỉ Số Mùa Vụ
α = Hệ Số

• Đi đến D16 và viết công thức sau

• Nhấn ENTER.

• AutoFill lên đến D22.

Bước 6: Đo Lường Các Xu Hướng Tiếp Theo

Hãy lấy ý tưởng về hiệu ứng xu hướng bây giờ. Công thức là

Tt= β(Lt-Lt-1)+(1-β)Tt-1

Trong đó

L = Mức Độ
T = Xu Hướng
M = 4 cho kỳ quý, 12 cho kỳ tháng
S = Chỉ Số Mùa Vụ
β= Hệ Số

Để tính hiệu ứng xu hướng,

• Đi đến E6 và viết công thức sau

• Nhấn ENTER để tiếp tục.

• AutoFill lên đến E22.

Bước 7: Tìm Giá Trị Dự Báo Để So Sánh Với Doanh Số Thực Tế

Bây giờ, chúng ta sẽ tính các giá trị dự báo để so sánh với doanh số thực tế. Cái đầu tiên sẽ là cho quý 6. Công thức để tính giá trị dự báo (để so sánh) là,

Ft = (Lt-1 + Tt-1)* St-M

Vì vậy hãy làm nó.

• Đi đến G16 và viết công thức sau

• Nhấn ENTER để đi.

• AutoFill lên đến G22.

Bước 8: Tính Sai Số Dự Báo

Bây giờ, chúng ta sẽ tính sai số dự báo bằng cách trừ giá trị dự báo từ doanh số thực tế.

• Đi đến H16 và viết công thức

• Sau đó nhấn ENTER.

• AutoFill lên đến H22.

Bước 9: Gán Giá Trị K Cho Các Quý Cần Dự Báo

Đã đến lúc tính dự báo. Nhưng trước đó, chúng ta phải hiểu hệ số k. Nó đại diện cho thời gian tương lai để dự báo. Trong trường hợp của chúng ta, chúng ta sẽ tính dự báo cho 4 quý của năm 2023. Và chúng ta có dữ liệu có sẵn cho năm 2022.

Vì vậy, cho quý 1 năm 2023, giá trị của k sẽ là 1, cho quý 2, nó sẽ là 2, và cứ thế.

Bước 10: Tính Giá Trị Dự Báo

Bây giờ, chúng ta đã sẵn sàng tính các giá trị dự báo của mình. Chúng ta sẽ sử dụng Mức Độ, Xu Hướng và Mùa Vụ có sẵn cuối cùng để tính chúng.

• Đi đến G23 và viết công thức sau

• Sau đó nhấn ENTER để lấy đầu ra.

• Bây giờ, AutoFill lên đến G25.

Bước 11: Tối Ưu Hóa Alpha, Beta, Và Gamma

Bây giờ, để giảm thiểu sai số, chúng ta sẽ tối ưu hóa giá trị của alpha, beta, và gamma. Chúng ta sẽ nhờ sự trợ giúp của Excel Solver để làm như vậy.

• Đầu tiên, chúng ta cần tính sai số bình phương trung bình căn bậc hai. Để làm vậy, đi đến C7 và viết công thức sau

Phân Tích Công Thức:

• COUNT(H15:H21) → Đếm số ô.
  • Đầu Ra → 7
• SUMSQ(H15:H21) → Tính tổng bình phương của H5:H11.
  • Đầu Ra → 463493653301
• =SQRT(SUMSQ(H15:H21)/COUNT(H15:H21)) → Tính RMSE
• =SQRT(992.463493653301/7)
• =SQRT(141.780499093329)
  • Đầu Ra → 9072

• Sau đó, nhấn ENTER.

• Bây giờ, đi đến tab Data (Dữ Liệu) >> chọn Solver.

• Cửa sổ Solver Parameters (Tham Số Solver) sẽ bật lên. Vì chúng ta muốn giảm thiểu sai số, mục tiêu của chúng ta là đặt RMSE ở mức tối thiểu bằng cách thay đổi giá trị của hệ số.
• Sau đó, để thêm ràng buộc, nhấp Add (Thêm).

• Cửa sổ Add Constraint (Thêm Ràng Buộc) sẽ xuất hiện. Các ràng buộc là 0<=α,４,ß<=1. Vì vậy để thêm ràng buộc đầu tiên, đặt tham chiếu ô và giá trị. (xem hình)

• Tương tự, sau khi thêm ràng buộc thứ hai, đầu ra của bạn sẽ như thế này. Sau đó nhấp Solve (Giải).

• Excel sẽ giảm thiểu sai số bằng cách tối ưu hóa alpha, beta, và gamma.

Các Điều Cần Nhớ

• Bạn phải kích hoạt solver add-in.
• Chúng ta không quan tâm đến giá trị k cho việc tính dự báo mà chúng ta sẽ so sánh với doanh số thực tế có sẵn.

Kết Luận

Trong bài viết này, tôi đã giải thích cách áp dụng Làm Mượt Exponential Holt-Winters trong Excel. Tôi hy vọng nó giúp mọi người. Vui lòng bình luận nếu bạn có bất kỳ thắc mắc nào.