Метод ковзної середньої в Excel (Ексель). Обчислення ковзного середнього засобами Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Розрахувати коефіцієнти сезонності;
2. Вибрати період для розрахунку середньогозначення;
3. Розрахувати прогноз, тобто. середнє значення помножити на коефіцієнт сезонності;
4. Врахувати додаткові фактори, що значно впливають на продаж;

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

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

Для цього розраховуємо коефіцієнти сезонності очищені від зростання, як описано у статті «Як розрахувати коефіцієнти сезонності, очищені від зростання?» . Потім визначаємо коефіцієнти сезонності до попередніх періодів, до 1 місяця, до 2-го місяця, до 3-го місяця і т.д. залежно від цього, який період беремо середнє значення для прогнозування продажів. Наприклад, розрахуємо місячні коефіцієнти сезонності (див. вкладений файл лист "Розрахунок коефіцієнтів")

до 1 місяця:

• коефіцієнт січня - відношення січневого коефіцієнта сезонності очищеного від зростання до грудневого;

  лютого – лютневого коефіцієнта до січневого;

  березня – березень до лютого;

до 2-х місяців:

• для січня - ставлення січневого коефіцієнта сезонності до середнього значення грудня та листопада

  для лютого - лютий ділимо на середнє значення коефіцієнтів січня та грудня

  для березня - березень до середнього лютневого та січневого коефіцієнтів

до 3-х місяців:

• для визначення січневого коефіцієнта сезонності до 3-х місяців січневий коефіцієнт сезонності, очищений від зростання, ділимо на середнє значення коефіцієнтів сезонності, очищених від зростання, за грудень, листопад, жовтень;

  для лютого - коефіцієнт лютого ділимо на середнє значення коефіцієнтів листопада, грудня та січня;

  Для березня - ставлення березня до середнього значення коефіцієнтів сезонності очищених від зростання грудня, січня та лютого;

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

2. Вибираємо період розрахунку середнього значення для прогнозу за методом ковзної середньої.

Для цього робимо прогноз для останнього та передостаннього періодів, дані за який нам відомі, трьома чи більше способами для визначення відповідного періоду розрахунку середньої(Див. вкладений файл лист «Вибір періоду»). І дивимося, який із варіантів робить більш точний прогноз:

1. Розрахуємо прогноз продажів за методом ковзної середньої до 1-го місяця:

Грудень = обсяг продажів листопада помножимо на грудневий коефіцієнт сезонності до попереднього місяця.

1. Розрахуємо прогноз продажів за методом ковзної середньої до 2-ум місяців:

Грудень = середній обсяг продажів за жовтень та листопад помножимо на грудневий коефіцієнт сезонності до 2-х місяців.

1. Розраховуємо прогноз за методом ковзної середньої до 3-х місяців:

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

Наразі ми розрахували прогноз трьома способами на грудень. Аналогічно розрахуємо на листопад.

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

3. Розрахуємо прогноз продажів за методом ковзної середньої.

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

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

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

4. Додаткові чинники, які варто врахувати під час розрахунку прогнозу продажів.

Для підвищення точності прогнозу важливо:

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

Точних вам прогнозів!

Програма Forecast4AC PRO розрахує прогноз за методом ковзної середньої одночасно більш ніж для 1000 часових рядіводним натисканням клавіші, значно заощадивши ваш час, одним з 4-х способів:

До середнього за два попередні періоди

До середнього за три попередні періоди

До середнього за 4 попередні періоди

Подвійна середня до 3 та 4 попередніх періодів

Приєднуйся до нас!

Завантажуйте безкоштовні програми для прогнозування та бізнес-аналізу:

• Novo Forecast Lite- автоматичний розрахунок прогнозув Excel.
• 4analytics - ABC-XYZ-аналізта аналіз викидів у Excel.
• Qlik Sense Desktop та QlikViewPersonal Edition - BI-системи для аналізу та візуалізації даних.

Тестуйте можливості платних рішень:

• Novo Forecast PRO- прогнозування Excel для великих масивів даних.

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

Для того, щоб відстежувати поведінку натовпу на ринку, існує стародавній індикатор MACD. Його абревіатура розшифровується як moving average convergence-divergence або якщо російською сходження-розбіжність ковзаючих середніх (маються на увазі історичні значенняцін на акції чи інші інструменти).

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

Для побудови гістограми MACD використовуємо excel.

1) Спочатку нам знадобляться історичні дані для аналізу. У попередній статті я наводив приклад, де такі дані можна роздобути. Наслідуємо цей приклад і перейдемо на брокерську сторінку експорту даних:

Виставивши вимоги до формату даних, що скачуються, отримуємо файл з даними формату csv, який розуміє excel. Також історичні дані по інструменту, що цікавить нас, можна скачати на сайті брокера ЗАТ «ФІНАМ по цій засланні .

