Робота з фінансовими функціями Excel

[ виправити ] текст може містити помилки, будь ласка перевіряйте перш ніж використовувати.

Завдання № 1. Робота з фінансовими функціями

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

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

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

Б3 (норма; чісло_періодов; виплата; нз; тип),

де:

норма - процентна ставка за один період;

число _періодов - загальна кількість періодів виплат;

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

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

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

Завдання 1.1. На ощадний рахунок в кінці кожного місяця вносяться обов'язкові платежі по 100 тис. грн. Розрахуйте, яка сума виявиться на рахунку через вісім років при ставці відсотка 9.5% річних.

Рішення:

Для розрахунку застосовується формула БЗ, т.к потрібно знайти майбутнє значення виплаченої суми. У цьому завданню при щомісячному нарахуванні відсотків загальна кількість періодів нарахування дорівнює 8 * 12 (аргумент чісло_періодов), а відсоток за період нарахування дорівнює 9,5% / 12 (аргумент норма). За умовою аргумент нз = - 100000. Це негативна сума, позаяк гроші були вкладені. Виплати неприсутній, тому аргумент виплата відсутня. Використовуючи функцію БЗ, отримаємо

Б3 (9,5% / 12; 8 * 12;; - 1000000) = 14297518,58 грн.

Результати рішення задачі представлені в таблиці 1.

Динаміка зростання вартості показана в малюнку 2.

Таблиця 3 містить розрахункові формули до розв'язання задачі в пакеті Microsoft Excel.

Таблиця 1

Розрахунок майбутньої вартості

	A	B	C	D	E	F	G
1	ЗАВДАННЯ № 1
2	рік	ставка	число періодів	виплата	внесок	тип	величина вкладу
3	1	0,007917	12	-100000	0	0	1253 653,69 р.
4	2	0,007917	24	-100000	0	0	2631 729,49 р.
5	3	0,007917	36	-100000	0	0	4146 575,97 р.
6	4	0,007917	48	-100000	0	0	5811 767,32 р.
7	5	0,007917	60	-100000	0	0	7642 224,88 р.
8	6	0,007917	72	-100000	0	0	9654 350,92 р.
9	7	0,007917	84	-100000	0	0	11866 175,62 р.
10	8	0,007917	96	-100000	0	0	14297 518,58 р.

Малюнок 2

Таблиця 3. Розрахунок майбутньої вартості

	A	B	C	D	E	F	G
1	ЗАВДАННЯ № 1
2	рік	ставка	число періодів	виплата	внесок	тип	величина вкладу
3	1	= 0,095 / 12	= 12 * A3	-100000	0	0	= БЗ (B3; C3; D3; 0; F3)
4	2	= 0,095 / 12	= 12 * A4

-100000

= БЗ (B4; C4; D4; 0; F4)

= 0,095 / 12

= 12 * A5

-100000

= БЗ (B5; C5; D5; 0; F5)

= 0,095 / 12

= 12 * A6

-100000

= БЗ (B6; C6; D6; 0; F6)

= 0,095 / 12

= 12 * A7

-100000

= БЗ (B7; C7; D7; 0; F7)

= 0,095 / 12

= 12 * A8

-100000

= БЗ (B8; C8; D8; 0; F8)

= 0,095 / 12

= 12 * A9

-100000

= БЗ (B9; C9; D9; 0; F9)

= 0,095 / 12

= 12 * A10

-100000

= БЗ (B10; C10; D10; 0; F10)

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

Для розрахунку поточної вартості (початкове значення) вкладу (позики) використовується функція

П3 (норма; Кпер; виплата; бс; тип),

де:

норма - процентна ставка за один період;

КПЕ - загальна кількість періодів виплат;

виплата - це виплата, вироблена в кожен періоду

бс - майбутня вартість вкладу, яку потрібно досягти після останньої виплати, якщо аргумент бс опущений, то він вважається рівним 0; ...

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

Завдання 1.2 Яку суму необхідно покласти на депозит під 16% річних, щоб отримати через чотири роки 25 млн. грн. при щоквартальному нарахуванні відсотків?

Рішення

Для розрахунку використовуємо функцію ПЗ.

При цьому норма = 16%, Кпер = 4, виплата = 2500000 грн., Бс = 0.

П3 (16; 4; 2500000;) ⁼ - 13 347 704,39 р. грн.

