Финансовая модель предприятия в excel примеры: Пример построения финансовой модели предприятия в Excel

Отзывы клиентов

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

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

Что наши клиенты говорят о наших шаблонах финансовой модели:

«Обожаю, это отличный продукт»

L.Кишор, предприниматель по приложениям

«Я нашел их ЧРЕЗВЫЧАЙНО полезными! и во что бы то ни стало быстрый и полезный способ избавиться от излишних вычислений, формул и траты времени. Быстрый, точный и точный способ проведения оценок и / или оценок ».

Г. Гарза, старший финансовый консультант

«Я всегда в восторге от ваших моделей. Спасибо. Продолжайте поставлять эти модели.

Дж. Касабона, финансовый директор FirmGreen Inc.

«Привет! Мне не нужна помощь.Просто хотел сообщить вам, что эта веб-страница просто фантастическая! Продолжай!

Р. Бакерисо, предприниматель

“Шаблоны финансовых моделей от eFinancialModels предлагают отличный способ профессионально разработать финансовую модель

Н. Бендаркар, предприниматель

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

10 примеров финансового моделирования

Финансовая модель в Excel бывает очень разнообразной. Но почему это так? Что ж, у каждого бизнеса разные требования, для которых строятся разные финансовые модели. Например, одному бизнесу может потребоваться модель слияния и поглощения из-за того, что вскоре произойдут события поглощения. Принимая во внимание, что другому бизнесу может потребоваться только базовая финансовая модель из трех отчетов для прогнозирования.

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

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

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

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

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

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

Создание финансовой модели в формате Excel для модели M&A — довольно сложная задача.Необходимо учитывать активы, обязательства, денежный поток и все остальное в обоих компаниях. Выбор профессионального сервиса для этого — лучший вариант для любого бизнеса.

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

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

Модель Sum-of-Parts — идеальный вариант для крупномасштабного бизнеса. Это не означает, что нужно учитывать слишком много активов.Вместо этого компании, у которых есть дочерние компании, конгломераты и тому подобное, должны использовать это, чтобы упростить себе финансовое моделирование.

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

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

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

О модели Some-of-Parts мы говорили ранее. Модель консолидации связана только с этим. В то время как модель Some-of-Parts фокусируется на каждом бизнес-подразделении, модель консолидации — это их объединение. Например, ваши финансовые модели в Excel для разных бизнес-единиц будут показаны вместе в одной консолидированной финансовой модели.

Финансовая модель консолидации в Excel дает вам четкое общее представление о вашем бизнесе.Итак, если у вас работают разные подразделения, вам также нужно будет построить эту модель.

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

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

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

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

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

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

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

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

Oak Business Consultant помог многим стартапам и малым и средним предприятиям в подготовке бизнес-плана, финансового моделирования и финансового консультирования.

Получите желаемое решение сегодня одним щелчком мыши

Лучшие практики финансового моделирования [Excel]

Введение

Подобно многим программистам, люди, строящие финансовые модели, могут быть весьма самоуверенными в отношении «правильного способа» сделать это.

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

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

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

Модель	Назначение	Гранулярность	Гибкость
Одна страница DCF	Используется в книге предложений на стороне покупателей, чтобы указать диапазон оценки для одного из нескольких потенциальных объектов приобретения.	Низкий. Диапазон оценки Болл-парка достаточен) / Малый. Весь анализ может поместиться на одном листе (<300 строк)	Низкий. Не подлежит повторному использованию без структурных изменений. Будет использоваться в определенной презентации и распространяться между 1-3 членами команды по сделке.
Полностью интегрированный DCF	Используется для оценки целевой компании в заключении о справедливости, представляемом совету директоров приобретающей компании	Средний	Низкий. Не подлежит повторному использованию без структурных изменений.Будет адаптировано для использования в мнении о справедливости и будет распространяться между участниками сделки.
Шаблон модели компа	Используется как стандартная модель всей производственной командой в банке с выпуклыми скобами	Средний	Высокий. Многоразовый без конструктивных изменений. Шаблон, который будет использоваться для различных презентаций и сделок многими аналитиками и сотрудниками, возможно, другими заинтересованными сторонами. Будет использоваться людьми с разным уровнем навыков работы с Excel.
Модель реструктуризации	Создан специально для транснациональной корпорации для проведения стресс-тестов воздействия продажи одного или нескольких предприятий в рамках консультационного соглашения по реструктуризации	Высокая	Средний.Некоторое повторное использование, но не совсем шаблон. Будет использоваться как командой по сделке, так и партнерами в фирме-клиенте.
Модель финансирования с использованием заемных средств	Используется в процессе утверждения ссуды для анализа результатов ссуды при различных операционных сценариях и кредитных событиях	Высокая	Высокий. Многоразовый без конструктивных изменений. Шаблон для использования в группе.

