Ольга Воробьева

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

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

Финансовая модель бизнеса: что это

Финансовая модель предприятия – это плановые показатели его деятельности по:

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

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

Обычно финансовая модель строится в Excel или Google-таблицах. Часть исходных данных вносится вручную (план по объему продаж, месячный фонд оплаты труда, нормы потребления материалов на единицу изделия и т.д.). Зависимые от них показатели задаются с помощью формул. Они обеспечивают моментальный пересчет итоговых значений выручки, операционной прибыли, дебиторки, денежных притоков и т.д.

Итоговый результат финансового моделирования – три формы отчетности:

  • баланс;
  • отчет о финансовых результатах (ОФР);
  • отчет о движении денежных средств (ОДДС).

Скачайте финансовую модель в Excel, о создании которой рассказываем в статье. Файл – Простая финансовая модель для торговли в Excel (Fail-Fin-model-v-Excel)

Финансовое моделирование проекта: что надо знать

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

Вот пошаговый план реализации.

Рисунок 1. Построение финансовой модели: рекомендуемые этапы

Рисунок 1. Построение финансовой модели: рекомендуемые этапы

Опробуем данный подход на практике. Рассмотрим пример для организации, которая занимается розничной торговлей. Такой выбор не случайный. Любое производство сложнее торговли, так как в нем предполагаются:

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

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

Финансовая модель (ФМ) в Excel: считаем доходы

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

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

Основная сложность этапа: откуда взять данные по плану продаж? Все зависит от вашей цели:

  • если она пока в том, чтобы построить и «обкатать» работоспособность ФМ, то берите любые данные. Например, за прошлый год или произвольные;
  • если вам одновременно нужны и модель, и качественные результаты расчета по ней, то займитесь планированием отдельно.

Мы ратуем за то, чтобы на данном этапе не уходить в детали. Но разумно уже сейчас подумать: чем будете усложнять и этим приближаться к реальности?

Вот два совета.

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

____________________

Примечание: в бюджет платится разница между НДС, который начислен к уплате, и тем, который предъявляется к возмещению по собственным закупкам.

____________________

Второй. Введите аналитику по продажам. Самое простое – это номенклатурные позиции. Дополнительно к ним:

  • процент возврата товаров;
  • категория клиента (физическое лицо или организация);
  • номер магазина или наименование региона, если есть несколько точек продаж.

Помним: пока излишняя детализация лишь запутает. Поэтому получаем в Excel простой план по доходам.

Рисунок 2. Упрощенный план по доходам в Excel

Рисунок 2. Упрощенный план по доходам в Excel

Построение финансовых моделей: пример планирования расходов

Определимся с расходами также пока в первом приближении. Обобщим полный перечень трат компании в разрезе элементов. Это будут:

  • материальные траты. В упрощенном варианте предположим, что к ним относятся только траты на покупку товара без НДС и что организация продает в течение месяца столько, сколько закупает. Значит, величину определим как произведение цены приобретения и объема продаж;
  • зарплата работников. Ограничимся единой суммой фонда оплаты труда за месяц без разбивки по составляющим;
  • социальные отчисления. Для них составим формульную зависимость. Обычно это 30% плюс процент взносов «на травматизм» в Фонд социального страхования от начисленной оплаты труда. Данный процент зависит от класса профессионального риска по организации. Мы взяли минимальное значение 0,2%;
  • амортизация. Вводим строку в ФМ, если в собственности предприятия есть основные средства и нематериальные активы. Указываем единую сумму для каждого месяца на основе средних фактических или предполагаемых значений;
  • прочие расходы. Это отчасти уход в детализацию. Однако если в компании есть постоянные легко планируемые траты, то зафиксируйте их. К ним относятся: арендные платежи, траты на рекламу, на командировки и т.п.

Для упрощенной ФМ такого списка достаточно. А это четыре совета на будущее о том, чем усложнить.

Первый. Введите дополнительные позиции в материальные расходы. Например, такие:

  • отопление, водоснабжение, электроэнергия;
  • упаковка;
  • ГСМ на автотранспорт, если он есть на балансе;
  • канцелярские принадлежности и т.п.

Второй. Отдельной строкой покажите входной НДС. Когда ставка – одна (20%), то его сумма – это произведение 20% на совокупные материальные траты без НДС. Когда примешивается еще 10%, тогда:

  • либо упрощайте и считайте налог по максимальной ставке;
  • либо усложняйте, применяя средневзвешенное значение ставки исходя из доли в закупках с НДС по 20% и 10% в прошлые периоды.

Третий. Разделите оплату труда на составляющие:

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

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

Рисунок 3. Финансовая модель бизнеса в Excel: план по расходам

Рисунок 3. Финансовая модель бизнеса в Excel: план по расходам

То, что мы посчитали, является основой для:

  • вычисления прибыли/убытка в ОФР;
  • корректировки накопленного финансового результата в балансе;
  • построения притоков и оттоков в ОДДС.

Перейдем ко второму этапу – обобщению данных в системе финансовых отчетов. Рассмотрим, как это делается.

Составляем плановый отчет о финансовых результатах

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

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

Рисунок 4. Упрощенный ОФР в Excel-модели

Рисунок 4. Упрощенный ОФР в Excel-модели

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

ОФР тесно связан с балансом и ОДДС. Смотрите в таблице, куда и какую информацию он передает.

Таблица 1. Взаимосвязь плановых ОФР, баланса и ОДДС

Строка ОФР Связанная строка баланса Связанная строка ОДДС
Операционная прибыль (убыток) Нераспределенная прибыль (убыток) – добавляется к значениям предыдущего периода
Выручка (операционные доходы) Поступления от покупателей и заказчиков – сумма выручки корректируется на авансы полученные и дебиторскую задолженность по формуле:
Поступления = Выручка + Авансы полученные – Дебиторская задолженность*
Операционные расходы Платежи по текущей деятельности – сумма денежных** расходов корректируется на авансы выданные и кредиторскую задолженность по формуле:
Платежи = Денежные расходы + Авансы выданные – Кредиторская задолженность
Примечание:
* в примере ФМ формируется по организации розничной торговли. Поэтому полагаем: авансов и дебиторки от покупателей нет. Значит, поступления равняются выручке;
** денежные расходы – те, которые приводят к оттоку денег (зарплата, налоги, материальные). Для сравнения: к неденежным относится амортизация

Чтобы плановый ОФР оказался более информативным, дополним строками:

Формируем плановый отчет о движении денежных средств

Здесь задача:

  • обобщить денежные притоки и оттоки;
  • рассчитать чистый денежный поток как разницу между ними;
  • прибавить его значение к остатку денег на начало периода и вычислить остаток денег на конец. Через эту сумму ОДДС связан с балансом.

Для создаваемой ФМ исходим из допущений:

  • организация занимается только основной деятельностью. При этом не покупает оборудование и ценные бумаги, не берет и не дает взаймы, не принимает новых учредителей. Значит, формируется один вид денежных операций – текущий. О двух других – инвестиционных и финансовых – читайте в статье «Анализ движения денежных средств компании»;
  • товары оплачиваются поставщику двумя платежами: 60% – аванс в месяце, который предшествует поставке, 40% – окончательный расчет сразу после принятия на склад;
  • зарплата выплачивается двумя переводами: 50% – аванс в месяце начисления, 50% – перевод остатка в периоде, который следует за отработанным;
  • страховые взносы перечисляются в месяце, идущем за их начислением;
  • прочая составляющая расходов по обычным видам деятельности представлена арендой. Платежи по ней производятся в месяце начисления арендной платы.

Получаем вариант ОДДС.

Рисунок 5. Упрощенный отчет о движении денежных средств в ФМ

Рисунок 5. Упрощенный отчет о движении денежных средств в ФМ

Сводим плановый баланс

Предположим, что у организации:

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

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

Таблица 2. Техника сведения упрощенного баланса

Показатель на начало месяца Расчетная формула Пояснения
Основные средства Основные средства на начало предыдущего месяца (баланс) – Амортизация за месяц (ОФР)
Запасы Неизменны и равняются нулю из-за допущения: закупаемые товары полностью распродаются в течение месяца
Дебиторская задолженность Дебиторская задолженность на начало предыдущего месяца (баланс) + Платежи поставщикам за товары (ОДДС) – Материальные расходы (ОФР) В примере нет дебиторки покупателей, так как организация торгует в розницу
Денежные средства Денежные средства на начало предыдущего месяца (баланс) + Чистый денежный поток за месяц (ОДДС)
Уставный капитал Неизменен и равняется 1000 тыс. руб. из-за допущения: новых учредителей в течение года не было
Нераспределенная прибыль Нераспределенная прибыль на начало предыдущего месяца (баланс) + Прибыль за месяц (ОФР)
Кредиторская задолженность Кредиторская задолженность на начало предыдущего месяца + Начисления по зарплате, социальным отчислениям и аренде (ОФР) – Платежи по зарплате, социальным отчислениям и аренде (ОДДС)

Формат баланса будет таким.

Рисунок 6. Упрощенный баланс в финансовой модели в Excel

Рисунок 6. Упрощенный баланс в финансовой модели в Excel

Детализация финансовой модели в Excel

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

  • используйте разные группировки расходов в ОФР. Мы обобщили их по элементам. Такой подход называется «по характеру расходов». Но он – не единственный. Можно одновременно с ним или вместо него использовать функциональную классификацию с выделением себестоимости продаж, а также расходов на сбыт и управление. Подробнее про такие варианты читайте в статье «БДР: бюджет доходов и расходов»
  • добавьте другие виды прибыли в ОФР. Например, валовую. Она покажет разницу между продажной и закупочной ценами товаров. Если в организации есть прочие операции, то в отчете не обойтись без чистой прибыли. Читайте о видах финансового результата в материале «Анализ финансовых результатов деятельности компании»;
  • включите в ОФР строки для прочих доходов и расходов. Растущая организация не сможет обходиться без них. Например, взятие кредита приведет к появлению процентных платежей по нему. Они – это прочий расход;
  • расширьте ОДДС за счет инвестиционных и финансовых операций, если собираетесь приобретать оборудование и брать взаймы. Не смешивайте такие денежные потоки с текущими;
  • детализируйте сложносоставные строки в балансе. Например, из запасов выделите товары, материалы, готовую продукцию и незавершенное производство (последние два пункта – неактуальны для торговли). Из состава дебиторки: авансы выданные поставщикам и долги покупателей. По аналогии поступите с кредиторкой и покажите в отдельных статьях: авансы полученные, задолженность перед поставщиками, перед персоналом по оплате труда, по налогам и страховым взносам, перед учредителями по дивидендам.

Не забудьте скачать Excel-файл с полученной финансовой моделью из начала статьи.

Финансовое моделирование в Excel помогает понять:

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

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

© «УПРАВЛЯЕМ ПРЕДПРИЯТИЕМ»
Все права защищены. Все торговые марки являются собственностью их правообладателей.