Короткі теоретичнi вiдомостi

Основнi понятт я про дiаграми. Д iаграми призначенi для графiчного вiдображення числових даних у звiтах, на презентацiйних, рекламних чи веб-сторiнках тощо.

Дiаграми подiляються на стандартнi (найбiльш поширені) та нестандартнi (використовуються зрiдка).

Є багато типiв стандартних дiаграм: гiстограма, лінійна, графiк, кругова, точкова, з областями, кiльцева, поверхнева, пелюсткова, булькова, бiржова, цилiндрична, конічна, пірамiдальна тощо. Кожний тип стандарної дiаграми має декілька видiв. Наприклад, гiстограма, яка вiдображає данi у вигляді пропорцiйних вiдносно даних стовпцiв, може бути звичайною (стовпцi розташованi поряд), з накладеними стовпцями, нормованою (усi стовпцi даних накладені i їхня загальна висота приймається за 100%), об’ємною звичайною, об’ємною накладеною, об’ємною нормованою, об’ємною з тривимiрним ефектом.

Найчастiше будують круговi, точковi, стовпцевi стандартні дiаграми рiзних видiв.

Головнi типи дiаграм. Оскiльки рiзновидiв дiаграм кiлька десяткiв, ми розглянемо лише найважливiшi.

Кругова дiаграма вiдображає один виокремлений рядок стовпець числових даних з таблицi у виглядi круга iэ секторами. Вона демонструє спiввiдношення частин i цiлого, де цiле відповiдає 100%. Є декiлька рiзновидiв кругових діаграм. Якщо серед даних є порiвняно незначнi, то їх можна об’єднати в один сектор, для якого буде створено окрему меншу дiаграму об’єднаних даних.

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

Точкова дiаграма призначена для побудови традицiйних графiкiв з довiльними значеннями аргумента. На однiй координатній площинi можна побудувати графiки вiдразу декiлькох функцiй. Записуватимемо значения аргумента в перший стовпець, а значення функцiй – в другий, третiй тощо. Тодi перший виокремлений стовпець у таблицi програма iнтерпретуватиме як вiсь Х, iншi – як значення одної чи кiлькох функцiй уздовж вертикальної осi. Кiлькiсть рядкiв у такiй таблицi має бути бiльшою, нiж кiлькiсть стовпцiв. Стовпцiв є два для однiєї функції, три – для двох функцiй і т.д.

Гiстограма (стовпцева дiаграма) показує числовi дані з таблицi у виглядi вертикальних стовпчикiв. Її найчастiше використовують для iлюстрацiї змiн даних у часi чи просторi.

Лiнiйнi звичайнi дiаграми складаються з горизонтальних смужок. Вони подiбнi до гiстограм, але повернутi на 90 градусів, що дає змогу вiзуально оцiнювати величини на пiдставi порiвняння довжин смужок.

