Analýza citlivosti v Exceli (vzorový údajový list)

Procesy v oblasti financií sú vždy prepojené – jeden faktor závisí od druhého a mení sa s ním. Sledujte tieto zmeny a pochopte, čo môžete očakávať v budúcnosti, možno pomocou funkcií Excelu a tabuľkových metód.

Získanie viacerých výsledkov pomocou tabuľky s údajmi

Možnosti údajových hárkov sú prvky analýzy typu „čo keby“ – často sa to robí prostredníctvom programu Microsoft Excel. Toto je druhý názov pre analýzu citlivosti.

Prehľad

Dátová tabuľka je typ rozsahu buniek, ktorý možno použiť na riešenie problémov zmenou hodnôt v niektorých bunkách. Vytvára sa, keď je potrebné sledovať zmeny v komponentoch vzorca a prijímať aktualizácie výsledkov podľa týchto zmien. Poďme zistiť, ako používať tabuľky s údajmi vo výskume a aké typy sú.

Základy údajových tabuliek

Existujú dva typy údajových tabuliek, líšia sa počtom komponentov. Musíte zostaviť tabuľku so zameraním na počet hodnôt uXNUMXbuXNUMXb, ktoré s ňou musíte skontrolovať.

Štatistici používajú tabuľku s jednou premennou, keď je v jednom alebo viacerých výrazoch iba jedna premenná, ktorá môže zmeniť ich výsledok. Často sa napríklad používa v spojení s funkciou PMT. Vzorec je určený na výpočet výšky pravidelnej platby a zohľadňuje úrokovú sadzbu uvedenú v zmluve. Pri takýchto výpočtoch sa premenné zapisujú do jedného stĺpca a výsledky výpočtov do druhého. Príklad údajového štítku s 1 premennou:

Ďalej zvážte dosky s 2 premennými. Používajú sa v prípadoch, keď na zmenu ktoréhokoľvek ukazovateľa vplývajú dva faktory. Tieto dve premenné môžu skončiť v ďalšej tabuľke spojenej s úverom, pomocou ktorej možno určiť optimálnu dobu splácania a výšku mesačnej splátky. Pri tomto výpočte je potrebné použiť aj funkciu PMT. Príklad tabuľky s 2 premennými:

Vytvorenie dátovej tabuľky s jednou premennou

Zvážte metódu analýzy na príklade malého kníhkupectva s iba 100 knihami na sklade. Niektoré z nich je možné predať drahšie (50 USD), zvyšok bude stáť kupujúcich menej (20 USD). Počíta sa celkový príjem z predaja všetkého tovaru – majiteľ sa rozhodol, že 60 % kníh predá draho. Musíte zistiť, ako sa zvýšia tržby, ak zvýšite cenu väčšieho objemu tovaru – 70 % a pod.

Venujte pozornosť! Celkový príjem je potrebné vypočítať pomocou vzorca, inak nebude možné zostaviť tabuľku s údajmi.

1. Vyberte voľnú bunku ďalej od okraja hárka a napíšte do nej vzorec: = Bunka celkového príjmu. Napríklad, ak je príjem zapísaný v bunke C14 (je uvedené náhodné označenie), musíte napísať toto: =S14.
2. Percento objemu tovaru zapíšeme do stĺpca naľavo od tejto bunky – nie pod ňu, to je veľmi dôležité.
3. Vyberieme rozsah buniek, kde sa nachádza percentuálny stĺpec a prepojenie na celkový príjem.

4. Na karte „Údaje“ nájdeme položku „Čo ak analýza“ a klikneme na ňu – v ponuke, ktorá sa otvorí, vyberte možnosť „Tabuľka údajov“.

5. Otvorí sa malé okno, v ktorom musíte zadať bunku s percentom kníh pôvodne predaných za vysokú cenu v stĺpci „Nahradiť hodnoty riadkami v ...“. Tento krok sa vykonáva s cieľom prepočítať celkový príjem s prihliadnutím na zvyšujúce sa percento.

Po kliknutí na tlačidlo „OK“ v okne, kde boli zadané údaje pre zostavenie tabuľky, sa v riadkoch zobrazia výsledky výpočtov.

Pridanie vzorca do tabuľky údajov s jednou premennou

Z tabuľky, ktorá pomohla vypočítať akciu iba s jednou premennou, môžete vytvoriť sofistikovaný analytický nástroj pridaním ďalšieho vzorca. Treba ho zadať vedľa už existujúceho vzorca – napríklad ak je tabuľka riadková, výraz zadáme do bunky napravo od existujúceho. Keď je nastavená orientácia stĺpca, napíšeme nový vzorec pod starý. Ďalej postupujte podľa algoritmu:

1. Znova vyberte rozsah buniek, ale teraz by mal obsahovať nový vzorec.
2. Otvorte ponuku analýzy „čo ak“ a vyberte „Dátový hárok“.
3. Do príslušného poľa pridávame nový vzorec v riadkoch alebo stĺpcoch v závislosti od orientácie dosky.

Vytvorte tabuľku údajov s dvoma premennými

