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