Финансовое моделирование в Excel и Google Таблицах: гид для малого бизнеса

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

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

Кому пригодится:

Задачи, которые закрывает модель:

Модель обновляется вместе с фактическими данными и служит долго.

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

P&L, или отчёт о прибылях и убытках, — ОПиУ — сколько компания заработала и потратила за период.

Cash flow, или отчёт о движении денежных средств, — ОДДС — показывает, сколько реальных денег пришло и ушло. В отличие от P&L здесь учитываются только фактические платежи с их сроками. Например, клиент подписал акт в марте, а оплатил в мае — в P&L выручка отразится мартом, в cash flow деньги появятся маем.

ОДДС делится на три вида деятельности:

Баланс — состояние бизнеса на конкретную дату. Состоит из двух частей:

Три отчёта связаны между собой, и взаимосвязь работает как проверка расчётов:

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

Пример — розничный интернет-магазин одежды. Небольшая команда, продажи через сайт, оплата картой, поставщик даёт отсрочку 30 дней. Модель — помесячная, на 12 месяцев.

Шаг 1. Определить цель модели. От неё зависит детализация. Инвестиционная модель считает окупаемость и NPV — чистую приведённую стоимость проекта — с горизонтом 3−5 лет. Оперативный бюджет собирают помесячно на год. Модель для оценки проекта делят на этапы с шагом от недели до квартала.

Шаг 2. Разделить листы. Минимальный набор:

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

Шаг 3. Заполнить вводные. На отдельном листе указывают все допущения: цена, средний чек, конверсия сайта, стоимость привлечения клиента, ставка налога, оклады сотрудников, ставка по кредиту, темпы роста рынка. Рядом с каждой цифрой — комментарий, откуда она взялась. Такой подход помогает через полгода вернуться к модели и понять её логику.

Шаг 4. Собрать план продаж через воронку — последовательность этапов, через которые проходит клиент. Например, человек посещает сайт → оставляет заявку → совершает сделку → оплачивает. На каждом этапе часть людей отсеивается, оставшаяся доля называется конверсией. Формула выручки собирает эти доли в одно число: количество посетителей на входе x общая конверсия визита в оплату x средний чек. Так план продаж опирается на измеримые показатели — цифры воронки видны в аналитике сайта и в CRM.

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

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

Шаг 6. Собрать P&L помесячно. Для каждого месяца — та же логика каскада: из выручки последовательно вычитаются группы расходов до чистой прибыли. Проверка на реалистичность: рентабельность соразмерна отрасли, а темпы роста не выше рыночных.

Шаг 7. Построить cash flow. Заложить отсрочки: в примере покупатели платят картой сразу, а поставщик даёт 30 дней — закупка марта оплачивается в апреле. Если бы отсрочку давали клиентам, выручка марта попала бы в кассу апреля. Отдельной строкой отражается оборотный капитал — запасы товара, задолженность клиентов и долги перед поставщиками. Считается он просто: запасы + долги клиентов − долги поставщикам. Строкой ниже обычно прописывают его изменение за месяц: если оборотный капитал вырос, значит, деньги ушли в товар и ещё не вернулись на счёт.

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

Шаг 9. Проверить логику. Три контрольных точки: актив равен пассиву, изменение денег в балансе равно сальдо cash flow, а чистая прибыль из P&L перетекает в капитал баланса.

Шаг 10. Заносить фактические данные по мере поступления и сравнивать план с фактом. План-факт-анализ превращает модель из справочника в инструмент управления.

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

В Google Таблицах те же расчёты, что и в Excel, но процесс работы устроен иначе: акцент смещён на командную работу и связь с внешними данными.

Совместное редактирование в реальном времени. Собственник, бухгалтер и наёмный аналитик работают в одном файле, видят курсоры друг друга и правки без сохранения и пересылки версий. Права на файл гибкие: только просмотр, комментарии, редактирование. Отдельным пользователям можно закрыть доступ к листу «Вводные» или к диапазону с зарплатами.

История версий и откат — по каждому изменению видно автора и время. Ошибочную правку можно откатить без ручного восстановления.

Уникальные функции Google Таблиц:

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

Формулы делятся на два уровня: технические функции таблиц и финансовые метрики, которые из них складываются.

Технические функции — работают в Excel и Google Таблицах одинаково:

Финансовые метрики. Каждая рассчитывается формулой в отдельной ячейке модели:

Амортизация и налог на прибыль задаются формулами от параметров листа «Вводные». Обе величины меняются вместе с расчётами:

Амортизация. Стоимость долгосрочного актива — оборудования, ремонта, компьютеров — растягивается на весь срок использования. Расчёт простой: стоимость делят на срок в месяцах. Реальные деньги за актив ушли при покупке, а расчётный расход по месяцам только уменьшает налоговую базу.

Налог на прибыль. Порядок расчёта зависит от режима налогообложения, а ставки — в том числе от региона и вида деятельности. Общая формула: Налог = База x Ставка. Ставки по самым распространённым режимам:

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

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

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

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

Стресс-тест — самое жёсткое отклонение вводных: падение выручки на 30−50%, рост закупочной цены товара на 20%. Показывает, сколько месяцев компания продержится, до какого минимального остатка упадёт касса и потребуется ли внешнее финансирование.

Как собрать сценарии в файле. Самый распространённый способ — держать на отдельном листе «Сценарии» колонки под каждый сценарий, а над ними — выпадающий список. Формула ВЫБОР / CHOOSE подставляет значения выбранной колонки в лист «Вводные», и вся модель пересчитывается.

Помимо этого способа, в Excel есть встроенные инструменты — «Диспетчер сценариев» и «Таблица данных». Первый хранит наборы вводных как отдельные варианты и переключает их одним кликом, второй же строит матрицу: как меняется итог при изменении одного или двух параметров. Начинающим бывает удобнее сделать копии модели на отдельных листах — по одному на каждый сценарий, включая стресс-тест.

Что смотреть в сценариях:

Анализ чувствительности — слой поверх сценариев: как чистая прибыль или NPV меняются при отклонении одного параметра — цены, конверсии, ставки по кредиту — на 10−30%. Помогает найти самый чувствительный параметр модели и оценить цену ошибки в прогнозе.