Ковзне середнє та експоненційне згладжування в MS Excel. Прогнозування ціни акцій на ринку цінних паперів в Excel

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

Інструмент "Ковзне середнє" можна викликати в діалоговому вікні команди "Аналіз даних" з меню "Сервіс".

За допомогою інструменту ковзної середньої складаю прогноз економічних показників таблиці 1.1 (табл. 3.1).

Таблиця3 .1 ― Оцінка тенденції поведінки показників досліджуваного динамічного ряду методом ковзного середнього

Примітка - Джерело: .

З даних таблиці строю графік ковзної середньої.

Рисунок 3.1 – Ковзне середнє

Примітка - Джерело: .

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

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

1. Складання лінійних прогнозів засобами Excel

За типом функціональних залежностей екзогенних змінних моделі тренда можуть бути лінійними та нелінійними. Складність економічних процесів та властивість відкритості економічних систем зумовлюють здебільшого нелінійний характер розвитку економічних показників. Однак побудова лінійних моделей є набагато менш трудомісткою і з технічної та математичної точок зору процедурою. Тому на практиці нерідко допускають часткове перетворення нелінійних процесів (за умови, що попередньо проведений графічний аналіз даних дозволяє це зробити), і моделювання поведінки досліджуваного показника зводиться до складання та оцінки лінійного рівняння динаміки.

1. Використання функції лінійн для створення моделі тренду

Функція робочого листа ЛІНІЙН допомагає визначити характер лінійного зв'язку між результатами спостережень і часом їх фіксації та дати їй математичний опис, що найкраще апроксимує вихідні дані. Для побудови моделі вона використовує рівняння виду y = mx + b, де y - досліджуваний показник; x = t - тимчасовий тренд; b, ​​m - параметри рівняння, що характеризують відповідно y-перетин і нахил лінії тренду. Розрахунок параметрів моделі Лінейн виробляють на основі методу найменших квадратів.

Викликувати функцію ЛІНІЙН можна у діалоговому вікні «Майстер функцій» (категорія «Статистичні»), розташованому на панелі інструментів «Стандартні».

Таблиця 3.2 ― Розрахунок та оцінка лінійної моделі тренду за допомогою функції ЛІНІЙН

Виберіть у меню Сервіспункт Аналіз даних, з'явиться вікно з однойменною назвою, головним елементом якого є область Інструменти аналізу. У цій галузі представлено список реалізованих у 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), при використанні методу простого експоненційного згладжування, на відміну від методу простої ковзної середньої, зберігаються дрібні хвилі.

Мета роботи : Придбати навички вирішення задач частотного аналізу за допомогою функції робочого листа аналізу MS Excel.

Коротка теорія

p align="justify"> При аналізі економічних показників часто виникає питання, як часто зустрічаються показники в заданих інтервалах значень.

Функція ЧАСТОТА робочого листа аналізу MS Excel відноситься до категорії статистичних функцій та повертає розподіл частот у вигляді вертикального масиву. Для даної множини значень та заданої множини кишень (інтервалів) частотний розподіл підраховує, скільки значень потрапляє в кожний інтервал.

Як масив даних може бути одновимірний або двовимірний масив (наприклад, A4: D15).

Синтаксис: ЧАСТОТА (масив_даних; масив_кишень)

Для частотного аналізу можна використовувати командуСервіс/Аналіз даних.Аналіз даних є однією з надбудов Excel . Якщо в меню відсутня ця команда, слід виконати командуСервіс / Надбудовита встановити відповідний прапорець у вікніНадбудови.

Завдання 1

За допомогою функціїЧастота для вибірки безлічі сум замовлень () введіть у діапазон підрахуйте, скільки значень потрапляють у задані інтервали значень. Наприклад, від 0 до 1000, від 1001 до 1500, від 1501 до 2000, від 2001 до 2500, понад 2500.

Порядок дій:

