Ako vytvoriť rozbaľovacie scenáre vo vašom finančnom modeli

Najbežnejšie používanou metódou vytvárania scenárov je použitie kombinácie vzorcov a rozbaľovacích políčok. Vo finančnom modeli vytvoríte tabuľku možných scenárov a ich vstupov a prepojíte názvy scenárov s rozbaľovacím rámčekom vstupnej bunky. Vstupy modelu sú prepojené s tabuľkou scenárov. Ak bol model zostavený správne so všetkými vstupmi prechádzajúcimi do výstupov, potom sa výsledky modelu zmenia, keď používateľ vyberie rôzne možnosti z rozbaľovacieho poľa.

Rozbaľovacie boxy na overenie údajov sa používajú na množstvo rôznych účelov vo finančnom modelovaní, vrátane analýzy scenárov.

Použitie overenia údajov na modelovanie scenárov ziskovosti

Stiahnite si súbor 0801.xlsx . Otvorte ho a vyberte kartu s názvom 8-1-start.

Spôsob, akým to bolo modelované, sú vstupy zoradené v stĺpci B. Analýzu citlivosti môžete vykonať jednoducho tak, že zmeníte jeden zo vstupov – napríklad zmeníte zákazníkov na operátora hovoru v bunke B3 zo 40 na 45 a vidieť, ako sa menia všetky závislé čísla. Toto by bola analýza citlivosti, pretože meníte iba jednu premennú. Namiesto toho budete v tomto cvičení s úplnou analýzou scenára meniť viacero premenných naraz, takže budete musieť urobiť viac, než manuálne vyladiť niekoľko čísel.

Ak chcete vykonať analýzu scenára pomocou rozbaľovacích polí na overenie údajov, postupujte takto:

Vezmite stiahnutý model a vystrihnite a prilepte popisy zo stĺpca C do stĺpca F. Môžete to urobiť zvýraznením buniek C6:C8, stlačením Ctrl+X, výberom bunky F6 a stlačením klávesu Enter.

Vstupy v bunkách B3 až B8 predstavujú aktívny rozsah, ktorý riadi model a taký aj zostane. Musia sa však stať vzorcami, ktoré sa menia v závislosti od rozbaľovacieho poľa, ktoré vytvoríte.

Skopírujte rozsah v stĺpci B do stĺpcov C, D a E.

Môžete to urobiť tak, že zvýrazníte B3:B8, stlačíte Ctrl+C, vyberiete bunky C3:E3 a stlačíte Enter. Tieto sumy budú rovnaké pre každý scenár, kým ich nezmeníte.

V riadku 2 zadajte tituly najlepšom prípade , základného scenára , a najhorší prípad.

Nastavenie modelu pre analýzu scenára.

Všimnite si, že vzorce sú stále prepojené so vstupmi v stĺpci B, ako môžete vidieť výberom bunky C12 a stlačením klávesovej skratky F2.

Upravte vstupy pod každým scenárom.

Môžete zadať čokoľvek, čo považujete za pravdepodobné, ale aby ste čísla priradili k tým v tomto príklade, zadajte hodnoty. Stĺpec B zatiaľ ignorujte.

Vstupy pre analýzu scenárov.

Teraz musíte pridať rozbaľovacie pole v hornej časti, ktoré bude riadiť vaše scenáre. Nezáleží na tom, kde presne umiestnite rozbaľovacie pole, ale malo by byť na mieste, ktoré sa dá ľahko nájsť, zvyčajne v hornej časti stránky.

Do bunky E1 zadajte názov Scenár .

Vyberte bunku F1 a zmeňte formátovanie na vstup, aby používateľ videl, že táto bunka je upraviteľná.

Najjednoduchší spôsob, ako to urobiť, je postupovať podľa týchto krokov:

Kliknite na jednu z buniek, ktoré sú už naformátované ako vstup, napríklad na bunku E3.

Stlačte ikonu Format Painter v sekcii Schránka na ľavej strane karty Domov. Váš kurzor sa zmení na štetec.

Ak chcete prilepiť formátovanie, vyberte bunku F1.

Format Painter je zvyčajne na jedno použitie. Keď vyberiete bunku, štetec zmizne z kurzora. Ak chcete, aby sa aplikácia Format Painter stala „lepkavou“ a aplikovala sa na viacero buniek, dvakrát kliknite na ikonu, keď ju vyberiete na karte Domov.

