Разработка финансовой модели в Excel

Бизнес-планы > Финансовое моделирование

Консультация по телефону

8 800 333 47 29

бесплатно из любой точки России

СЛОЖНЫЕ ФИНАНСОВЫЕ МОДЕЛИ в EXCEL

• Сложные исходные данные, большой объем

• Консолидированные финансовые модели

• Финансовые модели банков и кредитных кооперативов

• Динамические модели крупных предприятий

Существует множество программ, решающих задачи банков и финансовых компаний,

но Excel по-прежнему остается рабочей лошадкой для большинства профессионалов.

Содержание

Смысл финансового моделирования

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

Финансовая модель - это бизнес-план в цифрах

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

Финансовая модель бизнес-плана обеспечивает полный обзор проекта. Исходные данные при этом предстают в виде набора ключевых допущений и причинно-следственных связей, так что у вас не будет сомнений, что важно, а что – нет.

Структура финансовой модели

При построении финансовой модели в Excel следует помнить, что она готовится для того, чтобы убедить скептически настроенного человека дать денег. Следовательно, структура финансовой модели должна быть достаточно понятной и логичной, чтобы он разобрался в ней за 5 минут.
Она должна быть следующей: входы (исходные данные и допущения) - промежуточные расчеты - прогнозные финансовые отчеты и показатели.
Вышеуказанные группы должны быть визуально разделены друг от друга. Однако формулы должны связывать входящие в них элементы. В идеале их желательно разнести по разным листам и выделять группы листов цветом.

Классическая финансовая модель в Excel не должна содержать скрытых или заблокированных ячеек. Специалист, который будет анализировать составленную вами модель должен иметь возможность самостоятельно оценить используемые формулы и данные, заменить их и просмотреть влияющие и зависимые ячейки. Поэтому в составе формул могут присутствовать только ссылки на ячейки или на результаты расчетов.
Классическая финансовая модель должна давать возможность вносить изменения в исходные допущения и автоматически корректировать результаты после такого изменения.
К наиболее сложной части финансовой модели в Excel относится расчет финансовых показателей (NPV, IRR, PI, EBITDA и т.д.).
Также технически сложно создать автоматический модуль анализа чувствительности. Однако, все это возможно "сделать руками" с небольшим напряжением головного мозга. Примеры финансовых моделей можно взять тут

Мастер-класс по финансовому моделированию

_______________________________________________________________________

проводится у нас в офисе, по программе подготовки наших сотрудников

Исходные данные для финансовой модели

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

На схеме справа (кликабельна) данные для финансовой модели бизнес-плана представлены в структурированном виде, разбитые по группам "инвестиции - доходы - расходы". Схема дает представление, какие исходные данные для расчетов надо собрать.

ЗАДАЧА: собрать данные в одном месте, сгруппировать, систематизировать и обеспечить обзорность

Мы разработали анкету исходных данных финансовой модели для удобства их сбора и структурирования. Вы тоже можете ей пользоваться, скачать анкету финансовой модели бизнес-плана можно тут.

Исходные данные для финансовой модели бизнес-плана

Инструкция по работе с анкетой данных для модели

Если вы не занимаетесь финансовым моделированием каждый день - даже заполнение анкеты может вызывать трудности.

Для того чтобы вам было проще подготовить исходные данные для расчетов, мы разработали презентацию с инструкцией - как ее использовать.

Инструкция "Как пользоваться анкетой данных для финансовой модели" находится по этой ссылке.

NB! Финансовые модели - это не об абсолютных числах; они о связях. Хорошая финансовая модель демонстрирует связи, допущения и условия, которые вместе создают потенциал прибыльности бизнес-идеи.

Если вы понимаете связи, драйверы доходов, источники затрат и критические факторы успеха - вы понимаете суть моделируемого бизнеса.

Лучшие практики построения финансовых моделей от EY

1. Не смешиваем исходные данные, расчеты и результаты. Размещаем их на разных листах.

2. Структура расчетов должна быть логичной и понятной.

3. Столбцы, даже находящиеся на разных листах, должны соответствовать друг другу.

4. Используйте однотипные формулы в одной строке.

5. Не используйте в формулах значения входов.

6. Не допускайте циклических ссылок в финансовой модели.

7. Не используйте скрытые строки и столбцы.

8. Ограничьте использование имен массивов

9. Не усложняйте модель. Чем проще - тем лучше.

10. Всегда думайте об удобстве того, кто будет использовать эту модель.

Золотые правила финансового моделирования KPMG

