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

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

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

Що таке метод лінійного тренду?

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

Лінійна модель тренда — найпростіша, інтуїтивно зрозуміла та зустрічається частіше. Вона описує рівномірні зміни показника в часі. З лінійним трендом впорається кожен, достатньо лише вміти користуватися стандартними формулами Excel.

Рівняння тренду має такий вигляд — y(x)=a+bx, де:

• y — це послідовність значень, які ми аналізуємо (наприклад, продаж по місяцях);
• x — номер періоду (порядковий номер місяця);
• a – точка перетину з віссю на графіку (мінімальний рівень);
• b – це значення, на яке збільшується наступне значення тимчасового ряду.

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

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

Спосіб 1. Розрахунок лінійного тренду за допомогою функції ЛИНЕЙН

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

Крок 1. Уявімо вихідні дані у вигляді простої таблиці

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

Крок 2. Розраховуємо значення лінійного тренду — y(x)=a+bx

З таблиці вище:

• у — це обсяг продажів за попередній рік;
• х — № періоду.

Знайдемо значення параметрів а і b за допомогою функції ЛИНЕЙН, де перший параметр функції — діапазон значень y, другий — діапазон значень x. У третій і четвертий параметри вносимо 1 і 0 відповідно:

Примітка для тих, хто працює в Exсel: щоб розрахувати одразу 2 коефіцієнти  лінійного тренду (a) і (b), необхідно встановити курсор в осередок з формулою, виділити сусідню праворуч і натиснути клавішу F2, а потім одночасно — клавіші CTRL + SHIFT + ENTER. Google Таблиці з цим справляються  за замовчуванням.

Крок 3. Для кожного періоду розраховуємо значення лінійного тренду

У відоме рівняння y(x)=a+bx підставляємо розраховані коефіцієнти:

Крок 4. Визначаємо індекс сезонності для кожного місяці (відношення продажів місяця до середньої величини)

Фактично, потрібно кожен обсяг продажів на місяць розділити на середній обсяг продажів за рік:

Ми підготували всі необхідні дані для складання прогнозу.

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

Послідовність нумерації періодів продовжуємо:

Крок 6. На основі отриманих даних, складаємо прогноз продажів на наступний рік з урахуванням сезонності

Готово!

Спосіб 2. Швидкий розрахунок прогнозу, за допомогою функцій ТЕНДЕНЦІЯ та ПРЕДСКАЗ

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

Примітка. З функцією ЛИНЕЙН ми познайомилися не дарма, далі в цьому переконаєтеся.

Крок 1. Розраховуємо значення лінійного тренда за допомогою стандартної функції ТЕНДЕНЦІЯ

Параметри функції:

• відомі значення y — це обсяги продажів за аналізований період;
• відомі значення x — це номери періодів x для відомих значень обсягів продажів y;
• нові значення x — це номери періодів, для яких ми хочемо розрахувати значення лінійного тренду;
• константа — ставимо 1. Необхідно для того, щоб значення тренду розраховувалися з урахуванням коефіцієнту (a) для лінійного тренду y=a+bx.

Примітка для тих, хто працює в Excel. Щоб розрахувати значення тренда для всього часового діапазону, виділяємо діапазон комірок, рівний діапазону з новими значеннями X, з формулою в першій клітинці та натискаємо клавішу F2, а потім — клавіші CTRL + SHIFT + ENTER.

Крок 2. Розраховуємо значення лінійного тренду за допомогою стандартної функції ПРЕДСКАЗ

Параметри функції:

• значення x — номер періоду, для якого розраховуємо значення тренду;
• відомі значення y — обсяги продажів за аналізований період;
• відомі значення x — це номери періодів x для відомих значень обсягів продажів y.

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

Як можна виправити прогнозні значення тренду?

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

Скорегувати майбутні значення тренда — можливо. Зробити це досить просто, на основі розрахунку першим способом, — за допомогою функції ЛИНЕЙН.

Ми можемо впливати на параметри a і b лінійного рівняння y = a + bx, тим самим змінюючи значення:

• якщо змінюємо значення «a» лінійного тренду, то змінюється рівень тренду (піднімається паралельно);
• якщо змінюємо значення «b» лінійного тренду, то збільшуємо нахил тренду;

Таким чином, ми можемо змінювати нахил і рівень тренду, як окремо, так і одночасно (докладніше в прикладі):

Висновки

1. Метод, який я описала в статті, простий і зручний в застосуванні. Складно зробити помилку і отримати далекі від реальності дані.
2. Метод не прив’язаний до спеціалізованих програм і посилається на ваші реальні дані.
3. Для прогнозу потрібна невелика статистика. Але пам’ятайте,  більше даних, точніше прогноз.
4. Даний метод враховує сезонність бізнесу, як важливий фактор впливу на майбутні показники ефективності.
5. Є можливість самостійно коригувати тренд, з огляду на можливі сторонні чинники.

Часті питання про прогнозування бізнес-показників:

1️⃣ Як розрахувати лінійний тренд?

Рівняння лінійного тренду y=ax+b, де y — це обсяги продажу, а x — місяці.

2️⃣ Як розрахувати прогноз продажу за формулою?

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

3️⃣ Що показує рівняння лінії тренду?

Рівняння лінії тренду — це  формула, яка знаходить лінію, що відповідає точкам даних. Величина достовірності апроксимації (R^2) вимірює надійність лінії тренду. Чим ближче R2 до 1, тим краще лінія тренду відповідає даних.

4️⃣ Як додати середнє значення на графік?

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

Популярні запити: як порахувати тренд, лінійний прогноз, лінійний тренд excel, лінійний тренд формула, лінійний прогноз це, як розрахувати лінію тренда