Результати рішення задачі представлені в таблиці 4. Динаміка зростання вартості показана в малюнку 5. Таблиця 6 містить розрахункові формули до розв'язання задачі в пакеті Microsoft Excel.

Таблиця 4

Поточна вартість

	A	B	C	D	E	F	G
31	ЗАВДАННЯ № 2
32	рік	ставка	число періодів	виплата	внесок	тип	величина вкладу
33	1	16%	4	0	25000000	0	-21370104,78 Р.
34	2	16%	8	0	25000000	0	-18267255,13 Р.
35	3	16%	12	0	25000000	0	-15614926,24 Р.
36	4	16%	16	0	25000000	0	-13347704,39 Р.

Малюнок 5

Таблиця 6

Поточна вартість

ЗАВДАННЯ № 2

	рік	ставка	число періодів	виплата	внесок	тип	величина вкладу
30	1	16%	= 4 * A30	0	25000000	0	= ПЗ (B30 / 4; C30; D30; E30; F30)
31	2	16%	= 4 * A31	0	25000000	0	= ПЗ (B31 / 4; C31; D31; E31; F31)
32	3	16%	= 4 * A32	0	25000000	0	= ПЗ (B32 / 4; C32; D32; E32; F32)
33	4	16%	= 4 * A33	0	25000000	0	= ПЗ (B33 / 4; C33; D33; E33; F33)

Завдання № 2. Побудова економічної моделі виду y = f (x)

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

Для побудови кореляційного поля необхідно виконати наступні дії:

Відкрити робоче вікно EXCEL і ввести значення даних х и в.

Побудувати точкову діаграму.

Виконати пункти меню Діаграма - Додати лінію тренду. На вкладці Тип вибрати тип діаграми, (лінійна, логарифмічна, поліномінальної, статечна, експонентна).

Звернути увагу на те, що в різних варіантах залежність може бути будь-якого з перерахованих видів. Далі вибрати вкладку Параметри і поставити "7" у вікні Показати рівняння на діаграмі.

Зробити висновок про вид прийнятої гіпотези.

Завдання. Провести економічний аналіз для заданих статистичних даних. Зробити вибір.

X	5,21	5,61	6,12	6,61	7,01	7,59	7,98	8,48	8,99	10,49
Y	13,4	14,12	15,34	16,52	17,02	17,78	19,06	19,96	20,78	23,98

Рішення

Виконуємо побудова точкової діаграми і додаємо лінію трейда з різними типами діаграми:

- Лінійна - логарифмічна

- Поліномінальної - статечна, експонентна

Висновок: проаналізувавши величину коефіцієнта достовірності апроксимації R ² для кожного типу залежності можна зробити висновок, що вихідні економічні дані можна апроксимувати з найбільшою точністю лінійною залежністю y = 1,9844 x + 3,0873 і поліноміальної залежністю у = 0,0029 x2 + +1,9396 x + 3,2537, так як R ² = 0,99966.

Завдання № 3. Модель Леонтьєва багатогалузевої економіки (балансовий аналіз)

Однією з основних завдань, що виникають в макроекономіці, є завдання, пов'язане з ефективністю ведення багатогалузевого господарства; яким має бути обсяг виробництва кожної з n галузей, щоб задовольнити всі потреби у продукції цієї галузі. При цьому кожна галузь виступає, з одного боку, як виробник деякої продукції, а з іншого - як споживач продукції і своєю, і виробленої іншими галузями.

Введемо наступні позначення:

-Вектор валового випуску;

х _{y -} обсяг продукції i-й галузі, споживаної j-й галуззю в процесі виробництва;

-Вектор кінцевого продукту;

- Матриця прямих витрат, коефіцієнти прямих витрат обчислюються за формулою .

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

Матричне рішення даного завдання:

Робота з матрицями s пакеті Excel

У пакеті Excel існує кілька функцій для роботи з матрицями:

Трансп - транспонування матриці;

МОПРЕД - знаходження визначника матриці;

МУМНОЖ - множення матриць;

МОБР - знаходження оберненої матриці.

Всі ці функції (крім Трансп) знаходяться в категорії "Математичні", функція Трансп - в категорії "Посилання та масиви".

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

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

2 Не можна вибрати Вставка функції, знайти потрібну функцію.

3 Ввести адресу (або адреси) вихідної матриці (безпосередньо або курсором). Натиснути кнопку "ОК".

