Wykres prognozy w excelu metodą holta-wintersa

Prognozowanie w Excelu modelem Holta-Wintersa

Posted on Posted in wykresy

Excel jest narzędziem umożliwiającym przeprowadzanie różnych typów analiz, takich jak np. prognozy. W dzisiejszym wpisie przedstawię jak za pomocą Excela wykonać prognozę modelem Holta-Wintersa. Gotowy plik z prognozą można pobrać tutaj.

 

Teoria

Model Holta-Wintersa jest jedną z technik prognozowania wykorzystujących tzw. wygładzenie wykładnicze. Wygładzenie polega na stworzeniu ważonej średniej ruchomej, której wagi określa się według schematu - im starsza informacja o badanym zjawisku, tym mniejszą wartość stanowi ona dla aktualnej prognozy. Aby zbudować model należy przyjąć następujące założenia i wzory.

 

wzory-holt

 

W modelu oblicza się prognozy wygasłe, czyli takie które dotyczą okresu, w których rzeczywista wartość już się zrealizowała oraz prognozy rzeczywiste stawiane na okres, który jeszcze nie zaistniał.

 

wzory-prognozy

 

Za pierwsze wartości F1 i S1 przyjmuje się zazwyczaj:

 

Wartości początkowe holt

 

Aby ocenić precyzyjność przewidywań modelu, oblicza się tzw. błąd ex-post prognoz wygasłych za pomocą wskaźników:

Średni błąd bezwzględny (Mean Absolute Error) - informuje nas o tym o ile średnio w okresie predykcji, rzeczywiste realizacje zmiennej prognozowanej, będą się odchylać, co do bezwzględnej wartości, od prognoz.

 

mae wzór

 

Błąd średniokwadratowy (Mean Squared Error) - to średnia różnica kwadratów odchyleń pomiędzy rzeczywistymi realizacjami zmiennej prognozowanej a prognozą.

 

mse-wzór

 

Pierwiastek błędu średniokwadratowego (Root Mean Square Error) - mierzący o ile średnio odchylają się realizacje zmiennej prognozowanej od obliczonych prognoz.

 

rmse wzór

 

Stworzony model możemy uznać za dobry, jeżeli wskaźnik wartość RMSE/prognoza rzeczywista, będzie mniejszy niż 10%. W praktyce jednak, najlepszym testem na ocenienie skuteczności modelu, będzie porównanie tworzonych przez niego prognoz do rzeczywistych wartości.

 

Jak stworzyć model

Nasz model zbudujemy na bazie danych statystycznych zaczerpniętych ze strony Gdańskiego Lotniska im. Lecha Wałęsy. Za pomocą modelu, spróbujemy przewidzieć ilu pasażerów zostanie obsłużonych w zagranicznym regulowanym ruchu lotniczym, w latach 2016-2018. W naszej tabelce znajdą się kolumny oznaczające kolejno: C - liczba obsłużonych pasażerów, D - szacunek wahań przypadkowych modelu, E - szacunek trendu dla modelu, F - prognozy wygasłe i rzeczywiste, G-I - obliczenia potrzebne do wyliczenia wskaźników MAE, MSE, RMSE, M-N - parametry alfa i beta dla modelu.

 

Dane do wyliczeń modelu

 

Teraz zgodnie z przyjętymi na początku założeniami podajemy wartości początkowe dla parametrów F1 i S1. W naszym przypadku będzie to y1=F1 oraz y2-y1=S1. Następnie wprowadzamy parametry alfa i beta, przyjmując chwilowo ich wartości na poziomie 0,4 (później dane będziemy optymalizować Solverem).

 

Parametry alfa i beta w modelu

 

W kolejnym kroku obliczamy równocześnie Ft i St według podanych powyżej wzorów, przeciągając formuły w dół.

 

Parametr ft

 

Parametr st

 

Przechodzimy do kolumny F, w której za pomocą wzorów, obliczamy dla lat 1998-2015 prognozy wygasłe oraz 2016-2018 prognozy rzeczywiste.

 

Prognozy wygasłe w excelu

 

Prognozy rzeczywiste w excelu

 

Uzupełniamy kolumny G-I, które za chwilę posłużą nam do wyliczenia błędu ex-post prognoz wygasłych (aby otrzymać wartość bezwzględną w kolumnie H możemy posłużyć się funkcją MODUŁ.LICZBY).

 

Wyliczenia do wskaźników

 

Następnie korzystając z funkcji ŚREDNIA I PIERWIASTEK obliczamy wspomniane wskaźniki błędu MAE, MSE oraz RMSE.

 

Wskaźniki błędu prognoz wygasłych

Kiedy mamy już wyliczone wskaźniki możemy przystąpić do optymalizacji parametrów alfa i beta, tak aby wskaźnik MAE przybrał jak najmniejszą wartość. W tym celu posłużymy się Solverem, dostępnym pod kartą Dane w grupie Analiza (jeżeli dodatek nie jest u Ciebie aktywowany, wujek Google szybko podpowie jak go włączyć 🙂 ). Parametry Solvera ustawiamy tak, aby minimalizować wskaźnik MAE, poprzez zmianę komórek oznaczonych jako alfa i beta, zamkniętych w przedziale [0-1].

 

Ustawienia solvera w modelu holta

 

Po kliknięciu na przycisk Rozwiąż i zachowaniu uzyskanych wyników, parametry wygładzenia alfa i beta powinny wynosić a=1 i b=0,228657122399511. Na tym etapie możemy wstępnie zweryfikować czy nasz model może posłużyć za efektywne narzędzie do prognozowania. W tym celu obliczamy współczynnik dopuszczalności prognozy, określony wzorem RMSE/prognoza rzeczywista dla kolejnych okresów T16-T18. W naszym przypadku dla roku 2016 wynosi on 5%, zatem stawianą prognozę można uznać za wiarygodną. Na koniec warto zobrazować całą analizę na wykresie, przyjmując jako serie danych wartości rzeczywiste oraz prognozy.

 

Wartości rzeczywiste i prognozowane na wykresie

 

Kiedy mamy już prognozę, nie pozostaje nam nic innego, jak tylko śledzić nowo pojawiające się dane, aby zweryfikować czy używanie modelu ma sens. Z tego co udało mi się dowiedzieć na jednym z portali informacyjnych, liczba obsłużonych pasażerów ogółem w 2017 roku wyniosła ponad 4,6 mln. Istnieje zatem szansa, że prognozowane przez nas wartości sprawdzają się w praktyce 🙂

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *