Продемонструйте сім аркушів і закінчіть роботу.

Контрольні запитання

1. Як реалізуються розгалуження в ЕТ?

2. Яке значення функції ЯКЩО(1+3>5-2; 1; 2)?

3. Як обчислити суму чисел у стовпці?

4. Який загальний вигляд має функція ЯКЩО?

5. Як скопіювати формулу? Яке значення функції ЯКЩО(5=5; МІЩ4; 6); МАКС(4; 6))?

6. Які ви знаєте логічні функції?

7. Яке значення функції 1(2=2; 3=3; 3<4)?

8. Яке значення функції ЯКЩО(2>1; 10-5; 20/2)?

9. Яке призначення кнопки Автосума?

 

10. Що таке абсолютна і змішана адреси клітинки?

11. Як скопіювати таблицю на іншу сторінку?

169

12. Яка відмінність між відносними і абсолютними адресами?

13. Яке значення функції ЯКЩ0(1=2; 15; ЯКЩО(ТІШЕ;20;25)?

14. Яке значення функції АБО(1=2; 3=3; 4=5; 0,5>sin(250))?

15. Як ввести дату в клітинку?

16. Який загальний вигляд має логічна функція І?

17. Який розділювач можна використовувати у списках
аргументів функції?

18. Як визначити, скільки днів минуло від вашого дня народження?

19. Як вставити стовпець у таблицю? Яке значення ЯКЩО(5>2; ЯКЩО(5<3; 2; 4); 8)?

20. Як заповнити стовпець значеннями арифметичної прогресії?

21. Як задати чи скасувати режим відображення формул?

22. Який загальний вигляд має логічна функція АБО?

23. Як вилучити рядок з таблиці?

24. Як розграфити таблицю? Яке значення функції
ЯКЩО(АБО(3<5; 4<8); 8; 12)?

25. Яку стандартну функцію заміняє кнопка Автосума?

26. Як виокремити несуміжні діапазони клітинок?

27. У чому полягає метод підбору параметра?

28. Як перейти на іншу сторінку? Як перейменувати сторінку?

 

29. Як очистити весь стовпець? Яке значення ЯКЩО(8>2; СУММ(2; 8); МАКС(2;8»?

30. Які задачі можна розв'язати методом підбору параметра?

31. Які є функції для роботи з датами?

32. Яка відмінність між логічними функціями І та АБО?

33. Що таке засіб «Пошук розв'язку» (Solver)?

34. Яке значення функції ЯКЩО(2>1; ЯКЩО(1>2; 5; 8); 6)?

35. Які є способи розв'язування нелінійного рівняння?

Практична робота № 23

Тема

MS Excel. Фінансові функції.

Мета

Вміти використовувати фінансові функції для аналізу вигід­ності інвестицій в бізнес.

План

1. Функція для визначення майбутньої вартості теперішніх інвестицій.

2. функції для визначення виплат для погашення позики.

3. Функції для визначення теперішньої вартості майбутніх інвестицій.

4. Функції користувача. Створення модуля.

Теоретичні відомості

Фінансові функції використовують для розв'язування задач планування фінансової діяльності, визначення прибутків, аналізу вигідності капіталовкладень, кредитно-інвестиційної політики то­що. Інвестицією називають вкладання грошей у деякий бізнес на певних умовах. Позика у банку називається кредитом, а внесок на рахунок в банк — депозитом. Надходження грошей від деякого бізнесу називають рентою. Розглянемо основні параметри фі­нансових функцій і їхні скорочені назви:

♦ процентна ставка (ПС) виражається у відсотках і може бути добовою, місячною, річною тощо;

♦ кількість періодів (КП) кожний тривалістю доба, місяць, рік;

 

♦ періодична виплата (ПВ) — сума, яку виплачує клієнт щоперіоду (це від'ємне число), або сума, яку отримує клієнт що-періоду (це додатне число);

♦ сума внеску (СВ) — сума інвестиції, капіталовкладення, початкового внеску (це від'ємне число або нуль);

♦ тип операції (Т) — число 0, якщо виплата здійснюється наприкінці кожного періоду, і число 1, якщо на початку.

Розрізняють кредитну і депозитну процентні ставки. Кредит­на ставка є вищою за депозитну. Процентна ставка повинна бути узгоджена з тривалістю періоду, наприклад, річна ставка 60% дорівнює місячній ставці 5%. У цій роботі вважається, що місяч­на депозитна ставка — 5%, а кредитна — 6% (так було у 1997 p.). Зауважимо, що у 2005 р. ці ставки становили відповідно 1 і 2%.

1. Функція для визначення майбутньої вартості теперішніх інвестицій. Функція має вигляд БЗ(ПС; КП; ПВ; СВ; Т). Англі­йська назва функції FV.

Якщо параметр має значення 0, то його можна не зазначати. Якщо параметр пропускають в середині списку параметрів, то два розділювачі (у цьому випадку;) мають бути поряд.

Задача 1. Інвестор вкладає в бізнес 2000 грн (чи відкриває на цю суму рахунок у банку) на умовах 5% ставки прибутку щомісяця. Яка вартість інвестиції через 36 місяців?

Розв'язок задачі дає така формула:

=БЗ(5%;36;;-2000)

Відповідь: 11 583,63 грн.

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

Задача 2. Клієнт відкриває рахунок у банку на умовах 5% ставки прибутку щомісяця, кладе на рахунок 2000 грн і планує на початку кожного місяця забирати з рахунку 100 грн. Яка сума буде на рахунку через 36 місяців?

 

170

171

=БЗ(5%; 36; 100; -2000; 1) Відповідь: 1 520,82 грн.

Задача 3. Умова та сама, але клієнт планує не забирати, а докладати по 100 грн на початку кожного місяця.

=БЗ(5%; 36; -100; -2000; 1)

Відповідь: 21 646,45 грн.

Функція масиву для визначення майбутньої вартості ін­вестиційного капіталу на умовах нарахування різних процентів за певну кількість (до 30) періодів має вигляд БЗРАСПИС(каігітал; масив процентів), але є не у всіх програмах. Англійська назва функції FVSCHEDULE.

Задача 4. Фірма інвестує 2000 грн протягом шести місяців за умови таких щомісячних процентних ставок 7%,6%,5%,4%, 4%,4%. Яка вартість інвестиції через шість місяців?

= БЗРАСПИС(2000; {0,07; 0,06; 0,05; 0,04; 0,04; 0,04})

Відповідь: Shift + Ctrl + Enter дає 2 679,22 грн. Такий бізнес невигідний. Ліпше покласти 2000 грн у банк під 5% на 6 місяців і, нічого не роблячи, отримати Б3(5%;6;;-2000) = 2 680,19 грн.

2. Функції для визначення виплат для погашення позики.

Введемо нові терміни і їхні скорочені назви:

♦ номер періоду (НП);

♦ сума позики (СП);

♦ кінцеве значення позики (КЗ).

Функція ППЛАТ (англ. РМТ ) призначена для визначення суми періодичних виплат для погашення боргу і має вигляд ППЛАТ(ПС; КП; СП; КЗ; Т). Така виплата складається з двох частин, які обчислюють за допомогою двох функцій ПЛПРОЦ і ОСНПЛАТ (англ. ІРМТ та РРМТ), а саме:

а) виплата за процентами ПЛПРОЩПС; НП; КП; СП; КЗ; Т);

б) основна виплата ОСНПЛАТ(ПС; НП; КП; СП; КЗ; Т).
Виплата за процентами щоперіоду зменшується, а основна

виплата щоперіоду зростає, їхня сума постійна і дорівнює ППЛАТ. Задача 5. Бізнесмен взяв у банку кредит на суму 2000 грн терміном на 12 місяців за умови щомісячного погашення позики і місячної ставки кредиту 6%. Визначити величину щомісячних виплат і її складові наприкінці першого місяця.

= ППЛАТ(6%; 12; 2000)

Відповідь: -238,55 грн.

= ПЛПРОЦ(6%; 1» 12; 2000)

Відповідь: -120,00 грн.

= ОСНПЛАТ(6%; 1; 12; 2000)

172

Відповідь: -118,55 грн.

Задача 6. Побудувати таблицю значень двох складових ПЛПРОЦ і ОСНПЛАТ щомісячних виплат наприкінці кожного місяця за кредит (2000 грн, 6%) протягом року. Розв'яжть задачу самостійно.

Розглянемо функцію КПЕР (англ. назва NPER), яка обчис­лює кількість періодів, потрібних для погашення суми позики, наданої під деяку процентну ставку за умови заздалегідь заданої суми періодичних виплат: КПЕР(ПС; ПВ; СП; КЗ; Т).

Задача 7. Позику 2000 грн беруть за умови повертанняш-прикінці кожного місяця 200 грн і процентної ставки 6%. Скільки місяців потрібно для повертання позики?

=КПЕР(6%; -200; 2000)

Відповідь: 15,73 місяця.

Функція НОРМА(КП; ПВ; СП; КЗ; Т; початкове наближен­ня) визначає вигідність надання позики, тобто реальну процетну ставку від надання позики на певну суму за умови фіксованих періо­дичних виплат протягом деякої кількості періодів. Тут потрібно задати деяке початкове наближення до шуканої процентної станси, наприклад 0,1 (10%). (Англ. RATE, в російській версії MS Office ХР — СТАВКА).

Задача 8. Бізнесмен звертається до банку за позикою (кре­дитом) на суму 2000 грн на 12 місяців за умови періодичних виплат 200 грн наприкінці кожного місяця протягом року. Визначити процентну ставку позики.

=НОРМА(12; -200; 2000; 0; 0; 0,1)

Відповідь: 3%. Така позика для банку є невигідною, якщо місячна депозитна процентна ставка, наприклад, 5%. Банк позики не надасть.

3. Функції для визначення теперішньої вартості майбутніх інвестицій. Розглянемо функції для визначення вигідності інвестицій (капіталовкладень) у деякий бізнес.

Депозитна процентна ставка (ДПС) — це ставка, яку банк виплачує за вклади клієнтів.

Функція ПЗ(ДПС; КП; рента за один період; рента в кінці терміну; Т) обчислює сьогоднішню вартість низки майбутніхшд-ходжень (ренти) від бізнесу (англ. PV, в рос. MS Office ХР — ПС).

Для обчислення сьогоднішньої вартості майбутньої ренти використовується принцип дисконтування — приведення суми ренти (доходу) за деякий термін до її вартості в цей момент часу. Суттєво враховується депозитна процентна ставка, оскільки вважається, що вже перше надходження стає депозитом у банку.

173

Дисконтування дає відповідь на запитання: чи варто вкладати гроші в такий бізнес, чи краще їх поставити в банк під відсотки і нічого не робити.

Задача 9. Нехай для ведення деякого бізнесу потрібно вкласти сьогодні 3500 грн, а бізнес протягом п'яти місяців дава­тиме по 1000 грн доходу (ренти) наприкінці місяця. Депозит­на ставка банку 5%. Чи варто займатися цим бізнесом?

=113(5%; 5; 1000)

Відповідь: вартість бізнесу (гранично допустима інвестиція) — 4 329 грн. Оскільки цю суму потрібно вкладати, число отри­маємо від'ємне. Бізнес вигідний, бо для його ведення потрібно лише 3 500 грн. Якби потрібно було більше, ніж 4 329 грн, то такий бізнес був би збитковим.

Задача 10. Умова та ж, що й в задачі 9, але ренту (дохід) 4500 грн планується отримати наприкінці терміну. Чи вигідний такий бізнес?

=ПЗ(5%;5;;4500)

Відповідь: сьогоднішня вартість ренти 3 525 грн (отримаємо від'ємне число). Такий бізнес вигідним вважати не можна. Причина — немає змоги реінвестувати ренту.

Розглянемо функцію НПЗ(ДПС;рента1; рента2;...), яка об­числює сьогоднішню вартість різних рент, що надходять напри­кінці рівномірних періодів (англ.: NPV, в рос. Office ХР — ЧПС).

Задача 11. У бізнес потрібно вкласти сьогодні 25 000 грн. Наприкінці першого місяця потрібно вкласти ще 2000 грн., а в наступні п'ять місяців бізнес даватиме такі доходи: 4 000,5 000, 6 000, 7 000, 8 000 грн. Чи цей бізнес вигідний?

=НПЗ(5%; -2000; 4000; 5000; 6000; 7000; 8000)

Відповідь: вартість бізнесу 22 433 грн. Оскільки затрати 25 000 грн більші за вартість бізнесу, то такий бізнес невигідний.

Задача 12. Ви маєте сьогодні вкласти у бізнес 25 000 грн і будете вести його протягом п'яти місяців. Методом проб побудувати фінансову модель вигідного бізнесу.

Нехай очікуваний дохід щомісяця відповідно такий: 5000, 6000, 7000, 8000, 9000 — всього 35000. Оцінимо вартість цих рент:

=НПЗ(5%; 5000; 6000; 7000; 8000; 9000)

Відповідь: 29 884 грн. Вартість рент є більшою за інвестицію (25000), тому такий бізнес вигідний.

4. Функції користувача. Створення модуля. Користувач може побудувати власні фінансові чи інші функції і використо­вувати їх як стандартні.

Задача 13. Побудувати функцію для визначення суми в гривнях, яку треба заплатити за деяку валюту згідно з поточним курсом.

Function Гривні(Валюта, Курс) Гривні = Валюта * Курс

End Function

Покажемо, як користуватися такою функцією. Нехай треба купити 20 доларів по 5,15 грн за долар:

=Гривні(20; 5,15)

Відповідь: 103,00 грн потрібно заплатити в касу.

Для створення власної функції потрібно виконати команди Сервіс о Макрос ■=> Редактор Visual Basic о Вставити о Модуль. Ввести текст функції у вікно модуля, що відкриється, і закрити вікно модуля і вікно редактора.

Хід роботи

Придумати, записати умови і розв'язати 13 задач, подібних до наведених вище. Ваші задачі можуть відрізнятися від описаних лише числами. Зокрема, врахуйте нові поточні депозитні та кредитні ставки банків. Основна грошова сума (інвестиції, вклади, позика, рента), що наявна в умові задачі, визначається дописуванням трьох нулів до номера вашого варіанта. Записати відповідні фінансові функції заздалегідь, залишаючи у звіті два рядки для результатів і висновків, які вписати під час виконання роботи. Під час виконання роботи у стовпець А заносити текст "Задача №...", поряд у стовпець В вводити формули з фінансовими функціями й отримувати результати. У стовпець С вводити висновки: "Вигідно" або "Невигідно".

Контрольні запитання

1. Яке призначення функції БЗ (FV)?

2. Яке призначення функції БЗРАСПИС (FVSCHEDULE)?

3. Яке призначення функції ППЛАТ (РМТ)?

4. Яке призначення функції ПЛПРОЦ (РРМТ)?

5. Яке призначення функції ОСНПЛАТ (ІРМТ)?

6. Яке призначення функції КПЕР (NPER)?

7. Яке призначення функції НОРМА (RATE)?

8. Яке призначення функції ПЗ (PV)?

9. Яке призначення функції НПЗ (NPV)?

 

10. Яке призначення функції" Гривні?

11. Як створити функцію користувача?

12. Які функції використовуються для обчислення теперішньої вартості майбутніх інвестицій?

13. Які функції використовуються для обчислення майбутньої вартості теперішніх інвестицій?

14. Що означає принцип дисконтування?

 

174

175

15. Що таке рента і інвестиція, кредит і депозит?

16. Клієнт відкриває рахунок у банку, кладе 3000 грн на 5% і докладатиме наприкінці кожного місяця 200 грн. Яка сума буде на рахунку через 12 місяців?

17. Чи вигідно 5000 грн інвестувати в бізнес на три місяці, якщо пропонуються ставки доходу 7, 5 і 4%?

18. Підприємець бере позику 5000 грн у банку під 6% місячних терміном на 6 місяців. Визначте щомісячну виплату та її складові у першому і другому місяцях.

19. Підприємець бере позику 4000 грн у банку під 6% місячних терміном на 4 місяців. Визначте щомісячну виплату та її складові у всіх місяцях.

20. Який термін потрібний, щоб повернути банку кредит 3000 грн, взятий під 6% за умови повертання наприкінці кожного місяця 500 грн?

21. Деякий бізнес даватиме щомісяця дохід (ренту) 500 грн про­тягом шести місяців. Яка сьогоднішня вартість ренти?

22. Підприємець планує отримувати ренту протягом 4 місяців: 500, 700, 900 і 1000 грн. Яка сьогоднішня вартість ренти?

 

23. Побудуйте функцію користувача Сант(дюйми), яка пере­водить дюйми в сантиметри, знаючи, що 1 дюйм= 2,54 см.

24. Побудуйте функщю користувача Вклад(р, т, сума), яка виз­начає величину вкладу деякої суми в банк під р% через т місяців.

25. Обчисліть значення функції Гривні(25; 5,05).

Практична робота № 24

Тема

MS Excel. Інструменти Пошук розв'язку, Таблиця підстановки.

Мета

Вміти користуватися інструментами Пошук розв'язку і Таб­лиця підстановки для аналізу підприємницької діяльності і прийняття рішень.

План

1. Планування випуску продукції.

2. Аналіз кредиту таблицями підстановки.

Задача 9. Планування випуску продукції

Для виготовлення виробів х, у, z використовують три види сировини: І, II, III. У таблиці задано норми витрат сировини на один виріб кожного виду, ціна одного виробу, а також кількісті сировини кожного виду, яку можна використати. Скільки виробів кожного виду потрібно виготовити, щоб прибуток був максимальний (п — номер варіанта)?

хуг     Загальна к-сть сировини

I            18 15 12                   360 -ті

II           6   4   8                      192

ПІ          5   3   3                      180 + п

Ціна 9   10 16

Задача 10. Аналіз кредиту

Підприємець бере кредит на деяку суму під місячну ставку 6% і, зважаючи на свою щомісячну платоспроможність, повинен прийняти рішення, на скільки місяців його брати. Побудувати таблицю щомісячних виплат для різних термінів кредиту, наприклад, 4, 5, 6, 7 місяців і реальних сум, що повинні бути виплачені за кредит протягом усього терміну. Побудувати двовимірну таблицю щомісячних виплат з ура­хуванням двох параметрів: можливих сум позики і термінів позики.

Теоретичні відомості

1. Планування випуску продукції. Задача 9 є задачею ліній­ного програмування,. Вона розв'язується за допомогою інстру­мента Пошук розв'язку (Solver).

Математична модель задачі. Позначимо через х, у, z шукані кількості виробів трьох видів. Потрібно визначити х, у, z, для яких досягається максимум функції прибутку / = 9х + Юу + 16г за таких обмежень:

18л: +15у + 12 z<= 360 - п 6х + 4у + 8z <= 192 5х +3у + 32 <= 180 + п х, у, z >= 0; х, у, 2 — цілі. Розв'язування. Потрібно виконати такий алгоритм:

1) клітинкам А1, В1, СІ присвоїти імена х, у, z командами Вставити <=> Ім'я ф Присвоїти <=о Ввести х о ОК і т.д.;

2) у клітинку D1 ввести формулу =9* x+10* y+16* z;

3) запустити програму Пошук розв'язку з меню Сервіс;

4) задати адресу цільової клітинки D1 і зазначити дію до­сягнення максимуму функції (рис. 46);

5) задати клітинки, де має міститися розв'язок: х; у; z;

6) за допомогою кнопки Додати додати обмеження (рис. 47) у вигляді шести нерівностей (значення п підставити конкретне):

х <= (360 - п - 15*і/ - 12*2)/18

у <= (192 - 6*х - 8*2)/4

2 <= (180 + п - 5*х - 3*у)/3

х >= 0; у >= 0; z >= 0

х — ціле; у —- ціле; z — ціле;

 

176

2 Практикум                                                                                -| jj

2. Аналіз кредиту таблицями підстановки, аа допомогою таблиць підстановки можна оцінити вплив одного чи декількох параметрів на деяку величину чи декілька величин з метою прийняття рішень.

На прикладі розв'язування задачі 10 розглянемо вплив змі­ни одного параметра (кількості періодів позики) на дві величини: щомісячну і сумарну виплати за кредит.

Основною формулою розв'язування задачі є =ППЛАТ(ПС; КП; сума кредиту), яку розглядали в попередній роботі. Ви­конайте такий алгоритм (рис. 48).

1. У діапазон А1:АЗ введіть вхідні дані: ставку (6%), кіль­кість періодів (4) і суму кредиту, наприклад 3000.

2. У діапазон А5:А8 введіть можливі терміни позики: 4, 5, 6, 7.

 

3. У клітинку В5 введіть формулу =ППЛАТ(А1; А2; A3). У клітинку С5 введіть формулу =В5*А2. Ці формули мають бути першими у своїх стовпцях.

4. Виокремте діапазон А5:С8 і застосуйте команду Дані Ф Таблиця підстановки (Table...). Параметром у цій задачі є кіль­кість періодів з клітинки А2. Тому в отриманому діалоговому вікні у друге поле Підставляти значення ПО рядках введіть А2. Натис­ніть на кнопку ОК. Отримаєте таблицю, придатну для прийняття рішень. Який термін позики вам найбільше підходить?

Для аналізу щомісячних виплат, залежних від двох пара­метрів (можливих сум і термінів позики), таблицю будують так: у клітинку D1 вводять формулу =ППЛАТ(А1; А2; A3). Клітинки праворуч Е1:Н1 заповнюють деякими можливими сумами пози­ки: 2000, 2500, 3000, 3500, а клітинки знизу (D2:D5) - можли­вими термінами 4, 5, 6, 7 місяців. Вибирають прямокутний діапа­зон D1:H5 і виконують команду Дані о Таблиця підстановки. В отриманому діалоговому вікні у перше поле вводять A3, а в друге — А2. Отримаємо таблицю, аналіз якої дає змогу вибрати суму і термін позики, враховуючи щомісячну платоспроможність підприємця.

Хід роботи

Розв'яжіть задачу 9.

Переконайтесь, що засіб Solver доступний. Виконайте вказівки, описані в теоретичних відомостях. Який максимальний прибу­ток буде отримано?

Розв'яжіть задачу 10.

Виконайте вказівки, описані в теоретичних відомостях, а також додатково обчисліть, скільки всього грошей має повернути підприємець банку у випадку позики 3500 грн на термін 6 або 7 місяців, а також у випадку позики 4000 грн на 4 місяці.

 

178

179

 

3. Розв'яжіть задачу 10 методом табулювання функції двох змінних ППЛАТ($А$1; термін; сума позики).

Для цього очистіть діапазон Е2:Н5, у клітинку Е2 введіть фор­мулу =ІШЛАТ($А$1; $D2; Е$1) і скопіюйте її у діапазон Е2:Н5. Порівняйте отримані результати з п.2.

Практична робота № 25

Тема

MS Excel. Інструменти Сценарії і Зведена таблиця.

Мета

Вміти будувати сценарії і зведені таблиці для аналізу під­приємницької діяльності й прийняття рішень.

План

1. Аналіз сценаріїв оптової покупки.

2. Побудова зведених таблиць.