Практическое занятие 5. Экономические расчеты в MS Excel.

Таблица подстановки

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

На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные.

Таблицу подстановки можно использовать для следующих целей:

1. 
   изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул;
   
2. 
   изменения двух исходных значений, просматривая результаты только одной формулы.
   

Использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами. Рассмотрим эту методику на примере расчета ежемесячных вы­плат по займу (расчет происходит с помощью функции ПЛТ) и платежей по процентам (функция процплат):

Решение такой задачи предполагает следующие шаги:

1. 
   Создать или перейти на рабочий лист, где будет решаться ана­лизируемая задача.
   
2. 
   Организовать интерфейс таким образом, чтобы все вводимые данные были понятны пользователю:
   
   1. 
      в соответствующие ячейки рабочего листа вводятся необ­ходимые подписи и данные (рисунок 5.1).
      
   2. 
      в ячейку В5  формула:
      

=ПЛТ($B$4/12;$B$3*12;$B$2)

3. 
   в ячейку D6  формула:
   

=ПРОЦПЛАТ($D$4;$D$5;$D$3;$D$2)

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

Рисунок 5.1  Подготовка исходных данных задачи

3. 
   После подготовки исходных данных перейти к тому месту ра­бочего листа, где будут располагаться рассчитываемые значе­ния в зависимости от изменения одной переменной и от раз­личных рассчитываемых формул (рисунок 5.2).
   

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

Слева расположить различные значения исходных данных, ко­торые необходимо протестировать.

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

4. 
   Выделить ячейки, которые будут содержать таблицу. При этом самым левым столбцом этой таблицы должен быть стол­бец исходных значений, а самой верхней строкой должна быть строка анализируемых формул. Результат будет помещен в пустые ячейки (рисунок 5.3)
   

Рисунок 5.2  Подготовка изменяемого диапазона и расчетных формул для использования одномерной таблицы подстановки

Рисунок 5.3  Выделение диапазона

5. 
   Воспользоваться командой Данные | Таблица подстановки и в диалоговом окне Таблица подстановки (рисунок 5.4) указать, куда и какие значения необходимо подставлять.
   

В нашем примере  подстановка значений процентной ставки (столбец исходных значений А10:А19) происходит в ячейку В4, т. к. в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемые формулы. Ре­зультаты вычислений будут помещены в незаполненные ячей­ки выделенного диапазона (рисунок 5.5)

Рисунок 5.4  Использование таблицы подстановки

Рисунок 5.5  Рассчитанные значения для одномерной таблицы подстановки

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

1. 
   Организовать на рабочем листе соответствующий интерфейс пользователя для некоторого набора входных данных (рисунок 5.6):
   
   1. 
      конкретная процентная ставка  3% (ячейка В4);
      
   2. 
      конкретный срок погашения  3 года (ячейка В3);
      
   3. 
      формула для ячейки В5:
      

=ПЛТ($В$4/12;$В$3*12;$В$2)

Рисунок 5.6  Подготовка данных задачи

2. 
   Подготовить следующую таблицу (рисунок 5.7):
   
   1. 
      изменяемые данные поместить в левый столбец и верхнюю строку  в нашем случае значения процентной ставки (ячейка В4) располагаются в диапазоне В10:В14, а значе­ния срока погашения (ячейка ВЗ)  в диапазоне C9:F9;
      
   2. 
      на пересечении строки и столбца в верхнем левом углу расположить необходимую формулу или ссылку на нее (ячейка В9 содержит формулу ячейки В5, в которой обяза­тельна абсолютная адресация ячеек).
      

Рисунок 5.7  Подготовка диапазона для использования двумерной таблицы

подстановки

3. 
   Выделить диапазон ячеек, представляющий собой прямо­угольную область и содержащий формулу для расчета, изме­няемые данные для двух переменных и ячейки для результа­тов вычислений.
   
4. 
   Выполнить команду Данные | Таблица подстановки и в по­явившемся окне (рисунок 5.8) указать, куда и какие значения не­обходимо подставлять.
   

В рассматриваемом примере подстановка значений процент­ной ставки (столбец исходных значений В10:В15) происходит в ячейку В4, т. к. в этой ячейке изначально указывается вели­чина процентной ставки, входящей в рассчитываемую форму­лу, а подстановка значений сроков погашения (строка значе­ний C9:F9)  в ячейку В3. Результаты вычислений будут по­мещены в незаполненные ячейки выделенного диапазона (рисунок 5.9).

Рисунок 5.8  Использование таблицы подстановки при расчетах по двум

параметрам

Рисунок 5.9  Рассчитанные данные с использованием двумерной таблицы

подстановки

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

Мастер подстановок

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

Перед использованием мастера подстановок следует:

1. 
   расположить на рабочем листе исходные данные;
   
2. 
   определить расположение возвращаемых функцией данных и данных для поиска (их нахождение в соответствующих ко­лонках);
   
3. 
   задать строку для начала поиска;
   
4. 
   указать место на рабочем листе для помещения результата.
   

Перечень задач к практическому занятию № 5

«Таблица подстановки»

1. 
   Найти ежемесячные выплаты по займу размером 400 млн. руб. для различных сроков погашения и процентных ставок, ис­пользуя таблицу подстановки. Для решения задачи воспользо­ваться функцией ПЛТ (либо ППЛАТ).
   
2. 
   Предполагается, что в конце года капиталовложения по проек­ту составят около 1500 млн. руб. Ожидается, что за последую­щие 4 года проект принесет следующие доходы: 450, 480, 560, 600 млн. руб. Рассчитать чистую текущую стоимость проекта для различных норм дисконтирования и объемов капитало­вложения с использованием таблицы подстановки. Для реше­ния задачи воспользоваться функцией ЧПС (либо НПЗ).
   
3. 
   Облигации приобретены (дата соглашения) 09.09.2001 по курсу (цена) 89 и имеют купонный доход (ставка) в размере 10%, ко­торый выплачивается с периодичностью (частота)  один раз в полугодие. Предполагаемая дата погашения облигации (дата вступления в силу)  15.09.2005 по курсу (погашение) 100. Проанализировать влияние цены и купона облигации на годо­вую ставку помещения с помощью таблицы подстановки. Для решения задачи воспользоваться функцией ДОХОД. Временной базис расчета  1.
   
4. 
   Облигации номиналом 1000 руб. с купонной ставкой 8%, пе­риодичностью выплат 4 раза в год выпущена (дата выпуска) 01.09.2003, приобретена (дата соглашения) 05.10.2003. Дата пер­вой оплаты купонов 12.12.2003, базис расчетов  1. Проана­лизировать влияние на величину накопленного дохода часто­ты и купонной ставки с помощью таблицы подстановки. Для решения задачи воспользоваться функцией НАКОПДОХОД.
   
5. 
   Облигации имеют купонный доход (ставка) 11% с выплатой процентов (частота)  один раз в полугодие. Облигации при­обретены (дата соглашения) 01.05.2004 по курсу (цена)  98,8 с погашением (дата вступления в силу) 01.03.2005 по курсу (по­гашение) 100. Дата последнего купона (последний доход)  01.04.2004, базис расчета  1. Проанализировать влияние на величину дохода (ставки помещения) частоты и купонной ставки с помощью таблицы подстановки. Для решения задачи воспользоваться функцией доходпослнерег.
   
6. 
   Облигации выпущены (дата выпуска) 01.12.2003 и приобретены (дата соглашения) 01.05.2004. Предполагаемая дата погашения (дата вступления в силу) 01.03.2005 по курсу 100. Купонный доход (ставка)  10% с выплатой один раз в полугодие, став­ка помещения (погашение)  22%. Дата выплаты первого ку­пона 01.08.2004. Временной базис расчета  1. Проанализиро­вать изменение цены (курса) покупки в зависимости от даты купонной выплаты и даты приобретения ценной бумаги с по­мощью таблицы подстановки. Для решения задачи воспользо­ваться функцией ЦЕНАПЕРВНЕРЕГ.
   
7. 
   Облигации приобретены (дата соглашения) 01.05.2004. Пред­полагаемая дата погашения (дата вступления в силу) 01.03.2005 по курсу  100. Купонный доход (ставка)  8% с выплатой 4 раза в год, ставка помещения (доход)  9%. Дата выплаты первого купона (последний доход)  01.03.2004. Временной базис расчета  1. Проанализировать изменение цены (кур­са) покупки облигации в зависимости от ставки и дохода ценной бумаги с помощью таблицы подстановки. Для реше­ния задачи воспользоваться функцией ценапослнерег.
   
