Alati za predviđanje u programu Microsoft Excel

Prognoza je vrlo važan element gotovo bilo kojeg područja aktivnosti, od ekonomije do inženjerstva. Postoji veliki broj softvera koji su specijalizirani za ovo područje. Nažalost, ne znaju svi korisnici da uobičajeni procesor proračunskih tablica u Excelu ima u svom arsenalu alate za predviđanje, koji nisu mnogo inferiorni u odnosu na profesionalne programe. Otkrijmo što su ovi alati i kako napraviti prognozu u praksi.

Postupak predviđanja

Svrha svakog predviđanja je identificirati trenutni trend i odrediti očekivani rezultat u odnosu na proučeni objekt u određenom trenutku u budućnosti.

Metoda 1: linija trenda

Jedna od najpopularnijih vrsta grafičkog predviđanja u Excelu je ekstrapolacija izgradnjom linije trenda.

Pokušajmo predvidjeti iznos dobiti preduzeća za 3 godine na osnovu podataka o ovom pokazatelju za prethodnih 12 godina.

1. Gradi se graf ovisnosti na temelju tabelarnih podataka koji se sastoje od argumenata i vrijednosti funkcija. Da biste to učinili, odaberite područje tablice, a zatim se nalazite na kartici Umetni, kliknite na ikonu željene vrste grafikona koja se nalazi u bloku Grafikoni. Tada odabiremo vrstu pogodnu za određenu situaciju. Najbolje je da odaberete raspored rasipanja. Možete odabrati drugi prikaz, ali tada, kako bi se podaci pravilno prikazali, morat ćete izvršiti uređivanje, posebno ukloniti liniju argumenta i odabrati drugu ljestvicu vodoravne osi.



2. Sada moramo izgraditi liniju trenda. Desnim klikom na bilo koju točku na dijagramu. U aktiviranom kontekstnom meniju zaustavite odabir na stavci Dodajte liniju trenda.



3. Otvara se prozor formatiranja linije trenda. U njemu možete odabrati jednu od šest vrsta aproksimacije:
   • Linearno;
   • Logaritamska;
   • Eksponencijalna;
   • Snaga;
   • Polinom;
   • Linearno filtriranje.

   Započnimo odabirom linearne aproksimacije.

   U bloku postavki "Prognoza" u polju "Naprijed u" podesite broj "3,0", jer moramo napraviti prognozu za tri godine unaprijed. Pored toga, možete potvrditi potvrdni okvir pored postavki. "Prikaži jednadžbu u dijagramu" i "Postavite aproksimacijsku vrijednost pouzdanosti (R ^ 2) na dijagram". Posljednji indikator prikazuje kvalitetu linije trenda. Nakon podešavanja, kliknite na dugme Zatvori.



4. Izgrađena je linija trenda i iz nje možemo utvrditi približni iznos dobiti u tri godine. Kao što vidimo, do tada bi to trebalo biti preko 4500 hiljada rubalja. Koeficijent R2Kao što je već spomenuto, prikazuje kvalitetu linije trenda. U našem slučaju vrijednost R2 izmišlja 0,89. Što je koeficijent veći, veća je pouzdanost linija. Njegova maksimalna vrijednost može biti jednaka 1. Općenito je prihvaćeno da s koeficijentom iznad 0,85 linija trenda je pouzdana.



5. Ako vam nivo pouzdanosti ne odgovara, možete se vratiti u prozor formata linije trenda i odabrati bilo koju drugu vrstu aproksimacije. Možete isprobati sve dostupne opcije da biste pronašli najtačniju.

   

   Treba napomenuti da prognoza korištenjem ekstrapolacije kroz liniju trenda može biti efikasna ako predviđeno razdoblje ne pređe 30% analizirane baze razdoblja. To jest, kada analiziramo period od 12 godina, ne možemo napraviti efikasnu prognozu više od 3-4 godine. Ali čak će i u ovom slučaju biti relativno pouzdano ako za to vrijeme neće biti više sile ili, naprotiv, izuzetno povoljnih okolnosti, kakvih nije bilo u prethodnim periodima.

