LINEBURG


<< Пред. стр.

страница 54
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

fa — «будущая» стоимость вклада.
Величины pmt и г, схема платежей type остаются неизменными в течение срока
действия договора п. В расчетах учитывается длительность периода — месяц,
квартал, полгода, год. Периоду определенной длительности соответствует про­
центная ставка. Если процентная ставка дается за год, а учетный период — доля
года, то в расчетах используется пропорциональная величина годовой процент­
ной ставки. В финансовых функциях по расчету денежных потоков важно на­
правление потока: «к нам» — со знаком плюс, «от нас» — со знаком минус.
Используются модели задач двух видов.
1. Накопление капитала, размещенного на депозите в виде вклада (либо отдан­
ного в виде ссуды «в рост»), величина pv отрицательная, величина pmt мо­
жет принимать любое значение, в том числе и 0. В конце срока действия
договора п возвращается положительная сумма fa и равная абсолютному зна­
чению pv, или превосходящая его;
2. Пользование заемными средствами, величина pv — положительная, величина
pmt — отрицательная (регулярные выплаты), сумма fa должна быть равна 0
(полное погашение ссуды).
448 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


Существует ряд взаимосвязанных встроенных функций категории Финансовые
Microsoft Excel:
• БЗ — расчет наращенной суммы капитала/Ь, если неизменны параметры: pmt,
г, п, type, pv.
• ПЗ — расчет эквивалента будущей стоимости серии последовательных плате­
жей в настоящее время — величина pv, если неизменны параметры: pmt, r, п,
type, fv.
• КПЕР — количество периодов п, за которые исходная сумма pv достигнет ве­
личины fv, если неизменны параметры: pmt, r, type.
• НОРМА — величина процентной ставки г, при которой исходная сумма pv
достигнет величины fv, если неизменны параметры: pmt, n, type.
• ППЛАТ — величина pmt, которая обеспечивает увеличение суммы pv до вели­
чины fv, если неизменны параметры: г, п, type.
Функция ППЛАТ вычисляет общую сумму периодического платежа pmt, в кото­
рой есть две составляющие, вычисляемые с помощью встроенных функций:
• ОСНПЛАТ — сумма, составляющая накопление (погашение долга) за учетный
период;
• ПЛПРОЦ — проценты (доход или к уплате) за учетный период.
При этом величина pmt остается неизменной в течение всего срока п, а для
различных учетных периодов меняется соотношение составляющих ОСНПЛАТ и
ПЛПРОЦ:
ППЛАТ =ОСНППАТ+ПЛПРОЦ

Пример 1
Вычислить сумму накопления на депозитном вкладе, если размещены 1000 р. под
18 % годовых на срок 3 года с обязательством дополнительных вложений в сум­
ме 100 р. в конце каждого месяца.
Последовательность действий.
1. Открыть рабочую книгу Анализов с помощью команды меню Файл • Открыть.
2. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
3. Переименовать новый лист с помощью команды меню Формат • Лист • Пере­
именовать, лист — Анализ.
4. Разместить параметры для вычисления функций (рис. 6.9):

1 Норма
2 Число период*»
3 Выплаты
4 НЗ
5 Тип
6 БЗ


Рис. 6.9. Параметры для вычислений функций БЗ, ПЗ, КПЕР, НОРМА, ППЛАТ
449
Среда моделирования Microsoft Excel


5. Ввести в ячейки В1.В5 исходные данные.
6. Вычислить функцию БЗ по формуле: =БЗ(В1/12;В2*12;ВЗ;В4;В5). Резуль­
тат — 6 436,74 р.
7. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.
Пример 2
Определить, за какое время сумма на счете может возрасти до 10 000 р. при со­
хранении процентной ставки, условий и суммы ежемесячных выплат.
Последовательность действий.
1. Открыть рабочую книгу Анализ.х1э с помощью команды меню Файл • Открыть.
2. Установить курсор в ячейку с формулой БЗ (ячейка В6) на листе Анализ.
3. Выполнить команду меню Сервис • Подбор параметра (рис. 6.10).




+Г)
pmt (1 + г type) + pv (! + /•)* + > - О

