Strona główna  »  Excel w finansach  »  Porady  »  Prognozowanie - trend sezonowy

Prognozowanie - trend sezonowy

Wiele produktów sprzedaje się w różnych wielkościach zależnie od pory roku np. lody, napoje chłodzące, odzież zimowa, budownictwo. 

Przykład

A B C D E F G
1 n sprzedaż czas 1kw 2kw 3kw 4kw
2 1 2,798 1 1 0 0 0
3 2 1,850 2 0 1 0 0
4 3 4,150 3 0 0 1 0
5 4 4,800 4 0 0 0 1
6 5 4,060 5 1 0 0 0
7 6 2,726 6 0 1 0 0
8 7 5,149 7 0 0 1 0
9 8 6,521 8 0 0 0 1
10 9 5,679 9 1 0 0 0
11 10 4,420 10 0 1 0 0
12 11 7,962 11 0 0 1 0
13 12 9,540 12 0 0 0 1
14 13 8,638 13 1 0 0 0
15 14 6,540 14 0 1 0 0
16 15 9,302 15 0 0 1 0
17 16 11,690 16 0 0 0 1
18 17 8,884 17 1 0 0 0
19 18 8,540 18 0 1 0 0
20 19 12,800 19 0 0 1 0
21 20 13,850 20 0 0 0 1

Tabela przedstawia sprzedaż kwartalną np. w milionach. W tym przykładzie "n" i "czas" są tożsame i oznaczają kolejne kwartały. 

Prognoza na wykresie punktowym 

 x - n;

 y - sprzedaż. 

Zaznacz dane - kliknij na dane, utwórz wykres liniowy. Teraz z menu Wykres wybierz opcję Dodaj linię trendu. Pojawi się następujące okno:

Kliknij liniowy, następnie drugą zakładkę - opcje.

Zaznacz Wyświetl równanie na wykresie oraz Wyświetl wartość R-kwadrat na wykresie.

Powinieneś otrzymać następującą wersję wykresu.

Równanie trendu to funkcja liniowa y = a*x + b

R2 pokazuje jak dobrze dane estymowane są dopasowane do danych rzeczywistych. Maksymalna wartość R2 może być 1, ale nigdy nie jest, bo estymujemy, a dane rzeczywiste mają składnik losowy. Jeśli R2 wynosi ponad 0,8 można uznać trend za jakoś dopasowany

Prognozowanie 

Na początku należy dorobić tabelę z jedynkami i zerami jak na górze. (Dodaj czas, w wierszu danych dla 1 kwartału i kolumnie 1 kwartału daj 1, w pozostałych kolumnach tego samego wiersza daj 0).

Z menu Narzędzia > Dodatki wybierz Analiza danych (może zajdzie konieczność doinstalowania tej opcji). Pojawi się okno:

Kliknij Regresja i OK. Pojawi się kolejne okno, którego zawartość wypełnij następująco:

Zakres wyjściowy to jest komórka pod tabelą, (pamiętaj żeby tam nic nie było). Program wygeneruje następującą tabelę 

- lewa-górna komórka to jest A22.

PODSUMOWANIE - WYJŚCIE          
               
Statystyki regresji              
Wielokrotność R 0,984208              
R kwadrat 0,968666              
Dopasowany R kwadrat 0,957475              
Błąd standardowy 0,701959              
Obserwacje 20              
               
ANALIZA WARIANCJI            
  df SS MS F Istotność F      
Regresja 5 213,2611 42,65223 86,56011 5,06E-10      
Resztkowy 14 6,898457 0,492747          
Razem 19 220,1596            
               
  Współczynniki Błąd standardowy t Stat Wartość-p Dolne 95% Górne 95% Dolne 95,0% Górne 95,0%
Przecięcie 9,38025 23553847 3,98E-07 1 -5,1E+07 50518031 -5,1E+07 50518031
czas 0,491663 0,027747 17,71924 5,51E-11 0,43215 0,551175 0,43215 0,551175
1kw -7,79341 23553847 -3,3E-07 1 -5,1E+07 50518014 -5,1E+07 50518014
2kw -9,48168 23553847 -4E-07 1 -5,1E+07 50518012 -5,1E+07 50518012
3kw -6,91594 23553847 -2,9E-07 1 -5,1E+07 50518015 -5,1E+07 50518015
4kw -6 23553847 -2,5E-07 1 -5,1E+07 50518016 -5,1E+07 50518016

Do prognozowania potrzebne są tylko kolorowe komórki.

Tabela danych wyjściowych to model regresji wielokrotnej z danymi zero-jedynkowymi. 

Regresja wielokrotna ma postać: y = b0 + a1*x1+a2*x2+a3*x3+...+an*xn + składnik losowy  [u nas y - przychody, x1 - czas, xn - jedynki i zera]

U nas b0 to jest przecięcie, a1 to jest parametr przy czasie... itd., nie mamy składnika losowego. To jest różnica pomiędzy danymi rzeczywistymi i danymi estymowanynymi.

n sprzedaż czas 1kw 2kw 3kw 4kw

H1

Przecięcie 9,38025
1 2,798 1 1 0 0 0 2,079 czas 0,491663
2 1,850 2 0 1 0 0 0,882 1kw -7,79341
3 4,150 3 0 0 1 0 3,939 2kw -9,48168
4 4,800 4 0 0 0 1 5,347 3kw -6,91594
5 4,060 5 1 0 0 0 4,045 4kw -6

Równanie w komórce H2 (pod zaznaczoną H1) jest takie:

H2=$J$1+C2*$J$2+D2*$J$3+E2*$J$4+F2*$J$5+G2*$J$6

Pociągnij w dół, by otrzymać estymację regresji. Z R2 = 0,968 widać, że estymacja jest pięknie dopasowana. Można to ilustrować wykresem.

2001-03-23