Utilizarea instrumentelor de analiza din Excel: GOAL SEEK, SOLVER

Un instrument puternic de analiza in Excel        este reprezentat de scenarii. Un scenariu este un set de valori pe care Excel il poate salva si substitui automat intr-o foaie de calcul. Goal Seek (Cautare rezultat) si Solver (Rezolvitor) sunt doua instrumente incluse in Excel pe care le poti utiliza pentru a analiza date si a obtine raspunsuri la probleme simple sau chiar destul de complexe. Goal Seek este in special utilizat atunci cand exista o singura variabila iar Solver cand ai mai multe variabile si restrictii.. Instrumentul Solver nu este destinat doar analizei financiare ci poate fi utilizat si pentru modele de productie, marketing si contabilitate. Acest instrument trebuie utilizat cand cauti un rezultat si ai mai multe restrictii (variabile care se modifica).

Poti utiliza scenarii pentru a prevedea rezultatul unei foi de calcul tip model. Poti crea si salva diferite grupuri de valori pe o foaie de calcul si apoi poti comuta intre aceste variante diferite de scenarii pentru a vedea rezultatele diferite. Spre exemplu, daca vrei sa-ti creezi un buget dar nu esti sigur de veniturile tale, atunci iti poti defini diferite variante de venituri si poti sa comuti intre scenarii pentru a realiza o analiza.

Pentru a compara diferite scenarii poti crea un raport care sa insumeze toate scenariile pe aceeasi pagina.  Raportul poate lista scenariile parte cu parte sau le poate aseza intr-un tabel pivot de raport.

Pentru a crea un scenariu trebuie sa parcurgi pasii:

Executa ToolsScenarios.

Executa click pe butonul Add. Apare caseta Add scenario.

In campul Scenario name editeaza un nume pentru scenariu.

In campul Changing cells introdu referintele pentru celulele pe care doresti sa le modifici.

In sectiunea Protection bifeaza Prevent changes daca vrei sa impiedici alte persoane sa efectueze modificari in scenariul tau sau Hide daca vrei sa ascunzi modificarile.

Executa click pe OK. Apare caseta Scenario Values.

In caseta Scenario Values editeaza valorile pe care le doresti pentru a modifica celulele.

Pentru a crea scenariul apasa butonul OK.

Atunci cand afisezi un scenariu schimbi valorile celulelor salvate ca parte a acestuia. Pentru a afisa un scenariu trebuie sa parcurgi pasii:

Executa ToolsScenarios.

Selecteaza numele scenariului pe care vrei sa-l vizualizezi.

Apasa butonul Show.

Pentru a crea un raport de scenarii trebuie sa parcurgi pasii:

Executa ToolsScenarios.

Executa click pe butonul Summary.

Alege  Scenario summary sau Scenario PivotTable.

In campul Result cells introdu referintele pentru celulele care refera celulele ale caror valori au fost modificate prin scenariu. Separa referintele cu virgula.

Atunci cand stii ce rezultat doresti sa obtii ca urmare a aplicarii unei formule dar nu stii valoarea operanzilor poti utiliza instrumentul Goal Seek (Cautare rezultat). Atunci cand cauti operanzii, Excel modifica valorile dintr-o celula specifica pana cand formula ajunge la rezultatul dorit.

Pentru a intelege cum actioneaza instrumentul Goal Seek sa cream un scenariu simplu: esti agent de vanzari si trebuie sa realizezi pana la sfarsitul anului o cota de vanzari de 500.000.000 lei pentru a primi un bonus. Se stie ca pana in prezent ai facut vanzari in valoare de 350.000.000 iar pretul unui obiect vandut este de 130.000 lei. Este adevarat ca ar fi mult mai usor sa aplici formula (500.000.000-350.000.000)/130.000 pentru a afla rezultatul, dar avantajul instrumentulului Goal Seek este ca poti crea formula o singura data dupa care poti schimba datele pentru a obtine rapid cai alternative catre obiectul propus.

Pentru a utiliza Goal Seek :

Selecteaza celula formulei (D7 in acest exemplu)

Analiza: Solutii software pentru arhivarea datelor - oferta actuala pe piata de software Eseu: Comenzi SET - introducerea datelor de la tastatura

Executa secventa ToolsGoal Seek . pentru a afisa caseta de dialog Goal Seek

Urmatoarea lista descrie intrarile pentru fiecare obiect din caseta de dialog:

Set cells (Se seteaza celula) specifica locatia formulei pe care o utilizezi pentru a obtine rezultatul final. In acest caz formula, se afla in celula D7 si nu face decat sa inmulteasca numarul de obiecte vandute cu pretul lor.

In caseta To value (La valoarea) introdu valoarea tinta.

In caseta By changing cell (Modificand celula) specifica locatia celulei variabilei pe care vrei sa o modifici pentru a ati atinge obiectivul – in acest caz vanzari in valoare de 500.000.000 lei.

