На головну

Завдання аналізу та моделювання часових рядів

завдання 1. На підставі інформації про діяльність фірми протягом 9 років (табл. 1) провести наступні дії з використанням ППП Excel:

- Визначити наявність тренда Yp(T);

- Побудувати лінійну модель Yp(T) = a0 + a1xt, параметри якої оцінити за методом найменших квадратів (МНК);

- Побудувати точковий та інтервальний прогнози трудомісткості виробництва продукції на два роки вперед.

Таблиця 1. Відомості про діяльність фірми

 Поточний номер року, t
 Трудомісткість продукції, yt  8,8  9,2  8,4  7,8  8,1  7,4  6,7  5,8

Рішення. Для вирішення даного завдання виконаємо наступний алгоритм:

10. Визначимо наявність тренда по методу Тінтнера, для чого в стовпці А і В занесемо вихідні дані (рис. 1).

Рис.1. Вікно - вихідна таблиця

20. Перевіримо гіпотезу про рівність дисперсій за допомогою F-тесту, який можна знайти в арсеналі аналізу даних (Рис. 2)

Мал. 2. Вікно «Аналіз даних»

30. Ввести дані для виконання F-тесту, вказавши інтервал для першої і другої змінних (рис. 3). Результат виконання тесту наведено на рис. 4. Аналізуючи результати виконання двухвиборочного F-тесту для перевірки гіпотези про рівність дисперсій, робимо висновок, що дисперсії розрізняються незначно.

Мал. 3. Діалогове вікно

Мал. 4. Розрахункові результати аналізу методу Тінтнера

40. Вибрати інструмент аналізу Двухвиборочний t-тест з однаковими дисперсіями (Рис. 5) і ввести дані (рис. 6). Результат виконання t-тесту дано на рис.7. Аналізуючи тест, видно, що тренд існує.

50. Побудова лінійної моделі виду yt = a0 + a1t.

Визначимо параметри моделі yt за методом МНК за допомогою надбудови аналіз даних. Для регресійного аналізу необхідно:

- Вибрати команду Сервіс > Аналіз даних.У вікні аналіз даних вибрати інструмент регресія, А потім натиснути кнопку ОК(Рис.8);

Мал. 5. Діалогове вікно

Рис.6. Діалогове вікно

Мал. 7. Результати тесту

Мал. 8. Діалогове вікно

Мал. 9. Діалогове вікно

- у вікні регресія в полі Вхідний інтервал Y ввести адресу одного діапазону комірок, який представляє залежну змінну. В полі Вхідний інтервал X ввести адресу діапазону, який містить значення незалежної змінної t. Якщо виділені і заголовки стовпців, встановити прапорець Мітки в першому рядку;

- Встановити вихідні параметри (в даному прикладі - Новий робочий лист); в полі Графік підбору поставити прапорець; в полі залишкипоставити необхідні прапорці та натиснути кнопу ОК (рис. 9).

Результат регресійного аналізу отримаємо у вигляді, наведеному на рис. 10. У другому стовпці таблиці рис. 10 знаходяться коефіцієнти рівняння регресії а0 = 10,00476; а1. = -0,469047.

Мал. 10. Результат регресійного аналізу

Рівняння регресії має такий вигляд: Yt = 10 - 0,47t.

60. Проведемо оцінку параметрів лінійної моделі вручну (рис. 11).

Мал. 11. Проміжні розрахунки даних лінійної моделі

В результаті розрахунків отримуємо приблизно аналогічні результати.

Іноді для перевірки розрахунків корисно перевірити формули. Для цього слід вибрати команду Сервіс > Параметри і поставити прапорець у вікні формули (рис. 12). Після цього на аркуші Excel розрахункові значення будуть замінені відповідними формулами і функціями (рис. 13).

Мал. 12. Діалогове вікно

Мал. 13. Програма на Excel.

70. Побудова точкового та інтервального прогнозу на 2 кроки вперед.

Для обчислення точкового прогнозу в побудовану модель підставляємо відповідні значення фактора t = n + k: прогн (n+k) = а0 + а1(N + k). Тоді отримаємо:  = 10 - 0,47 * 10 = 5,3;  = 10 - 0,47 * 11 = 4,83.

Для обчислення інтервального прогнозу розрахуємо довірчий інтервал. Ширину довірчого інтервалу розрахуємо за формулою:

Yn+L ( n+L - сКр; n+L + сКр),

де с візьмемо з отриманого протоколу регресійного аналізу.

с = = =  = 0,35.

Таким чином, інтервал дорівнює: сКр = 0,35 * 1,05 = 0,3675.

Далі обчислимо верхню і нижню межі прогнозу:

y10  (5,3 - 0,3675 = 4,9325; 5,3 + 0,3675 = 5,6675),

y11  (4,83 - 0,3675 = 4,4625; 4,83 + 0,3675 = 5,1975).

Мал. 14. Графік фактичного часового ряду і його лінійної моделі

 



Завдання оптимізації в маркетингу і їх рішення на основі ППП Excel | Рішення задач оптимізації на основі авторських програм
© um.co.ua - учбові матеріали та реферати