Tabele variabile. Scenarii

Enuntul temei

Se vor analiza patru variante de abonament:

Standard, cu 20 minute incluse (primele vorbite īn fiecare luna, indiferent de destinatie);

Numere favorite, cu 50 minute gratuite pentru trei numere favorite;

Standard cu extra-optiune de 2$ care permite 200 minute cu 0.05 USD/minut pentru trei numere favorite;

Numere favorite cu extra-optiune de 2$ care permite 200 minute cu 0.05 USD/minut pentru trei numere favorite.

Tot ce nu cade sub incidenta unei reduceri sau gratuitati, se va taxa cu 0.14 USD īn retea, respectiv 0.22 USD īn afara retelei.

Rationamentul care va conduce la determinarea abonamentului optim tine cont de urmatoarele variabile:

timpul total de convorbiri - timp_total sau t;

fractia (se poate eventual exprima si procentual) din timpul total cāt s-a vorbit īn retea - in_retea sau k;

fractia (se poate eventual exprima si procentual) din timpul īn retea cāt s-ar vorbi cu numere favorite - nr_favorite sau q.

Se mai fac urmatoarele ipoteze:

timpul total de convorbiri este mai mic decāt 200 de minute;

numerele favorite sunt din retea.

Īn aceste conditii, costurile pentru cele patru variante de abonament si optiuni sunt:

- Standard: (timp_total-20)*(0.14*in_retea+(1-in_retea)*0.22)

- Numere favorite: (nr_favorite*in_retea*timp_total - 50) * (1+SIGN(nr_favorite*in_retea*timp_total-50))/2*0.14+(1- nr_favorite) * in_retea*timp_total*0.14+(1-in_retea)*timp_total*0.22

- Standard cu extra-optiune de 2$: (timp_total-20)*(1+SIGN(timp_total-20))/2*((1-in_retea)*0.22+(1-nr_favorite) * in_retea * 0.14 + nr_favorite * in_retea*0.05)+2

Numere favorite cu extra-optiune de 2$: (nr_favorite*in_retea*timp_total-50)*(1+SIGN((nr_favorite*in_retea*timp_total-50))) / 2 * 0.05 + in_retea * timp_total * (1-nr_favorite)*0.14+(1-in_retea)*timp_total*0.22+2

Prezenta functiei Excel SIGN() are rolul de a nu permite diminuarea facturilor cu gratuitatile nefolosite.

Pentru a evita erorile de scriere a formulelor kilometrice de mai sus, fisierul facturi_mobil.xls   va contine deja expresiile de mai sus īn celulele H10:K10, astfel ca structura initiala a documentului Excel mentionat va fi cea din figura 1.

Pentru usurinta scrierii si urmaririi formulelor si mai ales pentru a se putea copia mai usor īn diverse zone ale foii de calcul de lucru, celulele D8, D9 si D10, au primit respectiv numele: timp_total, in_retea si nr_favorite.

Se mai face observatia ca parametrul q (nr_favorite), nu intervine decāt īn cazul abonamentelor cu numere favorite.

Figura 1

Mod de lucru

Se vor parcurge succesiv urmatoarele etape:

1. Se creeaza un tabel variabil cu o intrare pentru aflarea costurilor unui tip de abonament (Standard de exemplu) pentru variatia parametrului timp_total.

Pentru aceasta se va proceda astfel (formula din celula H10 este deja completata ceea ce explica prezenta unei valori acolo):

Se completeaza īn celulele G11:G17, duratele totale de convorbire estimate (desi īn exemplu valorile sunt īn progresie aritmetica, aceasta conditie nu este obligatorie);

Se selecteaza zona G10:H17;

Se foloseste cāmpul Date Tabel si se ajunge la caseta de dialog Tabel, prezentata īn figura 2. Cum datele de substituire (zona G11:G17) sunt dispuse pe coloana, se va completa īn cāmpul Celula de intrare coloana, adresa absoluta $E$8 (este posibil sa se faca pur si simplu clic pe celula E8 - care contine timpul total de convorbiri) - vezi figura 2;

Figura 2

Zona de interes va arata ca īn figura 3. Celulele H11:H17 vor contine valoarea facturii pentru cazurile īn care timpul total de convorbiri ar fi 20, 30,..80 de minute. Se propune ca tema repetarea procedurii, dar cu variatia fractiilor din timpul total cāt s-a vorbit īn retea.

