Historyczne dane zbierane w Excelu to cenne źródło informacji o wielu aspektach działalności firmy ale to co nas naprawdę interesuje to problem jak na ich podstawie przewidzieć przyszłość. Kiedy skończą się zapasy? Ile towarów zamówić? Czy będziemy mieli zysk? Kiedy zakończy się produkcja jakiejś partii urządzeń? To są kluczowe pytania z którymi musi zmierzyć się menadżer. Oczywiście matematycy już dawno rozpracowali ten problem i wymyślili pojęcie regresji jako metody umożliwiającej przewidywanie nieznanych wartości jednych wielkości na podstawie znanych wartości innych. Podstawowym jej elementem jest odpowiedni dobór funkcji opisującej nasze zależności. W dużej ilości przypadków wziętych z życia funkcja ta jest liniowa i na takiej się dzisiaj skupimy niemniej Excel umożliwia również użycie funkcji wykładniczej, logarytmicznej, potęgowej i wielomianowej.
W naszym przykładzie posłużymy się danymi Sprzedaży i Kosztów pewnej firmy w okresie ostatnich 18 miesięcy.
……………………..
.
Patrząc na te dane na pierwszy rzut oka trudno określić czy za 2 lata Sprzedaż nadal będzie przewyższać Koszty. Widać, że oba parametry rosną ale czy równomiernie? Zróbmy sobie wykres Sprzedaży i Kosztów w czasie. Zaznaczmy tabelę a następnie wybierzmy Menu Wstawianie -> Wstaw wykres kolumnowy lub słupkowy -> Kolumnowy grupowany
Uzyskamy następujący efekt
Teraz może bardziej widać trend rosnący obu wielkości ale nadal niewiele z tego wynika. Najpierw pozbądźmy się miesięcy z naszego wykresu wchodząc w Menu Projekt wykresu -> Zaznacz dane
oraz odznaczając Miesiąc
Teraz do naszego wykresu dodamy linie trendu wybierając Menu Projekt wykresu -> Dodaj element wykresu
oraz wybór Linia trendu -> Prognoza liniowa
Teraz musimy wybrać której wielkości ma dotyczyć nasza linia trendu wybieramy Sprzedaż a za drugim razem Koszty.
I otrzymujemy następujący wykres
Teraz już na pierwszy rzut oka widać, że nie jest dobrze bo nasze Koszty rosną zdecydowanie szybciej niż Sprzedaż. Ok ale z wykresu nadal nie wynika kiedy dopadnie nas ujemny wynik czyli kiedy teoretycznie te linie się przetną.
Aby to zobaczyć kliknijmy dwa razy w naszą linię trendu Kosztów, pojawi nam się menu w którym w pozycji Prognoza -> Do przodu wpiszmy 20 miesięcy
I analogiczne czynności wykonajmy dla linii trendu Sprzedaży. Pojawi nam się wykres jak poniżej
Z którego już jasno wynika, że nieszczęścia można się spodziewać około 35 miesiąca.
Można zapytać czy istnieje bardziej precyzyjna lub prostsza metoda niż rysowanie wykresu.
Możemy skorzystać bezpośrednio z wzorów na regresję i wyliczyć na ich podstawie dokładny punkt przecięcia obu linii. W tym celu wykorzystamy funkcję liczącą parametry regresji liniowej dla naszych wartości Sprzedaży i Kosztów. Będą to odpowiednio:
=REGLINP(Tabela1[Sprzedaż];Tabela1[Miesiąc])
oraz
=REGLINP(Tabela1[Koszty];Tabela1[Miesiąc])
Stosując te funkcje otrzymujemy parametry funkcji liniowej opisanej wzorem Y=mX+b gdzie wartości m i b wylicza nasza funkcja, X to miesiące a Y to odpowiednio Sprzedaż lub Koszty. Czyli dla punkty w którym Sprzedaż jest równa kosztom otrzymujemy równanie m1*X+b1=m2*X+b2 co po przekształceniu daje nam wynik
X=(b2-b1)/(m1-m2) co w naszym przypadku daje 34,8 i jest zgodne z wykresem.
Pamiętajcie proszę, że to bardzo prosty model linowy a wartości są czysto teoretyczne wynikające z przyjętego uproszczenia matematycznego niemniej mogą one być bardzo pomocne w podejmowaniu odpowiednio wcześnie niezbędnych działań.
Gdybyście w swojej pracy potrzebowali bardziej złożonych modeli matematycznych lub mieli inne zagadnienia związane z planowaniem przyszłości zapraszam do kontaktu na adres konrad.pogodz@itch.pl
Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.
