Controlling & Finančná analýza | MS Excel

Využitie regresie na plánovanie čiastočne variabilných nákladov

Náklady, ktoré nie sú ani úplne fixné, ani úplne variabilné, je možné jednoduchým spôsobom odhadnúť pomocou regresie

10.11.2013 | Zdroj: QUADRICA

Šimon Bečica, Investičný analytik, QUADRICA Private Equity 

Niektoré druhy nákladov majú „semi-variabilnú“ povahu a nie je jednoduché ich spoľahlivo plánovať. Typickým príkladom sú náklady na energie vo výrobnom závode. Napríklad časť elektrickej energie sa spotrebúva na osvetlenie a pohon strojov a zariadení v čase medzi výrobnými cyklami, a  v čase výrobného cyklu sa spotreba zvýši. V takýchto prípadoch býva zložité odhadnúť, aká časť nákladov má fixnú povahu (t.j. vzniká bez ohľadu na vyrobené množstvo) a aká časť má variabilnú povahu.

Ak by sme celé náklady považovali za variabilné a plánovali ich ako variabilný náklad na jednotku výroby, pri plánovanom náraste výroby by sme náklady nadhodnotili a pri plánovanom poklese zase podhodnotili. Ak by sme naopak ponechali náklady ako fixné počas celého plánovaného obdobia, vznikol by opačný problém.

Jedným zo spôsobov, ako zjednodušene odhadnúť vzťah medzi objemom výroby a fixnou a variabilnou zložkou nákladov, je regresná analýza. Neposkytne samozrejme úplne presnú odpoveď, ale zároveň poskytne informáciu, do akej miery je regresia spoľahlivá a aké odchýlky môžeme pri jej použití očakávať, na základe čoho sa môžeme rozhodnúť, či je použiteľná pre účely plánovania.

Na výpočet regresie sú potrebné historické dáta za obdobie, ktoré poskytne dostatočný počet pozorovaní na spoľahlivú kalkuláciu. Vysvetlime to na ilustratívnom príklade. Firma má k dispozícii nasledovné informácie o počte vyrobených kusov a spotrebe elektrickej energie za predchádzajúci rok:

Z grafu je vidno, že medzi objemom výroby a nákladmi je určitá korelácia, ale relatívne (percentuálne) zmeny v objeme výroby sú väčšie, ako zmeny v spotrebe energie, čo dokazuje, že spotreba elektrickej energie má semi-variabilný charakter.

Lineárna regresná analýza vyhodnotí, aká je medzi radmi údajov korelácia a vypočíta parametre lineárnej rovnice y = a + bx, ktorá popisuje vzťah závislej premennej y (v našom prípade náklady) a nezávislej premennej x (objem výroby). Lineárna regresia predpokladá, že medzi objemom výroby a nákladmi existuje lineárny vzťah.

Regresia zobrazí pre každý mesiac hodnoty, ktoré boli vypočítané na základe identifikovanej lineárnej rovnice (na grafe nižšie červené body) a skutočne namerané mesačné náklady (modré body). Rozdiely medzi medzi týmito hodnotami sa nazývajú odchýlky (residuals):

Regresná analýza zároveň poskytne v exceli štandardizovaný výstup, ktorý je znázornený nižšie (pre lepšiu prehľadnosť je výstup sformátovaný a doplnený o komentáre v slovenčine, a najdôležitejšie parametre sú farebne zvýraznené). 

Medzi objemom výroby a nákladmi na energie existuje korelácia v hodnote 0,878 (korelácia sa pohybuje vždy od -1 po +1). Regresia vypočítala koeficient a (intercept - fixnú časť nákladov) vo výške 83,2 tis. EUR a koeficient b (variabilnú zložku, ktorou sa v rovnici násobí objem výroby, t.j. variabilný náklad na jednotku) vo výške 0,15741 EUR na kus.  

Napríklad pre výpočet nákladov v máji dosadíme do rovnice:

Vypočítaná hodnota sa od skutočne nameranej hodnoty v máji (175,4 tis. EUR) líši o 1,8 tis. EUR. 

