Анализ «что если» с менеджером сценариев

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

• Варьируется до 32 входных наборов.
• Объединение сценариев из нескольких различных рабочих листов или рабочих книг.

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

Сценарии

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

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

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

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

• Это включает в себя информацию из всех сценариев.
• Это позволяет сравнивать сценарии бок о бок.

Менеджер сценариев

Менеджер сценариев — это один из инструментов анализа «что, если» в Excel.

Чтобы создать отчет анализа с помощью Scenario Manager, вам необходимо выполнить следующие шаги:

Шаг 1 — Определите набор начальных значений и определите входные ячейки, которые вы хотите изменить, называемые изменяющимися ячейками.

Шаг 2 — Создайте каждый сценарий, назовите сценарий и введите значение для каждой изменяющейся входной ячейки для этого сценария.

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

Менеджер сценариев создает отчет, содержащий входные и выходные значения для каждого сценария.

Начальные значения для сценариев

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

Шаги для установки начальных значений для Сценариев:

• Определите ячейки, которые содержат входные значения.
• Назовите входные ячейки соответствующим образом.
• Определите входные ячейки с постоянными значениями.
• Укажите значения для постоянных входов.
• Определите входные ячейки с изменяющимися значениями.
• Укажите начальные значения для изменяющихся входов.
• Определите ячейки, которые содержат результаты. Ячейки результата содержат формулы.
• Назовите ячейки результата соответствующим образом.
• Поместите формулы в ячейки результата.

Рассмотрим предыдущий пример кредита. Теперь действуйте следующим образом —

• Определите ячейку для суммы кредита.

  • Это входное значение является постоянным для всех сценариев.

  • Назовите ячейку Loan_Amount.

  • Укажите значение как 5 000 000.

• Определите ячейки для процентной ставки, количества платежей и типа (платеж в начале или конце месяца).

  • Эти входные значения будут меняться в зависимости от сценария.

  • Назовите ячейки Interest_Rate, NPER и Type.

  • Укажите начальные значения для анализа в этих ячейках как 12%, 360 и 0 соответственно.

• Определите ячейку для EMI.

  • Это значение результата.

  • Назовите ячейку EMI.

  • Поместите формулу в эту ячейку как —

    = PMT (Interest_Rate / 12, NPER, Loan_Amount, 0, Type)

Определите ячейку для суммы кредита.

Это входное значение является постоянным для всех сценариев.

Назовите ячейку Loan_Amount.

Укажите значение как 5 000 000.

Определите ячейки для процентной ставки, количества платежей и типа (платеж в начале или конце месяца).

Эти входные значения будут меняться в зависимости от сценария.

Назовите ячейки Interest_Rate, NPER и Type.

Укажите начальные значения для анализа в этих ячейках как 12%, 360 и 0 соответственно.

Определите ячейку для EMI.

Это значение результата.

Назовите ячейку EMI.

Поместите формулу в эту ячейку как —

= PMT (Interest_Rate / 12, NPER, Loan_Amount, 0, Type)

Ваш рабочий лист выглядит так, как показано ниже —

Как видите, входные ячейки и результирующие ячейки находятся в столбце C с именами, указанными в столбце D.

Создание сценариев

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

• Нажмите вкладку ДАННЫЕ на ленте.
• Нажмите «Что, если анализ» в группе «Инструменты данных».
• Выберите Scenario Manager из выпадающего списка.

Откроется диалоговое окно Scenario Manager. Вы можете заметить, что он содержит сообщение —

«Сценарии не определены. Выберите Добавить в. ”

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

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

• Нажмите кнопку « Добавить» в диалоговом окне «Диспетчер сценариев».

Откроется диалоговое окно « Добавить сценарий ».

• В поле Имя сценария введите Сценарий 1.
• В разделе «Изменение ячеек» введите ссылки для ячеек, то есть C3, C4 и C5, с нажатой клавишей Ctrl.

Имя диалогового окна изменится на Редактировать сценарий.

• Отредактируйте текст в поле Комментарий как — Начальные значения .

• Выберите опцию Запретить изменения в разделе Защита и нажмите кнопку ОК.

Отредактируйте текст в поле Комментарий как — Начальные значения .

Выберите опцию Запретить изменения в разделе Защита и нажмите кнопку ОК.

