Анализ «что-если»
Цель работы: научиться работать с финансовыми функциями MS Excel и выполнять анализ «что-если»
Содержание работы:
1 Финансовые функции при экономических расчётах
2 Прогнозирование с помощью анализа «что-если».
1 Финансовые функции при экономических расчётах
В MS Excel имеется 50 финансовых функций, которые позволяют выполнять наиболее характерные финансовые операции.
1 Функция ПЛТ. Расчёт величины ежемесячной выплаты кредита
Пример 1 Определить ежемесячный платёж, если банк предоставляет кредит в 140000р. с рассрочкой в 5 лет под 8,5% годовых с ежемесячной выплатой. Последний платёж должен составить 10000р.
Заполним таблицу MS Excel данными:
Рисунок 5.1 – Ввод функции ПЛТ
1 Выделить ячейку В6 и щелкнуть по кнопке fx (Вставить функцию) в строке формул. Появится Мастер функций (рисунок 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)
