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 bộ dữ liệu khổng lồ. Chúng tôi có thể thực hiện vô số tác vụ của nhiều thứ nguyên trong Excel . Trong bài viết này, tôi sẽ giải thích cách áp dụng Làm mịn theo cấp số nhân Holt-Winters trong Excel . Phương pháp này hữu ích cho việc dự báo.
Tải xuống Workbook
Tải xuống sổ làm việc này và thực hành trong khi xem qua bài viết này.
Giới thiệu về Làm mịn theo cấp số nhân của Holt-Winters
Holt-Winters phương pháp là một phương pháp tiên tiến để dự báo các giá trị. Nó xem xét tính thời vụ và ảnh hưởng của 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ế ngoại trừ một số ngẫu nhiên.
Công thức để tính toán giá trị dự báo bằng cách sử dụng Holt-Winters Exponential Smoothing trong Excel là
Ft + k =(Lt + k * Tt) * St-m + k
Ở đâu, F =Giá trị dự báo
L =Cấp độ
T =Xu hướng
M = 4 cho khoảng thời gian hàng quý, 12 cho khoảng thời gian hàng tháng
S =Chỉ số thời vụ
11 bước đơn giản để thực hiện làm trơn theo cấp số nhân của Holt Winters trong Excel
Đây là tập dữ liệu cho bài viết hôm nay. Chúng tôi có doanh số hàng quý đến năm 2022.
Chúng tôi sẽ tính toán các giá trị dự báo cho năm 2023.
Bước 1:Chỉ định giá trị Alpha, Beta &Gamma Ngẫu nhiên
Nhiệm vụ đầu tiên là chỉ định một số giá trị ngẫu nhiên cho các hằng số alpha , beta, và gamma .
Sau đó, chúng tôi sẽ tối ưu hóa các giá trị này.
Bước 2:Tính Chỉ số Thời vụ Ban đầu
Sau đó, chúng tôi sẽ xác định chỉ số theo mùa ban đầu cho 4 đầu tiên các phần tư. Chúng tôi sẽ xác định các chỉ số theo mùa ban đầu bằng cách chia doanh thu của mỗi quý cho doanh thu trung bình trong số 4 đầu tiên quý . Chúng tôi sẽ sử dụng chức năng AVERAGE để làm như vậy.
- Đi tới F11 và viết ra công thức sau.
=C11/AVERAGE($C$11:$C$14)
- Bây giờ, nhấn ENTER . Excel sẽ trả về kết quả đầu ra.
- Sau đó, sử dụng Xử lý điền sang Tự động điền lên đến F14 .
Bước 3:Xác định mức và xu hướng ban đầu
Đã đến lúc tính toán mức độ và xu hướng ban đầu cho tập dữ liệu.
Cấp ban đầu là cấp độ cho quý 5 vì có 4 các quý trong một năm.
Công thức cho Cấp ban đầu là,
L5 =Y5 / S1
Ở đâu, Y5 =Bán hàng cho Quý 5.
S1 =Chỉ số theo mùa cho Quý 1.
Để xác định nó,
- Đi tới D15 và viết ra công thức
=C15/F11
- Bây giờ, nhấn ENTER . Excel sẽ trả về kết quả đầu ra.
Lần này, tôi sẽ tính toán xu hướng ban đầu (cũng là cho ngày 5 phần tư). Công thức cho xu hướng ban đầu là,
T5 =L5-Y4 / S4
Ở đâu, L5 =Mức cho Quý 5.
Y4 =Bán hàng cho Quý 4.
S4 =Chỉ số theo mùa cho Quý 4.
Để tính toán nó,
- Đi tới E15 và viết ra công thức,
=D15-C14/F14
- Nhấn ENTER để có được kết quả đầu ra.
Bước 4:Tính Chỉ số theo mùa tiếp theo
Bây giờ, chúng tôi sẽ tính toán các chỉ số theo mùa tiếp theo bằng công thức chung của chúng tôi. Công thức chung để tính chỉ số theo mùa là,
St =ɣ (Yt / Lt) + (1-ɣ) St-m
Ở đâu
L =Mức độ.
T =Xu hướng.
M = 4 cho khoảng thời gian hàng quý, 12 trong một khoảng thời gian hàng tháng.
S =Chỉ số thời vụ.
Ɣ = Hệ số.
Để tính toán chỉ số theo mùa,
- Đi tới F15 và viết ra công thức sau đây
=$C$6*(C15/D15)+(1-$C$6)*F11
- Nhấn ENTER .
- Tự động điền lên đến F22 .
Lưu ý: Bỏ qua lỗi trong một thời gian, sẽ ổn sau khi bạn đo các Cấp độ tiếp theo và Xu hướng tiếp theo .
Bước 5:Xác định các cấp độ tiếp theo
Bây giờ tôi sẽ chỉ cách xác định các cấp độ tiếp theo bằng công thức,
Lt =α (Yt / St-m) + (1-α) (Lt-1 + Tt-1)
Ở đâu
L =Cấp độ
T =Xu hướng
M = 4 cho khoảng thời gian hàng quý, 12 trong một khoảng thời gian hàng tháng
S =Chỉ số thời vụ
α = Hệ số
- Đi tới D16 và viết ra công thức sau đây
=$C$4*(C16/F12)+(1-$C$4)*(D15+E15)
- Nhấn ENTER .
- Tự động điền lên đến D22 .
Bước 6:Đo lường các xu hướng tiếp theo
Bây giờ hãy lấy ý tưởng về hiệu ứng xu hướng. Công thức là
Tt =β (Lt-Lt-1) + (1-β) Tt-1
Ở đâu
L =Cấp độ
T =Xu hướng
M = 4 cho khoảng thời gian hàng quý, 12 trong một khoảng thời gian hàng tháng
S =Chỉ số thời vụ
β = Hệ số
Để tính toán hiệu ứng xu hướng,
- Đi tới E6 và viết ra công thức sau đây
=$C$5*(D16-D15)+(1-$C$5)*E15
- Nhấn ENTER để tiếp tục.
- Tự động điền 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 tôi sẽ tính toán các giá trị dự báo để so sánh với doanh số bán hàng thực tế. Công thức đầu tiên sẽ dành cho quý 6. Công thức để tính toán các giá trị dự báo (để so sánh) là,
F t =(L t-1 + T t-1 ) * S t-M
Vì vậy, hãy làm điều đó.
- Đi tới G16 và viết ra công thức sau đây
=(D16+E16)*F12
- Nhấn ENTER đi.
- Tự động điền lên đến G22 .
Bước 8:Tính toán sai số dự báo
Bây giờ, chúng tôi sẽ tính toán sai số dự báo bằng cách lấy doanh số bán hàng thực tế trừ đi giá trị dự báo.
- Đi tới H16 và viết ra công thức
=C16-G16
- Sau đó nhấn ENTER .
- Tự động điền lên đến H22 .
Bước 9:Chỉ định giá trị K cho các phần được dự báo
Đã đến lúc tính toán dự báo. Nhưng trước đó, chúng ta phải hiểu về đồng hiệu quả k . Nó đại diện cho thời gian dự báo trong tương lai. Trong trường hợp của chúng tôi, chúng tôi sẽ tính toán dự báo cho 4 các quý của năm 2023. Và chúng tôi có sẵn dữ liệu cho năm 2022.
Vì vậy, trong quý đầu tiên của năm 2023, giá trị của k sẽ là 1, đối với quý thứ 2, nó sẽ là 2, v.v.
Bước 10:Tính giá trị dự báo
Bây giờ, chúng tôi đã sẵn sàng để tính toán các giá trị dự báo của mình. Chúng tôi sẽ sử dụng Mức, Xu hướng và Thời vụ có sẵn cuối cùng để tính toán chúng.
- Đi tới G23 và viết ra công thức sau đây
=($D$22+F23*$E$22)*F19
- Sau đó nhấn ENTER để có được kết quả đầu ra.
- Bây giờ, Tự động điền lên đến G25 .
Bước 11:Tối ưu hóa Alpha, Beta và Gamma
Bây giờ, để giảm thiểu lỗi, chúng tôi sẽ tối ưu hóa giá trị của alpha , beta và gamma . Chúng tôi sẽ nhờ sự trợ giúp của trình giải Excel để làm như vậy.
- Đầu tiên, chúng ta cần tính sai số bình phương căn bậc hai. Để làm như vậy, hãy truy cập C7 và viết ra công thức sau đây
=SQRT(SUMSQ(H15:H21)/COUNT(H15:H21))
Phân tích công thức:
- COUNT (H15:H21) → Đếm số lượng ô.
- Đầ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 toán RMSE
- =SQRT (992.463493653301 / 7)
- =SQRT (141.780499093329)
- Đầu ra → 9072
- Sau đó, nhấn ENTER .
- Bây giờ, hãy chuyển đến Dữ liệu tab>> chọn Bộ giải .
- Tham số bộ giải cửa sổ sẽ bật lên. Vì chúng tôi muốn giảm thiểu lỗi, mục tiêu của chúng tôi là đặt RMSE ở mức tối thiểu bằng cách thay đổi các giá trị của hệ số .
- Sau đó, để thêm các ràng buộc, hãy nhấp vào Thêm .
- Cửa sổ 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, hãy đặt tham chiếu ô và giá trị . (xem hình ả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 vào Giải quyết .
- Excel sẽ giảm thiểu lỗi bằng cách tối ưu hóa alpha , beta, và gamma .
Những điều cần nhớ
- Bạn phải kích hoạt phần bổ trợ giải mã .
- Chúng tôi không lo ngại về giá trị k để tính toán các dự báo mà chúng tôi sẽ so sánh với doanh số bán hàng thực tế hiện có.
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ịn theo cấp số nhân Holt-Winters trong Excel . Tôi hy vọng nó sẽ giúp ích cho tất cả 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.