1

COMPARAREA  ALTERNATIVELOR  ÎN  VEDEREA LUĂRII  DECIZIEI  OPTIME

    Instrumentul Goal Seek
Utilizarea facilităţii Goal Seek

Excel dispune de o serie de facilităţi pentru a putea răspunde la întrebări de genul “Ce se întâmplă dacă ?“. Presupunem că avem o foaie de calcul, cunoaştem răspunsul dorit, dar vrem să rezolvăm problema şi în sens invers, adică să găsim valoarea de intrare care conduce la un anumit răspuns. Pentru a putea rezolva probleme de acest tip se utilizează comanda Goal Seek.
Pentru a folosi comanda Goal Seek se formulează întâi problema, se introduc variabilele şi formulele în foaia de calcul. Celula cu rezultate trebuie să conţină neapărat o formulă iar formula respectivă trebuie să conţină referiri la alte celule din foaia de calcul, celule care conţin variabile de intrare.
Pentru găsirea valorii de intrare care să conducă la un anumit răspuns se vor parcurge următoarele etape:
1.    Se selectează celula rezultat, care trebuie să conţină o formulă şi în care vrem să obţinem o anumită valoare.
2.    Se aplică 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 conţine celula selectată în etapa 1. Dacă s-a sărit peste etapa 1, se scrie în această casetă referinţa celulei rezultat. În caseta To value se introduce soluţia la care vreţi să ajungeţi. În caseta By changing Cell se scrie referinţa celulei de intrare. Această celulă trebuie să contribuie la valoarea formulei din celula rezultat, specificată în Set Cell.
4.    Se selectează butonul OK.

Goal Seek înlocuieşte valoarea de intrare astfel încât soluţia să se apropie cât mai mult de soluţia cerută.

Aplicaţie – Goal Seek

O persoană depune o sumă la o bancă pe termen de o lună cu o rată a dobânzii de 7%. Să se calculeze, pentru un orizont de 12 luni suma din cont la începutul şi sfârşitul fiecărei luni. Să se calculeze valoarea din cont la sfârşitul perioadei pentru mai multe valori a sumei depuse. Să se determine ce sumă trebuie să fie depusă astfel încât la sfârşitul perioadei suma din cont să fie de 10.000 lei ?

Se va crea următoarea foaie de calcul (figura 2):
 
 

Figura.2

Suma la începutul lunii 1 este chiar suma depusă deci în B5 vom introduce formula =B1.
Suma la sfârşitul unei luni este suma de la începutul lunii la care se adaugă dobânda, deci formula din celula C5 va fi =B5+B5*B$2/12.
Suma la începutul lunii 2 este suma de la sfârşitul lunii 1, deci în B6 vom introduce formula =C5.
Se copiază pe coloană formulele din B6 şi B5. Suma de la sfârşitul perioadei este în celula C16. Valoarea din această celulă depinde în mod indirect de suma depusă din B1.
Dacă se modifică suma depusă automat se modifică şi valoarea din C16. De exemplu, pentru o sumă depusă de 30.000 se va obţine la sfârşitul perioadei o sumă de            32.168 lei.
Să rezolvăm acum următoarea întrebare: Ce sumă trebuie depusă astfel încât la sfârşitul perioadei suma finală să fie de 10.000 lei ?.
Rezolvare:
1.    Se selectează celula C16.
2.    Se aplică comanda Tools, Goal Seek
3.    Caseta Goal Seek se va completa în modul următor:
Set Cell    C16    Celula care conţine suma pe care vrem sã o obţinem
To Value    10.000    Suma pe care vrem sã o obţinem (suma depusã)
By Changing Cell    B1    Celula care variazã ca sã obţinem rezultatul

4.    Se selectează butonul OK

Excel rezolvă problema în mod invers, suma care trebuie depusă fiind de 9.325 lei.

Efectuarea de analize “What if” cu scenarii

Multe din analizele economice implică efectuarea de analize de tipul “Ce se întâmplă dacă?”. Pentru a răspunde la astfel de întrebări  se modifică valorile din celulele care conţin datele iniţiale ale problemei. La schimbarea acestor valori se modifică şi rezultatele. Cu cât există mai multe scenarii, cu atât urmărirea diferenţelor dintre rezultatele acestora este mai dificilă. Excel oferă o facilitate care permite urmărirea acestor scenarii: “Scenario Manager” (managerul de scenarii).

Crearea unui scenariu

