Tema 4

Utilizarea facilitatii Goal SeekExcel dispune de o serie de faciliti pentru a putea rspunde la ntrebri de genul Ce se ntmpl dac ?. Presupunem c avem o foaie de calcul, cunoatem rspunsul dorit, dar vrem s rezolvm problema i n sens invers, adic s gsim valoarea de intrare care conduce la un anumit rspuns. Pentru a putea rezolva probleme de acest tip se utilizeaz comanda Goal Seek.

Pentru a folosi comanda Goal Seek se formuleaz nti problema, se introduc variabilele i formulele n foaia de calcul. Celula cu rezultate trebuie s conin neaprat o formul iar formula respectiv trebuie s conin referiri la alte celule din foaia de calcul, celule care conin variabile de intrare.

Pentru gsirea valorii de intrare care s conduc la un anumit rspuns se vor parcurge urmtoarele etape:

1. Se selecteaz celula rezultat, care trebuie s conin o formul i n care vrem s obinem 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 conine celula selectat n etapa 1. Dac s-a srit peste etapa 1, se scrie n aceast caset referina celulei rezultat. n caseta To value se introduce soluia la care vrei s ajungei. n caseta By changing Cell se scrie referina 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 nlocuiete valoarea de intrare astfel nct soluia s se apropie ct mai mult de soluia cerut.

Aplicaie Goal Seek

O persoan depune o sum la o banc pe termen de o lun cu o rat a dobnzii de 7%. S se calculeze, pentru un orizont de 12 luni suma din cont la nceputul i sfritul fiecrei luni. S se calculeze valoarea din cont la sfritul perioadei pentru mai multe valori a sumei depuse. S se determine ce sum trebuie s fie depus astfel nct la sfritul perioadei suma din cont s fie de 10.000 lei ?

Se va crea urmtoarea 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 sfritul unei luni este suma de la nceputul lunii la care se adaug dobnda, deci formula din celula C5 va fi =B5+B5*B$2/12.

Suma la nceputul lunii 2 este suma de la sfritul lunii 1, deci n B6 vom introduce formula =C5.

Se copiaz pe coloan formulele din B6 i B5. Suma de la sfritul 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 obine la sfritul perioadei o sum de 32.168 lei.

S rezolvm acum urmtoarea ntrebare: Ce sum trebuie depus astfel nct la sfritul perioadei suma final s fie de 10.000 lei ?.

Rezolvare:

1. Se selecteaz celula C16.

2. Se aplic comanda Tools, Goal Seek3. Caseta Goal Seek se va completa n modul urmtor:

Set CellC16Celula care conine suma pe care vrem s o obinem

To Value10.000Suma pe care vrem s o obinem (suma depus)

By Changing CellB1Celula care variaz ca s obinem rezultatul

4. Se selecteaz butonul OKExcel 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 ntmpl dac?. Pentru a rspunde la astfel de ntrebri se modific valorile din celulele care conin datele iniiale ale problemei. La schimbarea acestor valori se modific i rezultatele. Cu ct exist mai multe scenarii, cu att urmrirea diferenelor dintre rezultatele acestora este mai dificil. Excel ofer o facilitate care permite urmrirea 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 funcie de intrri).

Pentru a crea un scenariu se vor efectua urmtorii pai:

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

Figura 11.5 - caseta de dialog Scenario Manager2. 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 informaii suplimentare. Automat Excel introduce n aceast caset numele utilizatorului i data la care a fost creat scenariul.

Pentru a evita efectuarea de modificri n celulele din foaia de calcul se selecteaz optiunea Prevent Changes din seciunea Protection a casetei de dialog. Pentru a ascunde datele din celule se selecteaz opiunea 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 ValuesDup 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 cmpul Changing Cells vor fi afiate adresele celulelor din scenariu, iar n cmpul Comments comentariile introduse.

5. Pentru a vedea scenariul se selecteaz denumirea lui din list i se execut un clic pe butonul Show. Excel va afia 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 afia n foaia de calcul valorile stabilite n scenariu.

Este bine ca atunci cnd 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 NamePentru a atribui un nume unei celule se efectueaz urmtorii pai:

1. Se selecteaz celula creia 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), asemntoare cu caseta Add Scenario. Se efectueaz toate modificrile 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 ScenarioSintetizarea 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

Pentru a crea un raport de sintetizare se efectueaz urmtorii pai:

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 Summary3. Din zona Report Type se selecteaz opiunea Scenario Summary. n caseta Result Cells se indic domeniul de celule rezultat (care conin formulele bazate pe celule cu datele iniiale).

4. Se aplic un clic pe butonul OK.

Excel va afia o nou foaie de calcul cu un tabel ce conine pentru datele iniiale i rezultatele din fiecare scenariuscenariu.

Crearea unui raport de tip tabel pivot pentru scenarii

Tabelele pivot sunt tabele obinute prin gruparea n diverse moduri a informaiilor din rndurile i coloanele unui tabel. Pentru a crea un tabel pivot plecnd de la scenariile din foaia de calcul se vor efectua urmtorii pai:

1. Se aplic comanda Tools, Scenarios.

2. Se aplic un clic pe butonul Summary.

3. Din caseta de dialog Scenario Summary se selecteaz opiunea Scenario Pivot Table. n caseta text Result Cells se indic domeniul de celule care conine formulele bazate pe celulele cu datele iniiale.

4. Se aplic un clic pe butonul OK.

Excel va afia o nou foaie de calcul cu un tabel pivot ce conine datele de pornire i rezultatele scenariului.

Aplicaie - Elaborarea de alternative de buget

Pentru a echilibra un buget trebuie gsit cea mai bun modalitate de a repartiza departamentelor resursele disponibile. Atunci cnd prevederile iniiale sunt depite trebuie comparate strategiile de redistribuire. Cu facilitile oferite de managerul de scenarii se pot modela diferite strategii pentru a analiza avantajele i dezavantajele diferitelor moduri de abordare.

Se va crea urmtoarea foaie de calcul

Figura 11.11

n coloana Buget proiectat sunt introduse prevederile iniiale ale bugetului.

n coloana Buget repartizat sunt introduse bugetele repartizate fiecrui departament.

n coloana Diferen se va calcula diferena 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 depete 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, diferena total i procentul de depire total. Celulele din aceast linie vor conine urmtoarele formule:

B6:Sum(B2:B5)

C6:Sum(C2:C5)

D6:B6-C6

E6:D6/C6

Folosind facilitatea Goal Seek s-ar putea rspunde la ntrebri de tipul: Ct de mult ar putea s scad bugetul repartizat pentru departamentul Desfacere astfel nct s se reduc depirea bugetului total?.

Depirea bugetului total se gsete n celula E6. Aici ar trebui s obinem valoarea 0. Bugetul pentru departamentul Desfacere se gsete n celula B5.

Pentru rezolvarea problemei:

1. Se aplic comanda Tools, Goal Seek.2. Caseta de dialog Goal Seek se completeaz n modul urmtor: Set Cell - E6, To Value - 0, By Changing Cell - B5.

3. Se selecteaz butonul OK.

Dup aplicarea comenzii celula B5 va conine bugetul care trebuie repartizat departamentului Desfacere astfel nct bugetul total s nu fie depit.

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 OKFolosind aceeai metod se vor denumi i celulele B3:B5, B6:E6 n modul urmtor:

B3 - Buget Aprovizionare,

B4 - Buget Resurse Umane,

B5 - Buget Desfacere.

B6 Total buget repartizat

C6 Total buget proiectat

D6 Diferena total

E6 Procent de depire total

Se vor crea mai multe scenarii care vor conine 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: Estimri iniiale. 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 paii 2-4 se vor mai crea urmtoarele scenarii:

Valori specifice scenariilor

B2B3B4B5

Scenariu1956750244,120370,0001,252,130

Scenariu295675023,970320,0001,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 opiunea Scenario Summary, iar n Result Cells se introduce domeniul B6:E6 (domeniul care conine rezultatele).

3. Se aplic un clic pe butonul OK.

Excel creeaz o nou foaie de calcul cu un tabel n care sunt afiate pentru fiecare scenariu valorile de intrare i valorile rezultat.