Что заполнить в окне значение ячеек сценария

Решение задачи с помощью Диспетчера сценариев

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

Задача. Фирма желает взять ссуду в банке в размере 1 500 000 руб. под 9, 2% годовых на 10 лет. Определить размер ежемесячных выплат и общую сумму выплат. После этого проанализировать, как влияет изменение процентной ставки и срока кредита на ежемесячные выплаты.

1. Создайте таблицу следующего вида:

Рис. 35. Таблица к задаче о ссуде

2. Установите курсор в ячейку С6.

3. Вставьте в ячейку С6 функцию ПЛТ и заполните ее параметры (Рис. 36). Так как платежи должны быть ежемесячными, то ячейку С4 надо разделить на 12, а ячейку С5 умножить на 12. В поле ПС ячейку С3 указали с минусом, чтобы результат функции был не отрицательным.

Рис. 36. Параметры функции ПЛТ

4. В ячейку С8 введите формулу =C5*C6*12.

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

1. Создадим три сценария. Для создания сценария выберите команду Сервис — Сценарии.

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

3. В поле Изменяемые ячейки укажите диапазон $C$3: $C$5. Т.е. все исходные данные могут изменяться.

4. Защитим сценарий от изменений. Флажок Запретить изменения означает, что никто, кроме вас, не сможет изменить сценарий. Скрыть означает, что он не появится в списке сценариев. В итоге у вас будут заданы все параметры, требуемые для расчета (Рис. 37).

Рис. 37. Сценарий Минимум

5. Щелкнув на кнопке ОК, вы увидите окно диалога, отображающее изменяемые ячейки и их текущее содержание. Измените их следующим образом:

· ячейка С3 – 1 000 000

· ячейка С4 – 0, 05

· ячейка С5 – 20.

Т.е. окно будет иметь следующий вид:

Рис. 38. Значения ячеек сценария Минимум

6. Нажмите кнопку ОК. Вы вернетесь в исходное окно.

7. Для создания следующего сценария, щелкните на кнопке Добавить. Появится окно диалога Добавление сценария.

8. Введите название Желаемые значения и щелкните на ОК. В окне диалога Значения ячеек сценария нужно ввести новые значения:

· ячейка С3 – 1 500 000

· ячейка С4 – 0, 1

· ячейка С5 – 15.

9. Создайте третий сценарий с именем – Максимум. Для него укажите следующие данные:

· ячейка С3 – 2 000 000

· ячейка С4 – 0, 05

· ячейка С5 – 10.

10. После последнего набора чисел нужно щелкнуть на кнопке ОК, чтобы вернуть на экран окно Диспетчер сценариев, на этот раз содержащее список из трех созданных сценариев.

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

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

13. Создав отчеты по сценариям, можно сравнить переменные и результаты на одном рабочем листе. Для этого вызовите Диспетчер сценариев (Сервис – Сценарии).

14. Щелкните на кнопке Отчет. В появившемся окне установите флажок Структура и щелкните на ОК (Рис. 39).

Рис. 39. Создание отчета по сценариям

15. После завершения диалога EXCEL создаст новый рабочий лист Структура сценария и представит отчет, который можно распечатать и проанализировать (Рис. 40).

Рис. 40. Итоговый отчет по сценариям

Диспетчер сценариев для анализа прогнозной модели

Признаком качественно выполненной прогнозной модели является наличие анализа чувствительности параметров модели. Как результирующий итог модели (например, внутренняя норма доходности – IRR или объем инвестиций), поведет себя при том или ином изменении исходных посылок? Если итог получен в результате сложных вычислений, то влияние отдельных параметров очень удобно оценивать с помощью анализа что если. Однако, этот инструмент удобен, когда нужно проанализировать влияние на результат одного или двух параметров. Если одновременно необходимо изучить влияние более чем двух параметров, воспользуйтесь диспетчером сценариев.[1] Диспетчер сценариев позволяет выполнить анализ чувствительности с возможностью изменения до 32 значений в ячейках с исходными данными.

Рис. 1. Данные, на которых основаны сценарии