Figura 3

2. Se completeaza tabelul initial cu noi coloane plasate la dreapta celei initiale. Se va putea face astfel o comparatie īntre diverse tipuri de abonamente pentru variatia aceluiasi parametru. Se va proceda astfel:

Se selecteaza zona G10:K17. Celulele I10:K10 contin deja formule, ceea ce explica prezenta unor valori īn celule. Se foloseste cāmpul Date Tabel si se completeaza cāmpul Celula de intrare coloana, exact ca īn exemplul anterior. Efectul va fi cel redat īn figura 4. Analiza tabelului permite stabilirea, pentru o anumita durata totala a convorbirilor, a celui mai convenabil abonament.

Se face precizarea ca ar fi fost posibila o dispunere a datelor de substitutie pe rānd si nu pe coloana. Īn acest caz, īn caseta de dialog din figura 2 s-ar fi completat cāmpul Celula de intrare rānd.

Figura 4

3. Īn exemplele precedente analiza s-a facut cu variatia unui singur parametru. Daca se doreste simularea variatiei a doi parametri se vor utiliza tabele variabile cu doua intrari. Se va proceda astfel:

Īntr-o zona oarecare a foii de calcul (fie G22:K29, ca īn figura 5), se genereaza tabelul din figura 5 (textul "20 minute incluse.", nu este obligatoriu). Celula cu fond gri contine copia celulei H10. Valorile de pe acelasi rānd cu celula cu fond gri, reprezinta diferite valori pentru fractia din timpul total cāt s-a vorbit īn retea (in_retea, sau k);

Figura 5

Se selecteaza zona G22:K29;

Se foloseste cāmpul Date Tabel, iar caseta de dialog Tabel se va completa ca īn figura 6 (se poate folosi si direct clic stānga pe celulele cu adresele corespunzatoare);

Figura 6

Se va obtine tabelul din figura 7. Analiza tabelului permite sa se afle de exemplu, care ar fi pretul facturii daca s-ar vorbi īn total 40 minute, din care 80% din convorbiri ar fi īn retea - valoarea (celula) īncadrata de o elipsa īn figura 7.

Figura 7

Procedānd analog, se vor genera tabele ca cele din figurile 8, 9, 10 si 11.

Figura 8

Figura 9

Figura 10

Figura 11

Formulele din celulele pe fond gri (situate īn coltul din stānga sus al fiecarui tabel), contin copii ale formulelor corespunzatoare din zona H10:K10. Avānd īn vedere ca īn formule nu s-au folosit adrese ci nume de celule, copierea se face īn adrese absolute astfel īncāt pozitionarea tabelelor din figurile 8.11 se poate face oriunde pe foaia de calcul. Interpretarea datelor din cele patru tabele este cāt se poate de usoara.

4. Daca se doreste compararea celor patru planuri tarifare pentru aceleasi conditii de utilizare (aceeasi parametri t, k si q), se poate proceda astfel:

Se copiaza  zona H10:K10 (care contine formulele pentru determinarea costurilor) īn orice zona libera a foii de calcul, fie de exemplu Q6:T6 (vezi figura 12) - pentru claritate se va adauga si capul de tabel din zona Q5:U5;

Īn celula U6 se scrie formula =MIN(Q6:T6);

Modificarea parametrilor din celulele E8:E11, va conduce la obtinerea īn celula U6 a valorii minime de factura pentru structura data de convorbiri.

Figura 12

Daca se doreste obtinerea nu a valorii minime a facturii ci a tipului (numelui) de abonament care conduce la factura minima, se va proceda astfel:

Se copiaza  zona H10:K10 (care contine formulele pentru determinarea costurilor) īn orice zona libera a foii de calcul, fie de exemplu Q2:T2 (vezi tot figura 12) - pentru claritate se va adauga si capul de tabel din zona Q3:T3 (este importanta amplasarea capului de tabel sub date si NU deasupra lor);

Īn celula U2 se scrie expresia =HLOOKUP(MIN(Q2:T2),Q2:T3,2,FALSE)

