Лабораторная работа 2.1 Подбор параметра

Цель работы: Освоить навыки работы с инструментом анализа «что если» с по-мощью подбора параметра.

Порядок работы:

1. При работе с финансовыми функциями используются специальные финансовые термины. Далее идет разъяснение основных финансовых терминов, необходимых для работы с финансовыми функциями.

2. Запустите программу MS Excel 2010.

3. В открывшемся окне нажмите кнопку Новая книга для создания новой рабочей области. Щелкните по кнопке Создать.

4. В поле Имя файла (файл сохранить как) задайте имя вашей рабочей книги lab2_1_FIO (где FIO - ваши инициалы) и выберите диск D/папка студент/папка FIO и номер группы (созданная на первом занятии).

5. При этом откроется окно вашей рабочей книги.

6. Для начала работы с финансовыми функциями убедитесь, что установлена необходимая надстройка «Пакет анализа», для этого активизируйте меню Файл→Параметры→Надстройки и выставите флажок напротив необходимой надстройки, если этот флажок уже выставлен, ничего дополнительно делать не нужно.

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

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

В поле Изменяя значение ячейки указывается адрес ячейки, содержащей значе­ние одного из аргументов функции. EXСEL решает обратную задачу: подбор значения аргумента для заданного значения функции.

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

При нажатии кнопки ОК подобранное значение аргумента сохраняется в ячейке аргумента; при нажатии кнопки Отмена происходит восстановление значения аргу­мента. При неуспешном завершении подбора параметра выдается соответствующее со­общение о невозможности подбора аргумента.

При подборе параметра Excel использует итерационный процесс. Многие финансовые функции имеют одинаковые аргументы. Общие аргументы описаны в таблице 1.

Таблица 1

Описание аргументов финансовых функций Excel, использующих

базовые модели

Аргумент	Описание аргумента
Бз	будущая стоимость фиксированных
Бс	периодических выплат или единой суммы; баланс налично­сти, которой нужно достичь после последней выплаты, по умолчанию равный 0 (например, будущая стоимость займа равна 0)
Выплата	фиксированная периодическая выплата
дата0; дата1;...;датаN	даты операций с наличными
Кпер	Общее
Число_периодов	число периодов
кол_пер	Выплат
Нз	начальное значение (текущая стоимость)
Тс	вклада или займа
Ставка	Процентная ставка за период , норма
Норма	Дисконтирования
Сумма 0;сумма1;...;	значения
СуммаN	выплат и
Значения	Поступлений
Тип	число 0 или 1, обозначающее, когда производится выплата (1- в начале периода, 0 -в конце периода); по умолчанию равно 0.0
Предположение	предполагаемое значение процентной ставки
Предп	по умолчанию
Прогноз	равно 0.1

Задание 11.

Определить, какую наибольшую ссуду под закладную можно взять на 30 лет, если процентная ставка равна 6,5% и ежемесячные выплаты составляют 2000 руб.

Алгоритм решения.

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

1) решить задачу с каким-нибудь начальным значением параметра. Например, для ссуды в 500 000 руб. месячные выплаты превышают 2 000 руб.;

2) сделать ячейку с формулой, в данном случае В4, активной ячейкой. (Это не обязательно, но упрощает процесс);

3) в меню Сервисвыбрать Подбор параметра;

4) в окне диалога Подбор параметра (Рис. 8) , оставить без из­менения значение в поле Установить в ячейке;

5) в поле Значение ввести максимальное значение, в данном случае –(-2000);

6) в поле Изменяя значение ячейки ввести ссылку или щелкнуть в листе на ячейке, значение которой является неизвестным. В данном случае это ячейка В1.

7) нажать кнопку ОК. Excel выведет окно диалога Результат подбора пара­метра. Ответ на вопрос задачи находится в ячейке, заданной ранее в поле Изме­няя значение ячейки. В данном примере результат 316 422 появляется в ячейке B1;

8) для сохранения значения следует нажать кнопку ОК.

Рис. 8. Окно диалога Подбор параметра

Задание 12.

В вашей рабочей книге lab2_FIO, откройте Лист 2, создадим модель для решения задачи (Рис. 9).

Вернемся к рассмотрению Примера 7 из Лабораторной работы 1 (стр.12). Получив результат, может возникнуть необходимость его проанализировать. Допустим суммы 219 878,38 руб. недостаточно для реализации задуманного проекта. Необходима сумма в 250 000 руб. Увеличение суммы кредита может быть за счет изменения условий договора. А именно, или должна быть изменена процентная ставка, или срок кредита, или периодический платеж. В данном случае изменение будущей суммы кредита не рассматриваем. Для подобного анализа воспользуемся командой Подбор параметра, нажав на кнопку «анализ что если», из группы Работа с даннымина вкладке Данные.

Рис. 9. Модель для функции ПС

В ячейке A2 запишем функцию для расчета ПС, ссылаясь на данные таблицы. Теперь будем поочередно изменять значения процентной ставки (С2), срока кредита (D2) и периодического платежа (E2).

1. Изменение процентной ставки (Рис. 10). В результате получаем, что при прочих прежних условиях кредит в 250 000 руб. можно получить при уменьшении годовой процентной ставки до 11%.

Рис. 10. Подбор параметра. Изменение процентной ставки

2. Изменение срока кредита (Рис. 11). В результате получаем, что при прочих прежних условиях кредит в 250 000 руб. можно получить, если срок выплаты кредита уменьшить до 1,16 года.

Рис. 11. Подбор параметра. Изменение срока кредита

3. Изменение суммы периодического платежа (Рис. 12). В результате получаем, что при прочих прежних условиях кредит в 250 000 руб. можно получить, если периодический платеж увеличить до 2003,8 руб. в месяц.

Рис. 12. Подбор параметра. Изменение суммы периодического платежа

После проведения анализа и получения результатов клиент вправе сделать выбор в пользу того или другого варианта.

Задание 13.

Одновременно сделан вклад и взяты три ссуды на различных условиях. Рассчитать выгодность операции (без учета инфляции).

Алгоритм решения.

В вашей рабочей книге lab2_FIO, откройте Лист 3. Для решения данной задачи создадим модель (Рис. 13).

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

Рис. 13. Модель для определения выгодности операций

По заданным условиям в результате вычислений видим, что чистый денежный поток составит -124,81 у.е.

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

Рис. 14. Подбор параметра для изменения чистого денежного потока