Prognozowanie w Excelu za pomocą wbudowanych narzędzi

Kto nigdy nie chciał znać przyszłości niech podniesie ręke 🙂 Może nie zawsze taka moc jest porządana, ale na pewno od czasu do czasu każdy chciałby mieć krysztyłową kulę przewidującą przyszłe zdarzenia. Takie zdolności tym bardziej są pożądane w biznesie. Niejeden przedsiębiorca próbuje przewidzieć trendy sprzedaży czy zachowania klientów w przyszłości. Dziś zobaczymy, co w w tej kwestii ma do zaoferowania prognozowanie w Excelu. Narzędzie to oferuje kilka funkcji statystycznych ułatwiających podejmowanie decyzji, więc warto się z nimi zapoznać. Zapraszam zatem do lektury 🙂

Prognozowanie w Excelu

Jakie mamy opcje? Jak pewnie przypuszczacie, nie mamy póki co narzędzi przewidujących dokładnie przyszłość 😉 Niemniej na podstawie matematyki, algorytmów i naszych dotychczasowych wyników otrzymujemy do dyspozycji funkcje liczące spodziewane wartości. Może to być sprzedaż, odwiedziny strony,  liczba zamówień lub cokolwiek innego. Musimy mieć tylko jakieś historyczne wyniki ujęte w przedziale czasowym, jak na przykład miesiąc czy rok. Najlepiej, jeśli będzie to kilka lub kilkanaście okresów, tak aby wynik był bardziej wiarygodny.

Excel oferuje dwa podejścia. Jedno to po prostu wybór jednej z formuł. Drugie zaś to skorzystanie z rodzaju wbudowanego narzędzia. Ono również bazuje na kilku formułach. Ułatwia jednak cały proces i „produkuje” dodatkowo wykres. A więc jest to jakby mini pakiet prognozowania w Excelu 🙂 I na tym konkretnym rozwiązaniu dziś się pochylę 😉

Prognozowanie – co potrzebujemy

To co potrzebujemy to po prostu dane wejściowe, ale muszą być przygotowane w odpowiedni sposób. Będą to dwie kolumny lub wiersze (oba układy zadziałają tak samo).

Przyjmijmy jednak, że pracujemy z kolumnami. Jedna musi zatem zawierać daty, a druga jakieś wartości liczbowe. Tu ważna uwaga. Daty muszą być również datami dla Excela. Innymi słowy, muszą to być poprawnie sformatowane komórki, które Excel rozpoznaje jako daty. Jeśli macie w tym zakresie więcej problemów, to zapraszam do oddzielnego wpisu poświęconemu pracy z datami i czasem (link). Bez tego wasze prognozowanie w Excelu natrafi na szybką przeszkodę 😉

Startujemy

Klikamy lewą myszą na nasze dane, tabelkę, może być też pod tabelką. Mój wstępny przykład będzie jak poniżej:

Następie w menu głównym w zakładce Dane wybieramy Arkusz Prognozy w sekcji Prognoza.

Zostaje otwarte okienko konfiguracji, gdzie mamy do wyboru dwie ścieżki tj. szybką i bardziej zaawansowaną, gdzie zmieniamy dodatkowe ustawienia. W obu przypadkach musimy zacząć od sprawdzenia i ewentualnej zmiany, do kiedy chcemy wykonać prognozę. Excel zapewne domyślnie wybierze kilka najbliższych okresów, np. miesiący jak poniżej.

Jeśli to nam pasuje, to praktycznie skończyliśmy naszą konfigurację i jest to szybkie prognozowanie w Excelu. Wystarczy tylko kliknąć przycisk „Utwórz”. W tym momencie tak też zrobię, aby pokazać wam nieco prostszy przykład. Poniżej jednak pójdziemy dalej i zobaczmy na ustawienia dodatkowe, z których szczególnie jedno ma bardzo fajne i dość częste zastosowanie.

Klikam zatem na „Utwórz”. Wcześniej można jeszcze wybrać jeden z dwóch dostępnych typów wykresu (liniowy lub kolumnowy – prawy górny róg). Ja zostawiam domyślny czyli liniowy, ale poniżej pokazuje, jak wyglądają oba.

