додати матеріал

приховати рекламу

Робота з фінансовими функціями 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

0

0

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

5

3

= 0,095 / 12

= 12 * A5

-100000

0

0

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

6

4

= 0,095 / 12

= 12 * A6

-100000

0

0

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

7

5

= 0,095 / 12

= 12 * A7

-100000

0

0

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

8

6

= 0,095 / 12

= 12 * A8

-100000

0

0

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

9

7

= 0,095 / 12

= 12 * A9

-100000

0

0

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

10

8

= 0,095 / 12

= 12 * A10

-100000

0

0

= БЗ (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

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


A

B

C

D

E

F

G

28

ЗАВДАННЯ № 2

29

рік

ставка

число періодів

виплата

внесок

тип

величина вкладу

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 2 для кожного типу залежності можна зробити висновок, що вихідні економічні дані можна апроксимувати з найбільшою точністю лінійною залежністю y = 1,9844 x + 3,0873 і поліноміальної залежністю у = 0,0029 x2 + +1,9396 x + 3,2537, так як R 2 = 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

15


-0,3

-0,15

0,8




16








17


1,34529148

0,358744

0,807175



100

18

EA (-1) =

0,33881415

1,275536

0,647733


(EA) (-1) * Y =

100

19


0,56801196

0,373692

1,674141



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


= B6-B2

= C6-C2

= D6-D2




14

EA =

= B7-B3

= C7-C3

= D7-D3


D =

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

15


= B8-B4

= C8-C4

= D8-D4




16








17


= МОБР (B13: D15)

= МОБР (B13: D15)

= МОБР (B13: D15)



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

18

EA (-1) =

= МОБР (B13: D15)

= МОБР (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 +1 +60 x 2

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

2 x 1 +1 x 2 ≥ 10

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

2 x 1 +3 x 2 ≥ 12

0 x 1 +2 x 2 ≥ 4

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

x 1 ≥ 0

x 2 ≥ 0

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

z = 50x 1 +60 x 2 (min)

2x 1 +1 x 2 ≥ 10

2x 1 +3 x 2 ≥ 12

0 x 1 +2 x 2 ≥ 4

x 1 ≥ 0

x 2 ≥ 0

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

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

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

{

2 x 1 +1 x 2 ≥ 10 [1]


0x 1 +2 x 2 ≥ 4 [2]

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

{

2 x 1 +3 x 2 ≥ 12 [3]


0 x 1 +2 x 2 ≥ 4 [4]

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

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

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

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

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

0 x 1 +2 x 2 = 4; x 2 = 2, x 1 = 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
Norton commander Word Excel і робота з ними
Робота з EXCEL прийоми оформлення документа звіту
Робота з редакторами Word і Excel Програмування в Паскалі
Робота з данними в середовищі електронних таблиць Microsoft Excel
Робота з макросами та модулями в Excel Основні прийоми створення макросів використання їх в до
Організація та методика проведення уроку з теми Професійна робота з табличним редактором MS Excel
© Усі права захищені
написати до нас
Рейтинг@Mail.ru