Použitie nástroja na vyhľadávanie cieľov vo formáte What-If

• 2019

Aj keď dlhý zoznam funkcií programu Excel je jedným z najviac lákavých funkcií aplikácie tabuľkových procesorov spoločnosti Microsoft, existuje niekoľko nevyužitých klenotov, ktoré tieto funkcie vylepšujú. Jedným často prehliadaným nástrojom je analýza What-If.

Nástroj na analýzu What-If je rozdelený na tri hlavné komponenty. Tu časť, o ktorej diskutujeme, je silná funkcia Cieľové vyhľadávanie, ktorá vám umožní pracovať späť z funkcie a určiť vstupy potrebné na získanie požadovaného výstupu z vzorca v bunke. Prečítajte si ďalej a zistite, ako používať nástroj What-If Analysis Goal Seek programu Excel.

Príklad príkladu nástroja Excel

Predpokladajme, že chcete zísť hypotekárny úver na kúpu domu a máte obavy, že úroková sadzba z úveru ovplyvní ročné platby. Výška hypotéky je 100 000 dolárov a splatíte pôžičku v priebehu 30 rokov.

Pomocou funkcie programu PMT programu Excel môžete ľahko zistiť, aké budú ročné platby, ak by úroková sadzba bola 0%. Tabuľka by pravdepodobne vyzerala takto:

Bunka na A2 predstavuje ročnú úrokovú sadzbu, bunka na B2 je dĺžka úveru v rokoch a bunka na C2 je výška hypotekárneho úveru. Vzorec v D2 je:

= PMT (A2, B2, C2)

a predstavuje ročné splátky 30-ročnej hypotéky vo výške 100 000 USD pri 0% úroku. Všimnite si, že číslo v D2 je záporné, pretože Excel predpokladá, že platby sú negatívnym peňažným tokom z vašej finančnej pozície.

Bohužiaľ, žiadny hypotekárny veriteľ vám nebude požičať 100 000 dolárov pri 0% úroku. Predstavte si, že si nejaké pomyslíte a zistíte, že si môžete dovoliť splatiť 6000 dolárov ročne v hypotekárnych splátkach. Teraz sa zaujímate, čo je najvyššia úroková sadzba, ktorú môžete previesť na úver, aby ste sa uistili, že neskončíte vyplácaním viac ako 6 000 dolárov ročne.

Mnohí ľudia v tejto situácii by jednoducho začali písať čísla v bunke A2, kým číslo v D2 nedosiahne približne 6 000 USD. Môžete však urobiť Excel pre vás prácu pomocou nástroja What-If Analysis Goal Seek. V podstate budete robiť Excel pracovať späť od výsledku v D4, kým príde s úrokovou sadzbou, ktorá vyhovuje vašej maximálnej výplaty vo výške 6000 dolárov.

Začnite kliknutím na kartu Údaje na páse kariet a vyhľadaním tlačidla Čo-Ak analýza v sekcii Nástroje údajov . Kliknite na tlačidlo Čo-Ak analýza a z ponuky vyberte možnosť Hľadanie cieľa .

Program Excel otvorí malé okno a zobrazí výzvu na zadanie iba troch premenných. Premenná Set Cell musí byť bunka, ktorá obsahuje vzorec. V našom príklade tu je D2 . Premenná To Value je suma, ktorú chcete, aby bola bunka na D2 na konci analýzy.

Pre nás je to -6000 . Pamätajte, že program Excel považuje platby za negatívny peňažný tok. Premenná Premenná bunka je úroková sadzba, ktorú chcete Excel nájsť pre vás, takže hypotéka 100 000 USD vás bude stáť iba 6 000 dolárov ročne. Takže použite bunku A2 .

Kliknite na tlačidlo OK a môžete si všimnúť, že program Excel bliká veľa čísel v príslušných bunkách, až kým sa konečné konečné čísla nezmení na konečné číslo. V našom prípade by mala bunka na A2 teraz čítať približne 4, 31%.

Táto analýza nám uvádza, že na to, aby ste nezaplatili viac ako 6 000 dolárov ročne na 30-ročnú hypotéku vo výške 100 000 dolárov, musíte zabezpečiť pôžičku na maximálne 4, 31%. Ak chcete pokračovať v analýze, môžete vyskúšať rôzne kombinácie čísel a premenných, aby ste preskúmali možnosti, ktoré máte pri pokuse zabezpečiť dobrú úrokovú sadzbu na hypotéku.

Nástroj na vyhľadávanie cieľov vo formáte What-If je výkonným doplnkom k rôznym funkciám a vzorcom, ktoré sa nachádzajú v typickej tabuľke. Spracovaním dozadu z výsledkov vzorca v bunke môžete prehľadnejšie prečítať rôzne premenné vo vašich výpočtoch.