Як вбудувати перевірки помилок у вашу фінансову модель Excel

Навіть якщо ви тільки почали моделювати, ви, напевно, добре знаєте, як легко помилитися у фінансовій моделі! Є три способи запобігти помилкам у фінансовій моделі:

• Уникайте в першу чергу помилок. У цій книзі я описую кілька прийомів, які ви можете використовувати, щоб уникнути помилок, наприклад, дотримуватись своїх формул.
• Перевірте модель на наявність помилок. Незважаючи на всі ваші зусилля, помилки неминуче можуть проскочити, тому перевірте, перевірте ще раз, і нехай хтось інший перевірить вашу модель після її завершення.
• Включіть перевірку помилок. Під час створення моделі включайте перевірки помилок, які запобігають потраплянню випадкових помилок у модель через неправильні записи або помилку користувача.

Інформація тут зосереджена на перших двох пунктах: техніках побудови моделі для зменшення помилок, а також способах перевірки моделі на наявність помилок.

Перевірка помилок є важливою частиною добре побудованої фінансової моделі, щоб користувач або розробник моделі могли з першого погляду побачити, чи правильно обчислюються формули. Наприклад, створюючи управлінські звіти, переконайтеся, що сума звітів кожного окремого відділу додається до загальної суми компанії. Це можна зробити, вставивши просту функцію IF, серед інших методів.

У наведеному тут прикладі капітальний бюджет було створено з приблизними датами витрат у стовпці E. У графіку капітальних витрат, показаному в стовпцях F–Q, витрати розподіляються на весь рік. Розробник моделі знає, що загальна сума капітальних витрат у розмірі 124 700 доларів США, показана в клітинці D17, повинна бути такою ж, як і загальна сума капітальних витрат, показана в клітинці R17, і якщо ці дві суми не збігаються одна з одною, тоді модель обчислюється неправильно. Отже, клітинка E1 для перевірки помилок містить дуже просту формулу =R17-D17.

Проста перевірка помилок.

Нижче ви можете побачити, що користувач ввів неправильне значення в клітинку E4. 20 лютого не є дійсним записом, оскільки графік капітальних витрат передбачає лише дати протягом 2019 року. Це означає, що наведена нижче фінансова модель є неправильною — користувач ввів у модель капітальних витрат на суму 124 700 доларів США, але лише 115 700 доларів США було розподілено на рік. Число, що відображається в клітинці E1 (9000), попереджає користувача про те, що є проблема. Завантажте файл 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, якщо цифри правильні, і перевіряти, якщо вони ні.

Застосування умовного форматування до перевірки помилок

Щоб зробити перевірку помилок ще більш помітною для користувача, подумайте про використання умовного форматування, щоб додати правило, завдяки якому вся клітинка стає червоною, якщо перевірка помилок була запущена. На вкладці «Головна» стрічки в групі «Стилі» натисніть кнопку «Умовне форматування». Потім наведіть курсор миші на правило виділення клітинок і виберіть Дорівнювати.

Застосування умовного форматування до перевірки помилок.

Коли з’явиться діалогове вікно «Дорівнює», введіть слово « помилка» у полі «Формат клітинок, які є РІВНІ» та натисніть «ОК». За замовчуванням він перетворює клітинку на світло-червону заливку з темно-червоним текстом, але ви можете змінити це у спадному списку.

Редагування кольору умовного форматування.

Умовне форматування є популярним у перевірці помилок, оскільки воно робить перевірку помилок більш помітною, коли її запускають. І, якщо пощастить, користувач зрозуміє, що у фінансовій моделі є помилка. Умовне форматування не обмежується перевіркою помилок — воно може бути корисним для інших моделей, де ви хочете звернути увагу користувача на відхилення або виділення в розрахунках.