Как провести анализ чувствительности в Excel

Обновлено: 24.11.2024

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

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

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

Вот подробности об ипотеке. Мой друг планировал занять 200 000 долларов, а процентная ставка составляет 3,40%. Срок ипотеки составляет 30 лет, что означает 360 месяцев.

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

У меня есть все необходимые входные данные — сумма ипотеки, процентная ставка и месяцы — в разделе 1 и таблица данных в разделе 2.

Изначально я ввел «=PMT (C3/12, C4, C2)» в ячейку C7, и вы видите, что мой друг должен платить 886,96 долларов США каждый месяц. Затем в столбец B из ячейки B8 (показано ниже) был проставлен диапазон суммы ипотечного кредита.

Использование односторонней (переменной) таблицы данных () для анализа чувствительности

Пришло время создать таблицу.

Я выбрал диапазоны таблицы (в данном случае ячейки B7: C14), которые начинаются на одну строку выше первого входного значения (строка 7), а последней строкой является строка, содержащая последнее введенное значение (строка 14).

Первый столбец диапазона — это столбец, содержащий входные данные (столбец B), а последний — столбец, содержащий выходные данные (столбец C). Затем щелкните вкладку «Данные» на ленте, выберите «Анализ «что, если», а затем нажмите «Таблица данных» (подробности см. ниже).

Диалоговое окно "Таблица данных" (показано ниже), которое открывается после нажатия на "Таблица данных". Поскольку наша таблица ориентирована на столбцы, я просто помещаю ссылку на ячейку ввода столбца — $C$2 — и оставляю ячейку ввода строки пустой.

Нажав OK, я получил следующие результаты. Когда сумма ипотеки уменьшится с 200 000 до 140 000 долларов, ежемесячный платеж уменьшится с 886,96 до 620,87 долларов. Вы также можете видеть, что формула для ячейки C8 такова. Эта формула выполняется для диапазона таблицы данных, и она будет отображаться во всех ячейках в пределах диапазона таблицы данных, который идет от ячейки C8 до ячейки C14.

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

Двусторонняя (переменная) таблица данных для анализа чувствительности

Мы также хотели бы знать, как меняется ежемесячный платеж, поскольку общая сумма ипотечного кредита варьируется от 140 000 до 260 000 долларов США (с шагом 20 000 долларов США), а продолжительность срока варьируется от 5 лет до 35 лет (с шагом 5 лет).

И в этом случае мы изменили два входа, и поэтому мы создали ниже двустороннюю таблицу данных.

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

Есть один момент, о котором я должен вам напомнить. Двусторонняя таблица данных может иметь только одну выходную ячейку, а формула для вывода должна быть помещена в верхний левый угол (в данном случае B7) диапазона таблицы. Поэтому мы поместили формулу — «=PMT (C3/12, C4, C2)» — в ячейку B7.

Как и в случае с односторонней таблицей, мы выбрали диапазон таблицы (ячейки B7: I14) и открыли вкладку "Данные". В группе «Инструменты данных» выберите «Анализ «что если», а затем выберите «Таблица данных». Поскольку есть два источника неопределенности, и поэтому мне нужно заполнить как ячейку ввода строк, так и ячейку ввода столбца.В нашем случае ячейка C2 (сумма ипотеки) — это ячейка ввода столбца, а ячейка C4 (длина срока ипотеки) — ячейка ввода строки.

Таким образом, заполнены значения «$C$2» и «$C$4», как показано на снимке экрана ниже.

После нажатия кнопки "ОК" вы увидите двустороннюю таблицу данных, как показано ниже. Например, в ячейке C8, когда сумма ипотечного кредита составляет 140 000 долларов США, а срок — 5 лет или 60 месяцев, ежемесячный платеж равен 2 540,58 долларов США. Вы заметили, что формула для ячейки C8 имеет вид ? Эта формула не соответствует формуле, которую вы заполнили в диалоговом окне «Таблица данных», по сравнению с приведенным выше рисунком, верно? Вы также можете сравнить ее с предыдущей формулой (<=TABLE(, C2)>), используемой в односторонней таблице, чтобы понять принцип и получить более глубокое понимание.

Внимание! Несколько заметок для вас

Наконец, у меня есть для вас несколько замечаний:

