Hogyan építsünk be hibaellenőrzést az Excel pénzügyi modelljébe

Még ha csak most kezdett el modellkedni, valószínűleg jól tudja, milyen könnyű hibázni egy pénzügyi modellben! Háromféleképpen lehet megelőzni a hibákat egy pénzügyi modellben:

• Először kerülje el a hibák elkövetését. Ebben a könyvben leírok néhány technikát, amelyeket alkalmazhat, hogy elkerülje a hibákat, például, hogy összhangban legyen a képleteivel.
• Ellenőrizze a modellt, hogy vannak-e hibák. Minden erőfeszítése ellenére a hibák elkerülhetetlenül átcsúszhatnak, ezért ellenőrizze, ellenőrizze még egyszer, és kérjen meg valakit, hogy ellenőrizze a modelljét, miután elkészült.
• Tartalmazza a hibaellenőrzéseket. A modell építése során végezzen hibaellenőrzéseket, amelyek megakadályozzák, hogy a helytelen bejegyzések vagy a felhasználói hiba miatt véletlenül becsúszjanak a modellbe.

Az itt található információk az első két pontra összpontosítanak: a modellépítés technikáira a hibák csökkentésére, valamint a modell hibáinak ellenőrzésére.

A hibaellenőrzés a jól felépített pénzügyi modell kritikus részét képezi, így a felhasználó vagy a modellező egy pillantással láthatja, hogy a képletek helyesen számolnak-e. Például vezetői jelentések létrehozásakor ellenőrizze, hogy az egyes részlegek jelentéseinek összege hozzáadódik-e a vállalati teljes összeghez. Ez megtehető egy egyszerű IF függvény beszúrásával, többek között más módszerek mellett.

Az itt bemutatott példában egy tőkeköltségvetést állítottak össze a becsült kiadási dátumokkal az E oszlopban. Az F–Q oszlopokban látható tőkeköltési ütemezésben a ráfordítás a teljes évre oszlik el. A modellező tudja, hogy a D17 cellában látható 124 700 dolláros teljes tőkekiadás összegének meg kell egyeznie az R17 cellában látható teljes tőkeütemezési összeggel, és ha a két összeg nem egyezik meg egymással, akkor a modell nem megfelelően számol. Tehát az E1 hibaellenőrző cella a nagyon egyszerű =R17-D17 képletet tartalmazza.

Egy egyszerű hibaellenőrzés.

Alább láthatja, hogy egy felhasználó hibás értéket írt be az E4 cellába. A február 20-i bejegyzés nem érvényes, mert a tőkeköltési ütemezés csak a 2019-es dátumokat engedélyezi. Ez azt jelenti, hogy az alábbi pénzügyi modell hibás – a felhasználó 124 700 USD értékű tőkekiadást írt be a modellbe, de csak 115 700 USD került allokálásra. az év. Az E1 cellában megjelenő szám (9000) figyelmezteti a felhasználót a probléma tényére. Töltse le a 0401.xlsx fájlt, és válassza ki a 4-9 és 4-10 füleket, hogy megpróbálja elindítani ezt a hibaellenőrzést.

Hibaellenőrzés indult.

Ez a hibaellenőrzés nagyon egyszerű és meglehetősen diszkrét. A nyilvánvalóbbá tétel érdekében érdemes a D1 cellában a hibaellenőrző cella mellé felvenni a „hibaellenőrzés” leírást, amely nyilvánvalóbbá teszi a felhasználó számára, hogy mi történt a hibaellenőrzés elindításakor.

A fent bemutatott hibaellenőrzés egy előnyben részesített hibaellenőrzési módszer, mivel nagyon egyszerű és gyorsan elkészíthető. Mivel hiba esetén értéket ad vissza, kissé túl diszkrét lehet az Ön ízléséhez – nem feltétlenül figyelmezteti azonnal a felhasználót, hogy hiba történt. Azonban minden bizonnyal gyorsan és egyszerűen követhető, és emiatt egy meglehetősen gyakori hibaellenőrzés, amelyet sok modellező kedvel.

Ha ezt a fajta hibaellenőrzést használja, formázza a vesszőstílussal (a Számok csoport Kezdőlap lapján található), és távolítsa el a tizedesjegyet, és formázza piros betűtípussal. Így a nulla nem jelenik meg, ha nincs hiba, és egy piros szám jelzi, ha hiba van.

Alternatív megoldásként a másik hibaellenőrző képletet részesítheti előnyben, például =D17=R17, amely az IGAZ értéket adja vissza, ha megegyezik, vagy a FALSE értéket, ha nem. Ez a módszer azonban téves hibának is kitéve.

Hibatűrés lehetővé tétele

Az =IF(D17<>R17”hiba”,0) kiváló hibaellenőrzés, de időnként hamis hibaeredményt ad vissza, annak ellenére, hogy az értékek megegyeznek. Ezt a hibaellenőrzési „hibát” az okozza, hogy az Excel 14 tizedesjegyig végzi a számításokat. Ezt követően csonkolja az értéket, és percnyi eltérést okozhat, ami hibát jelez, ha csak 0,00000000000001.

Ennek a lehetséges problémának a elkerülése érdekében használhat egy abszolút érték képletet, amely lehetővé teszi a hibatűrést. Az =IF(ABS(D17-R17)>1”hiba”,0) lehetővé teszi, hogy az értékek $1-el csökkenjenek, mielőtt hibát jelez. Ha az ABS függvényt használja az Excelben, akkor ez az eredmény abszolút értékét veszi fel, tehát nem számít, hogy pozitív vagy negatív számról van szó.

Ennek a képletnek számos változata létezik. Egyes modellezők szívesebben jelenítik meg az OK szót, ha a számok helyesek, és az Ellenőrzést, ha nem.

Feltételes formázás alkalmazása hibaellenőrzésre

Annak érdekében, hogy a hibaellenőrzés még szembetűnőbb legyen a felhasználó számára, fontolja meg a feltételes formázás alkalmazását egy olyan szabály hozzáadásához, amely az egész cellát pirosra váltja, ha a hibaellenőrzés elindult. A menüszalag Kezdőlap lapján, a Stílusok csoportban kattintson a Feltételes formázás gombra. Ezután vigye az egeret a Highlight Cells Rule fölé, és válassza az Egyenlő lehetőséget.

Feltételes formázás alkalmazása hibaellenőrzésre.

Amikor megjelenik az Egyenlő párbeszédpanel, írja be a hiba szót a Cellák formázása mezőbe, majd kattintson az OK gombra. Alapértelmezés szerint a cellát Világospiros Kitöltés sötétvörös szöveggel értékre változtatja, de ezt a legördülő menüben módosíthatja.

A feltételes formázás színének szerkesztése.

A feltételes formázás népszerű a hibaellenőrzéseknél, mert a hibaellenőrzést jobban kiemeli, ha aktiválódik. És minden szerencsével a felhasználó rájön, hogy hiba van a pénzügyi modellben. A feltételes formázás nem korlátozódik a hibaellenőrzésekre – hasznos lehet más modelleknél, ahol fel kívánja hívni a felhasználó figyelmét a számítások rendellenességeire vagy kiemeléseire.