Инструкция создания финансовой модели в Excel

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

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

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

Для создания финансовой модели необходимо в Excel шаблон внести:

Общие исходные данные проекта;

Данные о продажах.

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

2. Безопасность и устойчивость модели

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

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

Это поможет и легче ориентироваться в модели, и делать меньше ошибок.

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

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

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

Особый случай – это когда ячейка должна содержать только одно из нескольких возможных значений. Например, в ячейке B11 у нас может быть только флаг: 1 или 0. Тогда мы можем определить проверку данных по списку.

Теперь ячейка не просто редактируется. Она превратилась в выпадающее меню и вы всегда видите какие варианты здесь допустимы.

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

Сначала мы выделяем все ячейки, которые будут оставаться редактируемыми. Вызываем диалог формата ячейки. И в последней закладке отключаем флаг Заблокировать ячейку. Теперь когда весь лист будет защищен от изменений, эти ячейки останутся незащищенными. Дальше мы выбираем защиту листа. Если вы хотите, чтобы никто кроме вас не мог отключить эту защиту, то установите пароль. Часть действий с защищенным листом можно разрешить включая и выключая соответствующие флаги.

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

От таблиц в Excel к моделям данных: как автоматизировать создание отчётов для бизнеса.

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

Представлять данные в виде таблицы событий я начал, когда вёл управленческую отчётность для своего бизнеса. Я записывал все поступления и расходы денег по строкам с указанием даты, суммы, типа операции и контрагента. На основании этих данных я автоматически строил отчёт о движении денежных средств через функции СУММЕСЛИ и СУММЕСЛИМН. Это же можно было делать через сводные таблицы.

Потом в мою жизнь пришёл Microsoft Power BI. Это сервис для анализа и визуализации данных. Позволяет подтягивать данные из разных источников, включая 1С, базы данных, Файлы Excel.

Ключевая разница с Excel — это подход к организации данных. Перед созданием визуализаций нужно сделать модель данных. Это набор таблиц-справочников и таблицы событий, связанных между собой. По-сути вы создаёте базу данных и правила расчета показателей на основе этих данных. Модель данных позволяет делать визуализации интерактивными.

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

Автор текста: Дмитрий Колесников, основатель компании 3K Consulting & Digital — внедрение и ведение управленческой отчётности для бизнеса.

Уроки профессионального финансового моделирования в Excel

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

4 принципа постановки дедлайна, которые заставят вас его придерживаться Как построить карьеру в международной компании Анализ рынка труда в области финансов, инвестиций и консалтинга — Осенний сезон 2016 года

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

2,5 тыс. – это сегодняшняя цена. Уже даже в начале продаж она будет другой, и это надо учесть. Для этого в модели должен быть блок, отвечающий за прогнозную инфляцию. Он создается либо на отдельном листе в книге Excel, либо просто отдельным блоком.

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

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

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

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

Теперь этот блок должен быть задействован везде, где мы работаем с ценами.

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

В нем мы просто накапливаем инфляцию за все периоды.

И теперь мы умножаем не предыдущую, а начальную цену, на этот индекс.

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

2. Безопасность и устойчивость модели

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

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

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

Это поможет и легче ориентироваться в модели, и делать меньше ошибок.

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

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

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

Особый случай – это когда ячейка должна содержать только одно из нескольких возможных значений. Например, в ячейке B11 у нас может быть только флаг: 1 или 0. Тогда мы можем определить проверку данных по списку.

Теперь ячейка не просто редактируется. Она превратилась в выпадающее меню и вы всегда видите какие варианты здесь допустимы.

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

Сначала мы выделяем все ячейки, которые будут оставаться редактируемыми. Вызываем диалог формата ячейки. И в последней закладке отключаем флаг Заблокировать ячейку. Теперь когда весь лист будет защищен от изменений, эти ячейки останутся незащищенными. Дальше мы выбираем защиту листа. Если вы хотите, чтобы никто кроме вас не мог отключить эту защиту, то установите пароль. Часть действий с защищенным листом можно разрешить включая и выключая соответствующие флаги.

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

