Открыть свежий номер журнала>> / Оформите подписку по праздничной цене >> ✆ 8 (800) 550-07-98
Журнал

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

  • 1 октября 2018
  • 184297
финансовый директор ООО «Арлифт»
Финансовая модель предприятия

Читайте, как построить финансовую модель предприятия в Excel и скачайте пример. Такая модель поможет не потерять контроль над управлением предприятия и его деньгами.

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

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

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

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

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

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

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

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

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

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

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

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

Янв.

Фев.

Мар.

Дек.

Услуга 1

30

32

34

52

Услуга 2

30

32,5

35

57,5

Услуга 3

65

70

75

120

Итого

125

134,5

144

229,5

Скачайте Excel-модель, которая оценит финансовое состояние компании за 15 минут

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

Скачать Excel-модель

Шаг 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 устанавливаются ссылки на соответствующую ячейку и страницу. Ручной ввод цифр тут недопустим (за исключением данных о начальных остатках в балансе), иначе модель просто не будет пересчитывать данные.

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

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

В нашем примере создаются три листа, на которых размещаются по отдельности план доходов и расходов (ПДР), план движения денежных средств (ПДДС) и прогнозный баланс (ПБ). В плане доходов и расходов (см. табл. 3) строки «Операционные расходы» и «Операционные доходы» заполняются при помощи ссылок на соответствующие ячейки функциональных планов. Выручка расшифрована по видам услуг, затраты – по статьям. В этом случае такая расшифровка допустима, поскольку не утяжеляет восприятие отчета и не усложняет его анализ. Кроме того, в отчет включены два аналитических показателя – рентабельность (как отношение прибыли к выручке) и прибыль нарастающим итогом. Если понадобится провести более глубокий анализ, в частности, динамики доли оплаты труда в себестоимости услуг, все необходимые для этого расчеты лучше проводить на отдельном листе.

Таблица 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.09 вносятся из соответствующей статьи баланса за предыдущий период, а на каждую последующую дату рассчитываются как:

ДЗ = ДЗ на предыдущую дату + Отгрузка периода – Поступление денег периода

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

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

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

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

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

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

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

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

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

ВИДЕО: Как использовать финансовые модели для повышения точности планирования

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

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

Вложенные файлы

Доступно только подписчикам
  • Финансовая модель предприятия.rar

Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет меньше минуты!

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×
Зарегистрируйтесь или войдите на сайт!

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
Пожалуйста, войдите на сайт

Зарегистрируйтесь или войдите на сайт:

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
×

Вам предоставлен неограниченный доступ к «Системе Финансовый директор»

Активируйте доступ прямо сейчас.
Завтра он сгорит!

 
10:11:49


Также, вы сможете прочитать свежий номер журнала Финансовый Директор, архив с 2008 года, скачать книги и формы документов, а также воспользоваться всеми сервисами  для подписчиков.

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