Un model cu scenarii trebuie să aibă un set de valori de intrare şi un set  de valori rezultat (care se schimbă în funcţie de intrări).
Pentru a crea un scenariu se vor efectua următorii paşi:
1.    Se aplică 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 selectează butonul Add. Pe ecran apare caseta de dialog Add Scenario (figura 11.6).

 
Figura 11.6 – caseta de dialog Add Scenario
3.    În caseta Scenario Name se specifică  numele scenariului.
În caseta Changing Cells se indică celulele sau domeniul de celule care vor fi modificate pentru fiecare scenariu.
În caseta Comment se pot scrie informaţii suplimentare. Automat Excel introduce în această casetă numele utilizatorului şi data la care a fost creat  scenariul.
Pentru a evita efectuarea de modificări în celulele din foaia de calcul se selectează optiunea Prevent Changes din secţiunea Protection a casetei de dialog. Pentru a ascunde datele din celule se selectează opţiunea Hide.
4.    Se aplică un clic pe butonul OK.
Pe ecran apare caseta de dialog Scenario Values (figura 11.7), în care se  introduc datele pentru fiecare celulă din scenariu.

 
Figura 11. 7 – caseta de dialog Scenario Values
După introducerea datelor se selectează butonul OK. Pe ecran apare caseta de dialog Scenario Manager. Denumirea noului scenariu creat apare în lista Scenarios. La selectarea unui scenariu din listă în câmpul Changing Cells vor fi afişate adresele celulelor din scenariu, iar în câmpul Comments comentariile introduse.
5.    Pentru a vedea scenariul se selectează denumirea lui din listă şi se execută un clic pe butonul Show. Excel va afişa valorile din toate celulele din foaia de calcul. În cazul în care caseta de dialog  acoperă o parte din date, se trage cu mouse-ul bara de titlu a casetei de dialog spre marginea ecranului.
6.    Pentru a reveni în foaia de calcul se execută un clic pe butonul Close. Excel va afişa în foaia de calcul valorile stabilite în scenariu.

Este bine ca atunci când se lucrează cu scenarii fiecare celulă din scenariu să aibă un nume. Excel va folosi aceste nume în caseta de dialog Scenario Values şi în rapoartele pentru scenarii.

 
Figura 11. 8 – caseta de dialog Define Name
Pentru a atribui un nume unei celule se efectuează următorii paşi:
1.    Se selectează celula căreia trebuie să i se atribuie un nume.
2.    Se aplică comanda Insert, Name, Define. Pe ecran apare caseta de dialog Define Name (figura 11.8).
3.    Se scrie numele celulei în caseta Names.
4.    Se aplică un clic pe butonul OK.

Editarea şi ştergerea scenariilor

Un scenariu existent poate fi modificat sau poate fi şters.
Pentru a şterge un scenariu se selectează numele acestuia din caseta de dialog Scenario Manager şi se aplică un clic pe butonul Delete. Excel va elimina scenariul din lista cu scenarii.
Pentru a modifica un scenariu se selectează numele scenariului din caseta de dialog Scenario Manager şi se aplică un clic pe butonul Edit Scenario. Pe ecran apare caseta de dialog Edit Scenario (figura 11.9), asemănătoare cu caseta Add Scenario. Se efectuează toate modificările necesare şi se aplică un clic pe butonul OK. Pe ecran apare caseta de dialog Scenario Values în 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 oferă două metode. La prima metodă se creează un raport simplu sub formă de tabel, în care sunt prezentate datele din celulele de intrare şi efectul lor asupra rezultatelor.  La a doua metodă se generează un tabel pivot.

Crearea unui raport de sintetizare


1 Pentru a crea un raport de sintetizare se efectuează următorii paşi:
1.    Se aplică comanda Tools, Scenarios.
2.    Se aplică 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 selectează opţiunea Scenario Summary.
În caseta  Result Cells se indică domeniul de celule rezultat (care conţin formulele bazate pe celule cu datele iniţiale).
4.    Se aplică un clic pe butonul OK.

Excel va afişa o nouă foaie de calcul cu un tabel ce conţine pentru datele iniţiale şi rezultatele din fiecare scenariuscenariu.

Crearea unui raport de tip tabel pivot pentru scenarii

    Tabelele pivot sunt tabele obţinute prin gruparea în diverse moduri a informaţiilor din  rândurile şi coloanele unui tabel. Pentru a crea un tabel pivot plecând de la scenariile din foaia de calcul se vor efectua următorii paşi:
1.    Se aplică comanda Tools, Scenarios.
2.    Se aplică un clic pe butonul Summary.
3.    Din caseta de dialog Scenario Summary se selectează opţiunea Scenario Pivot Table. În caseta text Result Cells se indică domeniul de celule care conţine formulele bazate pe celulele cu datele iniţiale.
4.    Se aplică un clic pe butonul OK.
Excel va afişa o nouă foaie de calcul cu un tabel pivot ce conţine datele de pornire şi rezultatele scenariului.

Aplicaţie  -  Elaborarea de alternative de buget

Pentru a echilibra un buget trebuie găsită cea mai bună modalitate de a repartiza departamentelor resursele disponibile. Atunci cănd prevederile iniţiale sunt depăşite trebuie comparate strategiile de redistribuire. Cu facilităţile oferite de managerul de scenarii se pot modela diferite strategii pentru a analiza avantajele şi dezavantajele diferitelor moduri de abordare.
Se va crea următoarea foaie de calcul

 
Figura 11.11
În coloana Buget proiectat sunt introduse prevederile iniţiale ale bugetului.
În coloana Buget repartizat sunt introduse bugetele repartizate fiecărui departament.
În coloana Diferenţă se va calcula diferenţa dintre bugetul repartizat şi bugetul proiectat. În celula D2 se va introduce formula =B2-C2, care se va copia în domeniul D3:D5.
În coloana Procent se va calcula procentul cu care se depăşeşte bugetul proiectat. În celula E2 se va introduce formula =D2/C2. Această formulă se va copia în domeniul E3:E5.
În ultima linie din tabel se vor calcula totalurile: bugetul total repartizat, bugetul total proiectat, diferenţa totală şi procentul de depăşire total. Celulele din această linie vor conţine următoarele formule:
B6:    Sum(B2:B5)
C6:    Sum(C2:C5)
D6:    B6-C6
E6:    D6/C6

Folosind facilitatea Goal Seek s-ar putea răspunde la întrebări de tipul: “Cât de mult ar putea să scadă bugetul repartizat pentru departamentul Desfacere astfel încât să se reducă depăşirea bugetului total?”.
Depăşirea bugetului total se găseşte în celula E6. Aici ar trebui să obţinem valoarea 0. Bugetul pentru departamentul Desfacere se găseşte în celula B5.
Pentru rezolvarea problemei:
1.    Se aplică comanda Tools, Goal Seek.
2.    Caseta de dialog Goal Seek se completează în modul următor: Set Cell - E6, To Value - 0, By Changing Cell - B5.
3.    Se selectează butonul OK.

După aplicarea comenzii celula B5 va conţine bugetul care trebuie repartizat departamentului Desfacere astfel încât bugetul total să nu fie depăşit.

Pentru a testa mai multe strategii de repartizare a bugetului se poate folosi managerul de Scenarii.
Rezolvare:
Se denumesc celulele B2:B5:
1.    Se selectează celula B2.
2.    Se aplică comanda Insert, Name, Define.
3.    În caseta Name din caseta de dialog Define Name se introduce Buget Marketing.
4.    Se aplică un clic pe butonul OK
Folosind aceeaşi metodă se vor denumi şi celulele B3:B5, B6:E6 în modul următor:
B3 - Buget Aprovizionare,
B4 - Buget Resurse Umane,
B5 - Buget Desfacere.
B6 – Total buget repartizat
C6 – Total buget proiectat
D6 – Diferenţa totală
E6 – Procent de depăşire total

Se vor crea mai multe scenarii care vor conţine diferite valori pentru bugetele repartizate pentru cele patru departamente:
1.    Se aplică comanda Tools, Scenarios.
2.    Din caseta de dialog Scenario Manager se selecatează butonul Add.
3.    În caseta  Scenario Name se introduce numele scenariului: Estimări iniţiale. În caseta Changing Cells se indică celulele care vor fi modificate: B2:B5. Se aplică un clic pe butonul OK.
4.    În caseta de dialog Scenario Values se vor introduce datele specifice pentru fiecare scenariu.
B2:    956,750
B3:    244,120
B4:    370,000
B5:    145,188

5.    Se selectează butonul OK.

Folosind paşii 2-4 se vor mai crea următoarele scenarii:
    Valori specifice scenariilor
    B2    B3    B4    B5
Scenariu1    956750    244,120    370,000    1,252,130
Scenariu2    956750    23,970    320,000    1,000,000

După crearea scenariilor, pentru compararea acestora se poate crea un raport.
1.    În caseta de dialog Scenario Manager se aplică un clic pe butonul Summary.
2.    În caseta de dialog Scenario Summary, se selectează opţiunea Scenario Summary, iar în Result Cells se introduce domeniul B6:E6 (domeniul care conţine rezultatele).
3.    Se aplică un clic pe butonul OK.

Excel creează o nouă foaie de calcul cu un tabel în care sunt afişate pentru fiecare scenariu valorile de intrare şi valorile rezultat.