3. НДС в платежах

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

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

Получается итоговая сумма чистых поступлений за отчетный период. И всю эту суммы мы должны передать государству в качестве налогового платежа. Это появится у нас в кэш-фло в строке Налоги. То есть компания как бы пропускает НДС через себя и отдает излишки государству. Модель должна это отражать.

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

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

4. Учет и отображение шага планирования

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

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

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

Пусть здесь у нас будет план по кварталам.

Теперь можно определить дату начала каждого следующего периода, это мы делаем с помощью функции ДАТАМЕС(). Она вычисляет сдвиг даты на указанное число месяцев, и таким образом у нас нет проблем в связи с тем, что в месяцах разное количество дней.

На всякий случай в этом блоке лучше иметь сразу и дату окончания каждого периода. Это та же функция ДАТАМЕС(), но минус один день.

Ну и наконец название периода. Я сделаю его немного упрощенным, оно будет рассчитано только на планирование по кварталам. Чтобы извлечь из даты номер месяца, мы используем функцию МЕСЯЦ(). Номер квартала это целое от месяца минус 1, деленного на 3 плюс 1. Теперь надо добавить слово квартал и номер года. Для того, чтобы объединять несколько фрагментов текста в одну строку, в Excel используется символ амперсанд. Добавляем обозначение квартала. И номер года. Названия периодов готовы.

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

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

Можно сделать эти две строки менее бросающимися в глаза. Теперь надо зафиксировать их. Для этого ставим курсор в следующую строку и выбираем в меню Окно Закрепить области. Всё, теперь вопрос отображения периодов решен и у нас есть все данные, которые могут понадобиться в расчетах и формировании отчетов.

5. Модель на двух языках

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

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

Итак, изначально модель готовится на одном языке.

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

Теперь идея состоит в том, что все текстовые ячейки на расчетных листах будут ссылаться на первую колонку листа Язык, а в этой первой колонке всегда будет версия на текущем языке. Для этого мы делаем первую ячейку флагом языка. Ноль – базовый для нас русский язык, 1 – английский.

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

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

6. NPV, IRR – профессиональный расчет

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

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

Остается рассчитать IRR. Простой вариант – функция ВСД(), которая применяется к недисконтированному денежному потоку. Он оставляет нам кучу проблем. Что если у нас денежные потоки не по концу периода, а по началу? Что если как здесь, планирование сделано по кварталам? Что наконец если первый период не полной длины, то есть планирование например по годам, но проект начинается с апреля? Все эти проблемы оптом мы решаем, если переходим от функции ВСД() к другой, менее известной функции Excel, которая по-английски называется XIRR(), а в русском языке имеет длинное название ЧИСТВНДОХ().

Мы получили те же NPV и IRR, но наши расчеты стали прозрачнее, а модель более универсальной.

7. Подбор финансирования и циклические ссылки

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

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

Читайте также:  3 функции для определения номера квартала по дате в Excel

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

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

Теперь если например мы меняем что-то в инвестициях. Пусть нам надо еще 500 млн. График кредита меняется автоматически.

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

8. Таблицы и графики чувствительности

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

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

Первое что нам нужно это таблица в которой будут все интересующие нас значения NPV для каждого из значений параметров.

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

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

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

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

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

Последнее, что надо сказать об этом инструменте – он прекрасен и очень удобен, но надо иметь в виду, что вот например сейчас если я изменю что-то в затратах проекта, то модель пересчитается не один раз, а столько раз, сколько ячеек в таблице подстановки, то есть 7х7 = 49. С точки зрения вычислений, модель сейчас утяжелилась в 50 раз. Тут этого еще не заметно, но если исходная модель будет покрупнее или компьютер послабее, то вы обнаружите, что после каждой введенной цифры Excel подвисает на несколько секунд, и это конечно никуда не годится.

Для того, чтобы устранить эту проблему, в Excel есть специальная опция…

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

9. Сценарное планирование

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

Разработка такой модели начинается с создания обычной модели для одного сценария. А дальше мы добавляем в нее механизм управления сценариями. Для этого у нас будет отдельный лист Сценарии.

