Работа с финансовыми функциями.

Анализ «что-если»

 

Цель работы: научиться работать с финансовыми функциями MS Excel и выполнять анализ «что-если»

Содержание работы:

1 Финансовые функции при экономических расчётах

2 Прогнозирование с помощью анализа «что-если».

 

1 Финансовые функции при экономических расчётах

В MS Excel имеется 50 финансовых функций, которые позволяют выполнять наиболее характерные финансовые операции.

1 Функция ПЛТ. Расчёт величины ежемесячной выплаты кредита

Пример 1 Определить ежемесячный платёж, если банк предоставляет кредит в 140000р. с рассрочкой в 5 лет под 8,5% годовых с ежемесячной выплатой. Последний платёж должен составить 10000р.

Заполним таблицу MS Excel данными:

 

 

 

Рисунок 5.1 – Ввод функции ПЛТ

1 Выделить ячейку В6 и щелкнуть по кнопке f_x  (Вставить функцию) в строке формул.  Появится Мастер функций (рисунок 5.1).

Поле Ставка – это процент в месяц, вводим В4/12, т.к. функции ПЛТ требуется не годовая, а помесячная ставка.

▪ Кпер – количество периодов выплат, т.е. 5лет*12мес, вводим В5*12

▪ Пс – приведённая (нынешняя) стоимость - сумма всех платежей с текущего момента, вводим В2,

▪ Бс – будущая стоимость, баланс наличности перед последней выплатой, т.е. 10000, поэтому вводим В4,

§ Тип – выплата в конце месяца, поэтому вводим 0 или ничего.

    3.Нажать ОК.

 

Результат: – 3 006, 65 р. ежемесячно нужно выплачивать, чтобы погасить 130000 р. за 5 лет (в конце срока последним платежом ещё 10000р.). Знак "-" означает, что платим мы, а не банк.

2 Прогнозирование с помощью анализа «что-если».

Анализ «что-если» позволяет прогнозировать значение какой-либо функции (математической, финансовой, статистической и др.) при изменении её аргументов. Существует четыре способа прогнозирования значений: с помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.

1 способ. Таблица подстановки данных – это диапазон ячеек, показывающий, как изменение значений подстановки влияет на возвращаемый формулой результат. Если в какой-либо ячейке записана формула, содержащая элементы из других ячеек, то при изменении значения в какой-нибудь или нескольких ячейках изменится результат в ячейке, содержащей формулу.

Пример 2 Для примера 1 определить:

- ежемесячные выплаты при процентных ставках 7%, 8% и 9% годовых,

- ежемесячные выплаты при процентной ставке 5%, 6% и 7% и сумме заема 100 000, 110 000, 120 000 и 130 000р.

 

1 Для определения выплат при ставках 7%, 8% и 9% годовых введём таблицу данных в виде (рисунок 5.2):

 

 

 

Рисунок 5.2 – Таблица данных

               для изменения процентов

 

 

2 В ячейке А9  сделаем ссылку на ячейку с формулой ПЛТ, т.е. введём знак "=" и щёлкнем на ячейке В6. В строке формул запишется =В6, а в ячейке А9  появится результат –3 006,65р.

3 Выделим блок ячеек А9:В12 и запустим инструмент Таблица данных на вкладке Данные, группа Работа с данными, кнопка Анализ "что-если", пункт меню Таблица данных.

Рисунок 5.3 Окно Таблица данных

 

4 В появившемся окне (рисунок 5.3) введём в строке для столбцов адрес ячейки В2 (щелкнем по ячейке   В2), а в строке для столбцов щелкнем по В4 и нажмём ОК. Таблица данных заполнится числами – рисунок 5.4.

 

 

Рисунок 5.4 – Заполненная Таблица данных

 

5 Заготовим другую таблицу подстановок – введём столбцы для изменения суммы кредита (рисунок 5.5). В ячейке А14 также сделаем ссылку на ячейку с формулой, т.е. введём =В6, затем выделим таблицу (блок А15:Е18) и запустим инструмент Таблица данных на вкладке Данные, группа Работа с данными, кнопка Анализ "что-если", пункт меню Таблица данных. Введя В2 для столбцов и В4 для строк, получим:

 

Рисунок 5.5 – Таблица данных для варьирования ставки и кредита

 

Таблица подстановок должна обязательно в одной из ячеек содержать формулу.

2 способ. Сценарий – это набор значений подстановки, используемый для прогнозирования поведения модели. Если в Примере 1 изменить процентную ставку с 8,5% на 9%, то формула ПЛТ в ячейке В6 автоматически пересчитается и покажет результат  -3038,75р. При этом прежний результат -3006,65р. для 8,5% пропадает. Чтобы его сохранить, применяется сценарий, в котором приводятся расчёты ПЛТ с новыми значениями и сохраняется исходный (текущий) вариант. На одном листе Excel можно создать и сохранить несколько различных сценариев и переключаться на любой из них для просмотра результатов и выбора наилучшего.

Пример 3 Оформим в виде сценария вариант подстановки данных из примера 1.

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

1 Из вкладки Данные выберете команду Анализ «что-если», выбрать Диспетчер сценариев.                                                                                                                                                                                 

2 В открывшемся окне Диспетчер сценариев нажмите кнопку Добавить.                                                                                                                                                                                                                                        

3 Введите имя сценария., например «Кредит 200 000, 10%».                                                                                                                                                                                                                                                      

4 В поле Изменяемые ячейки задайте те ячейки (если они не смежные, то через Ctrl), которые Вы собираетесь изменить, в данном случае – ячейки В2 и В4.                 

5 Введите новые значения этих ячеек (рисунок 5.6). Нажмите кнопку ОК.

 

 

Рисунок 5.6 Новые значения кредита и ставки

                              

6 Нажмите кнопку Отчёт, выберите переключатель Структура, задайте ячейки для вывода результата В2:В6 (те, которые используются в формуле ПЛТ) и нажмите ОК.                                                                                                                                                                                                                                                                 

В результате на отдельном листе MS Excel Структура сценария появится сценарий с текущими и новыми значениями функции ПЛТ – рисунок 5.7.

Для просмотра сценария необходимо воспользоваться кнопкой Вывести в окне Диспетчер сценариев. Знаки «+»(«-«) слева и сверху позволяют разворачивать (сворачивать) отдельные разделы отчёта. Серым выделены изменяемые поля.                                                                                                                                                                                                                                                                                                                               

Рисунок 5.7 – Сценарий Кредит 20 000, 10%

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

3 способ. Подбор параметра. Подбор параметра – это обратная задача решения уравнений. Если в прямой задаче для функции y = f (x) по известному аргументу х вычисляется значение функции у, то в обратной задаче значение функции у задаётся числом, а величина х подбирается под заданное значение у.

При подборе параметра значение влияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая от этой ячейки не возвратит заданное значение.

Пример 4 Условие примера 1. Компания может ежемесячно выплачивать не более 2500р. Определить, каким должен для этого быть последний платёж.

1.Выделим ячейку В6:

2. На вкладке Данные, группа Работа с данными, кнопка Анализ "что-если" щелкнуть по пункту меню Подбор параметра – рисунок 5.8. Появится окно Подбор параметра:

 

 

Рисунок 5.8 – Окно Подбор параметра

В окне Подбор параметра:

-в поле Установить в ячейке – введено В6,

-в поле Значение -      ввести -2500

-в поле Изменяя значение ячейки – ввести В3 (ячейка последнего платежа),

-нажать ОК.

В итоге появится окно Результат подбора параметра – рисунок 5.9:

 

 

Рисунок 5.9 – Окно Результат подбора параметра

 

Таким образом, если выплачивать по 2500 р. В месяц, то последний платёж составит -27716 р.

При подборе параметра одна из ячеек обязательно должна содержать формулу.

  4 способ. Команда Поиск решения из вкладки Данные \ Работа с данными \ Анализ "что-если" используется для подбора одновременно нескольких параметров с целью максимизации или минимизации содержимого целевой ячейки и подробно рассматривается в лабораторной работе №7 (excel-7).

 

Контрольные вопросы

1 Как вывести на экран список функций MS Excel и запустить Мастер функций?                                                                                                                                                                                                                                                                                                                                                                                                             

2 Какую операцию выполняет функция ПЛТ, что вводится в её поля Ставка, Кпер, ПС, Бс, Тип?

3 Назначение и способы анализа «что-если»?

4 Что такое «Таблица данных», как её создать для одно- и двухмерного массива?

5 Что такое сценарий, как его создать, просмотреть, получить итоговый отчет на отдельном листе?

6 Сущность операции Подбор параметра, как она выполняется?

 

Задания

1. Выполнить задание примера 1, изменив сумму кредита на 140000· n, где n - номер студента в журнале преподавателя. Выполнить то же для новой суммы кредита, изменив годовой процент с 8,5% на 5%, а срок кредита с 5 на 10 лет.

2. Создать две таблицы данных как в примере 2, изменив сумму заёма на 80000·n, где n- номер студента в журнале преподавателя.

3. Оформить в виде сценариев все операции из п.1 (два сценария + Текущие значения) задания.

4. Выполнить задание примера 4, изменив сумму ежемесячной выплаты на n· 100.   

                                  

Содержание отчёта

1Название, цель, содержание работы

2 Задание своего варианта 

3 Письменные ответы на контрольные вопросы

4 Выводы по работе

На своём носителе должны быть сохранены результаты работы

 

 

Практическое занятие №6 (excel-6)