Откроется диалоговое окно « Значения сценария ». Исходные значения, которые вы определили, появляются в каждом из полей изменяющихся ячеек.

Сценарий 1 с начальными значениями создан.

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

• Нажмите кнопку « Добавить» в диалоговом окне «Значения сценария».

Откроется диалоговое окно «Добавить сценарий». Обратите внимание, что C3, C4, C5 появляются в поле «Изменение ячеек».

• В поле Имя сценария введите Сценарий 2.

• Отредактируйте текст в комментарии как — Другая процентная ставка.

• Выберите «Запретить изменения» в разделе «Защита» и нажмите «ОК».

В поле Имя сценария введите Сценарий 2.

Отредактируйте текст в комментарии как — Другая процентная ставка.

Выберите «Запретить изменения» в разделе «Защита» и нажмите «ОК».

Откроется диалоговое окно « Значения сценария ». Начальные значения появляются в изменяющихся ячейках. Измените значение Interest_Rate на 0,13 и нажмите Добавить .

Откроется диалоговое окно « Добавить сценарий ». Обратите внимание, что C3, C4, C5 отображаются в поле под изменяющимися ячейками.

• В поле Имя сценария введите Сценарий 3.

• Отредактируйте текст в поле для комментариев как — Разное нет. платежей.

• Выберите «Запретить изменения» в разделе «Защита» и нажмите «ОК».

В поле Имя сценария введите Сценарий 3.

Отредактируйте текст в поле для комментариев как — Разное нет. платежей.

Выберите «Запретить изменения» в разделе «Защита» и нажмите «ОК».

Откроется диалоговое окно «Значения сценария». Начальные значения появляются в изменяющихся ячейках. Измените значение NPER на 300 и нажмите Добавить .

Откроется диалоговое окно « Добавить сценарий ». Обратите внимание, что C3, C4, C5 появляются в поле «Изменение ячеек».

• В поле Имя сценария введите Сценарий 4.

• Измените текст в поле для комментариев как — Другой тип оплаты.

• Выберите «Запретить изменения» в разделе «Защита» и нажмите «ОК».

В поле Имя сценария введите Сценарий 4.

Измените текст в поле для комментариев как — Другой тип оплаты.

Выберите «Запретить изменения» в разделе «Защита» и нажмите «ОК».

Откроется диалоговое окно « Значения сценария ». Начальные значения появляются в изменяющихся ячейках. Измените значение Type на 1. Нажмите OK, поскольку вы добавили все сценарии, которые вы хотели добавить.

Откроется диалоговое окно диспетчера сценариев . В поле «Сценарии» вы найдете имена всех созданных вами сценариев.

• Нажмите Сценарий 1. Как вы знаете, сценарий 1 содержит начальные значения.



Теперь нажмите Сводка . Откроется диалоговое окно «Сценарий».

Теперь нажмите Сводка . Откроется диалоговое окно «Сценарий».

Сводные отчеты по сценариям

Excel предоставляет два типа сводных отчетов по сценариям:

• Краткое содержание сценария.
• Сценарий сводной таблицы.

В диалоговом окне Сводка сценария вы можете найти эти два типа отчетов.

Выберите «Сценарий» в разделе «Тип отчета».

Краткое описание сценария

В поле Ячейки результата выберите ячейку C6 (здесь мы поместили функцию PMT ). Нажмите ОК.

Сводный отчет по сценарию появится в новом листе. Рабочий лист называется Сводка сценария.

Вы можете наблюдать следующее в Сводном отчете по сценарию:

• Changing Cells — Включает в себя все ячейки, используемые в качестве меняющихся ячеек. Поскольку вы назвали ячейки Interest_Rate, NPER и Type, они, кажется, делают отчет значимым. В противном случае будут перечислены только ссылки на ячейки.

• Ячейки результата — отображает указанную ячейку результата, то есть EMI.

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

• Для всех созданных вами сценариев изменяющиеся ячейки будут выделены серым цветом.

• В строке EMI ​​будут отображены значения результатов для каждого сценария.

Changing Cells — Включает в себя все ячейки, используемые в качестве меняющихся ячеек. Поскольку вы назвали ячейки Interest_Rate, NPER и Type, они, кажется, делают отчет значимым. В противном случае будут перечислены только ссылки на ячейки.

