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

В этой главе мы рассмотрим следующие темы:

• Включение встроенных тестов и процедур
• Устранение неполадок
• Понимание анализа чувствительности
• Использование прямых и косвенных методов
• Понимание анализа сценариев
• Создание простой модели имитации Монте-Карло

Финансовая модель по своей природе полна формул и расчетов. Хотя большинство из них просты, их объем и повторяемость создают риск ошибок, которые могут вызвать кошмары у самого опытного модельера при попытке их отследить.

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

• Жестко закодированные ячейки:

Их следует выделять синим шрифтом.

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

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

• Проверка баланса:

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

• Чтобы балансовый отчет был сбалансирован, Общие активы минус Текущие обязательства должны равняться Общему собственному капиталу и Долгосрочным обязательствам. Если существует разница из-за округления, которая не отображается, это все равно вызовет предупреждение о несбалансированности. Вот почему следует использовать функцию ОКРУГЛ (ROUND), чтобы убедиться, что Р7 Офис игнорирует десятичные знаки при сравнении двух итогов.

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

• Денежные средства и их эквиваленты:

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

• Вводите значения только один раз:

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

• Используйте одну формулу на строку:

Используйте систему ссылок Р7 Офис, которая объясняется в Главе 4, «Применение системы ссылок в Р7 Офис», чтобы ввести формулу один раз и заполнить той же формулой всю строку вправо для остальных лет. Если все сделано правильно, это значительно сократит время моделирования и уменьшит количество ошибок.

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

• Влияющие ячейки (Precedents):

Влияющие ячейки — это те ячейки, на которые ссылались при получении значения в конкретной ячейке.

• Зависимые ячейки (Dependents):

Зависимые ячейки — это те ячейки, которые включили рассматриваемую ячейку в свою формулу.

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

Из предыдущего скриншота рассмотрим ячейку K8. Формула в этой ячейке =K6*(1-$D$7), поэтому ячейки K6 и D7 являются влияющими ячейками для ячейки K8. С другой стороны, ячейка K8 является зависимой ячейкой как для ячейки K6, так и для D7.

На вкладке "Формулы", в группе "Зависимости формул", выбор "Влияющие ячейки" или "Зависимые ячейки" показывает тонкие синие стрелки, связывающие ячейку либо с ее влияющими, либо с зависимыми ячейками соответственно.

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

Мы видим, что ячейка D7 имеет несколько зависимых ячеек. Однако, поскольку это жестко закодированное значение, у нее нет влияющих ячеек. Если в какой-либо ячейке возникает проблема, отслеживание влияющих
и/или зависимых ячеек может выявить ошибочные ссылки, которые могли вызвать аномалию.

Показать формулы:
Существует полезное сочетание клавиш, Ctrl + ` (апостроф), которое позволяет переключаться между отображением формул во всех соответствующих ячейках листа и отображением их значений.
Однократное нажатие Ctrl + ` отображает все формулы на листе, как показано на следующем скриншоте:

Повторное нажатие Ctrl+` возвращает отображение к значениям, как показано на следующем скриншоте:

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

• Вычислить формулу:

Существует порядок операций, который определяет порядок выполнения операндов (+, -, x, \, ^) в Р7 Офис — скобки, затем возведение в степень, затем умножение, деление (в зависимости от того, что идет первым, слева направо), а затем сложение и вычитание (в зависимости от того, что идет первым, слева направо). Когда вам приходится писать сложные формулы с несколькими операндами, следующий порядок операций помогает гарантировать правильное выполнение формулы. Неизбежно будут случаи, когда вы неправильно составите формулу и нарушите порядок, что приведет к ошибочному ответу.

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

Например, формула для расчета терминальной стоимости следующая:

Эта формула, введенная в ячейку Р7 Офис, становится следующей:

=N12*(1+D23)/(D21-D23)

CopyExplain

Выделив ячейку с терминальной стоимостью, перейдите к "Вычислить формулу" (Вкладка "Формулы" > группа "Зависимости формул" > "Вычислить формулу"). Откроется диалоговое окно "Вычисление формулы",
как показано на следующем скриншоте:

Диалоговое окно "Вычисление формулы" отображает ячейку с вычисляемой формулой (в данном случае N25), саму формулу, которая воспроизводится в поле "Вычисление", и четыре кнопки внизу диалогового окна: Вычислить, Шаг с заходом, Шаг с выходом и Закрыть. В поле "Вычисление" следующий вычисляемый элемент подчеркнут. Это может быть операция (+, -, x, \, ^), преобразование ссылки на ячейку в значение в этой ячейке или удаление скобок из части уравнения.

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

В формуле в N25 первым шагом для Р7 Офис является преобразование первой ссылки, N12 (FCFF), в значение в этой ячейке. Соответственно, на предыдущем скриншоте ссылка N12 подчеркнута.

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

Ссылка N12 теперь заменена ее значением. Обратите внимание, как Р7 Офис не показывает сокращенную сумму, 87 232, которая является результатом округления через форматирование, а полную цифру до 10 десятичных знаков.

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

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

Ссылка D23 теперь заменена ее значением, 0,05 (5%), и линия теперь находится под содержимым первого набора скобок, означая, что сложение, хотя оно и имеет более низкий ранг, будет выполнено следующим, чтобы дать (1,05):

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

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

Затем ссылка на ячейку D21 будет преобразована в ее значение:

Теперь D23 будет заменена ее значением, отображаемым в ячейке:

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

Сначала вычисляется вычитание:

Затем скобки удаляются, вводя цифру внутри них в формулу:

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

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

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

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

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

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

Существует два метода анализа чувствительности:
прямой и косвенный.

Оба метода используют таблицы данных, которые можно найти в разделе "Анализ «что-если»" в группе "Прогноз" на вкладке "Данные" в Р7 Офис. Ниже приведен скриншот расположения опции "Таблица данных...":

Чтобы использовать таблицу данных, вы должны структурировать свои данные определенным образом.

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

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

Значения входных данных для строк вводятся в верхней строке таблицы. Мы выбрали ТЕМП РОСТА В ПОСТПРОГНОЗНЫЙ ПЕРИОД для входных данных строки со значениями от 3% с шагом 1% до 7%. Входные данные для столбца — РОСТ ВЫРУЧКИ (CAGR) и вводятся в крайнем левом столбце таблицы со значениями от 0% с шагом 2,5% до 9,5%.

Однако значение 5% изменено на 4,5%, чтобы соответствовать фактическому историческому CAGR, используемому в качестве драйвера роста оборота. При подготовке к функции таблицы данных выберите всю таблицу, начиная с ячейки с целевым значением в верхнем левом углу, исключая значения входных данных/драйверов. Затем выберите "Таблица данных..." из меню "Анализ «что-если»" группы "Прогноз" на вкладке "Данные". Откроется диалоговое окно "Таблица данных", как показано на следующем скриншоте:

Диалоговое окно "Таблица данных" имеет два поля ввода:

"Подставлять значения по строкам в" и "Подставлять значения по столбцам в".

В этом методе ячейки входных данных для строк и столбцов напрямую связаны с моделью через ячейки, в которых они появляются в модели. В данном случае ячейка входных данных для строк — это ячейка ТЕМП РОСТА В ПОСТПРОГНОЗНЫЙ ПЕРИОД в разделе "Оценка", которая связана с вашей моделью; ячейка D265. Ячейка входных данных для столбцов — это драйвер роста оборота для Y06F в разделе "Предположения", который связан с вашей моделью, ячейка J11.

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

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

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

Ниже приведен скриншот заполненной таблицы данных:

Вы можете проверить точность вашей таблицы данных, посмотрев на стоимость акции при темпе роста в постпрогнозный период 5% и драйвере роста оборота 4,5%.

Ваша таблица была организована так, что это значение находится прямо посередине таблицы, в ячейке с более темной зеленой заливкой. Оно показывает 117,1 по сравнению с результатом вашей оценки 117,2. Это дает вам уверенность в том, что ваша таблица данных настроена правильно и рассчитывается корректно.

Из таблицы мы видим, что самая низкая цена акции составляет 80,2 руб. при темпе роста в постпрогнозный период 3% и росте выручки 0%, а самая высокая цена акции — 194,3 руб., полученная при темпе роста в постпрогнозный период 7% и росте выручки 9,5%.

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

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

Затем вы редактируете соответствующие формулы, чтобы связать таблицы данных с моделью, добавив ячейки 0,0%. Например, оборот за Y06F, первый год прогнозов, получается путем применения драйвера роста оборота к обороту предыдущего года, Y05A, как видно из следующей формулы:

Вы отредактируете формулу, чтобы добавить базовое значение для драйвера роста оборота из вашей таблицы данных, 0,0%, в ячейке C295, следующим образом:

=I10*(1+J11+C295)

CopyExplain

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

Таким же образом свяжите таблицу данных Себестоимость продаж с формулой в ячейке J12:

Затем свяжите таблицу данных темпа роста в постпрогнозный период с формулой в ячейке N267.

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

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

Теперь вы можете вернуться и заполнить свою таблицу данных с помощью функции "Анализ «что-если»", "Таблица данных".

Сначала выберите всю таблицу роста оборота:

Перейдите к "Таблица данных" в разделе Данные > Анализ «что-если» (или используйте сочетание клавиш, если оно настроено в Р7 Офис), чтобы открыть диалоговое окно "Таблица данных".

Ниже приведен скриншот диалогового окна "Таблица данных":

Оставьте поле "Подставлять значения по строкам в" пустым и выберите ячейку с 0,0% в качестве ячейки "Подставлять значения по столбцам в". Когда вы нажмете OK, таблица данных будет заполнена рассчитанными значениями цены акции при темпе роста оборота на 2,5% ниже исходного и на 2,5% выше исходного.

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

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

Это скриншот полных таблиц данных с использованием косвенного метода:

Снова проверив вашу таблицу данных, вы заметите, что она на 2,5% выше базового роста оборота 4,5%; другими словами, 7,0%.

При росте оборота 7% и темпе роста в постпрогнозный период 5%, используя прямой метод, мы получаем цену акции 123,3 руб., что совпадает со значением в нашей базе данных косвенного метода для роста оборота. При темпе роста в постпрогнозный период 4% и темпе роста оборота 4,5%, используя прямой метод, мы получаем цену акции 101,0 руб., что совпадает со значением в нашей базе данных косвенного метода для темпа роста в постпрогнозный период.

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

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

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

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

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

=НАИМЕНЬШИЙ(% изменения; номер ранга) (или SMALL в англ. версии)

CopyExplain

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

Используя функции ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) (см. Главу 3, «Формулы и функции»), введите соответствующее имя входных данных и драйвера рядом с ранжированным результатом (абсолютное изменение). Они будут служить нашими метками горизонтальной оси.

Ниже приведен скриншот формул ИНДЕКС и ПОИСКПОЗ:

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

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

Теперь создайте диаграмму, перейдя в Вставка > Гистограмма или Линейчатая диаграмма, а затем выберите Линейчатая с накоплением (2-D stacked bar chart). На вашем листе будет размещена пустая диаграмма. В контекстном меню конструктора диаграмм нажмите Выбрать данные, и откроется диалоговое окно Выбор источника данных, следующим образом:

Теперь добавьте два ряда, один за другим, выбрав Добавить в разделе Элементы легенды (Ряды). Откроется диалоговое окно Изменение ряда. Выберите столбец положительных результатов в качестве первого ряда.

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

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

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

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

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

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

Метки появятся напротив горизонтальной оси, внутри данных отрицательного ряда
(или первого ряда, если он отрицательный):

Дважды щелкните горизонтальную ось (ось категорий), чтобы открыть диалоговое окно Формат оси, как показано на следующем скриншоте:

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

Прокрутите вниз, чтобы открыть опцию Метки, и щелкните ее.

Измените положение метки на Низкое (или Внизу):

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

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

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

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

Для каждого сценария вы бы предположили альтернативные значения для выбранных переменных. При выборе переменных вы бы сосредоточились на тех входных данных или драйверах, которые являются наиболее субъективными. Анализ сценариев включает подстановку всех выбранных переменных для данного сценария в вашу модель и изучение влияния этого на цену акции. Мы рассмотрим оптимистичный, базовый и пессимистичный сценарии. Мы также выберем Turnover CAGR, TGR и WACC, так как эти переменные оказывают значительное влияние на цену акции.

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

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

Теперь нам нужно настроить поле выбора, которое позволит нам выбрать сценарий и связать сценарий со значениями, которые мы выбрали для каждого сценария. Мы будем использовать для этого поле со списком (combo box), а также функцию СМЕЩ (OFFSET). Это поле со списком можно найти в разделе Вставка в группе Элементы управления в Р7 Офис на вкладке Разработчик (или Плагины > Элементы управления содержимым, в зависимости от версии и настроек). Если вкладка Разработчик не отображается, перейдите в Файл > Параметры > Настройка ленты. В разделе Основные вкладки установите флажок рядом с Разработчик.

Следующий скриншот показывает расположение Поля со списком на вкладке Разработчик
(или аналогичное расположение):

Разместите Поле со списком рядом с полем ссылок, щелкните правой кнопкой мыши поле со списком и выберите Формат элемента управления. Откроется диалоговое окно Формат объекта (или аналогичное),
как показано на следующем скриншоте:

Для Диапазона ввода выберите ячейки с метками сценариев. Связь с ячейкой может быть любой пустой ячейкой. Выберите ячейку слева от метки ЦЕНА АКЦИИ.

Следующий скриншот показывает диалоговое окно Формат объекта для Поля со списком:

Щелкните стрелку справа от поля со списком. Откроется раскрывающийся список с именами сценариев.

Следующий скриншот — это раскрывающийся список опций Поля со списком:

Как только вы выберете любой из вариантов, он будет отображен в поле со списком, а в ячейке
Связь с ячейкой будет отображено число, соответствующее позиции в списке выбранного вами варианта.
Теперь нам нужно отразить значения переменных для выбранного сценария в строке ссылок.
Для этого мы будем использовать функцию СМЕЩ (OFFSET) вместе со счетчиком в Связанной ячейке.
Аргументы функции СМЕЩ расположены так, чтобы позволить вам направить Р7 Офис к конкретной ячейке,
из которой нужно извлечь содержимое.

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

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

Следующий скриншот показывает число 2 в Связанной ячейке.
Когда выбран Базовый случай, второй в списке сценариев:

При выборе Базового случая в Поле со списком в Связанной ячейке отображается число 2 (см. предыдущий скриншот). Это соответствует позиции Базового случая в раскрывающемся списке Поля со списком. Затем выберите ячейки в строке ссылок и введите функцию СМЕЩ.

Используя наши знания о системе ссылок Р7 Офис, мы понимаем, что это одна и та же формула, которую мы собираемся ввести в три ячейки в строке ссылок. Поэтому мы можем выбрать три ячейки, ввести формулу один раз, а затем нажать Ctrl + Enter, чтобы завершить и заполнить все три ячейки сразу.

Следующий скриншот показывает значения при вводе параметров формулы СМЕЩ:

Сделайте ссылочной ячейкой G306, заголовок Turnover CAGR. Когда ссылка копируется вправо, мы хотим, чтобы она смотрела на ячейку H306, заголовок TGR, который находится в следующем столбце той же строки. Сделайте ячейку строки $F$314 Связанной ячейкой. Мы блокируем эту ссылку на ячейку, чтобы при копировании ссылки вправо она по-прежнему смотрела на Связанную ячейку.

Затем мы добавляем запятую (или точку с запятой) после аргумента строки, чтобы показать, что мы закончили ввод параметров для этого аргумента. Однако, поскольку мы не вводим больше аргументов, мы можем закрыть скобки, а затем нажать Ctrl + Enter, чтобы завершить и заполнить все три ячейки сразу. Теперь свяжите цену акции с соответствующей ячейкой в разделе Оценка вашей модели. Мы также очистим ячейку Связь с ячейкой, чтобы она не отвлекала внимание.

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

Следующий шаг — связать ячейки в строке ссылок с соответствующим местом в вашей модели. Сначала свяжите ячейку CAGR роста выручки из Предпосылок, ячейку I10, с G312 в строке ссылок.

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

Затем свяжите TGR, ячейку D247, с ячейкой H312 в строке ссылок.

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

Затем свяжите ячейку D245, WACC, с ячейкой I312 (а не H312, как указано в тексте) в строке ссылок.

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

Наконец, мы используем условное форматирование, чтобы выделить любой текущий выбранный сценарий. Перейдите в Условное форматирование > Создать правило... на вкладке Главная.

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

Выберите Создать правило..., чтобы открыть диалоговое окно Новое правило форматирования:

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

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

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

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

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

=G307=G$312

CopyExplain

Когда это утверждение истинно (в настоящее время оно ложно), эта ячейка будет отображать специальный формат, который мы укажем. Когда формула копируется вправо, G307 становится H307, а G$312 становится H$312. Когда формула копируется вниз, G307 становится G308, а G$312 остается G$312. Это гарантирует, что формула всегда связана со строкой строки ссылок, строкой 312.

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

Этот скриншот показывает, как указать пользовательский формат шрифта:

Теперь мы выберем лучший цвет заливки.

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

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

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

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

В сценарии Базовый случай цена акции составляет 117,6.
Базовый сценарий с условным форматированием показан на следующем скриншоте:

В Поле со списком выберите Оптимистичный сценарий.

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

Значения для Оптимистичного сценария теперь отображают условный формат. Это потому, что текущий выбранный сценарий в нашем Поле со списком — Оптимистичный сценарий. Цена акции составляет 222,4. Теперь выберите Пессимистичный сценарий.

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

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

Цена акции составляет 69,9.

Имитация Монте-Карло — это модель, которая рассчитывает вероятности различных результатов в процессе, где присутствует большая внутренняя неопределенность. Модель использует случайно сгенерированные числа для получения тысяч возможных результатов, из которых можно вывести наиболее вероятный исход. Мы рассмотрим рост свободного денежного потока, FCFF, а также стоимость капитала, WACC, которые являются неотъемлемыми частями нашей модели DCF.

Рассчитайте темпы роста FCFF за исторические годы с Y02A по Y05A,
используя следующую формулу:

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

Обычно имитация Монте-Карло использует тысячи повторений. Однако в иллюстративных целях мы ограничимся числом 100. Давайте возьмем среднее значение исторического роста FCFF.

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

Мы получаем WACC из раздела оценки нашей модели.

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

Мы предположим стандартное отклонение 1% для каждого. Стандартное отклонение — это мера того, насколько мы ожидаем, что наши симуляции будут отличаться от наших начальных значений для роста FCFF и WACC. Теперь мы создаем генератор случайных чисел для роста FCFF и WACC, используя функцию Р7 Офис, СЛЧИС (RAND), как для роста FCFF, так и для WACC.

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

Функция СЛЧИС генерирует случайное число от 0 до 1. Каждый раз, когда Р7 Офис выполняет вычисление в этой или другой ячейке, функция пересчитывается, и будет сгенерировано другое случайное число. Число 0,83751 означает 83,75% вероятность возникновения этого значения.

При такой настройке Р7 Офис генерирует 100 различных результатов как для роста FCFF, так и для WACC. Если бы эти результаты были нанесены на график, они бы следовали так называемому нормальному распределению.

Это график нормального распределения:

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

В создаваемом нами сценарии мы генерируем случайные вероятности и хотим преобразовать их в значения как для роста FCFF, так и для WACC. Для этого мы используем функцию Р7 Офис, НОРМОБР (NORMINV). Эта функция использует среднее значение, стандартное отклонение и вероятность для расчета значения для переменных роста FCFF и WACC.

Ниже приведен скриншот, показывающий функцию НОРМОБР:

Мы можем вручную заставить Р7 Офис сгенерировать новое случайное число, просто нажав F2 (Редактировать), а затем Enter (или просто нажав F9 для пересчета). Каждый раз, когда генерируются новые случайные числа, создаются новые значения для роста FCFF и WACC.

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

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

Начните с ввода 1 в пустую ячейку. Выделив эту ячейку, щелкните значок Заполнить, который находится в группе Редактирование на вкладке Главная, а затем выберите Прогрессия.

Это скриншот выбора Заполнить > Прогрессия:

Откроется диалоговое окно Прогрессия. Выберите Столбцы в поле Расположение, введите Шаг как 1, а Предельное значение как 100.

Это скриншот диалогового окна Прогрессия:

Когда вы нажмете OK, будет сгенерирована последовательность от 1 до 100 вниз по столбцу, начиная с введенной вами ранее цифры 1. Теперь создайте первую итерацию, связав рост FCFF с ячейкой рядом с числом 1.

Это скриншот, показывающий, как рост FCFF связан с нашей новой таблицей:

Затем свяжите WACC таким же образом.

Это скриншот, показывающий, как WACC связан с нашей новой таблицей:

Теперь мы будем использовать Таблицу данных для заполнения нашей новой таблицы значениями Роста FCFF и WACC. Выделите все ячейки в новой таблице, а затем перейдите в группу Прогноз на вкладке Данные и выберите Анализ «что-если», а затем Таблица данных. В открывшемся диалоговом окне Таблица данных проигнорируйте поле "Подставлять значения по строкам в".

Для поля "Подставлять значения по столбцам в" выберите любую пустую ячейку за пределами таблицы.

Этот скриншот показывает выбор пустой ячейки за пределами таблицы в качестве ячейки для подстановки значений по столбцам:

Когда вы нажмете OK, таблица будет заполнена 100 итерациями Роста FCFF и WACC,
как показано на следующем скриншоте:

Затем мы получаем наиболее вероятное значение для роста FCFF, учитывая наши предположения, взяв среднее значение 100 итераций роста FCFF.

Этот скриншот иллюстрирует, как мы приходим к новому росту FCFF:

Наиболее вероятное значение для WACC — это среднее значение 100 итераций WACC.

Следующий скриншот иллюстрирует, как мы приходим к новому WACC:

Теперь мы возьмем наши новые значения для роста FCFF и WACC и подставим их в нашу модель оценки.

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

Затем мы подставляем WACC в нашу формулу для Терминальной стоимости, как показано здесь:

Мы пересчитываем приведенные стоимости для FCFF за прогнозируемые годы и терминальную стоимость, чистую приведенную стоимость и так далее, чтобы получить Стоимость предприятия 2 069 368 тыс. руб., Стоимость собственного капитала, рыночную капитализацию, 1 942 675 тыс. руб. и, в конечном итоге, Цену акции (РУБ.) 194,27.

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

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

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