Metoda pokretnog prosjeka u Microsoft Excelu. Izračunavanje pokretnog prosjeka u Excelu i predviđanje

Cilj rada : Steknite vještine u rješavanju problema analize frekvencije koristeći funkciju radnog lista za analizu MS Excel.

Kratka teorija

Prilikom analize ekonomskih pokazatelja često se postavlja pitanje koliko se često indikatori pojavljuju u datim intervalima vrijednosti.

Funkcija FREQUENCY radnog lista za analizu MS Excel je klasifikovana kao statistička funkcija i vraća distribuciju frekvencije kao vertikalni niz. Za dati skup vrijednosti i dati skup džepova (intervala), distribucija frekvencije broji koliko vrijednosti pada u svaki interval.

Niz podataka može biti jednodimenzionalni ili dvodimenzionalni niz (na primjer, A 4: D 15).

Sintaksa: FREQUENCY (podatak_niz; džepni_niz)

Za analizu frekvencije možete koristiti naredbuUsluga/analiza podataka.Analiza podataka je jedan od dodataka Excel . Ako ova naredba nije u meniju, tada bi trebali pokrenuti naredbuUsluga/Dodacii označite odgovarajući okvir u prozoru Dodaci.

Vježba 1

Korištenje funkcije Frekvencija za odabir skupa iznosa narudžbe () unesite u raspon, izbrojite koliko vrijednosti spada u date intervale vrijednosti. Na primjer, od 0 do 1000, od 1001 do 1500, od 1501 do 2000, od 2001 do 2500, preko 2500.

Procedura:

1. Na radnom listu MS Excel unesite podatke o obimu narudžbi u 20 ekspozitura kompanije za septembar u obliku tabele čiji je fragment prikazan na slici.

	Broj podružnice	septembra
		1230		1000
				1500
		….		2000
				2500

1. U slobodnom rasponu ćelija (kolona) unesite gornje granice intervala (na primjer, D 2=1000, D 3=1500, D 4=2000, D 5=2500).
2. Odaberite blok ćelija u koloni pored kolone intervala ( E2:E 21). Da bi se izbrojao broj vrijednosti koje prelaze donju granicu intervala, odabire se raspon koji je za jednu ćeliju veći od raspona intervala.
3. Do opsega E 2: E 6 unesite formulu ( =FREQUENCY( E 2: E 15; J 2: J 6)).

Za ovo koristite čarobnjak za funkcije (Insert/Function). U kategoriji Statistika odaberite funkciju Frekvencija sa liste. U dijaloškom okviru funkcije FREQUENCY popunite polja niza uzoraka i niza intervala (slika 1).Bez napuštanja prozora za dijalogpritisnite kombinaciju tastera< Ctrl / Shift / Enter > za izračunavanje elemenata niza.

Slika 1 Primjer popunjavanja dijaloškog okvira funkcije Frekvencija.

1. Napravite dijagram na osnovu vaših rezultata.
2. Sačuvajte fajl.

Zadatak 2

Napravite dvodimenzionalni niz na radnom listu koji sadrži statističke podatke o visini ljudi različitih starosnih kategorija. Prevucite prstom frekvencijska analiza rezultate pomoću funkcije FREQUENCY i Data Analysis (stavka menijaAnaliza podataka/histogram).

Kada koristite alat za analizu podataka u dijaloškom okviru u poljuInterval unosau polje unesite početni interval za koji se gradi histogramRazmak džepova- raspon sa vrijednostima gornjih granica intervala. Histogram se gradi na novom ili trenutnom radnom listu.

Dio 2

Rješavanje problema predviđanja u MS Excel-u. Metoda pokretnog prosjeka

Cilj rada : Sticanje vještina u predviđanju privredne aktivnosti preduzeća koristeći statistički softverski paket MS Excel.

Kratka teorija

Pod prognozom se podrazumijeva naučno utemeljen opis mogućih stanja sistema u budućnosti. Svaka organizacija, kada razvija kratkoročne i dugoročne planove, prinuđena je da predvidi vrijednost najvažniji pokazatelji ekonomske aktivnosti kao što su obim proizvodnje, prodaja, troškovi proizvodnje itd. Trenutno se za rješavanje problema predviđanja koriste moderne tehnologije. informacione tehnologije, čiji softver uključuje statističke softverske pakete.

Za rješavanje problema predviđanja u okruženju MS Excel se koristi paket za analizu , uključujući alate za analizu. Odabirom alata za analizu podataka i postavljanjem potrebnih parametara možete brzo riješiti kompleks statistički problemi, popraćeno grafičkom interpretacijom.

Preliminarna analiza vremenskih serija ekonomskih indikatora uključuje identifikaciju abnormalnih vrijednosti nivoa serije koji krše utvrđivanje prisustva trenda. Da bi se eliminisale abnormalne vrijednosti indikatora, koristi se postupak izravnavanja vremenskih serija. U ovom slučaju, da bi se identificirao trend serije, stvarne vrijednosti se zamjenjuju izračunatim.

Prilikom odabira metode predviđanja uzima se u obzir priroda promjene slučajna varijabla vremenske serije. Ako je varijacija u prosječnim vrijednostima beznačajna i sva zapažanja u vremenskoj seriji su od jednakog značaja za prognozu, tada se koristi metoda pokretnog prosjeka. Pokretni proseci vam omogućavaju da izgladite (filtrirate) nasumične i periodične fluktuacije u vremenskoj seriji. Jednostavno izglađivanje pokretnog prosjeka je najčešći postupak izravnavanja.

U alatu za analizu MS Excel Pokretni prosekbroj vrijednosti uključenih u izračunavanje predviđene vrijednosti određen je parametrom Interval . Što je interval izglađivanja veći, to je potrebnije izgladiti male fluktuacije u vrijednostima serije. MetodaJednostavan pokretni prosekdaje dobre rezultate u vremenskim serijama sa linearnim trendom razvoja.

