Створення сценарію.

 

Банк Аваль запропонував клієнту два варіанти вкладення коштів по депозиту згідно відповідної акції. Початкову суму PV у 5000 грн. можна вкласти у банк на 3 роки (n = 3) під 4% (r = 4), або на 4 роки під 3%. Необхідно визначити який варіант вкладення коштів є більш раціональним і на яку суму.

Загальний підхід. Після оформлення вхідної інформації у відповідному вигляді (див. рис. 16) послідовність команд Сервис Þ Сценарии… приводить до виклику Диспетчера сценария, якийвиводить на монітор робоче вікно Добавление сценария (рис. 17). За його допомогою можна: додавати новий сценарій під деякою назвою до множини сценаріїв, вилучати не­вдалий чи редагувати його, виводити на екран результати застосування сценарію, створювати звіт за всіма сценаріями, скористатися зі сценаріїв, створених на інших сторінках.

Результати застосування всіх сценаріїв можна подати у вигляді звіту. Для цього у вікні Добавление сценария використовуєтьсякнопка звіт і в новому робочому вікні вибирається тип звіту, у більшості випадків Структура, вибирається клітина-результат і по завершенню кнопка ОК. Звіт виводиться на окремій сторінці. В його структурі задіяні кнопки "+" і "-", які дають змогу згортати чи розгортати рівні звіту, а також здійснювати інші процедури.

 

Практична частина.

 

Використовуючи стандартні процедури роботи з Excel сформувати первинну інформацію у вигляді поданому на рис. 16. Клітини В5 і С5 використано для формули FV=PV*(1+r)^n, яка згідно розташованої первинної інформації трансформується наступним чином =$B$2*(1+$B$4/100)^B3 і =$С$2*(1+$С$4/100)^С3 Клітину С6 використано для формули =С5-В5, зміст якої очевидний.

По завершенню оформлення і введення необхідної інформації робочий лист повинен мати вид поданому на рис. 16. Для більш продуктивної роботи в подальшому робочий лист бажано перейменувати, що і здійснено надавши йому назву Сценарій.

Для отримання результатів, що характеризують кінцевий варіант розрахунку, необхідно стандартними методами задіяти блок команд: Сервис Þ Сценарии… У робочому вікні Диспетчер сценариев (рис. 18) задіяти режим Добавить. У робочому вікні Добавление сценария (рис. 17) у блоці Название сценария ввести відповідну назву, для прикладу Сценарій_1. У блоці Изменяемые ячейки стандартними методами виділити блок В3:С4. У блоці Защита встановити режим махисту, тобто встановити символ “галочка”. По завершенню ОК.

У робочому вікні Значение ячеек сценария (рис. 19) залишити варіант запропонований системою і ОК. На монітор виводиться робоче вікно Диспетчер сценариев (рис. 20) у якому зафіксовано, що користувач розробив сценарій ім’я якого Сценарій_1.

Для виведення звіту про сценарій необхідно задіяти кнопку Отчет. У робочому вікні Отчет по сценарию (рис. 21) Вибрати режим Структура і у якості Ячейки результата: вказати клітину С6. По завершенню ОК.

Диспетчер сценарієв виводить на монітор робоче вікно Диспетчера сценариев в режимі Структура (рис. 22). Слід відмітити, що автоматично Диспетчер сценариев формує новий робочий лист надаючи йому назву Структура сценариев (рис. 22).

Для формування нового сценарію необхідно перейти на лист Сценаріїввести команди: Сервис Þ Сценарии… і у робочому вікні Диспетчер сценариев (рис. 18) задіяти режим Добавить. У робочому вікні Добавление сценария (рис. 17) у блоці Название сценария ввести відповідну назву, для прикладу Сценарій_2. У блоці Изменяемые ячейки стандартними методами виділити блок В3:С4. У блоці Защита встановити режим захисту, тобто встановити символ “галочка”. По завершенню ОК.

У робочому вікні Значение ячеек сценария (рис. 19) ввести необхідні значення (у нашому варіанті це 3 і 4 а також 4 і 3) і ОК. На монітор виводиться робоче вікно Диспетчер сценариев (рис. 20) у якому буде зафіксовано, що користувач розробив ще один сценарій ім’я якого Сценарій_2, назва якого добавлена у відповідний список.

У режимі Отчет загальний вид робочого вікна подано на рис. 23. Економічна інтерпретація отриманого результату розрахунків очевидна, тому пропонується зробити її користувачу самостійно.

Завдання для самостійної роботи. Використовуючи інструментарій Сценарии добавити третій сценарій. Інформацію для формування сценарію визначити самостійно.

 

 

Завдання для самостійного опрацювання теми лабораторної роботи.

 

Самостійно вирішити наведені нижче завдання, прийнявши до розв'язання задачі згідно наведеної нижче табл. 1.
Таблиця 1
№ варіанту	Номер першого завдання	Номер другого завдання		№ варіанту	Номер першого завдання	Номер другого завдання

 

 

Задача 1. Відомий розмір внеску, який буде поміщений у банк на деякий термін під певний відсоток, вихідні дані про які наведені у табл. 1.
Таблиця 1
Вихідні дані
№ п/п	A	B
	Розмір внеску: PV	4 000 грн.
	Термін внеску, років: n
	Процентна ставка: r	12%
Коефіцієнт збільшення внеску при нарахуванні складних відсотків обчислюється за формулою: = (1+ r)^n, де r – процентна ставка, n – термін повернення внеску, а символ ^ – оператор “піднесення до ступіня”. Сума в кінці терміну FV обчислюється по формулі: = PV*(1+ r)^n, тобто згідно формули (1). Використовуючи відповідні підходи (загальні методи роботи з Excel, фінансові функції, інструментарії: Подбор параметра, Таблицы подстановки, Сценарии) потрібно: 1. Розрахувати суму в кінці періоду: а) методом застосування формули поданої у табл. 1; б) методом використання відповідної фінансової функції. 2. Визначити умови, при яких буде отримана сума у 10000 грн.: а) у випадку коли міняється процентна ставка; б) у випадку коли міняється термін внеску, роки. 3. Розрахувати суму в кінці періоду у випадку коли міняється процентна ставка і термін внеску одночасно. Використати інструментарій Таблицы подстановки. 4. Показати на власному прикладі порядок роботизінструментарієм Сценарии.

Задача 2. Відомий розмір внеску, який буде поміщений у банк на деякий термін під певний відсоток, вихідні дані про які наведені у табл. 1.
Таблиця 1
Вихідні дані
№ п/п	A	B
	Розмір внеску: PV	4500 грн.
	Термін внеску, років: n
	Процентна ставка: r	8%
Коефіцієнт збільшення внеску при нарахуванні складних відсотків обчислюється за формулою: = (1+ r)^n, де r – процентна ставка, n – термін повернення внеску, а символ ^ – оператор “піднесення до ступіня”. Сума в кінці терміну FV обчислюється по формулі: = PV*(1+ r)^n, тобто згідно формули (1). Використовуючи відповідні підходи (загальні методи роботи з Excel, фінансові функції, інструментарії: Подбор параметра, Таблицы подстановки, Сценарии) потрібно: 1. Розрахувати суму в кінці періоду: а) методом застосування формули поданої у табл. 1; б) методом використання відповідної фінансової функції. 2. Визначити умови, при яких буде отримана сума у 12000 грн.: а) у випадку коли міняється процентна ставка; б) у випадку коли міняється термін внеску, роки. 3. Розрахувати суму в кінці періоду у випадку коли міняється процентна ставка і термін внеску одночасно. Використати інструментарій Таблицы подстановки. 4. Показати на власному прикладі порядок роботизінструментарієм Сценарии

Задача 3. Відомий розмір внеску, який буде поміщений у банк на деякий термін під певний відсоток, вихідні дані про які наведені у табл. 1.
Таблиця 1
Вихідні дані
№ п/п	A	B
	Розмір внеску: PV	6000 грн.
	Термін внеску, років: n
	Процентна ставка: r	9%
