Финансовое моделирование в Excel - это планирование показателей, финансовых результатов и составление прогнозной финансовой отчетности. Узнайте, как построить финансовую модель предприятия в Excel и скачайте пример. Модель поможет не потерять контроль над управлением предприятия и его деньгами.
Как разработать финансовую модель компании в Excel
Что такое финансовая модель
Финансовая модель предприятия – это набор взаимоувязанных показателей, характеризующих его деятельность. Цель ее создания – возможность мгновенно просчитать влияние тех или иных изменений на финансовое состояние и результаты работы.
Именно поэтому модель наполняется не значениями, а уравнениями и формулами расчетов, чтобы все зависимые характеристики автоматически пересчитывались после изменения исходных данных. Как применять финансовое моделирование для управления стоимостью бизнеса
Показатели, которые будут использоваться в финансовой модели проекта, можно условно разбить на следующие группы:
- имущество (активы);
- обязательства (пассивы);
- денежные потоки (поступления и платежи денежных средств);
- доходы и расходы.
Подробнее о модели прогнозирования денежных потоков>>
То есть все результаты расчетов могут быть представлены в привычной форме – баланс, отчет о прибылях и убытках и отчет о движении денежных средств по состоянию на произвольную (выбранную) дату в будущем.
Модели в Excel - готовые решения от журнала "Финансовый директор" на каждый день
- Отчеты для акционеров и гендиректора предприятия
- Система формирования управленческой отчетности>>
- Комплексная оценка и анализ инвестпроектов: автоматизация процесса в формате Excel
- Годовой отчет: особенности составления
- Корректировка бюджета: практические примеры игры с цифрами
Больше полезной информации по теме Бюджетирование, читайте по ссылке>>>
Получить все решенияКак построить финансовую модель
Приступая к разработке финансовой модели бизнеса, лучше руководствоваться принципом «от простого к сложному», иначе в попытке учесть все нюансы есть риск запутаться в большом количестве формул и ссылок.
Оправданно сначала создать простейшую модель (с минимальным количеством элементов), установить связи общего характера между внешними параметрами (спрос на продукцию, стоимость ресурсов) и внутренними показателями деятельности предприятия (выручка, затраты, денежные потоки и т. д.). В первой итерации можно не заботиться об особой точности задаваемых параметров.
На этом этапе важнее установить правильные взаимосвязи между переменными так, чтобы финансовая модель предприятия автоматически пересчитывалась после изменения исходных данных и позволяла выстраивать различные сценарии. Уже после этого можно приступить к ее развитию, детализировать показатели, ввести дополнительные уровни аналитики и т. д.
Рассмотрим на примере компании, занимающейся оказанием услуг, как создать финансовую модель предприятия, описывающую его операционную деятельность в Excel. Этот вид деятельности наиболее прост для финансового моделирования (по сравнению с торговлей или производством), поскольку, как правило, подразумевает меньшее количество ключевых переменных.
Построение финансовой модели: алгоритм
Шаг 1. Задайте внешние параметры
Построение финансовой модели в Excel начинается с задания внешних параметров. Отправной точкой для дальнейших расчетов послужит план продаж, в нашем случае – оказания услуг. Для этого в Excel на одном из листов книги размещаем таблицу с планом продаж в денежном выражении (см. табл. 1).
На этом этапе выручку можно указать «навскидку» или использовать данные прошлого года. Excel спрогнозирует выручку за пять минут. Пока точность не имеет большого значения. Позднее при детализации модели план продаж придется доработать.
Таблица 1. План продаж, тыс. руб.
Янв. |
Фев. |
Мар. |
… |
Дек. |
|
Услуга 1 |
30 |
32 |
34 |
… |
52 |
Услуга 2 |
30 |
32,5 |
35 |
… |
57,5 |
Услуга 3 |
65 |
70 |
75 |
… |
120 |
Итого |
125 |
134,5 |
144 |
… |
229,5 |
Шаг 2. Определите размер переменных затрат
Исходя из объема продаж, определите размер переменных затрат. В самом общем виде расчет может выглядеть следующим образом:
Переменные затраты = Доля в выручке * Объем продаж
Сделаем небольшое допущение и предположим, что в примере переменными являются только затраты на оплату труда – заработная плата сотрудников полностью зависит от объема оказанных услуг, на нее уходит примерно 30 процентов выручки от реализации.
План затрат удобнее разместить на отдельном листе Excel (см. табл. 2). В нем зарплата рассчитывается помесячно как произведение коэффициента 0,3 (30% / 100%) и плана продаж на определенный месяц. Расходы на аренду и управление вводятся на первом этапе не как расчетные величины, а как фиксированные значения. В дальнейшем при детализации модели их можно будет заменить формулами, увязав с другими показателями.
Таблица 2. План затрат, тыс. руб.
Янв. |
Фев. |
Мар. |
… |
Дек. |
|
Зарплата |
37,5 |
40,35 |
43,2 |
… |
68,85 |
Аренда |
30 |
30 |
30 |
… |
30 |
50 |
50 |
50 |
… |
50 |
|
Итого |
117,5 |
120,35 |
123,2 |
… |
148,85 |
Как только сформированы два функциональных плана – продаж и затрат, можно приступать к созданию баланса, плана доходов и расходов, а также плана движения денежных средств. Excel-модель, которая составит прогнозный баланс
Данные в них вносятся двумя способами: рассчитываются или извлекаются напрямую из операционных или функциональных планов – продаж, затрат (как в нашем примере) и других, например, дебиторской задолженности, запасов, производства и пр.
В Excel устанавливаются ссылки на соответствующую ячейку и страницу. Ручной ввод цифр тут недопустим (за исключением данных о начальных остатках в балансе), иначе модель просто не будет пересчитывать данные.
Не стоит перегружать планы верхнего уровня (баланс, прибыли и убытки, движение денежных средств) показателями. Лучше стремиться к тому, чтобы каждый из них мог уместиться на одном печатном листе.
Зачастую трудно удержаться от соблазна расшифровать каждую цифру. Например, в плане доходов и расходов расписать выручку по видам продукции, группам клиентов, каналам сбыта и т. п. Если в план доходов и расходов включить сотню видов готовой продукции и статей затрат, это значительно затруднит его восприятие.
Тем не менее, с точки зрения информативности полезно подобные планы дополнять различными относительными показателями. Например, в баланс внести показатели структуры активов и пассивов (удельные веса статей в валюте баланса), в план доходов и расходов – рентабельность.
Узнайте, как:- построить финансовую модель девелоперского проекта в Excel для строительной компании
- убедиться в реалистичности прогнозных показателей финансовой модели инвестиционного проекта
Рекомендации доступны после оформления гостевого доступа к Системе Финансовый директор на три дня.
План доходов и расходов
В нашем примере создаются три листа, на которых размещаются по отдельности:
- план доходов и расходов (ПДР);
- план движения денежных средств (ПДДС);
- прогнозный баланс (ПБ).
Таблица 3. План доходов и расходов, тыс. руб.
Показатели |
Янв. |
Фев. |
Мар. |
… |
Дек. |
Итого |
Операционные доходы, в т.ч. |
125 |
134,5 |
144 |
… |
229,5 |
2127 |
услуга 1 |
30 |
32 |
34 |
… |
52 |
492 |
услуга 2 |
30 |
32,5 |
35 |
… |
57,5 |
525 |
услуга 3 |
65 |
70 |
75 |
… |
120 |
1110 |
Операционные расходы, в т.ч. |
117 |
120,35 |
123,2 |
… |
148,85 |
1598,1 |
зарплата |
37 |
40,35 |
43 ,2 |
… |
68,85 |
638,1 |
аренда |
30 |
30 |
30 |
… |
30 |
360 |
управленческие расходы |
50 |
50 |
50 |
… |
50 |
600 |
7,5 |
14,15 |
20,8 |
… |
80,65 |
528,9 |
|
6,00 |
10,52 |
14,44 |
… |
35,14 |
24,87 |
|
Прибыль нарастающим итогом |
7,5 |
21,65 |
42,45 |
… |
528,9 |
План движения денежных средств
План движения денежных средств (см. табл. 4) в нашем примере формируется со следующими допущениями:
Первое. Разделы «Финансовая деятельность» и «Инвестиционная деятельность» исключены из плана. Предполагается, что предприятие осуществляет только операционную деятельность, не привлекая заемные средства и не осуществляя капитальные вложения.
Второе. Предприятие оказывает услуги физическим лицам за наличный расчет, а значит, время оказания услуги и ее оплаты совпадает – в итоге у предприятия нет дебиторской задолженности.
Ситуация с платежами по операционной деятельности не так однозначна. Зарплата и аренда выплачиваются в месяце, следующем за месяцем начисления, а управленческие расходы – в месяце их осуществления.
Таблица 4. План движения денежных средств, тыс. руб.
Показатели |
Янв. |
Фев. |
Мар. |
… |
Дек. |
Поступления по операционной деятельности |
125 |
134,5 |
144 |
… |
229,5 |
Оплата услуг |
125 |
134,5 |
144 |
… |
229,5 |
Платежи по операционной деятельности |
50 |
117,5 |
120,35 |
… |
146 |
Зарплата |
0 |
37,5 |
40,35 |
… |
66 |
Аренда |
0 |
30 |
30 |
… |
30 |
Управленческие расходы |
50 |
50 |
50 |
… |
50 |
Сальдо по операционной деятельности |
75 |
17 |
23,65 |
… |
83,5 |
Сальдо на начало периода |
10 |
85 |
102 |
… |
554,25 |
Сальдо на конец периода |
85 |
102 |
125,65 |
… |
637,75 |
Прогнозный баланс
Последнее, что остается сделать, – создать прогнозный баланс (см. табл. 5). Скачайте шаблон прогнозного баланса. Данные по оборотам за период берутся из ПДР и ПДДС, начальные остатки – из баланса за предыдущий период (здесь допустимо ручное внесение информации).
Таблица 5. Прогнозный баланс, тыс. руб.
Показатели |
01.01 |
01.02 |
01.03 |
… |
01.12 |
01.01 |
Денежные средства |
10 |
85 |
102 |
… |
554,25 |
637,75 |
0 |
0 |
0 |
… |
0 |
0 |
|
Основные средства |
0 |
0 |
0 |
… |
0 |
0 |
АКТИВЫ, итого |
10 |
85 |
102 |
… |
554,25 |
637,75 |
0 |
67,5 |
70,35 |
… |
96 |
98,85 |
|
Капитал |
10 |
17,5 |
31,65 |
… |
458,25 |
538,9 |
ПАССИВЫ, итого |
10 |
85 |
102 |
… |
554,25 |
637,75 |
Остановимся на формировании некоторых строк баланса несколько подробнее и предварительно оговоримся, что на предприятии остаток денежных средств составляет 10 тыс. рублей, а все остальные остатки – нулевые. Данные о дебиторской задолженности (ДЗ) по состоянию на 01.01.20 вносятся из соответствующей статьи баланса за предыдущий период, а на каждую последующую дату рассчитываются как:
ДЗ = ДЗ на предыдущую дату + Отгрузка периода – Поступление денег периода
В примере дебиторская задолженность на 1 января равна нулю. В последующие периоды она не возникает. Кредиторская задолженность (КЗ) формируется из-за зарплаты и аренды расходов, поскольку они выплачиваются в месяце, следующем за месяцем начисления:
КЗ = КЗ на предыдущую дату + Начисление затрат текущего периода – Оплата задолженности в текущем периоде
После того как построение баланса подобным образом завершено, остается удостовериться, что все было сделано правильно – активы равны пассивам. В нашем примере они совпадают, значит, модель работает, выдает корректные результаты.
Построенная таким образом финансовая модель обозначает основные группы показателей, характеризующих деятельность предприятия (доходы, расходы, денежные средства и т. п.), увязывает их в три сводных плана. Ее можно использовать для сценарного анализа. В частности, если исключить из плана продаж услугу № 1(соответствующую строку удалять не нужно, достаточно проставить по ней нули), то можно увидеть, насколько ухудшатся показатели рентабельности и ликвидности.
Шаг 3. Детализируйте финансовую модель
Чтобы превратить модель в полноценный инструмент сценарного анализа, потребуется «насытить» ее аналитикой, детализировать исходную информацию до показателей, которыми можно управлять на практике. Например, предприятию, оказывающему услуги, надо детализировать план продаж, внесенный ранее в денежном выражении.
Выручку по каждому виду услуг можно рассчитать как произведение цены единицы услуги и количества указанных услуг. На практике, естественно, план продаж формируется исходя из конъюнктуры рынка, ожидаемого спроса, предполагаемой цены реализации, достигнутых договоренностей с ключевыми клиентами, запланированных маркетинговых мероприятий, ценовой и кредитной политики и т.д.
Аналогично детализируются и другие исходные данные. Например, арендную плату можно было бы разложить на площадь арендуемого помещения и стоимость одного квадратного метра, зарплату расписать по сотрудникам, управленческие расходы разбить по видам.
В итоге функциональность модели развивается до такого уровня, что можно увидеть, как влияет изменение любого, даже самого незначительного параметра на конечный результат.
Финансовая модель компании и бюджет
Использование финансового моделирования в Excel при планировании деятельности помогает увидеть, как те или иные планы развития отражаются на структуре активов, пассивов, доходов и расходов предприятия, а также определить, от каких факторов в наибольшей степени зависят будущая прибыль, ликвидность и финансовая устойчивость. См.подробнее о том, как управлять финансовой устойчивостью. Модель служит скорее инструментом мониторинга текущей ситуации на предприятии и выработки адекватной финансовой политики.
Финансовую модель стоит использовать в процессе бюджетирования сразу же после утверждения плана продаж. Если «прогнать» через нее план продаж, то полученный финансовый результат можно показать акционерам, чтобы установить целевые значения по затратам, прибыли, дивидендам.
Если планируемая выручка не обеспечивает необходимой прибыли с точки зрения акционеров, прямо в модели корректируются влияющие показатели. Окончательный вариант расчетов определяет целевые значения бюджетных лимитов для всех центров финансовой ответственности. Как разложить бюджет на целевые показатели для каждого сотрудника
В течение года финансовую модель предприятия можно будет корректировать, проставлять по пройденным месяцам фактические данные вместо плановых и контролировать таким образом финансовые результаты, отслеживать негативные тенденции и четко понимать, к чему они приведут предприятие.