Разделяйте входы, расчеты и результаты финансовой модели.
Используйте разные рабочие листы и разделы для разных «тем».
Используйте одинаковые периоды времени в столбцах во всей модели.
Не используйте скрытые столбцы или строки.
Используйте единообразное форматирование во всей модели.
Используйте одни и те же формулы в аналогичных строках и столбцах.
Соблюдайте логическую последовательность расчетов - предыдущие расчеты слева и выше, последующие расчеты - справа и ниже.
Не используйте в формулах числа - только ссылки на ячейки модели.
Ограничивайте использование массивов.
Избегайте внешних ссылок.
Не допускайте циклических ссылок.
Упрощайте финансовую модель!

Скачать примеры финансовых моделей

Вот несколько финансовых моделей (не наших), которые были подготовлены для Внешэкономбанка и других крупных банков, доступных для скачивания:

Модель от Номос-Банка можно скачать тут
Модель от Газпромбанка можно скачать тут
И модель из бизнес-плана, сданного в ВЭБ можно скачать тут

Цветовая кодировка финансовых моделей

Правильное форматирование финансовой модели в Excel упрощает для других людей ее чтение и улучшает понимание логики, которую вы в нее заложили. Да и для вас тоже, если спустя некоторое время вы решите внести в модель изменения.

Для этого используются универсальные стандарты цветовой кодировки финансовых моделей – формул, констант и ссылок.

Профессиональное форматирование наиболее важно для актуальных финансовых моделей, и в меньшей степени - для других наборов данных в Excel (заказов, клиентов, финансовых отчетов и т.п.).

Но в любом случае, таблицы Excel должны легко читаться и быть понятными.

Важно помнить, что в инвестиционном бизнесе форматирование ваших расчетов отражает также и ваш профессионализм в целом. Криво оформленный файл Excel может испортить впечатление от вашего проекта еще до того, как его начнут изучать.

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

NB! Даже если в вашей модели полно ошибок, а ее структура – непоследовательна, пусть у нее будет хотя бы правильная цветовая кодировка.

Стандарты цветовой кодировки

Цветовая кодировка позволит любому аналитику или пользователю быстро понять, что в модели можно изменять (константы) и чего нельзя трогать (формулы).

Конечно, все это можно понять, нажимая на ячейках F2, но это трудоемко и неудобно. Так что – используйте правильную цветовую кодировку.

Ссылки, константы и формулы.

Вот несколько стандартов цветовой кодировки прямых ссылок на ячейки на других листах, констант и формул:

Синий (или голубой) шрифт: все константы (то есть числа, вводимые вручную); используемые при отображении исторических финансовых данных, а также в ячейках раздела модели «Допущения». Чтобы выделить ячейки, предназначенные для ввода исходных данных, в нашей компании принято делать их с желтой заливкой. Это позволяет выделить такие ячейки относительно других, но такое форматирование не является обязательным.
Черный шрифт: Все формулы, в том числе – формулы, в составе которых присутствуют ссылки на другие листы (кроме формул, в которых участвуют прямые ссылки на другие листы. Например – если мы перемножаем два числа, которые находятся в ячейках на других листах. Для таких формул используется зеленый шрифт).
Зеленый шрифт: Все прямые ссылки на ячейки в других листах и формулы, в состав которых входят прямые ссылки на другие листы.

А что насчет текста? С технической точки зрения он должен был бы быть синим, так как является «константой». Однако, это сильно затруднило бы чтение модели, поэтому оставляем текст черным.

За исключением текста, который вводится в ячейку в качестве данных – он, конечно, должен быть синим.

Примеры цветовой кодировки модели

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

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

Создание финансовой модели подобно проекту, реализацию которого должна взять на себя компания. Как в любом другом проекте, важнейшим этапом при создании фин. модели является ее тестирование. Однако ввиду того, что тестирование модели в большинстве случаев оставляют на самый последний момент, временные рамки попросту не позволяют сделать это качественно, а недобросовестная финансовая модель, в свою очередь, может привести к гораздо более серьезным последствиям, нежели чем просрочка сроков сдачи проекта. В этой статье мы рассмотрим почему именно тестирование модели очень важно, а также разберем, как именно стоит проводить тестирование модели.

Зачем нужно тестировать финансовые модели?