Для того, щоб отримати на екрані всі значення результату, натиснути клавіші F 2 і одночасно Ctrl + Shift + Enter.

Завдання

Зв'язок між трьома галузями представлена матрицею прямих витрат А. Попит (кінцевий продукт) задано вектором Y. Знайти валовий випуск продукції галузей Х. Описати використовувані формули, представити роздруківку зі значеннями та формулами.

Рішення:

1. Вводимо вихідні дані в комірки пакета Excel. Матрицю прямих витрат А вводимо в осередки (B 2: D 4), матрицю попиту в осередки (G 2: G 4).

2. Визначимо матрицю прямих витрат . Спочатку знайдемо матрицю (Е-А).

Де Е - одинична матриця,

Вводимо в осередки (B 6: D 8) одиничну матрицю. Матрицю (Е-А) порахуємо в осередках (B 13: D 15) за формулою

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

Для цього виставляємо курсор у комірку, де буде визначник (G 14), викликаємо Вставку функції, в категорії "Математичні" вибираємо функцію знаходження визначника матриці МОПРЕД, вводимо адресу матриці МОПРЕД (В13: D 15) і натискаємо "ОК". У клітинці G 14 з'являється значення визначника матриці.

4. Для знаходження оберненої матриці використовуємо математичну функцію МОБР. Обернену матрицю знаходимо функцією МОБР:

Для цього виділяємо блок комірок, де повинна знаходиться зворотна матриця (B 17: D 19), викликаємо Вставку функції, в категорії "Математичні" вибираємо функцію знаходження оберненої матриці МОБР, вводимо адресу матриці MOBP (B 13: D 15), натискаємо "ОК ". Для отримання на екрані значення коефіцієнтів оберненої матриці, натискаємо клавіша F 2 і Ctrl + Shift + Enter одночасно.

5. Вектор валового випуску визначається за формулою , Н аходім вектор рішень системи рівнянь множенням оберненої матриці на вектор-стовпець , Використовуючи вбудовану математичну функцію МУМНОЖ:

Для цього виділяємо блок, де буде знаходиться вектор - (G 17: G 19). Викликаємо Вставку функції в категорії "Математичні", вибираємо функцію МУМНОЖ, вводимо адресу оберненої матриці (B 17: D 19) і вектора Y (G 2: G 4):

МУМНОЖ (B 17: D 19; G 2: G 4), натискаємо "ОК" Для отримання на екрані значення рішення, натискаємо клавіша F2 і Ctri + Shift + Enter одночасно.

У результаті рішення було визначено, що для задоволення попиту необхідно виробити продукції в1-й, 2-й і 3-й галузях на 100, 100 і 90 д. е. відповідно.

Витрати (галузі)	Випуск (споживання)			Кінцевий продукт	Валовий випуск
	1	2	3
1	0,05	0.15	0,4	44	100
2	0,1	0.1	0,3	53	100
3	0,3	0,15	0,2	27	90

	A	B	C		D	E	F	G
1	РОЗРАХУНОК ВАЛОВОГО ВИПУСКУ ПРОДУКЦІЇ
2		0,05	0,15	0,4				44
3	А =	0,1	0,1	0,3			Y =	53
4		0,3	0,15	0,2				27
5
6		1	0	0
7	Е =	0	1	0
8		0	0	1
9
10
11	Рішення завдання
12
13		0,95	-0,15	-0,4
14	EA =	-0,1	0,9	-0,3			D =

0,50175

-0,3

-0,15

0,8

1,34529148

0,358744

0,807175

100

EA (-1) =

0,33881415

1,275536

0,647733

(EA) (-1) * Y =

100

0,56801196

0,373692

1,674141

РОЗРАХУНОК ВАЛОВОГО ВИПУСКУ ПРОДУКЦІЇ

0,05

0,15

0,4

А =

0,1

0,3

Y =

0,3

0,15

0,2

Е =

Рішення завдання

= B6-B2

= C6-C2

= D6-D2

EA =

= B7-B3

= C7-C3

= D7-D3

D =

= МОПРЕД (B13: D15)

= B8-B4

= C8-C4

= D8-D4

= МОБР (B13: D15)

= МУМНОЖ (B17: D19; G2: G4)

EA (-1) =

= МОБР (B13: D15)

	= МОБР (B13: D15)		(EA) (-1) * Y =	= МУМНОЖ (B17: D19; G2: G4)