Lekcija: Kako izgraditi liniju trenda u Excelu

Metoda 2: FORECAST operator

Ekstrapolacija za tabelarne podatke može se obaviti putem standardne Excel funkcije PREDICIJA. Ovaj argument pripada kategoriji statističkih alata i ima sljedeću sintaksu:

= PREDICT (X; poznato_y_values; poznato_x_values)

"X" je argument za koji treba utvrditi vrijednost funkcije. U našem slučaju argument će biti godina za koju treba napraviti prognozu.

Poznate y vrijednosti - baza poznatih vrijednosti funkcija. U našem slučaju njegovu ulogu igra količina dobiti za prethodna razdoblja.

Poznate x vrijednosti su argumenti kojima odgovaraju poznate funkcije odgovaraju. U njihovoj ulozi imamo numeriranje godina za koje su prikupljene informacije o dobiti prethodnih godina.

Naravno, argument ne mora biti vremensko razdoblje. Na primjer, to može biti temperatura, a vrijednost funkcije može biti razina ekspanzije vode pri zagrijavanju.

Pri izračunavanju ove metode koristi se metoda linearne regresije.

Pogledajmo nijanse korištenja operatera PREDICIJA na konkretnom primjeru. Uzmi cijeli stol. Morat ćemo znati prognozu profita za 2018. godinu.

1. Odaberite praznu ćeliju na listu na kojoj planirate prikazati rezultat obrade. Kliknite na dugme "Umetanje funkcije".



2. Otvara se Čarobnjak za funkcije. U kategoriji "Statistički" odaberite ime "PREDICIJA"a zatim kliknite na dugme "OK".



3. Pokreće se prozor s argumentima. U polju "X" navedite vrijednost argumenta za koji želite pronaći vrijednost funkcije. U našem slučaju to je 2018. godina. Stoga pišemo "2018". Ali bolje je da ovaj pokazatelj navedete u ćeliji na listu i u polju "X" samo daj vezu do njega. To će omogućiti u budućnosti automatizaciju izračuna i po potrebi lako promjenu godine.

   U polju Poznate y vrijednosti odredite koordinate stupca "Dobit preduzeća". To možete postići postavljanjem kursora u polje, a zatim držanjem lijeve tipke miša i isticanjem odgovarajućeg stupca na listu.

   Slično je i na terenu Poznate x vrijednosti unesite adresu stupca "Godina" s podacima za protekli period.

   Nakon što su sve informacije unesene, kliknite na gumb "OK".



4. Operator izračunava na osnovu unesenih podataka i rezultat prikazuje na ekranu. Za 2018. godinu planira se profit u regiji od 4.564,7 hiljada rubalja. Na osnovu rezultirajuće tablice možemo izraditi grafikon pomoću gore navedenih alata za oblikovanje.



5. Ako promijenite godinu u ćeliji koja je korištena za unošenje argumenta, rezultat će se u skladu s tim promijeniti i raspored će se automatski ažurirati. Na primjer, prema predviđanjima za 2019. godinu, iznos dobiti iznosit će 4637,8 hiljada rubalja.

Ali nemojte zaboraviti da, kao i kod izgradnje linije trenda, vremensko razdoblje prije predviđenog razdoblja ne bi trebalo prelaziti 30% cjelokupnog razdoblja za koje je baza podataka akumulirana.

Lekcija: Ekstrapolacija u Excelu

Metoda 3: TREND operator

Za predviđanje možete koristiti drugu funkciju - TREND. Takođe spada u kategoriju statističkih operatora. Njegova je sintaksa slična sintaksi alata PREDICIJA i izgleda ovako:

= TREND (poznate vrijednosti_y; poznate vrijednosti_x; nove_vrednosti_x; [const])

Kao što vidite, argumenti Poznate y vrijednosti i Poznate x vrijednosti u potpunosti odgovaraju sličnim elementima operatera PREDICIJA, i argument "Nove x vrijednosti" argument podudaranja "X" prethodni alat. Pored toga, TREND postoji dodatni argument "Konstantan", ali je opcionalan i koristi se samo ako postoje stalni faktori.

