Prognozowanie w Excelu

Tylko on-line nr 12/2018

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).

Dostęp możliwy dla zalogowanych użytkowników serwisu. Jeśli posiadasz aktywną prenumeratę przejdź do LOGOWANIA. Jeśli nie jesteś jeszcze naszym Czytelnikiem wybierz najkorzystniejszy WARIANT PRENUMERATY.

Zaloguj Zamów prenumeratę

Źródło: Finanse i Controlling nr 56/2018

Ulubione Drukuj

Zobacz również

Czy dane finansowe można wizualizować? Analiza wizualna i KPI finansowe w Tableau

INFO_22_74.jpg

Często spotykam się z klientami, którzy postanowili rozpocząć swoją przygodę z rozwiązaniami Business Intelligence. W przypadku danych sprzedażowych nikt nie ma wątpliwości, że można je prezentować wizualnie. Jednak dość powszechna jest opinia, że dane finansowe można przedstawiać wyłącznie w postaci tabel. Wynika to z faktu, że często w tego rodzaju danych porównujemy wskaźniki w czasie, zestawiamy je z innymi wskaźnikami i badamy ich udziały. Czy rzeczywiście tak jest?

Czytaj więcej

Mapa procesu oraz pomiar wartości dla jego elementów

INFO_22_62.jpg

Pomiar strumienia wartości to dość popularna metoda kontroli procesów, np. produkcyjnych. Warto jednak spojrzeć na mapę procesu i generowane w jego efekcie wartości z punktu widzenia modelu biznesowego i towarzyszących mu wskaźników. Umożliwi to rzetelne podejście do analizy.

Czytaj więcej

Graficzna prezentacja danych sprzedażowych

INFO_22_56.jpg

Dane sprzedażowe bardzo często pojawiają się w programie Excel. Oprócz tego, że pojawiają się w wersji stricte analitycznej, często trzeba też dokonać ich wizualizacji i przedstawić na konkretnym wykresie. Co prawda narzędzi do wizualizacji danych jest coraz więcej i czasami wykonuje się je w innych programach, np. w Power BI Desktop, jednak w niniejszym artykule skupię się na możliwościach samego programu Excel w tym zakresie.

Czytaj więcej

Numer bieżący

Przejdź do

Partnerzy

Reklama