Порядок виконання роботи

РЕКОМЕНДАЦІЇ ДО ПРАКТИЧНИХ РОБІТ

Лабораторна робота № 1.2

Тема: Використання статистичних функцій MS Excel для розв’язування задач прогнозування.

Мета: Отримати навички використання засобів прогнозування та аналізу даних в MS Excel.

Порядок виконання роботи

1. Створити на робочому листі табличного процесора MS Excel таблицю 20 Надати робочому листу ім’я Вхідні дані.

Таблиця 20

Дата отримання	Фірма постачальник	Код товару	Закупівельна ціна одиниці товару, грн.	Кількість одиниць, шт.	Залишок товару, шт.	Ціна одиниці товару, грн.
16.12.10	Аванта	48230	2,2	4154	75	2,86
17.12.10	Гера	50001	4,72	2454	912	6,14
18.01.11	Орфей	48200	5,2	241324	84521	6,76
19.02.11	Аванта	48230	2,36	54800	30	3,07
19.02.11	Аванта	46007	17,54	2510	311	19,80
20.02.11	Аванта	46007	17,95	3000	415	20,34
21.02.11	Аванта	46007	10,45	1760	144	13,59
21.03.11	Аванта	32824	21,47	1250	8	27,91
22.03.11	Аванта	50001	12,14	4520	368	15,78
22.03.11	Гера	40154	6,47	1400	576	8,41
22.03.11	Орфей	33464	198,47	150	12	258,01

 

2. Визначити товар з найдорожчою ціною (використовуючи функцію ДМАКС).

3. Визначити найдешевшу закупівельні ціни серед придбаних партій товару в березні місяці 2011 року (функція ДМИН). Для цього треба створити допоміжну таблицю, яка б включала умову.

4. Визначити середню закупівельну ціну товарів, які постачались фірмами “Аванта” та “Орфей” (функція ДСРЗНАЧ).

5. Визначити загальну суму залишку товару по кожній фірмі використовуючи функцію СУММЕСЛИ, (у вікні функціі в поле діапазон вказати діапазон розміщення таблиці без поля «Дата отримання»; в полі крітерій ввести адресу комірки, яка містить назву фірми; в полі діапазон суммування ввести діапазон комірок, що містить дані про залишок товарів разом із заголовком стовбчика).

6. Визначити загальний залишок товарів, які постачались фірмою “Гера” (функція БДСУММ).

7. Створити допоміжну таблицю на листі 2, де потрібно спрогнозувати закупівельну ціну товару для кожної фірми на 2012 рік (функція ТЕНДЕНЦИЯ), відповідно до запланованої кількості товару.

8. Розрахувати ПДВ для кожного товару.

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

- у категорії Стовпчик розмістити Дата отримання;

- у категорії Рядок розмістити Код товару;

- у полі даних розмістити суму по полю Сплата ПДВ, Кількість.

10.  На листі 3 створити копію таблиці з листа Вхідні дані, де спрогнозувати значення залишку товару за допомогою функції ПРЕДСКАЗ.

11.  На листі 3 спрогнозувати значення кількості товару за допомогою функції РОСТ.

12. Дати відповіді на питання:

- Для чого і яким чином можна застосовувати статистичні функції в аналізі діяльності фірми?

- Чим відрізняються функції ТЕНДЕНЦИЯ і ПРЕДСКАЗ?

- Поясніть особливості роботи з функціями прогнозування.

- Поясніть особливості роботи з функціями по обробці списків даних.

- Які відмінності у роботі функцій ДМИН, ДМАКС, ДСРЗНАЧ, БДСУММ від МИН, МАКС, СРЗНАЧ, СУММ відповідно?

13.  Виконати прогнозування значення Y для кількох нових значень Х:

ПРЕДСКАЗ			ТЕНДЕНЦІЯ			РОСТ			НАКЛОН
Х	Y		Х	Y		Х	Y		Х	Y
2	3		2	3		2	3		2	3
6	8		6	8		6	8		6	8
4	8		4	8		4	8		4	8
3	3		3	3		3	3		3	3
7	8		7	8		7	8		7	8
3	8		3	8		3	8		3	8
5			5			5			5
			8			8			8
			9			9			9

Лабораторна робота № 3.1.

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

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

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

Порядок виконання роботи

1.  Розв’язати задачу: При якій річній ставці банку вигідно надати кредит підприємству в сумі 5 000 000 грн., якщо керівництво підприємства зобов’язується повернути борг через рік – 2 000 000.грн., через два роки – 1 900 000 грн., через три роки - 1600 000 грн.?

2.  Розробити форми подання вхідної та вихідної інформації у вигляді таблиці (рис. 1).

 Рис. 1

3.  В комірку В6 введемо довільне значення річної облікової ставки, наприклад 8%.

4.  За допомогою функції ЧПС розрахувати чистий поточний об’єм вкладу в комірці В7:= ЧПС (B6;B2:B4).

