Calcularea tabelelor de raspunsuri

Calcularea tabelelor de raspunsuri

Foile de calcul furnizeaza reactia imediata la o schimbare a marimilor de intrare. Atunci cand testam modul de reactie la o schimbare, realizam de fapt o analiza senzitiva. Pentru aceasta, se folosesc Data, Table., puse la dispozitie de Excel.

Excel poate crea un tabel care afiseaza intrarile pe care dorim sa le testam, precum si rezultatele, astfel incat nu este nevoie sa se introduca toate intrarile posibile de la tastatura.

Se poate utiliza comanda Table din meniul Data in urmatoarele moduri:

a)       Se schimba o intrare pentru a vedea efectul pe care-l produce asupra uneia sau mai multor formule.

b)       Se schimba doua intrari pentru a vedea efectul pe care-l produce asupra unei singure formule.

Schimbarea unei singure variabile si mai multe formule

Printre cele mai bune si mai frecvent folosite exemple de analiza senzitiva, se afla un tabel de date care calculeaza platile unui imprumut pentru rate diferite ale dobanzii. Tabelul de date cu o singura intrare descris mai jos creeaza o diagrama a platilor lunare pentru o serie de rate ale dobanzii imprumutului.

Inainte de a crea tabelul de date, trebuie construita foaia de calcul care rezolva problema pe care o supunem testarii. Foaia de calcul de mai jos calculeaza platile lunare pentru ipoteca asupra unei case. Prsupunem ca achizitionam o casa cu o ipoteca de 12400 $, cu plata in 4 ani, cu o dobanda anuala de 11%. Formula care urmeaza, aflata in celula D10, rezolva aceasta sarcina:

=PMT(D5/12,D6*12,D4)

Sintaxa functiei PMT este:

PMT(dobanda, nper,vp,vv,tip), unde:

dobanda = dobanda investitiei

nper = termenul   

vv = viitoare valori

vp = valoarea prezenta

tip = modul de plata al banilor

daca tip=0, banii sunt platiti la sfarsitul perioadei

daca tip=1, banii se platesc la inceputul perioadei

Daca nu se introduc valori pentru vv si tip se considera ca ele sunt nule.

Pentru a construi un tabel de date, se parcurg urmatoarele etape:

Se construieste foaia de calcul.

Se introduc diferitele valori pe care dorim sa le testam, in orice secventa. Celulele C11:C17 arata ratele dobanzii care vor fi utilizate ca intrari in analiza senzitiva.

In partea de sus al tabelului, randul 10, deasupra caruia apar rezultatele, se introduce formula pentru care se doreste raspunsul, prin urmare rezultatele pentru formula de plata din D10 sunt calculate pentru fiecare rata a dobanzii din tabel.

Se selecteaza celulele care contin tabelul (C10:D17). Se includ valorile de intrare in coloana din stanga si in randul de formule din partea de sus. Rezultatele vor umple celulele goale din D11:D17.    In meniul Data se executa clic pe Table pentru a afisa caseta de dialog Table.

Se introduce o valoare pentru Row Input Cell (Celula de intrare rand) sau Column Input Cell (Celula de intrare coloana). Se executa clic sau se indica celula in care dorim sa tastam numerele de variabila listate in tabel. In acest exemplu, Column Input Cell este $D$5 (o celula care nu apartine domeniului). Ar trebui introdus $D$5 in caseta de text Column Input Cell. Column Input Cell se foloseste mai degraba decat Row Input Cell, deoarece in acest tabel, valorile care sunt testate sunt ratele dobanzii care sunt desfasurate in jos, in coloana cea mai din stanga. Daca am fi calculat manual cantitatile de plata, ar fi trebuit sa tastam manual acele rate ale dobanzii in celula D5. Prin introducerea valorii $D$5 se comanda programului Excel sa testeze fiecare rata a dobanzii in coloana din stanga a tabelului, prin introducerea acelei rate in celula D5. Plata calculata pentru fiecare rata a dobanzii care rezulta este plasata apoi in celula adiacenta din coloana D.

Se executa clic pe OK.

Schimbarea a doua variabile si o singura formula

