Prepínanie medzi rôznymi množinami hodnôt pomocou scenárov

Existujú tri druhy nástrojov na analýzu What-If, ktoré sú súčasťou Excelu: Scenáre, Tabuľky údajov a Hľadanie riešenia. Scenáre a tabuľky údajov berú množiny vstupných hodnôt a projekt dopredu, aby určili možné výsledky. Funkcia Hľadanie riešenia sa líši od scenárov a tabuliek údajov v tom, že vezme výsledok a projekty dozadu na určenie možných vstupných hodnôt, ktoré tento výsledok vyprodukujú.

Každý scenár môže obsahovať až 32 hodnôt premenných. Ak chcete analyzovať viac ako 32 hodnôt a hodnoty predstavujú iba jednu alebo dve premenné, môžete použiť tabuľky údajov. Hoci je obmedzená len na jednu alebo dve premenné (jednu pre vstupnú bunku riadka a jednu pre vstupnú bunku stĺpca), tabuľka údajov môže obsahovať ľubovoľný počet rôznych hodnôt premenných. Scenár môže obsahovať maximálne 32 rôznych hodnôt, no môžete vytvoriť ľubovoľný počet scenárov.

Okrem týchto troch nástrojov môžete nainštalovať doplnky, ktoré vám pomôžu vykonať What-If analýzu, ako je napríklad doplnok Riešiteľ. Doplnok Riešiteľ sa podobá nástroju Hľadanie riešenia, ale dokáže zvládnuť viac premenných. Prognózy môžete vytvárať aj pomocou rukoväti výplne a rôznych príkazov, ktoré sú zabudované v Exceli. V prípade pokročilejších modelov môžete použiť doplnok Analytické nástroje.

Predpokladajme, že chcete vytvoriť rozpočet, ale neviete, aké sú vaše príjmy. Pomocou scenárov môžete definovať rôzne možné hodnoty výnosov a potom prepínať medzi scenármi a vykonávať analýzy hypotéz.

Predpokladajme napríklad, že vaším najhorším scenárom rozpočtu je hrubá tržba vo výške 50 000 EUR a náklady na predaný tovar vo výške 13 200 EUR, pričom hrubá tržba bude 36 800 EUR. Ak chcete definovať túto množinu hodnôt ako scenár, najprv zadajte hodnoty do hárka, ako je to znázornené na nasledujúcom obrázku:

Bunky s meniacimi sa bunkami obsahujú hodnoty, ktoré zadáte, zatiaľ čo bunka Výsledok obsahuje vzorec založený na bunkách Changing (v tejto ilustrácii bunka B4 obsahuje vzorec =B2-B3).

Potom použite dialógové okno Správca scenárov na uloženie týchto hodnôt ako scenára. Prejdite na kartu Údaje > What-If > Pridať správcu scenárov > Analysis >.

V dialógovom okne Názov scenára pomenujte scenár Najhorší prípad a zadajte, že bunky B2 a B3 sú hodnoty, ktoré sa medzi scenármi menia. Ak pred pridaním scenára vyberiete v hárku možnosť Zmeniť bunky , Správca scenárov automaticky vloží bunky za vás, v opačnom prípade ich môžete zadať ručne alebo použiť dialógové okno výberu buniek napravo od dialógového okna Zmena buniek.

Ochrana – Môžete tiež chrániť svoje scenáre, takže v časti Ochrana skontrolujte požadované možnosti alebo zrušte ich začiarknutie, ak nechcete mať k dispozícii žiadnu ochranu.

• Ak chcete zabrániť úpravám scenára, keď je hárok zabezpečený, vyberte možnosť Zabrániť zmenám .

• Ak chcete zabrániť zobrazeniu scenára zabezpečenia hárka, vyberte možnosť Skryté .

Teraz predpokladajme, že váš najlepší prípad rozpočtu scenár je Hrubý výnos vo výške 150.000 dolárov a náklady na predaný tovar vo výške 26.000 dolárov, takže 124.000 dolárov v hrubom zisku. Ak chcete túto množinu hodnôt definovať ako scenár, vytvorte iný scenár, pomenujte ju Najlepší prípad a zadajte rôzne hodnoty pre bunky B2 (150 000) a bunku B3 (26 000). Keďže hrubý zisk (bunka B4) je vzorec – rozdiel medzi výnosmi (B2) a nákladmi (B3) – bunku B4 nezmeníte pre najlepší scenár.

Po uložení sa scenár sprístupní v zozname scenárov, ktoré môžete použiť v analýzach hypotéz. Ak by ste sa vzhľadom na hodnoty na predchádzajúcom obrázku rozhodli zobraziť scenár Najlepší prípad, hodnoty v hárku by sa zmenili tak, aby pripomínali nasledujúci obrázok:

Niekedy sa môžu vyskytnúť prípady, keď máte všetky informácie v jednom hárku alebo zošite potrebné na vytvorenie všetkých scenárov, ktoré chcete zvážiť. Možno však budete chcieť zhromaždiť informácie o scenári z iných zdrojov. Predpokladajme napríklad, že sa pokúšate vytvoriť rozpočet spoločnosti. Môžete zhromažďovať scenáre z rôznych oddelení, ako napríklad Predaj, Mzdy, Výroba, Marketing a Právne, pretože každý z týchto zdrojov má rôzne informácie, ktoré možno použiť pri vytváraní rozpočtu.

Tieto scenáre môžete zhromaždiť do jedného hárka pomocou príkazu Zlúčiť . Každý zdroj môže poskytnúť ľubovoľný počet meniacich sa hodnôt buniek. Môžete napríklad chcieť, aby každé oddelenie dodalo prognózy výdavkov, ale potrebujete len prognózy príjmov z niekoľkých.

Keď sa rozhodnete zlúčiť, správca scenárov načíta sprievodcu scenárom zlúčenia, ktorý zobrazí zoznam všetkých hárkov v aktívnom zošite, ako aj zoznam všetkých ostatných zošitov, ktoré ste mohli mať v danom čase otvorené. Sprievodca vám povie, koľko scenárov máte v každom zdrojovom hárku, ktorý vyberiete.

Pri zhromažďovaní rôznych scenárov z rôznych zdrojov by ste mali použiť rovnakú štruktúru buniek v každom zošite. Napríklad výnosy sa môžu vždy zobrazovať v bunke B2 a výdavky sa môžu vždy zobrazovať v bunke B3. Ak používate rôzne štruktúry pre scenáre z rôznych zdrojov, môže byť ťažké zlúčiť výsledky.

Ak chcete porovnať niekoľko scenárov, môžete vytvoriť zostavu, ktorá ich zhrnie na tej istej strane. Zostava môže uvádzať scenáre vedľa seba alebo ich prezentovať v zostava kontingenčnej tabuľky.

Súhrnná zostava scenára založená na predchádzajúcich dvoch príkladoch by vyzerala približne takto:

Všimnite si, že Excel automaticky pridal úrovne zoskupenia, ktoré sa po kliknutí na jednotlivé selektory rozbalia a zbalia.

Na konci súhrnnej zostavy sa zobrazí poznámka s vysvetlením, že stĺpec Aktuálne hodnoty predstavuje hodnoty meniacich sa buniek v čase vytvorenia súhrnnej zostavy scenára a že bunky, ktoré sa zmenili pre každý scenár, sú zvýraznené sivou farbou.