Финансовая модель в Excel: пошаговый алгоритм и пример построения

Скачайте и возьмите в работу:

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

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

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

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

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

• имущество (активы);
• обязательства (пассивы);
• денежные потоки (поступления и платежи денежных средств);
• доходы и расходы.

Подробнее о модели прогнозирования денежных потоков>>

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

Модели в Excel - готовые решения от журнала "Финансовый директор" на каждый день

• Отчеты для акционеров и гендиректора предприятия
• Система формирования управленческой отчетности>>
• Комплексная оценка и анализ инвестпроектов: автоматизация процесса в формате Excel
• Годовой отчет: особенности составления
• Корректировка бюджета: практические примеры игры с цифрами

Больше полезной информации по теме Бюджетирование, читайте по ссылке>>>

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

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

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

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

Рассмотрим на примере компании, занимающейся оказанием услуг, как создать финансовую модель предприятия, описывающую его операционную деятельность в Excel. Этот вид деятельности наиболее прост для финансового моделирования (по сравнению с торговлей или производством), поскольку, как правило, подразумевает меньшее количество ключевых переменных.

Построение финансовой модели: алгоритм

Шаг 1. Задайте внешние параметры

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

На этом этапе выручку можно указать «навскидку» или использовать данные прошлого года. Excel спрогнозирует выручку за пять минут. Пока точность не имеет большого значения. Позднее при детализации модели план продаж придется доработать.

Таблица 1. План продаж, тыс. руб.

Шаг 2. Определите размер переменных затрат

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

Переменные затраты = Доля в выручке * Объем продаж

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

План затрат удобнее разместить на отдельном листе Excel (см. табл. 2). В нем зарплата рассчитывается помесячно как произведение коэффициента 0,3 (30% / 100%) и плана продаж на определенный месяц. Расходы на аренду и управление вводятся на первом этапе не как расчетные величины, а как фиксированные значения. В дальнейшем при детализации модели их можно будет заменить формулами, увязав с другими показателями.

Таблица 2. План затрат, тыс. руб.

План доходов и расходов

В нашем примере создаются три листа, на которых размещаются по отдельности:

• план доходов и расходов (ПДР);
• план движения денежных средств (ПДДС);
• прогнозный баланс (ПБ).

В примере дебиторская задолженность на 1 января равна нулю. В последующие периоды она не возникает. Кредиторская задолженность (КЗ) формируется из-за зарплаты и аренды расходов, поскольку они выплачиваются в месяце, следующем за месяцем начисления:

КЗ = КЗ на предыдущую дату + Начисление затрат текущего периода – Оплата задолженности в текущем периоде

После того как построение баланса подобным образом завершено, остается удостовериться, что все было сделано правильно – активы равны пассивам. В нашем примере они совпадают, значит, модель работает, выдает корректные результаты.

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

Шаг 3. Детализируйте финансовую модель

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

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

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

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

Финансовая модель компании и бюджет

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

Финансовую модель стоит использовать в процессе бюджетирования сразу же после утверждения плана продаж. Если «прогнать» через нее план продаж, то полученный финансовый результат можно показать акционерам, чтобы установить целевые значения по затратам, прибыли, дивидендам.

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

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