Как составить прогнозный Баланс и связать его с P&L и ДДС

Привет! Этот материал посвящен построению прогнозного баланса для финансовой модели в Excel, Google Sheets или Р7. Чуть ранее мы разбирали и практиковались на тренажерах, как построить P&L и ДДС. Для полной картины не хватает последнего отчета. Пришло время построить Баланс (он же Balance Sheet, или BS, или Форма 1).

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

Зачем он нужен? В отличие от отчетов о прибылях и убытках и движении денежных средств, Баланс умеет отвечать на некоторые важные вопросы, как например:

• Финансовая устойчивость: достаточно ли у компании будет активов, чтобы покрывать обязательства и не рисковать банкротством, и достаточно ли они ликвидны, чтобы не допустить кассового разрыва?
• Привлекательность для инвестора: какая будет структура капитала и какую доходность на акционерный капитал (ROE) ожидает компания?
• Кредитоспособность: какая будет долговая нагрузка у компании?

Построение Баланса может показаться самым сложным этапом в моделировании трех форм отчетности, поскольку именно Баланс содержит больше всего связей с другими отчетами, но это и самая важная проверка всей модели. В этом материале мы разберем основные статьи Баланса, а в тренажере вы научитесь с нуля строить прогнозный Баланс по шагам. Будем разбираться, как моделировать то, что отвечает на вопросы: «Чем владеет компания?» (активы) и «За чей счет она этим владеет?» (пассивы).

В этой теме:

• Закон №1 при моделировании Баланса
• Прогнозирование Активов — чем владеет компания (шаг 1)
• Прогнозирование Капитала — средства акционеров (шаг 2)
• Прогнозирование Обязательств — долги перед кредиторами (шаг 3)
• Финальная проверка — сходимость Баланса (шаг 4)
• Частые вопросы
• Тренажер

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

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

Активы (Assets) — это все ресурсы, которыми владеет компания и которые, как ожидается, принесут ей экономическую выгоду в будущем. Они делятся на внеоборотные (долгосрочные, Fixed Assets или Non-Current Assets) и оборотные (краткосрочные, Current Assets) — это один из способов классификации активов, и он обычно используется в финансовом моделировании. Есть и другие способы классификации, например: разделение на материальные (Tangible Assets) и нематериальные (Intangible Assets).

Рассмотрим основные виды активов, которые формируются в финансовых моделях. Из них ОС и ОНА будут относиться к внеоборотным, а Запасы, ДЗ и Деньги — к оборотным.

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

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

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

Как моделировать? На практике вы встретите два подхода к моделированию ОС. Выбор зависит от требуемой детализации и сложности модели.

Подход 1: сокращенный (одной строкой).
Это самый быстрый способ. В Балансе создается всего одна строка — «Основные средства» (PP&E). Ее изменение от периода к периоду рассчитывается так:

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

Подход 2: расширенный (профессиональный).
Этот способ является хорошей практикой в финансовом моделировании. Он требует создания отдельного блока-расшифровки (модуля или суб-модуля), обычно прямо под Балансом. Этот блок состоит из трех ключевых строк:

• Начальная стоимость ОС (Gross PP&E): показывает, сколько мы потратили на покупку всех активов за всю историю, без учета износа. Формула выглядит так: Начальная стоимость ОС (конец) = Начальная стоимость ОС (начало) + CapEx – Начальная стоимость проданных активов.
• Накопленная амортизация (Accumulated Depreciation): показывает сумму всего износа, начисленного по активам за все время их службы. Это «контр-актив», т.е. он уменьшает итоговую стоимость активов, поэтому в расчетах он идет с отрицательным знаком. Формула выглядит так: Накопленная амортизация (конец) = Накопленная амортизация (начало) + Амортизация за период – Накопленная амортизация проданных активов.
• Остаточная стоимость ОС (Net PP&E): это и есть реальная балансовая стоимость наших активов. Именно эта строка и переносится в основную часть Баланса в раздел «Активы». Формула выглядит так: Остаточная стоимость = Начальная стоимость – Накопленная амортизация.

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

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

Связь с другими формами: изменение запасов, их рост или снижение — это ключевая корректировка в операционном денежном потоке. Это изменение ЧОК, о котором мы говорили в предыдущей теме. Рост запасов — это отток денег, снижение запасов — это приток денег по операционному потоку.

Как моделировать? Обычно через оборачиваемость. Мы заранее в модели определяем показатель срока оборачиваемости запасов (например, 21 день) и предполагаем, что вновь поступившие на склад запасы превращаются в готовую продукцию и продаются за 21 день, то есть цикл «Поступление сырья → Переработка в готовую продукцию → Продажа» длится 21 день. Если вспомните пример с яблоками и джемом, то в начале и в конце цикла было еще два звена, «Деньги →» и «→ Деньги», но эти два блока относятся к оборачиваемости дебиторской задолженности, о которой ниже.

Итак, формула, которую можно использовать для расчета запасов в Балансе:

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

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

В финансовом моделировании ДЗ чаще всего относится именно к задолженности клиентов. Если же в бизнесе авансы поставщикам играют существенную роль, то целесообразно разделить ДЗ на две категории: дебиторскую задолженность клиентов и дебиторскую задолженность поставщиков; и смоделировать каждую по-отдельности.

Связь с другими формами: изменение ДЗ — это тоже ключевая корректировка в операционном денежном потоке. Это изменение ЧОК, о котором мы говорили в предыдущей теме. Рост ДЗ — это отток денег, снижение ДЗ — это приток денег по операционному потоку. Все как с запасами.

Как моделировать? По аналогии с запасами через срок оборачиваемости ДЗ — средний срок отсрочки, который мы даем клиентам и поставщикам, если ДЗ поставщиков присутствует в модели. Опять возвращаясь к примеру с яблоками и джемом, звено «Деньги →» — это и есть аванс поставщику (ДЗ поставщика перед нами), а звено «→ Деньги» — это задолженность клиента по оплате поставленных товаров и услуг.

Итак, формула, которую можно использовать для расчета ДЗ в Балансе:

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

Особая тема: НДС к возмещению. В ту же логику, что и прогнозирование ДЗ, укладывается и НДС к возмещению — тот, что вы заплатили поставщикам. Это, по сути, дебиторская задолженность налоговой перед вами. Его можно моделировать как отдельную строку ДЗ. Очень часто в финансовых моделях НДС игнорируется целиком, чтобы он не влиял на принятие решения, для которого создается модель. Почему так? Как правило, разность НДС к уплате – НДС к возмещению все равно предполагает, что бизнес должен налоги государству, поэтому рассчитывать на то, что эти деньги могут временно вам помочь вполне естественно, а принимать важнейшие бизнес-решения с опорой на них — недальновидно.

Это самый понятный актив — реальные деньги на счетах компании.

Связь с другими формами: Денежные средства [на конец периода] в Балансе — это Остаток денежных средств на конец [периода] в ДДС, итоговый результат этого отчета в соответствующем периоде. Это самая простая связь, однозначное соответствие двух строк в двух отчетах.

Как моделировать? Просто перенести итог отчета ДДС в Баланс. Точнее, указать ссылку в Балансе на итоговую строку ДДС.

Это ваш «налоговый щит» в будущем. ОНА — это актив, потому что он позволит вам сэкономить на налогах в будущих прибыльных периодах.

Связь с другими формами: этот актив напрямую рождается в P&L. Когда компания получает убыток, она не платит налог на прибыль. Вместо этого сумма убытка, умноженная на ставку налога, превращается в ОНА и размещается в Балансе. В будущие годы, когда компания получит прибыль, она сможет «списать» (погасить) этот актив, чтобы уменьшить налог к уплате.

Как моделировать? Мы уже коротко затрагивали ОНА в теме построения P&L и механику этого действия. Напомним. В модели это отражается так:

1. В убыточном году мы «создаем» в Балансе Отложенный налоговый актив (ОНА), равный Сумме убытка × Ставку налога.

2. В будущем компания «спишет» часть этого ОНА. В P&L для этого можно предусмотреть специальную строку, которая будет балансировать расчеты между расчетным налогом на прибыль и ОНА и «отдавать» дальше уже скорректированную цифру. Либо можно делать сложные условные расчеты прямо в существующих строках: Налоги в P&L, ОНА в Балансе. С целью прозрачности рекомендуем выносить эту часть расчета в отдельный блок-расшифровку.

3. Поскольку ОНА ведет к росту активов, то это должно балансироваться в пассивах. Механика этого действия достаточно проста: ОНА создается, когда расчетный налог у вас в P&L отрицательный, и ваша чистая прибыль должна быть еще меньше, чем уже отрицательная прибыль до налогообложения (EBT). В таком случае в строке Налоги просто пишется ноль — это как бы не дает чистой прибыли упасть еще сильнее и тем самым в Баланс в Нераспределенную прибыль «уходит» повышенное значение. В итоге уравнение Баланса сохраняется. Такая практика позволяет, в том числе, «не засорять» операционный денежный поток «бумажным» отрицательным налогом на прибыль.

В любом случае, при моделировании ОНА, следует особое внимание уделять проверке операционного денежного потока, P&L, активов Баланса и уравнения Баланса.

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

У Equity есть две главные составляющие:

• Уставный и добавочный капитал
• Нераспределенная прибыль

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

Это прямые инвестиции собственников. В модели это обычно либо статичная цифра, либо она меняется, если были новые взносы, которые отражаются в финансовом денежном потоке ДДС.

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

Это связь P&L и Баланса. Вся чистая прибыль, которую компания заработала, но не выплатила в виде дивидендов, накапливается здесь.

Формула для расчета нераспределенной прибыли достаточно проста:

Чистая прибыль берется из P&L, Дивиденды — из финансового денежного потока ДДС. То есть, если в финансовом потоке ДДС вы видите или моделируете выплату дивидендов, то (а) вам нужно уменьшить нераспределенную прибыль в Балансе и (б) убедиться, что дивидендов выплачивается не больше, чем есть этой нераспределенной прибыли.

Это заемные средства компании. Как правило, это долги компании перед третьими лицами: банками, поставщиками, государством.

В финансовом моделировании обычно встречается два типа обязательств:

• Кредиты и займы (они же Долг)
• Кредиторская задолженность

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

Это долги, как правило, перед банками или перед другими заимодавцами.

Логика моделирования этой статьи схожа с ОС и нераспределенной прибылью, а формула достаточно проста:

«Тело долга» или «тело кредита» — это разговорные термины, а в кредитных договорах всегда используется формулировка «основной долг». Это синонимы.

Все данные для этой формулы берутся либо из Баланса, либо из финансового денежного потока ДДС.

Кредиты и займы связаны с P&L через проценты. Строго говоря, моделируя проценты к уплате в P&L, вам нужно опираться на состояние кредитов и займов в Балансе. Образцовая формула для расчета процентов в данном случае выглядит так:

Эта формула может приводить к появлению циклических ссылок. Как с ними бороться, мы коротко рассматривали в теме построения P&L. Также можно почитать в этой статье.

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

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

Это долги перед поставщиками за товары или услуги, которые мы уже получили, но еще не оплатили.

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

Для КЗ справедливы все те же связи и способы моделирования, что и для ДЗ. Рост или сокращение КЗ ведет к изменению ЧОК, которое должно быть учтено при моделировании операционного денежного потока (формулу изменения ЧОК можно посмотреть в предыдущем материале, в котором разбиралось построение CF).

Формула прогнозирования кредиторской задолженности (кредиторки):

Особая тема: НДС к уплате. Это тот НДС, что вы начислили на свою выручку — это, по сути, ваша кредиторская задолженность перед бюджетом. При необходимости моделируется как отдельная строка КЗ с отдельными параметрами. Как мы писали выше, очень часто в финансовых моделях НДС игнорируется целиком.

Итак, мы собрали все части Баланса: активы, капитал, обязательства. Теперь важный момент, с которого начинался этот материал. Внизу Баланса либо сразу после описания и учета каждого из блоков, активов и пассивов, необходимы два показателя:

• Итого активы — сумма всех статей из шага 1
• Итого пассивы — сумма всех статей из шага 2 и шага 3

Как вы можете догадаться, нам нужна проверка Активы = Пассивы. Это уравнение Баланса. Проверку можно реализовать, сравнив их между собой или сравнив их разность с нулем, что то же самое.

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

Практический совет для моделирования в Excel: из-за технических особенностей, даже при полностью корректном составлении финансовой модели, проверка Активы = Пассивы может давать сбой; поэтому лучше сравнить разницу Активы – Пассивы с каким-то очень маленьким числом близким к нулю, например, 10^-9 (это одна миллиардная).