1. На робочому аркуші MS Excel введіть дані про обсяги замовлень у 20 філіях фірми за вересень у вигляді таблиці, фрагмент якої показано на малюнку.

	№ філії	Вересень
		1230		1000
				1500
		….		2000
				2500

1. У вільний діапазон клітин (стовпець) введіть верхні межі інтервалів (Наприклад, D 2 = 1000, D 3 = 1500, D 4 = 2000, D 5 = 2500).
2. Виділіть блок осередків стовпця, суміжного зі стовпцем інтервалів ( E 2: E 21). Для того, щоб підрахувати кількість значень, що перевищують нижню межу інтервалу, виділяється діапазон на одну комірку більше, ніж діапазон інтервалів.
3. В діапазоні E 2: E 6 введіть формулу ( =ЧАСТОТА( E 2: E 15; J 2: J 6)).

Для цього скористайтеся майстром функцій (Вставка/Функція). У категорії «Статистичні» виберіть зі списку опцію «Частота». У діалоговому вікні ЧАСТИНА заповніть поля масиву вибірки та масиву інтервалів (рисунок 1).Не виходячи з вікна діалогунатисніть комбінацію клавіш< Ctrl / Shift / Enter > для розрахунку елементів масиву

Рисунок 1 Приклад заповнення діалогового вікна функціїЧастота.

1. Побудуйте діаграму за результатами.
2. Збережіть файл.

Завдання 2

Створіть на робочому аркуші двовимірний масив, що містить статистичні дані про зростання людей різних вікових категорій. Проведіть частотний аналіз результатів за допомогою функції ЧАСТОТА та Аналізу даних (пункт менюАналіз даних / Гістограма).

При використанні інструмента аналізу даних у діалоговому вікні у поліВхідний інтервалвведіть вихідний інтервал, за яким будується гістограма, у поліІнтервал кишень- Діапазон зі значеннями верхніх меж інтервалів. Гістограма будується на новому або поточному робочому аркуші.

Частина 2

Вирішення задач прогнозування в середовищі MS Excel. Метод ковзного середнього

Мета роботи : Придбати навички прогнозування економічної діяльності підприємства із застосуванням статистичного програмного пакету MS Excel.

Коротка теорія

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

Для вирішення завдань прогнозування у середовищі MS Excel використовується Пакет аналізу , Що включає інструменти аналізу Вибравши інструмент для аналізу даних і задавши необхідні параметри, можна швидко вирішувати складні статистичні завдання, супроводжуючи їх графічною інтерпретацією.

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

Вибираючи метод прогнозування враховують характер зміни випадкової величини часового ряду. Якщо варіація середніх значень незначна і всі спостереження часового ряду мають однакову значущість для прогнозу, то використовують метод ковзного середнього. Ковзаючі середні дозволяють згладити (відфільтрувати) випадкові та періодичні коливання часового ряду. Згладжування простої ковзної середньої є найбільш поширеною процедурою згладжування.

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

Якщо прогнозу найбільш значущими є останні результати спостережень, то використовують метод експоненційного згладжування. У методі експоненційного згладжування кожне значення бере участь у формуванні прогнозованих значень зі змінною вагою, яка зменшується в міру «старіння» даних. В інструменті аналізу MS Excel « Експонентне згладжування»ваговий коефіцієнт, або параметр згладжування, визначається параметромФактор згасання. Зазвичай для часових рядів в економічних завданнях величину параметра згладжування задають в інтервалі від 01 до 03. Початкове розрахункове значення у процедуріЕкспонентне згладжуванняпакету Аналізу MS Excel приймається рівним рівню першого члена низки. Метод забезпечує гарне узгодження вихідних та розрахункових даних для перших значень ряду. Якщо кінцеві обчислені значення значно відрізняються від відповідних вихідних, доцільно змінити величину параметра згладжування. Оцінити величини розбіжностей можна з урахуванням стандартних похибок і графіка, які пакет Аналізу дозволяє вивести разом із розрахунковими значеннями ряду.

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

