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

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

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

8 800 333 47 29

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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