kak-sdelat-finansovuyu-model-v-excel

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

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

Содержание:

Шаблон бизнес-плана в Excel – готовые таблицы

Финансовая модель в формате xlsx

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

Алгоритм построения финансовой модели в Эксель

Шаг 1 – Составляем бюджет вложений

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

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

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

kak-sdelat-finansovuyu-model-v-excel

Формулы следующие: в ячейку F5 мы пишем =E5*D5 и протягиваем ее вниз до последней строки. Далее в ячейку F15 пишем формулу =СУММ(F5:F14).

Шаг 2 – Прогнозируем продажи

Далее начинаем заполнять доходную часть (мы это делаем правее от таблицы стартовых вложений). В первую очередь здесь необходимо определить какие товары/работы/услуги (сокращенно ТРУ) вы будете продавать. Если уже есть понимание что это будет, то хорошо, если нет, то можно посмотреть, что продают конкуренты, какой у них ассортимент или просто найти нужную информацию в интернете.
После этого следует определиться с ценами продаж (составить средний чек на товар/категорию товаров/услугу). Здесь уже есть несколько подходов:
    • можно также ориентироваться на цены конкурентов – проанализировать по какому прайсу они продают и сделать похожий, однако здесь есть нюанс, что мы не знаем точно, какие затраты они несут, чтобы продавать по таким ценам и может быть такое, что ваш проект с ориентирами на них будет убыточным;
    • пойти через затратный метод, то есть посчитать все операционные расходы проекта и сделать желаемую наценку (в таком случае средний чек пока что можно поставить условным, а после расчета всех затрат, сделать его с наценкой к себестоимости)
Затем делаем расчет будущей выручки – то есть формируем положительные денежные потоки. Все результаты можно оформить в виде таблиц, представленных ниже.
algoritm-postroeniya-finansovoj-modeli
В первой таблице для каждого ТРУ в разрезе месяцев формируем план продаж. Его можно сделать на основе статистики, ретро данных или исследованиях, опубликованных в интернете. Либо воспользоваться более реальным подходом – оценить загрузку бизнеса (бывали случаи, когда начинающие предприниматели следили за тем, сколько людей в среднем приходят в кафе конкурентов, чтобы понять какие значения брать в расчет финансовой модели). Декомпозиция по месяцам поможет учесть сезонность, если она есть.
Во второй таблице прописываем цены реализаций за 1 единицу (средний чек). Данные по каждому месяцу могут быть одинаковыми, однако если вы планируете увеличение цен, например, во втором полугодии, то это можно учесть.
Третья таблица – расчетная, в которой мы увидим выручку по каждому ТРУ и месяцу. Чтобы это сделать, необходимо прописать следующие формулы:

В ячейку J16 пишем =J4*J9 и протягиваем до декабря вправо, а после все вниз до последнего ТРУ. Внизу под каждым месяцем делаем сумму по всем ТРУ с помощью формулы  =СУММ(J16:J18) также протянув ее вправо.

Шаг 3 – Определяем переменные затраты

Начинаем работать с расходной частью, формируем переменные затраты – то есть те, которые напрямую будут связаны с объемом производства или как в нашем случае с планом продаж, определенным в предыдущем шаге. Их можно выразить через отдельные таблицы:
finansovaya-model-v-excel-skachat
Для каждого товара формируем затраты на 1 единицу. В них могут войти сырье, материалы, электроэнергия и так далее. Например, если у вас кофейня, то на 1 стакан кофе будет уходить немного зерен в эквивалентном денежном выражении, одна крышка, молоко, вода, сироп, итого сформируется стоимость за 1 шт. Так как есть детализация по месяцам, то можно заложить дальнейшее увеличение цен.
После того, как сформировали затраты на 1 ед, начинаем рисовать таблицу с абсолютными значениями, для этого делаем зависимость переменных затрат и плана продаж:

В ячейку J30 пишем =J24*J4 (тем самым связываем объем продаж и переменные затраты) и также как в прошлый раз протягиваем вправо и вниз. Далее под каждым месяцем суммируем значения через формулу  =СУММ(J30:J32) также протянув ее вправо.

Шаг 4 – Формируем постоянные расходы и ФОТ

Здесь необходимо составить план по постоянным расходам, которые возникнут во время проекта. Это расходы, которые никак не связаны с объемом продаж. Обычно под ними понимают аренду помещения, затраты на рекламу, фонд оплаты труда. В рамках примера их можно представить в виде следующей таблицы.
prostaya-finansovaya-model
Отдельное внимание стоит уделить расходам на оплату труда. Если вы будете устраивать в штат сотрудников, то следует учесть еще НДФЛ (13%) и страховые взносы (30% от суммы оклада на руки + НДФЛ). Также, если вы выберете организационно-правовую форму в виде ИП, то не забывайте о том, что индивидуальный предприниматель должен платить страховые взносы порядка 40 тыс рублей в год (можно добавить их пропорционально каждому кварталу).

В ячейку J40 записываем формулу  =СУММ(J36:J39), тем самым находим сумму постоянных расходов по месяцам. Все остальные ячейки заполняем как значения на основе ваших данных.

Шаг 5: Рассчитываем налоги и формируем финансовый результат

Для небольших проектов чаще всего используются специальные налоговые режимы, это упрощенная система налогообложения (УСН) доходы или доходы минус расходы, либо патентная система налогообложения. Отдельной строкой в финансовой модели можно вывести расчет налога, при этом:
    • если ваш режим налогообложения – УСН, где объектом являются доходы, облагаемые ставкой 6%, то формула будет простой: берем всех доходы и умножаем их на ставку налога (обычно она 6%, но в некоторых регионах может быть меньше);
    • если вы на УСН, где объектом являются доходы, уменьшенные на величину расходов, то уже в таком сценарии формула будет представлена как разница между выручкой и расходами, умноженная на 15% (опять же в некоторых регионах ставка может быть другой)
    • если ваш вид деятельности подходит под патент (патентная система налогообложения или сокращенно ПСН), то здесь уже можно воспользоваться налоговым калькулятором от ФСН и рассчитать стоимость патента отдельно на определенный период и указав это в модели.
В рамках примера для простоты расчета налоговые отчисления ведутся ежемесячно (правильно это делать ежеквартально после окончания отчетных периодов). После расчета налогов можно приступить к определению финансового результата. Для этого в отдельной строке сделаем формулу, в которой от всей выручки отнимем расходы и налоги. Представим это все в подобной таблице.
investicionnaya-model-v-excel
На выходе получаем чистую прибыль от реализации нашего проекта по месяцам, также можно вывести итоговое значение, просуммировав каждый период.

Шаг 6 – Инвестиционные показатели

Один из самых интересных шагов — это расчет инвестиционных показателей финансовой модели. Первым делом необходимо определить ставку дисконтирования. Простыми словами: деньги во времени дешевеют и для того, чтобы это учесть в финансовой модели, используют некий процент, приводящий денежные потоки к текущему моменту (по-другому это называется «Дисконтирование»).
Существует много подходов к расчету ставки дисконтирования, однако для небольшого стартапа достаточно просто понять, куда вы можете вложить денежные средства еще, альтернативно проекту, и под какой процент годовых. Например, сейчас можно сделать вклад в банке под 7% годовых на 1 год. Этот процент и будем брать за ставку дисконтирования.
После определения ставки, начинается инвестиционная оценка проекта. Обычно в расчете участвуют следующие показатели экономической эффективности:
    1. чистая приведенная стоимость (NPV);
    2. внутренняя норма доходности (IRR);
    3. срок окупаемости (PP);
    4. индекс доходности (PI).
Более подробно расчет этих показателей расписан здесь. В таблице ниже отразим полученные результаты.
poschitat-finansovuyu-model

NPV рассчитываем через формулу ЧПС (в русском Эксель), в которой первым аргументом будет ставка дисконтирования (так как у нас разбивка по месяцам, то она должна быть поделена на 12, что и было сделано в ячейке J47), а вторым массив из денежных потоков (J44:U44) и от полученного значения отнимаем стартовые вложения.

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

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

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

Шаг 7 – Делаем выводы

После того, как мы сделали все расчеты, необходимо их проанализировать. Если мы видим, что чистая приведенная стоимость проекта (NPV) отрицательна, то мы говорим о том, что проект за рассматриваемый период времени убыточен – в таком случае необходимо его отклонить, либо продлить период расчетов. Что касается IRR, то здесь следует обратить внимание на то, выше ли этот показатель, чем ставка дисконтирования – если да, то проект может считаться экономически целесообразным. Показатель периода окупаемости рассматриваем как есть, то есть если проект окупится за выбранный период, то это хорошо. Итоговый показатель – индекс доходности PI, он должен быть больше 1 – только в этом случае можно сказать, что проект экономически целесообразен.

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

  1. Соблюдайте точность при вводе исходной информации. Ошибки и недочеты могут привести к погрешностям при выявлении взаимосвязей и использовании формул, что отрицательно отразиться на итоговых результатах.
  2. Для повышения наглядности и достоверности можно использовать онлайн-шаблоны. Такие инструменты могут пригодиться как опытным специалистам, так и пользователям с поверхностными знаниями в части работы с Excel. Их можно приобрести на платной основе или скачать бесплатные аналоги через Интернет.
  3. Применяйте различные функции из программы для анализа полученной модели. Не стоит ограничиваться классическим набором, типа: SUM, AVERAGE, MIN и MAX.
  4. Не игнорируйте окончательную проверку модели после построения. Внимательно сверьте исходные данные и формулы для расчета переменных показателей. Также рекомендуем ввести экстремальные значения, чтобы оценить степень реагирования модели. Для аудита формул предусмотрен ряд настроек, которые проверяют их на корректность и согласованность.
  5. При построении уделите внимание структуре модели. Все рабочие листы должны быть информативными и расположенными в логическом порядке. Области предположений на страницах необходимо защитить от редактирования, а графы для ввода, наоборот, следует оставить незащищенными.
  6. Согласуйте внешний вид отчетов по всей модели. Желательно использовать одинаковую цветовую гамму.

Преимущества нашей финансовой модели

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

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

Для работы потребуется только компьютер или ноутбук со встроенным редактором MS Excel 2010-2019. 

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

Метки:
,
Следующая Как найти поставщиков для бизнеса
Назад к списку
Предыдущая Базы данных компаний 2023: цены, категории, виды

Похожие записи

Добавить комментарий