Executa click pe OK sau apasa tasta Enter. De indata ce ai efectuat aceasta operatie Excel incepe cautarea obiectivului specificat

Daca doresti sa vinzi un numar fix de obiecte pentru a ajunge la aceeasi valoare totala de 500.000.000 lei va trebui sa determini un pret pe obiect. Pentru aceasta trebuie sa modifici parametrul By changing cell astfel incat sa indice celula C7. Atunci Goal Seek va mari pretul obiectelor la o valoare care sa egaleze la 500.000.000 lei dar sa pastreze numarul de bucati vandute la 2000 (spre exemplu).

Rezultatul final va fi:

Solver (Rezolvitor) este un instrument foarte puternic de analiza care foloseste mai multe variabile si restrictii ce se modifica pentru a gasi solutia optima de rezolvare a unei probleme.

Pentru a exemplifica cum functioneaza acest instrument se va calcula costul final al unui proiect, tinand cont de mai multe variabile. Se stie ca suma maxima de buget anual este de 500.000 $, costul pentru fiecare proiect nu trebuie sa depaseasca 50.000 $ si doresti sa optimizezi sau sa aduni sumele pentru marketing si publicitate.

Pentru a configura acest scenariu trebuie sa parcurgi urmatorii pasi:

Configureaza tabelul.

Creaza restrictiile (constrangerile).

Selecteaza celula destinatie G16 si executa secventa ToolsSolver.

In caseta de dialog Solver Parameters (Parametri rezolvitori) stabileste parametrii pe care doresti sa-i utilizezi in problema. Pentru acest exemplu, vei dori ca in celula destinatie sa apara totalul dolarilor cheltuiti (G16), care doresti sa fie egal cu valoarea maxima a bugetului, 500.000 $ - specificata in caseta Value of (Valoarea de). Solver va calcula cea mai buna dispersie pentru obtinerea rezultatului optim, ajustand cantitatile din domeniul By Changing Cells (Celulele care se modifica) E5:F14.

In continuare, trebuie sa adaugi restrictii la problema. Selecteaza Add din sectiunea Subject to the Constraints pentru a specifica prima restrictie. In acest exemplu, doresti sa cheltuiesti un total de exact 50.000 $ pentru oricare dintre proiecte. Celula cu restrictia este G21.

Pentru a adauga mai multe restrictii executa click , din nou, pe butonul Add si specifica restrictia. In acest exemplu vei mai adauga o restrictie pentru costurile de marketing.

Ultima restrictie este bugetul total de 500.000$ din celula G23. Dupa ultima restrictie nu executa click pe Add ci, dupa ce ai terminat cu restrictiile, executa click pe OK pentru a reveni in caseta Solver.

Executa click pe butonul Solver sau apasa tasta Enter pentru a porni rezolvarea problemei. In timp ce lucreaza, acesta afiseaza un mesaj in bara de stare.

Cand Solver ajunge la concluzia finala afiseaza o caseta de dialog care indica rezultatul si modifica valorile specificate din foaia de calcul pentru a indeplini obiectivul. In figura urmatoare se observa celulele modificate atunci cand Solver a creat solutia optima pentru problema.

De aici poti salva rezultatele Solver si crea un raport de raspuns care sa prezinte scenariul original al costurilor si rezultatul final. Selecteaza Answer (Raspuns) in lista Reports si executa click pe butonul Save Scenario . pentru a afisa caseta de dialog Save Scenario.

Daca doresti sa anulezi foaia de calcul pentru a reveni la valorile initiale, selecteaza optiunea Restore Original Values (Refacere valori initiale) pentru a relua procesul cu valorile initiale.

Executa click pe OK si Excel va reface valorile si va crea raportul de raspuns . Raportul de raspuns compara valorile originale cu cele modificate si indica celulele care au fost schimbate. In acest fel poti compara scenariile; reia de la datele originale si ai grija sa fie Answer activ.

Restrictiile sunt salvate ca registrul de calcul, deci nu va trebui sa le reintroduci de fiecare data cand deschizi registrul.

Daca Solver nu poate obtine o concluzie satisfacatoare plecand de la datele furnizate, va aparea o caseta de mesaj. Ajusteaza restrictiile sau variabilele dupa necesitati pentru a continua incercarea de rezolvare a problemei.

Solutia instrumentului Solver la o problema complexa poate fi corecta dar nerealista. Fii sceptic: verifica corectitudinea oricaror valori modificate inainte de a crea un raport sau a implementa orice sugestie venita de la Solver.

Poti modifica parametrii Solver inainte de a incepe rezolvarea problemei daca banuiesti ca obtinerea problemei poate dura prea mult sau necesita prea multa putere de calcul. Executarea unui click pe butonul Options din caseta de dilaog Solver Parameters duce la afisarea casetei de dialog Solver Options in care poti stabili numarul de iteratii ale problemei ce va fi rulat de Solver in cautarea unui raspuns sau interval de timp pe care il va petrece cautand inainte de a renunta.

In continuare sunt prezentate caseta si optiunile disponibile: