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

Управляющее резюме

Введение: Финансовая модель

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

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

Планирование вашей модели

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

1. Определите конечную цель модели.

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

2. Понимание сроков как построения модели, так и срока ее полезного использования.

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

3. Найдите оптимальный компромисс между «детальностью» и «возможностью повторного использования».

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

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

Структурирование вашей модели

На данном этапе мы готовы открыть Excel и начать думать о структурировании. На максимально возможном уровне каждая модель может/должна быть разделена на три части: (а) входы/движущие силы, (б) расчеты (прогнозируемые финансовые отчеты) и (в) результаты. Чем лучше вы разделяете эти разделы, тем легче будет проверять и вносить поправки в модель, сводя к минимуму ошибки и выполняя своевременную оптимизацию.

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

1. Титульная страница (вкладка): кодовое название проекта, описание замысла модели, контактная информация автора и любые применимые заявления об отказе от ответственности.
2. Вкладка «Драйверы»: исходные данные и предположения.
3. Вкладка «Модель»: «Расчеты» (т. е. три прогноза и расчеты финансового отчета).
4. Вкладка «Выходные данные»: четкое и аккуратное резюме наиболее важных моментов модели.
5. Вкладка «Конфиденциальность»: ряд сценариев, чувствительности и результатов данных, на которые руководство будет полагаться при переходе к процессу принятия решений.

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

Обложка

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

1. Название модели: не требует пояснений.
2. Цель модели: абзац, описывающий ее предполагаемое использование.
3. Указатель модели: краткая таблица с подробным описанием и назначением каждой вкладки. Этот раздел может быть особенно полезен для операторов, не связанных с финансами, помогая им «усвоить» структуру и поток модели, выделив, какие вкладки им нужно использовать для входных данных, на каких выходных данных следует сосредоточиться при принятии решений и на каких вкладках сложных расчетов они должны работать. оставить нетронутым.
4. История версий модели. Если вы потратите несколько секунд на ввод по дате ключевых изменений, внесенных в модель по мере продвижения, вы всегда сэкономите время, особенно если вам нужно отследить и отменить/изменить изменения. Это особенно верно для сложных моделей и моделей, которые вы можете использовать в качестве шаблонов в будущих периодах.
5. Контактная информация автора: не требует пояснений
6. Применимые правовые оговорки (если таковые имеются, предоставленные вашим юрисконсультом) : говорят сами за себя

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

Вкладка водителя: входные данные и предположения

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

Пример примера вкладки «Драйверы и допущения»

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

Вкладка «Модель»: подробные расчеты и эксплуатационная компоновка

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

Пример вкладки "Пример модели"

Вкладка «Сценарии и конфиденциальность»

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

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

Несколько замечаний к изображению выше:

1. Пользователь модели должен иметь возможность редактировать только это, так как именно здесь он будет выбирать номер сценария. Номер относится к одному из сценариев, представленных в правой части электронной таблицы. Затем пользователь представит выбранный сценарий (в данном случае № 6) в первом столбце. Это единственный столбец электронной таблицы «Сценарий и чувствительность», на который ссылается модель.
2. Добавьте сюда пару полей описания, которые эффективно обобщают то, что представляет собой выбранный сценарий.
3. Я всегда нахожу очень полезным, особенно если кто-то еще будет использовать модель, добавить столбец, определяющий каждую единицу ввода.
4. Этот столбец извлекает статистику/поле кредитного плеча выбранного сценария (в данном случае № 6), которые все отображаются справа (синим цветом). Формула, необходимая для его управления, представляет собой функцию смещения, т. е. «=СМЕЩЕНИЕ (вставьте пустую ячейку сразу слева от первого сценария, выделенного красным выше, Ячейка, в которой сценарий выбран/выделен)». Обратите внимание, что между двумя ячейками есть пробел, поэтому две запятые (,,) не являются опечаткой.
5. Сгруппируйте свои предположения в макрокатегории и подкатегории. Это поможет и вам (разработчику модели), и вашим пользователям получить четкое представление о том, какой сценарий выбрала модель.

Пример таблицы чувствительности образца

Вкладка «Вывод»

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

1. Вкладка «Финансовые результаты»: это сокращенная сводка финансовых показателей, подробно описанных на вкладке модели. Обычно они представляются на годовой основе (хотя модель может быть и квартальной). Эти выходные данные должны содержать от 50 до 150 строк и должны представлять все ключевые позиции с вкладок расчетов. Обязательно предоставьте достаточно подробностей, чтобы пользователи не переключались между этой вкладкой и различными вкладками «Расчеты». Также обратите внимание, что в соответствии с передовой практикой никакие выходные вкладки не должны повторно выполнять какие-либо вычисления, и эта информация должна включать только прямые ссылки.
2. Вкладка «Резюме для руководства»: эта вкладка довольно стандартна и обычно представляет собой сочетание графиков, диаграмм и таблиц, максимально просто и легко усваивающих иллюстрирующих различные тенденции, анализы и основные сводные статистические данные, которые требуются руководителям и членам совета директоров для навигации. ключевые решения.
3. Вкладка «Конкретные результаты»: эта вкладка содержит конкретные результаты, обычно продиктованные шаблоном инвестиционного меморандума, презентацией инвестиционного комитета или запросами руководителей и членов совета директоров, которые необходимы для достижения их точек принятия решений.

