Використання Excel для рішення статистичних завдань

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

скачати

МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИ
Кафедра прикладної математики

Контрольна робота

з дисципліни «Інформатика»

2007


Завдання до контрольної роботи
Завдання № 1 Виконати розрахунки з використанням фінансових функцій. Оформити таблицю і побудувати діаграму, що відображає динаміку зростання внеску по роках. Описати використовувані формули, представити роздруківку зі значеннями і з формулами:
15.1 Внесок розміром 500 тис. грн. покладений під 12% річних. Розрахуйте, яка сума буде на ощадному рахунку через шість років, якщо відсотки нараховуються кожні півроку
15.2 Визначити поточну вартість звичайних щорічних платежів розміром 20 тис. грн. протягом трьох років при нарахуванні 16% річних.
Завдання № 2 Провести економічний аналіз для заданих статистичних даних і зробити висновок.
Таблиця 1 - Статистичні дані
X
1,01
1,51
2,02
2,51
3,01
3,49
3,98
4,48
4,99
5,49
Y
5,02
5,92
7,14
8,32
9,02
9,58
11,06
11,96
12,78
13,98
Завдання № 3 Зв'язок між трьома галузями представлена ​​матрицею прямих витрат А. Попит (кінцевий продукт) задано вектором . Знайти валовий випуск продукції галузей . Описати використовувані формули, представити роздруківку зі значеннями і з формулами.

Завдання № 4 Вирішити задачу лінійного програмування.
Варіант 15 Комерційний магазин хоче закупити овочі А і В. Кількість овочів, закупівельні ціни і ціни, за якими магазин продає овочі, наведені в таблиці 8.

Таблиця 8
Овочі
Ціни
Кількість овочів
Закупівля
Реалізація
А
1,6
2,4
60
У
1,7
2,2
70
Як вигідніше вкласти гроші, якщо загальна сума, якої розташовується магазин в даний час, становить 180 ВО, причому овочів А потрібно придбати не менше 10 тонн.

Завдання № 1
15.1 Внесок розміром 500 тис.грн. покладений під 12% річних. Розрахуйте, яка сума буде на ощадному рахунку через шість років, якщо відсотки нараховуються кожні півроку
Рішення
Для розрахунку поточної вартості вкладу будемо використовувати функцію
БЗ (норма; чісло_періодов; виплата; нз; тип),
де норма - процентна ставка за один період. У нашому випадку
величина норми становить 13% річних.
число періодів - Загальна кількість періодів виплат. У нашому випадку
дана величина становить 6 років.
виплата - виплата, вироблена в кожний період. У нашому
випадку ця величина покладається рівною -100000.
нз - поточна вартість вкладу. Дорівнює 0.
тип - даний аргумент можна опустити (дорівнює 0).
Отримаємо такий вираз БЗ (12 / 2; 12; 0; - 500, 0) = 1006.10 тис. грн.
Розрахунок майбутньої вартості вкладу по роках наведено в таблиці 3.
Таблиця 3 - Розрахунок майбутнього вкладу
РОЗРАХУНОК ПОТОЧНОГО ВКЛАДУ
РІК
СТАВКА
ЧИСЛО
ВИПЛАТА
ВНЕСОК, тис. грн
ТИП
ВЕЛИЧИНА
(РІК)
ПЕРІОДІВ
ВКЛАДУ, тис. грн
1
12%
2
0
-500
0
561.80
2
12%
4
0
-500
0
631.24
3
12%
6
0
-500
0
709.26
4
12%
8
0
-500
0
796.92
5
12%
10
0
-500
0
895.42
6
12%
12
0
-500
0
1006.10

Гістограма, яка відображає динаміку вкладу зростання по роках представлена ​​нижче.
\ S
Рисунок 1 - Динаміка зростання внеску по роках
Висновок: Розрахунки показують, що на рахунку через шість років буде 1006.10 тис. грн.
15.2 Визначити поточну вартість звичайних щорічних платежів розміром 20 тис. грн. протягом трьох років при нарахуванні 16% річних.
Рішення
Для розрахунку використовуємо функцію
ПЗ (норма; Кпер; виплата; бс; тип),
де норма = 16% - процентна ставка за один період;
Кпер = 3 - загальна кількість періодів виплат;
виплата = 20 тис. грн. - Щорічні платежі;
При цьому:
ПЗ (16%; 3; 20) = - 44,92 тис. грн.
Результат вийшов негативний, оскільки це сума, яку необхідно вкласти.
Висновок: Таким чином при заданих умовах поточна вартість вкладу становить 44,92 тис. грн.
Завдання № 2
1.2. Провести економічний аналіз для заданих статистичних даних і зробити висновок.
Таблиця 4 - Задані статистичні дані
X
1,01
1,51
2,02
2,51
3,01
3,49
3,98
4,48
4,99
5,49
Y
5,02
5,92
7,14
8,32
9,02
9,58
11,06
11,96
12,78
13,98
Рішення
1. Вводимо значення X і Y, оформляючи таблицю;
2. За даними таблиці будуємо точкову діаграму (див. рисунок 2);
3. Виконавши пункти меню Діаграма - Додати лінію тренда, отримуємо лінію тренда (див. рисунок 2);
З можливих варіантів типу діаграми (лінійна, логарифмічна, поліноміальна, статечна, експонентна), вибираємо лінійну залежність, тому що вона забезпечує найменше відхилення від заданих значень параметра Y.
y = 1.9733 x + 3.0667 - рівняння залежності;
R 2 = 0.9962 - величина вірогідності апроксимації;
4. Для обгрунтування зробленого вибору оформимо таблицю 5 - порівняльний аналіз прийнятих і заданих значень параметра Y.
У цій таблиці:
Y 1 - значення параметра Y, згідно з прийнятою гіпотезі;
Y - значення параметра Y, згідно з заданим даними.
ε - величина арифметичного відхилення ε = Y - Y 1;
\ S
Малюнок 2 - графік залежності у = f (x)
Таблиця 5 - Порівняльний аналіз заданих і прийнятих значень Y
X
1.01
1.51
2.02
2.51
3.01
3.49
3.98
4.48
4.99
5.49
Y
5.02
5.92
7.14
8.32
9.02
9.58
11.06
11.96
12.78
13.98
Y1
5.06
6.05
7.05
8.02
9.01
9.95
10.92
11.91
12.91
13.90
E
-0.04
-0.13
0.09
0.30
0.01
-0.37
0.14
0.05
-0.13
0.08
Висновок: На основі зібраних статистичних даних, представлених в таблиці знаходимо економічну модель - прийнята гіпотеза має ступеневу залежність і виражається рівнянням
y = 1.9733 x + 3.0667
Економічне прогнозування на основі рівняння даної залежності відрізняється достовірністю в області початкових значень параметра X - величина ε приймає малі значення і неточністю у довгостроковому періоді - в області кінцевих значень параметра X.

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

Рішення

Дане завдання пов'язана з визначенням обсягу виробництва кожної з N галузей, щоб задовольнити всі потреби в продукції даної галузі. При цьому кожна галузь виступає і як виробник деякої продукції і як споживач своєї і виробленої іншими галузями продукції. Завдання міжгалузевого балансу - відшукання такого вектора валового випуску X, який при відомій матриці прямих витрат забезпечує заданий вектор кінцевого продукту Y.
Матричне рішення даного завдання:
X = (E - A) -1 Y. [2]

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

