Как построить финансовую модель в Excel

Зачем нужна финансовая модель

Финансовая модель — это бизнес-план, выраженный в цифрах. Собранная корректно, она показывает, как поведут себя выручка, денежный поток и стоимость бизнеса при разных предпосылках, и помогает принимать решения до того, как вложены деньги.

Хорошая модель отвечает на главные вопросы того, кто даёт финансирование, — сколько нужно вложить, когда проект выходит в плюс, что будет при ухудшении условий. Она проходит путь от простейшего планирования движения денежных средств до основы для оценки стоимости предприятия.

Структура модели

Любая надёжная модель делится на три слоя — входы, расчёты и выходы. Их разносят по отдельным листам и связывают формулами. Такое разделение делает модель прозрачной: проверяющий за несколько минут видит, какие цифры заданы вручную, а какие посчитаны.

Допущения держат отдельно и группируют по типам, расчёты не смешивают с вводом, а итоги собирают на сводном листе. Меняя одну вводную, вы пересчитываете всю модель и сразу видите результат.

Исходные данные и допущения

Допущения — фундамент модели, и собирать их лучше отдельным листом, сгруппировав по типам: цены и тарифы, объёмы, затраты, налоги, параметры финансирования. Чем аккуратнее собран этот слой, тем легче потом проверять и менять модель.

Удобный способ ничего не упустить на старте — заполнить анкету исходных данных, где перечислены все нужные параметры по разделам.

Десять правил хорошей модели

Эти правила собраны из практики и из отраслевых стандартов моделирования. Они делают расчёт понятным, проверяемым и пригодным для защиты перед банком или комитетом.

1. Будьте проще. Простота соотносится с уровнем читателя — модель, понятную предпринимателю, эксперт назовёт примитивной, и наоборот. Делайте максимально просто, но не проще самой задачи.
2. Отделяйте входы от расчётов и результатов. Допущения — на отдельный лист, сгруппированные по типам. Тогда поменять вводную и пересчитать модель можно за минуту.
3. Не зашивайте константы в формулы. Любое число, которое может измениться, выносите в ячейку-допущение. Цифра, спрятанная внутри формулы, рано или поздно станет источником ошибки, которую никто не найдёт.
4. Пользуйтесь цветовой кодировкой. Единый цвет для ручного ввода, формул и ссылок на другие листы — и модель читается без пояснений автора. Подробнее об этом ниже.
5. Избегайте длинных формул. Десять последовательных простых формул проверить легче, чем одну гигантскую. Разбивайте расчёт на шаги.
6. Встраивайте проверки на ошибки. Контрольный лист собирает все сверки — сходится ли баланс, совпадают ли итоги — и сразу сигнализирует, если что-то разошлось.
7. Закладывайте анализ чувствительности и сценарии. Модель должна показывать не одну картину, а диапазон исходов при изменении предпосылок.
8. Сделайте сводный лист с дашбордом. Графики, таблицы и итоговые показатели на одном экране для инвестора или кредитного комитета.
9. Не допускайте циклических ссылок. Они делают модель нестабильной и медленной. Разрывайте циклы отдельным расчётом, а итеративные вычисления включайте осознанно.
10. Думайте о тех, кто будет читать модель. Её будет проверять скептик. Стройте так, чтобы он мог сам пройтись по логике, поменять допущение и увидеть результат.

Цветовая кодировка ячеек

Цвет шрифта подсказывает, что за число в ячейке, ещё до того как вы откроете формулу. Это отраслевой стандарт, и придерживаться его стоит во всей модели единообразно.

Не злоупотребляйте яркими заливками и вычурным оформлением — кодировка должна помогать чтению, а не превращать лист в пёструю мозаику.

Проверка и тестирование модели

Проверяют две разные вещи — механику и результаты. Механика отвечает на вопрос, считают ли формулы то, что задумано. Проверка результатов отвечает, осмысленны ли полученные цифры. Систематичный подход к моделированию не избавляет от ошибок полностью, поэтому тестирование закладывают заранее.

Виды тестирования

• Входное. Модель не должна принимать заведомо неверные данные — отрицательную выручку или ноль там, где его быть не может. Продуманная модель предупредит пользователя о подозрительном значении.
• Числовое. Проверка математических тождеств. Например, активы баланса должны равняться сумме обязательств и собственного капитала.
• Сравнительное. Сверка новой версии модели со старой, чтобы увидеть, что именно изменилось и нет ли непредвиденных расхождений.
• Техническое. На больших объёмах модель не должна тормозить и ломаться — проверяют скорость пересчёта и стабильность файла.

Глубина и горизонт модели

Горизонт расчёта обычно берут на пять лет. Если на пятилетке показатели не выходят на нужный уровень, горизонт удлиняют, чтобы дать денежному потоку накопиться. Глубину детализации подбирают исходя из цели — для оценки проекта достаточно помесячного первого года и годовых периодов далее.

Сложные модели уходят глубже — консолидация по нескольким направлениям, расчёт на больших массивах данных, пересчёт сценариев на лету. Здесь особенно важна та самая прозрачная структура, иначе модель быстро становится неуправляемой.

Excel или специальные программы

Существует много программ для финансовых расчётов, но Excel по-прежнему остаётся рабочей лошадкой профессионалов — за гибкость и прозрачность, ведь в нём видно каждую формулу. Специальные пакеты вроде Project Expert удобны типовыми расчётами, но менее прозрачны для внешнего проверяющего.

Выбор зависит от того, кто будет читать модель. Ряд институтов, включая ВЭБ.РФ, требует расчёты в Excel или в собственном формате и не принимает сторонние пакеты, поэтому формат стоит уточнить заранее, до начала работы.

Модели для банков, ГЧП и госпрограмм

Финансовая модель банка или кредитного кооператива сложнее обычной — к ней добавляются расчёт обязательных резервов и нормативов и контроль их соответствия требованиям ЦБ РФ, а также два баланса по счетам лоро и ностро.

Модели для ГЧП и концессий учитывают длинную структуру соглашения и источники возврата вложений на горизонте в десятки лет. У каждого фонда — ФРП, РФРИТ, Фонда моногородов, ОЭЗ, ТОСЭР, ТОР — свой формат и свои требования к оформлению расчёта.

Частые вопросы