Jak zabudovat kontroly chyb ve finančním modelu Excelu

I když s modelingem teprve začínáte, pravděpodobně dobře víte, jak snadné je ve finančním modelu udělat chybu! Chybám ve finančním modelu lze předejít třemi způsoby:

• V první řadě se vyvarujte chyb. V této knize popisuji několik technik, které můžete použít, abyste se v první řadě vyhnuli chybám, jako je například dodržování vašich vzorců.
• Zkontrolujte, zda model neobsahuje chyby. Navzdory vašemu nejlepšímu úsilí mohou chyby nevyhnutelně proklouznout, takže po dokončení zkontrolujte, znovu zkontrolujte a nechte někoho jiného zkontrolovat váš model.
• Zahrnout kontroly chyb. Při sestavování modelu zahrňte kontroly chyb, které zabrání nechtěným chybám ve vklouznutí do modelu v důsledku nesprávných zadání nebo chyby uživatele.

Zde uvedené informace se zaměřují na první dva body: techniky pro tvorbu modelu ke snížení chyb a také způsoby kontroly chyb v modelu.

Kontroly chyb jsou kritickou součástí dobře sestaveného finančního modelu, takže uživatel nebo modelář může na první pohled vidět, zda vzorce počítají správně. Například při vytváření zpráv managementu zkontrolujte, zda se součet zpráv každého jednotlivého oddělení přidává k celkovému součtu za celou společnost. To lze provést vložením jednoduché funkce IF, mezi jinými metodami.

Ve zde uvedeném příkladu byl vytvořen kapitálový rozpočet s odhadovanými daty výdajů ve sloupci E. V plánu kapitálových výdajů zobrazeném ve sloupcích F až Q jsou výdaje rozloženy na celý rok. Modelář ví, že celková částka kapitálových výdajů ve výši 124 700 USD uvedená v buňce D17 by měla být stejná jako částka celkového plánu kapitálu uvedená v buňce R17, a pokud se tyto dvě částky navzájem nerovnají, pak model nepočítá správně. Buňka E1 pro kontrolu chyb tedy obsahuje velmi jednoduchý vzorec =R17-D17.

Jednoduchá kontrola chyb.

Níže vidíte, že uživatel zadal do buňky E4 nesprávnou hodnotu. 20. února není platná položka, protože plán kapitálových výdajů počítá pouze s daty v průběhu roku 2019. To znamená, že níže uvedený finanční model je nesprávný – uživatel do modelu zadal kapitálové výdaje v hodnotě 124 700 USD, ale bylo alokováno pouze 115 700 USD. rok. Číslo zobrazené v buňce E1 (9 000) upozorňuje uživatele na problém. Stáhněte si soubor 0401.xlsx a vyberte karty označené 4-9 a 4-10 a zkuste sami spustit tuto kontrolu chyb.

Byla spuštěna kontrola chyb.

Tato kontrola chyb je velmi jednoduchá a docela diskrétní. Aby to bylo jasnější, můžete dát přednost zahrnutí popisu „kontrola chyb“ vedle buňky kontroly chyb v buňce D1, aby bylo uživateli lépe zřejmé, co se stalo při spuštění kontroly chyb.

Výše uvedená kontrola chyb je preferovanou metodou kontroly chyb, protože je tak jednoduchá a rychlá. Protože v případě chyby vrací hodnotu, může být na váš vkus příliš diskrétní – nemusí nutně uživatele okamžitě upozornit, že došlo k chybě. Nicméně je to určitě rychlé a snadné sledovat a z tohoto důvodu je to docela běžná kontrola chyb, kterou upřednostňuje mnoho modelářů.

Pokud používáte tento druh kontroly chyb, naformátujte jej pomocí stylu Čárka (najdete jej na kartě Domů ve skupině Čísla) a odstraňte desetinné místo a naformátujte jej červeným písmem. Tímto způsobem se nula nezobrazí, pokud není žádná chyba, a červené číslo se zobrazí, pokud dojde k chybě.

Případně můžete dát přednost jinému vzorci pro kontrolu chyb, například =D17=R17, který vrátí hodnotu TRUE, pokud jsou stejné, nebo FALSE, pokud nejsou. Tato metoda však také podléhá falešné chybě.

Umožňuje toleranci chyb

=IF(D17<>R17,”error”,0) je lepší kontrola chyb, ale tu a tam může vrátit chybný výsledek, i když jsou hodnoty stejné. Tato „chyba“ při kontrole chyb je způsobena tím, že Excel převádí výpočty na 14 desetinných míst. Poté hodnotu zkrátí a může způsobit minutovou nesrovnalost, která ohlásí chybu, když bude vypnutá pouze o 0,0000000000001.

Chcete-li se tomuto potenciálnímu problému vyhnout, můžete použít vzorec pro absolutní hodnotu, který by umožnil toleranci chyby. =IF(ABS(D17-R17)>1,”error”,0) umožní, aby byly hodnoty nižší o $1, než nahlásí chybu. Pokud použijete funkci ABS v Excelu, bude to mít absolutní hodnotu výsledku, takže nezáleží na tom, zda se jedná o kladné nebo záporné číslo.

Existuje mnoho variant tohoto vzorce. Někteří modeláři dávají přednost zobrazení slova OK, pokud jsou čísla správná, a Check, pokud nejsou.

Použití podmíněného formátování na kontrolu chyb

Chcete-li, aby byla kontrola chyb pro uživatele ještě výraznější, zvažte použití podmíněného formátování k přidání pravidla, které v případě spuštění kontroly chyb změní barvu celé buňky na červenou. Na kartě Domů na pásu karet ve skupině Styly klikněte na tlačítko Podmíněné formátování. Poté najeďte myší na Pravidlo zvýraznění buněk a vyberte možnost Rovná se.

Použití podmíněného formátování na kontrolu chyb.

Když se zobrazí dialogové okno Rovná se, zadejte slovo chyba do pole Formát buněk, které se ROVNOU a klepněte na tlačítko OK. Ve výchozím nastavení se buňka změní na světle červenou výplň s tmavě červeným textem, ale můžete to změnit v rozevíracím seznamu.

Úprava barvy podmíněného formátování.

Podmíněné formátování je oblíbené při kontrolách chyb, protože při spuštění kontroly chyb lépe vynikne. A s trochou štěstí si uživatel uvědomí, že ve finančním modelu je chyba. Podmíněné formátování není omezeno na kontrolu chyb – může být užitečné pro jiné modely, kde chcete upozornit uživatele na abnormality nebo zvýraznění ve výpočtech.