Поскольку вы изменяете входные значения на листе, значения, рассчитанные таблицей данных, также изменяются. Например, если вы увеличите процентную ставку с 3,40% до 4,00%, ежемесячный платеж составит 2578 долларов (число в синем квадрате) при пятилетней ипотеке на сумму 120 000 долларов. При сравнении с приведенным выше рисунком на приведенном ниже снимке экрана видно, что все значения в этой таблице данных с двумя переменными были изменены.
Вы не можете удалить или изменить часть таблицы данных. Если вы выберете ячейку в диапазоне таблицы данных и случайно отредактируете, в файле Excel появится предупреждающее сообщение, и вы больше не сможете сохранить, изменить или даже закрыть файл. Единственный способ закрыть его — завершить задачу из управления задачами. Это означает, что ваше время будет потрачено впустую, если вы не сохранили файл до совершения этой ошибки.
Хотя односторонняя таблица, ориентированная на столбцы (которая также применяется в этом посте), широко используется, вы также можете самостоятельно создать свою собственную одностороннюю таблицу, ориентированную на строки. Просто поместите входные данные в одну строку и заполните только ссылку на ячейку ввода строки в диалоговом окне таблицы данных.
При создании двусторонней таблицы данных не путайте ячейку ввода строки и ячейку ввода столбца. Такая ошибка может привести к большой ошибке и бессмысленным результатам.
Автоматическое вычисление включено по умолчанию, поэтому любое изменение входных данных может привести к пересчету всех данных в таблице данных. Это фантастическая функция. Однако иногда мы хотели бы отключить эту функцию, особенно когда таблицы данных большие, а автоматический пересчет выполняется очень медленно. Как в этой ситуации отключить автоматический расчет? Просто щелкните вкладку «Файл» на ленте, выберите «Параметры», а затем щелкните вкладку «Формулы» и выберите «Автоматически, кроме таблиц данных». Теперь все ваши данные в таблице данных будут пересчитываться только при нажатии клавиши F9 (пересчет).

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

В этом примере мы рассмотрим изменение силы в простой колонне фиксированной длины, которая подвергается изменениям температуры. Изменение силы описывается уравнением:

Результат для одного набора входных данных показан ниже:

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

Таблицы данных Excel для анализа чувствительности с одной переменной

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

В ячейке непосредственно выше и правее вычислите результат или укажите ссылку на ячейку, которая уже содержит результат.

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

Откройте инструмент "Таблица данных" в меню "Данные" > "Анализ "что если"" > "Таблицы данных".

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

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

Таблицы данных с двумя переменными

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

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

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

Затем выберите все строки и столбцы в таблице и откройте инструмент "Таблица данных". Поскольку строки содержат данные о переменном коэффициенте теплового расширения, выберите эту входную ячейку в качестве «ячейки ввода строки». И, как и раньше, выберите ячейку ввода температуры в качестве ячейки ввода столбца, поскольку столбец содержит данные о температуре.

Нажмите «ОК», и таблица будет завершена, что позволит нам увидеть анализ чувствительности Excel, расчет силы как к температуре, так и к коэффициенту теплового расширения:

Использование таблиц данных для проведения анализа чувствительности в Excel

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

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

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

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

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

Что делать, если результаты финансовой модели не являются окончательными? Разве не для этого вы в первую очередь строите модель — чтобы получить некоторую ясность или ответ относительно будущей эффективности бизнеса? И да и нет. Цель финансовой модели — дать некоторое представление о будущих результатах, но единого правильного ответа нет. Клиентам и управляющим директорам нравится видеть диапазон возможных результатов, и именно здесь в игру вступает анализ чувствительности или анализ «что, если».

Бесконечные возможности

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

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

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

Прежде чем мы начнем… Загрузите бесплатную таблицу данных

Используйте форму ниже, чтобы загрузить образец таблицы данных:

Построение таблицы данных

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

Построение матрицы

