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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ячейка С5 – 20.

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

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

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

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

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

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

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

ячейка С5 – 15.

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

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

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

ячейка С5 – 10.

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

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

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

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

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

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

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

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

Дата добавления: 2015-07-13