Наши популярные онлайн курсы
Опытом в части анализа и аудита финансовых моделей для дальнейшего встраивания рисков, в рамках зимней сессии АНО ДПО «ИСАР», поделился сооснователь и руководитель направления АНО ДПО «ИСАР», член наблюдательного совета ассоциации «Гильдия экспертов финансового рынка» Константин Дождиков. Он наглядно показал как можно подготовить финансовую модель для встраивания в нее рисков.
Введение. Немного о финансовых моделях.
Прежде всего, Константин Дождиков напомнил, что под финансовой моделью подразумевается математическое представление финансовых операций и финансовой отчетности компании, где используется некий набор входных факторов – курс доллара, план продаж, инфляция, стоимость материалов, план производства, административные расходы, инвестиции и так далее. Отдельно может анализироваться ситуация с рынком и прогнозы по нему. В целом, финансовая модель используется для прогнозирования будущих финансовых результатов компании на основе ключевых допущений.
Это классическое представление финансовой модели, но нужно учитывать, что такие модели создаются для разных целей, поэтому возможны различия во входных параметрах, структуре и итоговой отчетности.
В рамках семинара спикер рассматривал финансовые модели, в которые в дальнейшем можно встроить имитационную модель, прежде всего, экселевскую, поскольку для имитационной модели используются именно специализированные программные продукты, встроенные в Excel.
Имитационная модель и шаги по ее созданию
Имитационная модель моделирует множество возможных сценариев и оценивает влияние заданных рисков (возможных изменений исходных параметров) на конечный результат, а также позволяет определить те риски/исходные параметры, которые оказывают на него наибольшее влияние.
Создание имитационной модели предполагает следующие шаги:
- Постановка целей и задач моделирования. Определитесь, для чего вы проводите моделирование, какую цель преследуете. На этом этапе важно выдвинуть гипотезы в отношении того, какой результат вы ожидаете в действительности.
- Проверка (аудит) и подготовка существующей финансовой модели. Этот этап может занимать от 10 до 20% от общего времени, то есть от возникновения задумки сделать имитационную модель до подготовки презентации для лиц, принимающих решения, о том, как риски влияют на то или иное решение. От этой работы зависят дальнейшие шаги — без прохождения данного этапа невозможно двигаться дальше, поскольку нельзя включать риски в неработающую модель – в таком случае их нельзя будет объяснить с точки зрения логики бизнеса, и бизнесу будет невозможно принять объективное решение.
- Определение выходных факторов ModelRisk. Нужно понять, какие итоговые показатели вам важны — прибыль, остатки денежных средств и так далее. В результате вы должны увидеть, как риски влияют на эти целевые показатели.
- Определение входных факторов и их распределений. В каждый из факторов нужно будет включить неопределенности: такими факторами могут быть цены на продукцию, объем продаж, задержки в поставке оборудования и так далее.
- Определение корреляции риск-факторов. Когда вы определяетесь с видами распределений конкретных факторов, необходимо выстроить корреляцию, так как часть факторов между собой взаимосвязана. К примеру, на эластичных рынках при росте цены на какой-либо товар могут упасть объемы продаж. Это, так называемая обратная корреляция. Или, допустим, если в цепочке процессов в предыдущем этапе реализовывались риски, повышается вероятность возникновения других рисков на последующих этапах и т.д.
- Установка количества итераций и имитаций. Рекомендуется устанавливать 10 тысяч итераций. В большинстве моделей при таком количестве итераций результаты каждого отдельного моделирования будут не сильно отличаться друг от друга и по сути могут использоваться как репрезентативные.
- Запуск имитационного моделирования.
- Формирование выводов для менеджмента в виде графиков, слайдов и так далее. Кроме того, нужно проверить, насколько реалистичны получившиеся результаты, не допущены ли в процессе работы ошибки. Также важна проверка результатов на здравый смысл и бизнес-логику.
Проверка (аудит) и подготовка существующей финансовой модели
Риск-менеджеры, как правило, в качестве базовой модели берут исходную финансовую модель у финансистов, консультантов, а затем встраивают в нее риски. Т.е., по сути, на основе финансовой модели формируют имитационную модель. Реже риск-менеджерам приходится создавать модель “с нуля” (в таком случае речь идет о специфических моделях, которых не существует как инструмента в организации). Это может касаться конкретных производственных моментов, когда, например, нужно посчитать риски загрязнения окружающей среды.
Как узнать, можно ли использовать некую финансовую модель для встраивания рисков? Сфокусируйте внимание на нескольких аспектах:
1. Размер модели и ее содержание
Прежде всего, нужно понять размер модели и ее содержание. Часто модель растет с историей компании, на нее «налипают» следы истории в виде оставшихся макросов, кодов, листов с лишними расчетами, которые уже не используются в расчетах. В результате постоянного внесения изменений модель становится пере-размеренной. Работа с имитационной моделью — трудоемкий процесс с точки зрения компьютерной мощности — иногда моделирование может занимать несколько часов. Чтобы не ждать результатов так долго, нужно изначально посмотреть, насколько пере-размерена модель и, возможно, уменьшить ее: вполне вероятно, что она содержит лишние блоки, файлы и данные, которые не участвуют в расчетах.
Модель может сильно утяжелять механизм “Таблиц данных” Excel. Найдите блок «Анализ чувствительности», где эти таблицы часто используются, и удалите их. В дальнейшем при формировании результатов имитационной модели вы сможете построить диаграммы-торнадо с гораздо более правильными выводами, и вы сможете понять, насколько основные итоговые показатели чувствительны к риск-факторам. Это, по сути, заменит блок с анализом чувствительности.
Если вы считаете, что модель не сильно переразмерена, вам не хочется искать какие-то ошибки и что-то исправлять и вы принимаете решение работать с этой моделью, эффект скорее всего будет соответствующий: ошибочные выводы, которые очень сложно обосновать на языке бизнес-логики. В итоге лица, принимающие решения, отправят материал на доработку, и риск-менеджеру придется, как минимум, разбираться в данных, а как максимум – переделывать работу заново. Решение здесь очень простое: когда вы видите, что существует диссонанс между размером модели и ее фактическим содержанием, самый легкий способ – создать новую модель. По сути, это означает открыть чистый файл Excel и скопировать из финансовой модели ту логику и данные, которые вам нужны, в виде формул и вводных параметров.
На создание чистой модели в большинстве случаев у вас уйдет один-два часа, а на работу с уже имеющейся модель и последующей работе по исправлению ошибок модели — в разы больше времени.
2. Параметры вычислений и время вычислений
Нужно проверить, запускается ли модель. Первая проверка на работоспособность: в модели, в принципе, хоть что-то считается или может быть так, что в ней все цифры зафиксированы, и тогда непонятно, что и куда вы будете вставлять, какую имитационную модель будете строить, если ни одной формулы внутри этой модели нет, и цифры не меняются. Значит, вам такая модель не подходит и необходимо рассмотреть иные варианты – запросить другую модель или построить ее самому.
Для того, чтобы понять, можно ли использовать данную финансовую модель, понажимайте F9 (в Excel — перерасчет формулы) и посмотрите, как ведет себя компьютер – задумывается ли, насколько долго считает. В результате сможете понять, перегружены ли вычисления. Не забывайте в параметрах файла в разделе формулы поставить автоматический пересчет формул иначе модель не будет пересчитываться даже если в ней есть формулы (Меню «Файл», затем «Параметры», затем «Формулы», затем поставить галочку в разделе «Вычисления» «Автоматически»).
3. Ссылки на другие файлы и «ломанные» ссылки
Бывает, что одни Excel файлы делают одни подразделения, другие – другие, а третьи сводят их в общей таблице. И в итоге исходная модель имеет множество ссылок на другие файлы. Чтобы модель заработала, рекомендуется убрать ссылки (Меню «Данные», затем «Изменить связи», затем «Разорвать связь»). В результате вместо ссылок в ячейках останутся значения. Итоговая модель должна стать одним файлом без ссылок на другие файлы. Это наилучшая практика, к которой надо стремиться.
4. Список имен и ошибочных ссылок имен
В Excel есть механизм имен, когда вы можете назвать отдельный ряд ячеек таблицы Excel каким-то именем. При использовании имен в формулах вместо ссылки на ячейки будут использовать имена рядов. Так, например, формула выручки может выглядеть как «= Цена x Объем продаж». Важно чтобы не было ошибочных имен и имен с ссылками на другие файлы. Для этого надо зайти в Excel в раздел «формулы» и выбрав вкладку «диспетчер имен», можно увидеть список созданных имен. В этом списке видно, где имена имеют «ломанные» ссылки. Их надо удалить, так как они могут быть элементами ошибочных вычислений. При этом важно проверить использовались ли эти имена в расчетах, возможно имена придется заменить. Также следует проверить правильность всех имен в таблице, а те, которые не используются в дальнейших расчетах, можно удалить. В итоге у вас останутся только правильно работающие имена, которые используются в расчетах.
5. Ошибки вычислений (#ДЕЛ/0! и другие)
В модели важно проверить ошибки вычислений, которые могут не позволить провести имитационное моделирование. В помощь встроенный механизм Excel, который сразу показывает есть ли ошибка вычислений, например #ДЕЛ/0! или другие. Нужно визуально пройтись по всей модели просмотрев и откорректировав расчеты, которые дают такие ошибки.
6. Изменения формул в строке
Одна из распространенных ошибок, которые зачастую встречаются в разных моделях – это вставка в формулы некоторых коэффициентов или значений в виде чисел, а не ссылок на входные данные. Это серьезная проблема, т.к. такие цифры в формуле, как правило, нигде в модели не описаны и их суть не ясна. Это может быть корректирующий коэффициент или иная ручная корректировка формул. Чтобы выявить такие коэффициенты нужно внимательно просмотреть строки с одинаковыми формулами. Там, где формула была изменена, в частности, добавлен такой коэффициент, можно увидеть пометку, которую делает Excel в визе зеленого треугольника (см. на картинке ниже). Например, у вас в модели рассчитывается выручка на каждый прогнозный период. И в каждому году должна быть одна и таже формула/аналогичный расчет. Но в одном из прогнозных периодов формула изменена, в частности добавлена какой-то цифра вручную. Такое изменение мы как раз сможем увидеть в виде пометки Excel (см на картинке ниже). Нужно проверить формулу в данной ячейке и уточнить у создателей исходной модели почему изменена формула или вставлена вручную цифра и что она обозначает. В результате пояснений возможно потребуется включение в список входных параметров данной цифры и корректировка всех формул в строке либо удаление данной цифры из формулы.
7. Неправильные знаки (входные данные, расчеты, выходные формы)
Еще одной проверкой должна стать проверка расчетов на знаки. Т.е. если в модели все затраты со знаком “-“ а расчеты суммы расходов со знаком “+”, то значит где-то есть ошибка в формулах, которую надо исправить. Или выручка в определенные годы вдруг становится отрицательной.
8. Найти все вводные параметры
В исходной модели необходимо собрать все входные параметры. Это важно, т.к. от этого будет зависеть какие факторы вы сможете замоделировать в имитационной модели. Зачастую это бывает сложная задача, т.к. входные параметры могут быть на всех листах модели в различных ячейках. Тогда необходимо их все найти.
В результате надо составить конкретный список рисков и посмотреть достаточно ли этих параметров для решения задачи имитационного моделирования или часть параметров придется добавить, доработав исходную модель. Например, в модели нет курса доллара США, а в имитационной модели вы бы хотели учесть данный риск-фактор.
9. Анализ итоговых результатов:
Анализ итоговых результатов, например, выручки или себестоимости, по сути, связан со следующими проверками:
• Аккуратность формул
• Корректность логики
Оба эти аспекта важны. Механизмом проверки формул является встроенный в Excel механизм проверки формул (в меню «Формулы», «Влияющие ячейки», «Зависимые ячейки», «Проверка ошибок» и др.). Для корректности формул необходимо проверить на те ли ячейки ссылаются формулы. Для проверки логики расчетов необходимо проверить, например, правильно ли считается выручка и расходы. Так или иначе, в моделях всегда есть какое-то упрощение логики. Нужно посмотреть, насколько такая логика соответствует цели расчетов модели и обеспечивает отсутствие материальных ошибок в связи с таким упрощением.
После этих девяти шагов у вас, вероятно, появится список доработок (вы поймете, что не хватает каких-то факторов, элементов, и, возможно, нужно будет изменить логику в модели) и потребуется время на их устранение. Только после этого можно перейти к заключительному этапу.
10.Подготовка шаблона отдельного листа «Риск-факторы»
Если исходная финансовая модель была создана не риск-менеджерами, то очень вероятно, что она может меняться со временем (дополняться, уточняться, менять логику расчетов), и каждый раз это будет, по сути, новая модель. Встраивая в такую модель риски, вам придется проверять и отслеживать изменения, внесенные другими людьми. В связи с этим не рекомендуется изменять логику расчетов уже существующих листов модели, а риски в модель рекомендуется встраивать с помощью создания в модели отдельного листа, который можно назвать, например, «Риск-факторы».
Листы модели Excel могут выглядеть следующим образом:
В данном листе можно использовать некий шаблон для внесения туда рисков.
Шаблон может выглядеть следующим образом:
Исходя из списка входных параметров, формируется список риск-факторов. В итоге у вас получится некоторый список рисков с параметрами для моделирования. Далее необходимо просто подставить результаты моделирования (выделено желтым в таблице выше) в расчеты модели. Например для Риск 1 в нашем случае мы должны заменить значение в ячейке Расчеты!С5 (ячейка в исходной модели, где находится входной параметр курс доллара США) на формулу, ссылающуюся на соответствующую желтую ячейку нашей таблицы листа Риск-факторы. В нашем случае в ячейке Расчеты!С5 появится формула «=’Риск-факторы’!M5». Также, если необходимо, можно замоделировать целый ряд значений как это показано в шаблоне выше для значений, например, 2022-2026гг. Соответственно, ссылки в модели нужно будет сделать на этот ряд.
Правильность сделанных ссылок можно проверить, скопировав исходные значения входных параметров в столбце “Исходные данные модели” на листе «Риск-факторы» в желтые ячейки столбца «Моделирование». После чего нужно проверить выдает ли такие же результаты модель в сравнении с исходной моделью, до включения в нее рисков. Если результаты те же, то ссылки сделаны правильно.
На отдельном листе «Риск-факторы» можно выполнять любые расчеты, а также выводить результаты моделирования при этом не меняя логику исходной модели. Это, как уже выше было сказано, позволяет также быстро встроить риски в новую исходную модель, если, например, финансовые службы вдруг поменяли логику своих расчетов.
