Si të ndërtoni kontrolle të gabimeve në modelin tuaj financiar të Excel

Edhe nëse sapo keni filluar modelingun, me siguri e dini se sa e lehtë është të bësh një gabim në një model financiar! Ekzistojnë tre mënyra për të parandaluar gabimet në një model financiar:

• Shmangni gabimet në radhë të parë. Në këtë libër, unë përshkruaj disa teknika që mund të përdorni për të shmangur gabimet në radhë të parë, të tilla si të qenit në përputhje me formulat tuaja.
• Kontrolloni modelin për gabime. Pavarësisht nga përpjekjet tuaja më të mira, gabimet në mënyrë të pashmangshme mund të kalojnë, kështu që kontrolloni, kontrolloni dy herë dhe kërkoni dikë tjetër të kontrollojë modelin tuaj pasi të jetë përfunduar.
• Përfshini kontrollet e gabimeve. Ndërsa po ndërtoni modelin, përfshini kontrollet e gabimeve që parandalojnë gabimet e paqëllimshme të rrëshqasin në model për shkak të hyrjeve të pasakta ose gabimit të përdoruesit.

Informacioni këtu fokusohet në dy pikat e para: teknikat për ndërtimin e modelit për të reduktuar gabimin, si dhe mënyrat për të kontrolluar modelin për gabime.

Kontrollet e gabimeve janë një pjesë kritike e një modeli financiar të ndërtuar mirë, në mënyrë që përdoruesi ose modeluesi të mund të shohë me një shikim nëse formulat llogariten saktë. Për shembull, kur krijoni raporte të menaxhimit, kontrolloni që shuma e raportit të secilit departament individual t'i shtohet totalit të kompanisë. Kjo mund të bëhet duke futur një funksion të thjeshtë IF, ndër metodat e tjera.

Në shembullin e paraqitur këtu, është ndërtuar një buxhet kapital me datat e parashikuara të shpenzimeve në kolonën E. Në planin e shpenzimeve kapitale të paraqitur në kolonat F deri në Q, shpenzimet shpërndahen gjatë gjithë vitit. Modeluesi e di se shuma totale e shpenzimeve kapitale prej 124,700 $ e paraqitur në qelizën D17 duhet të jetë e njëjtë me shumën totale të planit të kapitalit të treguar në qelizën R17 dhe nëse të dy shumat nuk janë të barabarta me njëra-tjetrën, atëherë modeli nuk është duke llogaritur siç duhet. Pra, qeliza e kontrollit të gabimit E1 përmban formulën shumë të thjeshtë =R17-D17.

Një kontroll i thjeshtë gabimi.

Më poshtë, mund të shihni se një përdorues ka futur një vlerë të gabuar në qelizën E4. 20 shkurt nuk është një hyrje e vlefshme sepse plani i shpenzimeve kapitale lejon vetëm datat gjatë vitit 2019. Kjo do të thotë se modeli financiar më poshtë është i pasaktë — përdoruesi ka futur në model shpenzime kapitale me vlerë 124,700 dollarë, por vetëm 115,700 dollarë janë ndarë në të gjithë viti. Numri që shfaqet në qelizën E1 (9000) paralajmëron përdoruesin për faktin se ka një problem. Shkarkoni skedarin 0401.xlsx dhe zgjidhni skedat e etiketuara 4-9 dhe 4-10 për të provuar të aktivizoni vetë këtë kontroll gabimi.

U aktivizua një kontroll gabimi.

Ky kontroll i gabimeve është shumë i thjeshtë dhe mjaft i matur. Për ta bërë më të qartë, mund të preferoni të përfshini përshkrimin "kontrolli i gabimit" pranë qelizës së kontrollit të gabimit në qelizën D1, gjë që do ta bënte më të qartë për përdoruesin se çfarë ka ndodhur kur aktivizohet kontrolli i gabimit.

