Анализ «что если» с помощью диспетчера сценариев

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

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

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

Сценарии

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

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

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

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

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

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

Диспетчер сценариев - один из инструментов анализа «что, если» в Excel.

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

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

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

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

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

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

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

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

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

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

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

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

    • Назовите ячейку 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.

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

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

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

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

“No Scenarios defined. Choose Add to.”

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

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

  • Щелкните значок Add в диалоговом окне диспетчера сценариев.

В Add Scenario появится диалоговое окно.

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

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

  • Отредактируйте текст в Comment as – Initial Values коробка.

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

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

Scenario 1 с начальными значениями создается.

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

  • Щелкните значок Add в диалоговом окне Значения сценария.

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

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

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

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

В Scenario Valuesпоявится диалоговое окно. Начальные значения появляются в изменяющихся ячейках. Измените значениеInterest_Rate к 0.13 и нажмите Add.

В Add Scenarioпоявится диалоговое окно. Обратите внимание, что C3, C4, C5 появляются в поле под изменяемыми ячейками.

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

  • Отредактируйте текст в Commentкоробка как - Различных нет. платежей.

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

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

В Add Scenarioпоявится диалоговое окно. Обратите внимание, что C3, C4, C5 отображаются в поле «Изменение ячеек».

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

  • Отредактируйте текст в Comment поле как - Другой способ оплаты.

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

В Scenario Valuesпоявится диалоговое окно. Начальные значения появляются в изменяющихся ячейках. Измените значение Типа на 1. Нажмите ОК, так как вы добавили все сценарии, которые хотели добавить.

В Scenario Managerпоявится диалоговое окно. В поле под Сценариями вы найдете названия всех сценариев, которые вы создали.

  • Щелкните Сценарий 1. Как вы знаете, Сценарий 1 содержит начальные значения.
  • Теперь нажмите Summary. Откроется диалоговое окно «Сводка сценария».

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

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

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

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

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

Сводка сценария

в Result cells поле выберите ячейку C6 (Здесь мы поместили PMTфункция). Щелкните ОК.

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

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

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

  • Result Cells - Отображает указанную ячейку результата, то есть EMI.

  • Current Values - Это первый столбец, в котором перечислены значения того сценария, который выбран в диалоговом окне «Диспетчер сценариев» перед созданием сводного отчета.

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

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

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

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

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

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

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

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

  • Щелкните ДАННЫЕ> Анализ возможных вариантов> Диспетчер сценариев в главной книге.

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

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

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

Как видите, в разделе Сценарии слияния у вас есть два поля -

  • Book
  • Sheet

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

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

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

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

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

Щелкните значок Mergeкнопка. ВMerge Scenariosпоявится диалоговое окно. Теперь выберитеBank2_Scenarios из раскрывающегося списка в поле Книга.

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

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

Щелкните значок Mergeкнопка. ВMerge Scenariosпоявится диалоговое окно. Теперь выберитеBank3_Scenarios из раскрывающегося списка в поле Книга.

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

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

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

Щелкните кнопку Сводка. ВScenario Summary появится диалоговое окно.

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

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

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

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

  • Щелкните ДАННЫЕ> Анализ «Что если»> Диспетчер сценариев в группе «Инструменты для работы с данными». Откроется диалоговое окно диспетчера сценариев. Появится список сценариев.

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

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

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

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

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

  • Выберите Scenario PivotTable report в разделе Тип отчета.

  • Введите C6 в Result cells коробка.

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