Ako su najznačajniji za prognozu najnoviji rezultati zapažanja, a zatim upotrijebite metodu eksponencijalno izglađivanje. U metodi eksponencijalnog izglađivanja, svaka vrijednost učestvuje u formiranju predviđenih vrijednosti s promjenjivom težinom, koja se smanjuje kako podaci zastare. U alatu za analizu MS Excel « Eksponencijalno izglađivanje"težinski koeficijent, ili parametar izravnavanja, određen je parametromFaktor prigušenja. Tipično, za vremenske serije u ekonomskim problemima, vrijednost parametra glađenja je postavljena u rasponu od 0,1 do 0,3. Početna izračunata vrijednost u proceduriEksponencijalno izglađivanje MS Excel paket za analizu uzima se jednak nivou prvog člana serije. Metoda daje dobru saglasnost između početnih i izračunatih podataka za prve vrijednosti serije. Ako se konačne izračunate vrijednosti značajno razlikuju od odgovarajućih početnih, tada je preporučljivo promijeniti vrijednost parametra za izravnavanje. Veličina neslaganja može se procijeniti na osnovu standardnih grešaka i grafikona, koje vam paket Analysis omogućava da prikažete zajedno sa izračunatim vrijednostima serije.

Razmotrimo mogućnosti predviđanja pokazatelja učinka preduzeća koje se bavi pružanjem komunikacionih usluga.

Zadatak do laboratorijski rad(dio 2)

Vježba 1 : Izračunajte predviđenu vrijednost obima proizvoda (usluga) preduzeća koristeći metodu pokretnog prosjeka.

Procedura za izvršenje zadatka:

Hajde da napravimo kolonu na radnom listu koja sadrži podatke o obimu usluga u milionima rubalja koje je preduzeće pružilo u poslednjih 10 godina. Hajde da identifikujemo trend promena indikatora koristeći pokretni prosek. Odabrat ćemo trogodišnji period pokretnog prosjeka, jer u kraćem periodu pokretni prosjek možda neće odražavati trend, ali će ga u dužem periodu izgladiti.

Za proračune ćemo koristiti metodu direktnog uvođenja formule. Da bismo dobili trogodišnji pokretni prosek obima obavljenih usluga za naš primer, unosimo u ćeliju B 5 formula za izračunavanje =PROSEK( A2:A 4). Kopirajmo formulu u interval B 6: B 11.

Slika 1 Izračunavanje jednostavnog pokretnog prosjeka

Ilustrujmo rezultate grafikom koji odražava dinamiku promjena početnih podataka i pokretnog prosjeka.

Slika 2 Grafikon trenda promjena obima usluga dobijenih metodom jednostavnog pokretnog prosjeka

Drugo rješenje je korištenje pokretnog cijelog broja za definiranje Paket analiza . Paket za analizu je dodatak MS Excel (odaberite stavku menijaUsluga/Dodacii označite polje Paket analiza).

Procedura

1. Pokreni naredbuUsluga/analiza podatakai izaberite sa liste alata za analizuPokretni prosek.
2. U dijaloškom okviru navedite parametre za izračunavanje pokretnog prosjeka:

• Kao ulazni interval odaberite blok ćelija koje sadrže podatke o količini usluga.
• Odredi interval- 3 (podrazumevano je 3), kao izlazni interval bilo koje ćelije radnog lista (samo kliknite na ćeliju radnog lista iz koje treba da se izlaze rezultati);

Excel će obaviti posao unosa vrijednosti u formulu za izračunavanje pokretnog prosjeka. Zbog nedovoljnih podataka pri izračunavanju prosjeka za prva opažanja, vrijednost greške #N/A će biti prikazana u početnim ćelijama izlaznog opsega. Napominjemo da je prva dobivena vrijednost u nizu prognozirana vrijednost ne za treći, već za četvrti period. Stoga, ako ćelija navedena za izlaz odgovara početku kolone za promatranje, tada trebate pomaknuti stupac izračunatih vrijednosti za jednu ćeliju. Ova radnja će priložiti prognoze tačno na periode za koje su izračunate.

Analizirajte korištene formule za proračun i dobivene rezultate.

Slično, izračunajte petogodišnje jednostavne pokretne prosjeke. Uporedite rezultate izglađivanja za dvije opcije proračuna.

Zadatak 2: Izračunajte predviđenu vrijednost obima proizvoda (usluga) preduzeća koristeći metodu eksponencijalnog izravnavanja.

Procedura:

1. Na MS Excel listu kreirati listu koja sadrži podatke o broju zaposlenih u kompaniji u proteklih 10 godina. Unesite podatke nasumično, ali tako da se trend može pratiti.
2. Izgladite vremensku seriju koristeći eksponencijalni prosjek sa parametrima izglađivanja od 0,1, a zatim 0,3. Na osnovu rezultata proračuna, konstruirajte graf i odredite koji od rezultirajućih vremenskih serija je glatkiji.

Koristite komanduUsluga/analiza podatakai izaberite sa liste alata za analizuEksponencijalno izglađivanje.Odredite parametre za izračunavanje pokretnog prosjeka:

• Kao ulazni interval, odaberite blok ćelija koji sadrži podatke o populaciji.
• Odrediti Faktor prigušenja. Kao izlazni interval bilo koja ćelija radnog lista.
• Postavite izlaz grafa i standardne greške.

1. Dodajte linije trenda rezultirajućim grafikonima. Da biste to učinili, odaberite liniju grafikona (samo kliknite desnom tipkom miša na liniju grafikona) i odaberite stavku u kontekstnom izbornikuDodajte liniju trenda. U dijaloškom okviru odaberite tip trenda koji najbolje odgovara vašim podacima (na primjer, linearno filtriranje) i potvrdite okvir da biste prikazali jednadžbu prilagođavanja krivulje na grafikonu.
2. Provjerite i sačuvajte rezultate.

dio 3

Rješavanje problema predviđanja pomoću funkcija radnog lista i markera za popunjavanje

Kratka teorija

