Побудова детермінірованої частини прогнозуючої моделі ЧР (етап 1)

А) Ввести вхідні дані ЧР (не менше 20 чисел) в стовбчик A першого листа програми Excel, як показано на малюнку 1.

Б). Допустимо, що вхідний числовий ряд описується виразом (3.4). Для побудови параболічної залежності необхідно в стовбчик B ввести нумерацію елементів ЧР t, а в стовбчик С квадрат t, тобто t² (для одержання моделі поліноміальної залежності третього ступеню в слідуючий стовбчик уводяться дані t³, четвертого ступеню – t⁴ т.д.).

В). Для вичислення коефіцієнтів моделі і допоміжних результатів статистики в правій частині екрану з допомогою лівої кнопки миші виділити область пустих ячеєк розміром 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.032 t + 0.0023 t². (3.9)

 

Е). Розрахувати модельні значення y(t) в діапазоні t=1-20, підставляючи в одержане рівняння значення t і t². Всі дані в таблиці повинні бути відцентровані, дробні числа округлені до третього знаку після коми. Результати розрахунків прикладу представлені на рисуноку 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 – Таблиця часових рядів

 

 

Лабораторна робота № 4