2. Razred - MS Excel - Funkcija Sta ako - Jelena Nikolic

 

Ekonomska škola-Niš

 

 

 

 

 

 

Tema: ANALIZA ŠTA-AKO

 

 

 

 

 

 

 

 

Učenica: Jelena Nikolić                                        Profesor: Dejan Pejčić    

Odeljenje: IV-F2

 

 

 

 

 

 

 

Niš

 

 

 

 

Sadržaj

 

 

1.  Uvod u Analizu šta-ako

2.  Scenarija

3.  Tabele podataka

4.  Traženje cilja- Goal seek

5.  Solver

6.  Zadaci

 

 

 

 

 

 

 

 

 

 

 

 

 

Analiza šta-ako

 

 

 

 

Analiza šta-ako je alatka u programu Microsoft Excel-a i sa njom se može eksperimentisati i to sa nekoliko različitih skupova vrednosti u nekim formulama da bismo istražili razne rezultate.

Pomoću analize šta-ako možemo navesti rezultat koji želimo da formula kreira a zatim da utvrdimo koje skupove vrednosti će proizvesti taj rezultat. Ovaj program nam omogućava nekoliko različitih alatki koje će nam pomoći da izvrše tip analize koji najviše odgovara našim potrebama. Proces promene vrednosti u datim ćelijama predstavlja analizu šta-ako i na taj način mogu da se vide promene koje utiču na rezultate formula na radnom listu.

 

U Excel-u postoje tri vrste alatki za analizu šta-ako i to:

·        Scenarije

·        Tabele podataka  

·        Traženje cilja.

 

Između ova tri dodatka postoje bitne razlike.  Scenarije i tabele podataka utvrđuju rezultate na osnovu vrednosti unosa, dok traženje cilja funkcioniše na osnovu rezultata koji utvrđuje moguće vrednosti unosa i daju taj rezultat. Pored ova tri dodatka može se upotrebiti i četvrti a to je Solver. Solver je sličan traženju cilja, ali u njemu može da stane više promenljivih vrednosti.

 

 

 

 

 

 

 

 

 

 

 

 

   I.            Scenariji

 

Scenariji predstavljaju deo komandi programskog paketa koji se naziva analiza šta-ako.

Scenariji može da ima više promenljivih ali se može prilagoditi samo do 32 vrednosti. Scenariji je grupa vrednosti koje Excel čuva i može uvek da ih zameni u našem radnom listu. Ovaj programski dodatak nalazi se u meniju Tools i pomoću ovog dodatka možemo da kreiramo i sačuvamo različite grupe vrednosti  na radnom listu a onda da se prebacimo na bilo koji od ovih novih scenarija da bismo pregledali različite rezultate. Scenariji se koriste npr. kada nismo sigurni koliki je naš prihod i tada možemo da definišemo različite vrednosti prihoda da bi kasnije mogli da se prebacujemo između scenarija da bismo vršili analizu šta-ako. Prikazivanje scenarija se vrši tako što u meniju Alatke izaberemo stavku Scenariji, zatim izaberemo ime scenarija i kliknemo na dugme Prikaži.  Prilikom kreiranja scenarija, prvo se izabere opcija Scenariji, klikne se na Dodaj a zatim u polju ime scenarija unesemo ime za scenario a u polju Promenljive ćelije unesemo reference za ćelije koje želimo da promenimo.

 

