Лабораторна робота № 11
Тема: Прогнозування і перспективні оцінки
Мета: Ознайомитись з основними методами прогнозування і перспективних оцінок
План заняття
1. Прогнози з застосуванням методу ковзного середнього
2. Створення лінійних прогнозів з застосуванням функції ТЕНДЕНЦИЯ
3. Створення нелінійних прогнозів з застосуванням функції РОСТ
4. Прогнозування з використанням функції експоненціального згладжування
Теоретичні відомості Прогнозування і перспективні оцінки.
Для оцінки будь-яких показників на основі минулих періодів використовуються методи прогнозування. При цьому виникає необхідність в оцінці деяких кількісних характеристик, наприклад розподіл витрат і кількість службовців і т.д. Перед тим як розпочати прогнозування необхідно переконатись у відповідності показників за минулі періоди. Тобто базова лінія, що виражає цей ряд, відповідає чотирьом важливим характеристикам.
Базова лінія включає в себе результати спостережень - починаючи самими ранніми і закінчуючи останніми.
Всі часові періоди базової лінії мають однакову тривалість. Не потрібно змішувати дані, наприклад, за один день з середнім показником. На практиці незначне відхилення можна ігнорувати. Наприклад, в лютому і березні різна кількість днів, однак ця незначна різниця в два-три дні зазвичай не враховується в базових лініях. Побудованих на основі щомісячних результатів спостережень.
Спостереження фіксуються в той самий момент кожного часового періоду. Наприклад, при складанні базової лінії на основі щотижневих результатів, необхідно фіксувати дані тільки в один і той же день неділі.
Пропуск даних не допускається. Пропуск навіть одного результату спостереження небажаний при прогнозуванні, тому, якщо у дослідженнях відсутні результати навіть за незначний відрізок часу, бажано поповнити їх хоча б приблизними даними.
Прогнози з застосуванням методу ковзного середнього.
Метод ковзного середнього застосовувати не складно, однак він занадто простий для створення точного прогнозу. При використанні цього методу прогноз будь-якого періоду являє собою не що інше, як отримання середнього показника декількох результатів часового ряду. Наприклад, якщо вибрати ковзне середнє за три місяці, прогноз на травень буде середнє значення показника за лютий, березень, квітень. Вибравши в якості методу прогнозування ковзне середнє за чотири місяці, можна оцінити травневі показники як середнє значення показника за січень, лютий, березень, квітень. Розглянемо цей процес за на прикладі. Створіть в робочий книзі Excel новий робочий лист і перейменуйте його на “Прогноз”. Введіть в цей лист наступні дані:
Для визначення тенденції показника використаємо ковзним середнім за три періоди. Чому за три періоди? Відповідь на це питання така: ковзне середнє за менший період може не відобразити тенденцію, а за більш довший період занадто згладить її. Таким чином, щоб отримати ковзне середнє показника за три періоди в комірку C5 вводимо :
=СРЗНАЧ(B2:B4)
Далі використавши засоби Автозаполнение копіюємо і вставляємо цю формулу в комірки - С6:С12. Як видно з отриманих даних, показник середнього має тенденцію до збільшення.
Іншим способом застосування ковзного середнього є використання надбудови Пакет анализа. Для цього виконайте дії в наступній послідовності:
Виберіть команду Сервис > Анализ данных…
У діалоговому вікні Анализ данных із списку доступних функцій аналізу вибираємо - Скользящее среднее і натискаємо кнопку OK.
З’явиться діалогове вікно Скользящее среднее. В полі Входной интервал вказуємо діапазон робочого листа з даними про базову лінію.
В полі Интервал вводимо кількість періодів, яку буде використано при підрахунку ковзного середнього.
В полі Выходной интервал введіть адресу комірки, з якої хочете почати вивід.
Клацніть по копці OK.
Отримаємо:
Значення ковзного середнього починаються зі значень #Н/Д. Це результат недостатньої кількості даних для обчислення середнього значення декількох перших результатів спостереження. Можна помітити, що Выходной интервал починається з D3, а не з D2. Це через те, що не логічно розглядати деяке число в якості прогнозу на період, на основі якого створювався цей прогноз. Тобто якби вихідний інтервал починався з D2, то прогноз на 4-й період, записаний в D5, був би побудований на основі 2, 3, 4-го періодів, що записані в B3:B5. У випадку коли вихідний інтервал починається з D3, прогноз на 4-й період, записаний в D5, будується на основі 1, 2, 3-го періодів, що записані в B2:B4.
Створення лінійних прогнозів з застосуванням функції ТЕНДЕНЦИЯ.
Самий простий спосіб обчислення регресійного аналізу - використання статистичної функції ТЕНДЕНЦИЯ. Обрахуємо для нашого прикладу, що розташований на робочому листу Прогноз приклад. В комірку E1 вводимо текст - Тенденція. Виділяємо E2:E11, в рядок формул записуємо:
=ТЕНДЕНЦИЯ(B2:B11;A2:A11).
Не знімаючи виділення з діапазону E2:E11, і перебуваючи в рядку формул, натискаємо <Ctrl+Shift+Enter> - це необхідно для введення формули масиву. Відомо, що регресійний аналіз дозволяє проводити перспективну оцінку на майбутнє. Для цього, щоб це зробити за допомогою функції ТЕНДЕНЦИЯ хоча б на два періоди, а саме на 11 і 12, виділяємо E12:E13, записуємо формулу масиву:
=ТЕНДЕНЦИЯ(B2:B11;A2:A11;A12:A13)
Отримаємо:
Створення нелінійних прогнозів з застосуванням функції РОСТ.
Застосовування функції РОСТ краще, на відміну від ТЕНДЕНЦИЯ, для відображення і прогнозування базової лінії, що різко вигинається в одному з напрямків. Тобто залежність є не лінійною, а скоріше експоненціальною.
Застосуємо функцію РОСТ до прикладу. В комірці F1 Записуємо текст – Ріст. Для комірок F2:F11 записуємо формулу як при використанні функції ТЕНДЕНЦИЯ: =РОСТ(B2:B11;A2:A11). Для того, щоб скласти прогноз на 11 і 12-ий періоди, для цих двох комірок записують формулу масиву:
=РОСТ(B2:B11;A2:A11;A12:A13)
Отримаємо:
Прогнозування з використанням функції експоненціального згладжування.
Згладжування – це спосіб, що забезпечує швидке реагування прогнозу на всі події, які відбуваються на протязі періоду довжини базової лінії. Методи основані на регресії, такі як функції ТЕНДЕНЦІЯ і РОСТ, застосовують до всіх точок прогнозу одну і ту ж формулу. По цій причині досягнення швидкої реакції на зсуви в рівні базової лінії значно ускладнюється. Згладжування являє собою простий спосіб обійти дану проблему. Основна ідея застосування згладжування складається в тому, що кожний новий прогноз отримують за допомогою переміщення попереднього прогнозу в напрямку, який би дав би кращий результат у порівнянні з старим прогнозом. Базове рівняння має наступний вид:
F[t+1]=F[t]+a*e[t],
t – номер часового періоду;
F[t] – це прогноз зроблений в момент часу t; F[t+1] відбиває прогноз у часовий період, що наступає безпосередньо за моментом часу t;
a – константа згладжування.
e[t] – похибка, тобто відмінність між прогнозом, що зроблено в момент часу t, і фактичними результатами спостережень в момент часу t.
Таким чином, константа згладжування являється величиною, що самокоректується. Іншими словами, кожний новий прогноз являє собою суму попереднього прогнозу і поправочного коефіцієнта, який і пересуває новий прогноз в напрямку, що робить попередній результат більш точним. Застосуємо функцію експоненціального згладжування до нашої прикладу. Перш за все в комірку G1 внесемо текст – Згладжування. Далі виконаємо дії в наступній послідовності:
Виберіть команду Сервис > Анализ данных…
У діалоговому вікні Анализ данных із списку доступних функцій аналізу вибираємо Экспоненциальное сглаживание і натискаємо кнопку OK.
З’явиться діалогове вікно Экспоненциальное сглаживание. В полі Входной интервал вказуємо діапазон робочого листа з даними про базову лінію. Зауваження: для того, щоб скласти прогноз на період, який слідує за останнім показником базової лінії, в поле Входной интервал вводять на один рядок більше, чим необхідно.
В полі Фактор затухания вводимо значення фактору затухання, що дорівнює 0,2. Фактор затухання і константа згладжування зв’язані між собою формулою: 1-константа згладжування = фактор затухання. Таким чином, маючи значення будь-якої з цих величин, можна обчислити іншу. Для константи згладжування найбільш придатними є значення від 0,2 до 0,3. Ці значення показують, що помилка поточного прогнозу встановлена на рівні від 20 до 30 відсотків помилки попереднього прогнозу. Більш високі значення константи прискорюють відгук, але можуть привести до непередбачених викидів. Низькі значення константи можуть привести до зрушення аргументу для передвіщених значень.
В полі Выходной интервал введіть адресу комірки, з якої хочете почати вивід.
Клацніть по копці OK.
Аналогічно побудуємо експоненціальне згладжування, взявши фактор затухання рівним 0,7, у стовпчику H.
Отримаємо :
Для кращого розуміння вищесказаного побудуємо діаграму для Показник, Згладжування 0,2, Згладжування 0,7.
Те, що два графіки згладжування “завалюються” на нуль спочатку, викликано відсутністю даних, зважаючи на сам метод обчислення, для першого періоду. При більш детальному розгляді видно, що при збільшенні фактора затухання, графік функції експоненціального згладжування є більш рівномірним. Він слабше реагує на різки перепади значень базової лінії.