Začiatok takejto tabuľky je mierne odlišný – nad percentuálne hodnoty je potrebné umiestniť odkaz na celkový výnos. Ďalej vykonáme tieto kroky:

1. Cenové možnosti napíšte do jedného riadku s odkazom na príjem – jedna bunka pre každú cenu.
2. Vyberte rozsah buniek.

3. Otvorte okno tabuľky údajov, ako pri zostavovaní tabuľky s jednou premennou – cez záložku „Údaje“ na paneli nástrojov.
4. V stĺpci „Nahradiť hodnoty stĺpcami v ...“ nahraďte bunku s počiatočnou vysokou cenou.
5. Pridajte bunku s počiatočným percentom predaja drahých kníh do stĺpca „Nahradiť hodnoty riadkami v ...“ a kliknite na „OK“.

Výsledkom je, že celá tabuľka je zaplnená sumami možných príjmov s rôznymi podmienkami predaja tovaru.

Zrýchlite výpočty pre pracovné hárky obsahujúce dátové tabuľky

Ak potrebujete rýchle výpočty v tabuľke s údajmi, ktoré nespúšťajú prepočet celého zošita, existuje niekoľko vecí, ktoré môžete urobiť na urýchlenie procesu.

1. Otvorte okno možností, v ponuke vpravo vyberte položku „Vzorce“.
2. V časti „Výpočty v zošite“ vyberte položku „Automaticky, okrem tabuliek údajov“.

3. Prepočítajme výsledky v tabuľke ručne. Ak to chcete urobiť, vyberte vzorce a stlačte kláves F.

Ďalšie nástroje na vykonávanie analýzy citlivosti

V programe sú ďalšie nástroje, ktoré vám pomôžu vykonať analýzu citlivosti. Automatizujú niektoré úkony, ktoré by sa inak museli robiť manuálne.

1. Funkcia „Výber parametrov“ je vhodná, ak je známy požadovaný výsledok a na získanie takéhoto výsledku potrebujete poznať vstupnú hodnotu premennej.
2. „Hľadať riešenie“ je doplnok na riešenie problémov. Je potrebné nastaviť limity a ukázať na ne, po ktorých systém nájde odpoveď. Riešenie sa určí zmenou hodnôt.
3. Analýzu citlivosti je možné vykonať pomocou správcu scenárov. Tento nástroj sa nachádza v ponuke analýzy „čo keby“ na karte Údaje. Nahrádza hodnoty vo viacerých bunkách – počet môže dosiahnuť 32. Dispečer tieto hodnoty porovnáva, aby ich užívateľ nemusel meniť ručne. Príklad použitia správcu skriptov:

Analýza citlivosti investičného projektu v Exceli

Čo ak analýza je užitočná najmä v situáciách, keď sa vyžaduje prognóza, ako sú investície. Analytici pomocou tejto metódy zisťujú, ako sa zmení hodnota akcií spoločnosti v dôsledku zmien niektorých faktorov.

Metóda analýzy citlivosti investícií

Pri analýze „čo ak“ použite enumeráciu – manuálnu alebo automatickú. Rozsah hodnôt je známy a do vzorca sa nahrádzajú jeden po druhom. Výsledkom je súbor hodnôt. Vyberte si z nich vhodné číslo. Zoberme si štyri ukazovatele, pre ktoré sa v oblasti financií vykonáva analýza citlivosti:

1. Čistá súčasná hodnota – vypočíta sa odpočítaním výšky investície od výšky príjmu.
2. Vnútorná miera návratnosti / zisk – udáva, koľko zisku je potrebné získať z investície za rok.
3. Pomer návratnosti je pomer všetkých ziskov k počiatočnej investícii.
4. Index diskontovaného zisku – udáva efektivitu investície.

Vzorec

Citlivosť vkladania možno vypočítať pomocou tohto vzorca: Zmena výstupného parametra v % / Zmena vstupného parametra v %.

Výstupné a vstupné parametre môžu byť hodnoty opísané vyššie.

1. Musíte poznať výsledok za štandardných podmienok.
2. Nahradíme jednu z premenných a sledujeme zmeny vo výsledku.
3. Vypočítame percentuálnu zmenu oboch parametrov vzhľadom na stanovené podmienky.
4. Získané percentá vložíme do vzorca a určíme citlivosť.

Príklad analýzy citlivosti investičného projektu v Exceli

Pre lepšie pochopenie metodológie analýzy je potrebný príklad. Poďme analyzovať projekt s nasledujúcimi známymi údajmi:

1. Ak chcete analyzovať projekt, vyplňte tabuľku.

2. Cash flow vypočítame pomocou funkcie OFFSET. V počiatočnom štádiu sa tok rovná investíciám. Ďalej použijeme vzorec: =IF(OFFSET(číslo,1;)=2;SUM(Prítok 1:Odtok 1); SUM(Prítok 1:Odtok 1)+$B$ 5)
   Označenia buniek vo vzorci sa môžu líšiť v závislosti od rozloženia tabuľky. Na konci sa pripočítava hodnota z počiatočných údajov – hodnota záchrany.

