Přímková regrese INTERCEPT() a SLOPE()

V předchozím článku jsem představil funkci FORECAST.LINEAR(), která se používá pro předpověď budoucí hodnoty sledované proměnné. Praktickou aplikací funkce byla předpověď budoucí hodnoty spotřebních výdajů na základě lineárního vztahu s příjmem spotřebitele.

Lineární vztah mezi proměnnými bude nyní explicitně vypočten pro lineární regresní přímku ve tvaru

K odhadu parametrů b₀ a b₁ regresní přímky použijeme funkce INTERCEPT() a SLOPE().

Funkce INTERCEPT()

Funkce vrátí hodnotu úrovňové konstanty neboli absolutního členu přímky. Úrovňová konstanta určuje bod, ve kterém lineární funkce protíná osu y, tj. x = 0.

=INTERCEPT(pole_y; pole_x)

Parametr Pole_y slouží k zadání rozsahu buněk, které obsahují empirické hodnoty závisle proměnné. Parametr funkce Pole_x slouží pro zadání empirických hodnot nezávisle proměnné.

Funkce SLOPE()

Funkce vrátí směrnici (sklon) regresní přímky mezi 2 body.

=SLOPE(pole_y; pole_x)

Parametry funkce mají identický význam jako u předchozí funkce.

Užití funkcí v praxi

Úkolem je kvantifikovat pomocí regresního modelu vztah mezi průměrnou měsíční spotřebou a průměrným měsíčním příjmem spotřebitele. Nezávisle proměnnou x je čistý příjem a závisle proměnnou y je spotřeba. Rozsah s hodnotami nezávisle proměnné vložíme do parametru funkci Pole_x a rozsah s hodnotami závisle proměnné vložíme do parametru Pole_y.

Obecně lze vztah mezi 2 proměnnými identifikovat pomocí bodového grafu. Body v grafu ukazují na již zmíněnou lineární závislost.

Na následujícím obrázku jsou odhadnuty parametry b₀, b₁ lineárního regresního modelu pomocí excelovských funkcí

Interpretace parametrů modelu

Pomocí funkce INTERCEPT() je odhadnuta úrovňová konstanta 21,951 a pomocí funkce SLOPE() sklon regresní přímky 0,446

Odhadnutý lineární regresní model spotřeby představuje krátkodobou spotřební funkci. Při b₁ = 0 by byly spotřební výdaje spotřebitele rovny 21,951 tisíc Kč. Jedná se o fixní výdaje, které nejsou závislé na výši příjmu spotřebitele (např. nájemné, zálohy na plyn, elektřinu apod.). Parametr 0,466 udává, že z každé dodatečné 1 Kč příjmu je vynaloženo 0,47 halířů na dodatečné spotřební výdaje. Zbývající část, tj. 1 - 0,47 = 0,53 halířů, je uspořena.

Vyrovnané body regresního modelu

Na následujícím grafu jsou body proloženy odhadnutou regresní přímkou. Graf je kombinací bodového grafu XY a spojnicového grafu.

Předpověď budoucí spotřeby

Prostým dosazením očekávané hodnoty průměrného měsíčního příjmu ve výši 68,0 tisíc Kč za proměnnou x získáme předpověď průměrné měsíční spotřeby pro rok 2018 ve výši 52,3 tis. Kč.

Porovnejte předpověď spotřebních výdajů pro rok 2018 s výsledkem funkce FORECAST.LINEAR() z předchozího článku.