Kontrolli i gabimeve i paraqitur më sipër është një metodë e preferuar e kontrollit të gabimeve, sepse është shumë e thjeshtë dhe e shpejtë për t'u ndërtuar. Për shkak se kthen një vlerë në rastin e një gabimi, mund të jetë paksa shumë i matur për shijet tuaja - nuk e paralajmëron domosdoshmërisht përdoruesin menjëherë se është bërë një gabim. Megjithatë, sigurisht që është i shpejtë dhe i lehtë për t'u ndjekur dhe, për këtë arsye, një kontroll gabimi mjaft i zakonshëm i favorizuar nga shumë modelues.

Nëse përdorni këtë lloj kontrolli gabimi, formatoni atë duke përdorur stilin e presjes (që gjendet në skedën Home në grupin Numrat) dhe hiqni numrin dhjetor dhe formatoni atë me një font të kuq. Në këtë mënyrë, zeroja nuk do të shfaqet nëse nuk ka gabim, dhe një numër i kuq do të tregojë nëse ka një gabim.

Përndryshe, ju mund të preferoni formulën tjetër të kontrollit të gabimeve, si p.sh. =D17=R17, e cila do të kthejë vlerën TRUE nëse janë të njëjta ose FALSE nëse nuk janë. Megjithatë, kjo metodë është gjithashtu subjekt i një gabimi të rremë.

Lejimi i tolerancës ndaj gabimit

=IF(D17<>R17,”gabim”,0) është një kontroll më i lartë gabimi, por herë pas here mund të kthejë një rezultat gabimi të rremë, edhe pse vlerat janë të njëjta. Ky "bug" në një kontroll gabimi shkaktohet nga fakti që Excel i bart llogaritjet në 14 shifra dhjetore. Pas kësaj, ai shkurton vlerën dhe mund të shkaktojë një mospërputhje minutë, e cila do të raportojë një gabim kur është vetëm 0.00000000000001 off.

Për të shmangur këtë problem të mundshëm, mund të përdorni një formulë me vlerë absolute, e cila do të lejonte një tolerancë ndaj gabimit. =IF(ABS(D17-R17)>1,”gabim”,0) do të lejojë që vlerat të jenë joaktive me $1 përpara se të raportojë një gabim. Nëse përdorni funksionin ABS në Excel, kjo do të marrë vlerën absolute të rezultatit, kështu që nuk ka rëndësi nëse është një numër pozitiv apo negativ.

Ka shumë variacione të kësaj formule. Disa modelues preferojnë të tregojnë fjalën OK nëse numrat janë të drejtë dhe Kontrolloni nëse nuk janë.

Zbatimi i formatimit të kushtëzuar në një kontroll gabimi

Për ta bërë kontrollin e gabimit edhe më të spikatur për përdoruesin, merrni parasysh përdorimin e formatimit të kushtëzuar për të shtuar një rregull që e bën të gjithë qelizën të kthehet në të kuqe nëse kontrolli i gabimit është aktivizuar. Në skedën kryesore të shiritit, në grupin Styles, klikoni butonin "Formatimi i kushtëzuar". Më pas vendoseni miun mbi Rregullin e "Hikoni qelizat" dhe zgjidhni "E barabartë me".

Zbatimi i formatimit të kushtëzuar në një kontroll gabimi.

Kur të shfaqet kutia e dialogut Equal To, shkruani fjalën gabim në kutinë Format Cells që janë BARABARË ME dhe klikoni OK. Si parazgjedhje, ajo do ta kthejë qelizën në Plotësim të Kuq të lehtë me tekst të kuq të errët, por ju mund ta ndryshoni këtë në kutinë rënëse.

Redaktimi i ngjyrës së formatimit të kushtëzuar.

Formatimi i kushtëzuar është i popullarizuar në kontrollet e gabimeve sepse e bën kontrollin e gabimit të dallohet më shumë kur ai është aktivizuar. Dhe, me çdo fat, përdoruesi do të kuptojë se ka një gabim në modelin financiar. Formatimi i kushtëzuar nuk kufizohet vetëm në kontrollet e gabimeve - ai mund të jetë i dobishëm për modele të tjera ku dëshironi të tërhiqni vëmendjen e përdoruesit ndaj anomalive ose pikave kryesore në llogaritjet.