3. Určíme obdobie, za ktoré sa projekt vyplatí. Pre počiatočné obdobie používame tento vzorec: =SUMMESLY(G7: G17;»<0″). Rozsah buniek je stĺpec peňažného toku. Pre ďalšie obdobia použijeme tento vzorec: =Počiatočné obdobie+AK(Prvý e.stream>0; Prvý e.tok;0). Projekt je na hranici rentability za 4 roky.

4. Vytvárame stĺpec pre čísla tých období, kedy sa projekt oplatí.

5. Vypočítame návratnosť investície. Je potrebné urobiť výraz, kde sa zisk v určitom časovom období vydelí počiatočnou investíciou.

6. Diskontný faktor určíme pomocou tohto vzorca: =1/(1+Disk.%) ^Číslo.

7. Súčasnú hodnotu vypočítame pomocou násobenia – peňažný tok sa vynásobí diskontným faktorom.

8. Vypočítajme si PI (index ziskovosti). Súčasná hodnota v čase sa vydelí investíciou na začiatku projektu.

9. Definujme vnútornú mieru návratnosti pomocou funkcie IRR: =IRR(Rozsah peňažných tokov).

Analýza citlivosti investícií pomocou údajového listu

Na analýzu projektov v oblasti investícií sú vhodnejšie iné metódy ako dátová tabuľka. Mnoho používateľov má pri zostavovaní vzorca zmätok. Ak chcete zistiť závislosť jedného faktora od zmien iných, musíte vybrať správne bunky na zadávanie výpočtov a na čítanie údajov.

Faktorová a disperzná analýza v Exceli s automatizáciou výpočtov

Ďalšou typológiou analýzy citlivosti je faktorová analýza a analýza rozptylu. Prvý typ definuje vzťah medzi číslami, druhý odhaľuje závislosť jednej premennej od ostatných.

ANOVA v Exceli

Účelom takejto analýzy je rozdeliť variabilitu hodnoty na tri zložky:

1. Variabilita ako výsledok vplyvu iných hodnôt.
2. Zmeny v dôsledku vzťahu hodnôt, ktoré ho ovplyvňujú.
3. Náhodné zmeny.

Vykonajte analýzu rozptylu prostredníctvom doplnku Excel „Analýza údajov“. Ak nie je povolená, je možné ju povoliť v nastaveniach.

Počiatočná tabuľka musí spĺňať dve pravidlá: pre každú hodnotu je jeden stĺpec a údaje v nej sú usporiadané vzostupne alebo zostupne. Je potrebné preveriť vplyv úrovne vzdelania na správanie v konflikte.

1. Nájdite nástroj Analýza údajov na karte Údaje a otvorte jeho okno. V zozname musíte vybrať jednosmernú analýzu rozptylu.

2. Vyplňte riadky dialógového okna. Vstupným intervalom sú všetky bunky, okrem hlavičiek a čísel. Zoskupiť podľa stĺpcov. Výsledky zobrazíme na novom hárku.

Keďže hodnota v žltej bunke je väčšia ako jedna, predpoklad možno považovať za nesprávny – medzi výchovou a správaním v konflikte neexistuje vzťah.

Faktorová analýza v Exceli: príklad

Poďme analyzovať vzťah údajov v oblasti predaja – je potrebné identifikovať obľúbené a nepopulárne produkty. Prvotné informácie:

1. Musíme zistiť, po ktorom tovare sa dopyt počas druhého mesiaca zvýšil najviac. Na určenie rastu a poklesu dopytu zostavujeme novú tabuľku. Rast sa vypočíta podľa tohto vzorca: =IF((Dopyt 2-Dopyt 1)>0; Dopyt 2-Dopyt 1;0). Vzorec na zníženie: =IF(rast=0; dopyt 1 - dopyt 2;0).

2. Vypočítajte rast dopytu po tovare v percentách: =AK(Rast/Výsledok 2 =0; Zníženie/Výsledok 2; Rast/Výsledok 2).

3. Pre prehľadnosť si urobme graf – vyberte rozsah buniek a vytvorte histogram cez záložku „Vložiť“. V nastaveniach musíte odstrániť výplň, to je možné vykonať pomocou nástroja Formátovať rad údajov.

Obojsmerná analýza rozptylu v Exceli

Analýza rozptylu sa vykonáva s niekoľkými premennými. Zvážte to na príklade: musíte zistiť, ako rýchlo sa reakcia na zvuk rôznej hlasitosti prejavuje u mužov a žien.

1. Otvárame „Analýzu údajov“, v zozname musíte nájsť obojsmernú analýzu rozptylu bez opakovaní.
2. Interval vstupu – bunky, ktoré obsahujú údaje (bez hlavičky). Výsledky zobrazíme na novom hárku a klikneme na „OK“.

Hodnota F je väčšia ako F-kritická, čo znamená, že podlaha ovplyvňuje rýchlosť reakcie na zvuk.

záver

V tomto článku sa podrobne diskutuje o analýze citlivosti v tabuľke Excel, takže každý používateľ bude schopný pochopiť metódy jej aplikácie.