1. Ша г 1. Щоб прогнозувати попит на товари, з оббирає дані про продажі компанії
2. Крок 2. Робимо прогноз попиту на продукцію на заданий період
3. Крок 3. Робимо розрахунок коефіцієнта сезонності для прогнозу попиту
4. Крок 4. Коректуємо прогноз попиту на продукцію, враховуючи сезонність
5. Крок 5. Розраховуємо відхилення і будуємо два сценарії
6. Метод ефективний для прогнозування продажів малої кількості SKU
7. Метод ідеальний для аналізу продажів за зафіксованими показниками

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

Замість громіздких формул для прогнозу попиту на продукцію ми використовуємо один графік в Excel, який будуємо виходячи з даних про продажі компанії. Алгоритм вивели самостійно, спираючись на поради знайомих бізнесменів і матеріали з інтернету. За допомогою графіка прогнозуємо продажу на місяць, кілька місяців або рік. Щоб повторити досвід, вам буде потрібно версія Excel 2003-2016 роки. Крім того, в кінці статті ви знайдете альтернативний спосіб, який дозволить побудувати прогноз за кілька хвилин. Однак він підходить виключно для версії Excel 2016 року.

ТОП-5 найбільш затребуваних статей для комерсанта:

Ша г 1. Щоб прогнозувати попит на товари, з оббирає дані про продажі компанії

Щоб приступити до аналізу, вам знадобляться дані про продажі компанії за весь період її існування. Чим більше інформації, тим точніше прогноз. У нас, наприклад, є відомості про продажі з січня 2013 року по серпень 2015 го. Заносимо їх в таблицю (рисунок 1).

Крок 2. Робимо прогноз попиту на продукцію на заданий період

Щоб спрогнозувати продажу, наприклад, на місяць або на майбутній рік, використовуємо функцію «ПРЕДСКАЗ» в Excel. Функція заснована на лінійної регресії і призначена для прогнозування продажів, споживання товару та ін.

У осередок C34 записуємо функцію:

= ПРЕДСКАЗ (x; ізвестние_значенія_у; ізвестние_значенія_х),

де

х - дата, значення для якої необхідно передбачити (осередок A34);

відомі значення y - посилання на комірки таблиці з сумами продажів за певні періоди (B2: B33). У рядку з формулою виділяємо діапазон B2: B33 і натискаємо кнопку F4, щоб він закріпився. В результаті отримуємо ($ B $ 2: $ B $ 33);

відомі значення x - посилання на комірки таблиці з даними продажів за певні періоди ($ A $ 2: $ A $ 33).

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

Крок 3. Робимо розрахунок коефіцієнта сезонності для прогнозу попиту

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

= (($ B $ 2: $ B $ 13 + $ B $ 14: $ B $ 25) / СУММ ($ B $ 2: $ B $ 25)) * 12

Далі копіюємо формулу і вставляємо в осередку F2: F13 як формулу масиву. Завершуємо введення сполучення клавіш: Ctrl + Shift + Enter. Якщо цього не зробити, функція поверне значення помилки # значить! В результаті для січня отримаємо коефіцієнт +0,974834224106574, для лютого - +0,989928632237843 і т. Д. Для наочності можна призначити осередкам процентний формат. Правою кнопки миші вибираємо «Формат ячеек», потім вкладку «Число» і далі вкладку «Процентний, два знака після коми».

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

Додамо розраховані коефіцієнти в наявну функцію «ПРЕДСКАЗ» (осередки C34: C45):

= ПРЕДСКАЗ (A34; $ B $ 2: $ B $ 33; $ A $ 2: $ A $ 33) * ІНДЕКС ($ F $ 2: $ F $ 13; МІСЯЦЬ (A34))

Щоб скорегувати продажу, враховуючи коефіцієнт, використовуємо функцію «ІНДЕКС» (малюнок 2).

Першим аргументом у функції вказуємо посилання на 12 осередків з коефіцієнтами сезонності ($ F $ 2: $ F $ 13), другим - номер місяця, щоб повернути коефіцієнт для потрібного місяця (для цього використовуємо функцію «місяць», яка повертає тільки номер місяця з вказаної дати ). Для вересня 2015 року формула індексу виглядає так:

ІНДЕКС ($ F $ 3: $ F $ 14; МІСЯЦЬ (A35))

Щоб скорегувати прогноз, потрібно значення «ІНДЕКС» помножити на значення «ПРЕДСКАЗ», яке розраховували за крок 2. Ось що ми отримаємо:

= ПРЕДСКАЗ (A34; $ B $ 2: $ B $ 33; $ A $ 2: $ A $ 33) * ІНДЕКС ({97,48%: 98,99%: 90,38%: 94,66%: 100,86%: 99,02%: 100,66%: 110,39%: 100,47%: 104,82%: 105,13%: 97,14%}; 9)

Поширюємо функцію на подальші періоди і отримуємо скоригований прогноз з урахуванням сезонності в осередках C34: С45 (рисунок 1).

Крок 5. Розраховуємо відхилення і будуємо два сценарії

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

Верхні і нижні межі коридору попиту будуємо по формулі (осередок G2 на малюнку 1):

= ДОВЕРИТ (0,05 (АЛФА); СТАНДОТКЛОН (C34: C45); РАХУНОК (C34: C45)),

де

«ДОВЕРИТ» повертає довірчий інтервал, використовуючи нормальний розподіл. Функція враховує коливання продажів компанії, включаючи сезонні.

«АЛФА» - рівень значущості для обчислення довірчого рівня. Показник 0,05 означає, що ми отримаємо прогноз з точністю 95%.

«СТАНДОТКЛОН» - стандартне відхилення генеральної сукупності. Показує, наскільки прогнозовані продажу відрізняються від реальних.

«РАХУНОК» підраховує кількість місяців, за якими ми прогнозуємо продажу.

Щоб отримати оптимістичний і песимістичний сценарії, в осередку D34 і D35 записуємо формули (рисунок 1).

Оптимістичний: = $ C34 + $ G $ 2 (додаємо до суми прогнозу суму розрахованого довірчого інтервалу)

Песимістичний: = $ C34- $ G $ 2 (віднімаємо з суми прогнозу суму довірчого інтервалу)

Щоб за отриманими даними побудувати графік, в осередку C33, D33 і E33 копіюємо значення з комірки B33. Далі виділяємо всі дані (A1: E45), переходимо на вкладку «Вставка», знаходимо вкладку «Діаграми» і потім вкладку «Графік». У підсумку отримуємо графік з коридором попиту (малюнок 3).

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

думка експерта

Метод ефективний для прогнозування продажів малої кількості SKU

Максим Люлин,

генеральний директор «Актіон-прес»

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

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

думка експерта

Метод ідеальний для аналізу продажів за зафіксованими показниками

Кирило Чихачев,

генеральний директор «МЦФЕР-прес»

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

Максимальне і мінімальне значення продажів простіше розраховувати виходячи з двох точок на початку і кінці періодів, а не шукати точки, через які повинна проходити пряма.

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

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