- •Міністерство фінансів України
- •Передмова
- •1. Програма навчальної дисципліни
- •«Інформаційні системи і технології у фінансах»
- •Тематичний план навчальної дисципліни
- •Зміст навчальної дисципліни
- •Тема 2. Економічна інформація і засоби її формалізованого опису
- •Тема 8. Автоматизована інформаційна система Держказначейства України
- •Модуль 2. Програмне забезпечення автоматизованої обробки інформації
- •Тема 9. Автоматизація обробки інформації у страховій сфері
- •Тема 10. Автоматизація управління фінансами в бюджетних установах.
- •Тема 11. Автоматизація управління фінансами підприємств і комерційних структур.
- •Тема 12. Інформатизація фінансового ринку.
- •Тема 13. Міжнародна електронна мережа Міждержавних розрахунків swift.
- •2. Розподіл балів, що присвоюються студентам
- •3. Методичні рекомендації до самостійної роботи
- •Тема 3. Інформаційні технології обробки економічної інформації.
- •Тема 4. Організація інформаційної бази систем обробки економічної інформації.
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Тема 5. Організаційно-методичні основи створення і функціонування інформаційних систем управління фінансами.
- •Тема 6. Автоматизована система фінансових розрахунків (асфр).
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Тема 7. Автоматизація обробки інформації в податковій сфері України
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Тема 8. Автоматизована інформаційна система Держказначейства України
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Додаток
- •Про виконання державного бюджету за доходами
- •1 Графа:
- •2 Графа:
- •4 Графа:
- •5 Графа:
- •Тема 9. Автоматизація обробки інформації у страховій сфері
- •Тема 10. Автоматизація управління фінансами в бюджетних установах
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Тема 11. Автоматизація управління фінансами підприємств і комерційних структур
- •Тема 12. Інформатизація фінансового ринку
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Тема 13. Міжнародна електронна мережа Міждержавних розрахунків swift. Методика та алгоритми розв’язання основних задач аналізу фінансових розрахунків.
- •План вивчення теми
- •Методичні рекомендації до самостійної роботи
- •Аналіз фінансових операцій з елементарними потоками платежів за допомогою фінансових функцій Excel.
- •Таким чином,
- •Завдання
- •4. Методичні рекомендації до практичних занять
- •Практичне заняття №1
- •Тема 2. Економічна інформація і засоби її формалізованого опису.
- •План заняття
- •Ознайомитися з результатом розв’язку на листку "Результат 2".
- •В ms Word підготувати звіт, в якому подати:
- •Методичні рекомендації до практичного заняття
- •Обмеження – це залежності між шуканими змінними:
- •Завдання
- •Коефіцієнти цільової функції
- •Практичне заняття №2
- •Тема 3. Інформаційні технології обробки економічної інформації
- •План заняття
- •Методичні рекомендації до практичного заняття
- •Завдання
- •Практичне заняття № 3
- •Варіанти завдань
- •2. Прогнозування за допомогою функцій регресії в ms Ехсеl
- •2.1. Лінійний прогноз
- •Лінійний прогноз
- •Обсяг продажу товарів у 2006 році
- •Практичне заняття №4
- •Тема 10. Автоматизація управління фінансами в бюджетних установах.
- •Методичні рекомендації до практичного заняття
- •Завдання
- •Перший запуск програми
- •План заняття
- •5. Методичні рекомендації до виконання індивідуальних завдань
- •6. Підсумковий контроль
- •При модульному контролі
- •Система бальної оцінки знань, вмінь та навичок студентів на підставі об’єктів оцінювання з дисципліни «Інформаційні системи і технології у фінансах»
- •Cамостійна робота студентів
- •Індивідуальна робота студентів
- •Список рекомендованої літератури
- •Інформаційні системи і технології у фінансах
Лінійний прогноз
Обсяг продажу |
Дні |
Прогноз
|
45 |
1 |
44,67 |
34 |
2 |
47,35 |
71 |
3 |
50,02 |
56 |
4 |
52,69 |
39 |
5 |
55,36 |
45 |
6 |
58,04 |
67 |
7 |
60,71 |
89 |
8 |
63,38 |
67 |
9 |
66,05 |
54 |
10 |
68,73 |
Регресійний аналіз дозволяє робити перспективну оцінку віддаленішого майбутнього. В табл.10 продемонстровані можливості функції ТЕНДЕНЦИЯ по складанню перспективного прогнозу, що поширюється за межі даних останнього фактичного спостереження. Якщо застосувати функцію
= ТЕНДЕНЦИЯ(А1:А10;В1:В10;B11),
де перший аргумент - А1:А10 - визначає дані спостережень базової лінії (известные – значения - у); другий аргумент - В1:В10 - визначає часові моменти, в які ці дані були отримані (известные – значения - х), то значення 11 в комірці В11 є новое – значение -х і визначає час, що зв'язується з перспективною оцінкою. Отримане значення 71,4 є прогнозом на основі фактичних даних на одинадцятий часовий відлік, який ще не настав.
Рис 5. Лінійний регресійний аналіз (функція ТЕНДЕНЦИЯ)
Таблиця 10
Лінійний прогноз значення періоду, що виходить за межі базової лінії
Обсяг продажу |
Дні |
Прогноз |
45 |
1 |
44,67 |
34 |
2 |
47,35 |
71 |
3 |
50,02 |
56 |
4 |
52,69 |
39 |
5 |
55,36 |
45 |
6 |
58,04 |
67 |
7 |
60,71 |
89 |
8 |
63,38 |
67 |
9 |
66,05 |
54 |
10 |
68,73 |
|
11 |
71,4 |
Крім того, існує можливість одночасного прогнозування даних для декількох нових часових моментів.
У нашому прикладі, якщо ввести числа 11-13 в комірки В11:В13, а потім виділити комірки С11:С13 і ввести за допомогою формули масиву наступне:
= ТЕНДЕНЦИЯ(А1:А10;В1:В10),
Ехсеl поверне в комірку С11 прогноз на часовий момент 11 – 71,4. Щоб одержати прогноз на весь діапазон, треба перейти в рядок формул і натиснути Ctrl+Shift+Enter. Даний прогноз буде базуватися на зв'язку між даними спостережень базової лінії діапазону А1:А10 і часовими моментами базової лінії з 1 по 10, зазначеними в комірках В1:В10 (табл.11).
Таблиця 11
Одночасне лінійне прогнозування даних трьох часових моментів
Обсяг продажу |
Дні |
Прогноз
|
45 |
1 |
44,67 |
34 |
2 |
47,35 |
71 |
3 |
50,02 |
56 |
4 |
52,69 |
39 |
5 |
55,36 |
45 |
6 |
58,04 |
67 |
7 |
60,71 |
89 |
8 |
63,38 |
67 |
9 |
66,05 |
54 |
10 |
68,73 |
|
11 |
71,40 |
|
12 |
74,07 |
|
13 |
76,75 |
2.2.Завдання
Фірма бажає придбати земельну ділянку в липні, на початку наступного фінансового року. Фірма збирає інформацію про ціни за останні 12 місяців на типову земельну ділянку (табл.12).
Яка очікувана ціна ділянки в липні?
Побудувати лінійний графік і відповісти на наступні запитання:
Яку залежність між даними ми отримуємо за допомогою функції ТЕНДЕНЦИЯ?
Поясніть основні принципи лінійного регресійного аналізу.
Які ще функції в MS Excel застосовуються для лінійного прогнозування?
Таблиця 12
Варіанти завдань
Період |
Місяць |
Ціна, тис. у.о. |
| |||||||||
№ варіанта |
| |||||||||||
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 | |||
1 |
Січень |
1234 |
2234 |
2034 |
1255 |
5234 |
2237 |
2034 |
1255 |
4034 |
1255 | |
2 |
Лютий |
1256 |
2256 |
2156 |
1279 |
5256 |
2256 |
2156 |
1279 |
4156 |
1279 | |
3 |
Березень |
1390 |
2390 |
2290 |
1399 |
5390 |
2691 |
2290 |
1399 |
4490 |
1499 | |
4 |
Квітень |
1412 |
2412 |
2212 |
1412 |
6451 |
2712 |
2212 |
1412 |
5014 |
1512 | |
5 |
Травень |
1434 |
2434 |
2334 |
1434 |
6434 |
2737 |
2334 |
1434 |
5334 |
1535 | |
6 |
Червень |
1456 |
2456 |
2456 |
1488 |
6456 |
2756 |
2456 |
1499 |
5456 |
1588 | |
7 |
Липень |
1490 |
2490 |
2590 |
1500 |
7490 |
2791 |
2590 |
1500 |
5590 |
1600 | |
8 |
Серпень |
1412 |
2412 |
2612 |
1512 |
7452 |
2882 |
2612 |
1512 |
6614 |
1612 | |
9 |
Вересень |
1500 |
2500 |
2700 |
1600 |
7500 |
2911 |
2700 |
1600 |
6700 |
1700 | |
10 |
Жовтень |
1623 |
2623 |
2823 |
1623 |
8613 |
3123 |
2823 |
1623 |
6843 |
1723 | |
11 |
Листопад |
1689 |
2689 |
2989 |
1789 |
8689 |
3689 |
2989 |
1799 |
6989 |
1889 | |
12 |
Грудень |
1710 |
2710 |
3310 |
1810 |
9710 |
3711 |
3310 |
1910 |
7310 |
1910 |
Нелінійний прогноз
Функція ТЕНДЕНЦИЯ обчислює прогнози, що грунтуються на лінійному зв'язку між результатом спостереження і часом, у який це спостереження було зафіксовано.
Існує велика кількість типів даних, що змінюються в часі нелінійним способом. Деякими прикладами таких даних є обсяг продажу нової продукції, приріст населення, виплати по основному кредиту і коефіцієнт питомого прибутку.
У подібних випадках нелінійного взаємозв'язку застосовується нелінійний регресійний аналіз.
Нелінійний регресійний аналіз ґрунтується на застосуванні наступної формули для розрахункового (згладженого) значення:
.
В MS Excel для реалізації цього методу застосовується функція РОСТ.
Її синтаксичний опис такий:
РОСТ(Изв_знач_y, Изв_знач_х, Новые_знач_х, Константа)
Функція повертає значення y для послідовності нових значень x відповідно до нелінійної апроксимації за методом найменших квадратів.
Известные_значения_y - це множина значень y, що уже відомі для співвідношення y = b*m^x.
Известные_значения_x - це необов'язкова множина значень x, що уже відомі для співвідношення y = b*m^x.
Новые_значения_x - це нові значення x, для яких РОСТ повертає відповідні значення y.
Константа- це логічне значення, що вказує, чи потрібно, щоб константа b дорівнювала 1.
Якщо константа має значення ИСТИНА чи опущена, то b обчислюється звичайним чином.
Якщо константа має значення ЛОЖЬ, то b покладається рівним 1, а значення m підбираються так, щоб y = m^x.
Приклад. В таблиці 13 наводяться дані про продаж товару.
Таблиця 13
Фактичні дані
Факт |
1 |
1 |
4 |
5 |
10 |
19 |
34 |
55 |
84 |
120 |
Тижні |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
Побудуємо нелінійний прогноз продажу товару.
Розмістимо дані про фактичний продаж в комірках А2:А11, часові проміжки (тижні) – в комірках В2:В11. В комірку С2 вводимо формулу РОСТ(А2:А11;В2:В11), натискуємо Enter. Потім виділяємо весь діапазон С2:С11, переходимо в рядок формул і натискуємо Ctrl+Shift+Enter. Одержимо прогнозні дані в комірках С2:С11 (табл.14).
Продовжимо прогноз на тижні 11-13. Вихідна таблиця тепер має вид (табл.15).
Виділяємо С12:С14. Вводимо РОСТ(А2:А11;В2:В11;В12:В14). Одержуємо прогнозне значення тільки в С12. Щоб одержати прогноз в двох інших комірках, переходимо в рядок формул і натискуємо Ctrl+Shift+Enter. Результат розрахунку наведено в таблиці 16.
Таблиця 14 |
Таблиця 15 |
Таблиця 16 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Результати нелінійного прогнозування
|
Нелінійне прогнозування на три періоди наперед |
Результати нелінійного прогнозування на три періоди наперед | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Факт Тижні Прогноз 1 1 0,95 1 2 1,67 4 3 2,95 5 4 5,20 10 5 9,17 19 6 16,17 34 7 28,51 55 8 50,28 84 9 88,67 120 10 156,37 |
Факт Тижні Прогноз 1 1 0,95 1 2 1,67 4 3 2,95 5 4 5,20 10 5 9,17 19 6 16,17 34 7 28,51 55 8 50,28 84 9 88,67 120 10 156,37
11
12
С12:С14
13
|
Факт Тижні Прогноз 1 1 0,95 1 2 1,67 4 3 2,95 5 4 5,20 10 5 9,17 19 6 16,17 34 7 28,51 55 8 50,28 84 9 88,67 120 10 156,37
11 275,75
12 486,28
13 857,54 |
Побудуємо графік нелінійного прогнозу обсягу продажу (рис. 6).
Рис.6. Нелінійний прогноз продажу (функція РОСТ)
Застосуємо тепер до даних (табл.13) функцію ТЕНДЕНЦИЯ.
В табл.17 і на рис.7 наведено прогнозні результати, отримані за допомогою функцій РОСТ і ТЕНДЕНЦИЯ.
Бачимо, що для даної базової лінії функція РОСТ дає набагато точніший прогноз, ніж функція ТЕНДЕНЦИЯ.
Таблиця 17
Прогноз
Факт |
Тижні |
РОСТ |
ТЕНДЕНЦИЯ
| |
1 |
1 |
0,95 |
21,33 | |
1 |
2 |
1,67 |
9,19 | |
4 |
3 |
2,95 |
2,95 | |
5 |
4 |
5,20 |
15,09 | |
10 |
5 |
9,17 |
27,23 | |
19 |
6 |
16,17 |
39,37 | |
34 |
7 |
28,51 |
51,51 | |
55 |
8 |
50,28 |
63,65 | |
84 |
9 |
88,67 |
75,79 | |
120 |
10 |
156,37 |
87,93 | |
|
11 |
275,75 |
100,07 | |
|
12 |
486,28 |
112,21 | |
|
13 |
857,54 |
124,35 |
Рис.7. Прогноз з допомогою функцій РОСТ і ТЕНДЕНЦИЯ
2.4.Завдання
В таблиці 18 наведено дані продажу деяких продовольчих товарів у 2006 році.
Необхідно виконати прогнозування продажу товарів на перші 3 місяці 2007 року за допомогою функцій ТЕНДЕНЦИЯ і РОСТ. Провести порівняльний аналіз отриманих результатів.
Побудувати лінійні графіки і відповісти на наступні запитання:
Яку залежність між даними ми отримуємо за допомогою функції РОСТ?
Поясніть основні принципи нелінійного регресійного аналізу.
Таблиця 18