Здесь у нас будет два блока, я их себе заготовил. В одном в одном мы размещаем данные текущего сценария. Они будут отсюда переноситься в основную модель.

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

Теперь размножаем это на три сценария.

И пусть это будут сценарии под названием Рост и Кризис, и у них будут немного разные данные.

А блок Текущий сценарий заполняется у нас как выборка данных того сценария, номер которого написан сейчас вверху. Для этого мы используем функцию ВЫБОР().

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

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

10. Аккуратное отображение коэффициентов

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

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

У нас есть два варианта. Первый – мы можем воспользоваться функцией ЕСЛИОШИБКА(). Например, рентабельность продаж здесь в первые два года будет давать деление на ноль, потому что нет продаж. Добавим ЕСЛИОШИБКА(). Теперь функция подставляет аккуратный прочерк вместо сообщения об ошибке.

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

В данном случае, нам нужна функция ЕСЛИ(). Мы ставим проверку того, что денежные потоки положительные и только в этом случае рассчитываем коэффициент, а для отрицательных просто показываем прочерк. В результате модель выглядит аккуратнее и легче читается.

Структура модели

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

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

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

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

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

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

Финансовая модель инвестиционного проекта

Финансовая модель инвестиционного проекта

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

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

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

Финансовая модель инвестиционного проекта

Грамотно и правильно созданная финансовая модель помогает в решении многих задач, так как задачи предприятия носят экономический характер, то и называние получает финансово-экономическая модель (ФЭМ). Моделью предусмотрены обязательные разделы, без которых картина будет выглядеть неполноценной, и анализ будет проводиться недостоверно. Модель должна подчиняться всем законам бухгалтерии: в конечном итоге деятельности должен сводиться баланс, а также видны все результаты денежных потоков. Экономическая эффективность каждого предприятия и означает непосредственную прямую взаимозависимость между финбалансом и движением денежных средств.

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

Данные для инвестиционного финпроекта можно сгруппировать по следующим критериям:

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

Финансовая модель инвестиционного проекта

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

Финансовая модель – детализированный аналитический план по снижению рисков процесса инвестирования. Детализация и реалистичность – необходимые критерии создания качественной финмодели.

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

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

Финансовая модель инвестиционного проекта

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

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

Финансовая модель инвестиционного проекта

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

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

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

Финансовая модель инвестиционного проекта

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

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

Рассчитываем чистую прибыль

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

Амортизация. Рассчитывается из времени службы техники и показывает, как этот ресурс техники изнашивается.

В каждом бизнес своя техника — ее экономисты называют основными средствами. В нашем примере это швейные машины, оверлоки. Мы их купили за 200 000 ₽ и планируем использовать 5 лет.

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

В нашем производстве кредитов нет, но есть налог на УСН 6%.

Вот теперь можно посчитать чистую прибыль, а также рентабельность по чистой прибыли.

Рентабельность по чистой прибыли показывает сколько копеек компания заработала на 1 ₽ выручки. Она помогает понять, сколько денег собственник может вывести себе в качестве дивидендов или вложить в развитие бизнеса.

Расчет чистой прибыли в финансовой модели Чистая прибыль в финмодели

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

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

Вы можете одним кликом менять:

период планирования от 1 года до 10 лет;

дополнять продуктовую линейку;

изменять состав прямых и переменных затрат;

учесть сезонность, инфляцию, рост цен, как на сам продукт, так и на его создание;

детализировать любые затраты;

определить штатное расписание;

учесть рост персонала во времени и изменение окладов ;

и еще 74 бизнес критерия;

Финансовый план, шаблон компактный

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

Ниже приведен упрощённый образец финансового плана. Преимуществами данного шаблона являются: его простота; встроенная взаимосвязь плана продаж, плана закупок и финансового плана; а так же — автоматический расчёт чистой прибыли за год, по мере заполнения плана продаж.

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