Рис. 6.10. Диалоговое окно Подбор параметра


4. Ввести значение функции, для которого осуществляется подбор параметра —
10000, указать адрес ячейки, соответствующей параметру, для которого под­
бираются значения — $В$2, нажать кнопку ОК.
В процессе итерационного подбора на экране выводится окно сообщения. Для
остановки процесса подбора нажимается кнопка Пауза, после чего подбор мож­
но возобновить, нажав кнопку Шаг для получения следующей итерации или
кнопку Продолжить — для возобновления работы в автоматическом режиме.
Если подбор возможен, в ячейке параметра появляется значение и выводится
сообщение Результат подбора параметра о желаемом и текущем значении функ­
ции. При нажатии кнопки ОК значение параметра записывается в ячейку. При
нажатии кнопки Отмена в ячейке восстанавливается значение параметра. Если
подбор значения аргумента для заданного значения функции невозможен, выво­
дится сообщение о том, что решение не найдено. В данном случае результат
подбора — 4,346 года.
1. Проверить результат расчета с помощью функции КПЕР.
2. Восстановить условия расчета.
3. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.
450 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


Пример 3
Определить, как следует изменить сумму ежемесячных выплат, чтобы за 3 года
увеличить вклад до 100 00 р. при сохранении процентной ставки и условий пла­
тежа.
Последовательность действий.
1. Открыть рабочую книгу Анализ-xls с помощью команды меню Файл • Открыть.
2. Установить курсор в ячейку с формулой БЗ (ячейка В6) на листе Анализ.
3. Выполнить команду меню Сервис • Подбор параметра.
4. Ввести значение функции, для которого осуществляется подбор параметра —
10000, указать адрес ячейки, соответствующей параметру, для которого под­
бираются значения — $В$3, нажать кнопку ОК.
5. В данном случае результат подбора: -175,37, что соответствует результату
функции ППЛАТ.
6. Проверить результат расчета с помощью функции ППЛАТ.
7. Восстановить условия расчета.
8. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Пример 4
Определить, какая процентная ставка обеспечит за 3 года рост вклада до 10 000 р.
при сохранении условий и суммы ежемесячных платежей.
Последовательность действий.
1. Открыть рабочую книгу Анализ.хЬ с помощью команды меню Файл • Открыть.
2. Установить курсор в ячейку с формулой БЗ (ячейка В6) на листе Анализ.
3. Выполнить команду меню Сервис • Подбор параметра.
4. Ввести значение функции, для которого осуществляется подбор параметра —
10000, указать адрес ячейки, соответствующей параметру, для которого под­
бираются значения — $В$1, нажать кнопку ОК.
В данном случае результат подбора: 40 %. Это соответствует функции
НОРМА.
5. Проверить результат расчета с помощью функции НОРМА.
6. Восстановить условия расчета.
7. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.
Пример 5
Определить, какая первоначальная сумма обеспечит за 3 года рост вклада до
10 000 р. при сохранении процентной ставки, срока и условий платежей.
Последовательность действий.
1. Открыть рабочую книгу Анализов с помощью команды меню Файл • Открыть.
2. Установить курсор в ячейку с формулой БЗ (ячейка В6) на листе Анализ.
451
Среда моделирования Microsoft Excel


3. Выполнить команду меню Сервис • Подбор параметра.
4. Ввести значение функции, для которого осуществляется подбор параметра —
10000, указать адрес ячейки, соответствующей параметру, для которого под­
бираются значения — $В$4, нажать кнопку ОК.
5. В данном случае результат подбора: -3084,83 р. Это соответствует функции
ПЗ.
6. Проверить результат расчета с помощью функции ПЗ.
7. Восстановить условия расчета.
8. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Информационная технология
сценарного подхода
В электронной таблице Microsoft Excel можно сохранять наборы значений пара­
метров моделей в виде сценариев. Сценарий — именованный набор значений ука­
занных ячеек листа рабочей книги. Сценарный подход обеспечивает решение
задач типа «ЧТО ЕСЛИ», не ограничивая число одновременно изменяемых па­
раметров для каждого такого набора. Сценарии используются для подстановки
значений параметров в ячейки таблицы и вычисления зависящих от них фор­
мул.
Ограничений на общее число сценариев на одном листе рабочей книги нет. Сце­
нарии содержат лишь ячейки одного листа рабочей книги, при этом допускается
объединение сценариев нескольких листов и даже сценариев других рабочих
книг при подготовке отчетов. В отчете может быть выведено максимум 251 сце­
нарий.
Значения ячеек сценария хранятся в скрытом виде, но могут использоваться
для подстановки в ячейки. Можно многократно редактировать состав и значения
параметров, входящих в сценарий, добавлять новые и удалять существующие
сценарии. Сценарий можно снабдить комментарием, указать сведения о его авторе.
Команда меню Сервис • Сценарии выводит диалоговое окно Диспетчер сценари­
ев (рис. 6.11).
Окно содержит рад текстовых кнопок:
• Вывести — подстановка значений параметров выбранного сценария;
• Закрыть — закрытие диалогового окна и прекращение работы со сценариями;
• Добавить — добавление нового сценария для листа рабочей книги;
• Удалить — удаление указанного сценария;
• Изменить — изменение значений параметров выбранного сценария;
Q Объединить — объединение со сценариями других листов или книг;
• Отчет — вывод отчета по сценариям.
Для нового сценария выводится диалоговое окно Добавление сценария (рис. 6.12),
в которое следует ввести уникальное название сценария, выделить блок изме­
няемых ячеек.
452 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


Добавление сценария


