Як далеко прогнозувати?

Коли вас просять скласти прогноз продажів за допомогою даних Excel, одна з перших речей, які вам потрібно розглянути, — це те, наскільки далеко в майбутньому ви хочете подивитися. Деякі методи прогнозування дають вам можливість прогнозувати далі, ніж інші. На цьому малюнку показано дві методики, які дозволяють прогнозувати лише один період часу вперед.

Ковзні середні зазвичай обмежуються прогнозами на крок вперед.

Зверніть увагу на те, що відбувається, коли ви розтягуєте їх занадто далеко: як гумки, вони ламаються і ламаються на вас.

Подивіться спочатку на клітинку D5. Це середнє значення клітинок B2, B3 і B4, і це те, що прогнозує підхід ковзного середнього на вересень 2004 року. Тобто, як цей прогноз налаштовано, прогноз на вересень є середнім за червень, липень та серпень . Ви можете побачити прогноз 40 867 доларів США в клітинці D5, а саму формулу для ілюстрації в клітинці E5.

Формула в D5 копіюється та вставляється в клітинку D28, де вона надає «реальний» прогноз на серпень 2016 року. Я використовую «реальний» у тому сенсі, що я ще не бачив фактичного значення для цього місяця — моє останнє фактичне значення припадає на липень 2016 року — отже, серпень 2016 року вже позаду кінця базової лінії, і прогноз на цей місяць є реальним прогнозом. Сама формула з’являється в комірці E28.

Але якщо ви скопіюєте та вставте формулу ще на один рядок вниз, щоб спробувати отримати прогноз на вересень 2016 року, ви розтягнули його занадто далеко. Тепер він намагається усереднювати фактичні результати за червень – серпень 2016 року, а ви не маєте фактичних результатів за серпень. Через те, як працює AVERAGE Excel, він ігнорує клітинку B28, а формула повертає середнє значення B26 і B27.

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

І якщо ви переведете свій прогноз набагато нижче, він почне повертати дійсно неприємне значення помилки #DIV/0!. (Цей знак оклику покликаний привернути вашу увагу. Excel кричить на вас: «Ви намагаєтеся поділити на нуль!»)

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

Якщо ви хочете зробити прогноз на віддалі вперед, розгляньте прогноз регресії.

Зверніть увагу, що формула в клітинці D29 (формула показана в E29; значення, яке повертає формула, відображається в D29) використовує значення в клітинках B28 і D28. Але оскільки у вас ще немає фактичного на серпень, «прогноз» на вересень 2016 року є помилковим: насправді це не що інше, як прогноз на серпень, помножений на 0,7. Знову ж таки, у такому вигляді експоненціального згладжування ви обмежені прогнозом на один крок вперед.

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

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

Лінія тренда на діаграмі взята з робочого аркуша. Ви також можете отримати його з меню Chart.

Оскільки ви знаєте значення двох наступних дат, серпня та вересня 2016 року, ви можете використовувати зв’язок між датою та доходом у базовій лінії на наступні дві дати, щоб отримати прогноз. Значення прогнозу відображаються в клітинках C28 і C29 і відображаються на діаграмі як останні дві точки в ряду прогнозів.

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

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

Інший спосіб вийти за рамки підходу на один крок вперед — це сезонне згладжування. Такий підхід, який залежить від сезонного компонента у вашій базовій лінії, може підтримати прогноз на цей рік у майбутнє. Це не обов’язково так, але можливо.