Лабораторна робота №2, Варіант №16 - Дослідження трендів для здійснення короткострокового прогнозування витрат сировини у наступному періоді

« Назад

1. Визначити, який тренд найкращий для здійснення короткострокового прогнозування витрат сировини у наступному періоді. Побудувати діаграму «Вставка» - «Диаграмма» - «Мастер диаграмм» (шаг 1 из 4) (рис. 1).

 

Рис. 1. Побудована діаграма

Добавити  лінію тренда: права кнопка миші «Добавить линию тренда» - у вікні «Тип» - «Линейная»  – у вікні «Параметри» - поставити відмітку « показывать уравнение на диаграмме» - поставити відмітку «поместить на диаграмму величину достоверности апроксимации (R²)» (рис. 2).

 

Рис. 2. Діалогове вікно «Линия тренда»

За отриманими результатами результатами (рис. 3) заповнити таблицю в Excel (табл. 1).

 

Рис. 3. Побудована лінійна лінія тренда

Таблиця 1

Основні показники тренда

Найкраще для проведення короткострокового прогнозу підходить те рівняння тренда, де коефіцієнт детермінації має найбільше значення, тобто чим ближче він прямує до одиниці, тим кращий зв'язок. У нашому випадку найкращий для прогнозу експоненційний тренд, найгірший – логарифмічний. Підставивши значення у отримані рівняння, ми зможемо зробити прогноз на наступні періоди.

2. Знайдемо рівняння експоненційного тренда:  за допомогою функцій «ЛГРФПРИБЛ» (рис. 4).

 

Рис. 4. Діалогове вікно «Мастер функцій – шаг 1 из 2»

Для запису формули (рис. 5) необхідно спочатку виділити комірки розміром 1 (рядок) на 2 (стовпчика).

 

Рис. 5. Діалогове вікно « Аргументы функции»

Отриманий результат дає змогу розрахувати прогнозне значення експоненційного тренду за будь-який період шляхом підставлення порядкового номера наступного періоду у рівняння. Для цього необхідно використати функцію СТЕПЕНЬ: «Вставка» - «Функція» - «Категорія» - «Математические» - СТЕПЕНЬ – F2-Ctrl + Shift + Enter.

Запишемо розраховані прогнози у табл. 2.

Таблиця 2

Розраховані прогнозні значення вартості сировини

2. Для прогнозування використовують згладжування рядів динаміки, а саме згладжування за допомогою ковзної середньої та експоненційне згладжування.

- Порядок використання ковзної середньої: «Сервис» - «Анализ данных» - «Скользящее среднее» (рис. 6).

 

Рис. 6. Діалогове вікно « Анализ данных»

Стан діалогового вікна, що рекомендується, зображений на рис. 7.

 

Рис. 7. Діалогове вікно « Скользящее среднее»

Натисніть кнопку «Ок».

Якщо розмір згладжування є парним числом (p = 2m), розраховане згладжене середнє значення не можна віднести до конкретного рівня часу (табл. 3), тому необхідно застосувати процедуру центрування.

Таблиця 3.

Значення згладжених рівнів та стандартних похибок

Отримані результати подані у формі числових значень (табл. 3) і зображені графічно (рис. 8).

 

Рис. 8. Графік ковзної середньої

У нашому випадку p = 4, тому застосуємо центрування за допомогою функції «СРЗНАЧ» (рис. 9).

 

Рис. 9. Діалогове вікно «Мастер функцій – шаг 1 из 2»

Визначається середнє значення між першим і другим, другим і третім рівнями і т.д. (табл. 4).

Таблиця 4.

Значення згладжених рівнів та центрованих згладжених рівнів

Отримані значення нових згладжених рівнів подано в табл. 4, а скоригований графік ковзного середнього – на рис. 10.

 

Рис. 10. Графік згладжування ковзної середньої

- Порядок використання експоненційного згладжування: «Сервис» - «Анализ данных» - «Экспоненциальное сглаживание» (рис. 11).

 

Рис. 11. Діалогове вікно «Анализ данных»

 

Рис. 12. Графік експоненціального згладжування

Отримані значення згладжених рівнів у числовому виразі подано в табл. 5.

Таблиця 5.

Значення згладжених рівнів та стандартних похибок

Розраховані згладжені рівні не тільки дають уявлення про загальну тенденцію поведінки часового ряду, що вивчається, але можуть використовуватися і для розрахунку індексів сезонності. Метод простого експоненційного згладжування, на відміну від методу простої ковзної середньої, зберігає дрібні коливання.

Прийняття управлінських рішень після отриманих результатів моделювання полягають у такому: прогнозні значення вартості сировини у наступні три місяці становитимуть близько 4583,2; 4957,8; 5363,03 грн., тому що коефіцієнт детермінації має найбільше значення у рівнянні експоненційного тренда, а саме: R² = 0,5104, що вказує на середній зв'язок. Однак потрібно зазначити таке:

1) нелінійні моделі, до яких, зокрема, відносять і розглянуту модель, важко пояснювати;

2) крім того, прогноз на майбутні періоди, що отримується, в більшості випадків має тенденцію до значень, які відрізняються від істинних показників процесу;

3) звідки випливає, що прогноз за нелінійними моделями можна використовувати дуже обережно і лише в короткостроковому періоді.