Как построить Баланс (Balance Sheet) финансовой модели

Как построить финансовую модель (часть 3): разбираем, из чего состоит Баланс (он же Balance Sheet, BS), и как собрать все части модели воедино. Моделируем активы, пассивы и строим связь с P&L и ДДС. В конце вас ждет тренажер.
Привет! Это третья из трех базовых и связанных друг с другом тем, посвященных построению финансовой модели:
1. Как построить P&L (ОФР) финансовой модели.
2. Как построить ДДС (Cash Flow) финансовой модели.
3. Как построить Баланс (Balance Sheet) финансовой модели.

В предыдущих двух темах мы разобрали и построили в тренажере P&L и ДДС. Мы поняли, как моделировать доходы, расходы и денежные потоки. Для полной картины не хватает последнего отчета. Пришло время собрать все воедино и построить Баланс (он же Balance Sheet, BS, Форма 1).

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

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

Закон №1 при моделировании Баланса

Прежде чем мы двинемся дальше, вспомним главное правило Баланса, которое обеспечивает связь форм и корректность всего учета, и которого нам нужно достичь в модели. Оно должно соблюдаться всегда и без исключений:
УРАВНЕНИЕ БАЛАНСА
Активы = Обязательства + Капитал
Это уравнение — наша главная проверка, при построении Баланса в финансовой модели. Если оно сходится при любых вводных параметрах и расчетах в модели, значит, наша модель построена корректно. Если нет — где-то закралась ошибка.

Шаг 1: собираем Активы — всё, чем владеет компания

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

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

1. Основные средства (ОС) / Property, Plant & Equipment (PP&E)

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

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

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

Подход 1: сокращенный (одной строкой).
Это самый быстрый способ. В Балансе создается всего одна строка — «Основные средства» (PP&E). Ее изменение от периода к периоду рассчитывается так:
Изменение ОС
ОС (конец) = ОС (начало) + CAPEX – Амортизация за период
Этот метод удобен для быстрых, высокоуровневых моделей. Однако, он менее прозрачен и усложняет аудит, особенно если у компании есть регулярные продажи активов, так как их выбытие из Баланса учесть в такой формуле непросто. Формула в общем случае справедлива и для расширенного метода (ниже), но в модифицированном виде, состоящем из нескольких частей.

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

2. Отложенный налоговый актив (ОНА) / Deferred Tax

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

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

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

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

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

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

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

3. Запасы (Inventory)

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

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

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

Итак, формула, которую можно использовать для расчета запасов в Балансе:
Запасы
Запасы = Себестоимость (COGS) за период × Срок оборачиваемости запасов / Длительность периода
В этой формуле вам известны все элементы, кроме срока оборачиваемости, который можно либо (а) рассчитать, проанализировав фактическую отчетности компании, либо (б) предположить экспертно — сделать разумное допущение, заложив это в предпосылки сценария модели.

4. Дебиторская задолженность (ДЗ) / Accounts Receivable (AR)

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

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

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

Итак, формула, которую можно использовать для расчета ДЗ в Балансе:
ДЕБИтОРКА
ДЗ = Выручка за период × Срок оборачиваемости ДЗ / Длительность периода
Выше приведена формула для ДЗ покупателей. Если вам нужно смоделировать ДЗ поставщиков (авансы), то привязка должна идти не к выручке за период, а к себестоимости за период.

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

5. Денежные средства (Cash)

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

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

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

Шаг 2: собираем Обязательства и Капитал — за чей счет всё это куплено

Время собрать правую часть уравнения. Она показывает источники финансирования наших активов. Если помните, ранее мы коротко затрагивали, что финансирование любой компании осуществляется за счет заемного капитала (Обязательства, Liabilities) и собственного капитала (Капитал, Equity). Сейчас мы разберем эти составляющие и подходы к их моделированию.

1. Капитал (Equity)

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

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

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

2. Обязательства (Liabilities)

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

Кредиты и займы (Debt). Это долги, как правило, перед банками или, в ином случае, перед другими заимодавцами. Логика моделирования этой статьи схожа с ОС и нераспределенной прибылью:
ДОЛГ
Долг на конец = Долг на начало + Новые кредиты – Погашение тела долга
«Тело долга» или «тело кредита» — это разговорные термины, поскольку в кредитных договорах всегда используется формулировка «основной долг». Но мы можем их использовать для наглядности и лучшего понимания.

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

Кредиты и займы связаны с P&L через проценты. Строго говоря, моделируя проценты к уплате (Interest Expense) в P&L, вам нужно опираться на состояние кредитов и займов в Балансе. Образцовая формула для расчета процентов в данном случае выглядит так:
ПРОЦЕНТЫ в P&L
Проценты к уплате [в P&L] = (Кредиты и займы на начало периода + Кредиты и займы на конец периода) / 2 × Ставка кредитования × Длина отчетного периода [в днях] / 365
Эта формула может приводить к появлению циклических ссылок. Как с ними бороться, мы коротко рассматривали в теме построения P&L.

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

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

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

Для КЗ справедливы все те же связи и способы моделирования, что и для ДЗ. Рост или сокращение КЗ ведет к изменению ЧОК, которое должно быть учтено при моделировании операционного денежного потока (формулу изменения ЧОК можно посмотреть в предыдущем материале, в котором разбиралось построение CF).
Кредиторка
КЗ = Себестоимость (COGS) за период × Срок оборачиваемости КЗ / Длительность периода
Особая тема: НДС (часть 2) — НДС к уплате. Это тот НДС, что вы начислили на свою выручку — это, по сути, ваша кредиторская задолженность перед бюджетом. При необходимости моделируется как отдельная строка КЗ с отдельными параметрами. Как мы писали выше, очень часто в финансовых моделях НДС игнорируется целиком.

Шаг 3: финальная проверка — сходится или нет?

Итак, мы собрали все части Баланса: активы, капитал, обязательства. Теперь самый важный момент, с которого начинался этот материал. Внизу Баланса (либо сразу после описания и учета каждого из блоков, активов и пассивов) необходимы два показателя:
  • Итого Активы (сумма всех статей из Шага 1)
  • Итого Пассивы (сумма всех статей из Шага 2)
Как вы можете догадаться, нам нужна проверка: Активы = Пассивы. Это можно реализовать сравнив их между собой или сравнив их разность с нулем (это то же самое).

Если результат сравнения удачный — мы вас поздравляем! Ваша финансовая модель работает, Баланс сходится и все связи, вероятно, настроены корректно.
Практический совет: почему Баланс может не сходиться?
Если проверка не успешна, не паникуйте. 99% ошибок — это простые механические неточности:
  • Забыли знак «минус»: амортизация, дивиденды, приобретение ОС — проверьте формулы и еще раз убедитесь, что вы корректно учли знаки и направления операций.
  • Неправильная ссылка: убедитесь, что все связи с P&L и ДДС (чистая прибыль, CAPEX, амортизация, изменение долга и т.д.) ведут в правильные ячейки.
  • Ошибка в переносах: проверьте, что «остаток на начало периода» в одной колонке равен «остатку на конец периода» в предыдущей.
  • Ошибка в суммировании: проверьте, что в итоговых строках P&L, ДДС, Баланса вы нигде не забыли слагаемое и не посчитали ни одно слагаемое дважды.
  • Циклическая ссылка: убедитесь, что нигде в модели у вас нет циклических ссылок, а если есть, то включены итеративные вычисления в настройках табачного редактора.
Поздравляем! Теперь вы не просто знаете, как строится ядро финансовой модели, вы знакомы со всеми тремя формами финансовой отчетности, понимаете глубинную взаимосвязь трех форм отчетности. Вы видите, как решение о покупке нового оборудования (CAPEX) влияет на P&L (через амортизацию), на ДДС (через отток денег) и на Баланс (через рост ОС и снижение остатка денежных средств), а также как неоплаченные долги клиентов (ДЗ) негативно сказываются на возможностях компании не только платить по своим счетам, но и обслуживать долг, реализовывать программу инвестиций, а иногда вынуждают залезать в кредиты, тем самым зажимая рентабельность бизнеса.

Тренажер (практическое задание)

Как работать с тренажерами?
Тренажеры: в конце материалов расположены практические тренажеры, в каждом из которых вам предлагается выполнить одно или несколько заданий (добавить формулы, связи, перенести условия в модель, рассчитать показатель, расширить модель за счет новых строк или столбцов и т. п.). Условия задания описаны внутри тренажера и иногда дополнительно перед ним на странице материала; вам необходимо внимательно ознакомиться с условиями, чтобы корректно реализовать их. В тренажерах есть подсказки, которые помогут вам справиться с заданием. Чтобы воспользоваться тренажером, вам нужно его скачать или скопировать себе в облако (Google Drive, Яндекс Диск, OneDrive, Битрикс).

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

Тренировки: цель тренировок — научиться уверенно реализовывать заданные условия, писать формулы, строить таблицы модели, выстраивать связи. Если тренажер кажется вам простым, можете пропустить его. Тренироваться можете локально в Excel или Р-7 или онлайн в Google Sheets, Яндекс Документах, Excel Online или Битриксе (онлайн-редакторы таблиц требуют наличия профиля на соответствующем ресурсе).

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

Тренируем составление BS на базе P&L и CF по шаблону и с нуля на примере бизнесов кофейни и барбершопа.
Открыть тренажер