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

Эта глава охватывает следующие темы:

• Понимание концепций BASE и "штопора" (corkscrew)
• Подходы к моделированию активов
• График активов
• График долгов
• Создание графика амортизации кредита

Это общие стандарты, которым следует следовать при моделировании статей нашего балансового отчета.
BASE — это акроним, который означает:
Начальный остаток (Beginning) плюс Поступления (Additions) минус Выбытия (Subtractions) равно Конечный остаток (Ending). Концепция "штопора" относится к тому, как структура BASE связана от одного периода к другому.

На следующем скриншоте мы увидим, что конечный остаток одного года переносится как начальный остаток следующего года:

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

Краткое напоминание нашей повестки дня:

• Запись исторических отчетов о прибылях и убытках и балансового отчета
• Расчет исторических драйверов роста
• Прогнозирование драйверов роста для отчетов о прибылях и убытках и балансового отчета
• Построение прогнозируемых отчетов о прибылях и убытках и балансового отчета
• Подготовка графика активов и амортизации
• Подготовка графика долгов
• Подготовка отчета о движении денежных средств
• Анализ коэффициентов
• Оценка по методу DCF
• Другие методы оценки
• Анализ сценариев

Наш предмет обсуждения называется долгосрочными активами, основными средствами и основными фондами, зданиями и оборудованием. Актив является долгосрочным активом, от которого компания будет получать экономическую выгоду, используя его в течение периода более одного года. Этот период называется сроком полезного использования актива. Было бы несправедливо списывать всю стоимость такого актива в периоде его приобретения; вместо этого стоимость должна быть распределена на срок полезного использования актива.

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

Это называется остаточной стоимостью (net book value).

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

Амортизация с использованием SLM рассчитывается следующим образом:

или

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

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

Следующий скриншот показывает разницу между линейным методом и методом уменьшаемого остатка:

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

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

Следующий скриншот показывает годовую амортизацию для актива с ликвидационной
стоимостью 1 000:

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

Хотя SLM и метод уменьшаемого остатка являются двумя наиболее распространенными методами амортизации, существуют и другие методы, такие как метод суммы лет и метод единиц продукции.

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

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

Первая диаграмма показывает, что происходит, когда вы получаете прибыль от выбытия активов:

Это означает, что вы продали актив за сумму, превышающую его стоимость по бухгалтерским книгам.

Вторая диаграмма показывает, что происходит, когда вы несете убыток от выбытия активов:

В этом случае вы продали актив дешевле его стоимости по бухгалтерским книгам, что привело к убытку от выбытия активов.

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

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

Первый раздел содержит следующую информацию:

Здесь у нас есть несколько ключевых слов, таких как Метод амортизации и Срок службы актива.
Мы рассмотрим, что они означают для этого конкретного графика:

• Метод амортизации: В нашем случае это SLM (линейный метод).

• Срок службы актива: Используется для представления срока полезного использования актива, который в нашем случае составляет 10 лет.

• Выбытие активов: Этот раздел предназначен для выручки от продажи основных средств, если таковая имеется.

• Капитальные затраты (Capex): Эта строка показывает, сколько было — и прогнозируется — потрачено на основные средства в каждом году.

Следующий раздел — График амортизации:

Амортизация основных средств находится в строках 11-20 и по столбцам E-N. Годовая амортизация по поступлениям основных средств равна капитальным затратам года, деленным на срок службы актива. Для Yr1A это E8, деленное на E5, что равно 10 000, как показано на предыдущем скриншоте.

Начисление амортизации для поступлений Yr1A начинается в E11 и продолжается по строке 11 по 10 000 каждый год в течение еще 9 лет, как показано на следующем скриншоте:

Амортизация по поступлениям в Yr2A рассчитывается как F8, деленное на F5, и будет начисляться ежегодно в течение 10 лет по строке 12 — следующей строке вниз — начиная с F12. Таким же образом, амортизация по поступлениям Yr3A начнется с G13 и будет начисляться в течение 10 лет по строке 13.

Общая сумма амортизации за каждый год — это сумма всей амортизации в строках 11-20 столбца для этого года. Для Yr1A это будет сумма всей амортизации в строках 11–20 столбца E; для Yr2A это будет сумма всей амортизации в строках 11-20 столбца F.

В нашем примере только Yr1A и Yr4A имеют поступления капитальных затрат в течение года,
как видно на следующем скриншоте:

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

Следующий скриншот показывает Стоимость, Накопленную амортизацию и Остаточную стоимость:

Накопленная амортизация — это общая сумма амортизации, начисленной на текущую дату по основным средствам. Основные средства отражаются в балансовом отчете по их остаточной стоимости, которая равна стоимости за вычетом накопленной амортизации.

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

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

Следующий скриншот показывает расчет поля Коэффициент оборачиваемости основных средств:

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

Следующий скриншот иллюстрирует расчет среднего значения исторических коэффициентов оборачиваемости основных средств:

Амортизацию можно рассчитать следующим образом:

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

Затем вы рассчитываете среднее значение сроков полезного использования за последние
пять исторических лет:

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

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

Следующий список показывает нашу текущую повестку дня:

• Запись исторических отчетов о прибылях и убытках и балансового отчета
• Расчет исторических драйверов роста
• Прогнозирование драйверов роста для отчетов о прибылях и убытках и балансового отчета
• Построение прогнозируемых отчетов о прибылях и убытках и балансового отчета
• Подготовка графика активов и амортизации
• Подготовка графика долгов
• Подготовка отчета о движении денежных средств
• Анализ коэффициентов
• Оценка по методу DCF
• Другие методы оценки
• Анализ сценариев

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

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

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

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

Следующий скриншот представляет график долгов:

