Модель расчета маржинального дохода

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

Рисунок 1. Структура модели расчета маржинального дохода

Рисунок 2. Заполненная данными модель расчета маржинального дохода

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

=B4-B4/(1+B5)*(1+B7*B6/365)

Меняя любой из исходных параметров модели (объем продаж, торговую наценку, отсрочку платежа или цену капитала), мы можем наблюдать автоматически пересчитанный Excel результат в строке «Маржинальный доход». Например, при сценариях, которые представлены на рис. 2, более предпочтителен вариант 1 (несмотря на меньший объем продаж, маржинальный доход в этом сценарии выше за счет большей торговой наценки и меньшей отсрочки платежа). Но если мы, например, сократим отсрочку платежа во втором сценарии с 90 до 60 дней, наш выбор сменится на прямо противоположный (см. рис. 3).

Рисунок 3. Скорректированный результат работы модели при смене исходного параметра

Данная модель несколько нетипична, так как соотношение 10 процентов – 80 процентов – 10 процентов в ней не соблюдено: исходные данные занимают 80 процентов модели, результаты – 20 процентов, а промежуточных расчетов нет вовсе. Но это связано лишь с тем, что данная модель крайне проста, в реальной жизни модели, как правило, организованы чуть более сложно.

Модель аннуитетного платежа

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

• сумма процентов по кредиту;
• сумма погашения тела кредита;
• остаток задолженности по кредиту на конец каждого месяца.

При этом последовательность действий выглядит так.

Шаг 1. Рассчитывается сумма процентов.Для этого берется сумма задолженности на конец предыдущего месяца, умножается на годовую процентную ставку (введенную пользователем) и делится на 12. В примере, представленном на рис. 4, сумма процентов за первый месяц (750 000 рублей) рассчитана как сумма кредита (50 000 000 рублей), умноженная на 18 процентов и поделенная на 12, проценты за второй месяц (738 750 рублей) рассчитаны как остаток долга на конец первого месяца (49 250 000 рублей), умноженный на 18 процентов и поделенный на 12, и т.д.

Шаг 2. Рассчитывается сумма погашения тела кредита.Поскольку аннуитетный платеж у нас жестко задан в шапке таблицы (в примере из рис. 4 – это 1 500 000 рублей), то сумма погашения рассчитывается как разница между аннуитетным платежом и величиной начисленных процентов за текущий месяц. Таким образом, погашение кредита в первом месяце у нас составит 750 000 рублей(1 500 000 – 750 000), во второй месяц – 761 250 рублей (1 500 000 – 738 750) и т.д.

Шаг 3. Рассчитывается остаток долга на конец месяца. Это разница между остатком долга на конец предыдущего месяца и суммой погашения тела кредита в текущем месяце. Соответственно, остаток долга на конец первого месяца составит 49 250 000 рублей (50 000 000 – 750 000), на конец второго месяца – 48 488 750 рублей (49 250 000 – 761 250) и т.д.

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

В примере на рис. 4 на стр. 109 горизонт работы модели ограничен тремя годами, хотя совершенно очевидно, что мы можем продлить модель на любой интервал времени, допустим, даже на 50 лет (что может быть актуально, например, для расчета платежей по ипотечным кредитам), просто продублировав любую строку основной части таблицы (кроме первой) необходимое количество раз. В результате получится, что блок промежуточных расчетов составит 50 × 12 × 4 = 2400 ячеек, а доля исходных данных (представленных всего в двух ячейках) составит менее 0,1 процента.

Рисунок 4. Модель расчета аннуитетного платежа по кредиту

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

Например, если нас интересует кредит на три года, очевидно, что ежемесячного платежа в размере 1 500 000 рублей, как показано на рис. 4, недостаточно, поскольку при таком платеже остаток долга на конец третьего года составит 14 543 023 рубля. Увеличим сумму ежемесячного платежа до 1 700 000 рублей, в результате модель автоматически примет вид, представленный на рис. 5. Как видно из этого рисунка, сумма платежа 1 700 000 рублей приближает нас к цели: остаток задолженности на конец третьего года составит всего 5 087 829 рублей, но все равно эта сумма не является окончательной. Правильную сумму платежа можно подобрать экспериментальным путем (вручную занося различные его значения в модель и наблюдая за изменением результата), а можно воспользоваться штатной функцией Excel «Подбор параметра», которая делает то же самое, но значительно быстрее и удобнее. Попробуем воспользоваться ею для решения нашей задачи.

Рисунок 5.Модель расчета платежей по кредиту со скорректированным аннуитетным платежом

Вызовем команду меню «Данные» – «Анализ “что если”» – «Подбор параметра». Откроется такое диалоговое окно, которое показано на рис. 6. Поля этого окна следует заполнить следующим образом.

Рисунок 6. Диалоговое окно «Подбор параметра»

1. В поле «Установить в ячейке»: вводим адрес ячейки (или просто можно поставить в это поле курсор, после чего кликнуть по требуемой ячейке мышкой), которая должна быть обнулена. К примеру, если мы рассматриваем вариант кредита на три года, то сумма аннуитета должна быть такой, чтобы остаток долга по кредиту на конец третьего года был равен нулю. В этом случае в данное поле нужно ввести адрес ячейки F44. Если же нас интересует кредит на другой срок, соответственно в эту ячейку вводится адрес той ячейки, которая соответствует долгу по кредиту на конец другого года.

2. В поле «Значение»: вводится значение «0», так как мы ищем величину аннуитетного платежа, при котором кредит полностью погасится в заданный срок.

3. В поле «Изменяя значение ячейки»:задаем адрес ячейки, в которой хранится величина аннуитетного платежа. В нашем случае это ячейка D5. Именно эту ячейку Excel будет корректировать, чтобы получить ноль в конце третьего года.

4. Нажимаем кнопку «ОК». Excel произведет некоторые расчеты, после чего выведет информацию, которая показана на рис. 7. Данное сообщение означает, что наши поиски увенчались успехом: в результате нескольких итераций Excel удалось подобрать такое значение аннуитетного платежа, при котором сумма долга на конец третьего года составила 0 рублей.

Рисунок 7. Результат работы функции «Подбор параметра»

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

Рисунок 8. Результат работы модели расчета аннуитетного платежа

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

Бюджетная модель компании

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

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

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

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

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

Каждая из этих подмоделей будет формироваться на отдельном листе. Результаты работы модели будут выводиться на листы PL, CF и BS (отчет о прибылях и убытках, отчет о движении денежных средств и балансовый отчет соответственно), а также на лист «Свод», где будет появляться «выжимка» из всех форм отчетности, при необходимости украшенная графиками и иными средствами визуализации и облегчения восприятия информации.

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

Модель продаж

Создадим в нашей книге лист «Модель продаж» и заполним его так, как показано на рис. 1. Модель продаж идет в бюджетной модели первой, так как в ООО «Ромашка», как и в подавляющем большинстве реальных компаний, узким местом, ограничивающим возможности по росту бизнеса, является объем продаж. А узкие места должны идти первыми, так как все остальные показатели работы вытекают из них и рассчитываются на их основе.

Рисунок 1. Модель продаж

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

Итак, как видно из рис. 1, модель продаж представляет собой несколько смысловых блоков, самый первый из которых состоит из натуральных показателей (то есть измеренных не в рублях, а в неденежных единицах измерения).

«Суммарный трафик в сети» – первый показатель этого блока. В нем отображается количество показов страниц с рекламой посетителям интернета. Для того чтобы продавать рекламу на веб-страницах, необходимо, чтобы на эти страницы заходили пользователи, и количество этих пользователей является ограничителем объема продаж компании. В нашем случае мы знаем, что в 2015 году трафик составит 120 млн показов, а далее он будет расти по мере увеличения количества сайтов в нашей сети и количества посетителей этих сайтов. Ключевая задача – спрогнозировать этот трафик на весь 10-летний горизонт планирования. Это можно сделать, просто задав значения трафика на каждый год, но правильнее рассчитать его умножением трафика предыдущего года на некий коэффициент роста. Значение этого коэффициента прописано в показателе роста к прошлому году, который в нашей модели установлен на уровне 30 процентов в 2016 и 2017 годах и далее плавно снижается до 10 процентов в 2024 году. Очень важно правильно спрогнозировать этот рост, так как слишком высокие или слишком низкие темпы роста приведут к существенным перекосам в прогнозе продаж и снизят достоверность бюджетной модели.