U ekonomskom predviđanju koriste se različiti modeli rasta. Kriva rasta je određena funkcija koja aproksimira datu vremensku seriju. Prilikom izrade prognoze korištenjem krivulja rasta biraju se krive čiji oblik odgovara dinamici vremenske serije, procjenjuju se njihovi parametri, provjerava adekvatnost odabranih krivulja predviđenom procesu i tačka ili intervalna prognoza.

Postoji nekoliko metoda za uklapanje krivulja. Jedna od najjednostavnijih je vizualna metoda. Ako trend razvoja (trend) nije dovoljno vidljiv na grafu, tada se serija izglađuje kako je gore opisano, a zatim se bira kriva koja odgovara novoj seriji. U ovom slučaju se koristi i savremeni softver računarskih sistema. IN MS Excel ugrađen posebne funkcije, što vam omogućava da izračunate predviđene vrijednosti za određeni period.

Excel vrši linearnu ekstrapolaciju, tj. izračunava najbolju ravnu liniju koja prolazi kroz seriju date bodove. Zadatak je nacrtati skup tačaka na grafu, a zatim odabrati liniju duž koje možete pratiti razvoj funkcije sa najmanjom greškom. Ova linija se zove linija TREND. Rezultat izračuna korisnik može koristiti za analizu trenda i kratkoročno predviđanje.

Excel može automatski crtati linije trenda, razne vrste direktno na dijagramu. Izračuni se mogu izvršiti na dva načina:

• Korištenje ručke za punjenje
• Korištenje funkcija radnog lista

Prvi način

Linearna aproksimacija

• Koristeći lijevu tipku miša, povucite ručicu za popunjavanje tako da su odabrane i ćelije za koje želite izračunati predviđene vrijednosti. Ovako izračunate vrijednosti odgovaraju linearnoj prognozi.

Eksponencijalna aproksimacija

• Odaberite ćelije s rezultatima posmatranja.
• Povucite ručicu za popunjavanje koristeći desnu tipku miša tako da se izaberu i ćelije za koje želite izračunati predviđene vrijednosti.
• U kontekstualnom meniju koji se pojavi izaberite naredbu „Eksponencijalno uklapanje“.

Drugi način

U MS Excel-u Statističke funkcije radnog lista su ugrađene.

TREND() - vraća vrijednosti prema linearnoj aproksimaciji najmanjih kvadrata.

VISINA() - vraća vrijednosti prema eksponencijalnom trendu.

Korištenje ovih funkcija je još jedan način izračunavanja regresione analize.

Format

TREND (rev_value_Y; find_value_X; new_value_X; konstanta)

Funkcija GROWTH vraća vrijednosti prema eksponencijalnom trendu.

Laboratorijski zadatak (3. dio)

Vježba 1:

Izračunajte linearnu i eksponencijalnu prognozu za jednu godinu i sljedeća tri perioda (do 2011.) koristeći marker popunjavanja.

Zadatak 2:

Izračunajte linearne i eksponencijalne prognoze za jednu godinu, a zatim za sljedeća tri perioda koristeći funkcije radnog lista TREND i RAST. Za izračunavanje intervalne prognoze, nakon popunjavanja parametara dijaloškog okvira funkcije i bez napuštanja, pritisnite kombinaciju tipki Ctrl/Shift/Enter.

Formula za izračunavanje elemenata niza trebala bi se pojaviti u traci formule radnog lista, na primjer,

( = TREND (B 3: G 3; B 2: G 2; B 2: H 2))

Odredite koji je model najprecizniji.

Izradite grafikone i linije trenda za prvi i drugi zadatak.

1. Izračunati koeficijenti sezonskosti;
2. Izaberi period za izračunavanje prosjeka vrijednosti;
3. Izračunaj prognozu, tj. pomnožiti prosječnu vrijednost sa faktorom sezonskosti;
4. Uzmi račun dodatni faktori, koji značajno utiču na prodaju;

Izračunati prognoza korištenjem metode kretanja veoma prosečan Samo. Za ovo uzimamo prosječna vrijednost, na primjer, prosječna prodaja u posljednja 3 mjeseca i pomnožiti sa faktorom sezonskosti za 3 mjeseca - i prognoza za mjesec je spremna. Isto radimo i za sljedeći mjesec, samo će prethodni mjesec prognoze već biti uključen u obračun.

1. Izračunajmo koeficijente sezonskosti za prognozu koristeći metodu pokretnog prosjeka.

Za ovo računamo koeficijenti sezonskosti očišćeni od rasta, kao što je opisano u članku „Kako izračunati koeficijente sezonalnosti bez rasta?“ . Onda definišemo koeficijenti sezonskosti prema prethodnim periodima, za 1 mjesec, za 2 mjeseca, za 3 mjeseca, itd. u zavisnosti od perioda za koji uzimamo prosječnu vrijednost za prognozu prodaje. Na primjer, izračunajmo mjesečne koeficijente sezonalnosti (pogledajte priloženi list "Izračun koeficijenata")

do 1 mjeseca:

• Januarski koeficijent - odnos januarskog koeficijenta sezonalnosti očišćenog od rasta prema decembarskom;

  Februar - februarski koeficijent do januara;

  mart - mart do februar;

do 2 mjeseca:

• za januar - odnos januarskog koeficijenta sezonalnosti prema prosječnoj vrijednosti decembra i novembra

  za februar - februar se dijeli sa prosječnom vrijednošću koeficijenata za januar i decembar

  za mart - mart na prosjek februarskih i januarskih koeficijenata

do 3 mjeseca:

• za utvrđivanje januarskog koeficijenta sezonalnosti za 3. mjesec podijelimo januarski koeficijent sezonalnosti, očišćen od rasta, sa prosječnom vrijednošću koeficijenata sezonalnosti, očišćenih od rasta, za decembar, novembar, oktobar;

  za februar - februarski koeficijent se dijeli sa prosječnom vrijednošću koeficijenata za novembar, decembar i januar;

  Za mart - odnos marta i prosječne vrijednosti koeficijenata sezonalnosti očišćenih od rasta decembra, januara i februara;

