|
Prognozowanie - trend sezonowy
Autor porady: Andrzej Janowski (janowski11@poczta.onet.pl)
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.
|
statystyki zapewnia 