Rezultatul obtinut va fi numele tipului de abonament care asigura factura minima pentru conditiile de convorbiri date. Functia HLOOKUP cauta īn tabele. Īn exemplul de fata, va cauta īn zona Q2:T3, valoarea furnizata de MIN(Q2:T2) si va īntoarce continutul celulei situata imediat dupa cea care contine valoarea minima a facturii. Parametrul FALSE este si el important pentru ca indica faptul ca valorile din zona Q2:T2 nu sunt īn ordine crescatoare. Modificarea valorilor din celulele E8:E11 va determina modificarea continutului celulei U2. Testati!

5. Daca se doreste analiza costurilor prin considerarea tuturor celor trei parametri, solutia o reprezinta folosirea scenariilor. Se va proceda astfel:

Se foloseste cāmpul Instrumente Scenarii. Se ajunge la caseta de dialog Manager de scenarii (vezi figura 13.a);

Cum nu exista īnca nici un scenariu definit, se va apasa butonul Adaugare, ceea ce va conduce la caseta de dialog Editare Scenariu (figura 13.b). Se completeaza cāmpul Nume scenariu, respectiv Celule modificabile. Se vede ca zona precizata - $E$8:$E$10 - reprezinta celulele care contin cei trei parametri ce definesc profilul de utilizare. Se apasa butonul OK, ceea ce conduce la caseta de dialog Valori scenariu (figura 14). Aici se vor completa īn cāmpurile corespunzatoare valorile parametrilor ce definesc un anumit scenariu. Se observa ca valorile existente sunt cele curente din celulele $E$8:$E$10. Apasarea butonului OK asigura revenirea la celula de dialog Manager de scenarii, de aceasta data cu continutul din figura 15;

Se apasa pe butonul Adaugare si se defineste un nou scenariu, exact asa cum s-a facut mai sus, fireste cu precizarea altor valori pentru cei trei parametri;

Dupa ce s-au definit toate scenariile, se apasa pe butonul Rezumat, ceea ce va determina deschiderea casetei de dialog Rezumat Scenariu (vezi figura 16). Aici se precizeaza daca se va genera un sumar de scenariu sau un tabel pivot de scenariu si mai ales care este celula rezultatelor (U6 īn exemplul curent);

 

a)  b)

Figura 13

Figura 14

Figura 15

Figura 16

Apasarea butonului OK va determina crearea unei noi foi de calcul ce va contine chiar sumarul de scenariu sau tabelul pivot de scenariu. Daca s-a optat pentru un sumar de scenariu, se va ajunge la tabelul din figura 17;

Figura 17

Analiza scenariului permite compararea celor trei situatii ce difera prin valorile parametrilor considerati.

6. Este posibila si abordarea inversa a problemei: Care sa fie durata totala a convorbirilor (sau fractia de convorbiri īn retea, sau fractia de convorbiri cu numere favorite), astfel ca factura sa aiba o anumita valoare?

Aceasta problema se rezolva astfel:

Se selecteaza oricare din celulele care contine o formula pentru calculul unei facturi de abonament. Fie spre exemplificare celula Q6;

Se foloseste cāmpul Instrumente Cautare rezultat. Se ajunge astfel la caseta de dialog Cautare rezultat prezentata īn figura 18;

Figura 18

Īn cāmpul Se seteaza celula se mentine adresa Q6 (la fel de bine se poate alege o alta celula ce contine o formula);

Īn cāmpul La valoarea se scrie valoarea dorita a facturii (īn exemplu s-a optat pentru valoarea 3), iar īn cāmpul Modificānd celula, se scrie sau se selecteaza adresa (celula) E8 (care contine timpul total de convorbiri - putea fi la fel de bine aleasa celula E9 sau E10);

Se apasa butonul OK. Īnainte de a se vedea efectul, este afisata caseta de dialog Stare cautare rezultat (figura 19), care prezinta rezultatul algoritmului de cautare inversa. Nu īntotdeauna Valoare tinta si Valoare curenta coincid;

Figura 19

Apasarea butonului OK, va determina īnscrierea īn celula Q6 a valorii tinta (3), si modificarea valorii īnscrisa īn celula E8, astfel īncāt valoarea facturii sa fie 3. Este deci nevoie ca timpul total de convorbiri sa fie 39.231 minute pentru ca valoarea facturii pentru abonamentul Standard sa fie de 3 USD. Fireste se vor modifica toate tabelele, deoarece s-a schimbat unul din parametri de intrare.

Atentie !

Se recomanda testarea obtinerii unui tabel pivot de scenariu.