Завдання до лабораторної роботи (частина 2)

Завдання 1 : Обчислити прогнозоване значення величини обсягу продукції (послуг) підприємства методом ковзного середнього.

Порядок виконання завдання:

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

Для обчислень скористаємось способом прямого введення формули. Щоб отримати трирічне ковзне середнє обсягу виконаних послуг для нашого прикладу, введемо в осередок B 5 формулу для обчислення = СРЗНАЧ( A 2: A 4). Скопіюємо формулу в інтервал B6: B11.

Рисунок 1 | Обчислення простого ковзного середнього

Проілюструємо результати графіком, що відображає динаміку зміни вихідних даних та ковзного середнього.

Малюнок 2 | Графік тенденції зміни показника обсягу послуг, отриманої методом простого ковзного середнього

Іншим способом рішення є використання для визначення ковзного цілогоПакет аналізу . Пакет аналізу є надбудовою MS Excel (Виберіть пункт менюСервіс / Надбудовита встановіть прапорецьПакет аналізу).

Порядок дій

1. Виконати командуСервіс/Аналіз данихта вибрати зі списку інструментів аналізуКовзне середнє.
2. У діалоговому вікні вкажіть параметри для обчислення ковзного середнього:

• Як вхідний інтервал виділіть блок осередків, що містить дані про обсяг послуг.
• Вкажіть Інтервал- 3 (за замовчуванням використовується 3), в якості вихідного інтервалу будь-яку комірку робочого листа (просто клацніть на комірці робочого листа, з якої повинні виводитися результати);

Excel сам виконає роботу з внесення значень у формулу для обчислень ковзного середнього. Через недостатню кількість даних при обчисленні середнього значення для перших результатів спостережень у початкових осередках вихідного діапазону буде виведено значення помилки #Н/Д. Врахуйте, що перше отримане значення низки є прогнозним не так на третій, але в четвертий період. Тому, якщо зазначена для виведення осередок відповідає початку стовпця спостережень, то потрібно стовпець розрахованих значень перемістити вниз одну осередок. Ця дія приєднає прогнози саме до тих періодів, котрим вони розраховані.

Проаналізуйте використовувані розрахункові формули та отримані результати.

Аналогічно обчисліть п'ятирічні прості ковзні середні. Порівняйте результати згладжування двох варіантів розрахунку.

Завдання 2: Обчислити прогнозоване значення величини обсягу продукції (послуг) підприємства шляхом експоненційного згладжування.

Порядок дій:

1. На аркуші MS Excel створіть список, що містить дані про кількість співробітників фірми за останні 10 років. Дані введіть довільно, але так, щоб простежувалася тенденція.
2. Проведіть згладжування часового ряду за допомогою експоненційної середньої з параметрами згладжування 0,1 і потім 0,3. За результатами розрахунків побудуйте графік та визначте, який із отриманих часових рядів має більш гладкий характер.

Скористайтеся командоюСервіс/Аналіз данихта виберіть зі списку інструментів аналізуЕкспонентне згладжування.Вкажіть параметри для обчислення ковзного середнього:

• Як вхідний інтервал виділіть блок осередків, що містить дані про чисельність.
• Вкажіть Фактор згасання. В якості вихідного інтервалу будь-яку комірку робочого листа.
• Задайте виведення графіка та стандартних похибок.

1. Додати лінії тренда на отриманих графіках. Для цього виберіть лінію графіка (просто клацніть правою кнопкою миші на лінії графіка) та в контекстному меню виберіть пунктДодати лінію тренду. У діалоговому вікні виберіть тип тренда (наприклад, лінійна фільтрація), який найбільше підходить для ваших даних, і встановіть прапорець виведення рівняння кривої, що апроксимує, на графіку.
2. Перевірте та збережіть результати.

Частина 3

Розв'язання задач прогнозування за допомогою функцій робочого листа та маркера заповнення

