COMPARAREA ALTERNATIVELOR IN VEDEREA LUARII DECIZIEI OPTIME IN EXCEL

Cuprins

Utilizarea facilitatii Goal Seek

Aplicatie - Goal Seek

Calcularea tabelelor de raspunsuri

Efectuarea de analize "What if" cu scenarii

Crearea unui scenariu

Editarea si stergerea scenariilor

Sintetizarea scenariilor prin rapoarte

Crearea unui raport de sintetizare

Crearea unui raport de tip tabel pivot pentru scenarii

Aplicatie  - Elaborarea de alternative de buget

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 11.1).

Figura 11.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 50%. 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 10000000 lei ?

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

Figura 11.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 3000000 se va obtine la sfarsitul perioadei o suma de4896282lei.

Sa rezolvam acum urmatoarea intrebare: Ce suma trebuie depusa astfel incat la sfarsitul perioadei suma finala sa fie de 10000000 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 6127097 lei.

Calcularea tabelelor de raspunsuri

Facilitatile "What if" - "Ce se intampla daca" oferite de Excel sunt foarte folositoare in afaceri. Foile de calcul pot da raspuns imediat la intrebari cum ar fi: "Ce s-ar intampla daca s-ar reduce costurile cu 0,5% ?", "Ce s-ar intampla daca s-ar vinde mai mult cu 10%?", "Ce s-ar intampla daca nu am primi imprumutul?".

Atunci cand se testeaza cat de mult afecteaza rezultatele o schimbare cat de mica a marimilor de intrare, se realizeaza o analiza de senzitivitate. Pentru a realiza o analiza de senzitivitate pentru un domeniu mare de intrari se poate folosi comanda Table din meniul Data. Comanda poate fi utilizata in doua moduri:

1.   Modificarea unei date de intrare pentru a vedea efectul produs asupra uneia sau mai multor formule.

2.   Modificarea a doua date de intrare pentru a vedea efectul produs asupra unei formule.

Pentru a intelege mai bine ce face aceasta comanda sa consideram urmatorul exemplu:

Sa se calculeze ratele lunare care trebuie platite pentru a returna un credit, cu o anumita dobanda.

Rezolvare:

1.   Primul pas este crearea foii de calcul (figura 11.3).

Figura 11.3

In celula B6 se introduce formula =‑PMT(B2/12,B3*12,B1), formula cu care se calculeaza ratele lunare ce trebuie platite pentru a returna creditul care se gaseste in B1, pe durata specificata in B3 (B3*12 reprezinta numarul de luni), cu dobanda din celula B2 (B2/12 reprezinta dobanda lunara).

2.     Sa facem o analiza de senzitivitate in care sa analizam cum sunt influentate ratele lunare de dobanzi.

Se construieste un tabel in care prima coloana sau prima linie contine valorile care trebuie testate. Pentru exemplul nostru in domeniul A9:A13 contine ratele dobanzii care vor fi utilizate ca intrari in analiza de senzitivitate.

3.   In urmatoarele coloane (sau linii) din tabel, in celulele din capul de tabel se introduc adresele formulelor care contin raspunsul. In cazul nostru in celula B8 se introduce formula =B6 (B6 contine formula pentru calculul ratelor lunare).

4.   Se selecteaza celulele care contin tabelul. Se aplica comanda Data, Table. Pe ecran apare caseta de dialog Table (figura 11.3).

Figura 11.3 - caseta de dialog Table

5.   Se introduce adresa celulei care contine variabila de intrare in Row Input Cell (daca valorile care sunt testate sunt desfasurate pe linie ) sau Column Input Cell (daca valorile care sunt testate sunt desfasurate pe coloana). In cazul nostru, valorile testate sunt desfasurate pe coloana (A9/A13), deci vom introduce in Column Input Cell B2 (adresa ratei dobanzii).

6.   Se selecteaza butonul OK.

Rezultatul obtinut este un tabel care contine ratele lunare corespunzatoare fiecarei dobanzi.

In continuare vom vedea cum poate fi folosita comanda Table pentru a vedea efectul produs asupra unei formule prin modificarea a doua date de intrare.

Reluam exemplul anterior numai ca acum vom modifica doua date de intrare: dobanda si creditul. In foaia da calcul se va calcula rezultatul pentru mai multe combinatii ale acestor valori.

Rezolvare:

1.     Ca si in cazul precedent se creeaza foaia de calcul (figura 11.4).

2.     Se construieste un tabel in care prima coloana si prima linie contin valorile celor doua variabile. Coltul din stanga sus trebuie sa contina o formula sau o referinta la o formula

In exemplul nostru domeniul A9:A13 va contine valorile dobanzilor, domeniul B8:F8 valorile creditului, iar celula A8 va contine formula =B6 (o referinta la o celula care contine formula de calcul).

3.     Se selecteaza tabelul si se aplica comanda Table. In Row Input Cell se introduce adresa celulei care reprezinta variabila care are valorile desfasurate pe linie, iar in Column Input Cell se introduce adresa celulei care reprezinta variabila care are desfasurate valorile pe coloana. In cazul nostru: in Row Input Cell se introduce B1; in Column Input Cell se introduce B2.

4.     Se selecteaza butonul OK.

Rezultatul este prezentat in tabelul din figura 11.4.

Figura 11.4

Valorile din tabel reprezinta ratele lunare care trebuie platite pentru diferite valori ale creditului si diferite valori ale dobanzii.

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).

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.

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.

Figura 11. 7 - caseta de dialog Scenario Values

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.

Figura 11. 8 - caseta de dialog Define Name

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.

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.

Figura 11. 9 - caseta de dialog Edit Scenario

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.

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 foaia de calcul din 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.

Figura 11.11

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.