Prognozowanie sprzedaży i popytu w handlu: metody i przykład w Excelu

Na czym polega prognozowanie sprzedaży w handlu

Prognoza sprzedaży to liczba – w sztukach albo w złotych – którą firma przyjmuje jako najbardziej prawdopodobny wynik w przyszłym okresie. W handlu z tej liczby wynikają niemal wszystkie pozostałe plany: budżet zakupów, dostawy do sklepów, zatrudnienie w szczycie sezonu i plan promocji. Dlatego prognoza nie jest celem sprzedażowym. Cel mówi, ile chcemy sprzedać, prognoza – ile realnie sprzedamy przy obecnych założeniach.

W podręcznikach prognozowania wymienia się trzy funkcje prognoz: przygotowanie decyzji (funkcja preparacyjna), pobudzanie do działań, które wzmocnią lub osłabią przewidywany wynik (funkcja aktywizująca), oraz ostrzeganie przed zmianami (funkcja informacyjna). W handlu wszystkie trzy widać co tydzień: prognoza poniżej planu uruchamia promocję, a prognoza powyżej zapasu – uzupełnienie.

Metody prognozowania sprzedaży

Metody prognozowania dzielą się na ilościowe, oparte na historii sprzedaży, i jakościowe, oparte na ocenie ekspertów. W handlu najczęściej łączy się je: model liczy bazę, a zespół handlowy koryguje ją o to, czego w danych jeszcze nie ma, na przykład nową kolekcję czy zmianę cen.

Wybór metody zależy od poziomu, na którym się prognozuje. Im wyższy poziom agregacji, tym trafniejsza prognoza: sprzedaż całej kategorii w tygodniu da się przewidzieć znacznie lepiej niż sprzedaż jednego rozmiaru jednego modelu w jednym sklepie. Dlatego w handlu stosuje się podejście z góry na dół: prognoza kategorii, a potem rozbicie na modele, sklepy i rozmiary według udziałów.

Prognozowanie popytu w handlu modowym

Prognoza sprzedaży i prognoza popytu to nie to samo. Sprzedaż jest ograniczona zapasem: jeśli rozmiar M skończył się w trzecim tygodniu, sprzedaż w kolejnych tygodniach spada do zera, choć klienci nadal go szukają. Popyt to sprzedaż, która byłaby możliwa bez braków. Kto prognozuje wprost z historii sprzedaży, co sezon kupuje mniej tam, gdzie brakowało towaru, i zaniża plan w najlepszych modelach.

Przykład: model sprzedawał się przez pięć tygodni po 20 sztuk tygodniowo przy pełnej dostępności rozmiarów, a w dwóch kolejnych tygodniach po 8 sztuk, bo zabrakło M i L. Surowa średnia z siedmiu tygodni to (100 + 16) ÷ 7 ≈ 16,6 sztuki. Popyt lepiej szacować na podstawie tygodni z pełną dostępnością, czyli na około 20 sztuk tygodniowo. Różnica ponad 15% przeniesiona na plan zakupów oznacza kolejne braki w następnym sezonie.

Moda ma też kilka cech, które utrudniają stosowanie podręcznikowych metod:

• Nowe modele bez historii: większość kolekcji to nowe produkty, więc prognozę buduje się dla kategorii i poziomu cenowego, a model przypisuje się do analogu z poprzedniego sezonu, np. „sukienka midi w cenie 199–249 zł”.
• Krótki cykl życia: model sprzedaje się kilka–kilkanaście tygodni, więc prognozuje się krzywą sprzedaży w cyklu życia, a nie jedną średnią.
• Sezonowość i pogoda: przesunięcie zimna o dwa tygodnie zmienia rozkład sprzedaży okryć, choć suma w sezonie może być podobna.
• Promocje i obniżki: tygodnie wyprzedaży zawyżają sprzedaż w sztukach. Przy prognozie bazowej oznacza się je osobno i nie traktuje jako normy.
• Zmiany sieci: nowe i zamknięte sklepy zmieniają sumę sprzedaży, dlatego porównuje się sprzedaż na porównywalnej bazie sklepów (like-for-like).

W praktyce w modzie powstają więc dwie prognozy o różnym horyzoncie. Prognoza przedsezonowa, przygotowywana kilka miesięcy przed startem kolekcji, wyznacza budżet zakupów kategorii i opiera się na historii kategorii oraz analogach. Prognoza w sezonie jest aktualizowana co tydzień na podstawie pierwszych tygodni sprzedaży nowych modeli: jeśli model po trzech tygodniach sprzedaje się dwa razy szybciej niż jego analog, prognozę do końca sezonu podnosi się i sprawdza, czy da się dokupić towar. Ta druga prognoza decyduje o uzupełnieniach, przesunięciach między sklepami i momencie obniżek.

