Ako zabudovať kontroly chýb vo finančnom modeli Excelu

Aj keď ste s modelingom len začali, pravdepodobne dobre viete, aké ľahké je urobiť chybu vo finančnom modeli! Existujú tri spôsoby, ako zabrániť chybám vo finančnom modeli:

• V prvom rade sa vyvarujte chýb. V tejto knihe popisujem niekoľko techník, ktoré môžete použiť, aby ste sa v prvom rade vyhli chybám, ako je napríklad dodržiavanie vašich vzorcov.
• Skontrolujte, či model neobsahuje chyby. Napriek vášmu najlepšiemu úsiliu sa chyby nevyhnutne môžu prekĺznuť, takže po dokončení skontrolujte, dvakrát skontrolujte a požiadajte niekoho iného, ​​aby skontroloval váš model.
• Zahrňte kontroly chýb. Pri vytváraní modelu zahrňte kontroly chýb, ktoré zabránia tomu, aby sa do modelu dostali neúmyselné chyby v dôsledku nesprávnych zadaní alebo chyby používateľa.

Tu uvedené informácie sa zameriavajú na prvé dva body: techniky vytvárania modelov na zníženie chýb, ako aj spôsoby kontroly chýb v modeli.

Kontroly chýb sú kritickou súčasťou dobre zostaveného finančného modelu, takže používateľ alebo modelár môže na prvý pohľad vidieť, či vzorce počítajú správne. Napríklad pri vytváraní správ manažmentu skontrolujte, či sa súčet správ každého jednotlivého oddelenia pridáva k súčtu za celú spoločnosť. To sa dá dosiahnuť vložením jednoduchej funkcie IF, okrem iných metód.

V tu uvedenom príklade bol zostavený kapitálový rozpočet s odhadovanými dátumami výdavkov v stĺpci E. V pláne kapitálových výdavkov zobrazenom v stĺpcoch F až Q sa výdavky rozložia na celý rok. Modelár vie, že celková suma kapitálových výdavkov vo výške 124 700 USD uvedená v bunke D17 by mala byť rovnaká ako suma celkového plánu kapitálu uvedená v bunke R17, a ak sa tieto dve sumy navzájom nerovnajú, potom model nepočíta správne. Takže bunka E1 na kontrolu chýb obsahuje veľmi jednoduchý vzorec =R17-D17.

Jednoduchá kontrola chýb.

Nižšie môžete vidieť, že používateľ zadal nesprávnu hodnotu do bunky E4. 20. február nie je platná položka, pretože plán kapitálových výdavkov povoľuje iba dátumy počas roku 2019. To znamená, že finančný model uvedený nižšie je nesprávny – používateľ do modelu zadal kapitálové výdavky v hodnote 124 700 USD, no v rámci neho bolo pridelených iba 115 700 USD rok. Číslo zobrazené v bunke E1 (9 000) upozorňuje používateľa na to, že sa vyskytol problém. Stiahnite si súbor 0401.xlsx a vyberte karty označené 4-9 a 4-10, aby ste sa sami pokúsili spustiť túto kontrolu chýb.

Spustila sa kontrola chýb.

Táto kontrola chýb je veľmi jednoduchá a celkom diskrétna. Aby to bolo jasnejšie, môžete dať prednosť zahrnutiu popisu „kontrola chýb“ vedľa bunky kontroly chýb v bunke D1, čím by bolo pre používateľa jasnejšie, čo sa stalo pri spustení kontroly chýb.

Kontrola chýb zobrazená vyššie je preferovanou metódou kontroly chýb, pretože je taká jednoduchá a rýchla. Pretože v prípade chyby vracia hodnotu, môže to byť na váš vkus príliš diskrétne – nemusí okamžite upozorniť používateľa, že došlo k chybe. Je to však určite rýchle a jednoduché na sledovanie a z tohto dôvodu je to pomerne bežná kontrola chýb, ktorú mnohí modelári uprednostňujú.

Ak používate tento druh kontroly chýb, naformátujte ho pomocou štýlu Čiarka (nájdete ho na karte Domov v skupine Čísla) a odstráňte desatinné miesto a naformátujte ho červeným písmom. Týmto spôsobom sa nula nezobrazí, ak nie je chyba, a červené číslo sa zobrazí, ak sa vyskytne chyba.

Prípadne môžete uprednostniť iný vzorec na kontrolu chýb, napríklad =D17=R17, ktorý vráti hodnotu TRUE, ak sú rovnaké, alebo FALSE, ak nie sú. Táto metóda však tiež podlieha falošnej chybe.

Umožnenie tolerancie chýb

=IF(D17<>R17,”error”,0) je lepšia kontrola chýb, ale občas môže vrátiť chybný výsledok chyby, aj keď sú hodnoty rovnaké. Táto „chyba“ pri kontrole chýb je spôsobená skutočnosťou, že Excel prenáša výpočty na 14 desatinných miest. Potom skráti hodnotu a môže spôsobiť minútovú nezrovnalosť, ktorá ohlási chybu, keď bude vypnutá iba o 0,0000000000001.

Aby ste sa vyhli tomuto potenciálnemu problému, môžete použiť vzorec absolútnej hodnoty, ktorý by umožnil toleranciu chyby. =IF(ABS(D17-R17)>1,”chyba”,0) umožní, aby boli hodnoty nižšie o 1 $ predtým, ako nahlási chybu. Ak použijete funkciu ABS v Exceli, táto získa absolútnu hodnotu výsledku, takže nezáleží na tom, či ide o kladné alebo záporné číslo.

Existuje mnoho variácií tohto vzorca. Niektorí modelári dávajú prednosť zobrazeniu slova OK, ak sú čísla správne, a Check, ak nie sú.

Použitie podmieneného formátovania na kontrolu chýb

Ak chcete, aby bola kontrola chýb pre používateľa ešte výraznejšia, zvážte použitie podmieneného formátovania na pridanie pravidla, ktoré po spustení kontroly chýb zmení farbu celej bunky na červenú. Na karte Domov na páse s nástrojmi kliknite v skupine Štýly na tlačidlo Podmienené formátovanie. Potom umiestnite kurzor myši na pravidlo zvýraznenia buniek a vyberte možnosť Rovná sa.

Použitie podmieneného formátovania na kontrolu chýb.

Keď sa zobrazí dialógové okno Rovná sa, zadajte slovo chyba do poľa Formát buniek, ktoré sa rovnajú a kliknite na tlačidlo OK. V predvolenom nastavení sa bunka zmení na svetločervenú výplň s tmavočerveným textom, ale môžete to zmeniť v rozbaľovacom poli.

Úprava farby podmieneného formátovania.

Podmienené formátovanie je obľúbené pri kontrolách chýb, pretože pri spustení kontroly chýb viac vynikne. A pri troche šťastia si používateľ uvedomí, že vo finančnom modeli je chyba. Podmienené formátovanie nie je obmedzené na kontrolu chýb – môže byť užitočné pre iné modely, kde chcete upozorniť používateľa na abnormality alebo zvýraznenia vo výpočtoch.