3.2. Порядок роботи
3.2.1. Побудова детермінірованої частини прогнозуючої моделі чр (етап 1)
А) Ввести вхідні дані ЧР (не менше 20 чисел) в стовбчик A першого листа програми Excel, як показано на малюнку 1.
Б). Допустимо, що вхідний числовий ряд описується виразом (3.4). Для побудови параболічної залежності необхідно в стовбчик B ввести нумерацію елементів ЧР t, а в стовбчик С квадрат t, тобто t2 (для одержання моделі поліноміальної залежності третього ступеню в слідуючий стовбчик уводяться дані t3, четвертого ступеню – t4 т.д.).
В). Для вичислення коефіцієнтів моделі і допоміжних результатів статистики в правій частині екрану з допомогою лівої кнопки миші виділити область пустих ячеєк розміром 5х3 (5 рядків і 3 стовбчика, кількість рядків повинно відповідати кількості оцінюючих коэфіцієнтів). Для одержання тільки оцінок коэфіцієнтів регресії виділити область розміром 1х3;
Г). Активізувати режим обчислення коефіциєнтів рівняння регресії в слідуючему порядку: “Вставка – Функция – Статистические -Линейн.-Ок”;
Д). У вікні, що з`явилося, увести слідуючі вхідні дані:
- відомі значення у – діапазон, який містить дані про об`єкт (виділити мишою стовбчик даних ЧР);
- відомі значення x – діапазон, який містить дані часу і квадрату часу (виділити стовбчики B и C);
-Константа – логічне значення, яке вказує на наявність або на відсутність вільного члена в рівнянні 3.6 (якщо вставити “1”, то вільний член a0 розраховується, якщо -“0”, то вільний член дорівнює 0;
-Статистика – логічне значення, яке вказує, виводити додаткову інформацію по регресивному аналізу чи ні.
Щоб розкрити таблицю коэфіцієнтів моделі, потрібно натиснути одночасно на комбінацію клавіш <CTRL>+<SHIFT>+<ENTER>.
Для уведених вхідних даних: a0= 4.2828, а1= -0.032, а2= 0.0023.
Пошукове рівняння регресії детермінованої частини моделі виглядить слідуючим чином:
y(t) = 4.283− 0.032t + 0.0023t2 . (3.9)
Е). Розрахувати модельні значення y(t) в діапазоні t=1-20, підставляючи в одержане рівняння значення t і t2. Всі дані в таблиці повинні бути відцентровані, дробні числа округлені до третього знаку після коми. Результати розрахунків прикладу представлені на рисуноку 1 в стовбчику D (Yпр1).
Ж). Використовуючи графічні інструменти Excel, побудувати графіки вхідного ряду і ряду, розрахованого по вираженню (3.9). Рисунок повинен мати названня, відформатований по ширині листа, осі графіків повинні бути позначені. На рисунку 3.1 ці графіки позначені відповідно Y і Yпр1. Порівняти схожість графіків. Якщо вони сильно відрізняються, то можлива помилка в розрахунках.
З). Розрахувати прогнозні оцінки ЧР на моменти часу t=21; t=22; t=23.
Побудувати графік модельних даних для t=1,2,3,...,23. (рисунок 3.1“б”).
3.2.2. Побудова стохастичної частини моделі ЧР (етап 2).
А). Для кожного спостереження ряду в стовбчику E розрахувати відхилення ε(t),
як різницю між відповідними даними стовбчиків A і D так, як показано на рисунку 3.2”а”
Б). Для визначення коефіцієнту b1 рівняння (3.9) розмістим в розрахованій таблиці дані випадкової компоненти так, як показано в стовбчику F на рисунку 3.2“а”.
а)
б)
Рис. 3.1 – Розрахункові дані (“а”) і графіки
детермінованої частини (“б”) прогнозуючої моделі ЧР
В). Визначим коефіцієнт b1 моделі авторегресії, для цього повторити пункты В-Г розділу 3.2.1. з урахуванням того, що в даному разі визначаються коефіцієнти рівняння першого порядку. У вікно вхідных даних вставити слідуючі значення:
-Відомі значення у – виділити мишою діапазон ячеек E3-E21;
- Відомі значення x– виділити мишою діапазон ячеек F3-F21.
У ячейці I9 представлено розраховане значення коефіцієнта b1= 0.6257.
В результаті розрахунків методом найменьших квадратів рівняння авторегресії першого порядку має вид:
ε (t) = 0.6257ε (t −1). (3.10)
Рівняння (3.10) побудовано без вільного члена b0.
Г). В стовбчику G розрахункової таблиці (рис.2 “а”) по вираженню (3.10) розрахувати модельні значення випадкової компоненти для t=2,3,4,...,21.
Д). Використовуючи вираз (3.10), в ячійках G23-G25 розрахувати прогнозні значення випадкової компоненти для t=22,23,24. При обчисленні ε(22) у ячійці G23 використувати значення ε(21) із ячійки G22, при обчисленні ε(23) у ячійці G24 використувати значення ε(22) із ячійки G24 і так далі.
3.2.3. Розрахунок оцінок повного прогнозу (етап 3) проводиться по виразу (3.1) для t=21,22,23,24 у ячійках H22-H25 по даним ячійок D22 і G22, D23 і G23, D24 і G24, D25 і G25. По результатам розрахунків, представлених в колонках A,D і H побудувати графіки вхідного ЧР, прогнозу на основі детермінірованої моделі графіка оцінок прогнозу з урахуванням випадкової компоненти. На рисунку 3.2”б” для вибраного прикладу ці графіки позначені як Y,Yпр1 і Yпр2. Як видно із рисунка, графік Yпр2 більше близький до графіку Y, що свідчить про підвищення точності прогнозних оцінок при участі випадкової компоненти. Дати аналіз графіків, одержаних в результаті виконання заданого варіанту.
3.3. Контрольні питання
1. Привести приклади економічних і технічних задач, де потрібні прогнозні оцінки.
2. Дати характеристику моделі прогнозу.
3. Як вибираються моделі детермінірованої і стохастичної складових прогнозу?
4. Описати процес визначення коефіциентів моделі в середовищі Exсel.
5. Чим відрізняються процедури інтерполяції і екстраполяції ЧР?
а)
б)
Рис. 3.2 – Розрахункові дані (“а”) і графіки (“б”) повного прогнозу ЧР
3.4. Варіанти завдань
В таблиці 3.1 представлені дані часових рядів для прогнозуванния.
Табл. 3.1 – Таблиця часових рядів
