Jak w Excelu można prognozować przyszłe wyniki finansowe firmy? Na wiele sposobów! W zależności od wersji Excela, zainstalowanych dodatków i wymagań odnośnie do dokładności naszych prognoz mamy różne możliwości.
Najprostszą możliwością prognozowania w Excelu jest tworzenie serii liczb. Wystarczy, że napiszemy dwie liczby, np. 1 i 3 (najwygodniej jedna pod drugą). Jeśli zaznaczymy te dwie liczby i złapiemy za prawy dolny róg zaznaczenia lewym przyciskiem myszy i przeciągniemy w dół (rysunek 1)…
…Excel wykryje różnicę pomiędzy liczbami i kolejne (prognozowane) liczby będą powiększane dokładnie o tę różnicę. Jednak prognozowanie z dwóch liczb to słabe prognozowanie. W tym przykładzie chodziło o zademonstrowanie zasady postępowania z przeciąganiem.
W drugim przykładzie powiększymy nasz ciąg do pięciu liczb {2; 6; 7; 10; 17}. Znów zaznaczamy wszystkie komórki z liczbami, łapiemy prawy dolny róg zaznaczenia lewym przyciskiem myszy i przeciągamy w dół (rysunek 2).
Tym razem sposób obliczenia nie wygląda na prosty, ale jeśli przyjrzymy się uważnie, to zauważymy, że od drugiej dołożonej liczby (prognozy) różnica pomiędzy kolejnymi liczbami wynosi 3,4. Jak Excel znalazł (dokonał prognozy) kolejnye liczby? Okazuje się to prostsze, niż wygląda na pierwszy rzut oka. Na podstawie dostępnych liczb Excel wyznaczył równanie liniowe (ax + b) i podstawiał kolejne x, czyli numer liczby.
Później w artykule zobaczymy linię i równanie trendu na wykresie. Operacje, które Excel wykonuje w tym przykładzie, są identyczne, jednak nie są przedstawiane wizualnie, lecz jedynie za pomocą ciągu liczb.
Co w sytuacji, kiedy rozwój twojej firmy albo kolonii bakterii jest bardziej dynamiczny i lepiej odzwierciedlałby go ciąg geometryczny, a nie liniowy? Zasada jest analogiczna, ale przeciągamy zaznaczenie, trzymając prawy, a nie lewy przycisk myszy. Zaczynając od prostego przykładu liczb 1 i 2 (rysunek 3), możemy wyraźnie zobaczyć, że każda kolejna liczba jest dwa razy większa od poprzedniej.
Tym razem Excel w pamięci oblicza parametry funkcji wykładniczej (aebx), które najlepiej przybliżają dostępne punkty. Dla przykładu: z ciągu {1;3;5;7;16} Excel oblicza kolejne liczby jako elementy ciągu wykładniczego/geometrycznego (rysunek 4).
Linia trendu
Trochę bardziej widowiskowa w procesie prognozy jest linia trendu. Ma też więcej możliwości. Można ją dołożyć do większości wykresów, np. kolumnowego, liniowego czy punktowego.
Pokażemy linię trendu na przykładzie – przeanalizujemy, czy liczba godzin, jaką poświęcamy na naukę, wpływa na ocenę z egzaminu. Odpowiedź znajdziemy za pomocą wykresu i linii trendu.
Wstawiamy wykres punktowy (XY) na podstawie naszych danych i dodajemy do niego linię trendu (rysunek 5).
Domyślnie dodaje się prosta linia (równanie liniowe – rysunek 5), ale nie jest to jedyna możliwość i stwierdzenie, że rysujemy „linię” trendu, jest niekiedy mylące, bo Excel umożliwia wyznaczanie trendu za pomocą różnych równań. Wystarczy przejść do większej liczby opcji linii trendu (rysunek 5) lub zaznaczyć linię trendu, naciskając Ctrl + 1. Pojawi się okno (rysunek 6), w którym zobaczymy przede wszystkim, jakich równań/funkcji możemy użyć do wyznaczenia trendu naszych danych.
Linia prosta (równanie liniowe) jest domyślną możliwością, ale nie zawsze najlepszą. Możemy rysować linię trendu jako równanie wykładnicze, logarytmiczne, wielomianowe (od kwadratowego do szóstego poziomu), a nawet potęgowe (średnia ruchoma wyznacza średnią z sąsiadujących punktów i jako taka nie jest linią trendu, bo nie umożliwia przedstawienia prognozy – kolejnych punktów na wykresie).
O prognozie za pomocą linii trendu powiemy w dalszej części tekstu, ale najpierw pomówmy o równaniach, bo tylko narysowana linia trendu ułatwia naszemu umysłowi skupienie się na wyraźnym trendzie. Nasz mózg sam próbuje ten trend (wzorzec) znaleźć. Potrzebujemy czegoś bardziej matematycznego, czyli parametrów równania. Służą do tego dwa check-boksy na dole okna Formatowania linii trendu (rysunek 7).
Czego brakuje tym równaniom? Czasami dokładności wyświetlanych parametrów, bo przy wielu rodzajach równań nawet cyfra na szóstym miejscu po przecinku może mieć znaczenie. Poza tym nie widzę braków w tych równaniach – obliczają się błyskawicznie, nawet gdy przeskakujemy pomiędzy różnymi typami, wybierając ten, który najlepiej przybliża nasze dane, a nawet można skopiować równania do komórek Excela.
Jeśli potrzebowalibyśmy dokładnych parametrów równania trendu albo dane często by się zmieniały, można szukać formuł, które je obliczają, ale bez dużej wiedzy z dziedziny statystyki – odradzam. W tym artykule pokażemy tylko funkcje do obliczenia równania liniowego (rysunek 8).
Wróćmy jeszcze do wykresu i równania linii trendu. Co nam ono daje? Statystycznie patrząc, to w naszym przykładzie pozwala nam oszacować, ile godzin muszę się uczyć, żeby np. na egzaminie zdobyć powyżej 80 punktów (okazuje się, że przynajmniej 33 godziny).
Jednak czy rzeczywiście równanie liniowe najlepiej odzwierciedla nasze dane? Na to pytanie spróbuj znaleźć odpowiedź sam, zmieniając typ linii trendu na wykresie i dokładając do niej prognozę. To dwa pola w oknie Formatowania linii trendu, o których jeszcze nie powiedzieliśmy. Mamy możliwość prognozowania do przodu i do tyłu, czyli w naszym przykładzie, gdybyśmy zwiększali i zmniejszali liczbę godzin nauki, przewidywania, jakich wyników na egzaminie byśmy się spodziewali.
Dołożyliśmy po 10 okresów (godzin) w przód i w tył (rysunek 9) i niemal od razu widać, że w tym przykładzie te prognozy nie mają sensu, bo nie jest możliwe uczyć się mniej niż 0 godzin ani zdobyć więcej niż 100 punktów na egzaminie. Dlatego przy prognozowaniu i wybieraniu linii trendu potrzeba odrobiny zdrowego rozsądku (ustalenia założeń do danych, np. punktów granicznych) i testowania różnych rozwiązań.
Arkusz prognozy
Graficzne przedstawianie danych najlepiej przemawia do ludzi. Wiele firm chce też narzędzia do prognozowania przyszłych dochodów, liczby klientów itp. Dlatego w Excelu 2016 Microsoft dołożył na karcie Dane funkcjonalność Arkusz prognozy.
Tym razem wykorzystamy przykład linii lotniczej, gdzie mamy statystykę z ubiegłych lat dotyczącą liczby pasażerów – na jej podstawie chcemy wyznaczyć prognozę. Ważne jest tu, że mamy kolumnę z datą (miesiącem) i odpowiadającą jej liczbę (wartością parametru, który chcemy prognozować). Zaznaczamy obie kolumny danych i klikamy polecenia Arkusz prognozy z karty Dane (rysunek 10).
Zanim wstawimy arkusz prognozy, omówmy jego opcje.
Na samej górze widzimy wykres przedstawiający dane (kolor niebieski) i prognozę (kolor pomarańczowy). Zamiast wykresu liniowego możemy wybrać wykres kolumnowy, ale przeważnie wykres liniowy jest bardziej czytelny w kontekście prognozy oraz dolnej i górnej granicy ufności (o nich za chwilę).
W dalszych działaniach istotne są daty. Najważniejsze, żeby w przygotowanych danych miały one równe odstępy, np. kolejne miesiące, odstępy co siedem dni albo liczbowa oś czasu, czyli ciąg 1, 2, 3 itd.
W opcjach prognozy ustawiamy końcową oraz początkową datę prognozy, czyli określamy, dla jakiego okresu Excel ma obliczyć prognozę.
Kolejnym ważnym punktem jest pole wyboru Przedziału ufności. Jak najbardziej chcemy je mieć, gdyż generuje ono dolną i górną granicę ufności. Co oznacza ta ufność? Każda prognoza jest obarczona pewnym błędem. Im więcej mamy dostępnych danych, z dłuższego okresu, tym nasze wyniki są dokładniejsze, ale nigdy nie dadzą nam pewności, że przyszłość będzie wyglądać tak, jak prognozowaliśmy. Prawie na pewno będą jakieś odchylenia. Na to pozwala nam się przygotować przedział ufności.
Przedział ufności ustawiony na 95% oznacza, że na podstawie dostępnych danych mamy 95% szansy na to, że przyszłe wyniki (w tym przykładzie liczba klientów) uplasują się pomiędzy górną a dolną granicą ufności.
Im większa wartość ufności (maksimum, do którego nigdy nie dojdzie, to 100%), tym szerszy przedział ufności (musimy uwzględniać coraz mniej prawdopodobne wyniki). Im mniejsza wartość ufności (minimum 0%), tym bliżej jesteśmy głównej linii prognozy.
Czasami ważna jest sezonowość. W większości możemy zostawić to Excelowi, który wykryje ją automatycznie, czyli sprawdzi, czy w naszym biznesie występują systematycznie lepsze i gorsze okresy. W podanym przykładzie ta sezonowość jest łatwa do zauważenia – najwięcej ludzi lata w okresie wakacyjnym, a najmniej na początku roku. Excel bez problemu to wykrył i wpisał sezonowość na 12 okresów (12 miesięcy).
Przy sezonowości istotne są daty/okresy, które wpiszemy. Gdybyśmy podliczali klientów co tydzień, to w przykładzie lotniczym sezonowość wyniosłaby 52 (tyle jest mniej więcej tygodni w roku). Maksymalna wartość dla sezonowości to 8760 – liczba godzin w roku.
Uwaga! Jeśli nie uwzględnilibyśmy parametru sezonowości w naszym przykładzie (wpisali ręcznie 0 albo liczbę niezgodną z rzeczywistą sezonowością), granice ufności byłyby znacznie bardziej oddalone od głównej linii prognozy.
Zostały jeszcze dwie istotne linie wyboru. Pierwsza to sposób, w jaki uzupełniamy brakujące dane.
Załóżmy, że w naszym przykładzie część naszych danych uległa korupcji lub zostały usunięte, czyli nie wiemy, jakie powinniśmy tam wpisać dane. Excel może w takiej sytuacji zrobić jedną z dwóch rzeczy – albo interpolować dane, czyli obliczyć średnią z sąsiadujących liczb, albo wstawić 0. W większości sytuacji interpolacja jest właściwym podejściem.
Druga lista określa, jak mają być agregowane (łączone) duplikaty, czy jakie obliczenie ma wykonać Excel, gdy w danych mamy dwie albo więcej liczb dla tej samej daty. Domyślnie jest tu przyjęta średnia. My nie mamy powtarzających się dat, więc możemy zostawić domyślny sposób agregowania duplikatów, ale jeśli w danych trafiłyby się duplikaty, Excel może je połączyć za pomocą funkcji: średniej, policzyć liczby albo wypełnione komórki, wyznaczyć maksymalną bądź minimalną liczbę, a nawet medianę i oczywiście może zsumować wartości dla tej samej daty.
Omówiliśmy całe okno arkusza prognozy (poza polem wyboru Dołącz statystykę, ale ono ma znaczenie tylko wtedy, kiedy jesteś zaawansowany w statystyce). Możemy wreszcie stworzyć arkusz prognozy z wykresem, klikając przycisk Utwórz (rysunek 10).
Excel wstawił nowy arkusz, wstawiając do niego tabelę zawierającą dane historyczne oraz prognozę (rysunek 11). Przy prognozie wykorzystał funkcję REGLINX.ETS, a do wyznaczenia dolnej i górnej granicy ufności była jeszcze potrzebna funkcja REGLINX.ETS.CONFINT. Obie te funkcje są dostępne od Excela 2016. Wcześniej były dostępne tylko podstawowe funkcje prognozujące (m.in. REGLINP, REGLINW i REGLINX).
Podsumowanie
Microsoft zdaje sobie sprawę, że coraz więcej firm interesuje się analizą danych i prognozą przyszłych wyników, dlatego wprowadza narzędzia to umożliwiające od razu z możliwością wizualizacji wyników (m.in. arkusz prognozy). Ważne, że narzędzia tworzone przez Microsoft są proste i szybko przedstawiają wyniki (tego oczekuje większość użytkowników).
Dla specjalistów od statystyk są jeszcze dostępne takie dodatki , jak Analysis ToolPak, i bezpośrednie korzystanie z zaawansowanych funkcji.
Przypisy / Źródła / Podstawa prawna
Źródło wszystkich rysunków: opracowanie własne.
Możesz zobaczyć ten artykuł, jak i wiele innych w naszym portalu Controlling 24. Wystarczy, że klikniesz tutaj.