2) дані слід відформатувати як описано у .

Зрештою має вийти ось такий набір:

3) Тепер створимо новий листу книзі excel для розрахунків та побудови графіка технічного аналізу. Так і назвемо цей лист: "Розрахунок MACD". Потім скопіюємо на цей лист стовпець з датами та стовпець з даними ціни закриття . Ось так:

4) Тепер розрахуємо експоненційну ковзну середню з вікном у 12 днів (EMA 12). ЕМА 12 розраховується за формулою:

Закладемо цю формулу в стовпець праворуч від ціни закриття . Для цього запис у комірку починаємо з символу «=», що повідомляє процесору excel про те, що буде вводиться формула. Для першої комірки формула трохи інша, ніж для інших осередків, через те, що замість вчорашньої EMA12 слід підставити сьогоднішню ціну закриття. Ось так:

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

Повинно вийти так:

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

Тепер аналогічним чином розрахуємо експоненційну ковзну середню з вікном 26 днів (EMA 26). ЕМА 26 розраховується за формулою:

Закладемо цю формулу в стовпець праворуч від розрахованої EMA12. Для цього запис у комірку починаємо з символу «=», що повідомляє процесору excel про те, що буде вводиться формула. Для першого осередку формула трохи інша ніж інших осередків, через те, що замість вчорашньої EMA26 слід підставити сьогоднішню ціну закриття. Ось так:

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

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

Вітаю! Ми з вами впоралися із розрахунком експоненційних середніх. Тепер слід отримати швидку лінію MACD. Для цього потрібно з EMA12 відняти EMA26. Заб'ємо цю формулу в наступний стовпець праворуч:

Тепер потрібно вирахувати дев'ятиденну експоненційну ковзну середню для «швидкої» лінії MACD. Отримана лінія називатиметься "сигнальною" лінією MACD. Розрахунок зробимо за такою формулою:

Аналогічно забиваємо формулу розрахунку в excel в комірку правіше «швидкої» лінії MACD:

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

І нарешті ми можемо розрахувати останній стовпець даних для побудови гістограми MACD. Значеннями цього стовпця для побудови гістограми є різниця між швидкою і сигнальною лініями MACD. Вбиваємо останню формулу розрахунку даних для побудови гістограми:

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

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

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

А тицьнувши лівою кнопкою миші в шкалу зі значеннями цін, можна змінити формат даних осі побудови графіка. Після такого стукання шкала значень вертикальної (у нашому випадку) осі виділяється прямокутною рамкою. Як тільки з'явилася така рамка, слід натиснути праву кнопку миші для виклику контекстного меню. У контекстному менюлівою кнопкою миші вибираємо рядок<Формат оси…>, ось так:

У діалоговому вікні налаштування параметрів осі графіка налаштовуємо мінімальне значення (80) і максимальне (160). Це верхні два рядки в діалоговому вікні, що відкрилося. На малюнку нижче показано необхідне становищерадіокнопок та вписані значення 80 та 160 у відповідні рядки:

Під вікном графіка цін вставляємо вікно майбутньої гістограми MACD. У головному меню вибираємо вкладку<<Вставка>> потім підміню<<Гистограмма>> і в меню, що випадає, вибираємо лівий верхній значок гістограми, цей значок підсвічений жовтим на скрін-шоті нижче:

Головне, перед вставкою другого графіка не забути зняти виділення з першого. Інакше може статися заміщення одного графіка іншим, а нам потрібні обидва графіки.

Перед викликом меню<<Гистограмма>> непогано навести курсор на комірку А16 і натиснути ліву кнопку миші. Після вставки гістограми нам необхідно вказати наш стовпець із розрахунковими даними гістограми MACD. Для цього слід навести курсор миші на гістограму та натиснути праву кнопку миші для виклику контекстного меню керування діаграмою. У контекстному меню, що відкрилося, вибираємо пункт<Выбрать данные>:

Після натискання кнопки<<Добавить>> у попередньому вікні слід набрати найменування нашого графіка — «MACD», а нижньому ряду натиснути кнопочку праворуч від ряду:

Після натискання кнопки праворуч від нижнього ряду відкривається вузьке віконце «Зміна ряду». Не закриваючи цього вікна, переходимо за допомогою миші на лист з назвою MACD:

Після того, як стовпець з даними охоплений тонкою пунктирною лінією у вікні «Зміна ряду», слід натиснути кнопочку праворуч. Після цього відкриється вікно "Зміна ряду" з двома рядками. Ось у цьому віконці можна натиснути кнопку<> та перейти до вікна публікації графіка:

Повернувшись на аркуш із найменуванням «ГРАФІКИ» у вікні вибору даних для побудови гістограми теж натискаємо кнопку<>:

Можна трохи пограти з розміром вікон для графіків і отримати той результат, який здається наочнішим:

А ось ті ж графіки, побудовані торговою системою QUIK. Схоже, вийшло у нас з вами?

Дорогий читачу! Якщо ти вирішив побудувати ці графіки і в тебе щось не виходить - залиш своє питання в коментарях і разом ми обов'язково розберемося і навчимося будувати графіки в excel.

Вихідні файли excel з яких зроблено скріншоти і в яких є побудовані графіки можна завантажити по .

Метод ковзної середньої – це статистичний інструмент, за допомогою якого можна вирішувати різного родузавдання. Зокрема, він часто використовується при прогнозуванні. У програмі Excel для вирішення цілого ряду завдань також можна використовувати цей інструмент. Давайте розберемося, як використовується ковзна середня в Екселі.

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

Спосіб 1: Пакет аналізу

Пакет аналізує надбудовою Excel, яка за замовчуванням відключена. Тому насамперед потрібно її включити.

Після цієї дії пакет «Аналіз даних»активовано, і відповідна кнопка з'явилася на стрічці у вкладці «Дані».

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

1. Переходимо у вкладку «Дані»і тиснемо на кнопку «Аналіз даних», яка розміщена на стрічці інструментів у блоці «Аналіз».



2. Відкривається перелік інструментів, які доступні в Пакет аналізу. Вибираємо з них найменування «Слизьке середнє»і тиснемо на кнопку "OK".



3. Запускається вікно введення даних для прогнозування методом ковзної середньої.

   В полі "Вхідний інтервал"вказуємо адресу діапазону, де розташована щомісячно сума виручки без осередку, дані в якій слід розрахувати.

   В полі "Інтервал"слід вказати інтервал обробки значень методом згладжування. Для початку давайте встановимо значення згладжування у три місяці, а тому вписуємо цифру «3».

   В полі «Вихідний інтервал»потрібно вказати довільний порожній діапазон на аркуші, де будуть виводитися дані після їх обробки, який повинен бути на одну комірку більше вхідного інтервалу.

   Також слід встановити галочку біля параметра «Стандартні похибки».

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

   Після того, як всі налаштування внесені, тиснемо на кнопку "OK".



4. Програма виводить результат обробки.



5. Тепер виконаємо згладжування за період у два місяці, щоб виявити, який результат є коректнішим. Для цього знову запускаємо інструмент «Слизьке середнє» Пакет аналізу.

   В полі "Вхідний інтервал"залишаємо ті ж значення, що й у попередньому випадку.

   В полі "Інтервал"ставимо цифру «2».

   В полі «Вихідний інтервал»вказуємо адресу нового порожнього діапазону, який, знову ж таки, повинен бути на одну комірку більше вхідного інтервалу.

   Інші налаштування залишаємо колишніми. Після цього тиснемо на кнопку "OK".



6. Після цього програма робить розрахунок і виводить результат на екран. Для того, щоб визначити, яка з двох моделей точніша, нам потрібно порівняти стандартні похибки. Чим менше даний показник, Тим вище ймовірність точності отриманого результату. Як бачимо, за всіма значеннями стандартна похибка при розрахунку двомісячної ковзної менше, ніж аналогічний показник за 3 місяці. Таким чином, прогнозованим значенням на грудень вважатимуться величину, розраховану методом ковзання останній період. У разі це значення 990,4 тис. рублів.

Спосіб 2: використання функції СРЗНАЧ

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

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

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

1. Виділяємо комірку в порожній стовпчику в рядку за березень. Далі тиснемо на значок "Вставити функцію", що розміщений поблизу рядка формул.



2. Активується вікно Майстри функцій. у категорії «Статистичні»шукаємо значення «СРЗНАЧ», виділяємо його та клацаємо по кнопці "OK".



3. Запускається вікно аргументів оператора Відмінник. Синтаксис у нього такий:

   СРЗНАЧ(число1; число2; ...)

   Обов'язковим є лише один аргумент.

   У нашому випадку, у полі «Число1»ми повинні вказати посилання на діапазон, де вказано дохід за два попередні періоди (січень та лютий). Встановлюємо курсор у полі та виділяємо відповідні осередки на аркуші в стовпці "Дохід". Після цього тиснемо на кнопку "OK".



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



5. Отримуємо розрахунок результатів середнього значення за два попередні місяці до кінця року.



6. Тепер виділяємо комірку у наступному порожньому стовпці у рядку за квітень. Викликаємо вікно аргументів функції Відмінниктим самим способом, який був описаний раніше. В полі «Число1»вписуємо координати осередків у стовпці "Дохід"з січня до березня. Потім тиснемо на кнопку "OK".



