Prognozowanie sprzedaży uwzględniające sezonowość!

Z lekcji dowiesz się:

• Excel automatycznie prognozuje dane ze swoich algorytmów!

• Co to jest Przedział ufności?

• Kiedy Excel wstawia Interpolację?

W tej lekcji nauczymy się obliczać prognozę dla danych uwzględniając sezonowość. Zadanie to wykonamy na podstawie przykładowych danych z rys. 1.

(Rys. 1) Przykładowe dane sprzedażowe

Od Excela 2013 zadanie to jest bardzo proste. Wystarczy zaznaczyć dane, a następnie wybrać polecenie Arkusz prognozy z karty Dane (rys. 2).

(Rys. 2) Polecenie Arkusz prognozy

Excel automatycznie prognozuje dane na podstawie swoich algorytmów. Otrzymamy prognozę danych w formie wykresu liniowego przedstawioną na rys. 3.

(Rys. 3) Okno prognozy danych (prognoza w formie wykresu liniowego)

Na rysunku nr 3 kolorem niebieskim zostały oznaczone dane wejściowe, które zaznaczyliśmy, natomiast kolorem pomarańczowym dane prognozy. Możemy zmienić sposób wyświetlania danych i zamiast wykresu liniowego wybrać wykres kolumnowy, wystarczy kliknąć ikonę zaznaczoną strzałką na rys. 4.

(Rys. 4) Prognoza danych w formie wykresu kolumnowego

W większości sytuacji czytelniejszy jest jednak wykres liniowy. Taki wykres powinniśmy odpowiednio skorygować, przede wszystkim sprawdzić na jakiej podstawie Excel go tworzy. W tym celu rozwijamy polecenie Opcje w oknie Tworzenia arkusza prognozy.

W pierwszym kroku powinniśmy ustalić datę końca prognozy na np. datę 12 miesięcy w przód (punkt 1 na rys. 5), następnie sprawdzamy datę początku prognozy (data wstawiona automatycznie przez Excela jest prawidłowa).

Prognozy (górna i dolna granica) mają postać liniową, a dane wejściowe nie. Wynika to z faktu, że Excelowi nie udało się poprawnie wykryć sezonowości. W polu Sezonowość (punkt 2) możemy ustawić ją ręcznie. W tym celu zaznaczamy checkbox przy opcji Ustaw ręcznie, a następnie ustawiamy liczbę 12, ponieważ ustawiliśmy okres prognozy na 12 miesięcy.

Jeśli dobrze znamy się na statystyce, możemy zaznaczyć checkbox przy opcji Dołącz statystykę prognozy (punkt 3). Ważnym współczynnikiem jest Przedział ufności (punkt 4). Przedział ufności ustawiony na poziomie 95% oznacza, że z 95 procentowym prawdopodobieństwem wartość sprzedaży będzie się zawierała w przedziale od górnej granicy ufności do dolnej.

(Rys. 5) Opcje prognozy

Jeśli całkiem wyłączymy Przedział ufności otrzymamy tylko linię prognozy (rys. 6).

(Rys. 6) Wykres prognozy z wyłączonym przedziałem ufności

Im bardziej będziemy zmniejszać wartość procentową przedziału ufności, tym mniejsze będą odstępy między górną granicą ufności a dolną. I tak np. dla Przedziału ufności na poziomie 50%, otrzymamy wykres przedstawiony na rys. 7.

(Rys. 7) Wykres prognozy dla przedziału ufności równego 50%

W dodatkowych opcjach mamy również podany Zakres osi czasu oraz Zakres wartości, które są prawidłowe, ponieważ zostały ustalone na podstawie danych wejściowych (punkt 1 na rys. 8). W polu Uzupełnij brakujące punkty przy użyciu Excel automatycznie ustawia Interpolację, która sprawia, że jeśli brakowałoby jakichś danych czasowych, to Excel wyznaczy dla nich odpowiednią średnią (punkt 2).

Kolejną opcją jest Agreguj duplikaty, w której Excel domyślnie wybiera polecenie Średnia (punkt 3). Oznacza to, że w sytuacji kiedy mielibyśmy kilka wartości dla jednego punktu czasowego, to Excel wyznaczy średnią dla tych wartości. Możemy oczywiście wybrać inny sposób wyznaczenia jednej wartości, np. sumę, medianę, maksimum czy minimum.

(Rys. 8) Pozostałe opcje Tworzenia arkusza prognozy

Po ustawieniu wszystkich opcji możemy utworzyć prognozę, a w zasadzie cały arkusz prognozy, wystarczy zatwierdzić okno przyciskiem Utwórz. Otrzymamy nowy arkusz z prognozą zawierający zarówno wykres, jak i tabelę, na podstawie której ten wykres powstał (rys. 9).

(Rys. 9) Arkusz prognozy

W tabeli mamy najpierw dane wejściowe, następnie dane uzyskane ze skomplikowanych funkcji (obliczeń), jakich Excel użył do stworzenia tej prognozy. Mamy również tabelę z dodatkowymi współczynnikami, na podstawie których Excel wykonał obliczenia (rys. 10).

(Rys. 10) Tabela współczynników do obliczeń prognozy

Podsumowując, dzięki użyciu polecenia Arkusz prognozy z karty Dane, uzyskaliśmy kompletną prognozę sprzedaży. Excel sam obliczył wszystkie dane tylko na podstawie danych wejściowych i ustawienia kilku opcji.

Polecamy także:

• Wykres słupkowy z linią określającą np. wymaganą wartość

• Liczenie wartości pod rząd (np. wzrostów i spadków cen akcji)