Контрольные вопросы
1. Какую команду нужно выполнить, чтобы выполнить «таблица данных»?
Лабораторная работа 2.3 Диспетчер сценариев
Цель работы: Освоить навыки работы с инструментом анализа «что если» с по-мощью диспетчера сценариев.
Порядок работы:
1. Запустите программу MS Excel 2010.
2. В открывшемся окне нажмите кнопку Новая книга для создания новой рабочей области. Щелкните по кнопке Создать.
3. В поле Имя файла (файл сохранить как) задайте имя вашей рабочей книги lab2_3_FIO (где FIO ваши инициалы) и выберите диск D/папка студент/папка FIO и номер группы (созданная на первом занятии).
4. При этом откроется окно вашей рабочей книги.
Пример 15.
Предположим, есть несколько различных вариантов получения прибыли от вложения денежных средств в разные банки.
Вложить 400 000 руб. на 3 года под 8% годовых с начислением процентов ежемесячно, без возможности пополнения счета.
Вложить 300 000 руб. на 3 года под 10% годовых с начислением процентов ежеквартально, с возможностью пополнения счета. Периодические дополнительные вложения составят 5000 руб.
Вложить 400 000 руб. на 4 года под 7% годовых с начислением процентов ежеквартально, без возможности пополнения счета.
Вложить 300 000 руб. на 4 года под 10% годовых с начислением процентов ежеквартально, с возможностью пополнения счета. Периодические дополнительные вложения составят 2000 руб.
Необходимо определить, какой из вариантов будет наиболее выгодным.
Алгоритм решения.
В вашей рабочей книге lab2_3_FIO, откройте Лист 1. Составим в Excel таблицу с исходными данными (Рис. 17). В числе исходных данных у нас будут следующие показатели: сумма вклада, размер годовой процентной ставки, срок вклада, сумма периодических платежей, количество начислений в год. Заполним таблицу данными, предлагаемыми первым банком.
Рис.17. Таблица с исходными данными
Из этих исходных данных нам нужно вычислить несколько показателей: Будущую сумму кредита и прибыль, полученную от операции. Будущая сумма высчитывается при помощи функции БС (Рис. 18).
Рис.18. Таблица с исходными данными
Заполняем аргументы функции. Ставка годовая делится на количество платежей в год, Кпер получается в результате умножения количества лет вклада на количество начислений в год, Плт и Пс задаются со знаком «минус», так как предполагается временный отток денежных средств у клиента. Затем рассчитываем Прибыль от вложения средств вычитанием из Будущей суммы вклада Суммы вклада и Суммы периодических платежей умноженной на Срок вклада в годах и на Количество начислений в год (Рис. 19).
Рис.19. Таблица с исходными данными
Таким образом, мы подготовили калькулятор для определения прибыли от вложения средств в банки.
Создадим таблицу с исходными данными, предлагаемыми разными банками (Рис. 20).
Рис.20. Таблица с исходными данными
Далее воспользуемся командой Диспетчер сценариев, нажав на кнопку «анализ что если», из группы Работа с данными на вкладке Данные. Откроется диалоговое окно «диспетчер сценариев». Нажмем кнопку «Добавить». В поле «Название сценария» введем название банка «Банк 1», в поле «Изменяемые ячейки» введем диапазон B1:B5, затем отключаем опцию «запретить изменения», нажимаем ОК (Рис. 21).
Рис.21. Диспетчер сценарий
В открывшемся окне «значения ячеек сценария» заполняем соответствующие значения для Банка 1 (Рис. 22).
Рис.22. Диспетчер сценарий Значение ячеек сценария
Нажимаем ОК. Снова появляется окно диспетчера сценариев. Нажимаем кнопку
«добавить» (Рис. 23).
Рис.23. Диспетчер сценарий
Вносим название банка «Банк 2» и диапазон изменяемых ячеек B1:B5, затем отключаем опцию «запретить изменения», нажимаем ОК (Рис. 24).
Рис.24. Диспетчер сценарий
В открывшемся окне «значения ячеек сценария» меняем значения в полях на соответствующие значения для Банка 2 (Рис. 25).
Рис.25. Диспетчер сценарий Значение ячеек сценария
Те же действия выполняем для Банков 3 и 4.
После этого в диалоговом окне диспетчера сценариев появились сценарии для всех банков: Банка 1, Банка 2, Банка 3 и Банка 4 (Рис. 26).
Рис.26. Диспетчер сценарий
Теперь все сценарии готовы. Мы можем переключаться между готовыми сценариями при помощи кнопки «вывести», получая соответствующие результаты в исходной таблице.
Результаты по Банку 2
Результаты по Банку 4
Диспетчер сценариев дает возможность создать отчеты по сценариям, для этого в диалоговом окне диспетчера сценариев нажимаем кнопку «Отчет». Появляется окошко отчета по сценарию. Выбираем тип отчета «структура», в поле ячейки результата задаем адрес ячейки В9. Нажимаем кнопку ОК (Рис. 27).
Рис.27. Отчет по сценарию
Мы оказываемся на новом листе, созданном автоматически, который называется «Структура сценария» (Рис. 28).
Рис.28. Структура сценария
Адреса ячеек в отчете можно заменить на текст, соответствующий каждой ячейке (Рис. 29).
Рис.29. Структура сценария
Задания для самостоятельного выполнения.
Задание 2.3 (Вычисления с использованием финансовых функций и инструментария Диспетчер сценариев).
2.3.1 Вы задумались о покупке машины. Для этого Вам необходим кредит. Нужно определить, в каком из 3 банков наиболее оптимальные условия. В числе исходных данных у нас будут:
Стоимость автомобиля,
Размер первоначального взноса в процентах от стоимости,
Годовой процент по кредиту,
Срок кредита (лет),
Ежемесячные платежи за обслуживание счета,
На основании этих исходных данных нам нужно высчитать несколько показателей:
Размер первоначального взноса в денежном выражении (рассчитывается как произведение стоимости автомобиля на Размер первоначального взноса в процентах),
Сумму кредита (рассчитывается как разность между стоимостью автомобиля и размером первоначального взноса).
Сумму ежемесячных выплат (высчитывается при помощи функции ПЛТ),
Реальную стоимость покупки,
Общую сумму переплаты.
На рисунке 30 приведены формулы для расчета показателей и исходные данные трех банков.
Рис.30. Формулы для расчета показателей
С помощью инструмента «Диспетчер сценариев» создать отчеты, отображающие данные для сравнительного анализа результатов (Рис. 31).
Рис.31. Примерный отчет
Порядок выполнения заданий:
1. Каждое задание делается в одной рабочей книге на новом рабочем листе.
2. Варинаты заданий задает преподаватель.
3. Сравните правильность выполнения заданий, с ответами приведенные в самих за-даниях.
4. .Отчеты предоставляются в электронном виде (электронная тетрадь), в формате .xlsx