Коефіцієнт збільшення внеску при нарахуванні складних відсотків обчислюється за формулою: = (1+ r)^n, де r – процентна ставка, n – термін повернення внеску, а символ ^ – оператор “піднесення до ступіня”. Сума в кінці терміну FV обчислюється по формулі: = PV*(1+ r)^n, тобто згідно формули (1). Використовуючи відповідні підходи (загальні методи роботи з Excel, фінансові функції, інструментарії: Подбор параметра, Таблицы подстановки, Сценарии) потрібно: 1. Розрахувати суму в кінці періоду: а) методом застосування формули поданої у табл. 1; б) методом використання відповідної фінансової функції. 2. Визначити умови, при яких буде отримана сума у 18000 грн.: а) у випадку коли міняється процентна ставка; б) у випадку коли міняється термін внеску, роки. 3. Розрахувати суму в кінці періоду у випадку коли міняється процентна ставка і термін внеску одночасно. Використати інструментарій Таблицы подстановки. 4. Показати на власному прикладі порядок роботизінструментарієм Сценарии.

Задача 4. Відомий розмір внеску, який буде поміщений у банк на деякий термін під певний відсоток, вихідні дані про які наведені у табл. 1.
Таблиця 1
Вихідні дані
№ п/п	A	B
	Розмір внеску: PV	4000 грн.
	Термін внеску, років: n
	Процентна ставка: r	12%
Коефіцієнт збільшення внеску при нарахуванні складних відсотків обчислюється за формулою: = (1+ r)^n, де r – процентна ставка, n – термін повернення внеску, а символ ^ – оператор “піднесення до ступіня”. Сума в кінці терміну FV обчислюється по формулі: = PV*(1+ r)^n, тобто згідно формули (1). Використовуючи відповідні підходи (загальні методи роботи з Excel, фінансові функції, інструментарії: Подбор параметра, Таблицы подстановки, Сценарии) потрібно: 1. Розрахувати суму в кінці періоду: а) методом застосування формули поданої у табл. 1; б) методом використання відповідної фінансової функції. 2. Визначити умови, при яких буде отримана сума у 9000 грн.: а) у випадку коли міняється процентна ставка; б) у випадку коли міняється термін внеску, роки. 3. Розрахувати суму в кінці періоду у випадку коли міняється процентна ставка і термін внеску одночасно. Використати інструментарій Таблицы подстановки. 4. Показати на власному прикладі порядок роботизінструментарієм Сценарии.

Задача 5. Відомий розмір внеску, який буде поміщений у банк на деякий термін під певний відсоток, вихідні дані про які наведені у табл. 1.
Таблиця 1
Вихідні дані
№ п/п	A	B
	Розмір внеску: PV	4 000 грн.
	Термін внеску, років: n
	Процентна ставка: r	12%
Коефіцієнт збільшення внеску при нарахуванні складних відсотків обчислюється за формулою: = (1+ r)^n, де r – процентна ставка, n – термін повернення внеску, а символ ^ – оператор “піднесення до ступіня”. Сума в кінці терміну FV обчислюється по формулі: = PV*(1+ r)^n, тобто згідно формули (1). Використовуючи відповідні підходи (загальні методи роботи з Excel, фінансові функції, інструментарії: Подбор параметра, Таблицы подстановки, Сценарии) потрібно: 1. Розрахувати суму в кінці періоду: а) методом застосування формули поданої у табл. 1; б) методом використання відповідної фінансової функції. 2. Визначити умови, при яких буде отримана сума у 12000 грн.: а) у випадку коли міняється процентна ставка; б) у випадку коли міняється термін внеску, роки. 3. Розрахувати суму в кінці періоду у випадку коли міняється процентна ставка і термін внеску одночасно. Використати інструментарій Таблицы подстановки. 4. Показати на власному прикладі порядок роботизінструментарієм Сценарии.

6. Задача 6. Використовуючи умову задачі 1 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожні півроку.

7. Задача 7. Використовуючи умову задачі 2 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожні півроку.

8. Задача 8. Використовуючи умову задачі 3 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожні півроку.

9. Задача 9. Використовуючи умову задачі 4 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожні півроку.

10. Задача 10. Використовуючи умову задачі 5 здійснити розрахунок суми в кінці терміну FV при умові що нарахування процентів здійснюються кожні півроку.

11. Задача 11. Використовуючи умову задачі 1 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний квартал.