Izračunali smo koeficijente sezonalnosti za prethodne periode, sada ćemo utvrditi za koji period je bolje uzeti prosječnu vrijednost za više tacna prognoza Također možete jednostavno i brzo izračunati koeficijente sezonskosti pomoću programa Forecast4AC - pouzdanog pomoćnika u svim fazama predviđanja.

2. Odaberite period za izračunavanje prosječne vrijednosti za prognozu koristeći metodu pokretnog prosjeka.

Da bismo to učinili, pravimo prognozu za zadnji i pretposljednji period, podatke za koje znamo, na tri ili više načina da se odredi odgovarajući period za izračunavanje prosjeka(pogledajte priloženi list „Izbor perioda“). I da vidimo koja opcija daje precizniju prognozu:

1. Izračunat ćemo prognozu prodaje koristeći metodu pokretnog prosjeka za 1. mjesec:

Decembar = novembarski obim prodaje pomnožen sa decembarskim koeficijentom sezonalnosti u odnosu na prethodni mjesec.

1. Izračunajmo prognozu prodaje koristeći metodu pokretnog prosjeka za 2 mjeseca:

Decembar = prosječni obim prodaje za oktobar i novembar pomnožen sa decembarskim koeficijentom sezonalnosti za 2 mjeseca.

1. Izračunavamo prognozu koristeći metodu pokretnog prosjeka za 3 mjeseca:

Decembar = prosječni obim prodaje za septembar, oktobar i novembar pomnožen sa decembarskim koeficijentom sezonalnosti za 3 mjeseca.

Sada smo na tri načina izračunali prognozu za decembar. Na isti način ćemo računati i za novembar.

Sad uporedi stvarne vrednosti za novembar i decembar sa prognozama izračunatim na 3 načina. To vidimo na našem primjeru najpreciznija prognoza se izračunava metodom pokretnog prosjeka za 2 mjeseca, uzmimo to kao osnovu. U vašem slučaju, preciznija prognoza može biti za prethodni period, za 3 prethodna perioda ili za 4 prethodna perioda.

3. Izračunajmo prognozu prodaje koristeći metodu pokretnog prosjeka.

Jer izabrali smo prognozu na osnovu prosjeka za prethodna 2 mjeseca, zatim za prognozu za januar, prosječne prodaje za novembar i decembar, pomnožite januarski koeficijent sezonalnosti sa 2 mjeseca.

Za prognozu za februar Množimo prosječni obim prodaje u januaru i decembru sa faktorom sezone februara.

Po toj logici, prognozu produžavamo do kraja godine. Prognoza prodaje za godinu je spremna.

4. Dodatni faktori koje treba uzeti u obzir prilikom izračunavanja vaše prognoze prodaje.

Za povećanje tačnosti prognoze važno je:

1. Oduzmite faktore iz prošlih perioda, koji značajno uticalo na obim prodaje, ali se neće ponoviti u prognoziranim mjesecima(promocije prodaje, jednokratna isporuka velikom nestalnom kupcu, povlačenje iz velikog trgovačkog lanca itd.).
2. Dodajte faktore prognoziranim mjesecima, što će značajno uticati na prodaju – početak rada sa velikim lancima, provođenje velikih kampanja promocije prodaje, lansiranje novih proizvoda, reklamne kampanje itd.

Tačne prognoze za vas!

Program Forecast4AC PRO će izračunati prognozu koristeći metodu pokretnog prosjeka istovremeno za više od 1000 vremenskih serija jednim pritiskom na taster, značajno štedeći vaše vrijeme, na jedan od 4 načina:

Na prosjek za prethodna dva perioda

Na prosjek za prethodna tri perioda

Na prosjek za prethodna 4 perioda

Dvostruki prosjek 3. i 4. prethodnog perioda

Pridruži nam se!

Preuzmite besplatne aplikacije za predviđanje i poslovnu analizu:

• Novo Prognoza Lite- automatski kalkulacija prognoze V Excel.
• 4analytics - ABC-XYZ analiza i analiza emisija Excel.
• Qlik Sense Desktop i QlikViewPersonal Edition - BI sistemi za analizu i vizualizaciju podataka.

Testirajte mogućnosti plaćenih rješenja:

• Novo Prognoza PRO- predviđanje u Excelu za velike skupove podataka.

Metoda pokretnog prosjeka je statistički alat koji se može koristiti za rješavanje različitih vrsta problema. Posebno se često koristi u predviđanju. U Excelu također možete koristiti ovaj alat za rješavanje brojnih problema. Pogledajmo kako se pokretni prosek koristi u Excel-u.

Značenje ovu metodu sastoji se u tome da se uz njegovu pomoć apsolutne dinamičke vrijednosti odabrane serije mijenjaju u aritmetičke prosjeke za određeni period izravnavanjem podataka. Ovaj alat se koristi za ekonomske proračune, prognoze, u procesu trgovanja na berzi, itd. Najbolji način za korištenje metode pokretnog prosjeka u Excelu je korištenje najmoćnije oruđe statistička obrada podataka, koja se tzv Paket analiza. Osim toga, možete koristiti ugrađenu Excel funkciju za iste svrhe PROSJEČNO.

Metoda 1: Paket analize

Paket analiza je dodatak za Excel koji je po defaultu onemogućen. Stoga, prije svega, morate ga omogućiti.

Nakon ove akcije paket "Analiza podataka" aktiviran, a odgovarajuće dugme se pojavilo na traci u kartici "Podaci".

Pogledajmo sada kako možete direktno koristiti mogućnosti paketa Analiza podataka za rad koristeći metodu pokretnog prosjeka. Hajde da napravimo prognozu za dvanaesti mjesec na osnovu podataka o prihodima kompanije za 11 prethodnih perioda. Da bismo to učinili, koristit ćemo tabelu ispunjenu podacima, kao i alate Paket analiza.

1. Idite na karticu "Podaci" i pritisnite dugme "Analiza podataka", koji se nalazi na vrpci alata u bloku "analiza".



2. Spisak alata koji su dostupni u Paket analiza. Odaberite ime od njih "pokretni prosjek" i pritisnite dugme "UREDU".



3. Otvara se prozor za unos podataka za prognozu metodom pokretnog prosjeka.

   Na terenu "Input interval" Navodimo adresu raspona u kojem se nalazi mjesečni iznos prihoda bez ćelije u kojoj bi se podaci trebali izračunati.

   Na terenu "Interval" trebalo bi da odredite interval za obradu vrijednosti metodom izravnavanja. Prvo, postavimo vrijednost izglađivanja na tri mjeseca i stoga unesemo broj "3".

   Na terenu "Izlazni interval" potrebno je navesti proizvoljan prazan raspon na listu gdje će se podaci prikazati nakon obrade, koji bi trebao biti za jednu ćeliju veći od intervala unosa.

   Također biste trebali označiti kućicu pored parametra "Standardne greške".

   Ako je potrebno, također možete označiti polje pored stavke "Izlaz grafa" za vizuelnu demonstraciju, iako u našem slučaju to nije neophodno.

   Nakon što ste izvršili sva podešavanja, kliknite na dugme "UREDU".



4. Program prikazuje rezultat obrade.



5. Sada ćemo izvršiti izravnavanje u periodu od dva mjeseca kako bismo utvrdili koji je rezultat ispravniji. U ove svrhe ponovo pokrećemo alat "pokretni prosjek" Paket analiza.

   Na terenu "Input interval" ostavljamo iste vrijednosti kao u prethodnom slučaju.

   Na terenu "Interval" stavi broj "2".

   Na terenu "Izlazni interval" Naznačavamo adresu novog praznog raspona, koji opet mora biti za jednu ćeliju veći od ulaznog intervala.

   Ostale postavke ostavljamo istim. Nakon toga kliknite na dugme "UREDU".



6. Nakon toga, program vrši proračun i prikazuje rezultat na ekranu. Da bismo utvrdili koji je od ova dva modela tačniji, potrebno je uporediti standardne greške. Što manje ovaj indikator, veća je vjerovatnoća tačnosti dobijenog rezultata. Kao što vidite, za sve vrijednosti standardna greška pri izračunavanju dvomjesečnog pokretnog prosjeka je manja od istog indikatora za 3 mjeseca. Tako se predviđena vrijednost za decembar može smatrati vrijednošću izračunatom kliznom metodom za posljednji period. U našem slučaju, ova vrijednost je 990,4 hiljade rubalja.

Metoda 2: Upotreba funkcije AVERAGE

Postoji još jedan način korištenja metode pokretnog prosjeka u Excelu. Da biste ga koristili, potrebno je koristiti niz standardnih programskih funkcija, od kojih je osnovna za našu svrhu PROSJEČNO. Na primjer, koristićemo istu tabelu prihoda preduzeća kao u prvom slučaju.

Kao i prošli put, moraćemo da kreiramo izglađene vremenske serije. Ali ovoga puta akcije neće biti toliko automatizovane. Trebali biste izračunati prosjek za svaka dva, a zatim tri mjeseca da biste mogli uporediti rezultate.

Prije svega, izračunajmo prosječne vrijednosti za dva prethodna perioda koristeći funkciju PROSJEČNO. To možemo učiniti tek od marta, jer za više kasnijim datumima dolazi do prekida u vrijednostima.

1. Odaberite ćeliju u praznoj koloni u redu za mart. Zatim kliknite na ikonu "Insert Function", koji se nalazi u blizini trake formule.



2. Prozor je aktiviran Čarobnjaci funkcija. U kategoriji "Statistički" tražeći značenje "PROSJEČNO", odaberite ga i kliknite na dugme "UREDU".



3. Otvara se prozor argumenata operatora PROSJEČNO. Njegova sintaksa je sljedeća:

   PROSEK(broj1,broj2,…)

   Potreban je samo jedan argument.

   U našem slučaju, na terenu "Broj 1" moramo dati link do raspona u kojem su naznačeni prihodi za prethodna dva perioda (januar i februar). Postavite kursor u polje i odaberite odgovarajuće ćelije na listu u koloni "prihod". Nakon toga kliknite na dugme "UREDU".



4. Kao što vidite, rezultat izračunavanja prosječne vrijednosti za prethodna dva perioda je prikazan u ćeliji. Da bismo izvršili slične proračune za sve ostale mjesece perioda, moramo ovu formulu kopirati u druge ćelije. Da biste to učinili, postavite kursor u donji desni kut ćelije koja sadrži funkciju. Kursor se mijenja u ručicu za popunjavanje koja izgleda kao križ. Držite lijevu tipku miša i povucite je do samog kraja kolone.



5. Dobijamo izračun rezultata prosječne vrijednosti za prethodna dva mjeseca prije kraja godine.



6. Sada odaberite ćeliju u sljedećoj praznoj koloni u redu za april. Pozivanje prozora argumenata funkcije PROSJEČNO na isti način kao što je ranije opisano. Na terenu "Broj 1" unesite koordinate ćelija u koloni "prihod" od januara do marta. Zatim kliknite na dugme "UREDU".



7. Koristeći marker za popunjavanje, kopirajte formulu u ćelije tabele ispod.



8. Dakle, izračunali smo vrijednosti. Sada, kao i prethodni put, moraćemo da shvatimo koja je vrsta analize bolja: sa izglađivanjem od 2 ili 3 meseca. Da biste to učinili, trebali biste izračunati standardnu ​​devijaciju i neke druge pokazatelje. Prvo, izračunajmo apsolutnu devijaciju koristeći standard Excel funkcija ABS, koji umjesto pozitivnog ili negativni brojevi vraća njihov modul. Ova vrijednost će biti jednaka razlici između stvarnog pokazatelja prihoda za odabrani mjesec i predviđenog. Postavite kursor u sljedeću praznu kolonu u redu za maj. Zovem Čarobnjak za funkcije.



9. U kategoriji "matematički" označite naziv funkcije "ABS". Kliknite na dugme "UREDU".



10. Otvara se prozor sa argumentima funkcije ABS. U jednom polju "Broj" označavaju razliku između sadržaja ćelija u kolonama "prihod" I "2 mjeseca" za maj. Zatim kliknite na dugme "UREDU".



11. Koristeći marker za popunjavanje, kopirajte ovu formulu u sve redove tabele do zaključno novembra.



12. Izračunavamo prosječnu vrijednost apsolutnog odstupanja za cijeli period koristeći nam već poznatu funkciju PROSJEČNO.



13. Sličnu proceduru izvodimo kako bismo izračunali apsolutno odstupanje za 3-mjesečni pokretni prosjek. Prvo primjenjujemo funkciju ABS. Samo ovaj put izračunavamo razliku između sadržaja ćelija sa stvarnim prihodima i planiranim prihodima, izračunate metodom pokretnog prosjeka za 3 mjeseca.



14. Zatim izračunavamo prosječnu vrijednost svih podataka o apsolutnom odstupanju koristeći funkciju PROSJEČNO.



15. Sljedeći korak je izračunavanje relativnog odstupanja. On je jednak omjeru apsolutnog odstupanja i stvarnog pokazatelja. Kako bismo izbjegli negativne vrijednosti, ponovo ćemo koristiti mogućnosti koje operater nudi ABS. Ovog puta, koristeći ovu funkciju, dijelimo apsolutnu vrijednost odstupanja pri korištenju metode dvomjesečnog pokretnog prosjeka sa stvarnim prihodom za odabrani mjesec.



16. Ali relativno odstupanje se obično prikazuje u postocima. Stoga odaberite odgovarajući raspon na listu i idite na karticu "Dom", gdje je u bloku alata "Broj" u posebnom polju za formatiranje postavljamo procentualni format. Nakon toga, rezultat izračunavanja relativnog odstupanja se prikazuje u procentima.



17. Izvodimo sličnu operaciju za izračunavanje relativnog odstupanja sa podacima koristeći izravnavanje za 3 mjeseca. Samo u ovom slučaju za izračunavanje dividende koristimo drugu kolonu tabele, kojoj imamo ime “Aps. isključeno (3m)". Onda prevodimo numeričke vrijednosti u procentualnom obliku.



18. Nakon toga izračunavamo prosječne vrijednosti za oba stupca s relativnim odstupanjem, kao prije korištenja funkcije PROSJEČNO. Budući da za proračun uzimamo procentualne vrijednosti kao argumente funkcije, nema potrebe za dodatnom konverzijom. Izlazni operator proizvodi rezultat u procentualnom formatu.



19. Sada dolazimo do izračunavanja standardne devijacije. Ovaj indikator će nam omogućiti da direktno uporedimo kvalitet proračuna kada koristimo glačanje za dva i tri mjeseca. U našem slučaju, standardna devijacija će biti jednaka kvadratnom korijenu zbira kvadrata razlika između stvarnog prihoda i pokretnog prosjeka, podijeljenog sa brojem mjeseci. Da bismo izvršili proračune u programu, posebno moramo koristiti brojne funkcije ROOT, ZBIR DRUGAČIJI I CHECK. Na primjer, za izračunavanje standardne devijacije kada se koristi linija za izravnavanje dva mjeseca u svibnju, u našem slučaju će se koristiti sljedeća formula:

    SQRT(SUMVARE(B6:B12,C6:C12)/COUNT(B6:B12))

    Kopiramo ga u druge ćelije kolone i izračunavamo standardnu ​​devijaciju pomoću markera za popunjavanje.



20. Izvodimo sličnu operaciju za izračunavanje standardne devijacije za 3-mjesečni pokretni prosjek.



21. Nakon toga izračunavamo prosječnu vrijednost za cijeli period za oba ova indikatora koristeći funkciju PROSJEČNO.



22. Uporedivši proračune koristeći metodu pokretnog prosjeka sa izravnavanjem od 2 i 3 mjeseca prema indikatorima kao što su apsolutno odstupanje, relativno odstupanje i standardna devijacija, sa sigurnošću možemo reći da glačanje u trajanju od dva mjeseca daje pouzdanije rezultate od primjene zaglađivanja u trajanju od tri mjeseca. O tome svjedoči i činjenica da su gore navedeni pokazatelji za dvomjesečni pokretni prosjek manji nego za tromjesečni pokretni prosjek.



23. Tako će projektovani prihod preduzeća za decembar biti 990,4 hiljade rubalja. Kao što vidite, ova vrijednost se potpuno poklapa s onom koju smo dobili prilikom izračunavanja pomoću alata Paket analiza.

Izračunali smo prognozu koristeći metodu pokretnog prosjeka na dva načina. Kao što vidite, ovaj postupak je mnogo lakši za izvođenje pomoću alata Paket analiza. Međutim, neki korisnici ne vjeruju uvijek automatskom proračunu i radije koriste funkciju za proračune PROSJEČNO i pratećim operaterima kako bi provjerili najpouzdaniju opciju. Iako, ako je sve urađeno ispravno, konačni rezultat proračuna trebao bi biti potpuno isti.

U poslu, kao iu svakoj drugoj aktivnosti, čovjek želi znati šta će se dalje dogoditi. Teško je čak i zamisliti bogatstvo tog srećnika koji bi mogao da pogodi budućnost sa 100% tačnošću. Ali, nažalost (ili, na sreću), dar predviđanja je izuzetno rijedak. ALI... pokušajte barem generalni nacrt Preduzetnik jednostavno mora zamisliti buduću poslovnu situaciju.

U početku sam želio u jednom postu pisati o nekoliko jednostavnih i praktičnih tehnika odjednom, ali je post počeo ispadati vrlo dug. I tako će biti nekoliko postova posvećena temi prognoziranje. U ovom postu ćemo opisati jednu od najjednostavnijih metoda predviđanja koristeći Excel mogućnosti - metodu pokretnog prosjeka.

Najčešće u praksi marketinško istraživanje predviđene su sljedeće vrijednosti:

• Obim prodaje
• Veličina i kapacitet tržišta
• Obim proizvodnje
• Obim uvoza
• Dinamika cijena
• I tako dalje.

Za predviđanje koje razmatramo u ovom postu, savjetujem vam da se pridržavate sljedećeg jednostavnog algoritma:

1. Prikupljanje sekundarnih informacija o problemu(po mogućnosti i kvantitativno i kvalitativno). Tako, na primjer, ako predvidite veličinu vašeg tržišta, trebate prikupiti statističke podatke o tržištu (obim proizvodnje, uvoz, dinamika cijena, obim prodaje, itd.) kao i trendove, probleme ili tržišne prilike. Ako predviđate prodaju, tada su vam potrebni podaci o prodaji za period. Za predviđanje, što više istorijskih podataka uzmete u obzir, to bolje. Preporučljivo je dopuniti predviđanje analizom faktora koji utiču na predviđeni fenomen (možete koristiti SWOT, PEST analizu ili bilo koju drugu). To će vam omogućiti da shvatite logiku razvoja, a samim tim i provjerite vjerodostojnost određenog trend modela.

2. Dalje je poželjno provjeriti kvantitativne podatke. Da biste to učinili, trebate usporediti vrijednosti istih pokazatelja, ali dobivene iz različitih izvora. Ako se sve slaže, možete "ubaciti" podatke u Excel. Podaci takođe moraju ispunjavati sledeće uslove:

• Osnovna linija uključuje rezultate posmatranja - od najranijih do najnovijih.
• Svi osnovni vremenski periodi imaju isto trajanje. Podaci iz jednog dana, na primjer, ne bi se trebali miješati sa trodnevnim prosjekima.
• Posmatranja se bilježe u istoj tački u svakom vremenskom periodu. Na primjer, promet treba mjeriti u isto vrijeme.
• Preskakanje podataka nije dozvoljeno. Izostavljanje čak i jednog rezultata opservacije je nepoželjno prilikom prognoziranja“, stoga, ako vašim zapažanjima nedostaju rezultati u kratkom vremenskom periodu, pokušajte ih popuniti barem približnim podacima.

3. Nakon provjere podataka, možete primijeniti različite tehnike predviđanja. Želeo bih da počnem od samog početka jednostavna metoda – METODA POMOĆNOG PROSEKA

METODA POMOĆNOG PROSEKA

Metoda pokretnog prosjeka je prilično jednostavna za korištenje, ali je previše jednostavna za izradu tačne prognoze. Koristeći ovu metodu, prognoza za bilo koji period nije ništa drugo nego uzimanje prosjeka nekoliko prethodnih opservacija vremenske serije. Na primjer, ako ste odabrali tromjesečni pokretni prosjek, prognoza za maj bi bila prosjek za februar, mart i april. Odabirom četveromjesečnog pokretnog prosjeka kao metode predviđanja, možete procijeniti majsku cifru kao prosjek brojki za januar, februar, mart i april.

Tipično, prognoza pokretnog prosjeka se posmatra kao prognoza za period neposredno nakon perioda posmatranja. Istovremeno, takva prognoza je primjenjiva kada se fenomen koji se proučava razvija uzastopno, tj. Postoje određeni trendovi, a krivulja vrijednosti ne skače po dijagramu kao luda.

Da biste odredili koliko zapažanja želite da uključite u pokretni prosek, morate se osloniti na prethodno iskustvo i informacije koje imate o skupu podataka. Mora se uspostaviti ravnoteža između povećanog odgovora pokretnog prosjeka na posljednjih nekoliko zapažanja i velike varijabilnosti tog prosjeka.

Pa kako to uraditi unutraExcel

1. Recimo da imate mjesečni obim prodaje za posljednjih 29 mjeseci. I želite da odredite koliki će obim prodaje biti u 30. mjesecu. Ali, da budemo iskreni, uopće nije potrebno raditi s 30 prilikom izračunavanja vrijednosti prognoze. istorijska značenja, jer će ova metoda koristiti samo nekoliko poslednjih meseci. Dakle, samo proteklih nekoliko mjeseci su dovoljni za obračun.

2. Ovu tabelu dovodimo u razumljiv Excel oblik, tj. tako da su sve vrijednosti u istom redu.

3. Zatim uvodimo formulu za izračunavanje prosjeka na osnovu prethodne tri (četiri, pet? kako odaberete) vrijednosti (vidi u). Najpogodnije je koristiti posljednje 3 vrijednosti za proračune, jer ako uzmete u obzir više, podaci će biti previše prosječni, ako uzmete u obzir manje, neće biti tačni.

4. Korištenje funkcije autocomplete za sve naredne vrijednosti do 30, predviđenog mjeseca. Tako će funkcija izračunati prognozu za jun 2010. Prema prognoznim vrijednostima, prodaja u junu će biti oko 408 jedinica robe. Ali imajte na umu da ako je trend pada konstantan, kao u našem primjeru, izračunavanje prognoze na osnovu prosjeka će biti malo precijenjeno, ili će se činiti da „zaostaje“ za stvarnim vrijednostima.

Pogledali smo jedan od najpopularnijih jednostavne tehnike prognoziranje – metoda pokretnog prosjeka. U narednim objavama ćemo se osvrnuti na druge, preciznije i složenije tehnike. Nadam se da će vam moj post biti od koristi.

Odaberite iz menija Servis stav Analiza podataka, pojavit će se prozor s istim imenom, čiji je glavni element područje Alati za analizu. Ovo područje daje listu implementiranih Microsoft Excel metode statističke obrade podataka. Svaka od navedenih metoda implementirana je u formu poseban režim rad, da biste ga aktivirali, potrebno je da odaberete odgovarajući metod pomoću pokazivača miša i kliknete na dugme OK. Nakon što se pojavi dijaloški okvir za pozvani način rada, možete početi s radom.

Način rada " Pokretni prosek» služi za izravnavanje nivoa empirijskog vremenske serije baziran na metodi jednostavnog pokretnog prosjeka.

Način rada " Eksponencijalno izglađivanje» služi za izglađivanje nivoa empirijskih vremenskih serija zasnovanih na metodi jednostavnog eksponencijalnog izglađivanja.

U dijaloškim okvirima ovih modova (slike 2 i 3) postavljaju se sljedeći parametri:

2. Potvrdni okvir Oznake– aktivno stanje se postavlja ako prvi red (kolona) u opsegu unosa sadrži zaglavlja. Ako nema zaglavlja, potvrdni okvir treba deaktivirati. U ovom slučaju, automatski će se kreirati standardni nazivi za podatke o opsegu izlaza.

3. Interval(samo u dijalogu Pokretni prosek) – unesite veličinu prozora za izravnavanje R. Default p=3.

Slika 2 – Pokretni prosek za dijalog

4. Faktor prigušenja(samo u dijaloškom okviru Eksponencijalno izglađivanje) – unesite vrijednost eksponencijalnog koeficijenta izravnavanja str. zadano, p=0,3.

5. Interval izlaza / Novi radni list / Nova radna sveska– na poziciji Izlazni interval aktivira se polje u koje morate unijeti vezu do gornje lijeve ćelije opsega izlaza. Veličina izlaznog raspona će se automatski odrediti i na ekranu će se pojaviti poruka ako se izlazni raspon može preklapati sa izvornim podacima. U poziciji se otvara novi radni list novi list, u kojoj počevši od ćelije A1 rezultati analize se ubacuju. Ako trebate navesti ime u polju koje se nalazi nasuprot odgovarajućeg položaja prekidača. U poziciji se otvara Nova radna sveska Nova knjiga, na čijem prvom listu počevši od ćelije A1 rezultati analize se ubacuju.

6. Grafički izlaz– se postavlja u aktivno stanje za automatsko generiranje grafova stvarnih i teoretskih nivoa dinamičke serije na radnom listu.

7. Standardne greške– postavite na aktivno ako želite uključiti kolonu koja sadrži standardne greške u izlazni raspon.

Slika 3 – Eksponencijalno izglađivanje dijaloški okvir

Primjer 1.

Podaci o prodaji (milioni rubalja) poljoprivrednih proizvoda u trgovinama potrošačke kooperacije u gradu prikazani su u tabeli generisanoj na radnom listu Microsoft Excel (slika 4). U navedenom periodu (2009 – 2012) potrebno je identifikovati glavni trend u razvoju ovog ekonomskog procesa.

Slika 4 – Početni podaci

Za rješavanje problema koristimo način rada “ Pokretni prosek" Vrijednosti parametara postavljenih u istoimenom dijaloškom okviru prikazane su na slici 5, indikatori izračunati u ovom režimu su na slici 6, a konstruisani grafikoni su na slici 7.

Slika 5 – Popunjavanje dijaloškog okvira

Slika 6 – Rezultati analize

Slika 7 – Pokretni prosjek

Kolona D (slika 5) izračunava vrednosti izglađenih nivoa. Na primjer, vrijednost prvog izglađenog nivoa se izračunava u ćeliji D5 koristeći formulu =PROSEK(C2:C5), vrednost drugog izglađenog nivoa se izračunava u ćeliji D6 koristeći formulu =PROSEK(C5:C8), itd. .

Kolona E izračunava standardne greške koristeći formulu =ROOT(SUMAVARNA (blok stvarne vrijednosti; blok predviđene vrijednosti) / veličina prozora za izravnavanje).

Na primjer, vrijednost u ćeliji E10 se izračunava pomoću formule =ROOT(SUMQVAR(C7:C10,O7:B10)/4).

Međutim, kao što je gore navedeno, ako je veličina prozora za izravnavanje paran broj ( r=2m), tada se izračunata prosječna vrijednost ne može porediti ni sa jednom određenom tačkom u vremenu t, pa je potrebno primijeniti postupak centriranja.

Za ovaj primjer p=4, tako da je postupak centriranja neophodan. Tako je prvi uglađeni nivo (265,25) zabilježen između II i III kvartala. 2009, itd. Primjenom procedure centriranja (za ovo koristimo funkciju AVERAGE), dobijamo izglađene nivoe sa centriranjem. Za III kV. 2009, određena je sredina između prvog i drugog izglađenog nivoa: (265,25 + 283,25)/2 = 274,25; za IV kvartal 2009, drugi i treći izglađeni nivo su centrirani: (283,25 + 292,00)/2 = 287,6, itd. Izračunate vrijednosti su prikazane u tabeli 1. Prilagođeni grafikon pokretnog prosjeka je prikazan na slici 8.

Tabela 1 – Dinamika uglađenih nivoa prodaje proizvoda

Godina	Kvart	Iznos prodaje, milion rubalja.	Glatki nivoi sa centriranjem
		274,25
		287,63
			297,00
		307,50
		334,63
		374,13
			402,88
		421,00
		429,00
		430,75
			435,38
		446,63

Slika 8 – Prilagođeni grafikon pokretnog prosjeka

Primjer 2.

Razmatrani problem se također može riješiti metodom jednostavnog eksponencijalnog izglađivanja. Da biste to učinili, trebate koristiti način rada „Eksponencijalno izglađivanje“. Vrijednosti parametara postavljenih u istoimenom dijaloškom okviru prikazane su na slici 9, indikatori izračunati u ovom modusu prikazani su na slici 10, a konstruisani grafikoni prikazani su na slici 11.

Slika 9 – Popunjavanje dijaloškog okvira Eksponencijalno izglađivanje

Slika 10 – Rezultati analize

Slika 11 – Eksponencijalno izglađivanje

Kolona D (slika 10) izračunava vrednosti izglađenih nivoa na osnovu rekurentnih odnosa.

U koloni E, standardne greške se izračunavaju pomoću formule =ROOT(SUMVARE (blok stvarne vrijednosti; blok predviđene vrijednosti) / 3). Kao što je lako vidjeti (uporedite slike 8 i 11), kada se koristi metoda jednostavnog eksponencijalnog izglađivanja, za razliku od metode jednostavnog pokretnog prosjeka, mali valovi su očuvani.

Povratak