COMPARAREA ALTERNATIVELOR IN VEDEREA LUARII DECIZIEI OPTIME

Utilizarea facilitatii Goal Seek

Excel dispune de o serie de facilitati pentru a putea raspunde la intrebari de genul "Ce se intampla daca ?". Presupunem ca avem o foaie de calcul, cunoastem raspunsul dorit, dar vrem sa rezolvam problema si in sens invers, adica sa gasim valoarea de intrare care conduce la un anumit raspuns. Pentru a putea rezolva probleme de acest tip se utilizeaza comanda Goal Seek

Pentru a folosi comanda Goal Seek se formuleaza intai problema, se introduc variabilele si formulele in foaia de calcul. Celula cu rezultate trebuie sa contina neaparat o formula iar formula respectiva trebuie sa contina referiri la alte celule din foaia de calcul, celule care contin variabile de intrare.

Pentru gasirea valorii de intrare care sa conduca la un anumit raspuns se vor parcurge urmatoarele etape:

1.   Se selecteaza celula rezultat, care trebuie sa contina o formula si in care vrem sa obtinem o anumita valoare.

2.   Se aplica comanda Tools, Goal Seek. Pe ecran apare caseta de dialog Goal Seek (figura 1).

Figura 1 - caseta de dialog Goal Seek

3.   Caseta Set Cell contine celula selectata in etapa 1. Daca s-a sarit peste etapa 1, se scrie in aceasta caseta referinta celulei rezultat. In caseta To value se introduce solutia la care vreti sa ajungeti. In caseta By changing Cell se scrie referinta celulei de intrare. Aceasta celula trebuie sa contribuie la valoarea formulei din celula rezultat, specificata in Set Cell

4.   Se selecteaza butonul OK

Goal Seek inlocuieste valoarea de intrare astfel incat solutia sa se apropie cat mai mult de solutia ceruta.

Aplicatie - Goal Seek

O persoana depune o suma la o banca pe termen de o luna cu o rata a dobanzii de 7%. Sa se calculeze, pentru un orizont de 12 luni suma din cont la inceputul si sfarsitul fiecarei luni. Sa se calculeze valoarea din cont la sfarsitul perioadei pentru mai multe valori a sumei depuse. Sa se determine ce suma trebuie sa fie depusa astfel incat la sfarsitul perioadei suma din cont sa fie de 10.000 lei ?

Se va crea urmatoarea foaie de calcul (figura 2):

Figura.2

Suma la inceputul lunii 1 este chiar suma depusa deci in B5 vom introduce formula =B1.

Suma la sfarsitul unei luni este suma de la inceputul lunii la care se adauga dobanda, deci formula din celula C5 va fi =B5+B5*B$2/12.

Suma la inceputul lunii 2 este suma de la sfarsitul lunii 1, deci in B6 vom introduce formula =C5.

Se copiaza pe coloana formulele din B6 si B5. Suma de la sfarsitul perioadei este in celula C16. Valoarea din aceasta celula depinde in mod indirect de suma depusa din B1.

Daca se modifica suma depusa automat se modifica si valoarea din C16. De exemplu, pentru o suma depusa de 30.000 se va obtine la sfarsitul perioadei o suma de 32.168 lei.

Sa rezolvam acum urmatoarea intrebare: Ce suma trebuie depusa astfel incat la sfarsitul perioadei suma finala sa fie de 10.000 lei ?.

Rezolvare:

1.   Se selecteaza celula C16.

2.   Se aplica comanda Tools, Goal Seek

3.   Caseta Goal Seek se va completa in modul urmator:

4.   Se selecteaza butonul OK

Excel rezolva problema in mod invers, suma care trebuie depusa fiind de 9.325 lei.

Efectuarea de analize "What if" cu scenarii

Multe din analizele economice implica efectuarea de analize de tipul "Ce se intampla daca?". Pentru a raspunde la astfel de intrebari  se modifica valorile din celulele care contin datele initiale ale problemei. La schimbarea acestor valori se modifica si rezultatele. Cu cat exista mai multe scenarii, cu atat urmarirea diferentelor dintre rezultatele acestora este mai dificila. Excel ofera o facilitate care permite urmarirea acestor scenarii: "Scenario Manager" (managerul de scenarii).

Crearea unui scenariu

Un model cu scenarii trebuie sa aiba un set de valori de intrare si un set  de valori rezultat (care se schimba in functie de intrari).

Lucrare: Viziunea moderna asupra calitatii educatiei Lucrare: Eficiența in comunicarea didactica

Pentru a crea un scenariu se vor efectua urmatorii pasi:

1.     Se aplica comanda Tools, Scenarios. Pe ecran apare caseta de dialog Scenario Manager (figura 11.5).