Uneti podaci se mogu i zaštiti tj. sprečiti izmene i to preko opcije spreči promene da bismo sprečili druge korisnike da izvrše neke izmene. Scenario je uvek pristupačan iako je zaštićen, ali se ne može urediti ili izbrisati sve dok se u dijalogu Uređivanje scenarija ne opozove izbor Spreči promene. Prilikom uređivanja scenarija možemo da zadržimo njen naziv nakon promene vrednosti i na taj način nove vrednosti zamenjuju vrednosti u originalnom scenariju. Postoji opcija objedinjavanja scenarija koja funkcioniše jednostavno. Potrebno je da se scenariji objedine kada su na radnim listovima identični svi modeli šta-ako. Sve ćelije koje se nalaze na izvornom radnom listu moraju da se odnose i na odgovarajuće ćelije koje se menjaju na radnom listu koji je aktivan. Sve scenarije sa izvornog radnog lista Microsoft Excel kopira na aktivan radni list. Da bismo sve objedinili potrebno je da izaberemo radni list i da u meniju Alatke izaberemo opciju Scenariji i kliknemo na dugme Objedini, a zatim izaberemo ime radne sveske i lista. Scenarije se mogu i obrisati i to veoma jednostano. Potrebno je samo da izaberemo stavku Scenariji i odredimo ime scenarija koji želimo da izbrišemo i zatim kliknemo na dugme izbriši.

 

 

 

II.            Tabele podataka

 

Tabela podataka radi sa jednom ili dve promenljive ali može da prihvati mnoge različite vrednosti za te promenljive. Tabele podataka predstavljaju opseg ćelija tj. kako promena određenih vrednosti utiče na rezultate formula. Ovaj dodatak omogućava kraći put za izračunavanje više verzija u jednoj operaciji. Na primer, tabelu podataka možemo da upotrebimo da bismo utvrdili moguću mesečnu ratu prilikom promene  kamatne stope i dužine otplate koji se koriste u kreditima. Razlika između scenarije i tabele podataka jeste u tome što tabele podataka imaju samo jednu ili dve promenljive vrednosti-jednu za ulaznu ćeliju reda i jednu za ulaznu ćeliju kolone kao i neograničen broj različitih vrednosti dok scenario može imati maksimalno 32 različite vrednosti ali možemo kreirati neograničen broj scenarija. U zavisnosti od broja promenljivih i formula koje želimo da testiramo, možemo da kreiramo tabele podataka i to:

 

a.      Sa jednom promenljivom

b.     Sa dve promenljive

 

Tabela podataka sa jednom promenljivom koristi se kada želimo da utvrdimo kako će različite vrednosti jedne promenljive u jednoj ili više formula uticati na promene rezultata tih formula.  Formule koje se koriste u tabeli podataka sa jednom promenljivom moraju da upućuju samo na jednu ulaznu ćeliju. Ulazna ćelija je ona ćelija u kojoj se smenjuju sve ulazne vrednosti. Zamena vrednosti vrši se tako što se u jednu kolonu ili u jedan red otkuca lista vrednosti i ostave se nekoliko praznih redova i kolona sa obe strane vrednosti. Dalji postupak se može izvesti na više načina i to:

 

·        Ako je tabela organizovana po kolonama, formulu kucamo u ćeliju koja je jedan red iznad i jednu ćeliju udesno od kolone sa vrednostima.

 

·        Ako je tabela oranizovana po redovima, formulu kucamo u ćeliju koja se nalazi jednu kolonu ulevo od prve vrednosti i jednu ćeliju ispod reda sa vrednostima.

 

Onda izaberemo opseg ćelija koje sadrže formule i vrednosti koje želimo da zamenimo. U grupi Alatke podataka izaberemo stavku Analiza šta-ako a odmah zatim i opciju Tabela sa podacima. Ako je tabela organizovana po kolonama onda u polje Ulazna ćelija kolone unesemo referencu ulazne ćelije. Referenca je kordinantni skup koji opisuje položaj određene ćelije. Ukoliko je tabela organizovana po redovima, u polje Ulazna ćelija reda upiše se referenca ulazne ćelije.

 

Za razliku od ove tabele, tabela podataka sa dve promenljive koristi se kako bismo utvrdili kako će različite vrednosti dve promenljive u jednoj formuli uticati na promenu rezultata. Formula mora da upućuje na dve različite ulazne ćelije. U ćeliju na radnom listu unesemo formulu koja se odnosi na ulazne ćelije. Jedna lista ulaznih vrednosti upisuju se u koloni ispod formule, zatim se unosi druga lista u istom redu gde je i formula samo sa desne strane i odabere se opseg koji sadrži formulu, vrednosti redova i kolona kao i ćelije u kojima želimo da budu izračunate vrednosti. Izaberemo opciju Tabela sa podacima iz stavke Analiza šta-ako i unesemo referencu u polja Ulazna ćelija kolone i reda. Ako se desi da nakon kreiranja tabele, ne želimo da nastavimo da radimo sa funkcijama tabele koja ona pruža, možemo prestati sa radom podataka u tabeli a da pritom ne izgubimo oblikovan stil tabele i to zadavanjem opcije Konvertuj u opseg. Ukoliko želimo da povratimo sve dovoljno je da kliknemo u traci sa alatkama za brži pristup na dugme Opozovi radnju. Tabela podataka se može i cela obrisati zadavaljem funkcije Obriši u meniju Uređivanje.

 

 

 

III.            Traženje cilja (Goal Seek)

 

Traženje cilja je izgrađen u Excelu i to je funkcija koja nam pokazuje kako jedan podatak utiče na drugi. Traženje cilja predstavlja deo programskog paketa  koji se naziva Analiza šta-ako. I on se koristi kada znamo željeni rezultat jedne formule ali ne i ulaznu vrednost koja je potrebna da dođemo do rezultata. Traženje cilja je nemoguće izvesti ukoliko ne postoji ćelija koja sadrži formulu. I ne može se otkucati samo rezultat te formule već je potrebno da formula ima otkucane vrednosti npr. ne može se samo uneti da je ukupan prihod 50.000 dinara već se mora putem formule doći do tog rezultata npr. 100*500 = 50.000 dinara. Traženje cilja se nalazi u meniju Tools. Traženje cilja ili Goal seek predstavlja metod pronalaženja određene vrednosti na osnovu druge ćelije. Goal seek funkcioniše tako sto Microsoft Excel menja vrednosti u jednoj određenoj ćeliji sve dok formula ne prikaže željeni rezultat. Ćelije se na sledeći način prilagođavaju da bi proizvele određenu vrednost:

 

 

1.     U meniju Tool se odabere stavka Goal seek

2.     U okviru Set cell selektujemo ćeliju u kojoj se nalazi formula.

3.     U polju To value unesemo željeni rezultat

4.     i u By changing cell unesemo referencu na ćeliju sa vrednošću koju želimo da podesimo. Na ovu ćeliju mora da upućuje formula koja je precizirana u okviru Set cell....zatim kliknemo na dugme Ok.

 

 

Funkija Traženje cilja funkcioniše samo sa jednom promenljivom vrednošću unosa. Ukoliko želimo da imamo dve ili više ulaznih vrednosti onda Goal seek ne možemo koristiti već bi upotrebili programski dodatak Solver. Ova funkcija Microsoft Excel-a je veoma efikasna jer na brz i lak način može da nas dovede do željenog cilja.

Goal seek se dosta primenjuje prilikom izračunavanja prihoda tj. finansijskog poslovanja nekog preduzeća, a pored toga što se koristi za izračunavanje prihoda upotrebljava se i kod utvrđivanja kamatne stope kod koje se koristi i funkcija PMT. PMT je finansijska funkija i koristi se za izračunavanje mesečne rate kredita na osnovu konstantnih otplata i konstantnih kamatnih stopa.

 

 

IV.            Solver

 

Solver je isto deo paketa komandi sličan traženju cilja i spada u Analizu šta-ako.  Ovaj programski dodatak je veoma efikasan i pomoću njega možemo odrediti maksimalnu ili minimalnu vrednost tj. optimalnu za formulu u jednoj ćeliji. Ta ćelija ima naziv ćelija cilja i ona podleže ostalim ograničenjima. Promenljive odluke ili jednostavno promenljive ćelije ućestvuju u izračunavanju formula u ćelijama cilja kao i ograničenja. To je ustvari grupa ćelija koja radi zajedno sa Solverom.