Таким образом, значение показателя «Суммарный трафик в сети» на 2015 год задано в абсолютной величине (120 млн показов), а на каждый последующий год рассчитано как произведение трафика на предыдущий год и выражения (1 + рост к предыдущему году). В результате величина трафика за 10 лет должна вырасти в 5,5 раза до 663 802 425 показов.

«Процент продаваемого трафика» – следующий важный показатель. Далеко не на весь трафик, который генерируют клиенты компании, есть покупатели (рекламодатели, заинтересованные в показе своей рекламы именно данной аудитории). Поэтому процент трафика, который будет использован для показа рекламы – очень значимый показатель эффективности операционной деятельности, и в нашей модели он задан на уровне 50 процентов в 2015 году с ростом на 2 процентных пункта в каждом последующем году. Тут, как мы видим, рост не задан отдельным показателем, как в случае с «Суммарным трафиком в сети», поскольку это показатель эффективности компании, а не объема рынка.

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

«Доля продуктов в общем трафике». В этом блоке модели продаж общий планируемый трафик разбивается на трафик по каждому из продуктов компании, которые условно названы Продукт 1, Продукт 2, Продукт 3 и т.д. Под продуктами в данном контексте подразумеваются различные виды рекламных баннеров, которые могут быть показаны на сайте (TopLine, PopUnder, Rich-Media и т.д.). Поскольку каждый из видов баннеров продается по своей цене, важно определить физический объем трафика каждого вида баннеров, чтобы затем, умножив его на цену одного показа, определить величину выручки в денежном выражении.

Кстати, обратите внимание на то, что доля различных продуктов в общем трафике меняется с течением времени: так, доля Продукта 1 снижается с 60 процентов в 2015 году до 5 процентов в 2024 году, а, к примеру, доля Продукта 5 растет с нуля до 30 процентов. Таким образом, в модели продаж важно не просто заложить рост продаж, но и корректно прописать их структуру, так как какие-то морально устаревшие продукты могут постепенно терять свою долю, а новые – увеличивать.

«Объем продаж продуктов»является в чистом виде расчетным блоком: его показатели вычисляются путем умножения величины продаваемого трафика на долю каждого продукта в этом трафике. Очевидно, что в данном блоке объем продаж также указан в натуральном выражении.

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

Блоки «Объем продуктов в общем трафике», «Цена продуктов» предназначены для определения будущей финансовой и продуктовой политики компании. Именно поэтому следует отнестись к их заполнению максимально серьезно.

«Выручка».Этот блок определяет объем продаж каждого из продуктов в денежном выражении. Показатели определяются простым умножением физического объема продаж каждого продукта (блок «Объем продаж продуктов») на его цену (блок «Цена продуктов»). Далее выручка по всем продуктам суммируется и результат составляет 313,8 млн рублей в 2015 году с последующим ростом до 2,4 млрд рублей в 2024 году.

На этом задачу модели продаж можно было бы считать выполненной (так как объем продаж в результате работы модели мы получили), если бы не один нюанс: поскольку отчетные формы бюджетной модели представлены в помесячной разбивке, все подмодели должны давать результат в такой же разбивке. Соответственно, имеющиеся у нас данные о выручке в 2015–2024 годах необходимо детализировать до каждого месяца данного периода.

Годовые показатели продаж можно разбить на месяцы с помощью коэффициентов сезонности (это может быть актуально, поскольку интернет-реклама, как и многие другие отрасли, испытывает весьма серьезные сезонные колебания внутри календарного года). Добавим на лист «Модель продаж» такую таблицу, которая изображена на рис. 2. (Обратите внимание! На рис. 2 и далее многоточия в крайнем правом столбце означают, что данная таблица продолжается вправо до конца прогнозного периода, а именно декабря 2024 года. Формат книги не позволяет корректно отображать такие широкие таблицы, впрочем, в этом нет необходимости.)

Как мы видим из рис. 2, сезонность прописана в виде доли продаж каждого календарного месяца в годовом объеме. Самые слабые месяцы в нашем ООО «Ромашка» – январь и февраль, когда продажи составляют всего 6 процентов от годового объема, самые сильные – октябрь, ноябрь и декабрь, в которых продажи примерно в два раза выше.

Рисунок 2. Сезонность продаж

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

Рисунок 3. План продаж в помесячной разбивке