Как да вградите проверки за грешки във вашия финансов модел на Excel

Дори ако току-що сте започнали да моделирате, вероятно сте наясно колко лесно е да направите грешка във финансов модел! Има три начина за предотвратяване на грешки във финансов модел:

• Избягвайте да правите грешки на първо място. В тази книга описвам няколко техники, които можете да използвате, за да избегнете грешки на първо място, като например да спазвате формулите си.
• Проверете модела за грешки. Въпреки всичките ви усилия, грешките неизбежно могат да се промъкнат, така че проверете, проверете отново и накарайте някой друг да провери вашия модел, след като приключи.
• Включете проверки за грешки. Докато изграждате модела, включете проверки за грешки, които предотвратяват вмъкването на неволни грешки в модела поради неправилни записи или потребителска грешка.

Информацията тук се фокусира върху първите две точки: техники за изграждане на модел за намаляване на грешките, както и начини за проверка на модела за грешки.

Проверките за грешки са критична част от добре изградения финансов модел, така че потребителят или моделистът да може да види с един поглед дали формулите се изчисляват правилно. Например, когато създавате отчети за управление, проверете дали сумата от отчета на всеки отделен отдел се добавя към общия сбор за цялата компания. Това може да стане чрез вмъкване на проста функция IF, наред с други методи.

В показания тук пример е изграден капиталов бюджет с прогнозни дати на разходи в колона E. В графика за капиталови разходи, показан в колони F до Q, разходите се разпределят през цялата година. Разработчикът на модели знае, че общата сума на капиталовите разходи от $124 700, показана в клетка D17, трябва да бъде същата като общата сума на капиталовия график, показана в клетка R17, и ако двете суми не са равни една на друга, тогава моделът не се изчислява правилно. Така че клетката за проверка на грешки E1 съдържа много простата формула =R17-D17.

Проста проверка на грешки.

По-долу можете да видите, че потребител е въвел неправилна стойност в клетка E4. 20 февруари не е валиден запис, тъй като графикът за капиталови разходи позволява само дати през 2019 г. Това означава, че финансовият модел по-долу е неправилен — потребителят е въвел капиталови разходи на стойност $124,700 в модела, но само $115,700 са били разпределени за годината. Числото, което се показва в клетка E1 (9 000), предупреждава потребителя за факта, че има проблем. Изтеглете файла 0401.xlsx и изберете разделите с етикет 4-9 и 4-10, за да опитате сами да задействате тази проверка за грешка.

Задейства се проверка за грешка.

Тази проверка за грешка е много проста и доста дискретна. За да стане по-очевидно, може да предпочетете да включите описанието „проверка на грешки“ до клетката за проверка на грешки в клетка D1, което би направило по-ясно за потребителя какво се е случило, когато се задейства проверката за грешка.

Проверката за грешки, показана по-горе, е предпочитан метод за проверка на грешки, тъй като е толкова лесна и бърза за изграждане. Тъй като връща стойност в случай на грешка, може да е малко прекалено дискретен за вашия вкус — не е задължително да предупреждава потребителя незабавно, че е била допусната грешка. Въпреки това, със сигурност е бърз и лесен за следване и поради тази причина е доста често срещана проверка за грешки, предпочитана от много моделисти.

Ако използвате този вид проверка за грешки, форматирайте го с помощта на стила запетая (намира се в раздела Начало в групата Числа) и премахнете десетичното място и го форматирайте с червен шрифт. По този начин нулата няма да се покаже, ако няма грешка, а червено число ще се покаже, ако има грешка.

Като алтернатива може да предпочетете другата формула за проверка на грешки, като =D17=R17, която ще върне стойността TRUE, ако са еднакви или FALSE, ако не са. Този метод обаче също е обект на фалшива грешка.

Разрешаване на толерантност към грешки

=IF(D17<>R17,”error”,0) е превъзходна проверка за грешки, но от време на време може да върне фалшив резултат за грешка, въпреки че стойностите са еднакви. Тази „бъг“ при проверка на грешки е причинена от факта, че Excel пренася изчисления до 14 знака след десетичната запетая. След това той съкращава стойността и може да причини минутно несъответствие, което ще докладва грешка, когато е само 0.00000000000001.

За да избегнете този потенциален проблем, можете да използвате формула за абсолютна стойност, която би позволила толеранс за грешка. =IF(ABS(D17-R17)>1,”error”,0) ще позволи стойностите да бъдат изключени с $1, преди да докладва грешка. Ако използвате функцията ABS в Excel, това ще приеме абсолютната стойност на резултата, така че няма значение дали е положително или отрицателно число.

Има много вариации на тази формула. Някои моделисти предпочитат да показват думата OK, ако числата са правилни, и да проверяват дали не са.

Прилагане на условно форматиране към проверка за грешка

За да направите проверката за грешка още по-забележима за потребителя, помислете за използването на условно форматиране, за да добавите правило, което кара цялата клетка да стане червена, ако проверката за грешка е била задействана. В раздела Начало на лентата, в групата Стилове щракнете върху бутона Условно форматиране. След това задръжте курсора на мишката върху правилото за подчертаване на клетките и изберете Равно на.

Прилагане на условно форматиране към проверка за грешка.

Когато се появи диалоговият прозорец Equal To, въведете грешката в думата в полето Форматиране на клетки, които са EQUAL TO и щракнете върху OK. По подразбиране ще превърне клетката в светлочервено запълване с тъмночервен текст, но можете да промените това в падащото поле.

Редактиране на цвета за условно форматиране.

Условното форматиране е популярно при проверките за грешки, защото прави проверката за грешки да се откроява повече, когато е била задействана. И с малко късмет потребителят ще разбере, че има грешка във финансовия модел. Условното форматиране не се ограничава до проверки на грешки — може да бъде полезно за други модели, при които искате да привлечете вниманието на потребителя към аномалии или акценти в изчисленията.