12. Задача 12. Використовуючи умову задачі 2 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний квартал.

13. Задача 13. Використовуючи умову задачі 3 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний квартал.

14. Задача 14. Використовуючи умову задачі 4 здійснити розрахунок суми в кінці терміну FV при умові що нарахування процентів здійснюються кожний квартал.

15. Задача 15. Використовуючи умову задачі 5 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний квартал.

16. Задача 16. Використовуючи умову задачі 1 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний місяць.

17. Задача 17. Використовуючи умову задачі 2 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний місяць.

18. Задача 18. Використовуючи умову задачі 3 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний місяць.

19. Задача 19. Використовуючи умову задачі 4 здійснити розрахунок суми в кінці терміну FV при умові що нарахування процентів здійснюються кожний місяць.

20. Задача 20. Використовуючи умову задачі 5 здійснити розрахунок суми в кінці терміну FV при умові, що нарахування процентів здійснюються кожний місяць.

21. Використовуючи умову задачі 1 розрахувати початкову суму РV при умові, що нарахування процентів здійснюються у кінці терміну (тобто року), а виплачена по депозиту сума FV склала величину 6381,41 грн.

22. Використовуючи умову задачі 2 розрахувати початкову суму РV при умові, що нарахування процентів здійснюються у кінці терміну (тобто року), а виплачена по депозиту сума FV склала величину 8842,73 грн.

23. Використовуючи умову задачі 3 розрахувати початкову суму РV при умові, що нарахування процентів здійснюються у кінці терміну (тобто року), а виплачена по депозиту сума FV склала величину 7140,93 грн.

24. Використовуючи умову задачі 4 розрахувати початкову суму РV при умові, що нарахування процентів здійснюються у кінці терміну (тобто року), а виплачена по депозиту сума FV склала величину 7770,17 грн.

25. Використовуючи умову задачі 5 розрахувати початкову суму РV при умові, що нарахування процентів здійснюються у кінці терміну (тобто року), а виплачена по депозиту сума FV склала величину 7895,29 грн.

Завдання для поглиблення знань.

Завдання 1. Куплено облігацію, номінальна вартість якої становить 2000 грн., виплата за нею – 100 грн. на рік, відсоткова ставка – 12%, термін дії облігації – 10 років. Необхідно проаналізувати зміну вартості обліга­ції залежно від терміну погашення. Вартість облігації розраховують за допомогою функції ПС. Відповідь 857,04 грн.

Завдання 2. Необхідно розрахувати щорічний розмір платежу за позику 15000 грн. терміном на 5 років та відсотковою ставкою 12%. Використати функцію ПЛТ. Відповідь 4161,15 грн.

Завдання 3. Необхідно розрахувати, яку суму щомісячно слід переводити на рахунок банку, щоб при від­сотковій ставці 20% річних за 3 роки вклад досягнув суми 5000 грн. Використати функцію ПЛТ. Відповідь 185,82 грн.

Завдання 4. Необхідно розрахувати відсоткову ставку для трирічної позики розміром 5000 грн. при щомісячно­му вкладі 185 грн. Використати функцію Ставка. Відповідь 1,6667% для річної відсоткової ставки 20%.

Завдання 5. Куплено облігацію, номінальна вартість якої становить 1000 грн. Виплата за нею – 150 грн. на рік, відсоткова ставка – 22%, термін дії облігації – 10 років. Розрахувати вартість облігації на третій рік. Використати функцію ПС. Відповідь 857,04 грн.

Завдання 6. Номінальна ціна акції становить 300 грн., ціна для покупців – 270 грн., термін облігації – З роки; річна відсоткова ставка – 32%, періодичність виплат відсотків – 2 рази на рік. Розрахувати до­хід від акції. Використати функцію ДОХОД. Відповідь 0,09 або 9%.

Завдання 7. Необхідно розрахувати ціну облігації, дата купівлі якої 15 грудня 1998 року, термін погашен­ня – 20 січня 2005 року, ставка купона, який виплачу­ють раз на рік, – 4%, норма прибутковості – 10%, ціна облі­гації при продажу – 100 грн. Використати функцію ЦЕНА. Відповідь 73,54 грн.