Prognoza na szybko – rezultat

I voila’ 🙂 Mamy efekt. Excel tworzy nową zakładkę i umieszcza tam dwa elementy. Po pierwsze, dostajemy tabelę z dotychczasowymi wynikami jak i z prognozą (kolumna C). Dodatkowo znajdują się tam także kolumny D i E, o czym napiszę poniżej przy okazji ustawień dodatkowych. Oprócz tabelki z danymi otrzymujemy w gratisie wykres, którego podgląd widzieliśmy w poprzednim kroku. Oba obiekty to standardowe excelowe elementy i można je dowolnie kształtować (kolory, styl itd.). W ten sposób dostajemy prognozę opartą o nasze dane wejściowe (tu sprzedaż za miesiące styczeń – czerwiec). Poza tym Excel podpowiada również spodziewane odchylenia w dół oraz w górę względem głównej prognozy. Tym ostatnim sterują dodatkowe kolumny D i E.

Jeśli jesteśmy ciekawi, jakie formuły Excelowe stoją za tymi obliczeniami, to możemy je podejrzeć. W tym przypadku główna prognoza jest stworzona przy użyciu funkcji REGLINX.ETS. Jej parametry i ustawienia (np. sezonowść) zależą od dodatkowej konfiguracji, o czym będzie poniżej. Natomiast jeśli chcemy coś zmienić na tym etapie, to jest to klasyczna formuła, którą możemy dowolnie modyfikować.

Możemy też zobaczyć formuły stojące za odchyleniami wykresu. W dużym skrócie kolumna D i E pokazują możliwe odchylenia głównej prognozy. Ich stopień możemy do pewnego stopnia kontrolować, o czym również napiszę przy ustawieniach dodatkowych.

Prognoza – ustawienia dodatkowe

Teraz zobaczmy na dodatkowe ustawienia, które twórcy Excela konfigurują za nas i standardowo przyjmują domyślne wartości.  O ile ich nie zmienimy 😉 Jest tu kilka ciekawych opcji, więc zobaczmy, jak wygląda prognozowanie w Excelu z uwzględnieniem naszych preferencji.

Sezonowość

Jedną z nich jest możliwość „powiedzenia” Excelowi, że mamy do czynienia z sezonowością wyników. Jeśli weźmiemy podobną tabelkę sprzedaży, jak poprzednio, zauważymy że „szczytowy” wynik jest na koniec każdego kwartału czyli co trzy miesiące. Po tym następują dwa „chudsze” okresy.

Jeśli zostawimy to automatyce, to Excel zaproponuje poniższy wynik. Główna prognoza nie wygląda zbyt realistycznie, ewentuanie jej dolny wariant.

Tymczasem jeśli wejdziemy w opcje, możemy naszą sezonowość dostroić do naszych danych.

Zaznaczam opcję „Ustaw ręcznie” i wpisuję 3. Dlaczego akurat tyle? Ta liczba zależy od naszych danych i sezonowości. W moim przypadku przekazuję informację, że co 3 jednostki (w mojej tabelce to 3 miesiące) następuje sezonowe odchylenie. Jeśli np. mierzycie dni i cos będzie się dziać raz na 20 dni to wtedy taka liczba wynosiłaby 20.

Po zmianie podgląd wykresu pokazuje już znacznie bardziej prawdopodobna prognozę. Co ciekawe w tym przypadku odchylenie górne i dolne pokrywa się z główną prognozą. Wynika to z prostych danych i dość regularnego zachowania wykresu. Chciałem pokazać cos modelowego, więc tak wyszło 😉

Ustawienia zaawansowane – braki i niespójności w danych

Dwie kolejne opcje, które możemy ręcznie dopasować dotyczą dwóch popularnych scenariuszy. Nawet jeśli nie zmieniamy tych ustawień, to warto wiedzieć, jak domyślnie działa nasze prognozowanie w Excelu.

Pierwsza to sytuacja, kiedy brakuje nam informacji za jakiś miesiąc czy rok. W przypadku poniżej powiedzmy, że nie mam danych sprzedaży za luty. Domyślnie twórcy Excela zadbali o to, żeby narzędzie stosowało tzw. interpolację, czyli oblicza szacowaną wartość na bazie najbliższych komórek (tu zaznaczono na zielono).