Примечания к шаблону

  • Используется компактный формат плана, с минимумом отвлекающих деталей.
  • План продаж рассчитан на 100 номенклатурных позиций, для простоты и защиты формул от случайных изменений в остальных ячейках.
  • В плане продаж каждой номенклатурной позиции можно указать уточняющие характеристики — в таком варианте проще анализировать данные и экспортировать план продаж в автоматизированную систему, при необходимости.
  • В плане продаж есть раздел с данными о максимальном количестве поисковых запросов по номенклатуре; ориентируясь на примерную конверсию в 10% всех запросов в заказы, можно оценить достижимость планируемых продаж по позиции; нужно так же помнить, что у зрелого бизнеса большая часть продаж приходится не на новый трафик, а на лояльных клиентов — заказывающих напрямую.
  • В расчётах используются случайные коэффициенты и проценты (банковская комиссия, реклама и т.п.), подставьте свои значения, по данным учёта хозяйственной деятельности предприятия за прошедшие периоды.
  • Готовый к использованию для финансового планирования шаблон можно бесплатно скачать в Excel-формате по ссылке, приведённой на этой странице внизу.

Как пользоваться шаблоном

  • Шаблон состоит из четырёх вкладок:
    • финансовый план,
    • план продаж,
    • закупки (автоматический расчёт),
    • выручка (автоматический расчёт).

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

    Изображения, для предварительного просмотра шаблона

    Шаблон

    Excel-файл для скачивания

    Скачать файл шаблона в формате Excel-таблицы можно по этой ссылке.

    Артём В. Комлев, «Целесообразный Маркетинг», AdvisableMarketing.com, 2015г.

    Возможно, Вам будет так же интересно ознакомиться с другими тематическими материалами:

    КАК СОСТАВИТЬ ФИНАНСОВУЮ МОДЕЛЬ: ПРАКТИЧЕСКОЕ РУКОВОДСТВО

    За свою жизнь я преподавал финансовый менеджмент в Петрозаводском госуниверситете, строил систему бюджетирования и модели реорганизации на основе финансовых моделей энергокомпаний в РАО «ЕЭС», анализировал множество финансовых моделей, выносившихся на Инвестиционный комитет «РОСНАНО» и построил систему моделирования самого «РОСНАНО» на основе постоянного фин.мониторинга проинвестированных компаний, проанализировал более сотни финмоделей как «бизнес-ангел» и делал финансовые модели для десятка компаний из моего портфеля, привлекавших дополнительные инвестиции. В этой статье мне хотелось бы поделиться опытом составления финансовых моделей – в основном в инвестиционном контексте, но имеющих и вполне «операционное» применение. Надеюсь, эти рекомендации позволят сэкономить время как предпринимателям, составляющим финансовые модели не так регулярно, так и инвесторам, которые потом регулярно их рассматривают.

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

    Тем не менее, как уже отмечалось многими инвесторами, формирование финмодели имеет смысл:

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

    Вообще говоря, полная финансовая модель компании описывается тремя «подмоделями» / бюджетами: бюджетом движения денежных средств (БДДС; «кэш-флоу», от англ. cash flow), бюджетом по начислениям (прибыли и убытки, profit andloss (P&L)) и балансом активов и обязательств (balance sheet).

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

    В отличие от бюджета движения денежных средств, бюджет прибылей и убытков (еще его называют бюджетом по начислениям) формируется не на дату получения или осуществления какого-либо платежа, а на дату появления либо исчезновения обязательств – ваших или перед вами. Например, при продаже своей продукции вы можете попросить аванс, или например взять деньги за подписку на ваш сервис на год вперед. Соответственно, в БДДС у вас появится приход (денег), а вот в P&L – нет, доход (выручка) возникнет только в момент, когда вы отдадите товар покупателю (в случае подписки, как правило, вы будете признавать в качестве выручки 1/12 годового платежа ежемесячно).

    Основные расхождения между кэш-флоу и начислениями связаны как раз с разнесением потоков денежных средств и прав на них в разные периоды. Например, когда вы получаете аванс на выполнение работы или доступ к вашему сервису, или наоборот ждете 90 дней оплаты за поставленный вами товар. «Автоматически» данная ситуация возникает с зарплатой (которая начисляется, например, за январь, а выплачивается в феврале), налогами (которые рассчитываются и выплачиваются после истечения периода, за который они начисляются), амортизацией (покупка основных средств оплачивается одномоментно, а вот в расходы принимается только частично – пропорционально ожидаемому сроку использования данного актива). Таким образом, расхождение показателей кэш-флоу и начислениях, особенно у быстрорастущей фирмы, продающей SaaS по модели годовой подписки, может быть довольно существенным[1]. Задача бюджета прибылей и убытков – оценить эффективность работы фирмы, а не просто генерирование денежных средств: ведь компания может набрать авансов (продать подписок), премию продажникам обещать заплатить по итогам года, налоги тоже время платить придет только в следующем году, и может выглядеть, что у компании генерится (в моменте) хороший денежный поток, хотя по факту компания будет убыточной. Второй задачей бюджета прибылей и убытков является планирование налога на прибыль.

    Разницы между CF и P&L, а также их итоговые строки (остаток денежных средств и прибыль/убыток) «уходят» в баланс, увеличивая или снижая дебиторскую и кредиторскую задолженность, а также размер кредитов, привлекаемых для покрытия кассовых разрывов. Баланс показывает, насколько устойчива компания краткосрочном и долгосрочном периоде, насколько и как быстро она может погасить свои обязательства и что после этого останется акционерам.

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

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

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

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

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

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

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

    Модель денежного потока принято разбивать на три раздела: операционный (притоки и расходы по основной деятельности, показывает, какой свободный денежный поток генерирует основной бизнес), инвестиционный (приобретение основных средств и других бизнесов и их продажа); финансовый (привлечение капитала и заемных средств, уплата процентов и погашение займов). Сумма приходов и платежей по каждому разделу дает соответственно операционный, инвестиционный и финансовый денежный поток. Операционный поток, как правило, должен быть положительным, чтобы обеспечивать инвестиционные потребности бизнеса и погашение долгов.

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

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

    Итогом (суммой всех трех денежных потоков) является (свободный) денежный поток. Свободный денежный поток нарастающим итогом равняется остатку денежных средств на конец соответствующего периода.

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

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

    Допущения имеет смысл вынести на отдельный лист расчетного файла, чтобы было удобно менять их и анализировать последствия, для чего на этот же лист целесообразно продублировать результирующий денежный поток. С целью «читаемости» модели и снижения вероятности ошибок желательно все используемые данные явно выписывать в модели, а не «прятать» в формулах. Например, если вы хотите рассчитать количество производимых в месяц изделий, и предполагаете, что рабочий день составит 8 часов, в месяце будет 22 рабочих дня, в час один сборщик изготавливает 6 изделий, а сборщиков у вас трое, то лучше не вписывать числа в одну ячейку (=8*22*6*3), а выписать каждый показатель в отдельную строку и сделать формулу путем перемножения соответствующих ячеек. Кроме большей понятности и наглядности, это позволит гораздо легче менять модель в случае, если вы наймете четвертого сборщика или через год повысите производительность сборки до 8 изделий в час за счет эффекта обучения.

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

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

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

    Разница между выручкой и переменными затратами (ценой и переменными затратами на единицу продукции – то, что сейчас принято называть «юнит-экономикой») образуют операционную маржу. Если она отрицательна, у бизнеса нет перспектив, если положительна – то при определенном объеме продаж она перекроет постоянные расходы и фирма выйдет в прибыль.

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

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

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

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

    По данной ссылке можно скачать образец реальной финансовой модели, которую мы делали пару лет назад с одной проинвестированной мною ИТ-компанией. Компания в основном работает в В2В, поэтому маркетинговые расходы в основном приходятся на зарплату, только один из сегментов — розничный, по которому есть переменные издержки. Обратите внимание, что в первом сегменте компания планирует достичь почти 80% рынка — как правило, это не реалистично, но в данном случае это довольно узкий сегмент, в котором «победитель получает все». Жизнь показала, что прогноз был вполне реалистичен — в настоящее время компания полностью контролирует рынок Москвы, на 80% — Санкт-Петербурга и скоро запустится в остальных миллионниках.

    Итак, модель собрана. Теперь начинается самое интересное – «игра» с различными допущениями, чтобы определить различные варианты действий и возможные риски.

    Одним из самых распространенных является анализ чувствительности – когда каждое допущение изменяется на 1 или 10% и определяется результирующее изменение денежного потока / IRR / NPV. Чем больше изменение результирующего показателя, тем чувствительнее финансовая модель к данному фактору. Например, если при увеличении цены на 10% итоговый денежный поток вырос на 20%, а при снижении переменных издержек на 10% денежный поток вырос на 5%, то чувствительности модели к цене составляет 2, а к переменным издержкам – 0,5. Соответственно факторам, к которым наиболее чувствительна модель, надо уделять максимальное внимание при проработке бизнес-плана и последующем ведении бизнеса.

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

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

    16 таблиц для финансового учета: шаблоны с видеоинструкциями

    Привет! Мы «Нескучные финансы», помогаем бизнесу навести порядок в учете и управлять бизнесом на основе цифр. Для этого мы или берем финучет на аутсорс, или предоставляем удобные бесплатные шаблоны. В этой статье как раз второе.

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

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

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

    Можете просто перейти по ссылке и пользоваться таблицами на свое здоровье. А можете читать дальше: мы вкратце расскажем, зачем нужна каждая таблица из барсетки.

    Это документ, с которого начинается внедрение финучета в компании. Из этого отчета видно, откуда пришли деньги и куда ушли. Так или иначе его ведут многие собственники ― в блокноте, Эксельке или голове, а вот анализируют не все. Однако нужно сравнивать суммы за разные периоды и по одинаковым статьям. Например, в этом месяце мы тратим на содержание офиса больше, чем в том.

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

    Баланс состоит из активов и пассивов. В активах показывается, что есть у компании, а в пассивах ― кому это принадлежит.

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

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

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

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

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

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

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

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

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

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

    Сводный документ, в котором учитываются рекламные активности на разных онлайн площадках и привлеченный с них трафик. С помощью такого инструмента можно собрать данные для анализа эффективности рекламы из разных источников: в отчете наглядно видно, что эффективней для продвижения вашего продукта ― контекстная реклама в Яндекс.Директе или таркетинговая во Вконтакте. Также здесь можно вести учет рекламных расходов и выручки, которая получена от клиентов из онлайна.

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

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

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

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

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

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

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

    Если вы планируете модернизировать, дорабатывать самостоятельно шаблон финансового моделирования, добавлять строки, создавать свои расчёты, используйте профессиональный продукт финансового моделирования Шаблон ФМ-ПРО.

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

    Меня зовут Данила Тривайло, и сегодня я хотел поделиться с вами шаблоном для расчета финансовой модели вашего бизнеса.

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

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

    В тот день Женя решил купить себе муляж крысы. И надо сказать, это был один из самых веселых дней за тот год. Как же орали девчонки! Веселились мы славно и после того, как нас отчитал завуч, мы сидели с ним и обсуждали, что можно тут придумать.

    — Слушай, Жека, а давай будем продавать их?

    — Думаешь, они кому-то нужны?

    — Ну давай попробуем!

    Так и решили. Свободных денег у меня на тот момент не было, а у Женька были накопления около 3000 рублей. Игрушки стоили в среднем рублей 250. Решили просто умножать на два и продавать за 500. Деньги договорились делить пополам.

    В первый закуп удалось купить десять игрушек — такой мини-зоопарк. И каково было наше удивление, когда на следующий день до обеда все игрушки были проданы! Ажиотаж был нереальный — по всей школе на переменах раздавали дикие крики.

    Мы естественно повторили закупку — результат был аналогичный, все разобрали молниеносно. Третья закупка — и опять все разлетелось на «ура». Всем очень нравился наш товар.

    А как я был рад! По итогам недели мы наторговали на 18 тысяч рублей. Как и договаривались, половина была моя. Я забрал 9000 и ушел на выходные довольный, как слон. Каким же богачом я ощущал себя тогда! Зашел в продуктовый магазин и понял, что я вообще могу купить себе все что угодно — любые чипсы, мороженое, могу даже три штуки взять 🙂

    На следующей неделе мы встретились с Жекой перед уроками. Но он, надо сказать, как-то не был так весел, как я. Говорит, что не очень понимает — вроде столько продали всего, а у него денег не прибавляется… Мы начали мозговать, в чем может быть проблема.

    В итоге все встало на свои места. Женя дал оборотные 3000 рублей, мы продали первую партию и заработали 6000 рублей. Половину, то есть всю нашу прибыль, забрал я. А у него на руках итого осталось столько, сколько и было в начале:) И так три раза. Втянул его, ни копейки не вложил, никаких рисков не нес, всю прибыль забрал. Красавец:))

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

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

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

    Считаю, что мой файл будет полезен тем, кто:

    • Готовится запускать какой-то бизнес или проект.
    • Хочет создать новое направление внутри текущего бизнеса.
    • Уже запустил работающий бизнес, но по какой-то причине, не считал экономику проекта.
    • Не планирует запускать бизнес, но думает в этом направлении.

    Задача этого документа — получить ответ, сходится ли экономика Вашего проекта. Если сходится, то какие целевые значения нужно достичь.

    Если модель не сходится даже на бумаге — стоит задуматься над этим уже сейчас. Случаев, когда в расчетах все плохо, а в жизни все супер, я практически не встречал. Наоборот — постоянно:)

    Не стоит воспринимать этот документ как фундаментальный финансовый анализ. Это упрощенный вариант аналитики, который позволит получить ответы на базовые вопросы.

    • Файл залит на «Google диск». Мою версию нельзя редактировать — поэтому скачивайте ее себе на диск и там уже делайте, что хотите, под свои цели.
    • В файле четыре вкладки. Вкладка № 1 — это финансовая модель. По сути это фотоснимок вашего бизнеса в статике. Задача этой вкладки — понять, как должен выглядеть ваш проект в итоге с точки зрения финансов. Для удобства я завел туда реальные показатели одного из своих недавних проектов как пример.
    • Вкладка № 2 — это финансовый календарь. После того, как вы поймете ключевые показатели, считая финансовую модель (вкладка 1), вы можете посмотреть, что будет происходить с вашим проектом не в статике, а в динамике.
    • Вкладки № 3 и № 4 дублируют вкладку № 1 и № 2. Это рыба — пустые шаблоны. То есть вкладки №1–2 нужны для понимания, как заполнять документ, №3–4 подходят для просчета ваших проектов.
    • Файл рассчитан на анализ большинства бизнес-моделей. В ряде случаев придется немного поправить документ, но для подавляющего большинства бизнес-моделей его будет достаточно.
    • Файл содержит два сценария для расчета двух самых популярных форм бизнеса: штабной и сетевой формы. Если у вас штабной бизнес, когда есть некое ядро (интернет-магазин, производство) — можете сразу использовать документ, ничего дополнительного делать не нужно. Если у Вас сетевой бизнес, когда система строится на бизнес-единицах (юниты), а все юниты объединяет некий управляющий центр, нужно в Фильтре в ячейке «Юнит модель» открыть поля с пометкой «U». Тогда у Вас откроются дополнительные строки с параметрами, которые нужны для сетевых форм бизнеса.

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

    1. Начните с того, что определите, какая форму бизнеса у вас планируется. Если, например, вы хотите открывать салоны красоты — это сетевой бизнес. Если в планах делать только один салон — можно считать, что это штабная форма.
    2. Я бы советовал просто идти по порядку и заполнять пустые ячейки. Заполнять все ячейки не обязательно.
    3. Сначала пройдите вкладку «Финансовая модель». Ваша цель — понять, при каких показателях экономика бизнеса будет сходится.
    4. Наверно, первая задача первой вкладки — понять, при каких показателях вы достигнете точки безубыточности. Как правило, все крутится вокруг следующих параметров: количество продаж, средний чек, средняя наценка. Второй вопрос — насколько реально будет достичь полученных показателей в жизни.
    5. В разделе «Оценка рынка» я заполняю показатели в количестве человек. С точки зрения корректности показателей, TAM, SAM и SOM считается в деньгах, то есть количество пользователей умножается на средний чек. В данной таблице предлагаю считать емкость рынка в количестве пользователей или клиентов, а средний чек учитывать отдельно.
    6. Если вам не хватает строк в таблице, лучше сгруппировать параметры. Особенно это касается расходной части. Допустим, вы делаете просчет ресторана. Вы можете на отдельной вкладке выписать все компоненты кухни и их стоимость, а в финмодель в разделе расходов объединить все компоненты под пунктом «Оборудование для кухни».
    7. Чтобы получить максимально достоверные цифры — лучше всего обратиться к практикам. Найдите тех, у кого есть опыт в аналогичном бизнесе в схожей нише и покажите им вашу модель. Такие люди смогут быстро стряхнуть с нее налет неадекватности.
    8. Самый точный способ получить достоверную информацию — это практика и создание MVP. К примеру, очень сложный вопрос на старте — понять, каким будет спрос. Часто достаточно сделать лендинг с продуктом, как будто он у вас есть, запустить рекламу и увидеть, насколько ваш продукт интересен людям. По моей практике, на этом этапе отсеивается 90% идей.
    9. Разделяйте понятия дивидендов и зарплаты для себя. Если вы участвуете в запуске бизнеса и выполняете какие-то функции, у вас должна быть зарплата и ее нужно закладывать в финансовую модель. Такой подход подразумевает, что в любой момент вы можете выйти из операционной деятельности и вашу позицию займет другой человек.
    10. Один из ключевых показателей при запуске проекта — стоимость привлечения клиента. Проще говоря, это ответ на вопрос, сколько денег нужно потратить, чтобы один человек в результате совершил покупку. В угоду универсальности в этом шаблоне нет данного параметра. Но я очень советую посчитать этот показатель отдельно. Думаю, это топ-5 причин, почему у проектов не сходится экономика.
    11. Расходную часть почти любого проекта можно найти в интернете. Сейчас есть очень много детальных разборов типовых бизнесов. Еще полезный инструмент: запросить презентацию франшизы. Так, если вы хотите открыть барбершоп, запросите три-четыре файла от разных франшиз и вы получите понимание, сколько будет стоить открытие и каких расходов ожидать.
    12. Часто в финансовых планах возлагают большие надежды на показатель retention — повторные продажи. Я бы минимально рассчитывал на retention при построении финансовой модели. Пусть это будет вашим бонусом и лучше постарайтесь свести экономику без расчета на повторные продажи.

    Сам документ можно скачать по этой ссылке.

    Буду очень рад, если в итоге, этот документ поможет кому-то запустить проект, избежать ошибок или получить понимание, что происходит с их бизнесом в данный момент: ) Особенно буду рад, если файл поможет тем, кто вынужден начинать заново после пандемии. Не расстраивайтесь и не вешайте нос — у вас есть шанс сделать все еще лучше 🙂

    Для удобства на первой и второй вкладке с примером расчетов я добавил комментарии в некоторые ячейки с названием параметров (у таких ячеек черный уголок) — комментарий появляется при наведении на название параметра.

    Финансовая модель предприятия в Excel: продажи, цены, расчеты, БДР и БДДС

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

    Учет продаж и закупок можно вести, например, в таком реестре

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

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

    • Более подробно о том, как вести учет и бюджеты предприятия в Excel читайте в моей книге«Финансовая модель предприятия в Excel » на ЛитРес
    • Скачать пример финмодели в Excel можно здесь

    На основании бюджета продаж и закупок можно оперативно формировать такие отчеты

    Кроме продаж, в файле можно рассчитывать себестоимость и контролировать цены продаж

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

    Как лучше всего оценивать выполнение поставленных задач? Конечно в числовом выражении. У каждой организации, независимо от вида деятельности, может быть тысячи, а то и более показателей работы. Можно потратить на их определение большое количество времени и установить значения. Но как мы уже сказали выше, в ходе работы будут постоянные корректировки.

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

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

Ссылка на основную публикацию