Jak obliczyć prognozę średniej ruchomej w programie excel

Prognoza średniej ruchomej wykorzystuje średnią ruchomą z kilku poprzednich okresów do prognozowania wartości następnego okresu.

Poniższy przykład pokazuje, jak obliczyć prognozę średniej ruchomej w programie Excel.

Przykład: Jak obliczyć prognozę średniej ruchomej w programie Excel

Załóżmy, że mamy następujący zestaw danych, który pokazuje całkowitą sprzedaż zrealizowaną przez firmę przez 20 kolejnych dni:

Załóżmy, że chcemy obliczyć 5-dniową prognozę średniej ruchomej.

Oznacza to, że jako prognozę na kolejny dzień przyjmiemy średnią wartość sprzedaży z ostatnich 5 dni.

W tym celu w komórce C8 możemy wpisać następującą formułę:

 =AVERAGE( B2:B6 )

Możemy następnie kliknąć i przeciągnąć tę formułę do każdej pozostałej komórki w kolumnie C:

Wartości w kolumnie C reprezentują prognozowane 5-dniowe średnie wartości sprzedaży.

Przykładowo prognozowana wartość sprzedaży na dzień 1.06.2023 wynosi 11 .

Aby określić ilościowo, jak nasze przewidywane wartości odpowiadają rzeczywistym wartościom sprzedaży, możemy obliczyć MAPE (średni bezwzględny błąd procentowy), korzystając z następującego wzoru:

MAPE = (1/n) * Σ(|rzeczywista – prognoza| / |rzeczywista|) * 100

Złoto:

• Σ – symbol oznaczający „sumę”
• n – wielkość próby
• real – rzeczywista wartość danych
• prognoza – oczekiwana wartość danych

MAPE jest powszechnie stosowany, ponieważ jest łatwy do interpretacji i wyjaśnienia. Na przykład wartość MAPE wynosząca 11,5% oznacza, że średnia różnica między wartościami przewidywanymi a wartościami rzeczywistymi wynosi 11,5%.

Im niższa wartość MAPE, tym lepiej model jest w stanie przewidzieć wartości.

Aby obliczyć MAPE dla tego modelu prognozy, możemy wpisać następującą formułę w komórce D7 :

 =ABS( B7 - C7 )/ B7 *100

Możemy następnie kliknąć i przeciągnąć tę formułę do każdej pozostałej komórki w kolumnie D:

Na koniec możemy wpisać następującą formułę w komórce D22 , aby uzyskać MAPE dla tego modelu prognozy:

 =AVERAGE( D7:D21 )

Poniższy zrzut ekranu pokazuje, jak zastosować tę formułę w praktyce:

Można zauważyć, że MAPE tego modelu wynosi około 19,12% .

Mówi nam to, że średnia różnica pomiędzy wartościami sprzedaży przewidywanymi przez 5-dniową średnią ruchomą a rzeczywistymi wartościami sprzedaży wynosi 19,12%.

Jeśli chcemy, możemy następnie powtórzyć ten proces, stosując inny przedział, na przykład 10-dniową prognozę średniej ruchomej, aby określić, czy prognoza ta prowadzi do niższej wartości MAPE.

Dodatkowe zasoby

Poniższe samouczki wyjaśniają, jak wykonywać inne typowe zadania w programie Excel:

Jak obliczyć ważoną średnią ruchomą w programie Excel
Jak obliczyć średnią skumulowaną w programie Excel
Jak obliczyć wykładniczą średnią ruchomą w programie Excel