1. МОБР - знаходження оберненої матриці;
2. МУМНОЖ - множення матриць;
3. МОПРЕД - знаходження визначника матриці;
Також під час вирішення даного завдання використовували поєднання клавіш:
F2 CTRL + SHIFT + ENTER - для отримання на екрані всіх значень результату.
Розрахункові формули для вирішення даного завдання показані в таблиці 7.
Результат рішення показано в таблиці 6.
Таблиця 6 - Розрахункові формули
Витрати
Випуск (споживання)
Кінцевий
Валовий
(Галузі)
галузь А
галузь B
галузь C
продукт
випуск
галузь А
0.05
0.1
0.4
47
= МУМНОЖ (F12: H14; E3: E5)
галузь B
0.1
0.1
0.3
58
= МУМНОЖ (F12: H14; E3: E5)
галузь C
0.3
0.15
0.2
81
= МУМНОЖ (F12: H14; E3: E5)
Рішення
Е =
1
0
0
0
1
0
0
0
1
Е-А =
= B8-B3
= C8-C3
= D8-D3
(Е-А) -1 =
= МОБР (B12: D14)
= МОБР (B12: D14)
= МОБР (B12: D14)
= B9-B4
= C9-C4
= D9-D4
= МОБР (B12: D14)
= МОБР (B12: D14)
= МОБР (B12: D14)
= B10-B5
= C10-C5
= D10-D5
= МОБР (B12: D14)
= МОБР (B12: D14)
= МОБР (B12: D14)
Det (EA) =
= МОПРЕД (B12: D14)
Таблиця 7 - Результат рішення
Витрати
Випуск (споживання)
Кінцевий
Валовий
(Галузі)
галузь А
галузь B
галузь C
продукт
випуск
галузь А
0.1
0.1
0.4
47
140
галузь B
0.1
0.1
0.3
58
140
галузь C
0.3
0.15
0.2
81
180
Рішення
Е =
1
0
0
0
1
0
0
0
1
Е-А =
1
-0.1
-0.4
(Е-А) -1 =
1.322880941
0.27438
0.76433
-0.1
0.9
-0.3
0.333170015
1.25429
0.63694
-0.3
-0.2
0.8
0.558549731
0.33807
1.65605
Det (EA) =
0.51025

Висновок: Для задоволення попиту на продукцію галузі А величиною 47 Д.Є., галузі У - 58 Д.Є. і галузі З - 81 Д.Є. необхідно виробити продукції галузі А на суму 140 Д.Є., галузі В на суму 140 Д.Є., галузі С - на суму 180 Д.Є.

Завдання № 4
Варіант 15 Комерційний магазин хоче закупити овочі А і В. Кількість овочів, закупівельні ціни і ціни, за якими магазин продає овочі, наведені в таблиці 8.
Таблиця 8
Овочі
Ціни
Кількість овочів
Закупівля
Реалізація
А
1,6
2,4
60
У
1,7
2,2
70
Як вигідніше вкласти гроші, якщо загальна сума, якої розташовується магазин в даний час, становить 180 ВО, причому овочів А потрібно придбати не менше 10 тонн.

Рішення

Рішення даного завдання складається з трьох основних етапів:
1. складання математичної моделі (формалізація задачі);
Позначимо величину прибутку від овочу А як А, а величину прибутку від обощ У як В, тоді отримаємо, що прибуток від продажу овоча А становить (2,4-1,6) А, відповідно овоча В - (2,2-1, 7) В. Сумарний прибуток магазину від продажу овочів складе (2,4-1,6) А + (2,2-1,7) В = 0,8 А +0,5 В.
Тоді цільова функція має вигляд Z = 0,8 А - 0,5 В
сумарний прибуток повинна бути найбільшою (максимальної).
Дане завдання містить дві невідомих змінних, тобто її можна назвати плоскою і вона може бути вирішена графічно.
Складемо систему обмежень, виходячи з умови задачі:
- Обмеження на купівлю овочів по грошах:
На купівлю овоча А витрачається 1,6 Д.Е на 1 тонн. На всю кількість овоча А витрачається 1,6 А Д.Є. На овоч У витрачається 1,7 Д.Є. на 1 тонну на закупівлю овоча У витрачають 1,7 В. Отже, виходячи з умови задачі, сумарна сума на яку закуповуються овоча не повинна перевищувати 180 Д.Є. Отримаємо перша нерівність системи:
1,6 А + 1,7 В ≤ 180;
- Додаткові умови:
У умови завдання міститься додаткова умова - закупівля овоча А не менше 10 тонн і не більше 60 тонн. тобто маємо додаткові нерівності для овоча А:
А ≥ 10;
А ≤ 60;
Для овоча У накладено верхнє обмеження не більше 70 тонн, з умови задачі зрозуміло що нижнім обмеження є 0. Отримуємо додаткові нерівності для овоча В:
У ≥ 0;
У ≤ 70;
Отримали математичну модель задачі:
1,6 А + 1,7 В ≤ 180;
А10; А60;
У0; У70;
2. рішення формалізованої задачі;
Вирішивши завдання графічно та з використанням пакету Excel, отримаємо однакове рішення:
А = 60 тонн.
В = 49,412 тонн.
Хід вирішення - див таблиця 9 і рисунок 3
Висновок: для отримання максимального прибутку у розмірі 72,7 гр. од. необхідно в такий спосіб витратити існуючі гроші:
- Овоч А закупити в кількості 60 тонн.
- Овоч У закупити в кількості 49,412 м.
При цьому необхідно витратить усі гроші: 180 Д.Є.
Графічне рішення задачі 4

