UTILIZAREA INSTRUMENTELOR DE ANALIZA DIN EXCEL

Un instrument puternic de analiza în Excel este reprezentat de scenarii. Un scenariu este un set de valori pe care Excel îl poate salva si substitui automat într-o foaie de calcul. Goal Seek (Cautare rezultat) si Solver (Rezolvitor) sunt doua instrumente incluse în 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 în special utilizat atunci când exista o singura variabila iar Solver când 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 când 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 252i83c apoi poti comuta între 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 îti poti defini diferite variante de venituri si poti sa comuti între scenarii pentru a realiza o analiza.

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

Pentru a crea un scenariu trebuie sa parcurgi pasii:

Executa Tools\Scenarios.

Executa click pe butonul Add. Apare caseta Add scenario.

În câmpul Scenario name editeaza un nume pentru scenariu.

În câmpul Changing cells introdu referintele pentru celulele pe care doresti sa le modifici.

În sectiunea Protection bifeaza Prevent changes daca vrei sa împiedici alte persoane sa efectueze modificari în scenariul tau sau Hide daca vrei sa ascunzi modificarile.

Executa click pe OK. Apare caseta Scenario Values.

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

Pentru a crea scenariul apasa butonul OK.

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

Executa Tools\Scenarios.

Selecteaza numele scenariului pe care vrei sa-l vizualizezi.

Apasa butonul Show.

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

Executa Tools\Scenarios.

Executa click pe butonul Summary.

Alege Scenario summary sau Scenario PivotTable.

În câmpul Result cells introdu referintele pentru celulele care refera celulele ale caror valori au fost modificate prin scenariu. Separa referintele cu virgula.

Atunci când 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 când cauti operanzii, Excel modifica valorile dintr-o celula specifica pâna când formula ajunge la rezultatul dorit.

Pentru a întelege cum actioneaza instrumentul Goal Seek sa cream un scenariu simplu: esti agent de vânzari si trebuie sa realizezi pâna la sfârsitul anului o cota de vânzari de 500.000.000 lei pentru a primi un bonus. Se stie ca pâna în prezent ai facut vânzari în valoare de 350.000.000 iar pretul unui obiect vândut 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 în acest exemplu)

Executa secventa Tools\Goal 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. În acest caz formula, se afla în celula D7 si nu face decât sa înmulteasca numarul de obiecte vândute cu pretul lor.

În caseta To value (La valoarea) introdu valoarea tinta.

În caseta By changing cell (Modificând celula) specifica locatia celulei variabilei pe care vrei sa o modifici pentru a ati atinge obiectivul - în acest caz vânzari în valoare de 500.000.000 lei.

Executa click pe OK sau apasa tasta Enter. De îndata ce ai efectuat aceasta operatie Excel începe 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 încât 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 vândute 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, tinând 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 (constrângerile).

Selecteaza celula destinatie G16 si executa secventa Tools\Solver.

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

În continuare, trebuie sa adaugi restrictii la problema. Selecteaza Add din sectiunea Subject to the Constraints pentru a specifica prima restrictie. În 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. În 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 în caseta Solver.

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

Când Solver ajunge la concluzia finala afiseaza o caseta de dialog care indica rezultatul si modifica valorile specificate din foaia de calcul pentru a îndeplini obiectivul. În figura urmatoare se observa celulele modificate atunci când 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) în 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. În 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 când deschizi registrul.

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

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

Poti modifica parametrii Solver înainte de a începe 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 în care poti stabili numarul de iteratii ale problemei ce va fi rulat de Solver în cautarea unui raspuns sau interval de timp pe care îl va petrece cautând înainte de a renunta.

În continuare sunt prezentate caseta si optiunile disponibile: