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

Игорь Николаев, финансовый директор группы компаний «Интернест»

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

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

Если предстоит создать модель с нуля, руководствуйтесь принципом «от простого к сложному», иначе есть риск запутаться в большом количестве формул и ссылок. Сначала сформируйте простейшую модель с минимальным количеством элементов. Установить связи общего характера между внешними параметрами (спрос на продукцию, стоимость ресурсов) и внутренними показателями деятельности компании (выручка, затраты, денежные потоки и т. д.). В первой итерации можно не заботиться об особой точности. На этом этапе важнее установить правильные взаимосвязи между переменными так, чтобы модель автоматически пересчитывалась после изменения исходных данных и позволяла выстраивать различные сценарии. Уже после этого можно приступить к ее развитию, детализовать показатели, ввести дополнительные уровни аналитики и т. д.

Начнем финансовую модель с внешних параметров (скачать модель из примера). Отправной точкой для дальнейших расчетов послужит план продаж, в примере – оказания услуг. Для этого в Excel на одном из листов книги разместим таблицу с планом продаж в денежном выражении (см. таблицу 1. План продаж). На этом этапе выручку можно указать «навскидку» или использовать данные прошлого года. Пока точность не имеет большого значения. Позднее при детализации модели план продаж придется доработать.

Таблица 1. План продаж, тыс. руб.

ПоказательЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.
ПоказательЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.
Услуга 1151617181920212223242526
Услуга 2303335384043454850535558
Услуга 365707580859095100105110115120
Итого110119127136144153161170178187195204

Как планировать расходы в финансовой модели

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

Сделаем небольшое допущение и предположим, что в примере переменные только затраты на оплату труда. Заработная плата сотрудников полностью зависит от объема оказанных услуг, на нее уходит примерно 30 процентов выручки от реализации. Кстати, план затрат удобнее разместить на отдельном листе Excel (см. таблицу 2. План затрат). В нем зарплата рассчитывается помесячно как произведение коэффициента 0,3 (30% : 100%) и плана продаж на определенный месяц. Расходы на аренду и управление вводятся на первом этапе создания модели не как расчетные величины, а как фиксированные значения. В дальнейшем при детализации модели их можно будет заменить формулами, увязав с другими показателями.Таблица 2. План затрат, тыс. руб.

ПоказательЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.
ПоказательЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.
Зарплата333638414346485153565961
Аренда303030303030303030303030
Управленческие расходы505050505050505050505050
Итого113116118121123126128131133136139141

Как сформировать сводные планы в финансовой модели

Как только в модели сформируете два функциональных плана – продаж и затрат, создавайте баланс, план доходов и расходов, а также план движения денежных средств. Данные в них допустимо вносить двумя способами: рассчитать или извлечь напрямую из операционных или функциональных планов – продаж, затрат и других, например дебиторской задолженности, запасов, производства и др. В Excel установите ссылки на соответствующую ячейку и страницу. Ручной ввод цифр тут недопустим за исключением данных о начальных остатках в балансе, иначе модель просто не будет пересчитывать данные.

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

В нашем примере три листа, на которых разместим по отдельности план доходов и расходов (ПДР), план движения денежных средств (ПДДС) и прогнозный баланс (ПБ). В плане доходов и расходов (см. таблицу 3. План доходов и расходов) строки «Операционные расходы» и «Операционные доходы» заполните при помощи ссылок на соответствующие ячейки функциональных планов. Выручку расшифруйте по видам услуг, затраты – по статьям. Такая расшифровка допустима, поскольку не утяжеляет восприятие отчета и не усложняет его анализ. Кроме того, в отчет включим два аналитических показателя – рентабельность (как отношение прибыли к выручке) и прибыль нарастающим итогом. Если понадобится провести более глубокий анализ, в частности, динамики доли оплаты труда в себестоимости услуг, все необходимые для этого расчеты лучше проводить на отдельном листе.Таблица 3. План доходов и расходов, тыс. руб.

ПоказателиЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.Янв.
ПоказателиЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.Янв.
Операционные доходы, в т. ч.1101191271361441531611701781871952041881
услуга 1151617181920212223242526246
услуга 2303335384043454850535558525
услуга 3657075808590951001051101151201110
Операционные расходы, в т. ч.1131161181211231261281311331361391411524
зарплата333638414346485153565961564
аренда303030303030303030303030360
управленческие расходы505050505050505050505050600
Операционная прибыль-339152127333945515762357
Рентабельность, %-2,732,497,0110,9614,4417,5420,3122,8025,0627,1028,9730,6918,96
Прибыль нарастающим итогом-309244571104143187238294357713

План движения денежных средств (см. таблицу 4. План движения денежных средств) в нашем примере формируем со следующими допущениями. Первое: разделы «Финансовая деятельность» и «Инвестиционная деятельность» исключаем из плана. Предполагаем, что компания ведет только операционную деятельность, без привлечения заемных средств и без капитальных вложений. Еще одно допущение. Компания оказывает услуги физическим лицам за наличный расчет, а значит, время оказания услуги и ее оплаты совпадает – в итоге у компании нет дебиторской задолженности. Ситуация с платежами по операционной деятельности не так однозначна. Зарплата и аренда выплачиваются в месяце, следующем за месяцем начисления, а управленческие расходы – в месяце их осуществления.

Таблица 4. План движения денежных средств, тыс. руб.

ПоказателиЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.
ПоказателиЯнв.Февр.МартАпр.МайИюньИюльАвг.Сент.Окт.Нояб.Дек.
Поступления по операционной деятельности110119127136144153161170178187195204
Оплата услуг110119127136144153161170178187195204
Платежи по операционной деятельности50113116118121123126128131133136139
Зарплата03336384143464851535659
Аренда03030303030303030303030
Управленческие расходы505050505050505050505050
Сальдо по операционной деятельности60611172329354147535965
Сальдо на начало периода10707687104128157192233281334393
Сальдо на конец периода707687104128157192233281334393458

Последнее, что остается сделать, – создать прогнозный баланс (см. таблицу 5. Прогнозный баланс). Данные по оборотам за период берем из плана доходов и расходов, бюджета движения денежных средств, начальные остатки – из баланса за предыдущий период. Здесь допустимо вручную внести цифры.Таблица 5. Прогнозный баланс, тыс. руб.скачать.xls

Показатели01.0101.0201.0301.0401.0501.0601.0701.0801.0901.1001.1101.1201.01
Показатели01.0101.0201.0301.0401.0501.0601.0701.0801.0901.1001.1101.1201.01
Денежные средства10707687104128157192233281334393458
Дебиторская задолженность0000000000000
Основные средства0000000000000
АКТИВЫ итого10707687104128157192233281334393458
Кредиторская задолженность0636668717376788183868991
Капитал1071019345581114153197248304367
ПАССИВЫ итого10707687104128157192233281334393458

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

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

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

После того как построение баланса подобным образом завершено, остается удостовериться, что все было сделано правильно – активы равны пассивам. В нашем примере они совпадают, значит, модель работает, выдает корректные результаты.

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

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

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

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

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

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

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

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

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

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.