Sezon na popyt
Jak opracować miesięczną prognozę sprzedaży, aby była jak najbardziej realna? Jakiej metody prognozowania użyć, mając do czynienia ze sprzedażą sezonową?
Analityk sprzedaży pracujący w firmie sprzedającej wyprodukowany przez siebie produkt X, charakteryzujący się pewną sezonowością (niemonotoniczna zależność sprzedaży od czasu), dostał zadanie opracowania na rok 2015 miesięcznej prognozy sprzedaży na podstawie danych historycznych z lat 2011-2014. Od czego powinien zacząć?
Prognozowanie metodą trendu liniowego
W przygotowywanej prognozie skorzystamy z metody trendu liniowego. Najpierw wyznaczymy wskaźniki sezonowości sprzedaży w poszczególnych miesiącach na podstawie danych z lat poprzednich, a następnie zaprognozujemy wielkość sprzedaży na kolejne miesiące w roku następnym i skorygujemy wartości o wahania sezonowe. W tabeli 1 przedstawiono sprzedaż produktu X w milionach PLN w latach 2011-2014. Na rysunku 1 zobrazowano sezonowość wielkości sprzedaży produktu X (porównanie lat 2011-2014). Jak widać, pierwsze półrocze cechuje niska sprzedaż o tendencji wzrostowej, następnie w miesiącach: czerwiec, październik i grudzień następuje nagły skok sprzedaży, a gwałtowny spadek sprzedaży powtarza się w listopadzie i styczniu w każdym z badanych lat. Jeżeli utworzymy wykres (rys. 2) ilustrujący sprzedaż rok po roku, wyraźnie zobaczymy jej sezonowość.
Budowa modelu
Pierwszym etapem opracowania prognozy jest budowa modelu do wyznaczenia sprzedaży na rok 2015 (tabela 2). Model składa się z następujących kolumn:
- Rok – kolejne lata od 2011 do 2014,
- Miesiąc – kolejne miesiące w poszczególnych latach,
- Kolejny okres – numeracja wszystkich okresów w badanym modelu,
- Sprzedaż (w milionach PLN) – rzeczywiste wielkości sprzedaży w poszczególnych okresach,
- Sprzedaż wg trendu (w milionach PLN) – wielkość sprzedaży w poszczególnych okresach, wyznaczona metodą trendu liniowego,
- Eliminacja trendu (wahania sezonowe) – różnica pomiędzy rzeczywistą wielkością sprzedaży a wielkością wyznaczoną przy użyciu metody trendu liniowego.
Trend liniowy i funkcja REGLINX
W celu wyznaczenia równania opisującego naszą sprzedaż posłużymy się trendem liniowym. Na wykres obrazujący sprzedaż rok po roku 2011-2014 zostanie naniesiona linia trendu wraz z opisującym ją równaniem. Zaznaczamy serię danych na wykresie i z menu podręcznego wybieramy opcję Dodaj linię trendu (rys. 3). W okienku dialogowym Formatowanie linii trendu zaznaczamy Liniowy oraz opcję Wyświetl równanie na wykresie (rys. 4).
Otrzymujemy wykres z naniesioną linią trendu (rys. 5), która określa monotoniczną zależność sprzedaż/ czas (y = 1,9393x + 77,33 jest równaniem określającym nam tę zależność, gdzie x – okres, y – wielkość sprzedaży).
Kolejnym krokiem jest wyznaczenie sprzedaży wg trendu. Możemy to zrobić przy użyciu równania y = 1,9393x + 77,33 (gdzie za x podstawiamy kolejny okres) bądź przy użyciu funkcji REGLINX (funkcje statystyczne).
Funkcja REGLINX oblicza lub przewiduje wartość przyszłą na podstawie dostępnych wartości istniejących (rys. 6). Wartość przewidywana to wartość y dla danej wartości x. Znane są istniejące wartości x i y, a nowa wartość jest obliczana za pomocą regresji liniowej. Funkcja ta jest przydatna w przewidywaniu przyszłego poziomu sprzedaży, wymagań dotyczących zapasów lub trendów konsumpcyjnych. W naszym przykładzie użyjemy funkcji REGLINX, gdzie za X podstawiamy kolejno okresy, za Znane_y wielkość sprzedaży w latach 2011-2014, a za Znane_x wszystkie okresy od 1 do 48.
Formułę kopiujemy do końca, do okresu 60, i otrzymujemy wartości sprzedaży dla poszczególnych okresów wg trendu liniowego (tabela 3).
Następnie w kolumnie F (Eliminacja trendu) wyznaczamy różnicę pomiędzy wartością rzeczywistą sprzedaży a wartością wg trendu. Formułę przeciągamy do okresu 48 (grudzień 2014). Dzięki temu wyliczyliśmy wartość wahań sezonowych sprzedaży (od linii trendu sprzedaży) w poszczególnych okresach (rys. 7).
Na tej podstawie można wyznaczyć wahania sezonowe sprzedaży dla poszczególnych okresów w 2015 roku. Liczymy je jako średnią wahań sezonowych odpowiednich okresów z lat poprzednich, np. dla stycznia 2015 będzie to średnia wahań sezonowych wszystkich styczni lat 2011–2014 (rys. 8).
Ostatnim krokiem jest korekta wyznaczonej wielkości sprzedaży wg trendu w roku 2015 o wyliczone średnie wahania sezonowe w poszczególnych miesiącach (rys. 9).
W ten sposób otrzymaliśmy prognozowaną sprzedaż w poszczególnych miesiącach roku 2015 na podstawie lat ubiegłych (tabela 4).
Dodatkowo można wstawić wykres pokazujący sprzedaż historycznie za lata 2011-2014 oraz prognozowane wartości sprzedaży w roku 2015 (rys. 10).
W materiale na podstawie sprzedaży za lata ubiegłe została zaprognozowana miesięczna wartość sprzedaży sezonowej na rok następny. Na początku na podstawie linii trendu oraz funkcji REGLINX zostało wyznaczone równanie prostej opisującej sprzedaż historyczną. Kolejnym krokiem było wyliczenie na podstawie otrzymanego równania sprzedaży na 2015 rok. Ostatnim krokiem było wyznaczenie wahań sezonowych w poszczególnych miesiącach lat historycznych i skorygowanie prognozy na 2015 rok o te wahania. Dzięki temu analityk szybko i sprawnie wyznaczy prognozowaną miesięczną sprzedaż na rok 2015. Jednak, jak wiadomo, jest to tylko prognoza, która bazuje na liczbach z lat poprzednich, i może wpływać na nią wiele dodatkowych czynników, które mogą być przyczyną ewentualnych odchyleń od zaplanowanych wartości.
Przypisy / Źródła
Źródło wszystkich infografik w artykule: opracowanie własne autora