Maksimalna ili minimalna vrednost određuje se na osnovu izmene drugih ćelija.

Ukoliko Solver ili grupa Analiza nisu pristupačni tj.dostupni, moramo da instaliramo programski dodatak. Učitavanje se vrši na taj način što se zada opcija Programski dodatak Solver koji se nalazi u programske dodatke Excel-a. Kada učitamo programski dodatak Solver  u polje Postavljanje cilja unosimo određenu referencu. Ćelije cilja obavezno mora da sadrži formulu. Postoje sledeća tri načina prilikom unosa vrednosti a to su:

 

·        Ukoliko želimo da vrednost ćelije cilja budu što je moguće veća, izabraćemo opciju Max.

·        Ukoliko želimo da vrednost ćelije cilja budu što je moguće manja, izabraćemo opciju Min.

·        I ukoliko želimo da ćelije cilja imaju neku određenu vrednost potrebno je da putem opcije Vrednost,  sami unesemo vrednost u polju.

 

Promenljive ćelije mogu biti direktno ili indirektno u vezi sa ćelijama cilja. Kod solvera postoji i takozvana ograničenja. I prilikom ograničenja potrebno je u polje uneti referencu ćelije ili ime opsega ćelije. Nakon toga se izaberu relacije tj. (<=, =, >=)  int.bin ili dif.

 

Kada se izabere opcija int.onda se u polju Ograničenja pojavi stavka integer, kod bin-a pojavi se binary i ukoliko izaberete dif.u polju se javlja stavka aldifferent. Relacije int, bin i dif možemo koristiti samo u ograničenjima za promenljive ćelije.

 

Parametri programskog dodatka Sover predstavljaju opciju pomoću koje možemo odabrati neke algoritme ili metode rešavanja:

 

·        GRG (Generalizes Reduces Gradient)- nelinearko, ovo se koristi za ravne, nelinearne probleme

·        LP simpleks- koristi se za linearne probleme

·        Evolucioni- koriste se za probleme koji nisu ravni.

 

V.             .Zadaci

 

1.    Preduzeće X je ove godine ostvarilo profit od 280.000 dinara prodajom 2.000 artikala po ceni od 140 dinara. Koliko će cenu preduzeće morati da poveća da bi ostvarilo profit od 350.000 dinara prodajom 2.000 artikala?

 

 

Rešenje zadatka

R
PRE		POSLE
Cena	140	Cena	175
Proizvodi	2000	Proizvodi	2000
Profit	280000	Profit	350000

 

Cena artikala mora da se poveća za 35 dinara da bi preduzeće ostvarilo profit od 350.000 dinara.

 

 

2.    Ako neko preduzeće ostvari godišnji profit od 5.000.000 dinara prodajom 2.500 proizvoda po ceni od 2.000 dinara. Koliko proizvoda mora da proda po istoj ceni da bi sledeće godine ostvarilo profit od 7.000.000?

 

 

Rešenje zadatka

 

PRE		POSLE
Cena	2000	Cena	2000
Proizvodi	2500	Proizvodi	3500
Profit	5000000	Profit	7000000

 

Preduzeće mora da proda 3.500 proizvoda da bi ostvarilo profit od 7.000.000 dinara.

 

3.    Neko preduzeće X 2011 godine prodalo je 10.000 proizvoda po ceni od 1.500 dinara. Da bi preduzeće ostvarilo 50 % veći profit od ostvarenog kolika cena proizvoda mora biti ako se zna da će isti broj proizvoda prodati?

 

Rešenje zadatka

 

PRE		POSLE
Cena	1500	Cena	2250
Proizvodi	10000	Proizvodi	10000
Profit	15000000	Profit	22500000

 

50% od 15000000 = 7.500.000

Novi profit = 15000000+7500000 = 22500000

 

Cena proizvoda mora biti 2.250 dinara da bi se ostvario profit od 22.500.000 dinara.