Figura 11.5 - caseta de dialog Scenario  Manager

2.     Din caseta Scenario Manager se selecteaza butonul Add. Pe ecran apare caseta de dialog Add Scenario (figura 11.6).

Figura 11.6 - caseta de dialog Add Scenario

3.     In caseta Scenario Name se specifica numele scenariului.

In caseta Changing Cells se indica celulele sau domeniul de celule care vor fi modificate pentru fiecare scenariu.

In caseta Comment se pot scrie informatii suplimentare. Automat Excel introduce in aceasta caseta numele utilizatorului si data la care a fost creat scenariul.

Pentru a evita efectuarea de modificari in celulele din foaia de calcul se selecteaza optiunea Prevent Changes din sectiunea Protection a casetei de dialog. Pentru a ascunde datele din celule se selecteaza optiunea Hide

4.     Se aplica un clic pe butonul OK.

Pe ecran apare caseta de dialog Scenario Values (figura 11.7), in care se introduc datele pentru fiecare celula din scenariu.

Figura 11. 7 - caseta de dialog Scenario Values

Dupa introducerea datelor se selecteaza butonul OK. Pe ecran apare caseta de dialog Scenario Manager. Denumirea noului scenariu creat apare in lista Scenarios. La selectarea unui scenariu din lista in campul Changing Cells vor fi afisate adresele celulelor din scenariu, iar in campul Comments comentariile introduse.

5.     Pentru a vedea scenariul se selecteaza denumirea lui din lista si se executa un clic pe butonul Show. Excel va afisa valorile din toate celulele din foaia de calcul. In cazul in care caseta de dialog acopera o parte din date, se trage cu mouse-ul bara de titlu a casetei de dialog spre marginea ecranului.

6.     Pentru a reveni in foaia de calcul se executa un clic pe butonul Close. Excel va afisa in foaia de calcul valorile stabilite in scenariu.

Este bine ca atunci cand se lucreaza cu scenarii fiecare celula din scenariu sa aiba un nume. Excel va folosi aceste nume in caseta de dialog Scenario Values si in rapoartele pentru scenarii.

Figura 11. 8 - caseta de dialog Define Name

Pentru a atribui un nume unei celule se efectueaza urmatorii pasi:

1.     Se selecteaza celula careia trebuie sa i se atribuie un nume.

2.     Se aplica comanda Insert, Name, Define. Pe ecran apare caseta de dialog Define Name (figura 11.8).

3.     Se scrie numele celulei in caseta Names.

4.     Se aplica un clic pe butonul OK

Editarea si stergerea scenariilor

Un scenariu existent poate fi modificat sau poate fi sters.

Pentru a sterge un scenariu se selecteaza numele acestuia din caseta de dialog Scenario Manager si se aplica un clic pe butonul Delete. Excel va elimina scenariul din lista cu scenarii.

Pentru a modifica un scenariu se selecteaza numele scenariului din caseta de dialog Scenario Manager si se aplica un clic pe butonul Edit Scenario. Pe ecran apare caseta de dialog Edit Scenario (figura 11.9), asemanatoare cu caseta Add Scenario. Se efectueaza toate modificarile necesare si se aplica un clic pe butonul OK. Pe ecran apare caseta de dialog Scenario Values in care se introduc noile valori.

Figura 11. 9 - caseta de dialog Edit Scenario

Sintetizarea scenariilor prin rapoarte

Pentru compararea rezultatelor din mai multe scenarii, Excel ofera doua metode. La prima metoda se creeaza un raport simplu sub forma de tabel, in care sunt prezentate datele din celulele de intrare si efectul lor asupra rezultatelor.  La a doua metoda se genereaza un tabel pivot.

Crearea unui raport de sintetizare

Pentru a crea un raport de sintetizare se efectueaza urmatorii pasi:

1.     Se aplica comanda Tools, Scenarios.

2.     Se aplica un clic pe butonul Summary. Pe ecran apare caseta de dialog Scenario Summary (figura 11.10)

Figura 11. 10 - caseta de dialog Scenario Summary

3.     Din zona Report Type se selecteaza optiunea Scenario Summary.
In caseta  Result Cells se indica domeniul de celule rezultat (care contin formulele bazate pe celule cu datele initiale).

4.     Se aplica un clic pe butonul OK

Excel va afisa o noua foaie de calcul cu un tabel ce contine pentru datele initiale si rezultatele din fiecare scenariuscenariu.

Crearea unui raport de tip tabel pivot pentru scenarii

Tabelele pivot sunt tabele obtinute prin gruparea in diverse moduri a informatiilor din randurile si coloanele unui tabel. Pentru a crea un tabel pivot plecand de la scenariile din foaia de calcul se vor efectua urmatorii pasi:

1.     Se aplica comanda Tools, Scenarios

2.     Se aplica un clic pe butonul Summary.

3.     Din caseta de dialog Scenario Summary se selecteaza optiunea Scenario Pivot Table. In caseta text Result Cells se indica domeniul de celule care contine formulele bazate pe celulele cu datele initiale.

4.     Se aplica un clic pe butonul OK

Excel va afisa o noua foaie de calcul cu un tabel pivot ce contine datele de pornire si rezultatele scenariului.

Aplicatie - Elaborarea de alternative de buget

Pentru a echilibra un buget trebuie gasita cea mai buna modalitate de a repartiza departamentelor resursele disponibile. Atunci cand prevederile initiale sunt depasite trebuie comparate strategiile de redistribuire. Cu facilitatile oferite de managerul de scenarii se pot modela diferite strategii pentru a analiza avantajele si dezavantajele diferitelor moduri de abordare.

Se va crea urmatoarea foaie de calcul

Figura 11.11

In coloana Buget proiectat sunt introduse prevederile initiale ale bugetului.

In coloana Buget repartizat sunt introduse bugetele repartizate fiecarui departament.

In coloana Diferenta se va calcula diferenta dintre bugetul repartizat si bugetul proiectat. In celula D2 se va introduce formula =B2-C2, care se va copia in domeniul D3:D5.

In coloana Procent se va calcula procentul cu care se depaseste bugetul proiectat. In celula E2 se va introduce formula =D2/C2. Aceasta formula se va copia in domeniul E3:E5.

In ultima linie din tabel se vor calcula totalurile: bugetul total repartizat, bugetul total proiectat, diferenta totala si procentul de depasire total. Celulele din aceasta linie vor contine urmatoarele formule:

Folosind facilitatea Goal Seek s-ar putea raspunde la intrebari de tipul: "Cat de mult ar putea sa scada bugetul repartizat pentru departamentul Desfacere astfel incat sa se reduca depasirea bugetului total?".

Depasirea bugetului total se gaseste in celula E6. Aici ar trebui sa obtinem valoarea 0. Bugetul pentru departamentul Desfacere se gaseste in celula B5.

Pentru rezolvarea problemei:

1.     Se aplica comanda Tools, Goal Seek.

2.     Caseta de dialog Goal Seek se completeaza in modul urmator: Set Cell - E6, To Value - 0, By Changing Cell - B5.

3.     Se selecteaza butonul OK

Dupa aplicarea comenzii celula B5 va contine bugetul care trebuie repartizat departamentului Desfacere astfel incat bugetul total sa nu fie depasit.

Pentru a testa mai multe strategii de repartizare a bugetului se poate folosi managerul de Scenarii.

Rezolvare:

Se denumesc celulele B2:B5:

1.     Se selecteaza celula B2.

2.     Se aplica comanda Insert, Name, Define.

3.     In caseta Name din caseta de dialog Define Name se introduce Buget Marketing.

4.     Se aplica un clic pe butonul OK

Folosind aceeasi metoda se vor denumi si celulele B3:B5, B6:E6 in modul urmator:

B3 - Buget Aprovizionare,

B4 - Buget Resurse Umane,

B5 - Buget Desfacere.

B6 - Total buget repartizat

C6 - Total buget proiectat

D6 - Diferenta totala

E6 - Procent de depasire total

Se vor crea mai multe scenarii care vor contine diferite valori pentru bugetele repartizate pentru cele patru departamente:

1.     Se aplica comanda Tools, Scenarios.

2.     Din caseta de dialog Scenario Manager se selecateaza butonul Add.

3.     In caseta Scenario Name se introduce numele scenariului: Estimari initiale. In caseta Changing Cells se indica celulele care vor fi modificate: B2:B5. Se aplica un clic pe butonul OK

4.     In caseta de dialog Scenario Values se vor introduce datele specifice pentru fiecare scenariu.

5.     Se selecteaza butonul OK

Folosind pasii 2-4 se vor mai crea urmatoarele scenarii:

Dupa crearea scenariilor, pentru compararea acestora se poate crea un raport.

1.     In caseta de dialog Scenario Manager se aplica un clic pe butonul Summary

2.     In caseta de dialog Scenario Summary, se selecteaza optiunea Scenario Summary, iar in Result Cells se introduce domeniul B6:E6 (domeniul care contine rezultatele).

3.     Se aplica un clic pe butonul OK.

Excel creeaza o noua foaie de calcul cu un tabel in care sunt afisate pentru fiecare scenariu valorile de intrare si valorile rezultat.