Kā Excel finanšu modelī izveidot kļūdu pārbaudes

Pat ja esat tikai tikko sācis modelēt, jūs droši vien labi apzināties, cik viegli ir kļūdīties finanšu modelī! Ir trīs veidi, kā novērst kļūdas finanšu modelī:

• Vispirms izvairieties no kļūdām. Šajā grāmatā es aprakstu vairākus paņēmienus, kurus varat izmantot, lai vispirms nepieļautu kļūdas, piemēram, ievērotu savas formulas.
• Pārbaudiet, vai modelī nav kļūdu. Neraugoties uz jūsu centieniem, kļūdas neizbēgami var paslīdēt, tāpēc pārbaudiet, vēlreiz pārbaudiet un palūdziet kādam citam pārbaudīt jūsu modeli pēc tā pabeigšanas.
• Iekļaujiet kļūdu pārbaudes. Veidojot modeli, iekļaujiet kļūdu pārbaudes, kas novērš nejaušu kļūdu iekļūšanu modelī nepareizu ierakstu vai lietotāja kļūdu dēļ.

Šeit sniegtā informācija ir vērsta uz pirmajiem diviem punktiem: modeļa veidošanas paņēmieniem kļūdu samazināšanai, kā arī veidiem, kā pārbaudīt, vai modelī nav kļūdu.

Kļūdu pārbaudes ir svarīga labi izveidota finanšu modeļa sastāvdaļa, lai lietotājs vai modelētājs vienā mirklī varētu redzēt, vai formulas aprēķina pareizi. Piemēram, veidojot vadības pārskatus, pārbaudiet, vai katra atsevišķa departamenta atskaites summa tiek pievienota uzņēmuma kopsummai. To var izdarīt, citu metožu starpā ievietojot vienkāršu IF funkciju.

Šeit parādītajā piemērā kapitāla budžets ir izveidots ar aptuvenajiem tēriņu datumiem kolonnā E. Kapitāla izdevumu grafikā, kas parādīts kolonnās F līdz Q, izdevumi tiek sadalīti pa visu gadu. Modelētājs zina, ka kopējai kapitāla tēriņu summai 124 700 ASV dolāru apmērā, kas parādīta šūnā D17, jābūt tādai pašai kā kopējai kapitāla grafika summai, kas parādīta šūnā R17, un, ja abas summas nav vienādas, modelis neveic pareizu aprēķinu. Tātad kļūdu pārbaudes šūnā E1 ir ļoti vienkārša formula =R17-D17.

Vienkārša kļūdu pārbaude.

Tālāk ir redzams, ka lietotājs šūnā E4 ir ievadījis nepareizu vērtību. 20. februāris nav derīgs ieraksts, jo kapitāla tēriņu grafikā ir iekļauti tikai datumi 2019. gadā. Tas nozīmē, ka tālāk norādītais finanšu modelis nav pareizs — lietotājs modelī ir ievadījis kapitālizdevumus USD 124 700 vērtībā, bet tikai USD 115 700 ir piešķirti. Gads. Skaitlis, kas tiek rādīts šūnā E1 (9000), brīdina lietotāju par problēmu. Lejupielādējiet failu 0401.xlsx un atlasiet cilnes ar 4-9 un 4-10, lai mēģinātu pats aktivizēt šo kļūdu pārbaudi.

Tika aktivizēta kļūdu pārbaude.

Šī kļūdu pārbaude ir ļoti vienkārša un diezgan diskrēta. Lai padarītu to acīmredzamāku, iespējams, vēlēsities iekļaut aprakstu “kļūdu pārbaude” blakus kļūdu pārbaudes šūnai šūnā D1, kas lietotājam padarītu skaidrāku, kas ir noticis, kad tiek aktivizēta kļūdu pārbaude.

