Завдання 1

Лабораторна робота №9. Аналіз тенденцій розвитку та коливань

Мета – придбати навички використання екстраполяції та інтерполяції в рядах динаміки, побудови й аналізу моделей за допомогою MS Excel.

Завдання – використовуючи MS Excel необхідно знайти рівні динамічного ряду за допомогою екстраполяції. Побудувати прогноз та знайти в яких межах буде знаходитись прогнозне значення. Побудувати різні моделі динаміки за даними часового ряду.

Методичні рекомендації

Завдання 1

Маємо дані про обсяг реалізованої продукції на підприємстві за 1997-2009 рр. (табл. 9.1). Необхідно визначити за допомогою аналітичного вирівнювання прогнозне значення ВВП у 2010 р.

Таблиця 9.1

Обсяг ВВП за 1998-2009 рр.

На Листі 2 стовбець А - В будуть стовпцями вихідних даних.

Для побудови лінії тренда необхідно виділити часовий ряд та вибрати в контекстному меню команду «Добавити лінію тренда»
(рис. 9.1).

Рис. 9.1. Меню для побудови тренду

Після вибору команди відкриється діалогове вікно (рис. 9.2), в якому вибираємо тип, параметри побудови тренду.

Рис. 9.2. Діалогове вікно: лінія тренда

Результат побудови лінійного тренду представлено на рис. 9.3.

Рис. 9.3. Результати розрахунку параметрів тренду

Таким чином, рівняння прямої має вигляд: У= 186, 36+7, 4825*t.

Розрахувати обсяг реалізованої продукції в 2010 році можна:

1.Точковий прогноз. Використовуючи рівняння прямої, де t- це умовний показник часу на який буде будуватись прогноз. Тобто обсяг реалізованої продукції в 2010 році очікується на рівні:

У= 186, 36+7, 4825*t; t=13; У=283, 63 млн. грн.

2. Використовуючи стандартну функцію ПРЕДСКАЗ. Після вибору даної функції відкривається діалогове вікно (рис. 9.4), в якому задаємо параметри.

Рис. 9.4. Діалогове вікно: аргументи функції ПРЕДСКАЗ

Результат функції ПРЕДСКАЗ представлено на рис. 9.5.

Рис. 9.5. Результат функції ПРЕДСКАЗ

3. Використовуючи стандартну функцію ТЕНДЕНЦИЯ. Після вибору даної функції відкривається діалогове вікно (рис. 9.6), в якому задаємо параметри.

Рис. 9.6. Діалогове вікно: аргумент функції ТЕНДЕНЦИЯ

Результат функції ТЕНДЕНЦИЯ представлено на рис. 9.7.

Рис. 9.7. Результат функції ТЕНДЕНЦИЯ

Визначимо межі обсягу ВВП у 2010 р.: .

Розрахунок критерію Стьюдента за допомогою вбудованої функції СТЬЮДРАСПОБР (рис. 9.8) при довірчій імовірності 0, 95.

Розрахунок критерію Стьюдента: в ячейці В21 вводимо формулу =СТЬЮДРАСПОБР(0, 05; (СЧЁТ(B3: B14)-СЧЁТ(B17: B18))). Тоді при довірчій імовірності 0, 95 t₂ = 2, 23.

Розрахунок меж прогнозного значення обсягу ВВП в 2010 р.: в ячейці В22 вводимо формулу =B19-B20*B21; в ячейці В23 вводимо формулу =B19+B20*B21.

Таким чином, прогнозне значення обсягу ВВП в 2010 р. буде знаходиться у межах:

283, 64-2, 23*9, 45 ≤ у_пр≤ 283, 64+2, 23*9, 45;

262, 55 ≤ у_пр≤ 304, 72 млрд.грн.

Перевіримо адекватність тренду за допомогою критерію Фішера. В ячейці І3 вводимо формулу =(B3-$B$17)^2. Розтягуємо цю формулу на весь стовбець. По стовпцю розраховуємо суму.

залишкова дисперсія: в ячейці В25 вводимо формулу =СРЗНАЧ(H3: H14).

загальна дисперсія: в ячейці В26 вводимо формулу =СРЗНАЧ(I3: I14).

теоретичний коефіцієнт детермінації: в ячейці В27 вводимо формулу =1-B25/B26.

критерій Фішера (F): в ячейці В28 вводимо формулу =(B27/(1-B27))*(СЧЁТ(B3: B14)-2)/(2-1).

=19, 39;

> .

Кінцевий вид робочого листа для завдання 2 представлено
на рис. 9.8.

Параметри моделі можна інтерпретувати в такий спосіб:

параметр тренду а₀ показує обсяг прибутку у 2003 р. шостий місяць, коли t=0, а₁=7, 48 - показник сили зв'язку, що означає, що щороку обсяг ВВП зростав на 7, 48 млрд.грн.

Прогнозне значення обсягу ВВП у 2010 р. буде знаходитись в межах від 262, 55 до 304, 72 млрд.грн. Це можна стверджувати з імовірністю 95%.

Значення коефіцієнта детермінації = 0, 9 та дисперсійного критерію F(2, 10) = 89, 59 свідчать про адекватність моделі.

Рис. 9.8. Розрахунок прогнозного значення обсягу ВВП у 2010 р. за
допомогою аналітичного вирівнювання