Системи обробки економічної інформації 03.11.2013 03:26

 

МЕТОДИЧНІ ВКАЗІВКИ ДО ВИКОНАННЯ

КОНТРОЛЬНИХ ЗАВДАНЬ

 

У процесі виконання контрольних завдань з курсу "Системи обробки економічної інформації" студенти повинні навчитися:

–      працювати з сучасними програмними засобами щодо обробки економічної інформації за допомогою сучасних персональних комп`ютерів;

–      використовувати найпоширеніші спеціалізовані системи обробки та подання економічної інформації;

–      отримати навички практичного використання програм пакета MICROSOFT OFFICE в банківській сфері;

–      підготувати студентів до роботи на автоматизованих робочих місцях (АРМ) фахівців фінансово-кредитної системи;

–      допомогти студентам засвоїти теоретичні основи організації та функціонування інформаційних систем у банківській діяльності та банківських структурах в умовах застосування сучасних інформаційних технологій;

–      оволодіти методологією автоматизованого розв'язання комплексів банківських та фінансових задач з урахуванням сучасної практики банківської діяльності, обліку й звітності в Україні, а також міжнародних стандартів та нормативів;

–      здійснювати фінансово-економічні розрахунки з використанням пакету фінансового аналізу табличного процесора MS EXCEL при вирішенні задач аналізу інвестицій, обчислення швидкості обертання коштів, обчислення амортизації, аналізу цінних паперів;

–      здійснювати роботу по управлінню базами даних MS Access для обробки економічної інформації в банківській сфері;

–      використовувати MS PowerPoint для подання інформації в банківській сфері.

Вивчення дисципліни закладає основу для засвоєння можливостей використання комп'ютерної техніки в банківській сфері в ринкових умовах України.

Навчання проводиться у формі оглядових лекційних (6 години), практичних занять (6 годин) та самостійної роботи студентів (72 години) із застосуванням персональних ЕОМ, що забезпечує закріплення теоретичних знань, а також сприяє отриманню практичних навичок і розвитку творчого мислення.

Технічні засоби, що треба використовувати.

Всі контрольні завдання треба виконувати за допомогою IBM-сумісного персонального комп'ютера і принтера.

Необхідно використовувати таке програмне забезпечення:

–      ППП Microsoft Office та його додатки;

–      систему табличної обробки даних Microsoft Excel та пакет економічного аналізу;

–      систему управління базами даних Microsoft Access;

–      систему розробки презентацій Microsoft PowerPoint.

Завдання для самостійних робіт вибираються з даних методичних вказівок згідно з останньою цифрою залікової книжки студента.

Форма звітності.

Звіти про виконанні контрольної роботи повинні містити:

-        текст і дані постановки задачі;

-        роздруковані результати виконання контрольних завдань.

Звіти повинні бути оформлені у вигляді документів Microsoft Office формату А4 та роздруковані за допомогою принтера.

Кожний аркуш звіту повинен мати колонтитул, в якому треба вказати:

-        прізвище й ініціали виконавця;

-        номер навчальної групи;

-        номер варіанта, що виконувався;

-        номер контрольної роботи;

-        номер сторінки звіту.

 

ЗАВДАННЯ ДО КОНТРОЛЬНОЇ РОБОТИ

 

Розділ 1.Тема 1. Застосування електронних таблиць для обробки економічної інформації. Технологія роботи з електронними таблицями

 

Завдання №1

Виконання економічного розрахунку за допомогою електронної таблиці

 

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

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

• виплата тільки відсотків з поверненням кредиту наприкінці терміну;
• погашення кредиту рівними частками разом з відсотками.

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

Перший варіант: щомісячна виплата відсотків

1. Створіть нову робочу книгу і створіть текстові заголовки відповідно до таблиці 1.
2. Введіть у комірку C1 значення 1000 і встановіть для неї грошовий формат виду $# ##0,00.
3. Введіть у комірку C2 значення 0,1, а в комірку C3 – 0,2 і встановіть для них процентний формат.
4. Введіть у комірку C5 значення 2 (місяць, з якого повинне початися погашення кредиту).
5. Введіть у комірку C6 формулу =24-C5+1 (число виплат).
6. Введіть у комірку D7 формулу =АДРЕС(9+$C$5;2) (Обчислюється посилання на комірку, у якій знаходиться сума грошей, з якої ми почнемо сплачувати відсотки залежності від місяця початку виплати в комірці C5).
7. Введіть у комірку C7 формулу =ДВССЫЛ(D7) (Вибирається значення

з комірки, посилання на яку обчислене в комірці D7.

1. Введіть у комірку A10 значення 0, а в комірку B10 формулу =C1 (Ці значення потрібні, щоб правильно працювало обчислення посилання в комірці D7 при початку виплат з першого місяця).

 

Таблиця 1

	A		B	C	D
1	Сума кредиту
2		Відсотки по кредиту в місяць
3		Прибуток за місяць
4
5		Початок виплат
6		Число виплат
7	Сума
8
9	Місяці		Сума в кінці місяця	Щомісячні виплати	Сума в обороті
10

 

1. Введіть у комірку B11 формулу =C1+C1*C2+C11 (Ця формула означає, що наприкінці місяця ми повинні суму, рівну сумі, отриманій за попередній місяць, плюс відсотки, які набігли за місяць з цієї суми, плюс виплачені відсотки, тому що усі виплати ми будемо відбивати зі знаком мінус у стовпці «Щомісячні виплати»).

10. Введіть у комірку A11 значення 1, а в комірку A12 –  2 і сформуйте числову послідовність місяців до 24.

11.Введіть у комірку C11 формулу =ЕСЛИ($C$5>A11;0;-$C$7*$C$2) (Обчислюються щомісячні виплати для другого місяця. Якщо поточний місяць A12 менше, ніж місяць початку виплати відсотків, то значення формули дорівнює нулю, у противному випадку обчислюються відсотки «-$C$7*$C$2»).

12.Введіть у комірку D11 формулу =$C$1+$C$1*$C$3+C11

13.Введіть у комірку B12 формулу =B11+B11*$C$2+C12

14.Введіть у комірку C12 формулу =ЕСЛИ($C$5>A12;0;-$C$7*$C$2)

15.Введіть у комірку D12 формулу =D11+D11*$C$3+C12

16.Скопіюйте формат з комірки C1 на діапазон комірок B10:D13.

17.Для діапазону комірок A10:D13 установіть розташування по центру.

18.Виділіть діапазон комірок B12:D12 і скопіюйте усі формули до 34 рядка включно. (Комірки автоматично заповняться значеннями для 24 місяців).

19.Введіть у комірку C36 слово «Прибуток», а в комірку D36 формулу = D34-B34. Повинно вийти при цих даних $42 510,79 – це прибуток, якщо виплата по відсотках починається з другого місяця.

20.Змініть значення в комірці C5 на 5. Повинно вийти $50 699,76 – це прибуток, якщо виплати по відсотках починаються з п'ятого місяця.

21.Виконайте експерименти з різними значеннями кредиту, відсотків і початків виплат.

22.Використовуючи механізм підбору параметра, визначте який необхідно взяти кредит, щоб отриманий прибуток склав $100000.

 

Другий варіант: щомісячне погашення кредиту

Припустимо, що кредитор наполягає на щомісячному погашенні кредиту і відсотків рівними частками, але знову можна торгуватися, з якого місяця почати виплати. Для цього необхідно відредагувати попередню таблицю. У стовпці «Щомісячні виплати» необхідно скористатися функцією:

ПЛТ (СТАВКА_ВІДСОТКА, ЧИСЛО_ВИПЛАТ, СУМА_КРЕДИТУ)

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

Скорегуйте формулу в комірці C11, увівши:

=ЕСЛИ($C$5>A11;0;ПЛТ($C$2;$C$6;$C$7))

Скопіюйте формулу з комірки C11 на всі комірки даного стовпця до 24 місяця.

Виконайте експерименти з різними значеннями кредиту, відсотків і строків початку виплат.

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

 

2 Варіанти індивідуальних завдань

 

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

 

 

Література: [9], с. 215-221; [8], c. 136 – 142.

 

Завдання №2

Фінансово-економічні розрахунки з використанням функції ПЛТ

 

1 Методичні вказівки щодо використання функції ПЛТ

 

Функція ПЛТ(PMT) обчислює величину виплат за один період річної ренти (наприклад, регулярних платежів за позикою) при постійній процентній ставці. Синтаксис:

ППЛАТ(ставка; кпер; нз; бз; тип)

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

 

 

2 Постановка задачі і методичні вказівки до її виконання

 

За допомогою функції робочого листа ПЛТ розрахувати  30-літню іпотечну позику зі ставкою 8%  річних при початковому внеску 20% і щомісячній (щорічній) виплаті.

1. Відкрийте нову робочу книгу і створіть текстові заголовки відповідно до таблиці 2.

Таблиця 2

	A	B	C	D	E
1	Розрахунок іпотечної позики
2
3	Початкові дані
4	Ціна	201 900,0 грн.
5	Перший внесок	20%
6	Річна процентна ставка	8%
7	Розмір позики	0
8		Щомісячні виплати		Щорічні виплати
9	Термін погашення позики		місяців	30	років
10	Результати розрахунку
11	Періодичні виплати
12	Загальна сума виплат
13	Загальна сума комісійних

 

1. Установіть курсор у комірку B7 і для розрахунку розміру позички введіть формулу: =B4*(1-B5)
2. Установіть курсор у комірку B9 і для підрахунку числа місяців введіть формулу: =D9*12
3. Установіть курсор у комірку B11 і для підрахунку щомісячних періодичних виплат введіть формулу: =ПЛТ(B6/12;B9;-B7)
4. Установіть курсор у комірку D11 і для підрахунку щорічних періодичних виплат введіть формулу: =ПЛТ(B6;D9;-B7)
5. Установіть курсор у комірку B12 і для розрахунку загальної суми виплат по щомісячних виплатах введіть формулу: =B9*B11
6. Установіть курсор у комірку D12 і для розрахунку загальної суми виплат по щорічних виплатах введіть формулу: =D9*D11
7. Установіть курсор у комірку B13 і для розрахунку загальної суми комісійних по щомісячних платежах введіть формулу: =B12-B7
8. Установіть курсор у комірку D13 і для розрахунку загальної суми комісійних по щорічних платежах введіть формулу: =D12-B7

По завершенні буде отриманий результат розрахунку іпотечної суми. Відформатуйте результати в комірках B4, B7 та у діапазоні B11:D13 грошовим форматом з одним знаком після коми й установіть позначення грошової одиниці - грн.

3 Варіанти індивідуальних завдань

 

Обчислити основні платежі, плату по відсотках, загальну щорічну виплату і залишок боргу на прикладі позички P  грн. під річну ставку i%  на термін n років (номер варіанта відповідає останній цифрі в номері залікової книжки).

 

 

 

Завдання №3

Фінансово-економічні розрахунки з використанням функції ПЛТ

 

1 Постановка задачі і методичні вказівки до її виконання

 

Припустимо, що ми беремо позику у 100000 грн. при річній ставці 2% на термін 5 років. Зробити розрахунок щорічних і основних виплат по відсотках і щорічного залишку боргу.

1. Створіть нову робочу книгу і створіть текстові заголовки відповідно до таблиці 3.
2. До комірки C2 уведіть формулу =ЕСЛИ(B2=1;”рік”; ЕСЛИ(B2<=4; ”роки”; ”років”)).
3. Установіть курсор у комірку B2 і надайте їй ім'я «Відсоток», виконавши команду: Вставка->Имя->Присвоить.
   1. Аналогічним образом надайте імена коміркам B2, B3 і B4 відповідно «Термін», «Щорічна_плата», «Розмір_позики».
   2. Установіть курсор у комірку B3 і введіть для підрахунку щорічної плати формулу:

=ПЛТ(Відсоток; Термін; -Розмір_позики).

Таблиця 3

	A	B	C	D
1	Відсоток	2%
2	Термін	5	Років
3	Щорічна плата
4	Розмір позики	100 000,0 грн.
5	Рік	Плата по відсотках	Основна плата	Залишокборгу
6	0			.

 

 

1. Установіть курсор у комірку D6 і введіть для визначення початкового залишку формулу: =Розмір_позики.
2. Установіть курсор у комірку A7 і введіть для підрахунку номера року формулу: =A6+1.
3. Установіть курсор у комірку B7 і введіть для визначення плати по відсотках за перший рік формулу: =D6*Відсоток.
4. Установіть курсор у комірку C7 і введіть для обчислення основної плати за перший рік формулу: =Щорічна_плата-B7.

10.Установіть курсор у комірку D7 і введіть для обчислення залишку за перший рік формулу: =ЕСЛИ(D6-C7<>0;D6-C7;0).

11.Для комірок B3, B4 і діапазону комірок B6:D7 установіть грошовий формат, кількість знаків після коми – 1, позначення грошової одиниці – «гривні» .

12.Виділіть діапазон комірок A7:D7 і, використовуючи маркер заповнення, переміститься вниз по стовпцях доти, поки в стовпці залишку боргу не з'явиться нуль. У результаті буде сформована таблиця щорічних і основних виплат по відсотках і щорічному залишку боргу.

13.На базі даної таблиці зробіть розрахунок щомісячних виплат.

 

2 Варіанти індивідуальних завдань

Обчислити n-річну іпотечну позику на покупку квартири за P  грн. з річною ставкою i% і початковим внеском A%. Зробити розрахунок для щомісячних і щорічних виплат, для вихідних даних, представлених у наступній таблиці (номер варіанта відповідає номеру робочого місця).

 

Варіант	n  (Строк)	P  (Розмір позики)	i% (Відсоток)	A (%)
1	7	170000	5	10
2	8	200000	6	10
3	9	220000	7	20
4	10	300000	8	20
5	11	350000	9	15
6	7	210000	10	15
7	8	250000	11	30
8	9	310000	12	30
9	10	320000	12	25
10	11	380000	14	25

 

Література: [9], c.215-221]; [8], c.249-262.

 

Завдання №4

Фінансово-економічні розрахунки з використанням функції ЧПС

 

1 Методичні вказівки до використання функції ЧПС

 

Функція ЧПС (NPV) повертає чистий поточний обсяг внеску, що обчислюється на основі ряду послідовних надходжень готівки і норми амортизації. Чистий поточний обсяг внеску – це сьогоднішній обсяг майбутніх платежів (негативні значення) і надходжень (позитивні значення). Наприклад, Вам пропонують наступну угоду: у Вас беруть у борг деяку суму грошей і пропонують через k₁ рік повернути P_k1 грошей, через k₂ повернути P_k2 грошей і т.д., через k_n років повернути P_kn грошей. Крім даної угоди, у Вас є альтернативний спосіб використання ваших грошей, наприклад, покласти їх у банк під i% річних. Тоді чистим поточним обсягом внеску є та сума грошей, що ви повинні мати в початковому році, щоб, поклавши їх у банк під i% річних, одержати пропонований Вам прибуток.

Синтаксис функції ЧПС: ЧПС(ставка; 1-е значення; 2-е значення;...)

• ставка – відсоткова ставка за період;
• 1-е значення, 2-е значення, … - від 1 до 29 аргументів, що становлять витрати і доходи, які повинні бути рівномірно розподілені за часом і здійснюватися наприкінці кожного періоду. Функція ЧПС використовує порядок аргументів 1-і значення, 2-і значення,…для визначення порядку надходження і платежів.

 

2 Постановка і методичні вказівки виконання роботи

 

Вас просять дати в борг 10000 грн. і обіцяють повернути через рік 2000 грн., через 2 роки – 4000 грн., через 3 роки – 7000 грн. При якій річній відсотковій ставці ця угода має сенс?

1.Створіть нову робочу книгу і створіть текстові заголовки відповідно до таблиці 4.

2. Установіть курсор у комірку B8 і для розрахунку чистого поточного обсягу внеску введіть формулу: =ЧПС(B7;B3:B5)

3. Установіть курсор у комірку C6 і для автоматизації складання таблиці введіть формулу: =ЕСЛИ(B6=1; ”рік”; ЕСЛИ(B6<=4; ”роки”; ”років”))

4. Установіть курсор у комірку B7 і введіть відсоток, наприклад, 0%.

1. Установіть курсор у комірку B8 і оберіть команду СервисàПодбор параметра і заповніть поля введення діалогового вікна Подбор параметра, що відкриється в такий спосіб:

a)    у поле Установить в ячейке повинне бути записане посилання на комірку B8 у виді $B$8. У цій комірці обчислюється чистий поточний прибуток за формулою: =НПЗ(B7;B3:B5).

b)    у поле Значение введіть розмір позички, рівний 10000 грн.

c)    у поле Изменяя значения ячейки задайте посилання на комірку B7, у якій обчислюється річна відсоткова ставка.

 

Таблиця 4

 

1. Натисніть кнопку ОК. Засіб Подбор параметра розрахує, при якій річній відсотковій ставці чистий поточний обсяг внеску дорівнює 10000 грн.

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

 

3 Варіанти індивідуальних завдань

 

Вас просять дати в борг P грн. і обіцяють повернути P₁  грн. через рік, P₂ грн. – через два роки і т.д. P_n грн. – через n років. При якій річній відсотковій ставці ця угода має сенс? (варіант завдання відповідає номеру робочого місця)

 

Варіант	N  (Строк)	P  (Розмір позики)	P1	P2	P3	P4	P5
1	3	17000	5000	7000	8000
2	4	20000	6000	6000	9000	7000
3	5	22000	5000	8000	8000	7000	5000
4	3	30000	5000	10000	18000
5	4	35000	5000	9000	10000	18000
6	5	21000	4000	5000	8000	10000	11000
7	3	25000	8000	9000	10000
8	4	31000	9000	10000	10000	15000
9	5	32000	8000	10000	10000	10000	11000
10	3	36000	10000	15000	21000

 

Завдання №5

Фінансово-економічні розрахунки з використанням функції ПC

 

1 Методичні вказівки щодо використання функції ПC

 

Функція ПC повертає поточний обсяг внеску на основі постійних періодичних платежів. Вона аналогічна функції ЧПС. Основне розходження між ними полягає  в тім, що ПС допускає, щоб грошові внески відбувалися або наприкінці, або на початку періоду. Крім того, на відміну від грошових внесків змінної величини у функції ЧПС, грошові внески у функції ПС повинні бути постійні протягом усього періоду інвестиції. Синтаксис функції наступний: ПС(ставка; кпер; виплата; бз; тип), де

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

 

2. Постановка і методичні вказівки до виконання роботи 5

 

Припустимо, Вас просять дати в борг 10000грн. і обіцяють повертати по 2000 грн. протягом 6 років. Чи буде вигідна ця угода при річній ставці 7%?

1. Створіть нову робочу книгу і текстові заголовки відповідно до таблиці 5.

Таблиця 5

	A	B	C	D
1	Розмір позики	10 000 грн.
2	Термін	6	Років
3	Щорічно повернені кошти	2000
4	Річна відсоткова ставка	7%
5	Чистий поточний обсяг вкладу
6	Висновок

 

1. 2.    У комірку C2 введіть формулу

 =ЕСЛИ(B2=1;”рік”; ЕСЛИ(B2<=4;”роки”;”років”))

1. Установіть курсор у комірку B5 і для розрахунку чистого поточного обсягу внеску введіть формулу =ПС(B4;B2;-B3).
2. Установіть курсор у комірку B6 і для автоматизації складання таблиці й одержання відповіді на питання, чи вигідна угода введіть формулу:

=ЕСЛИ(B1<B5;”Вигідно дати гроші в борг”; ЕСЛИ(B5=B1;”Варіанти рівносильні ”;”Вигідніше гроші покласти під відсотки”))

1. Проекспериментуйте з різними значеннями вихідних даних.

 

3 Варіанти індивідуальних завдань

 

Вас просять дати в борг P грн. і обіцяють  повертати по A  грн. протягом n років. При якій річній відсотковій ставці ця угода має сенс?

 

Варіант	N	P	A
1	7	170000	30000
2	8	200000	31000
3	9	220000	33000
4	10	300000	34000
5	11	350000	41000
6	7	210000	32000
7	8	250000	37000
8	9	310000	40000
9	10	320000	35000
10	11	360000	41000

 

Література: [9],.c. 218-224; [8],.c. 157-175].

 

Завдання №6

Фінансово-економічні розрахунки з використанням функції РАНГ

 

1 Методичні вказівки щодо використання функції РАНГ

 

Функція РАНГ повертає ранг числа в списку чисел. Ранг числа – це його величина відносно інших значень у списку. Синтаксис:

РАНГ(число; посилання; порядок)

• число – число, для якого визначається ранг;
• посилання – масив чи посилання на список чисел. Нечислові значення в посиланні ігнорується.
• порядок – число, що визначає спосіб упорядкування. Якщо значення параметра порядок дорівнює нулю 0 чи він опущений, то ранг числа визначається так, ніби посилання було списком, відсортованим у порядку убування. Якщо значення параметра порядок є будь-яким не нульовим числом, то ранг визначається так, ніби посилання було списком, відсортованим у порядку зростання.

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

 ЧАСТОТА(масив_даних; двійковий_ масив)

• масив_даних – масив чи посилання на множину даних, для яких обчислюються частоти; якщо масив_даних не містить значень, то функція ЧАСТОТА повертає масив нулів;
• двійковий_масив – масив чи посилання на множину інтервалів, у які групуються значення аргументу масив_даних.  Якщо двійковий_масив не містить значень, то функція ЧАСТОТА повертає кількість елементів в аргументі двійковий_масив.

 

2 Постановка і методичні вказівки до виконання роботи

 

Ви менеджер мережі філій комерційних банків і Вам необхідно скласти звітну відомість за результатами діяльності філій за літній період з обробкою  даних. У цій відомості треба визначити:

• сумарний і середній прибуток кожної з філій за звітний період;
• сумарний прибуток усіх філій за кожен місяць звітного періоду;
• місце (рейтинг), що займає кожна з філій у сумарному обсязі прибутку;
• частку кожної з філій у сумарному обсязі прибутку;
• кількість філій, що мають сумарний прибуток до 1000 млн. грн., від 1000 млн. грн. до 1500 млн. грн., від 1500 млн. грн. до 2000 млн. грн. і понад 2000 млн. грн.

1. Створіть нову робочу книгу і створіть текстові заголовки відповідно до таблиці 6.

Таблиця 6

	A	B	C	D	E		F	G	H	I	J
1			Прибуток філій банку в млн. грн.
2	№ філій	Червень	Липень	Серпень	Сумарний прибуток	Місце(рейтинг філій		Середній прибуток	Відсоток	Діапазон	Кількість
3	1	324	435	534						1000
4	2	435	645	354						1500
5	3	532	623	451						2000
6	4	723	634	751
7	5	536	734	876
8	6	834	811	435
9	Разом:

 

1. Використовуючи команду «Автосумма», підрахуйте сумарний прибуток першої філії і потім, використовуючи маркер заповнення, підрахуйте сумарний прибуток усіх філій.
2. Використовуючи команду «Автосумма», у рядку «Разом» підрахуйте сумарний прибуток усіх філій за червень і потім, використовуючи маркер заповнення, підрахуйте сумарний прибуток усіх філій по місяцях і в загалом.
3. Установіть курсор у комірку G3 і для підрахунку середнього прибутку по першій філії запишіть формулу =СРЗНАЧ(B3:D3) і, використовуючи маркер заповнення, скопіюйте формулу на усі філії.
4. Для визначення частки обсягу прибутку першої філії стосовно сумарного прибутку всієї мережі філій установіть курсор у комірку H3, уведіть формулу: =E3/$E$9, установіть процентний формат і скопіюйте формулу з цієї комірки на усі філії.
5. Для визначення місця (рейтингу) першої філії в сумарному виторзі всієї мережі філій уведіть у комірку F3 формулу: =РАНГ(E3;$E$3:$E$8). Скопіюйте формулу з комірки F3 на усі філії.
6. Для визначення кількості філій, що мають сумарний прибуток до 1000 млн. грн., від 1000 млн. грн. до 1500 млн. грн., від 1500 млн. грн. до 2000 млн. грн. і понад 2000 млн. грн. уведіть у комірки I3:I6 значення 1000, 1500, 2000 відповідно. Виділить діапазон комірок J3:J6 і уведіть у нього формулу масиву =ЧАСТОТА(E3:E8;I3:I5). Завершіть уведення формули натисканням комбінації клавіш <Ctrl>+<Shift>+<Enter>.

 

3 Варіанти індивідуальних завдань

 

Скласти звітну відомість отриманого прибутку n філій з місяця A по місяць B. Величину прибутку по місяцях для кожної філії задайте самостійно.

 

Варіант	A	B	n
1	Травень	Грудень	5
2	Червень	Січень	4
3	Липень	Жовтень	5
4	Серпень	Січень	6
5	Вересень	грудень	7
6	Жовтень	Березень	8
7	Листопад	Березень	9
8	Грудень	Липень	10
9	Січень	Липень	6
10	Лютий	Серпень	5

 

Завдання №7

Складання звітних відомостей

 

1 Постановка і методичні вказівки до виконання роботи

 

Ви менеджер фірми з продажу комп'ютерів. Комп'ютери фірма продає на виплат, тому необхідно вести облік прострочених платежів. Прострочення платежів розбивається на «тимчасові кишені»:

• від 1 до 14 днів;
• від 15 до 29 днів;
• від 30  до 10000 днів.

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

1. Створіть нову робочу книгу і текстові заголовки відповідно до таблиці 7.
2. У комірку E2 введіть формулу для визначення періоду прострочення =ЕСЛИ(D2=0;$F$2-C2;0)
3. Виберіть комірку E2 і, потягнувши за маркер заповнення, скопіюйте формулу з комірки E2 на діапазон комірок E3:E22.
4. У комірку G3 введіть формулу для підрахунку загальної кількості замовлень з терміном прострочення 14 днів

2.  У комірку F2 введіть формулу для визначення дати переобліку =ДАТА(2002;7;15)

=СУММ(($E$2:$E$22>=I3)*($E$2:$E$22<=J3))

Після введення формули натисніть комбінацію клавіш <Ctrl>+<Shift>+<Enter>, указуючи, що дії будуть виконуватися над масивом. Виберіть комірку G3 і протягніть маркер заповнення вниз на діапазон G4:G5 для визначення числа прострочених замовлень для інших тимчасових інтервалів.

 

Таблиця 7

	A	B	C	D	E	F	G		H
1	Назва CPU	Ціна	Дата продажу	Дата оплати	Просрочка платежу	Дата переобліку	Прострочені замовлення		Термін прострочення, днів
2	Pentium I	67					Кількість	Вартість	від	до
3	Pentium I	98							1	14
4	Pentium I	209							15	29
5	Pentium I	182							30	10000
6	Pentium I	315
7	Pentium I	403
8	Pentium I	209
9	Pentium I	209
10	Pentium I	67
11	Pentium I	67
12	Pentium I	209
13	Pentium I	209
14	Pentium I	67
15	Pentium I	315
16	Pentium I	315
17	Pentium I	315
18	Pentium I	315
19	Pentium I	315
20	Pentium I	315
21	Pentium I	315
22	Pentium I	315

1. У комірку H3 введіть формулу для підрахунку сумарної вартості замовлень з періодом прострочення до 14 днів

=СУММ(($E$2:$E$22>=I3)*($E$2:$E$22<=J3)*($B$2:$B$22)).

Після введення формули натисніть комбінацію клавіш <Ctrl>+<Shift>+<Enter>, указуючи, що дії будуть виконуватися над масивом. Виберіть комірку H3 і протягніть маркер заповнення вниз на діапазон H4:H5 для визначення сумарної вартості прострочених замовлень для інших тимчасових інтервалів.

1. У комірку F2 уведіть сьогоднішню дату і проаналізуйте результат. Проекспериментуйте з різними значеннями дат.

 

Література: [9], c. 218 – 221; [8], c.156-162.

 

Завдання №8

Зведені таблиці

 

1 Постановка задачі

Скласти аналіз структури капіталів філій банку за літні місяці. Є дані по різних джерелах формування капіталу по кожній філії банку в кожнім місяці. Узагальнити результати в графічному вигляді і за допомогою зведених таблиць.

 

 

2 Методичні вказівки і порядок виконання

 

1. На першому листі створіть таблицю 8 наступного вигляду:

Таблиця 8

	A	B	C	D	E
1		Капітали філій банка в млн. грн.
2	Джерела коштів формування капіталу	Філія 1	Філія 2	Філія 3	Філія 4
3	Сплачений зареєстрований статутний капітал
4	Загальні резерви
5	Резервні фонди
6	Прибутки минулих років
7	Капітал – разом

 

1. У комірку B7 введіть формулу для обчислення загального капіталу Філії 1 і скопіюйте її на усі філії.
2. Для надання таблиці красивого вигляду скористайтеся функцією автоформатування. Виділіть комірки з A1 по E7 і виконайте команду меню Формат - Автоформат (Format-Autoformat), на екрані з'явиться діалог Автоформат (AutoFormat). У поле Список форматів (Table Format) виберіть Об'ємний 2 (3D Effects 2) і натисніть кнопку ОК. Шрифти, фон, розміри комірок виділеного діапазону зміняться і таблиця прийме зовсім інший вигляд. Виберіть найбільш зручний масштаб зображення, щоб таблиця займала велику частину вікна.
3. Клацніть правою клавішею миші по ярличку «Лист 1». З'явиться контекстне меню, у якому виберіть команду Перемістити/скопіювати (Move or Copy). З'явиться діалог Перемістити чи скопіювати (Move or Copy).
4. Установіть прапорець Створити копію (Create a Copy) і натисніть кнопку ОК. У робочій книзі з'явиться ще один лист із назвою Лист 1(2). Усі тексти, формати, розміри комірок на ньому точно такі ж як і на листі з назвою «Лист 1».
5. Аналогічно створіть ще одну копію листа
6. Клацніть правою клавішею миші по ярличку «Лист 1(3)». З'явиться контекстне меню, у якому виберіть команду Перейменувати (Rename) і замість назви «Лист 1 (3)» уведіть «Червень». Назва листа, написана в ярличку, зміниться.
7. Аналогічно поміняйте назви двох інших аркушів на «Липень» і «Серпень» відповідно.

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

Для подальшої роботи введіть наступні конкретні дані в документ:

 

Дані за червень

Джерело формування капіталів	Об’єм коштів у млн. грн.
	Філія 1	Філія 2	Філія 3	Філія 4
Оплачений зареєстрований капітал	2300	2400	3500	1900
Спільні резерви	800	700	1200	620
Резервні фонди	400	410	600	705
Доходи попередніх років	16898	15898	17898	14898

 

Дані за липень:

Джерело формування капіталів	Об’єм коштів у млн. грн.
	Філія 1	Філія 2	Філія 3	Філія 4
Оплачений зареєстрований капітал	2380	2460	3570	1970
Спільні резерви	850	780	1230	670
Резервні фонди	420	420	620	715
Доходи попередніх років	16898	15898	17898	14898

 

Дані за серпень:

Джерело формування капіталів	Об’єм коштів у млн. грн.
	Філія 1	Філія 2	Філія 3	Філія 4
Оплачений зареєстрований капітал	2480	2560	3670	2470
Спільні резерви	950	880	1430	770
Резервні фонди	510	520	720	815
Доходи попередніх років	16898	15898	17898	14898

 

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

1. Побудова зведеної таблиці здійснюється за допомогою майстра зведених таблиць. Для запуску майстра виконайте команду меню Данные - Сводная таблица (Data-Pivot Table). Майстер почне свою роботу з першого кроку.

10.Установіть перемикач у положення В нескольких диапазонах консолидации (Multiple Consolidation Ranges), тому що дані розташовані на різних аркушах робочої книги. Натисніть кнопку Далее (Next) і Ви переходите до наступного кроку.

11.Установіть перемикач у положення Создать одно поле страницы (Create a single page for me), тому що всі аркуші ідентичні і відрізняються тільки одним параметром – місяцем підведення підсумків. Натисніть кнопку Далее (Next) і Ви перейдете до наступного кроку.

12.Натисніть мишу в полі Диапазон (Range), потім натисніть мишу на ярличку листа «Червень» і виділіть комірки з A2 по E7. Якщо вікно майстра зведених таблиць закриває потрібні комірки, візьміться мишею за заголовок вікна майстра і зруште вікно вниз. Натисніть кнопку Добавить (Add) і текст Червень!$A$2:$E$7 буде доданий у поле Список диапазонов (All Ranges). Натисніть мишу на ярличку листа «Липень» і виділіть комірки з A2 по E7, потім натисніть кнопку Добавить (Add). Натисніть мишу на ярличку листа «Серпень». Excel уже догадався, що Ви хочете зробити далі і підставив адреси потрібних комірок. Натисніть кнопку Добавить (Add). Ви додали три діапазони, що відповідають літнім місяцям. Натисніть кнопку Далее (Next) і перейдіть до наступного кроку.

13.На цьому кроці установіть прапорець Поместить таблицу на новый лист і натисніть кнопку «Готово». З'явиться побудована зведена таблиця за зазначені місяці, у якій згруповані дані по всіх джерелах і філіях за зазначені місяці. Проаналізуйте отримані дані. Проекспериментуйте з кнопками, розташованими в комірках B1, A4, B3.

14.На панелі інструментів «Сводные таблицы» відкрийте список «Сводная таблица», виберіть «Мастер...» і потім «Макет...». На макеті зведеної таблиці поміняйте місцями рядки і стовпці. Для цього підведіть курсор миші до елемента з назвою «Строка», і не відпускаючи її, рухайте елемент вліво і помістіть його на макеті на місце «Столбец». Точно так перемістіть елемент «Столбец» на макет на місце «Строка» і натисніть кнопку «Готово». У підсумку два елементи помінялися місцями і змінився алгоритм роботи зведеної таблиці. З'явиться нова зведена таблиця. Проаналізуйте отримані дані і проекспериментуйте з цією таблицею.

15.Створіть звіт, натиснувши кнопку «Формат отчётов» на панелі інструментів «Сводная таблица». Формат звіту виберіть на свій розсуд.

16.Побудуйте діаграму, натиснувши кнопку «Майстер діаграм» на панелі інструментів «Сводная таблица».

 

 

3 Варіанти індивідуальних завдань

 

Скласти зведену таблицю капіталів по всіх n філіях банку  з місяця A по місяць B. Обсяги джерел кожної філії задайте самостійно.

 

Варіант	A	B	N
1	Травень	Грудень	5
2	Червень	Січень	4
3	Липень	Жовтень	5
4	Серпень	Січень	6
5	Вересень	Грудень	7
6	Жовтень	Березень	8
7	Листопад	Березень	9
8	Грудень	Липень	10
9	Січень	Липень	6
10	Лютий	Серпень	5

 

Розділ 1. Тема 4. Застосування MS Excel для обчислення амортизації та аналізу цінних паперів

 

Завдання №9

Фінансові функції для розрахунку амортизації

 

1 Розрахувати суми амортизаційних відрахувань:

• Рівномірним методом (функція АМР);

• Методом суми років (функція АМГД);

• Методом підвідного списання (функція ДДОБ);

• Методом знищуючого залишку (функція ДЛБ);

Початкова вартість обладнання 15000 грн., залишкова вартість 1800 грн., і термін корисної служби 5 років.

 

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

 

Розрахунок суми амортизаційних відрахувань

 

 

 

 

Розділ 1. Тема 5. Застосування MS Excel для проведення статистичного аналізу

 

Завдання №10

Використання функції ВПР (Посилання і масиви)

 

1 Завдання

 

Використовуючи функцію ВПР, розрахувати заробітну плату співробітників фірми залежно від основної шкали ставки і розміру податків.

Нарахування податку протягом року відбувається, виходячи з суми сукупного оподаткованого прибутку за основною шкалою ставок, встановленого указом Президента України від 21/11/95 року № 1082/95 згідно з таблицею 1.

Таблиця 1

Місячний сукупний оподаткований прибуток	Не оподатковується
До 17 грн. (з прибутку в розмірі 1 неоподаткованого мінімуму прибутків громадян)	10% з суми прибутків, що не перевищує один неоподаткований мінімуму
18-85 грн. (от 1 до 5 неоподаткованих мінімумів прибутків громадян)	6 грн.80 коп.+15% з суми, що перевищує 85 грн.
86-170 грн. (от 5 до 10 неоподаткованих мінімумів прибутків громадян)	19 грн.55 коп.+20% з суми, що перевищує 170 грн.
171-1020 грн. (от 60 до 100 неоподаткованих мінімумів прибутків громадян)	189 грн.55 коп.+30% з суми, що перевищує 1020 грн.
1701 грн. і  (більше 100 неоподаткованих мінімумів прибутків громадян)	393 грн.55 коп.+40% з суми, що перевищує 1700 грн.

 

 

Методичні вказівки і порядок виконання

1. Таблицю 1 необхідно представити у вигляді Таблиці 2

 Таблиця 2

Максимальний прибуток	Ставка податку	Відсоток з суми, яка перевищує границю прибутку	Верхня границя прибутку
1	2	3	4
0	0	0	0
18	0	0,1	0
86	6,8	0,15	85
171	19,55	0,2	170
1021	189,55	0,3	1020
1701	393,55	0,4	1700

 

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

 =ВПР(шукане_значення; інфо_таблиця; номер_стовпця; тип_перегляду)

де:

• шукане_значення – це значення, яке використовується при пошуку в таблиці для визначення першого індексу;
• інфо_таблиця – це масив або ім’я діапазону, яке визначає таблицю з даними;
• номер_стовпця – задає в таблиці стовпець, з якого вибирається повернене значення;
• тип_перегляду – це логічне значення, яке визначає тип відповідності.

Наприклад, формула =ВПР(41;A3:C7;3), записана в комірку C1, для наступної таблиці поверне значення 14.

	А	В	С
1			14
2
3	10	17,98	5
4	20	5,89	8
5	30	6,59	11
6	40	23,78	14
7	50	6,79	17
8

 Пояснення: спочатку функція визначає стовпець, якій містить порівнянне значення, - в даному разі, стовпець А. Потім вона перевіряє стовпець А і знаходить найбільше значення, яке менше або дорівнює шуканому значенню. Оскільки із порівнянних значень четверте (40) менше, а п’яте (50) більше шуканого значення 41, в якості індексу рядка використовується рядок 6, який містить число 40. Індекс стовпця – це номер_стовпця і в нашому прикладі він дорівнює 3. Таким чином, стовпець С містить очікувані дані, і функція повертає значення з комірки С6, яке дорівнює 14.

 

2. Порядок дій

1. Ввести таблицю 3, не заповнюючи 4-й, 5-й, 6-й, 7-й та 8-й, стовпці в яких для перевірки наведені результати розрахунків:

Таблиця 3

 

2. Присвоїти таблиці 2 ім’я “Таблиця”, стовпцю 3 таблиці 3 ім’я “Прибуток”, стовпцю 3 таблиці 3 ім’я “Сума_до_видачі”, стовпцю 5 ім’я “ВПР2”, стовпцю 6 ім’я “ВПР3”, стовпцю 7 ім’я “ВПР4”, стовпцю 8 “Сукупний_податок”.

3. Виконати розрахунки таблиці 3, застосовуючи такі формули:

-      для стовпця 5 (ВПР2): =ВПР(прибуток; таблиця; 2);

-      для стовпця 6 (ВПР3): =ВПР(прибуток; таблиця; 3);

-      для стовпця 7 (ВПР4): =ВПР(прибуток; таблиця; 4);

-      для стовпця 8 (Сукупний податок): =ВПР2+ВПР3*(прибуток-ВПР4);

-      для стовпця 4 (Сума до видачі): =прибуток – Сукупний_податок.