Ovaj se operator najučinkovitije koristi kada postoji linearna ovisnost funkcije.

Pogledajmo kako će ovaj alat raditi s istim nizom podataka. Da bismo usporedili rezultate, točku prognoze definiramo kao 2019. godinu.

1. Označavamo ćeliju za prikaz rezultata i pokretanje Čarobnjak za funkcije na uobičajen način. U kategoriji "Statistički" pronađite i istaknite ime "TREND". Kliknite na dugme "OK".



2. Otvara se prozor argumenata operatora TREND. U polju Poznate y vrijednosti gore opisanom metodom unosimo koordinate stupca "Dobit preduzeća". U polju Poznate x vrijednosti unesite adresu stupca "Godina". U polju "Nove x vrijednosti" unosimo vezu do ćelije u kojoj se nalazi broj godine za koju treba navesti prognozu. U našem slučaju to je 2019. godina. Polje "Konstantan" ostavi prazno. Kliknite na dugme "OK".



3. Operator obrađuje podatke i rezultate prikazuje na ekranu. Kao što vidite, iznos projicirane dobiti za 2019. godinu, izračunat metodom linearne ovisnosti, iznosit će kao i u prethodnoj metodi izračuna 4637,8 tisuća rubalja.

Metoda 4: operater RASTA

Još jedna funkcija koja se može koristiti za predviđanje u Excelu je operator GROWTH. Pripada i statističkoj grupi alata, ali za razliku od prethodnih prilikom izračunavanja se koristi ne metodom linearne ovisnosti, nego eksponencijalnom. Sintaksa ovog alata je sljedeća:

= GROWTH (poznate vrijednosti_y; poznate vrijednosti_x; nove_vrednosti_x; [const])

Kao što vidite, argumenti ove funkcije točno ponavljaju argumente operatera TREND, pa se po drugi put nećemo zaustaviti na njihovom opisu, već odmah pristupiti praktičnoj primjeni ovog alata.

1. Odabiremo ćeliju za izlaz rezultata i zovemo je na uobičajeni način Čarobnjak za funkcije. Na popisu statističkih operatera potražite stavku ROST, odaberite ga i kliknite na gumb "OK".



2. Prozor argumenata gornje funkcije je aktiviran. Unesite podatke u polja ovog prozora na isti način kao što smo ih unijeli u prozor argumenata operatora TREND. Nakon unosa podataka kliknite na gumb "OK".



3. Rezultat obrade podataka prikazuje se na monitoru u prethodno naznačenoj ćeliji. Kao što vidite, ovog puta rezultat je 4682,1 hiljada rubalja. Razlike u rezultatima obrade podataka operatora TREND beznačajni, ali dostupni su. To je zbog činjenice da ovi alati koriste različite metode proračuna: metodu linearne ovisnosti i metodu eksponencijalne ovisnosti.

Metod 5: LINEAR operator

Operator LINE u proračunu se koristi metoda linearnog približavanja. Ne treba ga brkati sa metodom linearne ovisnosti koju koristi alat. TREND. Njegova sintaksa je sljedeća:

= LINE (Poznate vrijednosti_y; poznate vrijednosti_x; nove_vrede_x; [const]; [statistika])

Poslednja dva argumenta su neobavezna. Sa prva dva smo upoznati s prethodnim metodama. Ali vjerovatno ste primijetili da u ovoj funkciji ne postoji argument koji ukazuje na nove vrijednosti. Činjenica je da ovaj alat određuje samo promjenu prihoda po jedinici razdoblja, koja je u našem slučaju jednaka godini, ali ukupan rezultat moramo izračunati odvojeno, dodajući rezultat izračuna operatera posljednjoj stvarnoj vrijednosti dobiti LINEputa broj godina.

1. Odabiremo ćeliju u kojoj će se proračun provoditi i pokrećemo čarobnjaka za funkcije. Odaberite ime LINEIN u kategoriji "Statistički" i kliknite na dugme "OK".



