Spolehlivost trendů (předpovědí)

Při použití trendů nezapomínejme na jedno. Výsledkem trendu je jenom matematická předpověď a ne stoprocentní jistota. Trend předpovídá budoucí vývoj na základě vývoje minulého. Ovšem každý jev je ovlivňovaný několika dalšími jevy, a tím můžeme naše předpovědi zpřesnit. 

Funkce Lintrend s více proměnnými

Výhodou funkce Lintrend je, že v její předpovědi budoucího stavu můžeme zahrnout několik proměnných. To obdobná funkce Forecast neumí. Proto se v našem kurzu zaměřujeme jenom na funkci Lintrend.

Podívejme se teď podrobněji na funkci Lintrend. Když si zobrazíme okno Argumenty funkce Lintrend, tak zjistíme, že tam jsou čtyři pole pro zadání dat:

Excel - lintrend

Jako první je potřeba vyplnit Pole y. Do tohoto pole zadáváme data, jejíchž vývoj chceme předpovědět. Např. budeme chtít předpovědět výši tržeb. V tom případě zde zadáme oblast buněk, ve kterých máme zaznamenaný vývoj tržeb v minulých měsících.

Jako druhé je potřeba vyplnit Pole x. Do tohoto pole zadáváme data, která svým vývojem ovlivňují vývoj těch dat, jejichž budoucí předpověď chceme získat. Použití tohoto pole se názorně ukážeme v této kapitole. 

Za třetí je potřeba vyplnit pole Nová x. Zde zadáváme, co vlastně chceme předpověď. Např. můžeme chtít předpověď výši tržeb pro sedmý měsíc.

Použití více proměnných ve funkci Lintrend si ukážeme na praktickém příkladě. Předpokládejme, že známe tržby za posledních šest měsíců a chceme předpovědět tržby pro měsíc sedmý. Připravte si v Excelu tabulku podle níže uvedeného vozoru:

Excel - lintrend

Nejdříve si na zkoušku vytvoříme odhad tržeb pro sedmý měsíc, aniž bychom do výpočtu zahrnuli i jakoukoliv jinou proměnnou. V našem případě jsme do předpovědi nezahrnuli proměnnou Počet prodejců. K této předpovědi použijeme vzorec ve tvaru =ZAOKR.NAHORU(LINTREND(B3:B8;:A12);1). Tedy použijeme funkci Lintrend tak, jak jsme se ji naučili používat v předchozích kapitolách:

Excel - lintrend

Odhad tržeb pro sedmý měsíc je 1928 (je použité zaokrouhlení na celé číslo nahoru). Co jsme vlastně vypočítali? Jenom jsme na základě minulých dat (1000, 1200, ..., 1580 a 1800) předpověděli lineární vývoj pro sedmý měsíc. Proto je ve vzorci pro hodnotu Nová x uvedena adresa buňky A12 - v této adrese máme uvedeno, že chceme předpověď pro sedmý měsíc.

Ovšem jaký bude odhad tržeb pro sedmý měsíc v situaci, kdy budeme vědět, že v sedmém měsíci budeme mít jenom 20 prodejců. Logickou úvahou musí být odhad tržeb nižší než 1800. Částky 1800 jsme dosáhli v šestém měsíci s pomocí 23 prodejců. V sedmém měsíci budeme mít o tři prodejce méně. 

Pokud do výpočtu předpovědi nezahrneme skutečnost, že výše tržeb je závislá na počtu obchodníků, tak nám funkce Lintrend předpoví tržby za sedmý měsíc zase ve výši 1928. Předpověď rozšíříme o obchodníky tak, že do Pole x zadáme oblast buněk C3:C8. V této oblasti máme zapsaný vývoj počtu prodejců. Zároveň musíme ve vzorci upravit hodnotu Nová x. Teď už se neptáme, jaký je odhad tržeb v sedném měsíci, ale ptáme se, jaký bude odhad tržeb s dvaceti prodejci. Proto do hodnoty Nová x zadáme adresu C9. V této buňce máme uvedeno, že v sedmém měsíci budeme mít jenom 20 prodejců. Do buňky C13 si vložte následující vzorec =ZAOKR.NAHORU(LINTREND(B3:B8;C3:C8;C9);1) a výsledek by měl vypadat následovně:

Excel - lintrend

Funkce Lintrend nám předpověděla, že s dvaceti prodejci v sedmém měsíci budeme mít s nejvyšší pravděpodobností tržby ve výši 1688.

Tím možnosti funkce Lintrend nekončí. Podobných proměnných můžeme ve funkci Lintrend zadat více, a tím i více zpřesnit budoucí předpověď. Samozřejmě za předpokladu, že tyto nové proměnné budou ovlivňovat třeba v našem případě výši tržeb. Předpokládejme, že naše tržby ovlivňuje nejen počet prodejců, ale i počet prodejen. Upravte si tabulku podle níže uvedeného vzoru:

Excel - lintrend

Předpokládejme, že v sedmém měsíci budeme mít 45 prodejen a 20 prodejců. Funkci Lintrend použijeme stejně jako v předchozím případě. Jenom rozšíříme Pole x i o prodejny - zadáme oblast buněk C3:D8. To samé se týká i hodnoty Nová x - zadáme oblast buněk C9:D9. Vzorec po úpravách má následující tvar =ZAOKR.NAHORU(LINTREND(B3:B8;C3:D8;C9:D9);1):

Excel - lintrend

Výsledkem je, že s 20 prodejci a 45 prodejnami budeme mít v sedmém měsíci tržby pravděpodobně ve výši 1922.

Podobným způsobem můžeme funkci Lintrend rozšířit o další proměnné, které ovlivňují výši tržeb.

Příklad si uložte, protože s ním budeme pracovat i v dalších kapitolách.

Koeficient determinace R

Každá předpověď má svoji spolehlivost. Spolehlivost znamená, do jaké míry můžeme předpovědi věřit. My se naučíme posoudit spolehlivost pomocí koeficientu determinace R. Koeficient determinace R vypočítáme pomocí funkce Linregrese.

Už jsme se naučili ve funkci Lintrend použití více proměnných, ale zatím nevíme, zda se tyto proměnné ovlivňují. To nám prozradí právě koeficient determinace.

My si spočítáme koeficient determinace R na našem příkladu s odhadem tržeb pro sedmý měsíc. Podle níže uvedeného vzoru si připravte v Excelu novou tabulku na novém listě (původní uloženou tabulku s příkladem neměňte ani nemažte, protože s ní budeme v další kapitole pracovat):

Excel - linregrese

Označte si oblast buněk např. B11 až C14 (zde si vložíme výsledek funkce Linregrese):

Excel - linregrese

Po označení oblasti si napište vzorec ve tvaru:

=LINREGRESE(B3:B9;C3:C9;PRAVDA;PRAVDA).

Vzorec je nutné zadat jako maticový kombinací kláves Ctrl+Shift+Enter:

Excel - linregrese

Po stisknutí kombinace kláves se nám v označené oblasti zobrazí výsledky funkce Linregrese. Pro nás je v tuto chvíli nejdůležitější hodnota koeficientu determinace R:

Excel - linregrese

V našem případě činí koeficient spolehlivosti 0,9937976. To je velmi dobrý výsledek, který ukazuje na velmi silnou závislost. Tedy že je mezi proměnnými skutečně silný vztah. Obecně se dá říct, že čím více se koeficient determinace blíží k hodnotě 1, tím je výsledek spolehlivější. Zjednodušeně můžeme říct, že výše tržeb je silně ovlivněna počtem prodejců a prodejen. 

Více informací k Excelu

CopyRights Fotis Fotopulos, 2010
Obrázky a animace CopyRights Fotis Fotopulos, 2010

Všechna práva vyhrazena. Žádná část této publikace se nesmí reprodukovat, ukládat do informačních systémů či rozšiřovat jakýmkoliv způsobem, ať již polygraficky, fotografickou reprodukcí, elektronicky, audio a video cestou, ani jinými prostředky bez písemného souhlasu majitele práv.

Výpis návodů pro Excel
Zajimavé příručky
Povinné ručení iPojištění.cz