Коротка теорія

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

Існує кілька методів підбору кривих. Одним із найпростіших є візуальний метод. Якщо на графіку недостатньо проглядається тенденція розвитку (тренд), то роблять, як описано вище, згладжування ряду, а потім підбирається крива, що відповідає новому ряду. І тут також застосовуються сучасні програмні засоби комп'ютерних систем. У MS Excel вбудовані спеціальні функції, що дозволяють розраховувати прогнозовані значення певний період.

Excel проводить лінійну екстраполяцію, тобто. розраховує найбільш підходящу пряму, яка проходить через серію заданих точок. Завдання полягає у нанесенні на графік набору точок, а потім у підборі лінії, за якою можна простежити розвиток функції з найменшою помилкою. Ця лінія називається лінією ТРЕНДА. Користувач може використовувати результат обчислень для аналізу тенденцій та короткострокового прогнозування.

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

• За допомогою маркера заповнення
• За допомогою функцій робочого листа

Перший спосіб

Лінійне наближення

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

Експонентне наближення

• Виділити осередки з результатами спостережень.
• Перетягніть маркер заповнення за допомогою правої кнопки миші, щоб виділеними виявилися також і комірки, для яких необхідно розрахувати прогнозовані значення.
• У контекстному меню вибрати команду «Експоненційне наближення».

Другий спосіб

У MS Excel вбудовано статистичні функції робочого листа.

ТЕНДЕНЦІЯ() - Повертає значення відповідно до лінійної апроксимації за методом найменших квадратів.

ЗРІСТ() - Повертає значення відповідно до експоненційного тренду.

Використання цих функцій ще один спосіб обчислення регресійного аналізу.

Формат

ТЕНДЕНЦІЯ (зв_знач_Y; зв_знач_X; новий_знач_X; константа)

Функція РОСТ повертає значення відповідно до експоненційного тренду.

Завдання до лабораторної роботи (частина 3)

Завдання 1:

Розрахуйте лінійний та експоненційний прогноз на один рік та на наступні три періоди (до 2011 року) за допомогою маркера заповнення.

Завдання 2:

Розрахуйте лінійний та експоненційний прогноз на один рік і потім на наступні три періоди за допомогою функцій робочого листа ТЕНДЕНЦІЯ та РОСТ. Для розрахунку інтервального прогнозу після заповнення параметрів діалогового вікна функції та не виходячи з нього натисніть комбінацію клавіш Ctrl/Shift/Enter.

У рядку формул робочого листа має з'явитися формула для розрахунку елементів масиву, наприклад,

( = ТЕНДЕНЦІЯ (B 3: G 3; B 2: G 2; B 2: H 2))

Визначте, яка модель є найточнішою.

Побудуйте графіки та лінії тренду для першого та другого завдання.

У бізнесі, як і в будь-якій іншій діяльності людина, хоче знати, а що буде далі. Навіть важко уявити багатство того щасливця, який зі 100% точністю міг би вгадувати майбутнє. Але, на жаль (або, на щастя) дар передбачення зустрічається вкрай рідко. АЛЕ… намагатися хоча б загалом уявити майбутню бізнес ситуацію підприємець просто зобов'язаний.

Спочатку я хотів написати в одному пості відразу про кілька простих і зручних методик, але піст став виходити дуже великим. І тому буде кілька постів, присвячених темі прогнозування. У даному пості ми опишемо один із найпростіших методів прогнозування з використанням можливостей Excel – метод ковзного середнього.

Найчастіше у практиці маркетингових досліджень прогнозуються такі величини:

• Обсяги продажів
• Розмір та ємність ринку
• Об'єми виробництва
• Обсяги імпорту
• Динаміка цін
• та ін.

Для прогнозування, яке ми розглядаємо в даному пості, раджу дотримуватися наступного простого алгоритму:

