Финансовый директор

Как создать наглядную финансовую модель в Excel

4345
Как создать наглядную финансовую модель в Excel
Бувин Николай
финансовый директор ООО «Литэко»
Наиболее сложные конструкции финансовых моделей (ФМ) относятся, как правило, к деятельности производственных предприятий. Для создания качественной и наглядной финансовой модели предприятия в Excel необходимо, в первую очередь, сформировать элементы основных блоков данных и определить их последовательность. Рассмотрим подробнее процесс разработки финансовой модели предприятия в Excel на конкретных примерах.

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

  • условия (задачи);
  • технология;
  • вводные (переменные);
  • производство;
  • отчетность;
  • оценка инвестиций.

Шаг 1. Определение общих условий финансовой модели предприятия в Excel

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

Задача

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

2. Выходные формы: ББЛ (бюджет по балансовому листу, прогнозный баланс), БДР, ДДС проекта и итоговый денежный поток каждого акционера, анализ эффективности инвестиций для Мажоритарного акционера

 Скачайте полезные документы:

Иконка WordБюджетная модель компании

Иконка WordБюджет доходов и расходов

Иконка WordБюджет движения денежных средств

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

Проект

Производство полимерных покрытий на основе наполнителя из базальта

 Технология производства разбивается на несколько ключевых этапов.

Технология:

1. Производство наполнителя (чешуя), получаемого из базальта путем плавления в печи и разбрызгивания струи базальта на центрифуге.

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

2. Товарная чешуя смешивается со смолой №1 на дисольверной (линия смешивания)  – выпускается полимерное покрытие.

3. Побочный продукт смешивается с другой смолой №2 на дисольверной  – выпускаются наливные полы

4. Готовая продукция заливается в бочки по 50 кг. и отправляется на склад готовой продукции

Завершает блок «условия» исчерпывающий перечень предпосылок, включая:

  • объем инвестиций;
  • состав инвесторов;
  • ключевые расходы;
  • условия производства;
  • параметры оборачиваемости рабочего капитала.

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

Инвестиции, инвесторы, постоянные расходы

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

1. Кап. вложения. Стоимость капитальных затрат 100 млн руб. с НДС.

2. Структура акционеров. Мажоритарный акционер = 80%, миноритарный = 20%, УК = 1 млн руб.

Деньги на проект: от Мажоритарного акционера через займ под 15%, после возврата ему займа с % (по мере возможности) - дивиденды

3. Постоянные расходы.

ФОТ = 2 млн руб. в месяц вкл. СВ; Общехозяйственные = 0,5 млн руб. в мес.               

 Описание производства

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

4. Производство:

Печь работает непрерывно 24 часа в сутки, 200 суток в году (остановки раз в месяц), потребляя электроэнергию (200 Квт/час), (количество исходящего базальта в струе = 80 кг/час.

В печи часть базальта выгорает (10%).

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

Из того что попало в сортировку с центрифуг после очистки: Выход товарной чешуи после сортировки 10%, Побочного продукта – 30%, Остальное  – отходы

Потребляемая э/э на дисольверной  – 80 Квт/час

Производительность дисольверной – 800 кг/час по готовому продукту

План продаж

Устанавливаются цены и объемы реализации. Указывается допущения по динамики запасов на складе готовой продукции.

5. Выручка:

Продажи = Объему производства

Цена полимерного покрытия  – 400 руб./кг с НДС

Цена наливных полов  – 300 руб./кг с НДС

Цены на сырье, материалы и ресурсы

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

6. Цены:

Стоимость электроэнергии

2,5 руб. за 1 Квт/час с НДС

Стоимость 1 бочки

50 руб. с НДС

Стоимость базальта с доставкой

10 руб./кг с НДС

Стоимость смолы №1 для полимерного покрытия 

50 руб./кг с НДС

Состав полимерного покрытия 

60% смолы 1, 40% товарной чешуи

Стоимость смолы №2 для наливных полов

30 руб./кг с НДС

Состав наливных полов 

60% смолы 2, 40% побочного продукта

7. Рабочий капитал:

Оборачиваемость запасов 

30 дней

Оборачиваемость кредиторской задолженности

15 дней

Оборачиваемость дебиторской задолженности 

60 дней

со второго года работы

выпуск печи увеличивается на 30% без дополнительных затрат

со второго года

выход чешуи 30%, побочного продукта 30%

Шаг 2. Демонстрация применяемой технологии

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

Рисунок 1. Пример схемы производства, необходимый при создании финансовой модели предприятия в Excel

Пример схемы производства, необходимый при создании финансовой модели предприятия в Excel

Шаг 3. Ввод данных

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

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

Рисунок 2. Представление данных в финансовой модели

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

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

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

Рисунок 3. Пример финансовой модели предприятия в Excel

Пример финансовой модели предприятия в Excel

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

Рисунок 4. Блок расчетов производства в финансовой модели

Блок расчетов производства в финансовой модели

Вводные по выручке задаются только ценой, поскольку есть условие об отсутствии запасов готовой продукции на конец периода (года). Данные оборачиваемости будут использованы остатков рабочего капитала в прогнозном балансе с последующим расчетом динамики РК в БДДС.

Рисунок 5. Данные по выручке в финансовой модели

Данные по выручке в финансовой модели

Шаг 4. Формирование расходов на производство

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

Рисунок 6. Рабочий лист «Производство»

Рабочий лист «Производство»

Шаг 5. Создание основных отчетов (БДР, БДДС, прогнозный баланс)

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

Бюджет движения денежных средств (косвенный)

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

Самый сложный раздел в БДДС в части формул – построение алгоритма дополнительного финансирования и выплат дивидендов. Для этого потребуется создать дополнительный блок с данными по долгу и процентам, начисленным за период. В данном примере с начала проект есть долг в 100 млн. руб., и одна из целей всего проекта – погасить этот долг в пределах пятилетнего горизонта. Для расчета суммы погашения за один году следует воспользоваться формулой ЕСЛИ(И):

=ЕСЛИ (И («Долг на начало периода» > 0; «Долг на начало периода» > «Денежный поток от операционной деятельности» - «проценты за прошлый период»); «Денежный поток от операционной деятельности» - «проценты за прошлый период» ; «Долг на начало периода»).

С помощью данной формулы:

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

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

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

Рисунок 7. Пример БДДС в финансовой модели предприятия в Excel

Как создать наглядную финансовую модель в Excel

Бюджет доходов и расходов

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

Рисунок 8. Бюджет доходов и расходов в финансовой модели предприятия

Бюджет доходов и расходов в финансовой модели предприятия

Бюджет по балансовому листу

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

Рисунок 9. Бюджет по балансовому листу

Как создать наглядную финансовую модель в Excel

Шаг 5. Оценка инвестиций

Завершает финансовую модель предприятия в Excel лист оценки инвестиций. Здесь классифицированы денежные потоки каждого партнера, с учетом вклада в УК, дивидендов, займов и процентов. База для формирования – БДДС. Норма доходности рассчитывается с помощью функции «ВСД».

Рисунок 10. Лист оценки инвестиций в финансовой модели предприятия в Excel (пример)

Лист оценки инвестиций в финансовой модели в Excel

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



Ваша персональная подборка

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

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

    
    ×
    Чтобы скачать документ, зарегистрируйтесь на сайте!

    Это бесплатно и займет всего 1 минуту.

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

    Это бесплатно и займет всего 1 минуту.

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