Posobie_1_chast2_Excel

19. Перейдіть на лист Вправа2.

20.Використовуючи можливість підбору параметра, знайдіть кількість казначейських зобов'язань, при якому їхня загальна вартість дорівнює 9 900. Для цього:

f у діалоговому вікні Подбор параметра у полі Установить в ячейке

уведіть посилання на комірку Е6;

fу полі введення Значение уведіть значення 9900;

fу полі введення Изменяя значение ячейки уведіть посилання на комірку С6.

21.Збережіть зроблені зміни.

100

Надбудова Пошук рішення

Для рішення задач аналізу даних крім підбора параметра можна також використовувати процедуру пошуку рішення. Можливості цієї процедури набагато ширше, оскільки пошук результату можна здійснити, змінюючи значення відразу декількох комірок. Пошук рішення дозволяє також задавати певні обмеження на змінювані значення. Таким чином, для процедури пошуку рішення треба визначити:

•цільову комірку, що містить формулу;

•змінювані комірки, на які ця формула посилається;

•обмеження.

Щоб використовувати можливості пошуку рішення, необхідно спочатку установити надбудову Пошуку рішення (якщо вона ще не встановлена). Для цього треба:

•вибрати команду Сервис/Надстройки;

•у діалоговому вікні Настройки ввімкнути прапорець Поиск решения;

•натиснути ОК.

Пошук рішення

Для запуску процедури пошуку рішення треба:

•вибрати команду Сервис/Поиск решения – відкриється діалогове вікно Поиск решения;

•у поле Установить целевую ячейку задати посилання на комірку, яка обов'язково повинна містити формулу, значення якої потрібно змінити в процесі пошуку рішення, натиснувши цю комірку мишею;

•встановити перемикач Равной положення: максимальному значенню, минимальному значению або ввести конкретне значення;

101

•ввести в поле Изменяя ячейки посилання на ті залежні комірки або діапазони комірок, значення яких варто змінювати в процесі пошуку рішення, натиснувши ці комірки або виділивши їх діапазон;

•для введення обмежень на значення натиснути кнопку Добавить;

•у діалоговому вікні Добавление ограничения:

9 ввести потрібні посилання в поле Ссылка на ячейку,

9 зі списку в полі Ограничение выбрать нужное,

9 ввести обмеження значення комірок, які впливають, у вільне поле і натиснути ОК;

•у діалоговому вікні Поиск решениянатиснути кнопку Выполнить.

Увікні повідомлень, що з'явилося, Результаты поиска решения треба

натиснути ОК, якщо приведений текст: «Решение найдено. Все ограничения и условия оптимальности выполнены».

Якщо ж у цьому вікні з'явилося інше повідомлення, наприклад, , «Значения целевой ячейки не сходятся» або «Поиск не может найти подходящего решения», варто встановити перемикач на Восстановить исходные значения), після чого натиснути ОК.

22. Перейдіть на лист Пошук рішення1.

23. Використовуючи Пошук рішення, знайдіть, яка кількість акцій і облігацій

102

можна купити, якщо:

fзагальна вартість покупки повинна дорівнювати 16335,

fкількість товарів – ціле невід’ємне число.

103

Звіт про знайдені рішення

За результатами пошуку рішення можна створювати звіти декількох видів. У звіті Результаты відображаються цільова комірка і ті комірки, що впливають на результат, значення яких змінювалися в процесі пошуку. Крім того, вказуються початкові і кінцеві значення цих комірок, формули і накладені обмеження. Створений звіт з'являється на новому листі книги.

Для створення звіту потрібно у вікні Результаты поиска решения вибрати потрібний звіт зі списку Тип отче та, натиснути ОК.

24.Створіть звіт Результаты за знайденим рішенням.

25.Перейдіть на лист Звіт за результатами і проаналізуйте приведені в звіті дані.

26.Перейдіть на лист Пошук рішення2.

27.Використовуючи пошук рішення, знайдіть кількість товарів, середню знижку і витрати на рекламу в 4-м кварталі, при яких у 4-м кварталі можна отримати максимальний прибуток:

f як цільову комірку вкажіть комірку Е16 і встановіть перемикач у полі

Равной на пункт максимальному значению,

104

fзмінювані комірки - вкажіть Е6, Е8, Е12;

fціна на товар (Е7) повинна бути менше або дорівнювати 650 грн.;

fкількість товарів (Е6) повинне бути цілим і невідємним (>=0) числом;

fсередня знижка (Е8) повинна бути більше або дорівнювати 5%;

fрічна витрата на рекламу (F12) не повинний перевищувати 5200 грн.

28.Створіть звіт Результаты за знайденим рішенням.

29.Примітка: У всі листки файлів вставте верхній колонтитул:

РС№		Прізвище студента	Шифр групи
30. Збережіть зроблені зміни.

105

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

1.Перелічите засоби аналізу даних Excel.

2.Висловіть свої припущення, у яких випадках доречно використовувати для аналізу дані Таблиці підстановки, а в яких – створення Сценаріїв.

3.Для чого використовується Звіт по сценарію?

4.Які з вивчених засобів можна було б застосувати для рішення задачі з однєю змінної, якщо кінцеве значення уже відомо?

5.Назвіть засіб аналізу, що дозволяє вирішувати задачі з декількома змінними.

Література: [1], [4], [5], [6], [7], [8], [11], [12], [13], [16], [19], [21], [22], [23], [25], [26].

106

Практичне заняття № 10

Тема дисципліни: Microsoft Office. Табличний процесор EXCEL (рівень експерта).

Тема заняття: Табличний процесор EXCEL.

Мета заняття: закріпити теоретичні знання щодо роботи з даними електронної таблиці, набути практичні навички використання фінансових функцій, ознайомитися з методами аналізу інвестиційних проектів і проведення фінансових розрахунків по цінних паперах. Придбати практичні навички використання засобів пакета MS Excel для аналізу фінансових операцій по інвестиціях і цінних паперах

Хід виконання роботи

1.Увімкніть комп’ютер, увійдіть в систему під ім’ям USER.

2.Завантажте табличний процесор Microsoft Excel.

При виконанні практичних завдань ретельно ознайомтесь з розділами довідки про використання фінансових функцій, методи аналізу інвестиційних проектів і проведення фінансових розрахунків по цінних паперах, використання засобів пакета MS Excel для аналізу фінансових операцій по інвестиціях і цінних паперах

Фінансовий аналіз інвестицій

У пакеті EXCEL існує група функцій, яка призначена для розрахунку фінансових операцій по кредитах, позичках, позиках. Ці розрахунки засновані на концепції тимчасової вартості грошей і припускають нерівноцінність грошей, що відносяться до різних моментів часу. Ця група функцій охоплює наступні розрахунки:

•визначення нарощеної суми (майбутньої вартості);

•визначення початкового значення (поточної вартості);

•визначення терміну платежу і процентної ставки;

•розрахунок періодичних платежів, зв'язаних з погашенням позик;

Визначення майбутньої вартості

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

умайбутньому зрастуть. Ця група функцій дозволяє розрахувати:

1)майбутню або нарощену вартість серії фіксованих періодичних платежів, а також майбутню вартість поточного значення внеску або позики при

107

постійній процентній ставці – функція БС;

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

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

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

Метод нарахування	Загальне число періодів	Ставка відсотка за період
відсотків	нарахування відсотків	нарахування, %
Щорічний	n	K
Піврічний	п-2	k/2
Квартальний	п-4	k/4
місячний	п-12	k/12

Розрахунки на основі постійної процентної ставки. Функція БС

Функція БС повертає майбутню вартість інвестиції на основі періодичних постійних (рівних по величині сум) платежів і постійної процентної ставки.

Завдання для розрахунків

Задача 1.

3.Розрахуємо, яка сума буде на рахунку, якщо 27 тис. грн. покладені на 33 роки під 13,5% річних. Відсотки нараховуються кожні півроку.

=БС(В3/2;В2*2;0;-В1)

Розрахунки на основі перемінної процентної ставки. Функція БЗРАСПИС

Якщо процентна ставка змінюється з часом, то для розрахунку

108

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

Задача 2.

4.За облігацією номіналом 100 тис. грн., випущеної на 6 років, передбачений наступний порядок нарахування відсотків: у перший рік 10%, у два наступні роки 20%, у три роки, що залишилися 25%. Розрахуємо майбутню (нарощену) вартість облігації по складній процентній ставці.

=БЗРАСПИС(В1;В4:В9)

Задача 3.

5.Виходячи з плану нарахування відсотків, приведеного в задачі 2, самостійно зробіть розрахунок номіналу облігації, якщо відомо, що її майбутня вартість склала 1546.88 тис. грн.

Для рішення задачі необхідно використовувати апарат Подбор параметра

пакета EXCEL.

Визначення поточної вартості

У багатьох задачах використовується поняття поточної (сучасної) вартості майбутніх доходів і витрат. Це поняття базується на положенні про те, що на початковий момент часу отримана в майбутньому сума грошей має меншу вартість, чим її еквівалент, отриманий у початковий момент часу. Відповідно до концепції тимчасової вартості грошей, витрати і доходи, що не відносяться до одного моменту часу, можна зіставити шляхом приведення до одного терміну (тобто шляхом дисконтування). Поточна вартість виходить як результат приведення майбутніх доходів і витрат до початкового періоду часу. EXCEL містить ряд функцій, що дозволяють розрахувати:

1)поточну вартість єдиної суми внеску (позики) і фіксованих періодичних платежів - функція ПС;

2)чисту поточну вартість майбутніх періодичних витрат і надходжень змінної величини - функція ЧПС;

3)чисту поточну вартість нерегулярних витрат і надходжень змінної величини (функція ЧИСТНЗ).

Помітимо, що розрахунки з використанням функцій ЧПС і ПС є частими

109