Teraz v bunke F1 vyberte Overenie údajov v časti Nástroje údajov na karte Údaje.

Zobrazí sa dialógové okno Overenie údajov.

Na karte Nastavenia zmeňte rozbaľovaciu ponuku Povoliť na možnosť Zoznam, pomocou myši vyberte rozsah =$C$2:$E$2 a kliknite na tlačidlo OK.

Vytvorenie rozbaľovacích scenárov overenia údajov.

Kliknite na rozbaľovacie pole, ktoré sa teraz zobrazuje vedľa bunky F1, a vyberte jeden zo scenárov (napríklad Základný prípad).

Aplikácia vzorcov na scenáre

Bunky v stĺpci B stále riadia model a tieto je potrebné nahradiť vzorcami. Pred pridaním vzorcov by ste však mali zmeniť formátovanie buniek v rozsahu, aby sa ukázalo, že obsahujú vzorce namiesto pevne zadaných čísel. Nasleduj tieto kroky:

Vyberte bunky B3:B8 a vyberte farbu výplne zo skupiny Písmo na karte Domov.

Zmeňte farbu výplne na biele pozadie.

Je veľmi dôležité rozlišovať medzi vzorcami a vstupnými bunkami v modeli. Každému používateľovi, ktorý otvára model, musíte objasniť, že bunky v tomto rozsahu obsahujú vzorce a nemali by byť prepísané.

Teraz musíte nahradiť pevne zakódované hodnoty v stĺpci B vzorcami, ktoré sa zmenia so zmenou rozbaľovacieho poľa. Môžete to urobiť pomocou množstva rôznych funkcií; HLOOKUP, vnorený príkaz IF, IFS a SUMIF vám pomôžu. Pridajte vzorce podľa týchto krokov:

Vyberte bunku B3 a pridajte vzorec, ktorý zmení hodnotu v závislosti od toho, čo je v bunke F1.

Tu je vzorec, ktorý bude v rámci rôznych možností:

• =HLOOKUP($F$1,$C$2:$E$8;2,0)

  Upozorňujeme, že pri tomto riešení musíte pri kopírovaní vzorca zmeniť indexové číslo riadka z 2 na 3 atď. Namiesto toho môžete použiť funkciu ROW v treťom poli takto: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)

• =AK($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))
• =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
• =SUMIF($C$2:$E$2,$F$1,C3:E3)

  Ako vždy je na výber niekoľko rôznych možností a najlepšie riešenie je to najjednoduchšie a najjednoduchšie na pochopenie. Ktorákoľvek z týchto funkcií vytvorí presne rovnaký výsledok, ale potreba zmeniť číslo indexu riadka v HLOOKUP nie je robustná a pridanie RIADKA môže byť pre používateľa mätúce. Vytvorenie a nasledovanie vnoreného príkazu IF je zložité a hoci je nová funkcia IFS navrhnutá tak, aby zjednodušila vnorenú funkciu IF, stále je dosť nepraktická. SUMIF je pomerne jednoduché zostaviť a sledovať a je ľahké ho rozšíriť, ak budete v budúcnosti potrebovať pridať ďalšie scenáre.

  Všimnite si, že IFS je nová funkcia, ktorá je k dispozícii iba s nainštalovaným balíkom Office 365 a Excelom 2016 alebo novším. Ak použijete túto funkciu a niekto otvorí tento model v predchádzajúcej verzii Excelu, môže zobraziť vzorec, ale nebude ho môcť upravovať.

Skopírujte vzorec v bunke B3 nadol v stĺpci.

Dokončená analýza scenára.

Použitím obyčajného kopírovania a prilepenia stratíte všetko svoje formátovanie. Je dôležité zachovať formátovanie modelu, aby ste na prvý pohľad videli, ktoré vstupy sú v dolárových hodnotách, percentách alebo číslach zákazníkov. Ak chcete zachovať formátovanie, použite Vzorce prilepenia. Môžete k nemu pristupovať tak, že skopírujete bunku do schránky, zvýrazníte cieľový rozsah, kliknete pravým tlačidlom myši a vyberiete ikonu Prilepiť vzorce, aby ste prilepili iba vzorce a ponechali formátovanie nedotknuté.

Teraz k zábavnej časti! Je čas otestovať funkčnosť scenára v modeli.

Kliknite na bunku F1, zmeňte rozbaľovacie pole a sledujte, ako sa menia výstupy modelu pri prepínaní medzi rôznymi scenármi.