Скачать заметку в формате Word или pdf, примеры в формате Excel

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

Рис. 2. Модель, на которой основаны сценарии

Для определения наиболее благоприятного сценария откройте вкладку ДАННЫЕ и в группе Работа с данными в раскрывающемся списке Анализ «что если» выберите инструмент Диспетчер сценариев. Нажмите кнопку Добавить и заполните поля в диалоговом окне Добавление сценария (рис. 3). Введите имя сценария и выберите ячейки В2:В4, как ячейки с исходными данными, содержащие определяющие сценарий значения. Нажмите кнопку OK и в открывшемся диалоговом окне Значения ячеек сценария заполните поля входными значениями, определяющими наиболее благоприятный вариант (рис. 4).

Рис. 3. Исходные данные для наиболее благоприятного сценария

Рис. 4. Определение исходных значений для наиболее благоприятного сценария

В диалоговом окне Значение ячеек сценария нажмите кнопку Добавить, и аналогичным образом введите данные для наиболее вероятного и наименее благоприятного сценариев. После ввода данных для всех трех сценариев в диалоговом окне Значение ячеек сценария нажмите ОК. Вы вернетесь в окно Диспетчер сценариев (рис. 5). Сейчас в нем отражены все три сценария. Нажмите кнопку Отчет. Выберите ячейки с конечными результатами, которые должны отображаться в отчетах по сценариям (рис. 6). Для отслеживания выбраны значения прибыли за каждый год после уплаты налогов (ячейки B18: F18) и значение чистой приведенной стоимости (ячейка B20). Так как ячейки с результатами B18:F18 и B20 находятся в несмежных диапазонах, их следует перечислить через точку с запятой. Также несколько диапазонов ячеек можно выбрать и внести при нажатой клавише . Установите переключатель Тип отчета в положение структура, и нажмите кнопку OK. В книге Excel будет создан отчет Структура сценария (рис. 7).

Рис. 5. Диспетчер сценариев

Рис. 6. Диалоговое окно Отчет по сценарию для выбора в отчет ячеек с результатами

Рис. 7. Отчет по сценариям

Обратите внимание, что в отчет включен столбец, помеченный как Текущие значения, для изначально указанных на листе значений. В наименее благоприятном сценарии компания несет убытки (в размере 13 346 долларов), в наиболее благоприятном — получает прибыль (в размере 226 893 долларов). Так как в наименее благоприятном сценарии цена ниже переменных затрат, компания теряет деньги каждый год.

Некоторые замечания

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

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

Если в диалоговом окне Диспетчер сценариев выбрать один из сценариев и нажать кнопку Вывести, на листе с моделью (рис. 9) появятся значения входных ячеек для выбранного сценария, и все формулы будут автоматически пересчитаны для выбранного сценария. Этот инструмент отлично подходит для подготовки презентации. Ctrl+Z отменяет работу сценария, и возвращает лист в исходное состояние.

Рис. 9. На лист с моделью выведены расчет для наиболее благоприятного сценария

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

Как и в любой структуре данных при нажатии на знак «минус» (–) в строках 5 и 9 отчета Структура сценария (см. рис. 7) строки с предполагаемыми значениями скрываются, а отображаются только результаты. При нажатии на знак «плюс» (+) отчет восстанавливается в полном объеме.

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

Excel works!

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

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

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

Сценарии E xcel могут применяться для прогнозов результатов моделей расчета листа.

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

Сценарии Excel. С помощью диспетчера сценариев возможно:

1. создавать сразу множество различных сценариев (каждый может иметь не более 32 значений для изменений),
2. присваивать имена сценариям,
3. выполнять и сохранять сценарии листов,
4. защищать сценарии от всевозможных изменения,
5. скрывать сценарии,
6. отслеживать изменения сценариев,
7. создавать итоговые расчеты,
8. объединять вместе сценарии.

Сценарием называют именованную совокупность данных изменяемых ячеек. Отметим, что для ячеек аргументов функций можно задать разные значения. С помощью команды Меню Данные — группа Работа с данными — Анализ «что-если» — можно вызвать диалоговое окно «Диспетчер сценариев», для значений ячеек текущего листа E xcel. Как показано на первом рисунке

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