Iepriekš parādītā kļūdu pārbaude ir ieteicamā kļūdu pārbaudes metode, jo tā ir tik vienkārša un ātra. Tā kā kļūdas gadījumā tas atgriež vērtību, tas var būt pārāk diskrēts jūsu gaumei — tas ne vienmēr brīdina lietotāju, ka ir pieļauta kļūda. Tomēr tas noteikti ir ātri un viegli izpildāms, un šī iemesla dēļ tā ir diezgan izplatīta kļūdu pārbaude, ko iecienījuši daudzi modelētāji.

Ja izmantojat šāda veida kļūdu pārbaudi, formatējiet to, izmantojot komatu stilu (atrodams cilnē Sākums grupā Cipari), un noņemiet decimāldaļu un formatējiet to ar sarkanu fontu. Tādā veidā nulle netiks rādīta, ja nav kļūdas, un sarkans cipars, ja ir kļūda.

Varat arī dot priekšroku citai kļūdu pārbaudes formulai, piemēram, =D17=R17, kas atgriezīs vērtību TRUE, ja tās ir vienādas, vai FALSE, ja tās nav. Tomēr šī metode ir pakļauta arī viltus kļūdai.

Pieļaujot kļūdu pielaidi

=IF(D17<>R17”error”,0) ir labāka kļūdu pārbaude, taču ik pa laikam tā var atgriezt nepatiesu kļūdas rezultātu, lai gan vērtības ir vienādas. Šo kļūdu pārbaudīšanas “kļūdu” izraisa fakts, ka Excel veic aprēķinus līdz 14 zīmēm aiz komata. Pēc tam tas saīsina vērtību un var izraisīt minūti lielu neatbilstību, kas ziņos par kļūdu, ja tā būs tikai 0,00000000000001.

Lai izvairītos no šīs iespējamās problēmas, varat izmantot absolūtās vērtības formulu, kas pieļauj kļūdu pielaidi. =IF(ABS(D17-R17)>1"kļūda",0) ļaus vērtībām samazināties par $1, pirms tiks ziņots par kļūdu. Ja programmā Excel izmantojat ABS funkciju, tiks izmantota rezultāta absolūtā vērtība, tāpēc nav nozīmes tam, vai tas ir pozitīvs vai negatīvs skaitlis.

Šai formulai ir daudz variāciju. Daži modelētāji izvēlas rādīt vārdu Labi, ja skaitļi ir pareizi, un Pārbaudiet, vai tie nav pareizi.

Nosacītā formatējuma lietošana kļūdu pārbaudei

Lai kļūdu pārbaude lietotājam būtu vēl pamanāmāka, apsveriet iespēju izmantot nosacījumu formatējumu, lai pievienotu kārtulu, kas liek visai šūnai kļūt sarkanai, ja kļūdas pārbaude ir aktivizēta. Lentes cilnes Sākums grupā Stili noklikšķiniet uz pogas Nosacītā formatēšana. Pēc tam virziet kursoru virs Highlight Cells Rule un atlasiet Vienāds ar.

Nosacītā formatējuma lietošana kļūdu pārbaudei.

Kad tiek parādīts dialoglodziņš Vienāds ar, lodziņā Format Cells that are EQUAL TO ierakstiet vārdu kļūda un noklikšķiniet uz Labi. Pēc noklusējuma šūna tiks pārvērsta par Gaiši sarkanu Aizpildīt ar tumši sarkanu tekstu, taču to var mainīt nolaižamajā lodziņā.

Nosacītā formatējuma krāsas rediģēšana.

Nosacījuma formatējums ir populārs kļūdu pārbaudēs, jo tas izceļ kļūdu pārbaudi, kad tā ir aktivizēta. Un, ja veiksies, lietotājs sapratīs, ka finanšu modelī ir kļūda. Nosacījuma formatējums neaprobežojas tikai ar kļūdu pārbaudēm — tas var būt noderīgs citiem modeļiem, kur vēlaties pievērst lietotāja uzmanību novirzēm vai aprēķinu izcēlumiem.