Финансовое моделирование в Excel за 13 шагов, со скринами и пояснениями эксперта

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

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

Содержание

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

Ключевые показатели и структура финансовой модели — что важно и почему

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

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

Финансовое моделирование в Excel: частые вопросы собственников

Финансовое моделирование в Excel: в двух словах

Поможем построить финмодель, чтобы увидеть прибыль и начать получать дивиденды

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

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

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

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

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

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

Ключевые показатели и структура финансовой модели — что важно и почему

Можно выделить универсальную структуру финансовой модели, применимую к любому бизнесу. Исходные данные ↓

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

2. Переменные расходы: зависят от объёма выручки — если выручка растёт, растут и эти расходы; если заказов нет, переменных расходов тоже нет.

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

1. Постоянные расходы: не зависят от объёма продаж — аренда, постоянные оклады, обучение, канцелярия.

2. Налоги, кредиты, амортизация — важные данные для качественной модели.

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

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

В модели блок оборотного капитала важен для прогноза денежного потока и предотвращения кассового разрыва.

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

Когда исходные данные собраны, переходим к расчетам:

а) определяем эффективность по уровням прибыли — маржинальной, валовой, чистой;

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

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

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

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

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

Шаг 2. Начинаем с воронки продаж. Она показывает, как клиент проходит путь от первого знакомства до оплаты. Это нужно, чтобы спрогнозировать поток клиентов и будущую выручку. Воронок может быть несколько. 

Заполнять финансовую модель будем на примере стоматологической клиники.

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

Шаг 3. Далее заполняем данные по блокам расходов. Начнем с переменных. 

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

↑ Для стоматологии пример переменных расходов — оплата труда врачей, себестоимость материалов, эквайринг

Шаг 4. Когда собрали переменные расходы в таблицу, адаптировали под бизнес, можем рассчитать маржинальный доход и рентабельность (все формулы прошиты в таблице). 

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

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

Шаг 5. Идём к постоянным расходам, связанным с поддержанием работы компании. Собираем всё, что есть: аренду, оклады персонала, коммуналку и прочие постоянные расходы. 

Напоминание: эти статьи не зависят от выручки, они постоянные. 

Постоянные расходы подразделяются на общепроизводственные и косвенные.

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

Шаг 6. После сбора общепроизводственных расходов можем оценить валовую прибыль и её рентабельность — от выручки вычитаем переменные и общепроизводственные расходы. 

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

Шаг 7. Переходим к косвенным расходам. Они не относятся напрямую к производству услуги, но нужны для поддержания жизнедеятельности компании. 

Косвенные расходы делят на:

а) административные — зарплаты генерального директора, главного бухгалтера, налоги с этих зарплат, хозяйственные расходы;

б) коммерческие, например, менеджеры по продажам с окладами, расходы на рекламу. 

Шаг 8. После сбора всех расходов рассчитываем операционную прибыль (EBITDA): из выручки вычитаем переменные и постоянные расходы. Далее считают рентабельность — какую долю прибыли бизнес получает с каждого рубля выручки — то есть насколько эффективно он работает.

Чтобы добраться до чистой прибыли, учитываем:

• проценты по кредитам; 

• налоги при текущей системе налогообложения;

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

↑ Нужно для правильного расчёта чистой прибыли, нельзя игнорировать.

Шаг 9. Смотрим, ниже ли расходы, чем EBITDA. Важно убедиться, что размера операционной прибыли хватает на покрытие расходов (проценты по кредитам, налоги и амортизацию). То, что осталось за вычетом всех расходов, и будет чистой прибылью.

Шаг 10. После учёта всех расходов остаётся чистая прибыль.

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

Шаг 11. Следующий шаг — работа с операционным оборотным капиталом.

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

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

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

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

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

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

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

Дебиторка — деньги, которые бизнесу должен контрагент. Кредиторка — деньги, которые должен бизнес. 

Кредиторка — это не всегда долг за купленное у поставщика. Бывают ситуации, когда клиент внёс аванс, но зубы ещё не вылечил — тогда ему должна стоматология (это тоже кредиторка). 

Шаг 12. Планируем движение денежных средств (ДДС) — заранее рассчитываем, сколько и когда в бизнес придет денег и сколько уйдёт, чтобы собственник точно понимал, хватит ли на оплату счетов и зарплат.

↑ Разделяем движение денег на виды деятельности: операционную, инвестиционную, финансовую

Операционная — связана с основной деятельностью: покупаем, производим, продаём. 

Инвестиционная — покупка и продажа основных средств, вложения денег, получение процентов. 

Финансовая — привлечение заёмных средств, их выплата, внесение учредителем денег в бизнес, выплата дивидендов. 

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

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

Нужно менять модель, корректировать факторы, тактику, чтобы избежать разрыва».

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

↑ Баланс поможет оценить устойчивость, ликвидность и рентабельность собственного капитала — собственник поймёт, насколько бизнес надёжен, устойчив и за чей счет живет

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

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

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

В Нескучных, например, используют: 

1. Модель трёх отчётов (ОПиУ, ДДС, баланс).

2. Инвестиционную финансовая модель — та же основа, плюс инвестиционные показатели.

3. Финмодель масштабирования — пригодится, когда планируется расширение бизнеса: прогноз роста, доходов, расходов.

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

Чтобы выбрать подходящую финансовую модель, нужно определить цель вашей деятельности.

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

Планируете рост — используйте модель масштабирования.

Финансовое моделирование в Excel: частые вопросы собственников

Что лучше: финмодель или бизнес-план?

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

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

Может ли бухгалтер заменить финдиректора?

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

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

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

Бухгалтерия — фундамент, финдиректор — архитектор, который проектирует будущее компании.

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

Какие популярные программы используют, чтобы вести управленческий учёт?

1С, ПланФакт, Финтабло, iiko (для ресторанного бизнеса), Ident (для стоматологии).

Финансовое моделирование в Excel: в двух словах

• Финансовое моделирование в Excel структурирует ключевые показатели компании и позволяет анализировать её состояние с помощью цифр.

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

• Финмодель включает всё важное: выручку, расходы, налоги, долги, запасы, движение денег и баланс. Это нужно, чтобы увидеть полную картину бизнеса.

Поможем построить финмодель, чтобы увидеть прибыль и начать получать дивиденды

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

Вот так клиенты Нескучных стали счастливее:

• Мечта vs реальность: как финдиректор уберёг спортшколу от больших убытков

• Как финдир помог увеличить выручку на 22% за 10 месяцев: кейс по оптимизации бизнес-процессов в общепите

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

• Как производственная компания разобралась с себестоимостью после 4 лет неудач

• Перестали давать скидки в убыток и выросли по маржинальности на 11%: кейс торгово-производственной компании

Самый простой способ подобрать инструменты, подходящие именно вам — это онлайн-встреча. Она бесплатная и ни к чему не обязывает ↓