Практична робота 6:"Табличний процесор Excel"
Практична робота, розрахована на учнів середньої школи з початковим рівнем знань, включае в зміст теоретичну частину, та практичні завдання.
ПРАКТИЧНА РОБОТА № 6.
Тема: Побудова звітних таблиць. Розрахунок ефективності капіталовкладень. Розрахунок комісійних та іпотеки.
Ціль: Вивчити основні прийоми побудови звітних таблиць. Навчитися розраховувати ефективність капіталовкладень, комісійні та іпотечну позичку.
МЕТОДИЧНІ ВКАЗІВКИ.
- Розрахунок іпотечної позички
MS Excel надає великий спектр функцій фінансового аналізу від знаходження плати по відсотках, амортизації устаткування регулярних виплат по позиці до оцінки ефективності капіталовкладень. Для розрахунку іпотеки використовується фінансова функція ППЛАТ(PMT) в MS Excel 2000 і функція ПЛТ(PMT) в MS Excel XP.
Розглянемо приклад розрахунку за допомогою функції робочого листа ППЛАТ(ПЛТ) 30-літньої іпотечної позички зі ставкою 8% річних при початковому внеску 20% суми й щомісячної (щорічної) виплаті.
Для іпотечного розрахунку в комірки введіть формули, показані на мал. 6.1 (для MS Excel XP замініть відповідну назву функції):
Мал. 6.1. Підготовка до розрахунку іпотечної позички
Мал. 6.2. Результат обчислення іпотечної позички
Функція ППЛАТ(ПЛТ) обчислює величину виплати за один період річної ренти (наприклад, регулярних платежів по позиці) при постійній процентній ставці. Синтаксис функцій такий:
ППЛАТ(ставка; кпер; нз; бз; тип) / ПЛТ (ставка; кпер; пс; бс; тип)
ставка - процентна ставка за період (процентна ставка по позичці);
кпер – загальне число періодів виплат (загальне число виплат по позичці);
нз(пс) - поточне значення, тобто загальна (основна) сума, що складуть майбутні платежі;
бз(бс) - майбутня вартість або баланс готівки (залишок коштів), якого потрібно досягти після останньої виплати. Якщо параметр бз(бс) опущений, то його значення дорівнює 0 (наприклад для позики бз(бс) дорівнює нулю);
тип - число, рівне 0 або 1, що позначає, коли повинна робитись виплата.
Якщо значення параметра тип дорівнює 0 або він опущений, то оплата провадиться наприкінці періоду, якщо його значення дорівнює 1, то на початку періоду. Якщо бз(бс) =0 і тип =0, то функція ППЛАТ (ПЛТ) повертає значення:
,
де Р – нз(пс); i - ставка й п – кпер.
- Функція ПЗ в MS Excel 2000 і ПС в MS Excel XP
Функція ПЗ(ПС) використовується для розрахунку ефективності капіталовкладень.
Функція ПЗ(ПС) повертає поточний обсяг внеску на основі постійних періодичних платежів, вона припускає, щоб грошові внески відбувалися або наприкінці, або на початку періоду. Грошові внески у функції ПЗ(ПС) повинні бути постійні протягом усього періоду інвестиції. Синтаксис функцій такий:
ПЗ (ставка; кпер; виплата; бз; тип) / ПС (ставка; кпер; плт; бс; тип)
ставка — процентна ставка за період;
кпер — загальне число періодів виплат;
виплата (плт) — величина постійних періодичних платежів, що не міняється за увесь час ренти;
бз(бс) — майбутня вартість або баланс готівки (залишок коштів), якому потрібно досягти після останньої виплати. Якщо параметр бз(бс) опущений, то його значення покладається рівним 0 (майбутня вартість позики, наприклад, дорівнює 0);
тип — число, рівне 0 або 1, що позначає, коли повинна провадитися виплата.
Якщо значення параметра тип дорівнює 0 або він опущений, то оплата провадиться наприкінці періоду, якщо ж його значення дорівнює 1, то на початку періоду.
Якщо тип = 0 і бз(бс) = 0, то значення функції ПЗ(ПС) обчислюється по наступній формулі:
,
де А — виплата, i — ставка, n — кпер.
Розглянемо наступне завдання. Вас просять позичити 10 000 грн. й обіцяють повертати по 2000 грн. протягом 6 років. Чи буде вигідна ця угода при річній ставці 7%?
У розрахунку в комірку В5 введена формула =ПЗ(В4;В2;-ВЗ)(MS Excel 2000)
=ПC(В4;В2;-ВЗ) (MS Excel XP)
Крім того, для автоматизації складання таблиці й одержання відповіді на питання, чи вигідна угода, в комірки C2 й B6 введіть наступні формули:
С2 =ЕСЛИ(В2=1;"рік";ЕСЛИ(В2<=4;"роки";"років"))
В6 =ЕСЛИ(В1<В5; "Вигідно дати гроші в борг"; ЕСЛИ (В5=В1; "Варіанти рівносильні"; "Вигідніше гроші покласти під відсотки") )
З результату (мал. 6.3) видно, що в розглянутому випадку гроші вигідніше покласти під відсотки.
Мал. 6.3. Результат роботи функції ПЗ(ПС)
6.3. Функції СРЗНАЧ, РАНГ і ЧАСТОТА
При складанні звітної відомості використовуються функції СРЗНАЧ, РАНГ і ЧАСТОТА.
Функція СРЗНАЧ(число1; число2; …) знаходить середнє значення (арифметичне) із вказаного діапазону комірок або із вказаних чисел.
Функція РАНГ(число; посилання; порядок) повертає ранг числа в списку чисел. Ранг числа - це його величина щодо інших значень у списку, тобто порядковий номер щодо інших чисел у списку (якщо список відсортувати, то ранг числа буде його позицією). Параметри функції:
число – число, для якого визначається ранг;
посилання – масив або посилання на список чисел; нечислові значення в посиланні ігноруються;
порядок – число, що визначає спосіб упорядкування; якщо значення параметра порядок дорівнює 0 або він опущений, то MS Excel визначає ранг числа так, ніби посилання було списком, відсортованим у порядку убування; якщо значення параметра порядок є будь-яким ненульовим числом, то MS Excel визначає ранг числа так, ніби посилання було списком, відсортованим у порядку зростання.
Функция ЧАСТОТА (масив_даних; двійковий_масив) (2000) / (масив_даних; масив_інтервалів) (XP) повертає розподіл частот у вигляді вертикального масиву, тобто обчислює частоту появи значень в інтервалі значень. Для даної множини значень і даної множини кишéнь (тобто інтервалів у математичному змісті) частотний розподіл підраховує, скільки вихідних значень потрапляє в кожен інтервал. Параметри функції:
масив_даних – масив або посилання на множину даних, для яких обчислюються частоти; якщо масив_даних не містить значень, то функція ЧАСТОТА повертає масив нулів;
двійковий_масив (масив_інтервалів) – масив або посилання на множину інтервалів, у які групуються значення аргументу масив_даних. Якщо двійковий_масив (масив_інтервалів) не містить значень, то функція ЧАСТОТА повертає кількість елементів в аргументі двійковий_масив (масив_інтервалів).
6.4. Приклад звітної відомості
Припустимо, що необхідно скласти звітну відомість за результатами діяльності торгової мережі магазинів за літній період з найпростішою обробкою даних. У цій звітній відомості треба визначити:
- сумарний і середній виторг кожного з магазинів за звітний період;
- сумарний виторг всіх магазинів щомісяця звітного періоду;
- місце, що займає кожний з магазинів у сумарному розмірі виручки;
- частку кожного з магазинів у сумарному розмірі виручки;
- кількість магазинів, що мають сумарний виторг до 1000 тис. грн., від 1000 тис. грн. до 1500 тис. грн., від 1500 тис. грн. до 2000 тис. грн. і понад 2000 тис. грн.
Для складання звіту необхідно виконати наступні дії:
1. Для знаходження сумарного виторгу першого магазину введіть в комірку E3 формулу =СУММ(ВЗ:D3)
2. Виберіть комірку E3, розташуйте покажчик миші на маркері заповнення й перемістіть його вниз на діапазон Е4:Е8. Так ми знайдемо сумарний виторг кожного з магазинів.
3. Для знаходження сумарного розміру виручки всіх магазинів за червень введіть в комірку B9 формулу =СУММ(ВЗ:В8)
4. Виберіть комірку B9, розташуйте покажчик миші на маркері заповнення й перемістіть його вправо на діапазон С9:Е9. Це дозволить знайти сумарний виторг мережі магазинів як за кожен місяць окремо, так і за весь період у цілому.
5. Для знаходження середнього виторгу першого магазину введіть в комірку G3 формулу =СРЗНАЧ(ВЗ:D3)
6. Виберіть комірку G3, розташуйте покажчик миші на маркері заповнення й перемістіть його вниз на діапазон С4:С8, так ми знайдемо середній виторг кожного з магазинів.
7. Для визначення частки розміру виручки першого магазину стосовно сумарного виторгу всієї мережі магазинів введіть в комірку H3 формулу =ЕЗ/$Е$9
8. Виберіть комірку H3, розташуйте покажчик миші на маркері заповнення й перемістіть його вниз на діапазон Н4:Н8, що дозволить знайти частку розміру виручки кожного з магазинів стосовно сумарного виторгу всієї мережі.
9. Виберіть діапазон Н3:Н8 і натисніть кнопку Процентный формат панелі інструментів Форматирование для установки процентного формату.
10. Для визначення місця першого магазину в сумарному виторзі всієї мережі магазинів введіть в комірку F3 формулу
=РАНГ(ЕЗ;$Е$3:$Е$8)
11. Виберіть комірку F3, розташуйте покажчик миші на маркері заповнення й простягніть його на діапазон F4:F8, щоб знайти місце кожного магазину в сумарному виторзі всієї мережі магазинів.
12. Для визначення кількості магазинів, що мають сумарний виторг до 1000 тис. грн., від 1000 тис. грн. до 1500 тис. грн., від 1500 тис. грн. до 2000 тис. грн. і понад 2000 тис. грн., спочатку заповніть комірки І3:І5 необхідними сумами, а потім виберіть діапазон J3:J6 й введіть у нього формулу масиву (завершивши введення натисканням <Ctrl>+<Shift>+<Enter>):
{=ЧАСТОТА(ЕЗ:Е8; І3:І5) }
Мал. 6.4. Складання звітної відомості
6.5. Розрахунок комісійних
Розробимо функцію, необхідну для розрахунку комісійних. Відсоток комісійних залежить від обсягу проданого товару. Той, хто продає більше, буде одержувати більш високий відсоток комісійних. Нехай нарахування комісійних будуть такі:
Обсяг продажів за тиждень, грн. Комісійні, %
Від 0 до 9 999 8
Від 10 000 до 19 999 10
Від 20 000 до 39 999 12
Більше 40 000 14
Для розрахунку комісійних для обсягів продажів, приведених на робочому листі, досить в комірку С2 ввести наступну формулу (під абзацом вказана ОДНА ДОВГА(!) формула), потім вибрати цю комірку і, потягнувши за маркер заповнення, розмістити формули в діапазоні С3:С9 для визначення комісійних за кожен із вказаних тижнів:
=ЕСЛИ(В2<=9999;В2*8%;0)+ЕСЛИ(И(10000<=В2;В2<=19999);В2*10%;0)+
ЕСЛИ(И(20000<=В2;В2<=39999);В2*12%;0)+ЕСЛИ(40000<=В2;В2*14%;0)
Мал. 6.5. Розрахунок комісійних
ПРАКТИЧНЕ ЗАВДАННЯ
6.1. Розрахувати 20-літню іпотечну позичку зі ставкою (№ по журналу) % річних при початковому внеску 15% суми й щомісячній виплаті.
6.2. Розрахувати ефективність капіталовкладень при початковій сумі 20000 грн. і щорічній виплаті по 1500 грн. протягом х років. Зробити висновок, чи буде вигідна ця угода, ґрунтуючись на річній ставці відповідно до варіанту:
Варіант 1: 5%; х=7;
Варіант 2: 6,5%; х=8;
Варіант 3: 15%; х=9;
Варіант 4: 2%; х=10;
Варіант 5: 12%; х=6;
Варіант 6: 5,5%; х=7;
Варіант 7: 10%; х=8;
Варіант 8: 4%; х=9;
Варіант 9: 11,5%; х=10;
Варіант 10: 9%; х=5.
6.3. Скласти звітну відомість реалізації товарів магазинами з місяця А по місяць B у відповідності до варіанту (y – кількість магазинів), вихідні дані брати з прикладу створення звітної відомості:
Варіант 1: А= травень; В= грудень; y= 5;
Варіант 2: А= червень; В= січень; y= 4;
Варіант 3: А= липень; В= жовтень; y= 5;
Варіант 4: А= серпень; В= січень; y= 6;
Варіант 5: А= вересень; В= грудень; y= 7;
Варіант 6: А= жовтень; В= березень; y= 8;
Варіант 7: А= листопад; В= березень; y= 9;
Варіант 8: А= грудень; В= липень; y= 10;
Варіант 9: А= січень; В= липень; y= 6;
Варіант 10: А= лютий; В= серпень; y= 5.
6.4. Скласти таблицю нарахування премії за підсумками роботи мережі n магазинів з місяця A по місяць B за наступним правилом:
- якщо продукції продано не менше ніж на C грн., то комісійні становлять i%;
- за перше місце додатково нараховується j1%, за друге j2% і т.д., за k-е місце нараховується jk%.
|
Варіант |
C |
i |
j1 |
j2 |
j3 |
j4 |
n |
A |
B |
|
1 |
23000 |
1 |
3 |
1,5 |
|
|
7 |
Травень |
Липень |
|
2 |
44000 |
2 |
4 |
2 |
|
|
6 |
Червень |
Август |
|
3 |
65000 |
3 |
5 |
2,5 |
1,25 |
|
5 |
Липень |
Листопад |
|
4 |
86000 |
4 |
6 |
3 |
1,5 |
0,75 |
6 |
Август |
Лютий |
|
5 |
107000 |
5 |
7 |
3,5 |
1,75 |
0,88 |
7 |
Травень |
Грудень |
|
6 |
128000 |
1 |
3 |
1,5 |
0,75 |
0,38 |
8 |
Жовтень |
Січень |
|
7 |
149000 |
2 |
4 |
2 |
1 |
0,50 |
10 |
Листопад |
Травень |
|
8 |
170000 |
3 |
5 |
2,5 |
1,25 |
0,63 |
9 |
Грудень |
Травень |
|
9 |
191000 |
4 |
6 |
3 |
1,5 |
|
5 |
Січень |
Травень |
|
10 |
212000 |
5 |
7 |
3,5 |
|
|
6 |
Лютий |
Червень |
КОНТРОЛЬНІ ПИТАННЯ.
- За допомогою яких функцій можна провести розрахунок іпотечної позики?
- Які функції використовуються для визначення ефективності капіталовкладень?
- Що визначає параметр “тип” в фінансових функціях MS Excel?
- Чи відноситься параметр “тип” до обов’язкових параметрів?
- Для чого викоритовуються функції СРЗНАЧ, РАНГ та ЧАСТОТА?
- Як розрахувати комісійні в залежності від обсягу продажів?
- Які аналоги у функцій MS Excel 2000 ППЛАТ та ПЗ в MS Excel XP?
про публікацію авторської розробки
Додати розробку