Скопировать ссылкуОднажды сделанную мной финансовую модель передали для заключения специалистам профессиональной оценочной фирмы. Через некоторое время мы разбирали модель на общей встрече: руководители фирмы-инвестора, заказавшей оценку, руководители фирмы-оценщика и я. «Мы сделали пару небольших изменений», – сообщили оценщики. Изменения в финансовых результатах, однако, были очень значительными. «А что именно вы изменили?» – спросили заказчики. Руководители фирмы-оценщика подумали и ответили: «Надо позвать аналитика, который работал с моделью». Пока они выясняли, кто именно занимался моделью, а потом – где этот человек, мы просматривали модель ряд за рядом и лист за листом. Все числа были те же – кроме финальных.
Пришел аналитик, который тоже не мог вспомнить, что именно он исправил два дня назад. В конце концов, я попросил у оценщиков файл с моделью и начал пошагово двигаться от окончательных результатов к исходным числам. И на примерно тридцатой ячейке и за 4–5 шагов до финального числа налетел на измененную формулу, в которой появился колоссальный поправочный коэффициент. Оценщики долго думали, что бы это могло быть такое – и наконец сообразили: «А! Это же наш гонорар за оценочное заключение!» На эту процедуру у нас ушло полчаса – и только, чтобы инвесторы напомнили оценщикам, что за это заключение им уже было уплачено раньше.
Увы, так работают с моделями 90% всех аналитиков. Чтобы этого не происходило с нами, сразу накрепко усвоим первое правило: переменные – отдельно, структура – отдельно. Ни одна формула не должна содержать численные значения. Никакие. Вообще.
Единственное исключение можно сделать для числа месяцев в году и квартале, и то если вы очень сильно торопитесь. Когда численное значение «вшито» в формулу, при необходимости его придется менять вручную везде, где оно было использовано. Если таких формул много и они рассеяны по таблицам и листам – горе аналитику. Он не просто потратит много времени, но возможно, забудет о какой-то формуле и испортит модель. Менять любое число в модели нужно только в одном месте – там, где оно было введено исходно.
Вот так формула выглядеть не должна:
=10000*(1+10%)^2
Правильный вид формулы – такой:
=B2*(1+B3)^B4
Но еще лучше, когда вы можете записать формулу так:
= credit*(1+stavka)^let
Здесь мы использовали возможность назначать в Excel диапазонам (отдельным клеткам и блокам клеток) «имена». Ячейку B2 мы назвали «credit», B3 – «stavka», а B4 – «let». Имя можно создать, впечатав его в специальное окошечко вручную, а можно подцепить из соседней клетки – команда Create Name это позволяет. Есть и диалог управления именами. С помощью имен можно создавать даже диапазоны динамической длины: но Excel заметно тормозит, когда работает с ними, так что, как бы они ни были удобны, пользоваться ими не рекомендую. С использованием имен формула становится намного нагляднее. Наглядности полезно добавить и визуально – цветом. Все клетки, где происходят какие-нибудь операции с числами, можно разделить на три вида:
- Переменная (вида 10000 ; 10% ; 2 и т.д.). Содержат только числа.
- Вызовы переменных (вида =$A$1 ; =credit ) Формула, не производящая вычислений, а только транслирующая свое значение из одного места в другое.
- Формула (вида SUM($A$1:$A$9) ; =$A1*B$2 ; =credit*let ; и т.д.). Формулы, вычисляющие новое значение по двум и более вводным из других ячеек или диапазонов.
Для каждого такого типа имеет смысл определить свой цвет ячейки (можно еще и шрифт), чтобы по ее виду сразу определять, что там. Ячейки можно форматировать вручную, но куда лучше создать несколько стилей и присваивать их сразу диапазонам. Помимо прочего, это позволит легко поменять внешний вид нужных клеток по всей модели разом. Мои личные предпочтения – светло-оранжевый для переменных, светло-желтый для вызовов и светло-зеленый для формул. Эти цвета при хорошем контрасте на экране не заглушают черный шрифт на распечатках.
Еще несколько важных деталей:
- Закладки листов также участвуют в формулах, так что практично дать и им имена. Первый по счету лист с переменными логичнее всего назвать Assumptions.
- В ячейки переменных могут случайно ввести недопустимое значение. Лучшая защита от этого – функция Data Validation. Она может ограничить численный диапазон или создать в ячейке выпадающий список возможных значений (см. рисунок).
- Лучше избегать кириллических символов в именах диапазонов и листов. Они могут непредсказуемо повести себя в формулах. Рекомендую применять только латинские буквы и цифры, а вместо пробела – знак подчерка «_».
- Если все перечни в модели снабдить порядковыми идентификаторами в первой колонке диапазона, то это сильно упростит последующую работу с ними и их обсуждение. «Посмотрите в пятой строке» куда понятнее, чем «Посмотрите в строке, где «Москва» и два раза по 800». Кроме того, по идентификаторам очень легко искать строки с формулами типа VLOOKUP и восстановить исходный порядок строк, если таблицу кто-то перемешал неудачной сортировкой.
Идеальная модель выглядит не как длинные ряды денежных потоков – это ее служебная часть – а как пульт управления, на котором вводятся переменные и выводятся конечные результаты. Например, так:
Все. Больше на этом листе нет ничего. Условия задачи (оранжевые) и ответ (светло-зеленые) перед нами. Все остальное – ее решение. Ему место на других листах.
Итак, первый этап:
- Переименовываем первый лист нового файла Excel в Assumptions (или любое другое говорящее название) и покрасим его в цвет «переменной».
- Определим все переменные, которые мы будем использовать в деятельности фирмы, и выпишем их в столбец.
- Выберем для каждой переменной имя из латинских символов, по которому мы будем вызывать ее в формулах.
- Когда список окончен – присвоим переменным имена командой Create Names.
- Разместим на листе желаемые финальные показатели модели (формулы их вызова или расчета мы сможем создать только когда модель будет завершена).
- Создадим стиль «assumption» и присвоим его клеткам с переменными.
- Создадим стиль «variable» и присвоим его клеткам с будущими вызовами.
- Создадим стиль «formula» и присвоим его клеткам с будущими формулами.
Домашнее задание: воспроизведите в Excel нарисованную на экране таблицу переменных со всем именами.
