 |
Решение задачи с помощью Диспетчера сценариев.
|
|
|
|
Решение задачи на функцию Подбор параметра
Если значение функции может быть изменено только вследствие изменения значения одного параметра, то тогда отыскать требуемое значение этого параметра можно, используя функцию Подбор параметра.
Задача. Клиент желает открыть счет в коммерческом банке под 10% процентов годовых и накопить на этом счете за пять лет сумму в 55 000 руб. Клиент согласен производить ежегодные отчисления на свой счет в банке. Необходимо узнать размер этих ежегодных отчислений.
1. Создайте таблицу по образцу (Рис. 28)
Рис. 28. Исходная таблица
2. Установите курсор в ячейку С6 и выполните команду Вставка – Функция.
3. В открывшемся окне выберите категорию Финансовые и выделите функцию БС. Подтвердите выбор, нажав ОК.
4. Заполните параметры функции по образцу (Рис. 29).
Рис. 29. Параметры функции БС
5. Нажмите ОК.
6. Теперь можно воспользоваться командой Подбор параметра для решения задачи. Выполните команду Сервис - Подбор параметра. На экране появляется окно, которое заполняем следующим образом:
Рис. 30. Параметры функции Подбор параметра
7. После нажатия кнопки ОК вы получите диалоговое окно Результат подбора параметра, содержащее краткий отчет о результатах проделанных итерационных вычислений. Нажав на кнопку ОК, вы сохраните результаты вычислений в ячейках рабочего листа.
Ответ должен получиться отрицательным, что говорит о том, что клиент должен периодически отдавать деньги банку в размере 9008 руб.
Рассмотрим второй пример. Клиент открыл счет в банке и внес начальную сумму 1000 руб. Требуется определить, через какое время размер суммы увеличится до 1500 руб., если процентная ставка в месяц составляет 5%.
|
|
|
1. Запустите EXCEL и создайте таблицу по образцу (Рис. 31).
Рис. 31. Исходная таблица
2. В ячейку D1 поместите размер начальной суммы – 1000 руб. Сначала определим размер накопленной суммы через 1 месяц. В ячейку D4 введите формулу, по которой определяется накопленная сумма: =D1*(1+D2/100)^D3.
После ввода формулы в ячейке будет виден результат, а в строке формул будет отображена формула (Рис. 32).
Рис. 32. Выполненный расчет
3. Выполните команду Сервис - Подбор параметра. Заполните параметры функции Подбор параметра следующим образом:
Рис. 33. Параметры функции Подбор параметра
4. Нажмите ОК и получите окончательный результат (Рис. 34).
Рис. 34. Полученный результат
Следовательно, через 8,3 месяца на счете накопиться требуемая сумма.
Решение задачи с помощью Диспетчера сценариев.
Если искомые значения зависят от большего числа переменных, следовательно, приходится изменять значения в нескольких ячейках. Если таких изменяемых ячеек много, то будет трудно запоминать, какие из них изменялись, и что после этого произошло на рабочем листе. В 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. Итоговый отчет по сценариям
|
|
|