Необхідно знайти значення (А, В), при яких функція Z = 0,8 А - 0,5 В досягає максимуму. При цьому А і В повинні задовольняти системі обмежень, наведеної раніше:

1,6 А + 1,7 В ≤ 180;
А10; А60;
У0; У70;

Рішення

1. Будуємо область, яка є перетином всіх півплощини, рівняння яких наведено в системі обмежень. Наприклад, полуплоскость 1,6 А + 1,7 В ≤ 180; представляє собою сукупність точок, що лежать нижче прямої, що з'єднує точки з координатами (65; 44,705) і (32,813; 75). Аналогічно - інші. Побудова - малюнок 3.
2. Знаходимо градієнт функції Z.
grad z = {0,8, 0,5}
Будуємо вектор з початком у точці (0; 0) і кінцем у точці (0,8; 0,5).
Побудова - малюнок 3.
3. Будуємо пряму, перпендикулярну вектору градієнта. Оскільки за умовою ми шукаємо максимум функції Z, то пересуваємо пряму в напрямку вказаному вектором. Точка максимуму - остання точка області, яку перетне ця пряма. У нашому випадку, шукана точка лежить на перетині прямих А = 60 і 1,6 А + 1,7 В = 180;
Побудова - рисунок 3
4. Вирішуємо систему рівнянь
А = 60;
1,6 А + 1,7 В = 180; В = 49,412;
Тобто графічну побудову дало результат (60; 49,412).

Максимальне значення функції Z = 0,8 * 60 +0,5 * 49,412 = 72,7.

Рисунок 3 - Графічне рішення задачі 4


Рішення задачі 4 з використанням пакету Excel

У пакеті Excel рішення задачі лінійного програмування здійснюється за допомогою пункту меню Сервіс - Пошук рішення.
Роздруківка рішення задачі в Excel наведена в таблиці 9.
Формули, за якими було зроблено розрахунок, наведені в таблиці. 10.
Таблиця 9 - Рішення задачі в Excel
Змінні
A
B
Значення
60
49.412
Нижня межа
10
0
Верхня межа
60
70
Z = (2.4-1.6) A + (2.2-1.7) B
0.8
0.5
72.706
max
Коефіцієнти цільової функції
Коефіцієнти
Значення
Фактичні ресурси
Невикористані ресурси
Система обмежень
1.6
1.7
180
<=
180
0
Таблиця 10 - Формули для розрахунку в Excel
Змінні
A
B
Значення
60
49.412
Нижня межа
10
0
Верхня межа
60
70
Z = (2.4-1.6) A + (2.2-1.7) B
0.8
0.5
= СУММПРОІЗВ
(B3: C3; B6: C6)
max
Коефіцієнти цільової функції
Коефіцієнти
Значення
Фактичні ресурси
Невикор-
ванні ресурси
Система обмежень
1.6
1.7
= СУММПРОІЗВ
(B3: C3; B10: C10)
<=
180
= F10-D10

Список використаної літератури
1. Фінансово-економічні розрахунки в Excel. - 2-е вид., Доп. - М: Інформаційно-видавничий дім «Філін», 2005. - 184 с.
2. Методичний вказівки та контрольні завдання з дисципліни «Інформатика» для студентів заочного факультету економічного напряму навчання. Ч. 3 / Укл. В.Н. Чорномаз, Т.В. Шевцова, О.А. Медведєва. - ДДМА, 2006 - 40 стор
Додати в блог або на сайт

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

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


Схожі роботи:
Рішення математичних завдань засобами Excel
Використання кореляційно регресійного аналізу для обробки економічних статистичних даних
Використання табличного процесора Excel для розрахунків
Використання інформатики для вирішення економічних завдань
Рішення військово логістичних завдань з вибору оптимального маршруту для військово транспортних засобів
Рішення військово-логістичних завдань з вибору оптимального маршруту для військово-транспортних засобів
Обчислення статистичних показників за допомогою пакету Excel 2
Обчислення статистичних показників за допомогою пакету Excel
Excel вирішення завдань з підбором параметрів
© Усі права захищені
написати до нас