Ячейки результата — отображает указанную ячейку результата, то есть EMI.

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

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

В строке EMI ​​будут отображены значения результатов для каждого сценария.

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

• Нажмите кнопку + слева от строки, содержащей имена сценариев. Комментарии к сценариям появляются в строке под именами сценариев.

Нажмите кнопку + слева от строки, содержащей имена сценариев. Комментарии к сценариям появляются в строке под именами сценариев.

Сценарии из разных источников

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

• Предположим, что сценарии находятся в рабочих книгах, Bank1_Scenarios, Bank2_Scenarios и Bank3_Scenarios. Откройте три рабочие книги.

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

• Нажмите ДАННЫЕ> Анализ «что если»> «Менеджер сценариев» в основной книге.

Предположим, что сценарии находятся в рабочих книгах, Bank1_Scenarios, Bank2_Scenarios и Bank3_Scenarios. Откройте три рабочие книги.

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

Нажмите ДАННЫЕ> Анализ «что если»> «Менеджер сценариев» в основной книге.

Откроется диалоговое окно Scenario Manager .

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

Откроется диалоговое окно «Сценарии объединения».

Как вы можете видеть, в сценариях слияния из, у вас есть два поля —

• Книга
• Простынь

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

Примечание . Соответствующие рабочие книги должны быть открыты для отображения в этом списке.

Выберите книгу — Bank1_Scenarios .

Bank1 лист отображается. В нижней части диалогового окна отображается количество сценариев, найденных на исходном листе. Нажмите ОК.

Откроется диалоговое окно диспетчера сценариев. Два сценария, которые были объединены в Основную рабочую книгу, будут перечислены в разделе Сценарии.

Нажмите кнопку Объединить . Откроется диалоговое окно « Сценарии объединения». Теперь выберите Bank2_Scenarios из выпадающего списка в поле Book.

Лист Bank2 исчез. В нижней части диалогового окна отображается количество сценариев, найденных на исходном листе. Нажмите ОК.

Откроется диалоговое окно Scenario Manager . Четыре сценария, которые были объединены в Главную рабочую книгу, перечислены в разделе Сценарии.

Нажмите кнопку Объединить . Откроется диалоговое окно « Сценарии объединения». Теперь выберите Bank3_Scenarios из выпадающего списка в поле Book.

Bank3 лист отображается. В нижней части диалогового окна будет отображено количество сценариев, найденных на исходном листе. Нажмите ОК.

Откроется диалоговое окно Scenario Manager. Пять сценариев, которые были объединены в Главную рабочую книгу, будут перечислены в разделе Сценарии.

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

Нажмите кнопку Сводка. Откроется диалоговое окно « Сценарий ».

• Выберите Сценарий.
• В поле Ячейки результата введите C6 и нажмите кнопку ОК.

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

Отображение сценариев

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

• Нажмите DATA> Анализ «что если»> «Менеджер сценариев» в группе «Инструменты данных». Откроется диалоговое окно Scenario Manager. Появится список сценариев.

• Выберите сценарий, который вы хотите отобразить. Нажмите Показать .

Нажмите DATA> Анализ «что если»> «Менеджер сценариев» в группе «Инструменты данных». Откроется диалоговое окно Scenario Manager. Появится список сценариев.

Выберите сценарий, который вы хотите отобразить. Нажмите Показать .

Значения на рабочем листе обновляются в соответствии с выбранным сценарием. Значения результата пересчитываются.

Отчет по сводной таблице сценариев

Отчет о сценарии можно также увидеть в форме сводной таблицы.

• Нажмите кнопку «Сводка» в диалоговом окне « Диспетчер сценариев» . Откроется диалоговое окно «Сценарий».

• Выберите отчет Сводная таблица сценариев в разделе Тип отчета.

• Введите C6 в поле Ячейки результата .

Нажмите кнопку «Сводка» в диалоговом окне « Диспетчер сценариев» . Откроется диалоговое окно «Сценарий».

Выберите отчет Сводная таблица сценариев в разделе Тип отчета.

Введите C6 в поле Ячейки результата .

Отчет Сводная таблица сценариев появится на новом листе.