19		= МОБР (B13: D15)	= МОБР (B13: D15)	= МОБР (B13: D15)			= МУМНОЖ (B17: D19; G2: G4)

Завдання № 4

У дослідному господарстві встановили, що відгодівля тварин можливий тоді, коли тварина буде отримувати речовини А не менше 10 од., Речовини В - не менше 12 од. і речовини С - не менше 4 од. Для годування тварини використовуються два види корму. У 1 кг корму першого виду міститься 2, 2 і 0 одиниць поживних речовин відповідно. У 1 кг корму другого виду міститься 1, 3, 2 одиниці поживних речовин відповідно. Ціна 1 кг корму першого виду дорівнює 50 д. е., корми другого виду - 60 д. е. Скільки корму кожного виду потрібно витрачати щодня, щоб витрати на нього були мінімальними?

Рішення:

1. Формалізація задачі.

Позначимо:

кількість корму 1-го виду через x _1;

кількість корму 2-го виду через x _2;

Тоді цільова функція - витрати на корм - дорівнює:

z = 50 x ₊₁ +60 x ₂

Співвідношення кількості речовини А в денному раціоні не повинна бути менше 10 д. е., тобто

2 x ₁ +1 x ₂ ≥ 10

Відповідно для речовини В і речовини С

2 x ₁ +3 x ₂ ≥ 12

0 x ₁ +2 x ₂ ≥ 4

Так як x ₁ і x ₂ - кількість продукту, то справедливо

x ₁ ≥ 0

x ₂ ≥ 0

Отримана математична модель задачі про сумішах:

z = 50x ₁ +60 x ₂ (min)

2x ₁ +1 x ₂ ≥ 10

2x ₁ +3 x ₂ ≥ 12

0 x ₁ +2 x ₂ ≥ 4

x ₁ ≥ 0

x ₂ ≥ 0

2. Точне (алгебраїчне) рішення формалізованої завдання.

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

Система рівнянь I:

{	2 x ₁ +1 x ₂ ≥ 10 [1]
	0x ₁ +2 x ₂ ≥ 4 [2]

з [2] x ₂ = _2; тоді з [1] x ₁ = 4, Система рівнянь II:

{	2 x ₁ +3 x ₂ ≥ 12 [3]
	0 x ₁ +2 x ₂ ≥ 4 [4]

з [4] x ₂ = _2; тоді з [3] x ₁ = 3, Приймаємо x ₁ = 4, x ₂ = _2, оскільки значення x ₁ = 3 не задовольняє нерівність 2 x ₁ +1 x ₂ ≥ 10

3. Графічне рішення формалізованої завдання.

Будуємо область, яка є перетином всіх площин математичної моделі отриманої при формалізації задачі (див. черт.1).

Знаходимо градієнт функції z: grad z = {50; 60}. Будуємо вектор з початком у т. (0; 0) і кінцем у точці (50; 60). Визначаємо зону допустимих рішень. Для цього будуємо лінії обмежень, прирівнюючи між собою ліві і праві частини рівнянь і визначаючи значення точок перетину ліній обмеження з осями Х1 і Х2, привласнюючи значення рівні 0:

2 x ₁ +1 x ₂ = 10; x ₁ = 0, x ₂ = 10 / x ₁ = 5, x ₂ = 0, 2 x ₁ +3 x ₂ = 12; x ₁ = 0, x ₂ = 4 / x ₁ = 6, x ₂ = 0

0 x ₁ +2 x ₂ = 4; x ₂ = _2, x ₁ = 0, x _{2 =} 0

Будуємо пряму, перпендикулярну вектору градієнта. Пересуваємо цю пряму в напрямку, зазначеному вектором. Сама остання точка, яку перетинає пряма, і є точка максимуму.

Рисунок 1 - Графічне рішення формалізованої завдання

4. Рішення задачі за допомогою пакету Excel.

Для вирішення даної задачі лінійного програмування в пакеті Excel скористаємося допомогою пункту меню Сервіс, пункт Пошук рішення.

Перш, ніж скористатися цією програмою, введемо вихідні дані:

1. У комірки C 3 та D 3 вводимо значення точки максимуму відповідно.

2. Вводимо коефіцієнти цільової функції 50 і 60 в осередку C 6 і D 6 відповідно.

3. У осередок F 6 вводимо формулу для обчислення цільової функції. Для цього викликаємо Вставка функції - "Математичні" - СУММПРОІЗВ і вводимо осередку C $ 3: D $ 3 і C 6: D 6. Формат функції; = СУММПРОІЗВ (С $ 3: 0 $ 3; С6: D 6).

4. У комірки C 4: D 4 вводимо нижні межі рівні 0. Нижня межа показує, що змінні не негативні.

5. Вводимо коефіцієнти системи обмежень у клітинки C 10: D 12.

6. Вводимо праві частини системи обмежень у клітинки Н10: Н12.

7. У осередок F 10 вводимо формулу розрахунку виконання обмежень = СУММПРОІЗВ (С $ 3: D $ 3; C 10: D О). Копіюємо цю формулу в комірки F 11, F 12.

8. У осередок I 10 вводимо формулу розрахунку невикористаних ресурсів = H 10 - F 10. Копіру їм цю формулу в комірки I 11, I 12

Після введення вихідних даних викликаємо програму Пошук рішення з пункту меню Сервіс.

У вікно Пошуку рішення вводимо значення в осередках:

1. Вводимо $ F $ 6 у вікно "Встановити цільову клітинку", виставляємо її "Рівної мінімального значення".

2. У віконце "Змінюючи осередку" вводимо $ C $ 3: $ D $ 3.

3. У віконці "Обмеження" вибираємо пункт "Додати"

"Посилання на клітинку" - СЗ, знак -> =, "Обмеження" - С4. З'являється обмеження:

$ З $ 3> = $ З $ 4. Аналогічно вводимо:

$ D $ 3> = $ D $ 4;

$ F $ 10> = $ H $ 10;

$ F $ 11> = $ H $ 11;

$ F $ 12> = $ H $ 12

4. Після цього натискаємо "Виконати", далі Тип звіту - "Результати".

Отримуємо рішення в осередках СЗ і D3 - значення змінних, в осередках F6 - значення цільової функції, в осередках F 10: F 12 - значення обмежень до в осередках I 10: I 12 - різницю між вихідними ресурсами та використаними.

	A	B	C		D	E	F	G	H	I
1			Змінні
2			X1	X2
3		Значення	4	2
4		Ниж. межа	0	0
5		Верхн. межа
6		F	50	60			320	max
7		Коефіцієнти цільової ф-ції
8
9			Коеф-ти				Значення		Факт. ресурси	Неісп. ресурси
10	Сис-ма обмежень		2	1			10	> =	10	0
11			2	3			14	> =	12	-2
12			0	2			4	> =	4	0

	A	B	C	D	E	F	G	H	I
1			Змінні
2			X1	X2
3		Значення	4	2
4		Ниж. межа	0	0


5		Верхн. межа
6		F	50	60	= СУММПРОІЗВ (C3: D3; C6: D6)	max
7		Коефіцієнти цільової ф-ції
8
9			Коеф-ти		Значення		Факт. ресурси	Неісп. ресурси
10	Сис-ма огранич		2	1	= СУММПРОІЗВ (C3: D3; C10: D10)	> =	10	= H10-F10
11			2	3	= СУММПРОІЗВ (C3: D3; C11: D11)	> =	12	= H11-F11
12			0	2	= СУММПРОІЗВ (C3: D3; C12: D12)	> =	4	= H12-F12

Економічний висновок

Для мінімізації витрат при щоденному витраті необхідно включать в раціон 4 кг першого виду і 2 кг другого виду кормів. при цьому в раціон необхідно вносити:

Речовини А - 10 од. при фактичному 10 од.

Речовини В - 14 од. при фактичному 12 од.

Речовини С - 4 од. при фактичному 4 од.

Речовина В є недостатнім

Додати в блог або на сайт

Цей текст може містити помилки.

Програмування, комп'ютери, інформатика і кібернетика | Контрольна робота
113.5кб. | скачати

Схожі роботи:
Робота з функціями в мові програмування Сі
Робота з програмою Excel
Робота з базами даних в MS Excel
Робота з EXCEL прийоми оформлення документа звіту
Norton commander Word Excel і робота з ними
Робота з редакторами Word і Excel Програмування в Паскалі
Робота з данними в середовищі електронних таблиць Microsoft Excel
Робота з макросами та модулями в Excel Основні прийоми створення макросів використання їх в до
Організація та методика проведення уроку з теми Професійна робота з табличним редактором MS Excel