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

Цель работы: научиться работать с финансовыми функциями Excel

и выполнять анализ «Что-если»

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

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

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

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

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

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

А В

1 Услуга Сумма 1 Выделить ячейку В6 и щелкнуть по кноп

ке Изменить формулу (знак «=»

2 Кредит,р 140000 слева от строки формул). Появится Мас-

тер функций, поле Имя будет

3 П.платёж 10000 заменено одной из функций. Раскрыть

поле Имя и выбрать функцию

4 % 8,50 ПЛТ (если её в списке нет, то через

пункт Другие функции...произво-

5 Срок, лет 5 дится выход в Мастер функций)

6 Еж. платёж 2 Щелкнуть мышью по функции ПЛТ,

перетащить окно ПЛТ на свободное место экрана, чтобы освободить таблицу и заполнить его поля:

▪ Поле Ставка – это процент в месяц, вводим 0,085,

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

▪ Нз – общая сумма всех платежей с текущего момента, вводим 140000,

▪ Бс – будущая стоимость, вводится 130000 со знаком "-", т.к. платим мы, а не банк,

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

3.Нажать ОК.

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

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

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

С помощью таблиц подстановки данных, с помощью сценариев и с помощью подбора параметров и поиска решения.

Способ. Таблица подстановки данных

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

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

Анализ формулы начинается с подготовки таблицы подстановки:

1. Левую верхнюю ячейку блока, отведенного под таблицу, оставить пустой.

2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения варьируемой переменной.

3. В верхнюю строку блока, начиная со второй ячейки, ввести ссылки на ячейки с анализируемыми формулами.

Допускается и другая ориентация таблицы, когда значения варьируемой переменной вводятся в первую строку, а анализируемые формулы — в первый столбец блока.

4. Выделить таблицу подстановки (в ячейки, расположенные рядом с таблицей, можно ввести пояснительные надписи, но эти ячейки не входят в таблицу подстановки данных и, следовательно, не выделяются).

5. В меню Данные выбрать команду Таблица подстановки.

6. Если значения варьируемой переменной расположены в столбце, то надо щелкнуть по полю Подставлять значения по строкам в и ввести в это поле адрес изменяемой ячейки (т.е. ячейки, которая играет роль варьируемой переменной в формуле). Если значения варьируемой переменной расположены в строке, то адрес изменяемой ячейки вводится в поле Подставлять значения по столбцам.

7. Щелкнуть по кнопке ОК. Таблицабудет заполнена значениями.

В случае анализа зависимости формулы от двух переменных таблица подстановки подготавливается по-другому:

1. В левую верхнюю ячейку блока, отведенного под таблицу, ввести ссылку на ячейку с анализируемой формулой.

2. В левый столбец блока, начиная со второй ячейки, последовательно ввести значения одной из варьируемых переменных.

3. В верхнюю строку блока, начиная со второй ячейки, ввести значения другой варьируемой переменной.

4. Выделить таблицу подстановки.

5. В меню Данные выбрать команду Таблица подстановки.

6. В поле Подставлять значения по строкам в ввести ссылку на ячейку с переменной, значения для которой расположены в левом столбце таблицы подстановки.

7. В поле Подставлять значения по столбцам ввести ссылку на ячейку с переменной, значения для которой расположены в первой строке таблицы подстановки.

8. Щелкнуть по кнопке ОК. Таблица будет заполнена значениями.

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

Пример 2. Вычислить величину ежемесячной выплаты (ПЛТ) фирмой, взявшей кредит в размере 74000р. со ставкой 8% годовых на период, равный 5 лет и будущей стоимостью 5000р.

Определить величину ежемесячной выплаты (ПЛТ) используя таблицу подстановок:

§ при процентной ставке 5%, 7%, 8%, 9%, 10%, 12% годовых, и сроке кредита 3, 4, 5, 6, 7, 8 лет соответственно.

1.Введем исходные данные в виде таблицы:

	А	В
		Сумма
	Кредит	74 000,00р.
	Ставка	8,00%
	Срок, лет
	Будущая стоимость	5 000,00р.
	ПЛТ	-1 568,50р.

Введём в ячейку В6 формулу платежа =ПЛТ(B3/12;B4*12;B2;B5) вручную или через окно ПЛТ из Мастера функций, в В6 появится рассчитанное значение функции -1568,50р.

2. Выделить блок ячеек под таблицу подстановок размером 2х6, например, А10:В16. В ячейку А10 ввести ссылку на ячейку с формулой ПЛТ, т.е. записать в неё =В6

3. В левый столбец блока, начиная со второй ячейки (А11:А16), последовательно ввести значения одной варьируемой переменной - от 5% до 12%.

4. В верхнюю строку блока, начиная со второй ячейки (В10:G10), ввести значения другой варьируемой переменной - сроки кредита 3, 4, 5, 6, 7, 8 лет.

5. Выделить таблицу подстановки или её первую ячейку А10. Появится окно "Таблица подстановок. В строке "Подставлять значения по столбцам" записать ячейку со сроками (щелкнуть по яч. В4), в строке "Подставлять значения по строкам" записать ячейку со ставкой (щелкнуть по яч. В3) и нажать ОК.

Таблица заполнится данными. При щелчке по любой ячейке таблицы в строке ввода появится формула =ТАБЛИЦА(B4;B3)

	а	в	с	d	e	f	g
	-1 779,86р.
	5%	-2346,867538	-1798,48	-1469,99	-1251,46	-1095,75	-979,3
	7%	-2410,123986	-1862,59	-1535,13	-1317,7	-1163,16	-1047,9
	8%	-2442,239538	-1895,29	-1568,5	-1351,79	-1197,98	-1083,46
	9%	-2474,67888	-1928,42	-1602,41	-1386,52	-1233,54	-1119,87
	10%	-2507,441122	-1961,98	-1636,85	-1421,87	-1269,83	-1157,09
	12%	-2573,930475	-2030,37	-1707,31	-1494,47	-1344,57	-1233,97

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

2 способ. Сценарий – это набор значений подстановки, используемый для прогнозирования поведения модели. На одном листе Excel можно создать и сохранить несколько различных сценариев и переключаться на любой из них для просмотра результатов и выбора наилучшего.

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

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

1 Из меню Сервис выберете команду Сценарии.

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

3 Введите имя сценария., например «Ставка 7%».

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

5 Нажмите кнопку ОК.

6 В открывшемся диалоговом окне Значения сценария для каждой изменяемой ячейки введите новое значение или формулу, в данном случае вводим в В3 число 0,07. Нажмите кнопку ОК. Исходную модель " что-если " желательно сохранить в виде сценария, присвоив ему, например, имя «Стартовые значения». В противном случае при задании новых изменяемых ячеек исходные данные будут потеряны.

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

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

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

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

2.В меню Сервис выбрать команду Подбор параметра.

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

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

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

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

-нажать ОК.

Результат: последний платёж = -27716 р.

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

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

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

1 Как вывести на экран Палитру формул, выйти через неё в приложение Мастер функций?

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

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

3 Что такое «Таблица подстановок», каков состав её ячеек?

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

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