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

Ako analyzovať a plánovať sezónne údaje

Jednoduchý spôsob, ako vypočítať sezónny index, očistiť údaje od sezónnosti a pripraviť plán, ktorý zohľadňuje śezónnosť

28.11.2013 | Zdroj: CFO.sk

CFO.sk 

Ak tržby alebo iné premenné (napr. niektoré ceny komodít) vykazujú sezónnosť, možno pri plánovaní postupovať nasledovne:

1. V časovom rade historických hodnôt sa ku každej hodnote vypočíta sezónny index.
   
2. Ak máte k dispozícii hodnoty za dlhšie časové obdobie, tieto indexy je možné spriemerovať (napr. vypočítať priemerný sezónnny index tržieb z januárových tržieb za posledné 3 roky).
   
3. Skutočné hodnoty očistíme o sezónnosť a získame časový rad, ktorý nie je skreslený sezónnosťou. Z neho môžeme identifikovať trend, napr. či očistené tržby majú rastúcu alebo klesajúcu tendenciu.
   
4. Z očistených hodnôt vytvoríme plán, buď pomocou extrapolácie trendu, alebo iným spôsobom (napr. ak predpokladáme, že na budúci rok celkové tržby stúpnu o 5%, zohľadníme tento nárast v pláne očistených hodnôt).
   
5. Na plánované očistené hodnoty aplikujeme (priemerný) sezónny index vypočítaný v kroku 2.
   

Vysvetlime výpočet na príklade. Graf nižšie zobrazuje vývoj tržieb turistického zariadenia za posledné tri roky. Tržby rastú v zimných a letných mesiacoch, a v medziobdobí sú nižšie.

Krok 1

V Exceli chronologicky zoradíme tržby v jednotlivých mesiacoch.

(Poznámka: červená čiara na riadku 17 zobrazuje riadky, ktoré boli pre prehľadnosť prezentácie skryté).

V prvom kroku je potrebné pre každý mesiac vypočítať sezónny index. Ten získame, ak skutočnú hodnotu vydelíme priemerom tržieb za 12 mesiacov. Napríklad sezónny index za august 2011 môžeme vypočítať nasledovne:

• D10=C10/AVERAGE(C4:C15), alebo
  
• Presnejší výpočet D10=C10/((AVERAGE(C4:C15)+AVERAGE(C5:C16))/2), ktorý zohľadňuje to, že v roku je párny počet mesiacov a správne vypočítaný sezónny index by mal preto mať v menovateli priemer z priemerných tržieb za feb11-jan12 a priemerných tržieb za mar11-feb12
  

Krok 2

V príklade máme k dispozícii mesačné údaje za 3 roky, čo môžeme využiť a vypočítať priemerný sezónny index. Napríklad priemerný sezónny index za február bude predstavovať priemer indexov za feb12 a feb13, ktoré máme k dispozícii – viď výpočet v bunke H5. Keďže súčet indexov za všetkých 12 mesiacov by sa mal rovnať 12 (1200%), tak v stĺpci I prevážime index v každom mesiaci tak, aby ich súčet predstavoval 1200% (napr. I5=H5*1200%/$H$16).  Stĺpec I ukazuje priemerný sezónny index, ktorý použijeme v nasledovných výpočtoch.

Krok 3

Keď máme vypočítaný priemerný sezónny index pre každý kalendárny mesiac v roku, skutočné tržby za jednotlivé mesiac očistíme od sezónnosti tak, že tieto tržby vydelíme príslušným priemernýmm sezónnym indexom. Výsedok je zobrazený v stĺpci E:

Krok 4

Z časového radu očistených mesačných tržieb vidno, že ukazujú stabilný rastúci trend. Ak predpokladáme, že tento trend bude pokračovať aj v roku 2014, môžeme použiť funciu TREND na vypočítanie tržieb v januári až decembri 2014. Výpočet plánovaných tržieb v januári 2014 vyzerá nasledovne:

E39=TREND($E$3:E38;$B$3:B38;B39)

Funkciu skopírujeme do buniek E40 až E50.

Krok 5

Na plánované tržby, ktoré sú očistené od sezónnosti, aplikujeme priemerný sezónny index vypočítaný v kroku 2. Napríklad plán tržieb v januári 2014 získame prenásobením očistených plánovaných tržieb v januári priemerným sezónnym indexom v januári:

1 457 = 1 277 x 114,1%

Ak chcete váš výpočet vyšperkovať a viac zautomatizovať, odporúčame tento link.