1. Збір вторинної інформації з проблеми(бажано як кількісної, і якісної). Так, наприклад, якщо Ви прогнозуєте розмір свого ринку, потрібно зібрати статистичну інформацію щодо ринку (обсяги виробництва, імпорту, динаміку цін, обсяги продажу та ін.) так і тенденції, проблеми чи можливості ринку. Якщо ви прогнозуєте обсяг продажу, тоді вам потрібні дані про продаж за період. Для прогнозування чим більше історичних даних ви розглянете, тим краще. Бажано прогнозування доповнити аналізом факторів, що впливають на прогнозоване явище (можна SWOT, PEST аналіз або будь-який інший). Це дозволить розуміти логіку розвитку, і ви зможете таким чином перевіряти правдоподібність тієї чи іншої моделі тренду.

2. Далі бажано перевірити кількісні дані. Для цього потрібно порівняти значення тих самих показників, але отриманих з різних джерел. Якщо все сходитися, можна «заганяти» дані в Excel. Також дані повинні відповідати таким вимогам:

• Базова лінія включає результати спостережень - починаючи з ранніх і закінчуючи останніми.
• Усі періоди базової лінії мають однакову тривалість. Не слід змішувати дані, наприклад, за день із середніми триденними показниками.
• Спостереження фіксуються в той самий момент кожного тимчасового періоду. Наприклад трафік замірятись повинен в один і той же час.
• Перепустка даних не допускається. Перепустка навіть одного результату спостережень небажана при прогнозуванні» тому, якщо у ваших спостереженнях відсутні результати за незначний відрізок часу, постарайтеся заповнити їх хоча б приблизними даними.

3. Перевіривши дані, можна застосовувати різні методики прогнозування. Почати я хотів би з найпростішого методу – МЕТОДУ КОВЗНОГО СЕРЕДНЬОГО

МЕТОД КОВЗНОГО СЕРЕДНЬОГО

Метод ковзного середнього застосовувати досить нескладно, проте він дуже простий для побудови точного прогнозу. При використанні цього методу прогноз будь-якого періоду є не що інше, як отримання середнього показника за кількома попередніми спостереженнями часового ряду. Наприклад, якщо ви вибрали ковзне середнє за три місяці, прогнозом на травень буде середнє значення показників за лютий, березень та квітень. Вибравши як метод прогнозування ковзне середнє за чотири місяці, ви зможете оцінити травневий показник як середнє значення показників за січень, лютий, березень та квітень.

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

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

Отже, як це робити вExcel

1. Припустимо, що у Вас є обсяги місячних продажів за останні 29 місяців. І ви хочете визначити, який обсяг продажів буде у 30 місяці. Але, якщо чесно, зовсім не обов'язково при розрахунку прогнозних значень оперувати 30 історичними значеннями, адже цей метод використовуватиме для середнього розрахунку лише кілька останніх місяців. Тому для розрахунку достатньо лише кілька минулих місяців.

2. Наводимо цю таблицю як зрозумілий Excel, тобто. щоб усі значення були в одному ряді.

3. Далі вводимо формулу розрахунку середнього за попереднім трьом (чотирьом, п'яти? як самі оберіть) значенням (див. в). Найзручніше все-таки використовуватиме розрахунку останні 3 значення, т.к. якщо враховувати більше, дані будуть занадто середні, якщо менше - не будуть точними.

4. Використовуючи функцію автозаповнення для всіх наступних значень до 30, прогнозного місяця. Таким чином, функція розрахує прогноз на червень 2010 р. Згідно з прогнозними значеннями у червні продажу становитимуть близько 408 одиниць товару. Але зверніть увагу, що якщо тенденція падіння постійна, як у нашому прикладі, розрахунок прогнозу за середньою буде трохи завищеним, або буде «відставати» від реальних значень.

Ми розглянули одну з найпростіших методик прогнозування – метод ковзного середнього. У наступних постах ми розглянемо інші, точніші і складніші методики. Сподіваюся, мій пост буде Вам корисним.