5.  Для автоматизації заповнення таблиці у комірці, що містить назви строків надання кредиту, ввести слова "рік", "роки", "років" в залежності від кількості років за допомогою функції ЕСЛИ.

= ЕСЛИ (B5=1;"рік";ЕСЛИ(И(B5>=2;B5<=4);"роки";"років"))

6.  Визначте чи вигідно буде при даній річній ставці банку надати кредит підприємству. Для цього уведіть у комірку В8 наступну умову:

= ЕСЛИ (B1<B7;”Вигідно банку надати кредит підприємству”; ЕСЛИ (В7=В1;”Варіанти рівносилі”;”Банку невигідно надати кредит підприємству”)).

7.  Застосувати засіб Підбір параметра (пункт меню Данные)   (Рис. 2) для визначення оптимальної річної відсоткової ставки і проаналізувати, яка ефективність надання кредиту.

 Рис. 2

В результаті дії команди   Підбір параметра було розраховано річну відсоткову ставку для суми займу 5000000 грн. на термін 3 роки при змінних величинах щорічних виплат, яка становить 5%. Це значення автоматично з’явиться в комірці В6.

8.  Розв’язати задачу: Підприємство бере кредит 100 000 грн. Необхідно розрахувати розміри основних платежів, сплати за відсотками загальної щорічної сплати та залишку боргу (таблиця 1.) на прикладі позики 100 000 грн. строком на 5 років при річній ставці 3%, застосувавши функції ПРПЛТ та ОСПЛТ.

Таблиця 1.

Роки	Розмір основних платежів, грн.	Розмір сплати за відсотками, грн.	Загальна сума платежів, грн	Залишок боргу, грн. (загальний борг складається з суми позики та сплати за відсоткам)
1	=ОСПЛТ (3%;1;5;-100000) отримуємо результат 18835,50	=ПРПЛТ (3%;1;5;-100000) отримуємо результат 3000,00	=ОСПЛТ(3%;1;5; -100000)+ ПРПЛТ (3%;1;5; -100000), отримуємо результат 21835,46	=100000+ ∑ відсотків за 5 років – загальна сума платежів за поточний рік. отримуємо результат 87341,83
2	=ОСПЛТ (3%;2;5;-100000) отримуємо результат 19400,52	=ПРПЛТ (3%;2;5;-100000) отримуємо результат  2434,94	=ОСПЛТ(3%;2;5; -100000)+ ПРПЛТ(3%;2;5; -100000) отримуємо результат 21835,46	= Залишок боргу за минулий рік - загальна сума платежів за поточний рік отримуємо результат 65506,37
3	= ОСПЛТ (3%;3;5;-100000) отримуємо результат 19982,54	= ПРПЛТ (3%;3;5;-100000) отримуємо результат 1852,92	= ОСПЛТ (3%;3;5; -100000)+ ПРПЛТ (3%;3;5; -100000) отримуємо результат 21835,46	= Залишок боргу за минулий рік - загальна сума платежів за поточний рік отримуємо результат 43670,91
4	= ОСПЛТ (3%;4;5;-100000) отримуємо результат 20582,01	= ПРПЛТ (3%;4;5;-100000) отримуємо результат 1253,44	= ОСПЛТ (3%;4;5; -100000)+ ПРПЛТ (3%;4;5; -100000) отримуємо результат 21835,46	= Залишок боргу за минулий рік - загальна сума платежів за поточний рік отримуємо результат 21835,46
5	= ОСПЛТ (3%;5;5;-100000) отримуємо результат 21199,50	= ПРПЛТ (3%;5;5;100000) отримуємо результат 635,98	= ОСПЛТ (3%;5;5; -100000)+ ПРПЛТ (3%;5;5; -100000) отримуємо результат 21835,46	= Залишок боргу за минулий рік - загальна сума платежів за поточний рік отримуємо результат 00000,00

3. Для визначення розміру грошових коштів на рахунку наприкінці двох років, якщо фізична особа вкладе по 300 грн. щомісячно, поточна сума внеску 7200 грн. при річній ставці 6%, застосуйте фінансову функцію БС. Формула = БС (6%;2;300*12;7200) повертає значення 15505,97 грн. 

4. Для визначення розміру річної відсоткової ставки за один період виплат, якщо необхідно отримати 8000 грн. на протязі 7 років при щомісячній сплаті 150 грн., застосуйте фінансову функцію СТАВКА. Формула = СТАВКА (7*12;-150;8000) повертає значення розміру щомісячної відсоткової ставки в 1%, а для розрахунку річної відсоткової ставки значення 1% поможемо на 12 і отримаємо значення 12%.

5. Припустимо, що ви погодилися на купівлю кондомініуму за 120 000 $. Протягом наступних 5 років ви очікуєте отримати 25000$, 27000$, 35000$, 38000$ та 40000$ чистого рентного прибутку. Формула: = ВСД (-120000;25000;27000;35000;38000;40000) повертає внутрішню швидкість обороту, яка становить 11%. Якщо бар’єрна ставка дорівнює 10%, можна розглядати купівлю кондомініуму як привабливе вкладення.(Дані розмістити в окремих комірках, під час застосування формули, звертатись до адрес комірок).

6. Припустимо, що ви погодилися на купівлю кондомініуму за 120 000 $. Протягом наступних 5 років ви очікуєте отримати 25000$, 27000$, 35000$, 38000$ та 40000$ чистого рентного прибутку. Фінансова ставка дорівнює 10%, а ставка реінвестування - 8%. Використовуючи формулу = МВСД (-120000;25000;27000;35000;38000;40000;10%;8%), ми отримаємо модифіковану внутрішню швидкість обороту, яка дорівнює 10%.

7. Припустимо, що необхідно визначити амортизацію приладу з початковою вартістю 5000 грн, яка має час життя 5 років (60 місяців) і ліквідну вартість 100 грн. Формула = ДДОБ (5000;100;60;1) повідомляє, що амортизація методом двократного обліку за перший місяць складає 166,67 грн.

8. Припустимо, що ви купили обладнання на суму 15000 грн. в кінці першого кварталу поточного року і це майно через 5 років буде мати ліквідну вартість 2000 грн. Щоб визначити амортизацію цього майна за наступний рік (з 4 по 7 квартал його використання), введіть формулу = ПУО (15000;2000;20;3;7). Амортизація за цей період складе 3670,55 грн.

9.  Для розрахунку величини амортизації обладнання за 10 років експлуатації загальною початковою вартістю 340000 дол.США та остаточною вартістю 10000 дол.США методом рівномірної амортизації застосуйте наступну формулу = АПЛ (340000;10000;10), яка повертає значення амортизації за кожен рік у сумі 33000 грн.

10. Для розрахунку величини амортизації по рокам обладнання за 10 років експлуатації загальною початковою вартістю 340000 дол.США та остаточною вартістю 10000 дол.США методом постійного обліку амортизації застосуйте функцію АСЧ. У таблиці 2. наведені формули і результати обчислення.

Таблиця 2.

Рік експлуатації	Формула
1	= АСЧ (340000;10000;10;1) отримуємо результат 60000 грн.
2	= АСЧ (340000;10000;10;2) отримуємо результат 54000 грн.
3	= АСЧ (340000;10000;10;3) отримуємо результат 48000 грн.
4	= АСЧ (340000;10000;10;4) отримуємо результат 42000 грн.
5	= АСЧ (340000;10000;10;5) отримуємо результат 36000 грн
6	= АСЧ (340000;10000;10;6) отримуємо результат 30000 грн.
7	= АСЧ (340000;10000;10;7) отримуємо результат 24000 грн.
8	= АСЧ (340000;10000;10;8) отримуємо результат 18000 грн.
9	= АСЧ (340000;10000;10;9) отримуємо результат 12000 грн
10	= АСЧ (340000;10000;10;10) отримуємо результат 6000 грн

 

14. Зберегти результати на сервері. Виконати контрольні завдання.

Контрольні завдання

1. Визначити розмір грошових коштів на рахунку наприкінці 3 років, якщо фізична особа вкладе по 200 грн. щомісячно, загальна сума внеску 7800 грн. при річній ставці 9% за умови, що відсотки нараховуються що місяця.

2. Визначити розмір річної відсоткової ставки за один період виплат, якщо необхідно отримати 8000 грн. на протязі 7 років при щомісячній сплаті 150 грн.

3. Розрахувати величину амортизації обладнання за 10 років експлуатації загальної початкової вартістю 340000 грн. та остаточною вартістю 1000 грн. методом рівномірної амортизації.

4. Визначити розміри основних платежів по рокам на прикладі позики 100000 грн. строком на 5 років при річній ставці 3%.

5. Визначити розміри сплати по відсоткам загальної щорічної сплати на прикладі позики 100000 грн. строком на 5 років при річній ставці n% (n – номер студента за списком у журналі групи).

6. Розрахувати чистий поточний об’єм вкладу при річній відсотковій ставці 10%, щомісячній сплаті 400 грн. строком на 7 років.

7. Визначити кількість періодів виплат (у роках) кредиту розміром 30000 грн. при річній ставці 12%, щомісячній сплаті 380 грн.

8. Визначити розмір щомісячної сплати кредиту, розміром 30000 грн. при річній відсотковій ставці 9%, строк сплати кредиту 7 років.

 

 

Лабораторна робота № 3.2

Тема: Застосування кореляційно-регресійного аналізу в табличному процесорі MS Excel для визначення параметрів функціональної залежності між результативним фактором та факторами-показниками при обґрунтуванні бізнес-плану створення нової структурної одиниці

Мета: оволодіти методикою застосування кореляційно регресійного аналізу в табличному процесорі MS Excel для визначення параметрів функціональної