Чтобы создать новый сценарий, нужно кликнуть по кнопке «Добавить», после чего появится новое диалоговое окно.

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

Последний показатель — автора, можно изменить, используя команду Сервис -> Параметры, графа «Общие», поле «Имя пользователя».

Переключатель «Запретить изменения» реализует защиту данных изменяемых ячеек приложения от какого-либо редактирования. Устанавливая флажок переключателя «Скрыть», можно добиться того, что имя сценария не будет показываться в списке. Нажимая на «ОК» вы увидите диалоговое окно, с помощью которого можно будет ввести значения изменяемых ячеек.

Просмотр результатов работы сценария

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

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

Нажимая кнопку «Закрыть», можно выйти из «Диспетчера сценариев», причем в редактируемых ячейках сохранятся значения последнего сценария, что участвовал в просмотре. Клавиша «Отчет» позволяет получать необходимые отчеты по сценариям. Вы можете выбрать нужный тип итогового отчета.

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

Можно наблюдать два типа отчетов:

1. итоги сценариев – отчет — таблица, где содержатся составы изменяемых ячеек для каждого сценария.
2. свободная таблица, где отображаются результаты изменения ячеек листа,

Похожие статьи

• 05.06.2018Вертикальное написание текста в Word и ExcelPosted in Оформление
• 20.02.2018Как правильно и быстро очистить ячейки в Excel?Posted in Работа с текстом, Оформление, Работа с файлом
• 17.10.2018Разметка страницы ExcelPosted in Оформление
• 15.07.2016Представления Excel. Сохранить вид анализаPosted in Оформление
• 05.07.2015Формат по образцу для пакета MS OfficePosted in Оформление
• 03.06.2017Стиль ссылок R1C1 в Excel. Когда вместо букв в столбцах цифрыPosted in Формулы, Оформление
• 30.05.2016Как сделать «красиво в Excel»? Основные уловкиPosted in Оформление
• 26.01.2017Как добавить фигуру в Excel? Применение автофигурPosted in Оформление

Что заполнить в окне значение ячеек сценария

На этом шаге мы рассмотрим диспетчер сценариев.

Анализ данных с помощью таблиц подстановки является весьма эффективным. Однако он имеет несколько недостатков:

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

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

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

Чтобы Вы смогли получить общее представление о средстве Диспетчер сценариев , начнем с простого примера — производственной модели (рис. 1).

Рис. 1. Пример производственной модели

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

Таблица 1. Три сценария производственной модели Excel Сценарий Почасовая оплата Стоимость единицы материала В лучшем случае 30 57 В худшем случае 38 62 Наиболее вероятно 34 59

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

Рис. 2. Диалоговое окно Диспетчер сценариев

Когда Вы впервые вызываете это диалоговое окно, в нем имеется сообщение, в котором сказано, что ни один сценарий не определен, и это не удивительно, ведь Вы только начали. Как только будут добавлены новые сценарии, их названия появятся в диалоговом окне. Чтобы добавить сценарий, в диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить . Появится диалоговое окно Добавление сценария , которое показано на рисунке 3. Это диалоговое окно разделено на четыре части:

Рис. 3. Диалоговое окно Добавление сценария

• Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.
• Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
• Примечание. По умолчанию в это поле Excel помещает информацию о том, кто создал сценарий, а также дату его создания. Однако Вы можете отредактировать этот текст, добавить к нему новый текст или вовсе удалить его.
• Защита. Две опции, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защитить лист активизирована опция Сценарии . Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев .

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

Рис. 4. Диалоговое окно Значение ячеек сценария

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

• Выполнить команду Сервис | Настройка .
• В диалоговом окне Настройка выбрать вкладку Команды .
• Выбрать категорию Сервис .
• Выделить инструмент Сценарий и перетащить его на любую панель инструментов.
• Щелкнуть на кнопке Закрыть .

На следующем шаге мы рассмотрим отображение, изменение и объединение сценариев.