2. U polju Poznate y vrijednosti, otvoreni prozor argumenata, unesite koordinate stupca "Dobit preduzeća". U polju Poznate x vrijednosti unesite adresu stupca "Godina". Preostala polja ostaju prazna. Zatim kliknite na gumb "OK".



3. Program izračunava i prikazuje linearnu vrijednost trenda u odabranoj ćeliji.



4. Sada moramo saznati veličinu projektovane dobiti za 2019. godinu. Postavite znak "=" u bilo koju praznu ćeliju na listu. Kliknemo na ćeliju koja sadrži stvarni iznos dobiti za posljednju studijsku godinu (2016). Stavili smo znak "+". Zatim kliknite na ćeliju koja sadrži prethodno izračunati linearni trend. Stavili smo znak "*". Budući da između posljednje godine razdoblja studija (2016) i godine za koju želite prognozirati (2019.) leži razdoblje od tri godine, u ćeliji postavljamo broj "3". Za izračun kliknite na gumb Unesite.

Kao što vidite, projicirana marža profita izračunana metodom linearnog približavanja u 2019. godini iznosit će 4.614,9 tisuća rubalja.

Metoda 6: LGRFPPRIBLE operator

Posljednji alat koji ćemo gledati biće LGRFPPRIBLE. Ovaj operater vrši proračune na temelju metode eksponencijalne aproksimacije. Njegova sintaksa ima sljedeću strukturu:

= LGRFPRIBLE (Poznate vrijednosti_y; poznate vrijednosti_x; nove_vrede_x; [const]; [statistika])

Kao što vidite, svi argumenti u potpunosti ponavljaju odgovarajuće elemente prethodne funkcije. Algoritam za proračun prognoze malo će se promijeniti. Funkcija izračunava eksponencijalni trend koji pokazuje koliko će se puta prihodi mijenjati za jedno razdoblje, odnosno za godinu dana. Trebat ćemo pronaći razliku u dobiti između posljednjeg stvarnog razdoblja i prvog planiranog i množiti ga s brojem planiranih razdoblja (3) a rezultatu dodajte zbroj zadnjeg stvarnog razdoblja.

1. Na popisu operatora čarobnjaka za funkcije odaberite ime LGRFPPRIBL. Kliknite na dugme "OK".



2. Pokreće se prozor s argumentima. U nju podatke unosimo tačno kao i mi, koristeći funkciju LINE. Kliknite na dugme "OK".



3. Rezultat eksponencijalnog trenda se izračunava i prikazuje u određenoj ćeliji.



4. Stavili smo znak "=" u praznu ćeliju. Otvorite zagrade i odaberite ćeliju koja sadrži vrijednost prihoda za posljednji stvarni period. Stavili smo znak "*" i odaberite ćeliju koja sadrži eksponencijalni trend. Stavljamo znak minus i ponovo kliknemo na element u kojem se nalazi vrijednost prihoda za posljednji period. Zatvorite držač i vozite znakove "*3+" bez citata Opet kliknite na istu ćeliju koja je izabrana zadnji put. Da biste izvršili izračun, kliknite na gumb Unesite.

Predviđeni iznos dobiti u 2019. godini, izračunat metodom eksponencijalne aproksimacije, iznosit će 4639,2 hiljade rubalja, što se opet ne razlikuje mnogo od rezultata dobivenih u prethodnom izračunu.

Lekcija: Ostale statističke funkcije u Excelu

Otkrili smo kako predvidjeti u programu Excel. To se može grafički učiniti korištenjem linije trenda i analitički pomoću brojnih ugrađenih statističkih funkcija. Kao rezultat obrade identičnih podataka od strane ovih operatera, može se dobiti drugačiji rezultat. Ali, to nije iznenađujuće, jer svi koriste različite metode proračuna. Ako je fluktuacija mala, onda se sve ove mogućnosti primjenjive na određeni slučaj mogu smatrati relativno pouzdanim.