7. За допомогою маркера заповнення копіюємо формулу в комірки таблиці, розташовані нижче.



8. Отже, значення ми підрахували. Тепер, як і в попередній раз, нам потрібно буде з'ясувати, який вид аналізу якісніший: зі згладжуванням у 2 або 3 місяці. Для цього слід розрахувати середнє квадратичне відхилення та деякі інші показники. Для початку розрахуємо абсолютне відхилення, скориставшись стандартною функцією Excel ABSяка замість позитивних або негативних чиселповертає їхній модуль. Це значення дорівнює різниці між реальним показником виручки за обраний місяць і прогнозованим. Встановлюємо курсор у наступний порожній стовпець у рядок за травень. Викликаємо Майстер функцій.



9. у категорії «Математичні»виділяємо найменування функції «ABS». Тиснемо на кнопку "OK".



10. Запускається вікно аргументів функції ABS. У єдиному полі «Кількість»вказуємо різницю між вмістом осередків у стовпцях "Дохід"і "2 місяці"за травень. Потім тиснемо на кнопку "OK".



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



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



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



14. Далі розраховуємо середнє значення всіх даних абсолютного відхилення за допомогою функції Відмінник.



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



16. Але відносне відхилення прийнято відображати у відсотковому вигляді. Тому виділяємо відповідний діапазон на аркуші, переходимо у вкладку «Головна», де в блоці інструментів «Кількість»у спеціальному полі форматування виставляємо процентний формат. Після цього результат підрахунку відносного відхилення відображається у відсотках.



17. Аналогічну операцію щодо підрахунку відносного відхилення проробляємо і з даними із застосуванням згладжування за 3 місяці. Тільки в цьому випадку для розрахунку як ділимо використовуємо інший стовпець таблиці, який має назву «Абс. відкл (3м)». Потім переводимо числові значення у відсотковий вигляд.



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



19. Тепер ми підійшли до розрахунку середнього квадратичного відхилення. Цей показник дозволить нам безпосередньо порівняти якість розрахунку при використанні згладжування за два та за три місяці. У нашому випадку середнє квадратичне відхилення дорівнюватиме кореню квадратному із суми квадратів різниць фактичної виручки та ковзної середньої, поділеної на кількість місяців. Для того, щоб зробити розрахунок у програмі, ми маємо скористатися цілим рядом функцій, зокрема КОРІНЬ, СУМКВРАЗНі РАХУНОК. Наприклад, для розрахунку середнього квадратичного відхилення при використанні лінії згладжування за два місяці у травні буде в нашому випадку застосовуватись формула наступного виду:

    КОРІНЬ(СУМКВРАЗН(B6:B12;C6:C12)/РАХУНОК(B6:B12))

    Копіюємо її в інші осередки стовпця з розрахунком середнього квадратичного відхилення за допомогою маркера заповнення.



20. Аналогічну операцію з розрахунку середнього квадратичного відхилення виконуємо і для ковзної середньої за 3 місяці.



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



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



23. Таким чином, прогнозований показник доходу підприємства за грудень становитиме 990,4 тис. рублів. Як бачимо, це значення повністю збігається з тим, яке ми отримали, розраховуючи за допомогою інструментів Пакет аналізу.

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

Ковзне середнє або просто МА (Moving Average), є середньоарифметичним ціновим рядом. Загальна формулаковзного середнього така:

Де:
МА - ковзне середнє;
n- період усереднення;
Х – значення ціни акції.

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

Спрогнозуємоза допомогою моделі ковзного середнього вартість акційкомпанії Аерофлот (AFLT). Для цього експортуємо котирування акції із сайту finam.ru за половину 2009 року. Усього буде 20 значень.

Графік вартості акцій Аерофлотуза вибраний проміжок часу наведено нижче.

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

Побудуємо ковзне середнєз періодом усереднення три місяці MA(3). Для розрахунку значення ковзного середнього для акції скористаємося формулою Excel.

СРЗНАЧ(C2:C4)

У колонці "D" розраховані значення ковзного середнього з періодом усереднення 3.

Після розрахунку ковзного середнього побудуємо прогноз на 3 періодивперед (три місяці вперед). Скористаємося формулою для визначення значення ціни акції, перше прогнозне значення дорівнюватиме останньому значенню ковзного середнього. Помаранчева область - це область прогнозів. С22 дорівнюватиме значенню ковзного середнього, тобто:

С22 = D21 С23 = D22 і т.д.

Від нових прогнозних даних вартості акції розраховується наступне середнє, що ковзає.

Побудуємо прогнозні значенняпо ковзному середньому для акцій Аерофлоту на три місяці наперед. Нижче наведено графік та прогнозні значення акції.