Готовое решение

ДОПОЛНИТЕЛЬНЫЙ МАТЕРИАЛ
Файл Excel с автоматизированным расчетом бета-коэффициента, WACC и EVA

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

рисунок 1. Оценка бета-коэффициента

Кстати, в авторском шаблоне определяется не только значение бета-коэффициента, но и рассчитывается стоимость собственного капитала, WACC и EVA (см. рис. 1 и 2).

В предлагаемой модели показателем динамики рынка служит индекс РТС. Он обновляется автоматически с сайта биржи при открытии файла (разумеется, на компьютере, с которого вы будете работать с файлом Excel, должен быть доступ в интернет). Автоматически загружаются и сведения о доходности облигаций Центробанка.

рисунок 2. Отчет о затратах на капитал (WACC)

Они нужны для определении безрисковой ставки доходности. Чтобы все обновления проходили без проблем, не забудьте сразу после открытия файла разрешить подключение к данным в окне «Оповещение системы безопасности». Листы с данными по динамике индекса РТС и доходности облигаций («index» и «cbr» соответственно) править не нужно, для их заполнения используется инструмент Excel «Импорт данных с веб-страницы»**. Чтобы вы не запутались, ярлыки этих листов в книге Excel выделены красным.

И все же некоторую исходную информацию придется вводить руками. Речь идет о данных из баланса и отчета о прибылях и убытках. Заполнять придется только один лист в книге Excel, который озаглавлен «input» (от англ. «ввод») (см. рис. 3). Обратите внимание, что дата в таблице отображается в формате «месяц.год», но при этом заполняется полностью «день.месяц.год», причем на первое число. Например, если данные – за ноябрь 2009 года, то указывается «01.11.2009». Это обязательное условие, иначе не получится корректно сопоставить рентабельность собственного капитала и индекс РТС (его дата загружается с сайта именно в таком формате).

рисунок 3. Ввод исходных данных

И несколько правил заполнения исходной таблицы. Под прибылью от продаж понимается операционная прибыль от основной деятельности или прибыль до вычета процентов и налогов (EBIT). Она берется из отчета о прибылях и убытках (прибыль от реализации) или как итоговый оборот по счетам 90 (дебетовый – убыток, кредитовый – прибыль). Собственный капитал – итоги третьего раздела баланса, все средства, инвестированные в компанию собственниками, акционерами, инвесторами, имеющими долевое участие. Сюда же прибавляются займы, полученные от собственников и отраженные в обязательствах.

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

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

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

Для расчета безрисковой ставки используется формула СРЗНАЧЕСЛИ. Дело в том, что ставки по облигациям могут меняться несколько раз в месяц. А значит, необходимо брать их среднее значение за определенный месяц. Для преобразования дат в тот же формат, который используется в случае с РТС, на листе «cbr» добавлен вспомогательный столбец.

Последнее поле в исходной таблице – бета-коэффициент. Его значение рассчитывается на листе «beta_est» с помощью функции Excel НАКЛОН. Оно же отображается в уравнении на графике корреляций, где по вертикали отображены изменения ROE компании, по горизонтали – индекса РТС.

На листе «db» приведены промежуточные расчеты по месяцам, в том числе стоимости собственного капитала, средневзвешенные затраты на капитал компании (WACC), рентабельности инвестированного капитала (ROIC) и показатель EVA.

Все итоговые показатели на последний отчетный месяц сведены в отчете о затратах на капитал (лист «Report») в трех таблицах. Первая отражает расчет стоимости собственного капитала. Вторая – средневзвешенной стоимости (затрат) на капитал. Третья – EVA. Для наглядности таблицы дополнены графиками, на которых отображаются рассчитанные показатели в динамике.