Prognoza sprzedaży w Excelu krok po kroku

Dla większości marek nie trzeba zaczynać od specjalnego oprogramowania. Dobrze przygotowany arkusz daje prognozę kategorii, którą da się wytłumaczyć zespołowi zakupów, a to na początku jest ważniejsze niż kilka punktów procentowych trafności.

• Krok 1. Dane: jedna kolumna z datą (tydzień lub miesiąc), druga ze sprzedażą w sztukach. Okresy muszą być równe i bez luk; co najmniej dwa pełne lata, jeśli sprzedaż jest sezonowa.
• Krok 2. Czyszczenie: oznaczenie tygodni z brakami towaru, jednorazowych promocji i zamknięć sklepów, a w razie potrzeby zastąpienie ich wartością szacowanego popytu.
• Krok 3. Prognoza: funkcja Arkusz prognozy na karcie Dane albo formuła PROGNOZA.ETS, która sama wykrywa sezonowość. Dla danych bez sezonowości wystarczy PROGNOZA.LINIOWA.
• Krok 4. Korekta: dodanie znanych zdarzeń, których nie ma w historii – nowe sklepy, zmiana cen, większa kampania.
• Krok 5. Test: prognoza dla okresu, który już się zakończył, i porównanie z rzeczywistą sprzedażą.

C26  prognoza z sezonowością:  =PROGNOZA.ETS(A26;$B$2:$B$25;$A$2:$A$25;12)   (FORECAST.ETS)
D26  trend liniowy:            =PROGNOZA.LINIOWA(A26;$B$2:$B$25;$A$2:$A$25)  (FORECAST.LINEAR)
E26  średnia ruchoma 3 mies.:  =ŚREDNIA(B23:B25)                             (AVERAGE)

PROGNOZA.ETS to wygładzanie wykładnicze z sezonowością. Ostatni argument, 12, mówi Excelowi, że cykl ma dwanaście miesięcy; przy danych tygodniowych wpisuje się 52. Arkusz prognozy korzysta z tej samej metody, a dodatkowo rysuje wykres z przedziałem ufności, który dobrze pokazuje zespołowi, jak duża jest niepewność.

Prostszą i bardzo czytelną alternatywą są indeksy sezonowe. Indeks pokazuje, ile razy sprzedaż w danym okresie jest wyższa lub niższa od średniej. Liczy się go jako średni udział okresu w sprzedaży roku pomnożony przez liczbę okresów. Prognozę roczną dzieli się potem na okresy według indeksów.

Sprzedaż roku 2 wyniosła 9000 sztuk, więc przy wzroście 10% prognoza roczna to 9900 sztuk, czyli średnio 2475 na kwartał. Prognoza na IV kwartał to 2475 × 2,32 ≈ 5742 sztuki. Suma czterech kwartałów znów daje 9900. Tę samą logikę stosuje się do tygodni: wtedy indeksów jest 52, a zamiast kwartałów w tabeli są tygodnie sezonu.

Jak mierzyć trafność prognozy sprzedaży

Prognozy, której trafności nikt nie mierzy, nie da się poprawić. Wystarczą dwa wskaźniki liczone co miesiąc lub po każdym sezonie. MAPE, czyli średni bezwzględny błąd procentowy, mówi, o ile średnio prognoza myli się w górę lub w dół. Obciążenie (bias) mówi, czy myli się systematycznie w jedną stronę.

D2   błąd bezwzględny %:  =MODUŁ.LICZBY(C2-B2)/B2        (ABS)
D14  MAPE:                =ŚREDNIA(D2:D13)
D15  obciążenie (bias):   =SUMA(C2:C13)/SUMA(B2:B13)-1

Obciążenie jest dla handlu ważniejsze, niż się wydaje. Prognoza, która co sezon jest o kilka procent za wysoka, przekłada się na nadwyżki i obniżki, a prognoza stale za niska – na braki i utraconą sprzedaż. Jako regułę kciuka przyjmuję, że obciążenie powinno oscylować wokół zera; jeśli przez kilka okresów z rzędu ma ten sam znak, poprawiam założenia, zanim zacznę szukać bardziej zaawansowanej metody.

Prognoza kategorii to punkt wyjścia do planu zakupów i budżetu open to buy, a jej podział na rozmiary opisuję w artykule o krzywej rozmiarów. Jeśli chcesz zbudować model prognozy sprzedaży dla swojej marki w Excelu lub Power BI, umów konsultację – odpowiadam w ciągu jednego dnia roboczego.