Бульковi дiаграми подiбнi до точкових, але маркери даних в них — це круги чи сфери, дiаметри яких пропорцiйнi до даних, якi вони вiдображають (див. приклади на географiчних картах.

Бiржовi дiаграми використовують у банкiвськiй справi для вiдображення верхньої i нижньої межi курсiв валют, акцiй тощо.

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

Структура i форматування дiаграми. Майже всі дiаграми (крiм кругової i пелюсткової) мають двi головнi осi: горизонтальну — вiсь категорiй, вертикальну — вiсь значень. Об’ємні дiаграми мають ще третю вiсь — рядiв даних.

Дiаграма складається з багатьох елементiв. Нижче наведено ті, назви яких можна прочитати на екранi, навiвши на елемент вказiвник мишi:

Ø область дiаграми;

Ø область побудови дiаграми;

Ø легенда;

Ø заголовок дiаграми;

Ø вiсь ряду даних;

Ø вiсь категорiй;

Ø вiсь значень;

Ø маркери даних;

Ø назва осi значень;

Ø назва осi категорiй;

Ø стiни, кути (в об’ємних дiаграмах) тощо.

Вигляд усiх елементiв можна змiнювати. Елементи дiаграми є об’єктами, над якими визначені дії перемiщення та форматування. Розмiри дiаграми змiнюють, перетягуючи маркери габаритiв. А вигляд змiнюють за допомогою команди Формат елемента... з його контекстного меню. Пiд час форматування можна, зокрема, замалювати елементи деяким кольором чи. текстурою. На кожну поверхню дiаграми (сектор, прямокутник, стіну) можна нанести рисунок з файлу, наприклад, зображення продукції, зокрема, автомобiлiв, парфум тощо, чи iсторичних пам’ятникiв, що є символами мiст. Можна змiнити текстовi пiдписи елементiв і шрифт, яким воин виконанi.

Створення дiаграми. Дiаграму можна створити командами Вставити Þ Об’єкт Þ Microsoft Graph Chart. Таблицю-зразок, яка з’явиться на екранi, слiд заповнити власними даними і простежити, як автоматично змiнюватиметься стовпцева діаграма-зразок. Можна також змiнювати величину стовпчика методом перетягування його вершини — данi в таблицi змiнюватимуться автоматично. Коли данi будуть введенi, можна змiнити тип дiаграми за допомогою її контекстного меню.

Часто дiаграми будують за допомогою програми, яка має назву Майстер дiаграм. Її можна запустити двома способами:

Ø натисканням на кнопку Майстер дiаграм на панелi iнструментів;

Ø командами з меню Вставити Þ Дiаграму.

Рекомендують перед запуском майстра правильно виокремлювати дiапазони з даними, якi треба графічно вiдобразити. Це зазвичай суміжнi рядки чи стовпцi (часто з назвами). Щоб виокремити несумiжнi дiапазони, потрiбно натиснути на клавішу Ctrl.

Пiд керiвництвом майстра виконують чотири кроки.

Крок 1: вибирають тип i вигляд дiаграми.

Крок 2: задають дiапазони з даними (якщо вони не були вибранi заздалегiдь).

Крон 3: задають параметри (пiдписи, легенду, вигляд осей, наявнiсть сiтки) дiаграми.

Крок 4: зазначають, куди заносити дiаграму (на окрему чи поточну сторiнку).

Щоб перейти до наступного кроку, натискають на кнопку Далi, а щоб повернутися назад — на кнопку Назад.

Можна пропустити один чи два кроки, натискаючи вiдразу на кнопку Далi. Щоб завершити (часто достроково) роботу майстра дiаграм i отримати дiаграму, натискають на кнопку Готово.

Змiни у створенiй дiаграмi можна внести за допомогою контекстного меню елементiв, команди Дiаграма або панелi iнструментiв з назвою Дiаграма.

Щоб вставити в дiаграму пропущений елемент, використовують команди Вставити Þ Дiаграма Þ Параметри діаграми.

Звернiть увагу на те, що тип дiаграми можна будь-коли помiняти. Для цього дiаграму треба вибрати, викликати майстра дiаграм, вибрати iнший тип i натиснути на кнопку Готово.

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

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

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

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

¨ сума внеску (СВ) – сума інвестицій, капіталовкладення, початкового внеску (це від’ємне число або нуль);

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

Розрізняють кредитну і депозитну процентні ставки. Кредитна ставка є вищою за депозитну. Процентна ставка повинна бути узгоджена з тривалістю періоду, наприклад, річна ставка 24% відповідає місячній ставці 2%. У цій роботі вважатимемо, що місячна депозитна ставка – 1% (1%=0,01), а кредитна – 2%.

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

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

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

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

       =БС(1%; 12;; -5000) або =БС(0,01; 12;; -5000)

       Відповідь:  5634,13 грн.

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

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

=БС(1%; 12; 200; -5000; 1)

Вiдпов iдь: 3 072,26 грн.

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

=БС(1%; 12; -200; -5000; 1)

  Вiдпов iдь: 8 195,99 грн.

Функцiї для визначення виплат для погашення позики. Введемо новi термiни i їхнi скороченi назви:

·  номер перiоду (НП);

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

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

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

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

 б) основна виплата ОСПЛТ (ПС; НП; КП; СП; КЗ; Т).

Виплата за процентами щоперiоду эменшується, а основна виплата щоперiоду зростає, їхня сума постійна і дорівнює ПЛТ.

Задача 4. Б iзнесмен взяв у банку кредит на суму 5000 грн. термiном на 12 мiсяцiв за умови щомі сячного погашення позики i мiсячної ставки кредиту 2%. Визначити величину щомiсячних виплат i її складовi наприкiнцi першого мiсяця.

=ПЛТ(2%; 12; 5000). Вiдповiдь: -472,80 грн.

= ПРПЛТ(2%; 1; 12; 5000). Вiдповiдь: -100,00 грн.

= ОСПЛТ(2%; 1; 12; 5000). Вiдповiдь: -372,80 грн.

Задача 5. Побудувати таблицю значень двох складових ПРПЛТ i ОСПЛТ щом iсячних виплат наприкiнцi кожного мiсяця за кредит (5000 грн, 2%) протягом року. Для шести мiсяцiв розв’язок показано на рис. 1.

Рис. 1.

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

  Задача 6. Позику 5000 грн беруть за умови повер не ння наприкiнцi кожного мiсяця 500 грн і процентної ставки 2%. Скiльки мiсяцiв потрiбно для повертання позики?

=КПЕР(2%; -500; 5000)

Вiдповiдь: 11,27 мiсяця.

Функцiя СТАВКА(КП; ПВ; СП; КЗ; Т; початкове наближення) визначає вигiдність надання позики, тобто реальну процентну с тав ку від надання позики на певну суму за умови фiксованих перiодичних виплат протягом деякої кiлькостi перiодiв. Тут потрiбно задати деяке початкове наближення до шуканої процентної ставки, наприклад 0,1 (10%). (Англ. RАТЕ).