В ячейке рабочего листа укажите формулу, относящуюся к двум входным ячейкам, которые мы хотели бы сделать более чувствительными. В ячейке D208 указана прибыль на акцию за 2009 год.
Введите один список входных значений в том же столбце под формулой. В этом примере мы ввели ряд предположений о росте дохода.
Введите второй список в той же строке справа от формулы. В этом примере мы ввели ряд предположений о марже EBIT.
Выделите диапазон ячеек, содержащий формулу, а также строку и столбец значений. В приведенном ниже примере вы должны выбрать диапазон D208:I214.
Нажмите клавиши Alt-D-T на клавиатуре. Это откроет окно «Таблица данных», как показано справа от таблицы данных ниже. Примечание. Этот «ярлык» работает как в Excel 2003, так и в 2007, хотя альтернативой может быть нажатие Alt-AWT для версии 2007 года. , который направит вас к окну таблицы данных через меню "Анализ возможных вариантов".
В поле "Ячейка ввода строки" введите ссылку на ячейку ввода для входных значений в строке. В приведенном ниже примере вы должны ввести ячейку E35 в поле ввода строки.
В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце. В приведенном ниже примере введите E33 в поле ввода столбца.
Нажмите "ОК"!

Получение результатов!

Наконец-то мы получим различные разбавленные результаты EPS, как показано в ячейках с E209 по I214 в таблице данных.Осталось только проверить результаты на вменяемость. По мере роста выручки мы должны увидеть увеличение разводненной прибыли на акцию, и мы это наблюдаем. Мы также должны увидеть увеличение разводненной прибыли на акцию по мере улучшения маржи EBIT, что мы и наблюдаем. Похоже, мы создали хорошо функционирующую таблицу данных!

Следует знать, что иногда Excel настроен на автоматический расчет, за исключением таблиц данных. Если кажется, что ваша таблица данных не работает, попробуйте нажать «F9», чтобы пересчитать весь рабочий лист. Вы также можете настроить настройку Excel, нажав Alt-TO, а затем перейдя на вкладку «Расчеты» в Excel 2003 или раздел «Формулы» в Excel 2007. Вы также можете нажать Alt-MX в Excel 2007, чтобы сделать свой выбор. .

Некоторые заключительные мысли

Таблица данных – это эффективный и простой способ представить ценную финансовую информацию начальнику или клиенту. Он предоставляет ряд возможных результатов для конкретной части информации и может выделить запас прочности, который может существовать до того, как что-то пойдет не так. Например, насколько низким может стать рост выручки или маржа EBIT, прежде чем прибыль на акцию станет отрицательной? Как только вы создадите несколько таблиц данных, вы поймете, что это совсем не требует времени и что нет оправдания тому, что вы не включили их в свой арсенал финансового моделирования.

Анализ чувствительности — это инструмент, используемый в финансовом моделировании Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем строить модель. для анализа того, как различные значения набора независимых переменных влияют на конкретную зависимую переменную при определенных конкретных условиях. Как правило, анализ чувствительности используется в самых разных областях: от биологии и географии до экономики и техники.

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

Анализ «что, если»

Анализ финансовой чувствительности, также известный как анализ «что, если» или имитационное упражнение «что, если», чаще всего используется финансовыми аналитиками. The Analyst Trifecta® Guide Полное руководство о том, как стать финансовым аналитиком мирового класса. Хотите стать финансовым аналитиком мирового класса? Вы хотите следовать лучшим отраслевым практикам и выделяться из толпы? Наш процесс под названием The Analyst Trifecta® состоит из аналитики, презентации и социальных навыков, чтобы предсказать результат определенного действия при выполнении в определенных условиях.

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

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

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

Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

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

Средняя цена упаковки рождественских украшений – 20 долларов США.Во время праздничного сезона прошлого года компания HOLIDAY CO продала 500 упаковок рождественских украшений, в результате чего общий объем продаж составил 10 000 долларов США.

Проведя анализ финансовой чувствительности, Джон определил, что увеличение количества покупателей в торговом центре на 10 % приводит к увеличению количества продаж на 7 %.

Используя эту информацию, Джон может предсказать, сколько денег заработает компания XYZ, если трафик клиентов увеличится на 20 %, 40 % или 100 %. Согласно анализу финансовой чувствительности Джона, такое увеличение трафика приведет к увеличению дохода на 14 %, 28 % и 70 % соответственно.

Узнайте, как построить подобную таблицу, в нашем бесплатном ускоренном курсе Excel Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. !

Загрузить бесплатный шаблон

Введите свое имя и адрес электронной почты в форму ниже и загрузите бесплатный шаблон прямо сейчас!

Читайте также: