
Как сделать финансовую модель в Excel самостоятельно
Содержание:
Финансовая модель в формате xlsx
Шаблон бизнес-плана в Excel это полноценная финансовая модель, включающая в себя все необходимые разделы с удобными формами и автоматическим составлением денежных потоков проекта с интерактивным представлением
Алгоритм построения финансовой модели в Excel
Шаг 1 – Составляем бюджет стартовых вложений в проект
Справа пример таблицы как можно оформить стартовые вложения. Все данные лучше заносить в Excel, так как в нем имеется большой функционал для финансового моделирования и этого вполне хватит на начальном этапе.
В первом столбце таблицы порядковый номер (для удобства), далее наименования позиций, их количество, цена и итоговая стоимость. Внизу рассчитано общее значение инвестиций для начала деятельности.
Формулы следующие: в ячейку F5 мы пишем =E5*D5 и протягиваем ее вниз до последней строки. Далее в ячейку F15 пишем формулу =СУММ(F5:F14).
Шаг 2 – Прогнозируем продажи
- можно также ориентироваться на цены конкурентов – проанализировать по какому прайсу они продают и сделать похожий, однако здесь есть нюанс, что мы не знаем точно, какие затраты они несут, чтобы продавать по таким ценам и может быть такое, что ваш проект с ориентирами на них будет убыточным;
- пойти через затратный метод, то есть посчитать все операционные расходы проекта и сделать желаемую наценку (в таком случае средний чек пока что можно поставить условным, а после расчета всех затрат, сделать его с наценкой к себестоимости)
В ячейку J16 пишем =J4*J9 и протягиваем до декабря вправо, а после все вниз до последнего ТРУ. Внизу под каждым месяцем делаем сумму по всем ТРУ с помощью формулы =СУММ(J16:J18) также протянув ее вправо.
Шаг 3 – Определяем переменные затраты
В ячейку J30 пишем =J24*J4 (тем самым связываем объем продаж и переменные затраты) и также как в прошлый раз протягиваем вправо и вниз. Далее под каждым месяцем суммируем значения через формулу =СУММ(J30:J32) также протянув ее вправо.
Шаг 4 – Формируем постоянные расходы и ФОТ
Здесь необходимо составить план по постоянным расходам, которые возникнут во время проекта. Это расходы, которые никак не связаны с объемом продаж. Обычно под ними понимают аренду помещения, затраты на рекламу, фонд оплаты труда. В рамках примера их можно представить в виде следующей таблицы.
В ячейку J40 записываем формулу =СУММ(J36:J39), тем самым находим сумму постоянных расходов по месяцам. Все остальные ячейки заполняем как значения на основе ваших данных.
Шаг 5: Рассчитываем налоги и формируем финансовый результат
- если ваш режим налогообложения – УСН, где объектом являются доходы, облагаемые ставкой 6%, то формула будет простой: берем всех доходы и умножаем их на ставку налога (обычно она 6%, но в некоторых регионах может быть меньше);
- если вы на УСН, где объектом являются доходы, уменьшенные на величину расходов, то уже в таком сценарии формула будет представлена как разница между выручкой и расходами, умноженная на 15% (опять же в некоторых регионах ставка может быть другой)
- если ваш вид деятельности подходит под патент (патентная система налогообложения или сокращенно ПСН), то здесь уже можно воспользоваться налоговым калькулятором от ФСН и рассчитать стоимость патента отдельно на определенный период и указав это в модели.
В рамках примера для простоты расчета налоговые отчисления ведутся ежемесячно (правильно это делать ежеквартально после окончания отчетных периодов). После расчета налогов можно приступить к определению финансового результата. Для этого в отдельной строке сделаем формулу, в которой от всей выручки отнимем расходы и налоги. Представим это все в подобной таблице.
Шаг 6 – Инвестиционные показатели
- чистая приведенная стоимость (NPV);
- внутренняя норма доходности (IRR);
- срок окупаемости (PP);
- индекс доходности (PI).
NPV рассчитываем через формулу ЧПС (в русском Эксель), в которой первым аргументом будет ставка дисконтирования (так как у нас разбивка по месяцам, то она должна быть поделена на 12, что и было сделано в ячейке J47), а вторым массив из денежных потоков (J44:U44) и от полученного значения отнимаем стартовые вложения.
IRR считаем с помощью формулы ВСД – здесь тоже все просто, для начала, скопируйте ячейки с финансовым результатом куда-нибудь ниже, а затем перед самой первой ячейкой вставьте отрицательное значение стартовых вложений, чтобы у вас все получилось в одну строку. Полученный массив и будет являться аргументом функции и выведет внутреннюю норму доходности.
PP находим с помощью накопленного итога. В первом периоде формула будет такой: берем стартовые вложения с отрицательным знаком и прибавляем полученное значение чистого денежного потока в первый период. В следующей ячейке берем получившееся значение из предыдущей и прибавляем чистую прибыль. Делаем так да тех пор, пока накопленный итог не станет положительным – этот период и будет являться сроком окупаемости.
PI – берем NPV, прибавляем к нему стартовые вложения и полученный результат делим ни них же.
Шаг 7 – Делаем выводы
Полезные советы по построению финансовой модели
- Соблюдайте точность при вводе исходной информации. Ошибки и недочеты могут привести к погрешностям при выявлении взаимосвязей и использовании формул, что отрицательно отразиться на итоговых результатах.
- Для повышения наглядности и достоверности можно использовать онлайн-шаблоны. Такие инструменты могут пригодиться как опытным специалистам, так и пользователям с поверхностными знаниями в части работы с Excel. Их можно приобрести на платной основе или скачать бесплатные аналоги через Интернет.
- Применяйте различные функции из программы для анализа полученной модели. Не стоит ограничиваться классическим набором, типа: SUM, AVERAGE, MIN и MAX.
- Не игнорируйте окончательную проверку модели после построения. Внимательно сверьте исходные данные и формулы для расчета переменных показателей. Также рекомендуем ввести экстремальные значения, чтобы оценить степень реагирования модели. Для аудита формул предусмотрен ряд настроек, которые проверяют их на корректность и согласованность.
- При построении уделите внимание структуре модели. Все рабочие листы должны быть информативными и расположенными в логическом порядке. Области предположений на страницах необходимо защитить от редактирования, а графы для ввода, наоборот, следует оставить незащищенными.
- Согласуйте внешний вид отчетов по всей модели. Желательно использовать одинаковую цветовую гамму.
Преимущества нашей финансовой модели
Наша финансовая модель в Excel:
-
-
- является универсальным инструментом для различных направлений бизнеса;
- оснащена встроенными формулами (возможность их редактирования отсутствует, что обусловлено структурой файла);
- не нуждается в специальном программном обеспечении, скриптах и макросах;
- обеспечивает планирование на любой выбранный период;
- не требует специальных экономических знаний (инструмент рассчитан на людей, не имеющих базового финансового образования);
- наделена интуитивно понятным интерфейсом и подсказками.
-
Наш шаблон в Эксель представляет собой законченный инструмент для финансового планирования и моделирования. Предоставляем бесплатное сопровождение на каждом этапе его использования.
Для работы потребуется только компьютер или ноутбук со встроенным редактором MS Excel 2010-2019.
Наша финансовая модель поможет вам чувствовать себя уверенно на этапе моделирования и расчета основных показателей, что позволит принять правильное управленческое решение. Для приобретения универсального Excel-файла или получения дополнительной консультации достаточно позвонить нам.