(Вариант


_j


Автор Ильина, 14 07 20001 нМ




S (вдхтвывиоинив^
> fas»®»

j
• { »V Ь> У* $" " ^ М ^. ^WMI>J°|

Значения ячеек сценария




Рис. 6 . 1 1 . Диалоговое окно Диспетчер сценариев


Добавление сценария




•"-а.
}В:Я2,!М




' J? $ir»»Ti«!>«»***»s*f , i Г «крыц» / ,

.,у , | Огнен» f


Рис. 6.12. Диалоговое окно Добавление сценария

ВНИМАНИЕ
Если ячейки, входяпще в сценарий, не смежные, они выделяются при нажатой клавише
Ctrl, максимальное число ячеек в одном сценарии — 32

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


Защита • Защитить лист. Сценарии можно скрыть, при этом также требуется
предварительная защита листа.
После нажатия кнопки ОК появляется диалоговое окно Значения ячеек сценари­
ев, в котором и задаются значения ячеек, соответствующие параметрам модели
(рис. 6.13). Нажатие кнопки ОК завершает ввод сценариев, нажатие кнопки До­
бавить обеспечивает ввод очередного сценария.




Рис. 6.13. Диалоговое окно Значения ячеек сценария

На основании сценариев формируются итоговые отчеты: сводная таблица и
структурный отчет.
Пример 6
Создать сценарии для вариантных расчетов функции БЗ. Сохранить варианты и
сформировать по ним отчет.
Последовательность действий.
1. Открыть рабочую книгу Анализов с помощью команды меню Файл • Открыть.
2. Установить курсор на листе Анализ.
3. Выполнить команду меню Сервис • Сценарии.
4. Подготовить сценарии для вариантных расчетов согласно табл 6 5.
Таблица 6.5. Варианты сценариев

Параметры Вар 1 Вар 2 Вар 3 Вар 4 Вар 6 Вар 7
Вар 5

0,18
Норма 0,18 0,18 0,20 0,20 0,20 0,20

4,0
3,0 3,5 3,0
Число 3,0 3,5 4,0
периодов

НЗ -1000 -1000 -1000 -800 -1000 -1000 -800


5. В качестве изменяемых ячеек выбрать ячейки параметров: НЗ, Норма и Число
периодов, что соответствует адресам: $В$1:$В$2;$В$4. Установить флажок
Защита от изменений.
6. Последовательно вывести варианты сценариев для ознакомления.
7. Внести изменения в сценарий с именем Вар 7: новое значение параметра
НЗ: - 750.
454 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


8. Подготовить отчет в форме структурной таблицы. Для вывода отчета указать
адрес ячейки, содержащей функцию, зависящую от изменяемых параметров
сценариев — ячейка В6. Отчет выводится на отдельный лист — Структура
сценария.
9. Подготовить сводную таблицу по сценариям. Указать адрес ячейки, содержа­
щей функцию, зависящую от изменяемых параметров сценария — ячейка В6.
Сводная таблица выводится на отдельный лист — Сводная таблица по сцена­
рию.
10. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Информационная технология
таблицы подстановки
Информационная технология таблицы подстановки используется для изучения
влияния значений параметра на результат несколько функций либо значений
двух параметров на результат одной функции. Эта информационная технология
обеспечивает решение задач анализа типа «ЧТО ЕСЛИ», ограничивая число од­
новременно изменяемых параметров модели. Значения параметра (параметров)
и результирующей функции представлены на листе в явном виде. Можно про­
сто изменять значения параметра (параметров) и автоматически вычислять зна­
чения функции.

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

Пример 7
Требуется проследить с помощью таблицы подстановки, как изменение пара­
метра Выплата отражается на значении функций: БЗ, ПЗ, НОРМА.
Последовательность действий.
1. Открыть рабочую книгу Анализ.xls с помощью команды меню Файл • Открыть.
2. Установить курсор на листе Анализ в столбец L.
3. Подготовить значения параметра Выплаты в виде блока ячеек одной строки
(рис. 6.14), например, в ячейках L2:R2.

ПРИМЕЧАНИЕ
Выделенная на рис. 6 14 ячейка К2 рассматривается как ячейка подстановки по столбцам,
поскольку значения параметра представлены в строке. Если бы исходные данные пара­
метра были подготовлены в столбце, то эта же ячейка рассматривалась бы как ячейка под­
становки по строкам.
455
Среда моделирования Microsoft Excel

Функции Значения изменяемого параметре
Ячейка
-во

поостаапки -500
-150 •ш
-10В -12S




Рис. 6.14. Таблица подстановки с одним параметром

4. Ввести формулы (БЗ, ПЗ, НОРМА) в столбец Функции.
Например, в ячейки введены формулы вида:
КЗ. =БЗ(18%/12;3*12;К2;-1000;0)
К4: =НОРМА(3*12;К2;-1000;1000;0)
К5 :=ПЗ(18%/12;3*12;К2;10000:0)

ПРИМЕЧАНИЕ
Число формул не ограничено, в каждой из них имеется ссылка на параметр Выплаты — ад­
рес ячейки постановки (в данном случае К2).


5. Выделить блок ячеек, начиная с ячейки подстановки, включающий все стро­
ки с формулами и столбцы со значениями параметра (на рис. 6.14 блок ячеек
затенен — ячейки K2:R5).
6. Выполнить команду меню Данные • Таблица подстановки. В диалоговом окне
Таблица подстановки указать адрес ячейки подстановки — Подставлять значе­
ния по столбцам в $К$2.
7. Нажать кнопку ОК.
В результате формируется массив формул вида {=ТАБЛИЦА(К2;)}. Ячейка
К2 — ячейка подстановки.
8. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Таблица подстановок для двух параметров
На листе рабочей книги готовятся два массива значений параметра, один из ко­
торых — вектор-строка, другой — смежный вектор-столбец. Вводится формула
функции, которая использует параметры. Для вычисления этой функции созда­
ется массив формул.
Пример 8
Требуется с помощью таблицы подстановки проанализировать влияние пара­
метров Норма (Параметр 1) и Выплата (Параметр 2) на функцию БЗ.
Последовательность действий.
1. Открыть рабочую книгу Анализ-xls с помощью команды меню Файл • Открыть.
2. Установить курсор на листе Анализ.
456 Глава 6. Специальные информационные технологии анализа данных в Microsoft Excel


3. Подготовить значения параметров Норма и Выплата в виде блоков ячеек
(рис. 6.15), ячейки М13:М15 и N3:T3, соответственно.

I I I I I I
Ячейка 1
Ячейка 2
Параметр 2

<< Пред. стр.

страница 54
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign