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

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

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

Эксперт статьи

Тимур Амерханов

Ведущий эксперт по финансовому учету в сервисе МойСклад

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

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

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

Базовая финансовая модель включает данные по:

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

Пример составления финансовой модели в таблице Excel: пошаговая инструкция

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

Шаг 1: Сделайте копию документа

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

Шаблон включает следующие вкладки:

1. Титульный лист.

2. Интернет-магазин.

Таблица с финансовой моделью для e-commerce.

3. Офлайн-магазин

Таблица с финансовой моделью для розничного магазина «живых» продаж.

В зависимости от вида бизнеса выберите нужную вкладку — интернет-магазин или офлайн-магазин.

Шаг 2: Приступите к заполнению таблицы

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

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

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

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

Шаг 3: Пропишите план продаж и значения воронки

Начинаем с доходов — в таблице они выделены для наглядности зеленым цветом.

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

Визиты на сайт

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

Количество посещений рассчитывается по формуле: (Посещения в феврале) = Посещения в январе + (Посещения в январе) x 5%. По такой же аналогии применяем формулу для следующих месяцев.

Количество заказов

Для расчета важно знать, какой процент посещений завершается оформлением заказа или добавлением товара в корзину, например. Это конверсия из визита в заказ. Количество заказов будет равно: (Количество посещений) x (Конверсия в заказ).

Количество оплаченных заказов

Не все заказы завершаются оплатой. Иногда люди забывают про корзины надолго или передумывают покупать товар. Нам важно знать количество оплаченных заказов: (Количество заказов) x (Конверсия в оплату).

Выручка

Она рассчитывается как произведение среднего чека и количества оплаченных заказов.

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

Вы можете визуализировать воронку продаж, как это сделать — смотрите в нашей инструкции.

Шаг 4: Рассчитайте переменные затраты

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

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

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

Себестоимость = − Выручка x (1 − Маржинальность)

Шаг 5: Оцените маржинальный доход и рентабельность

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

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

Шаг 6: Рассчитайте постоянные расходы

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

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

Шаг 7: Оцените валовую прибыль и рентабельность

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

Рентабельность по валовой прибыли помогает отслеживать эффективность работы компании в целом.

Шаг 8: Рассчитайте косвенные расходы

Закладываем расходы на упаковку, условно они одинаковые каждый месяц.

При онлайн-продажах нужно предусмотреть также расходы на привлечение трафика. Чуть выше вы уже задали стоимость клика — по ней калькулятор рассчитает, сколько вы платите за все посещения сайта.

Расходы на трафик = − Визиты на сайт x Стоимость клика

Шаг 9: Оцените операционную прибыль и рентабельность

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

Шаг 10: Учтите налоги, кредиты и амортизацию

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

Шаг 11: Оцените финансовый результат

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

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

Шаг 12: Запланируйте операционный оборотный капитал

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

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

По тому же принципу планируем задолженность. Дебиторская здесь не нужна, так как клиент сразу оплачивает заказ. Кредиторская нужна в том случае, если вы не платите поставщикам 100% предоплаты.

Шаг 13: Запланируйте движение денежных средств

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

Расходы по инвестиционной деятельности — это затраты в нулевом периоде на оборудование и ремонт помещения.

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

Чем финансовое моделирование помогает бизнесу

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

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

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

Виды финансовых моделей

Начнем с базовых.

Модель на базе трех отчетов

Финансовая модель трех отчетов — это таблица, основанная на трех отчетах:

• ОпиУ — отчет о прибыли и убытках, который показывает финансовый результат бизнеса и рентабельность;
• Баланс — информация об активах, пассивах, обязательствах и собственном капитале;
• ОДДС — отчет о движении денежных средств.

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

Наш шаблон построен на основе этой модели.

Инвестиционная модель

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

Модель включает данные:

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

Предпринимателю модель инвестиций помогает:

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

Инвестор посредством данной модели сможет:

• оценить риски вложений;
• оценить целесообразность вложений в конкретный бизнес;
• выбрать самый выгодный вариант для инвестирования.

Модель для масштабирования

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

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

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

Их очень много, среди популярных — следующие:

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

Рекомендации по построению финмодели

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

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

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

Что еще важно знать про финансовые модели

Отвечаем на вопросы.

Автоматизация управленческого учета в МоемСкладе

Автоматизация экономит время: ни руководителю, ни его помощникам не нужно самостоятельно вносить и сверять данные.

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

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

У МоегоСклада есть готовое решение для внедрения и автоматизации управленческого учета. Программа объединяет разные задачи, чтобы вы могли одновременно:

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

Сервис адаптирован сразу для трех видов учета:

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

Программа подходит для того, чтобы анализировать данные по счетам: следить за остатками и оборотами. Можно подключить терминал сбора данных и выгружать сведения в 1С: Бухгалтерию для подготовки отчетности, работать с ЭДО и использовать интеграции с банками, чтобы загружать выписки и отправлять платежные поручения.

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

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

Разберемся, как устроены основные отчеты.

Движение денежных средств. Показывает все поступления и выплаты в пределах заданного периода.

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

Баланс на начало и конец заданного временного промежутка можно посмотреть в строках с начальным и конечным остатками.

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

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

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

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

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

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

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