Финансовая модель в Excel: пошагово, с формулами и примером
Практическая модель от драйверов продаж и точки безубыточности до P&L, Cash Flow, сценариев и контроля ошибок.
1. Что должна отвечать финансовая модель
Финансовая модель — это связанная система допущений и расчётов, а не таблица с желаемой прибылью. Хорошая модель позволяет изменить цену, объём продаж, себестоимость, срок оплаты или фонд зарплаты в одном месте и сразу увидеть влияние на выручку, прибыль и остаток денег. Для малого бизнеса обычно достаточно помесячного горизонта 12–24 месяца: он достаточно подробный для решений и не создаёт ложной точности далёкого прогноза.
До сборки файла сформулируйте вопросы, на которые он должен отвечать. Например: сколько заказов нужно для безубыточности; сколько денег потребуется до выхода в плюс; выдержит ли бизнес падение продаж на 20%; когда понадобится нанять ещё одного сотрудника; какую цену можно дать клиенту без разрушения маржи. Если расчёт не помогает принять решение, его не стоит добавлять только ради объёма модели.
Разделяйте три слоя: вводимые допущения, расчёты и управленческий вывод. Пользователь меняет только допущения, формулы остаются защищёнными, а итоговые показатели собираются на отдельном листе. Такая архитектура заметно снижает риск случайно заменить формулу числом и получить правдоподобный, но неверный прогноз.
2. Структура файла: 6 листов вместо одной большой таблицы
Практичная книга состоит из шести листов. «Допущения» хранит цену, объём, рост, переменную себестоимость, комиссии, зарплаты, аренду, налоги, отсрочки и стартовый остаток денег. «Продажи» рассчитывает физические драйверы и выручку. «P&L» показывает экономический результат. «Cash Flow» — реальные поступления, выплаты и остаток денег. «Сценарии» содержит альтернативные наборы параметров. «Контроль» собирает ключевые проверки и показатели модели.
На листе «Допущения» полезны колонки: Параметр, Значение, Единица, Источник, Комментарий. Например: Средний чек — 3 000 ₽; заказы первого месяца — 400 шт.; рост заказов — 5% в месяц; переменные затраты — 1 800 ₽ на заказ; постоянные расходы — 360 000 ₽ в месяц; отсрочка покупателей — 30 дней. Исходные числа не должны быть спрятаны внутри формул на других листах.
Для временной оси используйте одинаковые месяцы во всех отчётах. Если B1 — январь, C1 — февраль и далее, ссылки между листами становятся прозрачнее. Ключевые параметры можно именовать или фиксировать абсолютными ссылками, например =$B$4. Формула =Продажи!B12*Допущения!$B$4 лучше, чем =Продажи!B12*3000: во втором варианте через полгода никто не вспомнит происхождение числа 3000.
3. Выручка: прогнозируйте от драйверов, а не от желания
Для интернет-магазина базовая модель выручки строится от количества оплаченных заказов и среднего чека. Если B5 — заказы, B6 — средний чек, то выручка в B7 равна =B5*B6. При 400 заказах и чеке 3 000 ₽ получаем 1 200 000 ₽. Если ожидаемый месячный рост заказов лежит в Допущения!$B$7, количество заказов следующего месяца можно рассчитать как =B5*(1+Допущения!$B$7). При росте 5% февраль даст 420 заказов.
Если бизнес работает через воронку, модель лучше строить ещё глубже: лиды × конверсия = заказы, заказы × средний чек = выручка. Например, 2 000 лидов при конверсии 20% дают 400 заказов. Тогда падение конверсии до 17% автоматически уменьшит продажи до 340 заказов, и вы увидите финансовый эффект изменения именно коммерческого драйвера.
Для нескольких продуктов не усредняйте всё без необходимости. Создайте строки по продуктам: количество, цена, выручка и переменные затраты, затем суммируйте =СУММ(B10:B14). Это особенно важно, если маржа продуктов различается: рост доли низкомаржинального товара способен увеличить общую выручку и одновременно снизить прибыль.
4. Расходы, маржинальный доход и точка безубыточности
Разделите расходы на переменные и постоянные. Переменные возникают вместе с продажей: закупка, упаковка, комиссия, доставка. Постоянные не зависят напрямую от количества заказов в пределах текущей мощности: аренда, фиксированные оклады, сервисы. Маржинальный доход с единицы считается как Цена−ПеременныеЗатраты. При цене 3 000 ₽ и переменных затратах 1 800 ₽ он равен 1 200 ₽, а маржинальность — =1200/3000, то есть 40%.
Точка безубыточности в штуках равна ПостоянныеРасходы/(Цена−ПеременныеЗатраты). При постоянных расходах 360 000 ₽ получаем =360000/(3000-1800)=300 заказов. В Excel при цене в B4, переменных затратах в B5 и постоянных расходах в B6 используйте =ЕСЛИОШИБКА(B6/(B4-B5);0). Выручка безубыточности в этом примере равна 900 000 ₽.
Не относите расходы к постоянным навсегда. Например, зарплата может быть ступенчатой: до 500 заказов справляется текущая команда, а с 501-го нужен новый сотрудник за 80 000 ₽. Тогда формула может быть =БазовыйФОТ+ЕСЛИ(Заказы>500;80000;0). Такие пороги делают прогноз реалистичнее, чем простое умножение прошлогодних расходов на процент роста.
5. Практический пример: от продаж до прибыли
Возьмём интернет-магазин. В базовом месяце — 400 заказов по 3 000 ₽, значит выручка 1 200 000 ₽. Переменные затраты на заказ — 1 800 ₽, или 720 000 ₽ всего. Маржинальный доход равен 480 000 ₽. Постоянные расходы: маркетинг 120 000 ₽, зарплаты 170 000 ₽ и аренда с сервисами 70 000 ₽ — всего 360 000 ₽. Операционная прибыль до налогов составляет 120 000 ₽, а операционная маржа =120000/1200000=10%.
Теперь проверим стресс-сценарий. При 340 заказах выручка падает до 1 020 000 ₽, переменные затраты — до 612 000 ₽, маржинальный доход — до 408 000 ₽. Постоянные расходы остаются 360 000 ₽, поэтому прибыль уменьшается до 48 000 ₽. Продажи снизились на 15%, а прибыль — на 60%. Именно такой нелинейный эффект модель должна показывать руководителю до принятия решения.
Если одновременно снизить цену до 2 850 ₽, при 340 заказах выручка составит 969 000 ₽. При неизменных переменных затратах 1 800 ₽ маржинальный доход с заказа упадёт до 1 050 ₽, общий — до 357 000 ₽. После постоянных расходов получится убыток 3 000 ₽. Простая скидка 5% превращает ещё прибыльный стресс-сценарий в убыточный — полезный пример того, почему цену нельзя оценивать только по росту конверсии.
Создать финансовую модель в Excel
Опишите бизнес, доходы, расходы и период — SmartXLSX соберёт персональную модель XLSX с рабочими формулами.
Открыть инструмент →6. P&L и Cash Flow: прибыль не равна деньгам
На P&L соберите выручку, переменные расходы, маржинальный доход, постоянные расходы и операционную прибыль. Если выручка в B10, переменные расходы в B11, постоянные в B14, то маржинальный доход =B10-B11, операционная прибыль =B10-B11-B14, а операционная маржа =ЕСЛИОШИБКА(B15/B10;0). P&L показывает экономику периода, но не отвечает на вопрос, сколько денег реально лежит на счёте.
Cash Flow учитывает даты денег. Базовые строки: Остаток на начало, Поступления, Выплаты, Чистый поток, Остаток на конец. Если B30 — начальный остаток, B31 — поступления, B32 — выплаты, конечный остаток =B30+B31-B32. Начало следующего месяца должно ссылаться на предыдущий конец: =B33. Это простая, но критичная контрольная связь.
Допустим, январская выручка 1 200 000 ₽ признана полностью, но 30% клиентов платят с отсрочкой 30 дней. В январе поступит только 840 000 ₽, а 360 000 ₽ перейдут в февраль. При выплатах января 900 000 ₽ и стартовом остатке 100 000 ₽ конец месяца составит 40 000 ₽. P&L при этом может показывать прибыль 120 000 ₽. Так модель заранее выявляет кассовый риск, который невозможно увидеть только по прибыли.
7. Сценарии и чувствительность: что сломает план первым
Минимально сделайте три сценария: базовый, консервативный и оптимистичный. В строках таблицы сценариев храните цену, количество заказов, рост, переменные затраты, конверсию и маркетинг. В отдельной ячейке выбирайте сценарий, а параметры подтягивайте через ПРОСМОТРX/XLOOKUP или ИНДЕКС+ПОИСКПОЗ. Например: =ПРОСМОТРX($B$2;Сценарии!$B$2:$D$2;Сценарии!$B$5:$D$5). Не копируйте всю книгу три раза — версии быстро разойдутся.
Отдельно полезен анализ чувствительности по двум параметрам. Например, по строкам задайте цену 2 700, 2 850, 3 000, 3 150 ₽, а по столбцам — 300, 350, 400, 450 заказов. В каждой точке рассчитывайте прибыль. Такая матрица показывает не один прогноз, а область устойчивости бизнеса и помогает увидеть комбинации, при которых прибыль становится отрицательной.
Смотрите не только на прибыль, но и на минимальный остаток денег. Консервативный сценарий может оставаться прибыльным за год, но требовать 500 000 ₽ дополнительного финансирования в третьем месяце. Для собственника это часто важнее красивой годовой маржи.
8. Контроль модели и типичные ошибки
Первая ошибка — исходные числа внутри формул. Вторая — смешивание рублей, процентов и штук без единиц измерения. Третья — пропорциональное изменение всех расходов, хотя часть фиксированная или ступенчатая. Четвёртая — прогноз P&L без Cash Flow. Пятая — рост продаж без ограничений мощности, персонала и маркетинга. Шестая — ручная замена формул фактом, после которой прогнозные месяцы начинают ссылаться на случайные значения.
Сделайте лист «Контроль». Проверка перехода денег: =ЕСЛИ(C30=B33;"OK";"ПРОВЕРИТЬ"). Проверка выручки: итог должен равняться сумме продуктовых строк. Проверка сценария: выбранное значение существует в справочнике. Проверка маржи: при отрицательном маржинальном доходе выводите отдельное предупреждение. Для временного ряда контролируйте, что каждый следующий месяц действительно следует за предыдущим.
Перед использованием модели измените по одному ключевому допущению и проверьте направление результата. Цена выше должна при прочих равных увеличивать маржинальный доход; рост переменной себестоимости — уменьшать его; увеличение отсрочки оплаты не должно менять P&L, но должно ухудшать Cash Flow. Такие тесты часто находят ошибки связей быстрее визуального просмотра сотен ячеек.
9. Как обновлять модель и использовать её для решений
После закрытия месяца не удаляйте прогноз. Сохраните план и добавьте факт, затем посчитайте отклонение: =Факт-План и =ЕСЛИОШИБКА((Факт-План)/План;0). Если выручка ниже плана, разложите отклонение на драйверы: меньше лидов, ниже конверсия, меньше заказов или ниже средний чек. Для расходов отдельно смотрите цену единицы и объём потребления.
Раз в месяц обновляйте фактические данные, пересматривайте только те допущения, для которых появилась новая информация, и сохраняйте объяснение изменений. Тогда модель становится живым управленческим инструментом, а не одноразовой презентацией. Для регулярного контроля результата полезно связать её с план-факт анализом, бюджетом и платёжным календарём.
Если исходную структуру нужно получить быстро, используйте инструмент SmartXLSX для финансовой модели: задайте бизнес, период, доходы и расходы, а затем адаптируйте полученный XLSX под реальные драйверы. Соседние руководства по P&L и Cash Flow помогут глубже настроить соответствующие отчёты, не перегружая одну модель лишней логикой.
Частые вопросы
На какой период строить финансовую модель?
Для операционного малого бизнеса обычно достаточно 12–24 месяцев помесячно. Для инвестиционного проекта горизонт может быть длиннее, но дальние периоды разумно укрупнять.
Какие листы нужны в финансовой модели?
Практичный минимум: Допущения, Продажи, P&L, Cash Flow, Сценарии и Контроль. Вводимые параметры лучше не смешивать с расчётами.
Как посчитать точку безубыточности?
Разделите постоянные расходы на маржинальный доход с единицы. При расходах 360 000 ₽, цене 3 000 ₽ и переменных затратах 1 800 ₽ точка безубыточности равна 300 заказам.
Почему нужен Cash Flow, если есть P&L?
P&L показывает экономическую прибыль периода, а Cash Flow — фактические поступления, выплаты и остаток денег. Из-за отсрочек, авансов и закупок прибыльный бизнес может получить кассовый разрыв.
Как сделать сценарии без копирования книги?
Храните базовый, консервативный и оптимистичный наборы допущений в одной таблице и подставляйте выбранный набор через ПРОСМОТРX/XLOOKUP или ИНДЕКС+ПОИСКПОЗ.
Какие формулы обязательно проверить?
Проверьте расчёт выручки от драйверов, маржинальный доход, точку безубыточности, переход конечного остатка Cash Flow в начало следующего месяца и отсутствие исходных чисел, зашитых внутрь формул.
Как учитывать факт после начала периода?
Не затирайте исходный план. Храните план и факт отдельно, считайте абсолютное и процентное отклонение и обновляйте прогноз будущих месяцев только при появлении новой информации.