In continuare, vom crea un tabel de date care modifica doua valori de intrare: dobanda si capitalul (volumul de inceput al imprumutului). Foaia de calcul da rezultatul unei formule pentru toate combinatiile acelor valori. Randul 10 al tabelului contine diferite valori ale capitalului pentru celula D4, Row Input Cell. Coloana din stanga a tabelului contine insa secventa de rate ale dobanzii care urmeaza sa fie folosita in D5.

Se remarca faptul ca atunci cand se folosesc doua valori diferite de intrare, se pot testa rezultatele intr-o singura formula Formula sau o referire la formula trebuie sa fie in coltul din stanga-sus al tabelului. In figura de mai jos, celula C10 contine formula de plata care urmeaza sa fie testata.

Row Input Cell este $D$4 deoarece valorile din randul de sus al tabelului sunt substituite in celula D4. Column Input Cell este $D$5 deoarece valorile din coloana din stanga a tabelului sunt substituite in celula D5. Deoarece fiecare suma lunara reprezinta o plata de efectuat, rezultatele apar cu semnul minus.

Editarea tabelelor de date

Dupa ce tabelul de date este complet, se pot schimba valorile din foaia de calcul de care depinde tabelul de date. Utilizand noile valori, tabelul recalculeaza. De exemplu, introducerea unui nou termen de plata in D5 determina aparitia unor noi valori de plata. Se pot schimba de asemenea numerele sau textul din liniile si coloanele valorilor de intrare. Daca este selectata recalcularea automata (Tools, Options, Calculations, Automatic), tabelul de date face actualizarea in mod prestabilit.

Nu se poate edita numai o singura formula din cadrul unui tabel de date. Toate formulele din aceasta zona au forma: . Pentru a reconstrui sau a extinde tabelul de date, se selecteaza din nou tabelul, incluzand celulele cu care dorim sa-l extindem, si se repeta etapele parcurse pentru a crea tabelul original. Noul tabel va fi scris peste cel vechi.

Calculele in tabelele de date

Tabelele de date mari sau numeroase pot reduce viteza de calcul. Daca se doreste ca foaia de calcul - dar nu si tabelele de date - sa efectueze recalculari:

In meniul Tools se executa clic pe Options.

In fereastra de dialog care apare, se executa clic pe tab-ul Calculation si se selecteaza Automatic except Tables.

Tabelele se recalculeaza apasand F9 pentru a calcula toate foile de calcul sau Shift+F9 pentru a calcula foaia de calcul activa.

Daca este selectata optiunea de recalculare Manual si se realizeaza o analiza pentru o baza de date mare, este posibil sa nu se doreasca ca foaia de calcul si tabelele relationate sa refaca acele calcule inainte de a face o salvare. Pentru a salva fara recalculare, se alege Tools, Options, se selecteaza eticheta Calculation si se dezactiveaza caseta Recalculate Before Save.

Analiza tendintelor

Excel poate calcula o regresie liniara sau o linie de maxima conformitate (linia care reprezinta cu erori minime tendinta datelor) fata de o serie de date pe care acesta le parcurge.

In unele cazuri, se poate utiliza rezultatul acestor calcule pentru analizarea tendintelor si pentru a face previziuni pe termen scurt. Sunt disponibile doua modalitati de calcul al datelor pentru aceste tendinte: utilizand marcajele grafice de umplere sau utilizand functiile foii de calcul.

In figura urmatoare, celulele C6:F6 contin date cunoscute privind costurile de deschidere a unor santiere, din 1991 pana in 1994.

Daca tendintele din intervalul 1991-1994 continua, se poate utiliza o regresie liniara pentru a calcula costurile de deschidere estimate pentru anii 1995 si 1996. Se poate face proiectia acestor date in celulele G6 si H6, utilizand o conformitate liniara maxima.

Daca se estimeaza o tendinta de crestere, proiectia acestor date se poate face utilizand comanda Grown Trend (tendinta ascendenta de forma exponentiala)

Calcularea tendintelor cu ajutorul marcajelor grafice de umplere

Pentru a umple un domeniu utilizand o conformitate liniara maxima:

Se selecteaza celulele care contin datele cunoscute, pe baza carora se doreste cunoasterea tendintei.

Se trage marcajul grafic de umplere catre dreapta cu butonul stang al mouse-ului apasat.

Pentru a umple un domeniu utilizand o tendinta de crestere, se parcurg urmatoarele etape

Se selecteaza celulele care contin datele cunoscute, pe baza carora se doreste cunoasterea tendintei.

Se trage marcajul grafic de umplere catre dreapta cu butonul din dreapta al mouse-ului apasat. Excel afiseaza un meniu rapid, ce contine comenzile Linear Trend (Tendinta liniara) si Growth Trend (Tendinta de crestere).

Se selecteaza Growth Trend.

Figura de mai jos arata rezultatele acestor proceduri.

Calcularea tendintelor cu ajutorul comenzii Data Series

Utilizand comanda Edit, Fill, Series se poate crea cea mai buna estimare (aproximare) a datelor in scopul inlocuirii sau extinderii setului original de date.

Datele originare care vor produce tendintele exemplificate sunt numerele 1, 5 si 12 din domeniul B4:D4. Sunt posibile urmatoarele optiuni pentru a alege aproximarea tendintei:

Pentru a crea o extrapolare folosind Edit, Fill, Series, se parcurg etapele:

Se selecteaza datele originale si celulele in care dorim extinderea datelor prin extrapolare.

Se alege Edit, Fill, Series.

Se alege una din optiunile descrise in tabelul precedent.

Calcularea tendintelor cu ajutorul functiilor foii de calcul

Functiile de extrapolare calculeaza ecuatia optima pentru cresterea liniara sau exponentiala a dreptei care trece prin datele respective. Functiile LINEST() si LOGEST() calculeaza parametrii pentru ecuatiile de interpolare liniara si exponentiala. Functiile TREND() sau GROWTH() calculeaza valorile de-a lungul dreptei de crestere liniara sau exponentiala, necesare pentru o prognoza pe termen scurt.

Pentru a folosi functiile de extrapolare este necesara definirea a doua tipuri de variabile:

Variabile dependente - sunt acele variabile a caror valoare se modifica atunci cand se schimba variabilele independente.

Variabile independente - cel mai adesea acestea pot fi: timpul, pretul sau materiile prime, temperatura sau marimea unei populatii.

Valorile variabilelor independente se introduc in functie ca argument x-cunoscut (known-x), iar variabilele dependente sunt introduse ca argument y-cunoscut (known-y).

In figura alaturata variabilele independente de timp (x-cunoscute) sunt introduse in B3:E3. Variabilele dependente ale proiectelor de constructii de locuinte (y-cunoscute) sunt introduse in B4:E4. Daca tendinta din ultimii 4 ani continua, se poate estima deschiderea unui santier de constructii de locuinte pentru urmatorii doi ani, parcurgand urmatoarele etape:

Se selecteaza domeniul de celule in care se obtin rezultatele interpolarii liniare (in exemplu B6:G6).

Se introduce functia TREND() fie de la tastatura, fie folosind Function Wizard.

Se introduc argumentele pentru functia TREND(). Sintaxa corecta este urmatoarea:

TREND(know_y's,known_x's,new_x's)

Pentru acest exemplu, argumentul known_y's este B4:E4 (numarul de locuinte sunt coordonate pe axa Y, deoarece aceste numere sunt dependente de valoarea anului).

Argumentul known_x's (coordonatele cunoscute pe axa X) este B3:E3 (anul este variabila independenta).

Argumentul new_x's (noile coordonate x) este B3:G3, reprezentand anii pentru care dorim sa stim valorile care descriu liniile de extrapolare. Zona selectata acopera spatiul pentru valorile rezultate calculate pentru y.

Pentru a introduce functia TREND() in domeniul selectat, se apasa Shift+Ctrl+Enter.

Trebuie remarcat ca noile valori de pe axa Y din celulele B6:E6 nu sunt identice cu cele de pe axa Y din celulele B4:E4 deoarece functia TREND() calculeaza valorile pentru deschiderea santierelor de constructii pentru acesti ani in functie de ecuatia de extrapolare (regresie liniara).