Štandardná odchýlka (standard error) vypočítaných hodnôt od skutočných hodnôt je 6,8 tis. EUR. Regresná kalkulácia poskytne aj graf a tabuľku s hodnotami odchýlok (residuals). Zistená odchýlka sa pri väčšine pozorovaní pohybuje v rozmedzí od -6,3 tis. EUR po +3,8 tis. EUR, čo predstavuje -4,0% až +2,3% skutočných nákladov. Väčšia odchýlka vznikla iba v marci pri objeme výroby 482 tis. ks, a to až +17,6 tis. EUR.

O spoľahlivosti regresie hovorí viacero parametrov. Okrem vyššie popísanej štandardnej odchýlky je to koeficient R Square (koeficient determinácie), ktorý ukazuje, koľko percent celkovej variácie nákladov je vysvetlených regresiou. V našom prípade to je 77%. Čím bližšie k 1, tým lepšie regresia opisuje skutočné hodnoty nákladov.

O spoľahlivosti koeficientov a a b hovoria hodnoty P-value, t.j. pravdepodobnosti, že koeficienty nie sú štatisticky významné; inými slovami je to pravdepodobnosť, že nájdeme rovnako vysokú hodnotu nákladov vo vzorke náhodných výsledkov, v ktorých premenná nemá nijaký vplyv. Aby sme si boli istí, že premenná pri danom koeficiente skutočne ovplyvňuje úroveň nákladov, tieto pravdepodobnosti by mali byť čo najmenšie. Koeficienty s hodnotami vyššími ako 0,1 (10%) by nemali byť použité. V našom prípade sú menšie ako 1%.

Posledné dva stĺpce "Lower 95%" a "Upper 95%" definujú hranice 95% intervalu spolahlivosti pre jednotlivé koeficienty; teda je 95% pravdepodobnosť, že skutočná hodnota koeficientu leží medzi hornou a dolnou hranicou intervalu. Tu platí pravidlo čím užší interval, tým lepšie.

V našom prípade poskytla regresia vo väčšine mesiacov hodnotu, ktorá sa od skutočných nákladov líši o niekoľko percent, hoci marcový výkyv spoľahlivosť regresie znižuje (možno by stálo za to zistiť, čo sa v marci vlastne stalo, keď náklady na energiu vzrástli relatívne viac, ako môžeme pozorovať v ostatných mesiacoch – možno sa jednalo o poruchu na zariadeniach). Vypočítané koeficienty a a b sú na základe hodnoty P-value spoľahlivé.  

Pre účely regresie v prípade energií je samozrejme možné použiť nielen náklady v EUR (ako premennú y), ale aj spotrebované kWh či m3. Nakoľko je ich však potrebné následne konvertovať do peňažného vyjadrenia, a zmluvy o dodávke energií často obsahujú komplikované cenové mechanizmy s množstvom fixných a variabilných poplatkov, môže byť jednoduchšie v regresii použiť priamo údaje o nákladoch.

Pre použitie regresnej analýzy je potrebné v Exceli túto doplnkovú funkciu nainštalovať z doplnkov Analytické nástroje. Ak máte Excel 2010, kliknite v otvorenom súbore na záložku „Súbor“ vľavo hore, potom vyberte „Možnosti“ a následne „Doplnky“. V ponuke vyberte „Analytické nástroje“ a kliknite na tlačítko „Spustiť...“ v dolnej časti okna. Následne sa vám na hornej lište v záložke „Údaje“ úplne vpravo objaví možnosť „Data analysis“.  

Odporúčania na záver

• Ak viete, že vstupné údaje boli v niektorých mesiacoch skreslené (napr. ako vyššie spomenutá porucha, ktorá viedla k netypickému správaniu nákladov), upravte ich alebo ich z výpočtu vynechajte
  
• Regresnú analýzu pravidelne aktualizujte dopĺňaním aktuálnych dát
  

QUADRICA Private Equity je manažérom fondu rizikového kapitálu, ktorý investuje do malých a stredne veľkých spoločností pôsobiacich na Slovensku.