8. 
   Облигации приобретены (дата соглашения) 01.05.2004. Пред­полагаемая дата погашения (дата вступления в силу) 01.03.2005 по курсу  100. Купонный доход (ставка)  9,2% с выплатой один раз в полугодие, ставка помещения (до­ход)  10%. Временной базис расчета  1. Проанализиро­вать изменение курса покупки облигации при своевременном погашении купонных выплат в зависимости от ставки и час­тоты купонных выплат с помощью таблицы подстановки. Для решения задачи воспользоваться функцией цена.
   
9. 
   Бескупонные облигации на сумму (инвестиция)  200 000 приобретены (дата соглашения) 06.09.2002 с погашением (да­та вступления в силу) 12.09.2005 по цене (погашение)  250 000. Временной базис расчета  1. Проанализировать изменение годовой ставки дополнительного дохода (нараще­ния) в зависимости от величины инвестиции и цены (погаше­ния) с помощью таблицы подстановки. Для решения задачи воспользоваться функцией инорма.
   
10. 
    Вексель выдан (дата соглашения)  06.09.2002 на сумму (инвестиция)  250 000 руб., оплачен (дата вступления в силу) 12.09.2004 с учетной ставкой (скидка)  8%. Временной ба­зис расчета  1. Проанализировать изменение суммы к полу­чению по векселю (номинал) в зависимости от изменения ве­личины инвестиции и скидки с помощью таблицы подстанов­ки. Для решения задачи воспользоваться функцией получено.
    
11. 
    Облигации приобретены (дата соглашения)  11.08.2001, да­та погашения (дата вступления в силу)  25.11.2003, купон­ный доход (купон)  10% с выплатой процентов один раз в полугодие, годовая ставка дохода (доход)  12%. Временной базис расчета  1. Проанализировать длительность ценной бумаги в зависимости от изменения величины дохода и купо­на с помощью таблицы подстановки. Для решения задачи воспользоваться функцией длит.
    
12. 
    Облигации приобретены (дата соглашения)  14.08.2001, да­та погашения (дата вступления в силу)  25.12.2003. Частота купонных выплат  один раз в полугодие, купонная ставка (купон)  9%. Ставка помещения (доход)  11%. Временной базис расчета  1. Проанализировать модифицированную длительность ценной бумаги в зависимости от изменения ве­личины дохода и купона с помощью таблицы подстановки. Для решения задачи воспользоваться функцией МДЛИТ.
    

Содержание отчета и его форма

Отчет по практическому занятию оформляется в виде рабочей книги MS Excel (по каждой задаче отдельная страница рабочей книги) и должен включать результаты выполнения индивидуальных заданий. Варианты индивидуальных заданий к практическому занятию представлены в таблице 5.1. Вариант совпадает с номером фамилии студента в списке учебной группы.

Таблица 5.1  Варианты индивидуальных заданий

Вариант	Номера задач, подлежащих решению
1	1	2	3
2	2	3	4
3	3	4	5
4	4	5	6
5	5	6	7
6	1	7	8

Продолжение таблицы 5.1

Вариант	Номера задач, подлежащих решению
7	7	8	12
8	8	9	10
9	2	10	11
10	3	5	8
11	4	10	12
12	5	8	11
13	6	7	12
14	7	9	12

Условия получения зачета по практическому занятию № 5

Результаты выполнения индивидуального задания в виде рабочей книги MS Excel (по каждой задаче отдельная страница рабочей книги) сохраняются на дискете, и представляется студентом преподавателю для проверки и последующей защиты. Защита результатов практического занятия производится студентом только индивидуально.

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

Рабочие книги MS Excel, оформленные с отступлениями от требований настоящих указаний к защите не принимается.

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

1. 
   Каковы возможности средства экономических расчетов MS Excel Таблица подстановки?
   
2. 
   Какова методика применения средства экономических расчетов MS Excel Таблица подстановки? Приведите пример.
   
3. 
   Каковы особенности использование таблицы подстановки с двумя изменяющимися переменными и одной формулой?
   
4. 
   Какие финансовые функции были использованы при выполнении индивидуального задания? Какие аргументы у этих функции? Каково название этих функции в ранних версиях MS Excel?
   
5. 
   Какова методика работы с финансовыми функциями MS Excel?
   
6. 
   О чем следует помнить при задании аргументов финансовых функций MS Excel?
   
7. 
   Как было выполнено индивидуальное задание?
   
8. 
   Какие выводы можно сделать по результатам выполнения индивидуального задания?