Мы готовим график долгов, используя макеты BASE и "штопора".

В нашем примере был взят кредит в размере 40 млн рублей в Yr1A под 10% годовых, подлежащий погашению в течение 8 лет; и кредит в размере 250 млн рублей, взятый в Yr4A, также под 10% годовых, подлежащий погашению в течение 10 лет. Кредит в 40 млн рублей погашается в течение 8 лет, с Yr2A по Yr9E. Погашение кредита в 250 млн рублей не начинается до Yr5A и будет продолжаться еще 9 лет.

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

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

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

Следующий скриншот показывает график долгов с конечным остатком, включающим начисленные проценты:

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

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

Следующий скриншот показывает, как Р7 Офис помечает циклическую ссылку:

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

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

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

В Р7 Офис (или аналогичном офисном пакете) перейдите в Файл > Параметры (или Инструменты > Параметры). Откроется диалоговое окно параметров. Затем перейдите на вкладку Расчет (или Формулы) и установите флажок Включить итеративные вычисления. Примите максимальное количество итераций по умолчанию, равное 100. Это означает, что Р7 Офис считает, что после 100 итераций разница, генерируемая каждой итерацией, становится незначительной или несущественной.

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

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

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

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

Как только мы обновим наш балансовый отчет и отчет о прибылях и убытках нашими расчетами, единственной недостающей статьей, необходимой для завершения модели 3-х отчетов, будут денежные средства.

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

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

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

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

Первое, что нужно сделать, это создать шаблон для расчета кредита.

Мы сделаем это, сначала создав таблицу два на четыре для ввода переменных кредита, следующим образом:

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

Для этого мы сначала создадим таблицу, содержащую 4 значения — 24, 12, 4 и 1, как показано здесь:

Теперь нам нужно преобразовать эту таблицу в список.

Мы сделаем это, перейдя на вкладку Формулы и выбрав опцию Определить имя (или Диспетчер имен), как показано здесь:

Во всплывающем окне, которое появится, мы определим имя списка, которое я ввел как Tenure.
В поле Ссылается на: мы введем массив ячеек, где находится таблица, который в данном случае — $J$2:$J$5:

Во всплывающем окне, которое появится, откройте раскрывающееся меню Разрешить: и выберите Список,
как показано здесь:

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

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

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

Таким образом, мы теперь создали шаблон, где можем реализовать наш график кредита, как показано на следующем скриншоте:

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

Теперь, когда наш шаблон готов, пришло время применить магию Р7 Офис и создать формулы для расчета различных переменных, являющихся частью кредита, таких как основная сумма, сумма процентов, общая сумма платежа и остаток суммы после платежа. Если мы попытаемся ввести фактические формулы, которые мы использовали для расчета каждого значения, формулы станут слишком сложными для нас. К счастью, Р7 Офис упростил нам расчет таких значений с помощью встроенных функций, таких как ПЛТ (PMT), ОСНПЛТ (PPMT) и ПРОЦПЛТ (IPMT).

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

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

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

• В нашем случае формула ПЛТ должна быть следующей:

=ПЛТ(Годовая процентная ставка/Платежей в год; Лет*Платежей в год; Сумма)

CopyExplain

Мы заменим эти переменные фактическими значениями — с помощью ссылок на ячейки — так что наша окончательная формула будет следующей:

=ПЛТ($C$2/$C$4;$C$3*$C$4;$C$5)

CopyExplain

• Теперь, когда первая формула на месте, мы рассчитаем основную сумму с помощью функции ОСНПЛТ, которая принимает следующие аргументы:

Эта формула похожа на формулу ПЛТ, которую мы видели ранее; единственное отличие в том, что мы учитываем номер текущего платежа, который является переменной `период`.
Таким образом, наша формула для основной суммы будет следующей:

=ОСНПЛТ($C$2/$C$4;$A8;$C$3*$C$4;$C$5)

CopyExplain

• Следующая формула, которую нам нужно добавить, — это формула для расчета суммы процентов.
• Мы будем использовать для этого функцию ПРОЦПЛТ, которая принимает следующие аргументы:

Эта формула является точной копией формулы ОСНПЛТ, за исключением того, что мы используем функцию ПРОЦПЛТ вместо ОСНПЛТ:

=ПРОЦПЛТ($C$2/$C$4;$A8;$C$3*$C$4;$C$5)

CopyExplain

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

Предположим, к вам обратился клиент и запросил кредит в размере 10 000 долларов США на срок 2 года. В соответствии с политикой компании вы должны взимать с него годовую процентную ставку 5%. Кроме того, клиент хочет выбрать ежемесячные платежи.

Давайте рассчитаем для него кредит, выполнив следующие шаги:

• Мы введем все переменные, которые запросил клиент, как показано на следующем скриншоте:

Красные значения указывают на вычеты, которые будут вычтены из исходных сумм
(Примечание: Функции ПЛТ, ОСНПЛТ, ПРОЦПЛТ обычно возвращают отрицательные значения для платежей).

• Теперь мы выберем диапазон A8 – E8 и перетащим маркер заполнения вниз на одну строку, чтобы заполнить второй столбец. Это приводит к следующему результату:

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

• Нам нужно изменить формулу в столбце Остаток так, чтобы первая ссылка указывала на E8, где находится начальный остаток. Убедитесь, что вы не блокируете столбец при этом. Наша формула для второго поля остатка теперь следующая (предполагая, что ОСНПЛТ возвращает отрицательное значение):

=E8+C9
CopyExplain

• Это приводит к правильному результату, как показано здесь:

Мы видим, что последний остаток равен $0.00. Это означает, что наш кредит полностью погашен!

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

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

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