Детализация финансовой модели

Критическим определяющим фактором структуры модели является степень детализации .Гранулярность означает, насколько детальной должна быть модель. Например, представьте, что вам нужно выполнить LBO-анализ для Disney. Если цель состоит в том, чтобы предоставить минимальный диапазон оценки минимальной стоимости, который будет использоваться в предварительной книге презентаций, может быть вполне уместным выполнить анализ LBO «высокого уровня», используя консолидированные данные и делая очень простые предположения для финансирование.

Пошаговый онлайн-курс

Все необходимое для освоения финансового моделирования

Станьте участником премиального пакета: изучите моделирование финансовой отчетности, DCF, M&A, LBO и Comps.Такая же программа обучения используется в ведущих инвестиционных банках.

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

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

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

Гибкость финансовой модели

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

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

1. Поправки к валюте эквайера
2. Динамическая календари (для определения финансовых показателей компании-покупателя на финансовый год покупателя)
3. Заполнители для различных статей отчета о прибылях и убытках, баланса и отчета о движении денежных средств, которые не отображаются в финансовых отчетах Disney или Apple.
4. Анализ чистых операционных убытков (ни у Disney, ни у Apple нет NOL)

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

В таблице ниже показаны уровни детализации / гибкости общих моделей инвестиционного банкинга.

Возможность представления финансовой модели

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

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

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

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

Форматирование

Цветовая маркировка

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

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

Тип ячеек	Формула Excel	Цвет
Жестко заданные числа (входные данные)	= 1234	Синий
Формулы (расчеты)	= A1 * A2	Черный
Ссылки на другие рабочие листы	= Лист2! A1	зеленый
Ссылки на другие файлы	= [Книга2] Лист1! $ 1	Красный
Ссылки на поставщиков данных (т. е. CIQ, Factset)	= CIQ (IQ_TOTAL_REV)	Темно-красный

Хотя все согласны с тем, что цветовое кодирование очень важно, не отставать от него в собственном Excel может быть проблемой. Отформатировать ячейки в зависимости от того, являются ли они входными данными или формулами, непросто, но можно сделать и . Один из вариантов — использовать программу Excel «Перейти к специальному» (она описана в нашем ускоренном курсе по Excel, записаться на который вы можете здесь). В качестве альтернативы цветовое кодирование значительно упрощается с помощью сторонней надстройки Excel, такой как Macabacus (которая поставляется в комплекте с продуктами для самообучения Wall Street Prep и зачислением в учебные лагеря), Capital IQ или Factset.Эти инструменты позволяют «автоматически раскрасить» весь рабочий лист одним щелчком мыши.

Комментарии

Вставка комментариев (сочетание клавиш Shift F2, см. Наш список основных сочетаний клавиш Excel) в ячейки имеет решающее значение для добавления сноски к источникам и добавления ясности к данным в модели.

Например, ячейка, содержащая предположение о росте дохода, полученное из отчета об исследовании капитала, должна включать комментарий со ссылкой на отчет об исследовании. Итак, сколько комментариев вам нужно? Всегда ошибайтесь в том, чтобы комментировать слишком много .Ни один управляющий директор никогда не будет жаловаться на то, что у модели слишком много комментариев. Кроме того, если вы участвуете в конференц-связи и кто-то спрашивает, как вы пришли к номеру в ячейке AC1238, а вы не заполняете его, вы пожалеете, что не комментировали.

Соглашение о знаках

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

Конвенция 1: Все доходы положительны, все расходы отрицательны.

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

Конвенция 2: Все расходы положительные; отрицательная внереализационная прибыль.

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

Условие 3: Все расходы положительные, за исключением внереализационных расходов.

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

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

Формулы

Избегайте частичного ввода (все модели)

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

Одна строка, один расчет

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

Использовать вычисления с накатом вперед («ОСНОВАНИЕ» или «пробка»)

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

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

Напишите хорошие (и простые) формулы

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

1. Упростите операторы IF и избегайте вложенных IF
2. Рассмотрите возможность использования флагов

Упростите операторы IF

IF, будучи интуитивно понятными и хорошо понимаемыми большинством пользователей Excel, могут стать длинными и трудными для аудита. Есть несколько отличных альтернатив IF, которые часто используют первоклассные разработчики моделей.Они включают использование логической логики вместе с множеством справочных функций, включая MAX, MIN, AND, OR, VLOOKUP, HLOOKUP, OFFSET.

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

с использованием оператора IF

с использованием MIN

Формула револьвера, использующая MIN в качестве альтернативы IF, также лучше работает, когда требуется дополнительная сложность.Представьте, что есть ограничение на ежегодный розыгрыш револьвера в 50 000 долларов. Посмотрите, как нам нужно изменить обе формулы, чтобы учесть это:

с использованием оператора IF

с использованием MIN

Хотя обе формулы сложно проверять, формулу, использующую операторы IF, труднее проверять и она более уязвима для полного выхода из-под контроля с дополнительными модификациями. Он использует вложенные (или встроенные) операторы ЕСЛИ, с которыми наш слабый человеческий мозг с трудом справляется, когда их больше одного или двух.

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

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

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

В нашем примере ниже револьвер компании «зависает», когда она становится банкротом, и новый тип заимствования («DIP») действует как новый револьвер, пока компания не выйдет из банкротства. Кроме того, новая функция «Выход» заменяет DIP. Мы вставляем 3 «флага» в строки 8-10, чтобы вывести «ИСТИНА / ЛОЖЬ» в зависимости от фазы, в которой мы находимся.Это позволяет нам строить очень простые и последовательные формулы для каждого револьвера без необходимости встраивать операторы IF в каждое вычисление.

В ячейке F16 формула = F13 * F8. Всякий раз, когда вы применяете оператор (например, умножение) к ИСТИНА, ИСТИНА обрабатывается как «1», а ЛОЖЬ обрабатывается как «0». Это означает, что револьвер до банкротства является фактическим револьвером, когда флаг предварительного банкротства принимает значение ИСТИНА и становится 0, когда значение флага принимает значение ЛОЖЬ (начиная с столбца I в нашем примере ниже).

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

Имена и именованные диапазоны

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

Не проводить расчет в балансе — ссылка на вспомогательные графики.

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

Как ссылаться на ячейки

Никогда не вводите один и тот же ввод повторно в разных местах

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

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

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

Избегайте формул, содержащих ссылки на несколько листов

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

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

Если вы работаете с более крупными моделями и у вас есть предположения, на которые необходимо ссылаться из отдельного рабочего листа, рассмотрите возможность связывания предположений непосредственно в рабочем листе, на котором вы их используете, и выделите их цветом как отдельную ссылку на рабочий лист. Другими словами, не используйте входную ссылку, встроенную в вычисление (например, = D13 * input! C7). Вместо этого используйте чистую ссылку = input! C7 и отдельную ячейку для расчета. Хотя это создает избыточную ссылку на ячейку, это сохраняет возможность визуального аудита вкладки модели и снижает вероятность ошибки.

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

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

Рабочие листы

Один длинный лист лучше многих коротких

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

Не «прячьте» строки — «группируйте» их (и делайте это экономно)

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

Сохранение исходных данных (предположений) вместе (для моделей с высокой степенью детализации)

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

• Допущения → Расчеты → Результат

Преимущества:

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

Тем не менее, несмотря на эти преимущества, эта практика никогда не получила широкого распространения в инвестиционном банкинге.

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

Другая причина заключается в том, что многие модели инвестиционного банкинга просто недостаточно детализированы, чтобы заслужить дополнительный контрольный журнал и беготню. Банкиры часто проводят более широкий анализ, чем глубокий. Например, в книге презентаций может быть представлена ​​оценка с использованием 4 различных моделей оценки, но ни одна из них не будет чрезмерно детализированной. Обычный инвестиционно-банковский анализ, такой как модели разбавления приращения, модели LBO, операционные модели и модели DCF, обычно не вникают в детали, выходящие за рамки публичных заявок и базовых прогнозов. В этом случае переход назад и вперед от вкладок ввода к расчетам и выводам излишне громоздок.Если вы усердно относитесь к цветовому кодированию, размещение предположений на одном листе и непосредственно под расчетами предпочтительнее в небольших моделях, потому что ваши предположения визуально отображаются рядом с выходными данными, что позволяет легко увидеть, что движет.

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

Итог по хранению всех данных вместе

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

Между данными нет разделительных столбцов

Лифт прыгает

В длинных листах выделение крайнего левого столбца для размещения «x» или другого символа в начале расписания упростит быструю навигацию от раздела к разделу.

Годовые и квартальные данные (периодичность)

Большинство инвестиционно-банковских моделей являются ежеквартальными или годовыми. Например, модель доходов от исследований рынка акций США всегда будет квартальной, потому что одна из ее ключевых целей — прогнозировать предстоящую прибыль, о которой фирмы сообщают ежеквартально.Аналогичным образом, модель реструктуризации обычно является квартальной (или даже ежемесячной или еженедельной), потому что ключевая цель этой модели — понять влияние операционных и финансовых изменений на денежный поток в течение следующих 1-2 лет. С другой стороны, DCF-оценка — это долгосрочный анализ, требующий по крайней мере 4-5 лет четких прогнозов. В этом случае уместна годовая модель.

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

При определении периодичности модели помните следующее:

1. Модель должна быть настроена на наименьшую желаемую единицу времени , при этом более длительные периоды времени агрегируются (объединяются) из этих более коротких периодов времени. Если вы создаете интегрированную модель финансовой отчетности, в которой хотите видеть квартальные и годовые данные, сначала спрогнозируйте квартальные данные.
2. Храните квартальные и годовые данные в отдельных таблицах. Когда месячные не совпадают, легче контролировать происходящее. Кроме того, объединение квартальных и годовых данных на одном листе либо A) заставит вас нарушить передовой опыт согласованности одной строки / одной формулы, либо B) вам придется прыгать через несколько безумных обручей, чтобы поддерживать согласованность.

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

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

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

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

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

В то время как основная логика для желания включить цикличность в модель может быть действительной, проблемы цикличности могут привести к минутам, если не часам, потраченного впустую времени аудита на попытки найти источник (и) цикличности, чтобы обнулить их. Есть несколько вещей, которые могут сделать разработчики моделей, чтобы лучше справиться с цикличностью, в первую очередь создание простого автоматического выключателя, который создает центральное место в модели, которое «сбрасывает» любую ячейку, содержащую цикличность или оборачивающую формулу ловушки ошибок (ЕСЛИОШИБКА) вокруг формулы, которая является источником округлости.

Автоматический выключатель или ловушка ошибки ЕСЛИ ОШИБКА

При построении преднамеренной округлости вы ДОЛЖНЫ построить автоматический выключатель и четко обозначить все округлости в вашей модели. В нашем простом примере мы поместили автоматический выключатель в D17 и изменили формулу в D8 так, чтобы округлость обнулялась, когда пользователь переключает выключатель в положение «ON»:

Подход 1. Добавление переключателя автоматического выключателя

Альтернативный подход состоит в том, чтобы просто обернуть функцию ЕСЛИОШИБКА вокруг источника замыкания.Когда в модели происходит короткое замыкание, функция ЕСЛИОШИБКА вычисляет условие ЛОЖЬ и автоматически заполняет модель нулями. Основным недостатком этого подхода является то, что они затрудняют поиск непреднамеренных круговоротов. Это потому, что вы никогда не можете явно включить или выключить прерыватель — ЕСЛИ ОШИБКА делает это автоматически. Тем не менее, пока все круги обрабатываются функцией ЕСЛИОШИБКА, модель никогда не взорвется.

Подход 2: Добавление ловушки ошибок с помощью функции ЕСЛИОШИБКА

Итог: По кругу или не по кругу?

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

Округлость допустима только при соблюдении всех следующих условий.

1. Это сделано намеренно: Рискуя заявить очевидное, вы должны точно понимать, почему, где и как существует замкнутость. Описанный выше пример является наиболее распространенным источником замкнутости в финансовых моделях.
2. В настройках Excel выбрано «включить итерационные вычисления»: Это говорит Excel, что цикличность является преднамеренной, и гарантирует, что Excel не выдаст ошибку и не заполнит всю модель случайными нулями.
3. У вас есть автоматический выключатель или формула ловушки ошибок: Формула автоматического выключателя или ловушки ошибок гарантирует, что если файл станет нестабильным и # DIV / 0! S начнут заполнять модель, существует простой и понятный способ исправить это.
4. Модель не будет передана новичкам в Excel: Круговые формы, даже с автоматическим выключателем, могут создать путаницу для пользователей Excel, которые с ней не знакомы. Если модель, которую вы строите, будет передана клиентам (или управляющему директору), которые хотели бы изучить модель, но обычно не знакомы с Excel, избегайте цикличности и избавьте себя от головной боли.

Не использовать макросы

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

Проверка ошибок

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

1. Плохие предположения: Если ваши предположения ошибочны, результаты модели будут неверными независимо от того, насколько хорошо она структурирована.
2. Плохая структура: Даже если предположения вашей модели верны, ошибки в расчетах и ​​структуре приведут к неверным выводам.

Ключ к смягчению # 1 — представить результаты с четко определенными диапазонами предположений (сценариев и чувствительности) и сделать предположения четко определенными и прозрачными. Разбивка моделей на входные данные → расчет → выход помогает другим быстро определить и оспорить ваши предположения (подробно рассмотрено в разделе «Презентация» выше).Гораздо более пагубная ошибка моделирования — №2, потому что ее гораздо труднее найти. Как вы можете себе представить, проблема растет в геометрической прогрессии по мере увеличения детализации модели. Вот почему проверка ошибок в вашей модели является важной частью построения модели.

Сборка при проверке ошибок

Самая распространенная проверка ошибок в финансовой модели — проверка баланса — проверка по формуле, по которой активы = обязательства + собственный капитал :

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

• Обеспечение источников средств = использование средств
• Обеспечение суммирования квартальных результатов с годовыми
• Суммарные прогнозируемые амортизационные расходы не превышают ОС
• Выплата по долгу не превышает непогашенного основного долга

Отдавайте предпочтение прямым вычислениям, а не «пробкам»

Ниже мы показываем два распространенных способа, с помощью которых пользователи настраивают таблицу источников и использования средств в финансовых моделях.В обоих подходах пользователь случайно ссылается на нематериальные активы. В подходе 1 неверные данные связываются с D37. Модель замечает, что источники не равны по использованию, и выдает сообщение об ошибке в D41. Второй (и не менее распространенный) подход структурно устанавливает D52 равным D47 и использует D49 в качестве плагина, чтобы гарантировать, что источники и использование всегда равны. Как вы думаете, какой подход предпочтительнее? Если вы угадали первый подход, вы правы. Проблема второго («подключаемого») подхода заключается в том, что из-за неправильного связывания в D50 модель неправильно вычисляет сумму обеспеченных кредитов, необходимых для транзакции, , и ошибка не выявляется.

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

Совокупные проверки ошибок в одной области

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

Захват ошибок

Модели, требующие большой гибкости (шаблоны), часто содержат области, которые пользователю могут не понадобиться сейчас, но которые потребуются в будущем.Это включает в себя дополнительные позиции, дополнительные функции и т. Д. Это создает возможность для ошибки, поскольку Excel имеет дело с пустыми значениями. Такие формулы, как IFERROR (и ISERROR), ISNUMBER, ISTEXT, ISBLANK, являются полезными функциями для перехвата ошибок, особенно в шаблонах.

Подарок

Титульный лист и оглавление

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

1. Название компании и / или проекта
2. Описание модели
3. Контактная информация разработчика моделей и команды

Включите оглавление, если модель достаточно велика, чтобы соответствовать ей (хорошее эмпирическое правило — более пяти рабочих листов).

Рабочий лист дизайна

Обозначьте рабочие листы по характеру анализа (например, DCF, LBO, FinStatements и т. Д.). Вкладки должны располагаться логически слева направо. Следуя подходу «входы → расчеты → выход», раскрасьте вкладки рабочего листа в соответствии с этим делением:

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

Сценарии и чувствительность

Цель построения модели — предоставить практическую информацию, которая иначе была бы незаметна.Финансовые модели проливают свет на самые важные бизнес-решения:

• Как приобретение меняет финансовую отчетность покупателя (увеличение / разводнение)?
• Какова внутренняя стоимость компании?
• Какой вклад инвестор должен внести в проект с учетом установленных требований к доходности и допусков к риску?

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

Заключение и дополнительная литература

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

Пошаговый онлайн-курс

Все необходимое для освоения финансового моделирования

Станьте участником премиального пакета: изучите моделирование финансовой отчетности, DCF, M&A, LBO и Comps. Такая же программа обучения используется в ведущих инвестиционных банках.