Excel savet: Kako da optimizujete ulazne parametre

Kada računamo vrednosti formula, uobičajeno je da znamo ulazne parametre, pa da na kraju procesa dobijemo rezultat. Često poželimo da dobijemo određenu konačnu vrednost, a da pri tome promenimo neki od ulaznih parametara. Umesto da transformišemo celu formulu tako da s leve strane ostane nepoznata vrednost, možemo upotrebiti funkciju Goal Seek. Na stranu i činjenica da je ovakva jednoznačna transformacija formule obično vrlo komplikovana, a ponekad i neprimenjiva. U ovakvim situacijama potrebno je iterativno isprobati niz ulaznih vrednosti koje se menjaju sve dok se ne dobije zadata vrednost.

S obzirom na to da računari imaju veliku moć obrade sračunavanja, nizovi s milionima rastućih vrednosti mogu da se obrade takoreći u tren oka. Najveći deo vremena otići će vam na pametno formulisanje problema. Može se desiti da ćete imati formulu s više parametara i da nećete moći da „ubodete“ optimalnu vrednost iz prve, već ćete morati da optimizujete više ulaznih parametara. Goal Seek možete da primenite i u ovakvim situacijama, ali tako što ćete prvo pronaći „najbolju“ vrednost menjajući najuticajniji, tj. „glavni“ parametar, a nakon toga i pomoćne parametre, jedan po jedan. Goal Seek može da optimizuje samo jednu ulaznu vrednost, a za složenije optimizacije koje paralelno menjaju više parametara najbolje je da koristite Solver, što ćemo ostaviti za neki budući prilog.

Primer: Recimo da želite da pozajmite novac. Znate koliko vam je novca potrebno, znate i koliko vremena biste želeli da otplaćujete uzetu pozajmicu, kao i koliko novca biste mogli da se odreknete svakog meseca za otplatu. Goal Seek može da odgovori na pitanje da li je čitava konstrukcija realna, tako što će vam pomoći da odredite kamatu koja odgovara vašim željama i mogućnostima.

Najlakše je razumeti na primeru. Želimo da pozajmimo 200.000 dinara i da to otplaćujemo tri godine, tj. 36 meseci. Možemo sebi da dozvolimo otplatu do 8.000 dinara mesečno. Problem bismo definisali kao izračunavanje maksimalne kamate, čime bismo proverili i može li da se upotrebi kreditna kartica, keš kredit ili mora da se ide na namenski kredit. U tom računanju pomoći će nam PMT funkcija koja izračunava mesečnu ratu i period otplate. U ovom konkretnom slučaju mesečna rata biće cilj koji se traži.

U novu tabelu unesite Iznos zajma, Period, Kamatnu stopu i Ratu u poljima od A1 do A4. Unesite sve poznate vrednosti koje smo nabrojali, a to su Iznos zajma i Period u polja B1 i B2. Polje B3 ostavljamo prazno jer za sada ne znamo kolika je Kamatna stopa koja nam je prihvatljiva. U polje B4 unesite =PMT(B3/12, B2, B1). B3/12 je mesečni ekvivalent godišnje kamate koja se ugovara i kojom se inače uobičajeno operiše u svim izračunavanjima. S obzirom na to da smo polje B3 u startu ostavili prazno, PMT funkcija pretpostaviće da je podrazumevana Kamatna stopa 0% – lepo bi bilo da i vaša banka to pretpostavi, ali nažalost banke tako ne funkcionišu. Kao rezultat dobićemo da je potrebno da rata bude 5555,56 dinara. Rezultat je vrlo zgodan, trgovački broj koji je na sreću manji od naše maksimalne mesečne rate koju možemo da podnesemo.

Prelazimo na upotrebu opcije Goal Seek. Potrebno je da na Data ribbon tab‑u u Data Tools grupi (Excel 2013) ili Forecast grupi (Excel 2016) kliknete na What‑If Analysis i nakon toga na Goal Seek. Excel 2016 poseduje sjajnu novu mogućnost rada koja se zove Tell me what you want to do… (ili skraćeno samo Tell me…). Starije verzije Office paketa imale su mogućnost detaljnog kontekstnog sistema za pomoć koji je prikazivao uputstvo kako da nešto izvedete tokom rada s tabelama. Nova Tell me… opcija odlazi korak dalje: ona će umesto pokazivanja sama uraditi upravo ono što vi zadate u polje za pretragu. Dakle, ako zaboravite gde se Goal Seek nalazi, dovoljno je u polje Tell me… da unesete frazu Goal Seek i Excel će pokrenuti Goal Seek i prikazati prozor za zadavanje parametara. Još brže možete da dođete do cilja korišćenjem tasterske prečice Alt+Q.

Pozicionirajte se na polje D3 i pokrenite Goal Seek. U Set cell polje unesite B4 kao adresu formule koja se optimizuje. U polje To value unesite maksimalni iznos rate, tj. ‑8000. Negativan broj označava ratu koja smanjuje dug. U ► changing cell box unesite B3 – to je adresa polja u koje će se umetnuti odgovarajuća maksimalna Kamatna stopa koja se određuje. Nikako nemojte da zaboravite da ovo polje mora da bude pozvano u formuli koja se nalazi u polju B4.

Potvrdite sa OK i sačekajte trenutak dok se Goal Seek izvrši. Stara tabela biće izmenjena. Promeniće se sadržaj polja B3 gde će se prikazati da je maksimalna kamata 25%, a u B4 biće ispisano da je tada mesečna rata 8000 dinara. Sad preostaje još samo da pronađete odgovarajući bankarski proizvod koji će vam omogućiti da dobijete željeni zajam.