Частотний аналіз серед MS Excel. Розрахунок ковзної середньої в Excel та прогнозування
Розрахунок ковзного середнього – це, перш за все, метод, який дозволяє спростити визначення та аналіз тенденцій у розвитку динамічного рядуна основі згладжування коливань вимірювань за часовими інтервалами. Ці коливання можуть виникати через випадкові помилки, які часто є побічним ефектом техніки окремих розрахунків та вимірювань або результатом різних тимчасових умов.
Інструмент "Ковзне середнє" можна викликати в діалоговому вікні команди "Аналіз даних" з меню "Сервіс".
За допомогою інструменту ковзної середньої складаю прогноз економічних показників таблиці 1.1 (табл. 3.1).
Таблиця3 .1 ― Оцінка тенденції поведінки показників досліджуваного динамічного ряду методом ковзного середнього
Примітка - Джерело: .
З даних таблиці строю графік ковзної середньої.
Рисунок 3.1 – Ковзне середнє
Примітка - Джерело: .
Загальна динаміка ланцюгових темпів приросту і ковзне середнє відображено на графіці, з якого видно, що показник ковзного середнього має тенденцію до зростання, потім зниження, потім знову зростання, тобто. з кожним місяцем обсяг товарообігу постійно змінюється.
Розрахунок ковзного середнього є швидким і простим способомкороткострокового прогнозування економічних показників. У ряді випадків він виглядає навіть ефективніше за інші методи, засновані на довготривалих спостереженнях, оскільки дозволяє при необхідності скоротити динамічний ряд досліджуваного показника до такої кількості його членів, що відображатиме лише останню тенденцію його розвитку. Тим самим прогноз не спотворюватиметься за рахунок викидів, зламів та іншого, що мали місце раніше, і набагато точніше відобразить можливе значення прогнозованого показника в найближчій перспективі.
Складання лінійних прогнозів засобами Excel
За типом функціональних залежностей екзогенних змінних моделі тренда можуть бути лінійними та нелінійними. Складність економічних процесіві властивість відкритості економічних систем зумовлюють здебільшого нелінійний характер розвитку економічних показників. Однак побудова лінійних моделей є набагато менш трудомісткою і з технічної та математичної точок зору процедурою. Тому на практиці нерідко допускають часткове перетворення нелінійних процесів (за умови, що попередньо проведений графічний аналіз даних дозволяє це зробити), і моделювання поведінки досліджуваного показника зводиться до складання та оцінки лінійного рівнянняйого динаміки.
Використання функції лінійн для створення моделі тренду
Функція робочого листа ЛІНІЙН допомагає визначити характер лінійного зв'язку між результатами спостережень та часом їх фіксації та дати їй математичний опис, найкращим чиномапроксимує вихідні дані. Для побудови моделі вона використовує рівняння виду y = mx + b, де y - досліджуваний показник; x = t - тимчасовий тренд; b, m - параметри рівняння, що характеризують відповідно y-перетин і нахил лінії тренду. Розрахунок параметрів моделі Лінейн виробляють на основі методу найменших квадратів.
Викликувати функцію ЛІНІЙН можна у діалоговому вікні «Майстер функцій» (категорія «Статистичні»), розташованому на панелі інструментів «Стандартні».
Таблиця 3.2 ― Розрахунок та оцінка лінійної моделі тренду за допомогою функції ЛІНІЙН
Ковзна середня являє собою статичну функцію, яка дає можливість з легкістю отримувати результати з різних завдань. Наприклад, завдання щодо отримання прогнозу.
Ковзна середня дозволяє змінювати абсолютні динамічні значення цілого ряду осередків на середні арифметичні, використовуючи згладжування даних. Її часто застосовують у підрахунках на економічних біржах, торгівлі та інших сферах.
Як його застосовувати в Excel - давайте розберемо все за етапами.
Даний метод в Excel застосовується через використання функції пакета аналізу і безпосередньо через саму вбудовану функцію, яка отримала назву СРЗНАЧ.
Розглянемо перший спосіб використання методу ковзної середньої через пакет аналізу:
1. Пакет аналізу в стандартному наборі функцій немає, тому його необхідно включити. Робиться це через параметри документа - "Файл" - "Параметри" - "Надбудови". Внизу діалогового вікна є вкладка Надбудови. Саме вона нам і потрібна.
Включаємо «Пакет аналізу» та зберігаємося. Весь функціональний додався в «Дані» та повністю готовий до використання.
2. Щоб зрозуміти, яким чином працює метод ковзної середньої, спробуємо отримати дані за 12 місяців на основі тих, які ми вже отримали за 11 минулих – зробимо прогноз. Заповнюємо вихідні значення таблиці.
3. У раніше доданому функціоналі «Аналіз даних» на робочій панелі з параметрів надбудов документа, вибираємо «Змінну середню» функцію і натискаємо «Ок».
4. У діалоговому вікні заповнимо всі значення. «Вхідний інтервал» - всі наші показники за 11 місяців без осередку. «Інтервал» - показник згладжування щодо наших вихідних даних, встановимо «3». «Вихідний інтервал» - осередки, куди виводитимуться отримані дані методом ковзної середньої. Включаємо «Стандартні похибки» і отримуємо всі значення, що шукаються.
5. Для отримання більш правильного результату виконаємо повторне згладжування з інтервалом у «2» одиниці. Вкажемо новий «Вихідний інтервал» та отримуємо нові дані.
6. На основі нових отриманих даних можна зробити прогноз показник на шуканий місяць шляхом розрахунку методу ковзної середньої за останній період. Грунтуємося на тому, що менше показник стандартної похибки, тим точніше дані.
Розглянемо другий спосіб - функцію СРЗНАЧ:
1. Якщо пакет аналізу робить практично всі операції автоматизованими, використання функції СРЗНАЧ вимагає застосування кількох стандартних функцій Excel. Використовуємо ті самі вихідні дані з 11 місяців. Вставимо функцію.
2. У діалоговому вікні Майстра функцій перейдемо у вкладку «Статистичні» і виберемо нашу функцію «СРЗНАЧ».
3. Функція «СРЗНАЧ» має дуже простий синтаксис – «=СРЗНАЧ(число1;число2;число3;...). Вкажемо в аргументі «число 1» діапазон за «Січень» та «Лютий».
4. Розрахуємо показник для періодів часу, що залишилися, шляхом протягування маркера заповнення формули по стовпцю вниз.
5. Проведемо цю операцію, але з різницею в період за 3 місяці.
6. Але які дані у нашому випадку вірні, на основі двох місяців чи трьох? Для отримання правильної відповіді застосуємо розрахунок абсолютного відхилення, середнього квадратичного та ще кількох інших показників. За повне відхилення відповідає функція «ABS».
У діалоговому вікні функції вказуємо різницю між доходом та ковзною середньою за два місяці.
7. Маркером заповнення заповнимо стовпець та розрахуємо «СРЗАНЧ» за весь час.
8. Проведемо аналогічну операцію для пошуку абсолютного відхилення та середнього значення за період у три місяці.
9. Залишилося ще кілька кроків. Для початку розрахуємо відносне відхилення за двома та трьома місяцями шляхом функції пошуку абсолютного значення поділу знайденого відхилення на наявні вихідні дані, а також знайдемо середнє значення отриманих значень.
Всі дані уявимо у відсотках.
10. Для отримання кінцевого результату методу ковзної середньої залишилося підрахувати середнє квадратичне відхиленнятакож за два та за три місяці.
Наше шукане середнє квадратичне відхилення дорівнюватиме квадратного кореняіз суми квадратів різниць вихідних даних про виручку та отримані дані методом ковзної середньої, розділеної на період часу.
Пропишемо нашу функцію «КОРІНЬ(СУМКВРАЗН(B6:B12;C6:C12)/РАХУНОК(B6:B12))», заповнимо стовпці маркерами заповнення та знайдемо середнє значення за отриманими даними.
11. Проведемо аналіз отриманих даних і можемо з упевненістю зробити висновок – згладжування за двома місяцями дало найбільш правдиві кінцеві показники.
Метод ковзної середньої – це статистичний інструмент, за допомогою якого можна вирішувати різного родузавдання. Зокрема, він часто використовується при прогнозуванні. У програмі ExcelДля вирішення цілого ряду завдань також можна застосовувати цей інструмент. Давайте розберемося, як використовується ковзна середня в Екселі.
Сенс даного методуполягає в тому, що за його допомогою відбувається зміна абсолютних динамічних значень обраного ряду на середні арифметичні за певний період шляхом згладжування даних. Цей інструмент застосовується для економічних розрахунків, прогнозування, у процесі торгівлі біржі тощо. Застосовувати метод ковзної середньої в Екселі найкраще за допомогою найпотужнішого інструменту статистичної обробки даних, який називається Пакетом аналізу. Крім того, з цією ж метою можна використовувати вбудовану функцію Excel Відмінник.
Спосіб 1: Пакет аналізу
Пакет аналізує надбудовою Excel, яка за замовчуванням відключена. Тому насамперед потрібно її включити.
Після цієї дії пакет «Аналіз даних»активовано, і відповідна кнопка з'явилася на стрічці у вкладці «Дані».
А тепер давайте розглянемо, як безпосередньо можна використати можливості пакету Аналіз данихдля роботи за методом ковзної середньої. Давайте на основі інформації про доход фірми за 11 попередніх періодів складемо прогноз на дванадцятий місяць. Для цього скористаємось заповненою даними таблицею, а також інструментами Пакет аналізу.
- Переходимо у вкладку «Дані»і тиснемо на кнопку «Аналіз даних», яка розміщена на стрічці інструментів у блоці «Аналіз».
- Відкривається перелік інструментів, які доступні в Пакет аналізу. Вибираємо з них найменування «Слизьке середнє»і тиснемо на кнопку "OK".
- Запускається вікно введення даних для прогнозування методом ковзної середньої.
В полі "Вхідний інтервал"вказуємо адресу діапазону, де розташована щомісячно сума виручки без осередку, дані в якій слід розрахувати.
В полі "Інтервал"слід вказати інтервал обробки значень методом згладжування. Для початку давайте встановимо значення згладжування у три місяці, а тому вписуємо цифру «3».
В полі «Вихідний інтервал»потрібно вказати довільний порожній діапазон на аркуші, де будуть виводитися дані після їх обробки, який повинен бути на одну комірку більше вхідного інтервалу.
Також слід встановити галочку біля параметра «Стандартні похибки».
При необхідності можна також встановити галочку біля пункту «Виведення графіка»для візуальної демонстрації, хоча в нашому випадку це не обов'язково.
Після того, як всі налаштування внесені, тиснемо на кнопку "OK".
- Програма виводить результат обробки.
- Тепер виконаємо згладжування за період два місяці, щоб виявити, який результат є коректнішим. Для цього знову запускаємо інструмент «Слизьке середнє» Пакет аналізу.
В полі "Вхідний інтервал"залишаємо ті ж значення, що й у попередньому випадку.
В полі "Інтервал"ставимо цифру «2».
В полі «Вихідний інтервал»вказуємо адресу нового порожнього діапазону, який, знову ж таки, повинен бути на одну комірку більше вхідного інтервалу.
Інші налаштування залишаємо колишніми. Після цього тиснемо на кнопку "OK".
- Після цього програма робить розрахунок і виводить результат на екран. Для того, щоб визначити, яка з двох моделей точніша, нам потрібно порівняти стандартні похибки. Чим менше даний показник, Тим вище ймовірність точності отриманого результату. Як бачимо, за всіма значеннями стандартна похибка при розрахунку двомісячної ковзної менше, ніж аналогічний показник за 3 місяці. Таким чином, прогнозованим значенням на грудень вважатимуться величину, розраховану методом ковзання останній період. У разі це значення 990,4 тис. рублів.
Спосіб 2: використання функції СРЗНАЧ
В Екселі існує ще один спосіб застосування методу ковзної середньої. Для його використання потрібно застосувати цілий рядстандартних функцій програми, базової з яких для нашої мети є Відмінник. Для прикладу ми будемо використовувати ту саму таблицю доходів підприємства, що й у першому випадку.
Як і минулого разу, нам потрібно буде створити згладжені часові ряди. Але цього разу дії не будуть настільки автоматизовані. Слід розрахувати середнє значення за кожні два, а потім три місяці, щоб мати змогу порівняти результати.
Насамперед, розрахуємо середні значення за два попередні періоди за допомогою функції Відмінник. Зробити це ми можемо тільки починаючи з березня, тому що для пізніших дат йде обрив значень.
- Виділяємо комірку в порожній стовпчику в рядку за березень. Далі тиснемо на значок "Вставити функцію", що розміщений поблизу рядка формул.
- Активується вікно Майстри функцій. у категорії «Статистичні»шукаємо значення «СРЗНАЧ», виділяємо його та клацаємо по кнопці "OK".
- Запускається вікно аргументів оператора Відмінник. Синтаксис у нього такий:
СРЗНАЧ(число1; число2; ...)
Обов'язковим є лише один аргумент.
У нашому випадку, у полі «Число1»ми повинні вказати посилання на діапазон, де вказано дохід за два попередні періоди (січень та лютий). Встановлюємо курсор у полі та виділяємо відповідні осередки на аркуші в стовпці "Дохід". Після цього тиснемо на кнопку "OK".
- Як бачимо, результат розрахунку середнього значення за два попередні періоди відобразився в осередку. Для того, щоб виконати подібні обчислення для решти місяців періоду, нам потрібно скопіювати цю формулудо інших осередків. Для цього стаємо курсором у нижній правий кут комірки, що містить функцію. Курсор перетворюється на маркер заповнення, який має вигляд хрестика. Затискаємо ліву кнопку миші і простягаємо його вниз до кінця стовпця.
- Отримуємо розрахунок результатів середнього значення за два попередні місяці до кінця року.
- Тепер виділяємо комірку у наступному порожньому стовпці у рядку за квітень. Викликаємо вікно аргументів функції Відмінниктим самим способом, який був описаний раніше. В полі «Число1»вписуємо координати осередків у стовпці "Дохід"з січня до березня. Потім тиснемо на кнопку "OK".
- За допомогою маркера заповнення копіюємо формулу в комірки таблиці, розташовані нижче.
- Отже, значення ми підрахували. Тепер, як і в попередній раз, нам потрібно буде з'ясувати, який вид аналізу якісніший: зі згладжуванням у 2 або 3 місяці. Для цього слід розрахувати середнє квадратичне відхилення та деякі інші показники. Для початку розрахуємо абсолютне відхилення, скориставшись стандартною функцією Excel ABSяка замість позитивних або негативних чиселповертає їхній модуль. Це значення дорівнює різниці між реальним показником виручки за обраний місяць і прогнозованим. Встановлюємо курсор у наступний порожній стовпець у рядок за травень. Викликаємо Майстер функцій.
- у категорії «Математичні»виділяємо найменування функції «ABS». Тиснемо на кнопку "OK".
- Запускається вікно аргументів функції ABS. У єдиному полі «Кількість»вказуємо різницю між вмістом осередків у стовпцях "Дохід"і "2 місяці"за травень. Потім тиснемо на кнопку "OK".
- За допомогою маркера заповнень копіюємо цю формулу у всі рядки таблиці до листопада включно.
- Розраховуємо середнє значення абсолютного відхилення за весь період за допомогою вже знайомої нам функції Відмінник.
- Аналогічну процедуру виконуємо і для того, щоб підрахувати абсолютне відхилення для ковзання за 3 місяці. Спочатку застосовуємо функцію ABS. Тільки цього разу вважаємо різницю між вмістом осередків із фактичним доходом та плановим, розрахованим за методом ковзної середньої за 3 місяці.
- Далі розраховуємо середнє значення всіх даних абсолютного відхилення за допомогою функції Відмінник.
- Наступним кроком є підрахунок відносного відхилення. Воно дорівнює відношенню абсолютного відхилення до фактичного показника. Для того, щоб уникнути негативних значень, ми знову скористаємося тими можливостями, які пропонує оператор ABS. На цей раз за допомогою цієї функції ділимо значення абсолютного відхилення при використанні методу ковзної середньої за 2 місяці на фактичний дохід за вибраний місяць.
- Але відносне відхилення прийнято відображати у відсотковому вигляді. Тому виділяємо відповідний діапазон на аркуші, переходимо у вкладку «Головна», де в блоці інструментів «Кількість»у спеціальному полі форматування виставляємо процентний формат. Після цього результат підрахунку відносного відхилення відображається у відсотках.
- Аналогічну операцію щодо підрахунку відносного відхилення проробляємо і з даними із застосуванням згладжування за 3 місяці. Тільки в цьому випадку для розрахунку як ділимо використовуємо інший стовпець таблиці, який має назву «Абс. відкл (3м)». Потім переводимо числові значення у відсотковий вигляд.
- Після цього вираховуємо середні значення для обох колонок із відносним відхиленням, як і раніше використовуючи для цього функцію Відмінник. Так як для розрахунку як аргументи функції ми беремо відсоткові величини, то додаткову конвертацію робити не потрібно. Оператор на виході видає результат уже у процентному форматі.
- Тепер ми підійшли до розрахунку середнього квадратичного відхилення. Цей показник дозволить нам безпосередньо порівняти якість розрахунку при використанні згладжування за два та за три місяці. У нашому випадку середнє квадратичне відхилення дорівнюватиме кореню квадратному із суми квадратів різниць фактичної виручки та ковзної середньої, поділеної на кількість місяців. Для того, щоб зробити розрахунок у програмі, ми маємо скористатися цілим рядом функцій, зокрема КОРІНЬ, СУМКВРАЗНі РАХУНОК. Наприклад, для розрахунку середнього квадратичного відхилення при використанні лінії згладжування за два місяці у травні буде в нашому випадку застосовуватись формула наступного виду:
КОРІНЬ(СУМКВРАЗН(B6:B12;C6:C12)/РАХУНОК(B6:B12))
Копіюємо її в інші осередки стовпця з розрахунком середнього квадратичного відхилення за допомогою маркера заповнення.
- Аналогічну операцію з розрахунку середнього квадратичного відхилення виконуємо і для ковзної середньої за 3 місяці.
- Після цього розраховуємо середнє значення за період для обох цих показників, застосувавши функцію Відмінник.
- Зробивши порівняння розрахунків методом ковзної середньої зі згладжуванням у 2 та 3 місяці за такими показниками, як абсолютне відхилення, відносне відхилення та середньоквадратичне відхилення, Можна з упевненістю сказати, що згладжування протягом двох місяців дає більш достовірні результати, ніж застосування згладжування протягом трьох місяців. Про це говорить те, що вищезазначені показники за двомісячним ковзним середнім, меншим, ніж за тримісячним.
- Таким чином, прогнозований показник доходу підприємства за грудень становитиме 990,4 тис. рублів. Як бачимо, це значення повністю збігається з тим, яке ми отримали, розраховуючи за допомогою інструментів Пакет аналізу.
Ми зробили розрахунок прогнозу за допомогою методу ковзної середньої двома способами. Як бачимо, цю процедуру набагато простіше виконати за допомогою інструментів Пакет аналізу. Проте деякі користувачі не завжди довіряють автоматичного розрахункуі вважають за краще для обчислень використовувати функцію Відмінникта супутні оператори для перевірки найбільш достовірного варіанту. Хоча, якщо все зроблено правильно, на виході результат розрахунків має бути цілком однаковим.
Виберіть у меню Сервіспункт Аналіз даних, з'явиться вікно з однойменною назвою, головним елементом якого є область Інструменти аналізу. У цій галузі представлений список реалізованих у Microsoft Excelметодів статистичної обробки даних Кожен із перерахованих методів реалізований у вигляді окремого режиму роботи, для активізації якого необхідно виділити відповідний метод вказівником миші та клацнути по кнопці ОК. Після появи діалогового вікна викликаного режиму можна розпочинати роботу.
Режим роботи " Ковзне середнє» служить для згладжування рівнів емпіричного динамічного ряду на основі методу простої ковзної середньої.
Режим роботи " Експонентне згладжування» служить для згладжування рівнів емпіричного динамічного ряду на основі методу простого експоненційного згладжування.
У діалогових вікнах даних режимів (рисунок 2 та 3) задаються такі параметри:
2. Прапорець Мітки– встановлюється активний стан, якщо перший рядок (стовпець) у вхідному діапазоні містить заголовки. Якщо заголовки відсутні, прапорець слід деактивізувати. У цьому випадку будуть автоматично створені стандартні назвиданих вихідного діапазону.
3. Інтервал(тільки в діалоговому вікні Ковзаюче середнє) – вводиться розмір вікна згладжування р. За замовчуванням р=3.
Рисунок 2 – Діалогове вікно ковзного середнього
4. Фактор згасання(тільки у діалоговому вікні Експонентне згладжування) – вводиться значення коефіцієнта експоненційного згладжування p. За замовчуванням, p=0,3.
5. Вихідний інтервал / Новий робочий лист / Нова робоча книга– у положенні Вихідний інтервал активізується поле, у яке необхідно ввести посилання на ліву верхню комірку вихідного діапазону. Розмір вихідного діапазону буде визначено автоматично, і на екрані з'явиться повідомлення у разі можливого накладання вихідного діапазону на вихідні дані. У положенні Новий робочий лист відкривається новий лист, в який починаючи з осередку А1вставляються результати аналізу. Якщо потрібно задати ім'я в полі, розташоване навпроти відповідного положення перемикача. У положенні Нова робоча книга відкривається нова книга, на першому аркуші якого починаючи з комірки А1вставляються результати аналізу.
6. Виведення графіка- Встановлюється в активний стан для автоматичної генерації на робочому аркуші графіків фактичних та теоретичних рівнів динамічного ряду.
7. Стандартні похибки– встановлюються в активний стан, якщо потрібно увімкнути у вихідний діапазон стовпець, що містить стандартні похибки.
Рисунок 3 – Діалогове вікно експонентного згладжування
приклад 1.
Дані реалізації (млн. крб.) товарів сільськогосподарського виробництва магазинами споживчої кооперації міста наведено у таблиці, сформованої робочому аркуші Microsoft Excel (рисунок 4). У зазначеному періоді (2009 – 2012 рр.) необхідно виявити основну тенденцію розвитку цього економічного процесу.
Рисунок 4 – Вихідні дані
Для вирішення задачі використовуємо режим роботи « Ковзне середнє». Значення параметрів, встановлених у однойменному діалоговому вікні, представлені малюнку 5, розраховані у цьому режимі показники – малюнку 6, а побудовані графіки – малюнку 7.
Рисунок 5 – Заповнення діалогового вікна
Рисунок 6 – Результати аналізу
Малюнок 7 - Ковзне середнє
У стовпці D (рис. 5) обчислюються значення згладжених рівнів. Наприклад, значення першого згладженого рівня розраховується в комірці D5 за формулою = СРЗНАЧ(С2:С5), значення другого згладженого рівня – в комірці D6 за формулою = СРЗНАЧ(С5:С8) і т.д.
У стовпці E обчислюються значення стандартних похибок за допомогою формули = КОРІНЬ (СУМАКВРАЗН (блок фактичних значень; блок прогнозних значень) / розмір вікна згладжування).
Наприклад, значення в комірці Е10 розраховується за формулою = КОРІНЬ (СУМКВРАЗН (С7: С10; О7: В10) / 4).
Разом з тим, як зазначалося вище, якщо розмір вікна згладжування є парним числом ( р = 2m), то розраховане усереднене значення не можна зіставити якомусь певному моменту часу t, тому необхідно застосовувати процедуру центрування.
Для прикладу, що розглядається р = 4Тому процедура центрування необхідна. Так, перший згладжений рівень (265,25) записується між ІІ та ІІІ кв. 2009 і т.д. Застосовуючи процедуру центрування (для цього використовуємо функцію СРЗНАЧ) отримуємо згладжені рівні з центруванням. Для ІІІ кВ. 2009 визначається серединне значення між першим і другим згладженими рівнями: (265,25 + 283,25) / 2 = 274,25; для IV кв. 2009 р. центруються другий та третій згладжені рівні: (283,25 + 292,00)/2 = 287,6 тощо. Розраховані значення представлені в таблиці 1. Скоригований графік ковзної середньої представлений малюнку 8.
Таблиця 1 - Динаміка згладжених рівнів реалізації продукції
| Рік | Квартал | Розмір реалізації, млн. руб. | Згладжені рівні з центруванням |
| 274,25 | |||
| 287,63 | |||
| 297,00 | |||
| 307,50 | |||
| 334,63 | |||
| 374,13 | |||
| 402,88 | |||
| 421,00 | |||
| 429,00 | |||
| 430,75 | |||
| 435,38 | |||
| 446,63 | |||
Рисунок 8 – Коригований графік ковзного середнього
приклад 2.
Розглянута задача може бути вирішена за допомогою методу простого експоненційного згладжування. Для цього необхідно використовувати режим роботи експонентного згладжування. Значення параметрів, встановлених у однойменному діалоговому вікні, представлені малюнку 9, розраховані у цьому режимі показники – малюнок 10, а побудовані графіки – малюнку 11.
Рисунок 9 – Заповнення діалогового вікна «Експоненційне згладжування»
Рисунок 10 – Результати аналізу
Малюнок 11 – Експонентне згладжування
У стовпці D (рис. 10) обчислюються значення згладжених рівнів на основі рекурентних співвідношень.
У стовпці E розраховуються значення стандартних похибок за допомогою формули = КОРІНЬ (СУМКВРАЗН (блок фактичних значень; блок прогнозних значень) / 3). Як легко помітити (порівняйте малюнок 8 і 11), при використанні методу простого експоненційного згладжування, на відміну від методу простої ковзної середньої, зберігаються дрібні хвилі.
У бізнесі, як і в будь-якій іншій діяльності людина, хоче знати, а що буде далі. Навіть важко уявити багатство того щасливця, який зі 100% точністю міг би вгадувати майбутнє. Але, на жаль (або, на щастя) дар передбачення зустрічається вкрай рідко. А… намагатися хоча б у загальних рисахуявити майбутню бізнес ситуацію підприємець просто зобов'язаний.
Спочатку я хотів написати в одному пості відразу про кілька простих і зручних методик, але піст став виходити дуже великим. І тому буде кілька постів присвячених теміпрогнозування. У даному пості ми опишемо один із найпростіших методів прогнозування з використанням можливостей Excel – метод ковзного середнього.
Найчастіше у практиці маркетингових досліджень прогнозуються такі величини:
- Обсяги продажів
- Розмір та ємність ринку
- Об'єми виробництва
- Обсяги імпорту
- Динаміка цін
- та ін.
Для прогнозування, яке ми розглядаємо в даному пості, раджу дотримуватися наступного простого алгоритму:
1. Збір вторинної інформаціїз проблеми(бажано як кількісної, і якісної). Так, наприклад, якщо Ви прогнозуєте розмір свого ринку, потрібно зібрати статистичну інформацію щодо ринку (обсяги виробництва, імпорту, динаміку цін, обсяги продажу та ін.) так і тенденції, проблеми чи можливості ринку. Якщо ви прогнозуєте обсяг продажу, тоді вам потрібні дані про продаж за період. Для прогнозування чим більше історичних даних ви розглянете, тим краще. Бажано прогнозування доповнити аналізом факторів, що впливають на прогнозоване явище (можна SWOT, PEST аналіз або будь-який інший). Це дозволить розуміти логіку розвитку, і ви зможете таким чином перевіряти правдоподібність тієї чи іншої моделі тренду.
2. Далі бажано перевірити кількісні дані. Для цього потрібно порівняти значення тих самих показників, але отриманих з різних джерел. Якщо все сходитися, можна «заганяти» дані в Excel. Також дані повинні відповідати таким вимогам:
- Базова лінія включає результати спостережень - починаючи з ранніх і закінчуючи останніми.
- Усі періоди базової лінії мають однакову тривалість. Не слід змішувати дані, наприклад, за день із середніми триденними показниками.
- Спостереження фіксуються в той самий момент кожного тимчасового періоду. Наприклад трафік замірятись повинен в один і той же час.
- Перепустка даних не допускається. Перепустка навіть одного результату спостережень небажана при прогнозуванні» тому, якщо у ваших спостереженнях відсутні результати за незначний відрізок часу, постарайтеся заповнити їх хоча б приблизними даними.
3. Перевіривши дані, можна застосовувати різні методики прогнозування. Почати я хотів би з самого простого методу – МЕТОДУ КОВЗНОГО СЕРЕДНЬОГО
МЕТОД КОВЗНОГО СЕРЕДНЬОГО
Метод ковзного середнього застосовувати досить нескладно, проте він дуже простий для побудови точного прогнозу. При використанні цього методу прогноз будь-якого періоду є не що інше, як отримання середнього показника за кількома попередніми спостереженнями часового ряду. Наприклад, якщо ви вибрали ковзне середнє за три місяці, прогнозом на травень буде середнє значення показників за лютий, березень та квітень. Вибравши як метод прогнозування ковзне середнє за чотири місяці, ви зможете оцінити травневий показник як середнє значення показників за січень, лютий, березень та квітень.
Як правило, прогноз із застосуванням ковзного середнього розглядається як прогноз на період, що безпосередньо наступає за періодом спостереження. Разом з цим такий прогноз можна застосувати, коли досліджуване явище розвивається послідовно, тобто. є певні тенденції, і крива значень не скаче по діаграмі як пригоріла.
Щоб визначити, скільки спостережень бажано включити до ковзного середнього, потрібно виходити з попереднього досвідута наявної інформації про набір даних. Необхідно витримувати рівновагу між підвищеним відгуком ковзного середнього на кілька свіжих спостережень і великою мінливістю цього середнього.
Отже, як це робити вExcel
1. Припустимо, що у Вас є обсяги місячних продажів за останні 29 місяців. І ви хочете визначити, який обсяг продажів буде у 30 місяці. Але, якщо чесно, зовсім не обов'язково при розрахунку прогнозних значень оперувати 30 історичними значеннями, адже цей метод використовуватиме для розрахунку середнього лише кілька останніх місяців. Тому для розрахунку достатньо лише кілька минулих місяців.
2. Наводимо цю таблицю як зрозумілий Excel, тобто. щоб усі значення були в одному ряді.
3. Далі вводимо формулу розрахунку середнього за попереднім трьом (чотирьом, п'яти? як самі оберіть) значенням (див. в). Найзручніше все-таки використовуватиме розрахунку останні 3 значення, т.к. якщо враховувати більше, дані будуть занадто середні, якщо менше - не будуть точними.
4. Використовуючи функцію автозаповнення для всіх наступних значень до 30, прогнозного місяця. Таким чином, функція розрахує прогноз на червень 2010 р. Згідно з прогнозними значеннями у червні продажу становитимуть близько 408 одиниць товару. Але зверніть увагу, що якщо тенденція падіння постійна, як у нашому прикладі, розрахунок прогнозу за середньою буде трохи завищеним, або буде «відставати» від реальних значень.
Ми розглянули одну з найпростіших методик прогнозування – метод ковзного середнього. У наступних постах ми розглянемо інші, точніші і складніші методики. Сподіваюся, мій пост буде Вам корисним.