W opcjach wygląda to tak:

Rezultat natomiast będzie jak poniżej. W przypadku wartości 200 i 400 interpolacja obliczyła szacunkową wartość na 300. Tak więc taki wynik został przypisany na luty.

Inna dostępna tu opcja to potraktowanie brakujących danych jako zero:

Wówczas prognoza wyglądałaby następująco:

Drugi scenariusz to sytuacja, gdy co prawda mamy dane za każdy okres (np. miesiąc), ale niektóre pozycje występują więcej niż raz. Nie są unikalne. W poniższym przykładzie taka sytuacja występuje znów dla lutego. Mamy dwa różne wiersze z różnymi wartościami. I co teraz? 😉

Otóż w takiej sytuacji Excel domyślnie policzy średnią. Odpowiedzialne za to jest ustawienie, które nazywa się „Agreguj duplikaty” przy użyciu…i tu możemy coś wybrać.

Prognoza wygląda jak poniżej, a więc znów luty to 300 zł (średnia z 400 zł i 200 zł).

Opcji wyboru jednak jest znacznie więcej. Będą to typowe funkcje grupujące, które znamy z Excela, choćby z tabel przestawnych. Możemy przykładowo wybrać sumę.

Wówczas podgląd prognozy będzie wyglądał jak poniżej:

Przedział ufności i dodatkowe wskaźniki statystyki prognozy

Dwa ostatnie elementy, które możemy ustawić dotyczą tzw. przedziału ufności oraz wyświetlenia dodatkowych informacji statystycznych.

Jeśli chodzi o przedział ufności to domyślnie jest on ustawiony na 95 %. Możemy jednak go zmienić, wówczas będzie miało to wpływ na górne i dolne odchylenia prognozy (cienkie pomarańczowe odgałęzienia poniżej).

Im mniejszy współczynnik (%) ufności, tym odchylenia będą bardziej „płaskie” czyli mniejsze. Poniżej te same dane, ale przedział ufności zmniejszyłem do 50 %.

 

W skrócie dotyczy on, na ile ufamy naszym danym, które stanowią bazę dla prognozy. Jeśli nie mamy tu jakichś zasadniczych uwag, to wartość domyślna 95 % powinna sprawdzić się najlepiej. Później już po wygenerowaniu prognozy w tabelce z wynikami znajdziemy formułę, która jest odpowiedzialna za górny i dolny przedział ufności. Znajdziemy tam m.in zmienną, która zależy właśnie od powyższego wyboru. Ufność na poziomie 95 % poniżej pojawia się jako 0,95.

Jeśli w ogólnie nie chcielibyśmy pokazywać dolnej i górnej granicy ufności, to możemy ją wyłączyć (poniżej pole zaznaczone niebieską strzałką).

Dla chętnych i lubiących zadania z gwiazdką 😊 do końcowej prognozy można dołączyć dodatkową tabelkę z wartościami wskaźników statystycznych. Wystarczy zaznaczyć odpowiednie pole…

…i po kliknięciu „Utwórz” dostajemy dodatkowo garść informacji czyli wartość statystyczną jako wynik prognozowania szeregu czasowego. Wynikają one bezpośrednio z użytej funkcji czyli REGLINX.ETS.STATYSTYKA. W zależności od wybranego parametru (1,2,3…) funkcja zwaraca inny wskaźnik. Zaznaczenie powyższej opcji powoduje wygenerowanie całego pakietu opartego na tejże funkcji.

Dla chętnych zamieszczam link do strony Microsoft (link), gdzie znajdziecie więcej informacji na temat powyższych wartości.

Prognozwanie w Excelu

I to byłoby tyle na ten moment 🙂 Rozłozyliśmy na czynniki pierwsze wbudowany moduł, ale do samego tematu będę wracać w przyszłości, więc zapraszam! Jesli macie jakieś uwagi, to piszcie lub komentujcie.

Ps. Zapraszam również do obejrzenia filmu na You Tube 🙂