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