Prognoza budućih trendova

Za prognozu budućih trendova možete da iskoristite Excel opciju Forecast Sheet– to je najbrži i najlakši način da uočite šta će se dešavati u budućnosti na osnovu istorijskih podataka. Ona koristi funkciju FORECAST.ETS koja, pored izračunavanja trenda, može i da popuni nedostajeće podatke ili da prepozna sezonalnost podataka; takođe, koristi i funkciju FORECAST.ETS.CONFINT za računanje intervala poverenja… Kako prognozu uraditi ručno?

Za izračunavanje i prikaz linije trenda u Excel-u koristi se regresiona analiza. U zavisnosti od toga koja matematička funkcija se izabere to može da budu: linearna regresija, eksponencijalna regresija, logaritamska, polinomna, regresija stepene funkcije ili metoda pokretnog proseka (nije regresija). Prognozu budućih trendova možete suštinski da uradite i uz pomoć linije trenda. U konkretnom primeru smo izabrali polinom 6. stepena za prikaz linije trenda, a zatim u opciji Forecast zadali smo pravilo da Excel prikaže prognozu za dva perioda unapred i to je on učinio. Šta će biti period, zavisi od jedinice prikazane na x osi. Ako su na x osi iskazani meseci, to će biti meseci. Ako su prikazane godine, respektivno to će biti godine… U nekim situacijama i linija trenda može da bude rešenje, ali kada postoji sezonalnost, nedostaju podaci ili prosto nema dovoljno podataka bolje je da trendove izračunate ručno.

Kao primer, za ručno izračunavanje, ćemo iskoristiti jednostavnu tabelu koja prikazuje vrednosti prodaje kroz vreme. Ona sadrži četiri kolone: YEAR, QUARTER, DATE i SALES. Iako smo definisali godine i kvartale, za izradu prognoze potrebna je kolona koja sadrži datume- odatle kolona DATE, gde smo prikazali datum koji označava početak novog i kraj prethodnog kvartala. U koloni SALES su date neke proizvoljne vrednosti prodaje, npr. prodaja u milionima EUR.

Za računanje trenda uz pomoć linearne regresije možemo da iskoristimo funkciju TREND. Sintaksa:

TREND(<poznate_y >;<poznate_x>;<novi_x>;<b>)

Njeni argumenti:

  • poznate_y: poznate istorijske vrednosti, npr. vrednost prodaje
  • poznate_x: Istorijske nezavisne vrednosti, npr. datumi
  • novi_x: buduća vrednost koja treba da se izračuna
  • b: podrazumevana vrednost je TRUE, a ako je izostavimo trend se izračunava normalno. Ako je FALSE, to znači da linija trenda mora da prođe kroz nultu tačku (x=0, y=0).

Za linearnu regresiju može da se iskoristi i funkcija FORECAST.LINEAR:

FORECAST.LINEAR(<poznate_y >;<poznate_x>;<novi_x>)

Ona vraća iste vrednosti a ima malo jednostavniju sintaksu. Pa ako unesemo formulu:

=FORECAST.LINEAR(C15;$D$2:$D$14;$C$2:$C$14)

Excel će prognozirati vrednosti prodaje u budućnosti za prvi budući kvartal. Kopiranjem formule dobijamo vrednosti prodaje za sva tri buduća kvartala.

U prethodnom slučaju, za 2023-Q3, nisu prikazane vrednosti prodaje. Ako zaista nije bilo prodaje, to nije sporno, ali šta ako su u pitanju nepostojeći podaci? U tom slučaju potrebno je da izvršimo interpolaciju kako bismo izračunali nedostajeće vrednosti. Interpolacija se računa uz pomoć formule:

=D3+(D5-D3)*(C4-C3)/(C5-C3)

Kada dopunimo nedostajeće vrednosti, funkcije TREND i FORECAST.LINEAR će vratiti drugačije rezultate.

Ipak, za prognozu je najbolje iskoristiti funkciju FORECAST.ETS. Ona predviđa buduće vrednosti na osnovu istorijskih podataka upotrebom Exponential Triple Smoothing (ETS) algoritma, a može da uzme u obzir i sezonalnost, da popuni nedostajeće podatke ili izvrši uprosečavanje vrednosti. Sintaksa:

FORECAST.ETS (<ciljni_datum >;<vrednosti>;<vremenski_okvir>;[<sezonalnost>];
[<nedostajući podaci>];[<agregacija>])

Argumenti funkcije:

  • ciljni_datum– datum za koji računamo novu vrednost
  • vrednosti– opseg (kolona) sa vrednostima prodaje
  • vremenski_okvir- opseg (kolona) sa datumima
  • sezonalnost– ukazuje na sezonske promene. Npr. ako unesete vrednost 4 to je signal da se promene dešavaju u sklopu 4 kvartala.
  • nedostajući podaci– ukazuje da li treba popuniti nedostajeće podatke interpolacijom. Ako je vrednost 1 izvršiće se interpolacija, a ako je 0 onda nije bilo prodaje (podaci su 0).
  • agregacija– ako imate npr. dve jednake vrednosti za neki datum birate kojom funkcijom ćete izvršiti njihovu agregaciju. Najčešće je to funkcija AVERAGE (1), ali mogu se iskoristiti i neke druge funkcije za agregaciju.

Unosom formule:

=FORECAST.ETS(C14;$D$2:$D$14;$C$2:$C$14;4;1;1)

izvršićemo izračunavanje budućih vrednosti, funkcija će uzeti u obzir sezonalnost (4 kvartala), popuniće nedostajeće podatke (interpolacija) i izvršiće uprosečavanje za postojeće datume.

Gornji i donji interval poverenja možemo da izračunamo uz pomoć funkcije FORECAST.ETS.CONFINT. Unesimo formulu:

=FORECAST.ETS.CONFINT(C14;$D$2:$D$13;$C$2:$C$13;0,95;1;1)

Sada, ako ovu vrednost oduzmete od prognozirane vrednosti dobićete donji interval poverenja, a ako je dodate na prognoziranu vrednost izračunava se gornji interval poverenja.

Funkcije i metode koje ste malopre videli mogu da se iskoriste za lako računanje budućih trendova, ali ako nemate iskustva u ovim stvarima puno bolje je da iskoristite Excel opciju Forecast Sheet.