Задача 7. Б iзнесмен звертрає ться до банку за п озикою (кредитом) на суму 5000 грн на 12 мiсяцiв за умови перiодичних виплатл 450 грн наприкiнцi кожного мiсяця протягом року. Визначити процентну став ку позики.

  =СТАВКА(12; -450; 5000; 0; 0; 0,1)

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

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

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

Функцiя ПС(ДПС; КП; рента за один перiод; ре нт а в кiнцi термiн у; Т) обчислює сьогоднiшню вартiсть низки майбутнiх надходжень (ренти) вiд бiзнесу (англ. PV).

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

Задача 8. Нехай для ведення деякого бізнесу потрібно вкласти сьогодні 20 000 грн, а бізнес протягом п’яти місяців даватиме по 5000 грн доходу (ренти) наприкінці місяця. Депозитна ставка банку 1%. Чи варто займатися цим бізнесом?

=ПС(1%; 5; 5000). Відповідь: -24 267,16 грн.

Висновок: вартість бізнесу (гранично допустима інвестиція) – 24 267 грн. Оскільки цю суму потрібно вкладати, отримали від’ємне число. Бізнес вигідний, бо для його ведення потрібно лише 20 000 грн. Чистий прибуток вигода становитиме не 5000 грн., а 4 267 грн. Якби потрібно було більше, ніж 24 267 грн., то такий бізнес був би збитковим.

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

=ПС(1%; 5;; 21000). Відповідь: -19 980,78 грн.

Висновок: сьогоднішня вартість ренти 19 981 грн. (отримали від’ємне число). Такий бізнес вигідним вважати не можна. Причина – немає змоги реінвестувати ренту. Краще покласти 20 000 грн. в банк і нічого не робити.

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

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

=ЧПС( 1%; -2000; 4000; 5000; 6000; 7000; 8000 )

Відповідь: вартість бізнесу 26 756,44 грн. Оскільки затрати 27 000грн. більші за вартість бізнесу, то такий бізнес невигідний.

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

Нехай щомісяця очікується такий дохід: 5 000, 6 000, 7 000, 8 000, 9 000 – всього 35 000. Оцінимо сьогоднішню вартість майбутніх рент:

= ЧПС (1%; 5000; 6000; 7000; 8000; 9000)

Відповідь: 33 877,44 грн. Вартість рент є більшою за інвестицію (30 000), тому такий бізнес є вигідний. Прибуток 5 000 грн., чистий прибуток від бізнесу складає 3877,44 грн.

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

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

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

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

End Function

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

8,01 грн. за долар:

       =Гривні(135; 8,01)

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

Для створення власної функції потрібно виконати команди СЕРВІС Þ МАКРОС Þ РЕДАКТОР Visual Basic Þ ВСТАВИТИ Þ МОДУЛЬ. Ввести текст функції у вікно модуля, що відкриється, і закрити вікно модуля і вікно редактора.

Зауваження. У різних версіях програми назви фінансових функцій можуть відрізнятися від наведених вище.

Хід роботи

1. Придумати, записати умови і розв’язати 12 задач, подібних до наведених вище. Ваші задачі можуть відрізнятися від описаних лише числами. Зокрема, врахуйте нові поточні депозитні та кредитні ставки банків. Основна грошова сума (інвестиція, вклад, позика, рента), що наявна в умові задачі, задається дописуванням трьох нулів до номера вашого варіанта (вашого порядкового номера в журналі). Зразок виконання і оформлення роботи див на рис. 1. Збережіть документ у власній папці. Закрийте Excel.

 

 

Хiд роботи

Запустiть програму ЕТ.

3. Вiдкрийте нову книжку i книжку, яка мiстить розв’язок «Товарний чек».
Скопiюйте таблицю «Товарний чек» на перший аркуш книжки. Якщо файл втрачено, побудуйте таблицю заново.

4. Виокремте дiапазон з назвами предметiв i діапазон з сумами, якi треба заплатити.
Пiд час виокремлення несуміжних дiапазонiв користуйтеся Ctrl.

5. Запустiть Майстра дiаграм і виконайте перший крок: задайте тип i вигляд дiаграми.
Тип: Стандартнi => Кругова => Вигляд: Об’смна => далi.

6.   Наступний (другий) крок можна пропустити, тому що джерело даних задане виокремленням.
Перевiрте, чи у текстовому вiкнi правильно описано дiапазони даних: =Аркуш1!$В$2:$В$9, Аркуш1!$Е$2:$Е$9 Натиснiть на кнопку Далi i виконайте наступний крок.

7. Задайте параметри дiаграми.
Дайте дiаграмi назву: “Купiвля канцтоварiв” тощо. Переконайтеся, що легенда буде праворуч. Пiдпис даних виберiть такий:
частка. Поекспериментуйте з пiдлисами: категорiя, категорiя і частка, значення. Вимккiть ключi легенди i лiнiї виноски. Натиснiть на кнопку Далi.