Как построить выпадающие сценарии в вашей финансовой модели - манекены

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

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

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

Загрузить файл 0801. xlsx. Откройте его и выберите вкладку с надписью 8-1-start.

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

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

Возьмите загруженную модель и вырежьте и вставьте описания из столбца C в столбец F. Вы можете сделать это выделив ячейки C6: C8, нажав Ctrl + X, выбрав ячейку F6 и нажав Enter.
Входы в ячейках B3-B8 являются активным диапазоном, который управляет моделью и останется таким. Однако они должны стать формулами, которые изменяются в зависимости от раскрывающегося списка, которое вы создадите.
Скопируйте диапазон в столбце B в столбцы C, D и E.
Вы можете сделать это, выделив B3: B8, нажав Ctrl + C, выбрав ячейки C3: E3 и нажав Enter. Эти суммы будут одинаковыми для каждого сценария, пока вы их не измените.
В строке 2 введите заголовки Best Case , Base Case , и худший случай.
Настройка модели для анализа сценариев.
Обратите внимание, что формулы все еще ссылаются на входы в столбце B, как вы можете видеть, выбрав ячейку C12 и нажав клавишу быстрого доступа F2.
Отредактируйте входы под каждым сценарием.
Вы можете поместить все, что вы считаете вероятным, но чтобы соответствовать номерам тем, которые приведены в этом примере, введите значения. Игнорировать столбец B на данный момент.

Входы для анализа сценариев.
Теперь вам нужно добавить раскрывающийся список вверху, который будет управлять вашими сценариями. На самом деле не имеет значения, куда именно вы помещаете раскрывающийся список, но он должен находиться в месте, которое легко найти, обычно в верхней части страницы.
В ячейке E1 введите название Scenario .
Выберите ячейку F1 и измените форматирование на вход, чтобы пользователь мог видеть, что эта ячейка доступна для редактирования.
Самый простой способ сделать это - выполнить следующие шаги:
1. Щелкните одну из ячеек, которые уже отформатированы как входные данные, например ячейка E3.
2. Нажмите значок «Формат» в разделе «Буфер обмена» в левой части вкладки «Главная». Ваш курсор изменится на кисть.
3. Выберите ячейку F1, чтобы вставить форматирование.
  Формат Painter обычно предназначен для одноразового использования. После того, как вы выбрали ячейку, кисть исчезнет из курсора. Если вы хотите, чтобы Format Painter стал «липким» и применил к нескольким ячейкам, дважды щелкните значок, когда вы выберете его со вкладки «Главная».
Теперь в ячейке F1 выберите «Проверка данных» в разделе «Инструменты данных» на вкладке «Данные».
Появится диалоговое окно «Проверка данных».
На вкладке «Настройки» измените раскрывающийся список «Разрешить» на «Список», с помощью мыши выберите диапазон = $ C $ 2: $ E $ 2 и нажмите «ОК».
Создание раскрывающихся сценариев проверки данных.
Выберите раскрывающийся список, который теперь отображается рядом с ячейкой F1, и выберите один из сценариев (например, базовый пример).

Применение формул к сценариям

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

Выберите ячейки B3: B8 и выберите цвет заливки в группе «Шрифт» на вкладке «Главная».
Измените цвет заливки на белый фон.
Очень важно различать формулы и входные ячейки в модели. Вы должны дать понять любому пользователю, открывающему модель, что ячейки в этом диапазоне содержат формулы и не должны быть переопределены.

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

Выберите ячейку B3 и добавьте формулу, которая изменит значение в зависимости от того, что находится в ячейке F1.
Вот формула будет под разными опциями:
- = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, 2, 0)
  Обратите внимание, что с этим решением вам нужно изменить номер индекса строки от 2 до 3 и т. д. при копировании формулы вниз. Вместо этого вы можете использовать функцию ROW в третьем поле: = HLOOKUP ($ F $ 1, $ C $ 2: $ E $ 8, ROW (A3) -1, 0)
- = IF ($ F $ 1 = $ C $ 2, C3, IF ($ F $ 1 = $ D $ 2, D3, E3))
- = IFS ($ F $ 1 = $ C $ 2, C3, $ F $ 1 = $ D $ 2, D3, $ F $ 1 = $ E $ 2, E3)
- = SUMIF ($ C $ 2: $ E $ 2, $ F $ 1, C3: E3)
  Как всегда, есть несколько различных вариантов выбора, и наилучшим решением является тот, который является самым простым и легким для понимания.Любая из этих функций даст точно такой же результат, но изменение номера строки в HLOOKUP не является надежным, и добавление ROW может ввести в заблуждение для пользователя. Вложенный оператор IF сложно построить и следовать, и хотя новая функция IFS предназначена для упрощения вложенной функции IF, она по-прежнему довольно громоздка. SUMIF довольно просто построить и следовать, и его легко расширить, если вам нужно добавить дополнительные сценарии в будущем.
  
  Обратите внимание, что IFS - это новая функция, доступная только с установленными Office 365 и Excel 2016. Если вы используете эту функцию, и кто-то открывает эту модель в предыдущей версии Excel, она может просмотреть формулу, но она не сможет ее отредактировать.
Скопируйте формулу в ячейке B3 по столбцу.
Завершенный сценарийный анализ.
Используя обычную копию и вставку, вы потеряете все свое форматирование. Важно сохранить форматирование модели, чтобы вы могли сразу увидеть, какие входы находятся в долларовых значениях, процентах или числах клиентов. Используйте формулу Вставить, чтобы сохранить форматирование. Вы можете получить к нему доступ, скопировав ячейку в буфер обмена, выделив диапазон назначения, щелкнув правой кнопкой мыши и выбрав значок «Вставить формулу», чтобы вставлять только формулы и оставить форматирование неповрежденным.

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