На основе расчетов, заложенных в финансовую модель компании, зачастую принимаются ключевые управленческие решения. Следовательно, для составителя модели необходимо быть уверенным, что данные, лежащие в основе модели достоверны и не содержат ошибок. Среднестатистическая финансовая модель состоит из нескольких тысяч математических операций и расчетов, производящихся одновременно, поэтому не может быть гарантий, что построенная финансовая модель не будет содержать неточностей или ошибок. Однако, если модель будет протестирована, вероятность получения ошибки можно свести к нулю. С другой стороны, если тестирование будет проведено недостаточно качественно или не проведено в принципе – скорее всего, на последних стадиях модели вы обнаружите ошибки. Ошибки, выявленные впоследствии аудиторами, могут вызвать не только недоверие к самой модели, но и нанести непоправимый урон репутации компании в целом. Именно поэтому гораздо лучше провести детальный анализ модели на этапе ее создания, чем нежели исправлять уже появившиеся недочеты, выявленные надзорными органами.

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

Многие эксперты часто путают тестирование модели с проверкой модели, отождествляя эти выражения. Давайте разберемся, в чем же заключается различие между этими двумя понятиями.

Тестирование модели означает проверку на точность и корректность каждой математической операции, осуществляемой в рамках модели, в то время как проверка модели – это выявление соответствия основных элементов модели будущей деятельности компании и потребностям бизнеса. То есть в первом случае анализ чисто математический, во втором же – смысловой. Рассмотрим следующий пример: клиент попросил создать десятилетнюю финансовую DCF-модель. На что при работе обратит проверяющий (аудитор), а на что – тестировщик? Первый будет смотреть на сколько лет была составлена финансовая модель, а также, соответствует ли она базовым предпосылкам и учитывает ли особенности бизнеса (например, учтена ли налоговая система страны, если бизнес будет открыт за рубежом; учтены ли отраслевые особенности налогообложения). Тестировщик же, помимо пунктов, перечисленных выше, включит в свою работу проверку каждой математической операции, производящейся в модели, а также проведет анализ будущих денежных потоков компании с поправкой на инфляцию.

Кто должен тестировать модель?

В идеале человек, проводящий тестирование модели не должен являться частью команды, составлявший ее, поскольку при проверке модели на точность нужно иметь свежий взгляд. В то же время тестировщик модели должен иметь по крайней мере не меньший уровень знаний, чем ее составители. Очень хорошо, если это составитель моделей, не принимавший участие в разработке конкретной финансовой модели, но регулярно занимающийся составлением финансовых моделей.

Когда нужно тестировать финансовую модель?

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

Наиболее распространенные виды тестирования

Существует много функционала, который должен быть протестирован в финансовых моделях, вот лишь некоторых из них:

1. Входное тестирование

Каждая финансовая модель начинается с ввода данных от конечного пользователя. Поэтому важно убедиться, что модель не принимает неверные входные данные. Интеллектуальная модель имеет встроенные проверки, которые предупреждают пользователя о заполнении необычных значений. Следовательно, проверка модели на входные значения означает намеренный ввод неверных значений в качестве входных данных и проверку того, предупреждает ли система пользователя. Неправильные значения обычно включают исключительно большие или малые значения. Они также включают отрицательные значения или даже ноль. Хорошо продуманная модель не позволит вводить неверные значения. Плохо спроектированная модель будет принимать неверные значения, осуществлять операции с ними и выдавать неверные результаты. Из-за этого пользователю придется вручную проверить свои входные данные, чтобы устранить основную причину возникшей ошибки.

2. Числовое тестирование

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

3. Сравнение моделей

Тестирование также может быть проведено путем сравнения двух моделей. Обычно это происходит, когда более новая модель заменяет более старую версию. В таких случаях результаты, полученные новой моделью, сравниваются со старой моделью, чтобы выявить недостатки.

4. Техническое тестирование

Наконец, финансовые модели также необходимо проверить на совместимость с системой. Финансовые модели могут быть чрезвычайно объемными электронными таблицами или приложениями. В результате для их работы может потребоваться определенное количество вычислительной мощности. Если не учитывать технические нюансы, вся система может выйти из строя. Поэтому техническое тестирование необходимо для обеспечения работы модели должным образом с учетом технических характеристик компьютера пользователя.

Можно сделать вывод, что тестирование модели – не менее важный этап, чем ее составление, поскольку поверхностное тестирование модели делает бессмысленным не только ее содержание, но и целесообразность ее составления в принципе.

ФИНАНСОВАЯ МОДЕЛЬ ДЛЯ БАНКА

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

Мы оказываем услуги от разработки финансовых моделей для банков до создания сложных инвестиционных презентаций.

Работая с нами, вы получаете:

• Руководство по использованию финансовой модели.
• Динамические исходные данные.
• Визуальное представление в виде графиков и диаграмм.
• Возможность протестировать чувствительность проекта к изменению факторов.
• Показатели для принятия решений (IRR, NPV, период окупаемости и др.).
• Систему контроля ошибок в расчетах

СТАНДАРТНЫЕ ФИНАНСОВЫЕ МОДЕЛИ

За время своей работы мы "набили руку" на ряде наиболее востребованных на рынке финансовых моделей и готовы предложить их в качестве стандартного продукта.

Финансовая модель для ФРП

Финансовая модель для Фонда Развития Промышленности - модель, которую мы делаем довольно часто и уже имеем свою историю успеха.

Около 30 финансовых моделей нашей разработки были одобрены ФРП.

Модель для Фонда Моногородов

Фонд Моногородов в последнее время увеличил объем выдаваемых кредитов, что повлекло за собой рост спроса на финмодели по его структуре. Это один из наиболее сложных видов моделей на рынке, но мы справляемся.

Финансовые модели для ОЭЗ/ТОСЭР/ТОР

Финансовые модели для Особых Экономических Зон и Территорий Опережающего Социально-Экономического развития не представляют для нас особых трудностей. Мы делаем их в большом количестве и всегда успешно.

Модель для фонда развития информационных технологий

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

ФИНАНСОВАЯ МОДЕЛЬ БАНКА ИЛИ КРЕДИТНОГО КООПЕРАТИВА

Финансовая модель банка и финансовая модель кредитного кооператива отличаются от обычных моделей дополнительными модулями - расчетом финансовых нормативов и контроля их соответствия требованиям ЦБ РФ.

В модели также рассчитываются обязательные резервы по привлекаемым средствам и формирование резервного фонда по видам размещения средств, в том числе с учетом допущений по доле сомнительных и безнадежных вложений. При этом, финансовая модель банка также содержит два баланса - по счетам лоро и ностро, удовлетворять и всем стандартным требованиям к моделям, обеспечивая необходимый обзор его деятельности, прогноз движения денежных средств и финансовых показателей. При этом модель банка или кредитного кооператива должна быть динамической, позволяя сотрудникам менять исходные допущения в процессе ее использования.

Глубина финансовой модели

При выборе глубины финансовой модели обычно мы ориентируемся на финансовые показатели проекта.

Сначала делаем расчеты на 5 лет, если показатели не очень хорошие - начинаем удлинять, чтобы дать накопиться положительному денежному потоку.

В ряде случаев использование excel обязательно - целый ряд банков, включая ГК "Внешэкономбанк" требуют предоставления финансовых расчетов именно в таком виде. Это требование связано с их желанием самостоятельно протестировать результаты расчетов при анализе бизнес-плана. Но сами мы предпочитаем финансовые модели бизнес-планов в Project Expert 7.55. Моделирование в нем обеспечивает высокую производительность и позволяет анализировать проект по многим факторам, произвольно меняя масштаб временной шкалы, чего сложно добиться в excel.

семинар по финансовому моделированию в Ernst & Young

Сложные финансовые модели

К сложным задачам моделирования относятся построение консолидированных финансовых моделей, объединяющих в себе модели целого ряда компаний с построением общих Cash Flow и P&L и расчетом объединенных финансовых показателей.

Еще один вариант сложных моделей связан с большими наборами исходных данных, которые описываются комплексными автоматизированными условиями.

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

Обзор нашей финансовой модели

Анализ риска в финансовой модели

Анализ риска в стандартной финансовой в модели присутствует в трех видах:

1. Расчет ставки дисконтирования. Теоретически, если она рассчитана правильно, а NPV проекта - положительный, можно считать, что с учетом всех рисков в проект можно идти.

2. Анализ безубыточности. Этот анализ учитывает риск недостаточных продаж. Если запас финансовой прочности получается больше 100%, то риск считается низким.

3. Анализ чувствительности проекта. Анализ чувствительности показывает, как изменятся показатели проекта при изменении существенных для него факторов - объема продаж, прямых издержек и т.д. На выходе получаем диапазон ошибки в исходных данных, в пределах которого проект продолжает окупаться.

Как сделать анализ чувствительности в Excel?

Анализ чувствительности в финансовой модели используется для определения допустимых диапазонов изменения параметров проекта, при которых его показатели сохраняют приемлемый уровень.

Стандартно анализируется изменение NPV в зависимости от таких параметров, как цена и объем продаж, прямые и общие издержки, объем инвестиций, затрат syf персонал и еще несколько показателей.

В наших моделях для построения анализа чувствительности по разным параметрам используем функцию Excel «Таблица данных». (Путь: Данные => Анализ «что если» => таблица данных (см. рисунок ниже).

как сделать анализ чувствительности в Excel

Рассмотрим пример влияния изменения «Цены продажи» на NPV проекта, то есть как повлияет отклонение цены на значение чистого приведенного дохода, высокая ли зависимость от данного параметра.

анализ чувствительности в финансовой модели в Excel

В ячейке R4 стоит ссылка на расчётную ячейку NPV, т е ссылаемся на значение, которое хотим проанализировать.

В диапазоне ячеек Q5:Q15 можно ставить любые значения – это будет отклонение «Цены продажи» от имеющегося значения. Например, если значение -10%, то это означает, что цена реализации продукции снизится на 10% и составит 90%.

Как это происходит: используется нулевая ячейка (в нашем случае R2), на которую будем ссылаться в формуле расчета цены реализации по периодам. Формула будет выглядеть следующим образом =Цена*(1+ $R$2). Обязательно закрепить ячейку, чтобы при продлении формулы на все периоды проекта ссылка на ячейку была постоянной. Данная формула никак не влияет на результат проекта, она участвует в «Подборе данных».

анализ чувствительности в финансовой модели

Что происходит, когда нажимаем ОК?! Вместо нулевой ячейки подставляются поочередно значения из диапазона Q5:Q15. Например, если цена реализации снизиться на 10%, то NPV проекта упадет больше чем на 200%, вместо 60 млн. рублей станет -69 млн. рублей. Это нам говорит, что изменение цены продаж оказывается огромное влияние на показатели проекта, что нужно следить за целевой политикой в компании.

Пример анализа чувствительности финансовой модели

анализ чувствительности в модели в excel

Готовая финансовая модель

Чужую финансовую в Excel стартапа часто бывает трудно использовать. Модель - это многолистовой файл со множеством формул, а логика ее построения находится в голове у разработчика. Проще написать ее "с 0", чем разбираться в чужой

Но если вы купите финансовую модель у нас - эта проблема снимается. Вы всегда сможете позвонить нам и задать свои вопросы, а мы все поясним. Это позволит сэкономить уйму времени, получить качественный продукт и консультационную поддержку. А цена - чисто символическая.

Звоните нам, чтобы заказать финансовую модель, или заполните форму запроса.

Программы для финансовых моделей

Классической программой для разработки финансовых моделей бизнес-планов является MS Excel, большинство моделей мы готовим в нем. Однако существует одна отличная программа для разработки финансовых моделей бизнес-планов - Project Expert. Моделирование в этой программе дает множество возможностей, недоступных в excel, особенно в плане анализа проекта. У нас есть эта программа. Когда требуется быстро прикинуть эффективность вложений или клиент предъявляет повышенные требования к использованию модели - мы проводим расчеты в ней.

Преимущества моделирования в Excel

Финансовая модель в Excel дает возможность:
• спланировать деятельность по проекту, внести ясность в соотношение его эффективности и планируемых затрат на его реализацию;
• проанализировать финансовые показатели проекта, такие как NPV, IRR, PBP, WACC и др.;
• вводить и анализировать любые изменения в проект.
К преимуществам использования моделирования в Excel относится то, что получаемая финансовая модель гибка и понятна. Вы с любой момент можете посмотреть формулу расчета того или иного показателя и изменять исходные данные проекта по своему усмотрению. Еще одно преимущество построения финансовой модели в Excel - то, что все расчеты идут последовательно и обоснованно.
Для построения финансовой модели в Excel необходима следующая информация по проекту:
• баланс компании на последнюю отчетную дату;
• список продуктов, цены, объем продаж, способы оплаты;
• перечень издержек компании, таких как прямые и общие издержки, заработная плата персонала;
• условия финансирования;
• инвестиционный план проекта;
• условия лизинга (если имеется).

Выходами финансовой модели в Excel являются:

• отчет о прибыли и убытках;
• отчет о движении денежных средств;
• баланс;
• финансовые показатели проекта.

Опыт успешной подготовки моделей

Подготовленные нами финансовые модели успешно проходили экспертизу в:

Фонде поддержки промышленности
Внешэкономбанке
Россельхозбанке

NB! ВЭБ и другие банки, которые пользуются его стандартами при анализе бизнес-планов, не принимают модели в Альт-Инвест, что бы ни утверждали сотрудники этой многоуважаемой компании. Мы несколько раз пытались упростить себе жизнь и договориться с сотрудниками Внешэкономбанка о подготовке финансовой модели в Альт-Инвесте - и всегда получали отказ.

Давайте обсудим ваш проект

Чтобы связаться с нашими специалистами, позвоните по бесплатному номеру 8 800 333 47 29,

направьте e-mail на адрес info@masterplans.ru или заполните форму ниже:

Дальше: разработка финансовых моделей