Pomični prosjek znači da izračunavamo prosjek prosjeka skupa podataka koji imamo, u Excelu imamo ugrađenu značajku za izračun pokretnog prosjeka koja je dostupna na kartici za analizu podataka u odjeljku za analizu, uzima ulazni opseg i izlaz raspon s intervalima kao izlazom, izračuni na temelju pukih formula u Excelu za izračunavanje pokretnog prosjeka su teški, ali u Excelu imamo ugrađenu funkciju.
Što je pokretni prosjek u Excelu
Pomični prosjek je široko korištena tehnika u analizi vremenskih serija koja se koristi za predviđanje budućnosti. Pomični prosjeci u vremenskim serijama u osnovi se konstruiraju uzimajući prosjeke različitih sekvencijalnih vrijednosti drugih podataka vremenskih serija.
Postoje tri vrste pokretnih prosjeka, a to su jednostavni pomični prosjek, ponderirani pomični prosjek i eksponencijalni pomični prosjek u Excelu.
# 1 - Jednostavan pokretni prosjek u Excelu
Jednostavan pokretni prosjek pomaže u izračunavanju prosjeka zadnjeg broja razdoblja niza podataka. Pretpostavimo da su date cijene n razdoblja. Tada se jednostavni pomični prosjek daje kao
Jednostavan pokretni prosjek = (P1 + P2 + …. + Pn) / n
# 2 - Ponderirani pokretni prosjek u Excelu
Ponderirani pokretni prosjek daje ponderirani prosjek posljednjih n razdoblja. Ponderiranje se smanjuje sa svakom točkom podataka prethodnog vremenskog razdoblja.
Ponderirani pomični prosjek = (Cijena * faktor težine) + (Cijena prethodnog razdoblja * faktor težine-1)
# 3 - Eksponencijalni pomični prosjek u Excelu
Slično je jednostavnom pomičnom prosjeku koji mjeri trendove u određenom vremenskom razdoblju. Dok jednostavni pokretni prosjek izračunava prosjek danih podataka, eksponencijalni pomični prosjek pridaje veću težinu trenutnim podacima.
Eksponencijalni pomični prosjek = (K x (C - P)) + P
Gdje,
- K = eksponencijalna konstanta zaglađivanja
- C = trenutna cijena
- P = eksponencijalni pomični prosjek prethodnih razdoblja (za izračun prvih razdoblja koristi se jednostavni pomični prosjek)
Kako izračunati pomični prosjek u programu Excel?
Ispod su primjeri pokretnog prosjeka u Excelu.
Primjer # 1 - Jednostavan pokretni prosjek u Excelu
Za izračunavanje jednostavnog pokretnog prosjeka uzeli smo podatke o prodaji tvrtke od siječnja do prosinca za 2018. godinu. Cilj nam je izravnati podatke i znati brojku prodaje u siječnju 2019. Ovdje ćemo koristiti tromjesečni pomični prosjek.
Korak 1: Pomični prosjek siječnja, veljače i ožujka izračunava se uzimajući zbroj prodajne brojke u mjesecima i podijelivši ga s 3.
Korak 2: Odabirom na uglu D5 ćelije, a zatim samo povlačenjem i ispuštanjem dolje dobit ćete pokretni prosjek za preostala razdoblja. Ovo je izvrsna funkcija alata za popunjavanje.
Predviđanje prodaje za siječanj 2019. je 10456.66667.
Korak 3: Sad crtamo brojku prodaje i pokretni prosjek u linijskom grafikonu kako bismo razumjeli razliku u trendu. To se može učiniti na kartici za umetanje. Prvo smo odabrali niz podataka, a zatim smo iz odjeljka Grafikoni pod umetnuti linijski grafikon.
Nakon izrade grafova, može se vidjeti da je graf s pomičnim prosjekom puno izglađeniji od izvornih serija podataka.
Primjer # 2 - Jednostavno klizanje prosjeka putem kartice Analiza podataka u programu Excel
- Na kartici Podaci u grupi Analiza moramo kliknuti Analiza podataka. Slijedi snimak zaslona.
- Iz analize podataka može se pristupiti pokretnom prosjeku.
- Nakon klika na pomični prosjek, odabrali smo brojku prodaje kao ulazni raspon.
- Kliknu se oznake u prvom retku kako bi Excel razumio da prvi redak ima naziv oznake.
- Odabran je interval 3 jer želimo pokretni prosjek od tri godine.
- Odabrali smo izlazni raspon u skladu s prodajom.
- Također želimo vidjeti izlaz grafikona, u kojem ćemo moći vidjeti razliku između stvarnog i prognoziranog.
Ovaj grafikon prikazuje razliku između stvarnog i prognoziranog pokretnog prosjeka.
Primjer # 3 - Ponderirani pokretni prosjek u Excelu
Koristimo tri godine ponderirani pomični prosjek, a formula je dana na snimci zaslona.
Nakon korištenja formule dobili smo pokretni prosjek za određeno razdoblje.
Pokretni prosjek za sva ostala razdoblja dobili smo povlačenjem i ispuštanjem vrijednosti u sljedeće stanice.
Prognoza za siječanj 2019., tj. 10718,33
Sada smo uzeli linijski graf kako bismo vidjeli izravnavanje podataka. Za to smo odabrali prognozirane podatke za naš mjesec, a zatim umetnuli linijski grafikon.
Sada ćemo usporediti naše prognozirane podatke sa stvarnim podacima. Na donjim snimkama zaslona lako možemo vidjeti razliku između stvarnih podataka i predviđenih podataka. Grafikon na vrhu su stvarni podaci, a grafikon u nastavku pokretni prosjek i prognozirani podaci. Možemo vidjeti da se graf pokretnog prosjeka znatno izravnao u usporedbi s grafom koji sadrži stvarne podatke.
Primjer # 4 - Eksponencijalni pomični prosjek u Excelu
Formula za eksponencijalni pomični prosjek je S t = α.Y t-1 + (1- α) S t-1 … (1)
Gdje,
- Y t-1 = stvarno opažanje u t-1-om razdoblju
- S t-1 = jednostavni pokretni prosjek u t-1-om razdoblju
- α = faktor zaglađivanja, a on varira između .1 i .3. Što je vrijednost α bliža grafu stvarnim vrijednostima, a što je vrijednost α manja, to će grafikon biti glatkiji.
Prvo izračunavamo jednostavni pokretni prosjek, kao što je ranije prikazano. Nakon toga primjenjujemo formulu danu u jednadžbi (1). Za fiksiranje vrijednosti α za sve sljedeće vrijednosti pritisnuli smo F4.
Vrijednosti dobivamo povlačenjem i ispuštanjem sljedećih ćelija.
Sada želimo vidjeti usporedbu stvarnih vrijednosti, jednostavnog pomičnog prosjeka i eksponencijalnog pomičnog prosjeka u Excelu. To smo učinili radeći linijski grafikon.
Iz gornjeg snimka zaslona možemo vidjeti razliku između stvarne brojke prodaje, jednostavnog pomičnog prosjeka i eksponencijalnog pomičnog prosjeka u Excelu.
Stvari koje treba zapamtiti o pomičnom prosjeku u Excelu
- Jednostavni pokretni prosjek može se izračunati pomoću PROSJEČNE funkcije u Excelu
- Pomični prosjek pomaže u izravnavanju podataka
- Sezonski prosjeci često se nazivaju sezonskim indeksom
- Eksponencijalni pomični prosjek u Excelu daje veću težinu nedavnim podacima od jednostavnog pomičnog prosjeka. Stoga je uglađivanje u slučaju eksponencijalnog pomičnog prosjeka u Excelu više od onog kod jednostavnog pomičnog prosjeka.
- U tvrtkama poput burze, pomični prosjek pomaže trgovcu da lakše prepozna trend.