Пример вкладки вывода модели, включая таблицы, диаграммы и графики

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

Форматирование вашей модели

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

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

Синий = входные данные или любые жестко закодированные данные, такие как исторические значения, предположения и драйверы.

Черный = формулы, расчеты или ссылки, взятые из того же листа.

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

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

Красный = ошибка, которую необходимо исправить.

Пример хорошо отформатированной (цветной) сводки финансовых показателей

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

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

Инструкции по созданию макросов (для версий Excel для Mac и ПК):

1. Нажмите Alt + W + M + R одновременно, чтобы назвать и начать запись вашего макроса.
2. Нажмите F5 («Перейти к ячейке»), а затем одновременно нажмите Alt + S, чтобы перейти в меню «Перейти к специальному».
3. Нажмите «O», чтобы выбрать «Константы», и «X», чтобы снять отметку с текста.
   
4. Теперь одновременно нажмите Alt + H + FC (или Ctrl + 1) и выберите синий цвет шрифта для этих констант.
5. Нажмите Esc.
6. Теперь сделайте то же самое, начиная с F5, но вместо констант выберите формулы (F) и нажмите «X», чтобы снять отметку с текста.
7. Теперь нажмите Alt + H + FC (или Ctrl + 1) и выберите черный цвет шрифта для этих констант.
8. Остановите запись макроса с помощью Alt + W + M + R или Alt + T + M + R.

Ссылки на другие книги

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

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

Лучшие практики для аудита модели

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

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

1. Один ряд, одна формула

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

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

Одним из простых способов устранения этих случаев является использование флагов (например, 1/0, TRUE / FALSE ), расположенных в верхней части электронной таблицы, а затем ссылающихся на них с помощью операторов IF через тело модели. Простая иллюстрация этого в действии выглядит следующим образом:

Пример «Использование флагов» в моделировании Excel

2. Никаких жестко закодированных чисел, встроенных в формулы

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

3. Простота всегда лучше

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

4. Последовательно придерживайтесь своего соглашения о жестах

Вы должны решить в нулевое время, каким будет ваше соглашение о знаках/ключ. В качестве иллюстрации спросите себя на этапе разработки вашей модели: «Будут ли затраты, расходы, отчисления, амортизация, капитальные затраты и т. д. представлены в виде отрицательных или положительных чисел?» Я лично предпочитаю всегда представлять затраты в виде отрицательных чисел по двум причинам: (а) итоги всегда будут прямыми суммами, и вы сведете к минимуму ошибки пользователя, и (б) будет легче обнаруживать ошибки, используя только знаки.

5. Избегайте именования ячеек, вместо этого полагайтесь на логику сетки Excel.

Там, где это возможно, я настоятельно рекомендую избегать именования ваших ячеек, так как становится трудно найти исходный ввод для указанной именованной ячейки (например, «Инфляция») в будущем. Вместо этого я рекомендую вам полагаться на соглашение о сетке Excel в ваших формулах (например, просто ссылаясь на ячейку C4 или местоположение, [Tab Name]l'!G21 , если ссылка находится на другой вкладке или в другой книге).

6. Никогда не используйте один и тот же вход в нескольких местах

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

7. Избегайте связывания файлов

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

8. Не прячьте листы или строки

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

9. Чем меньше вкладок, тем лучше, чем несколько вкладок меньшего размера

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

10. Создавайте проверки по всей модели с помощью «Агрегированных проверок ошибок», расположенных на одной вкладке.

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

Образец Бухгалтерского баланса "Чек"

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

Вырезать — для опытных пользователей: советы по Excel

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

1. Используйте как можно больше сочетаний клавиш. В Интернете есть несколько файлов с сочетаниями клавиш Excel, которые подходят для разных нужд. Я сошлюсь на пару здесь:
   • Ключевая шпаргалка по моделированию
   • Полный список советов по Excel
2. Используйте F5 («перейти к специальному»), чтобы быстро найти все жестко закодированные числа или формулы.
3. Используйте прецеденты трассировки и зависимые трассировки для аудита модели.
4. Используйте XNPV и XIRR , чтобы разрешить применение пользовательских __ дат к денежным потокам на пути к анализу доходности; это, в отличие от функций Excel NPV и IRR , которые неявно предполагают равноудаленные интервалы времени для расчета.
5. Используйте функцию INDEX MATCH функции VLOOKUP для поиска информации в больших электронных таблицах.
6. VLOOKUP почти всегда превосходит операторы IF ; освоиться с ним.
7. Возьмите за привычку включать IFERROR в синтаксис ваших формул.
8. Используйте комбинацию функции даты, EOMONTH и оператора IF , чтобы сделать даты динамическими.
9. Удалите линии сетки при представлении или совместном использовании финансовой модели; это делает выходной документ более чистым и безупречным.

Нравится или нет…

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

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