APLICAŢII FINANCIAR – CONTABILE rezolvate cu Microsoft Excel XP
EDITURA ? 2004
APLICAŢII FINANCIAR – CONTABILE rezolvate cu Microsoft Excel XP
Autorii sunt membrii ai colectivului de cadre didactice al catedrei de Contabilitate şi Informatică de Gestiune
Conf. dr. HORGA Vasile
Lect. dr. HORGA Mihaela
Asist. drd. BUSUIOC Loredana
Asist. drd. COMAN Marius
Date identificare editura + ISBN
CUPRINS
Noţiuni teoretice privind utilizarea foilor electronice de calcul 1.1 Noţiuni generale 5 1.2 Concepte ale utilizării foilor electronice de calcul 6 1.3 Prezentarea procesorului de tabele Microsoft Excel XP 14 1.4 Lucrul în foaia de calcul 15 1.5 Crearea unui tabel 16 Aplicaţia 1 – Situaţia contractelor de asigurare încheiate de o societate de asigurare
22
Aplicaţia 2 – Statul de salarii 31 Aplicaţia 3 – Aplicaţii de contabilitate financiară rezolvate cu ajutorul funcţiilor financiare şi de tip baze de date (amortizarea bunurilor achiziţionate de către o societate comercială)
47
Aplicaţia 4 – Aplicaţii de contabilitate financiară rezolvate cu ajutorul unor formule simple (majorarea capitalului unei societăţi comerciale)
62
Aplicaţia 5 – Aplicaţii financiar-contabilitate rezolvate cu ajutorul funcţiilor financiare (rambursarea unui împrumut contractat, determinarea valorii prezente/finale a unei sume de bani, determinarea valorii actualizate a unui flux de venituri şi a ratei interne de rentabilitate)
66
Aplicaţia 6 – Reprezentarea datelor sub formă de diagrame (grafice liniare, sectoriale, bursiere, mixte)
81
Aplicaţia 7 – Selecţia datelor din liste cu ajutorul filtrării automate sau complexe
97
Aplicaţia 8 – Întocmirea situaţiilor financiare plecând de la balanţa sintetică a conturilor, ilustrarea performanţelor unei întreprinderi prin intermediul graficelor
107
Aplicaţia 9 – Obţinerea unor rapoarte cu ajutorul instrumentelor PivotTable sau PivotChart
122
Probleme de rezolvat 137
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
1.1 Noţiuni generale
Programele de calcul tabelar sunt cunoscute în literatura informatică şi sub denumirea de
"editoare de tabele" sau programe "spreadsheet". Ele pun la dispoziţia utilizatorilor trei facilităţi
importante:
1. înregistrarea şi prelucrarea datelor sub form tabelară;
2. construirea şi afişarea sub formă grafică a datelor conţinute în tabele;
3. lucrul cu baze de date.
Conceptul original introdus de aceast categorie de produse software este FOAIA DE
CALCUL (electronic spreadsheet). Foaia de calcul, reprezintă un tabel uriaş, cu 65.536 de linii şi
256 de coloane, ceea ce ar înseamna un tabel de 7m lăţime şi 114m lungime. Pe ecran se afişează
doar o mică porţiune din acestă foaie, o fereastră mobilă formată de regulă din 20 linii şi 8 coloane
standard. Însă ne putem deplasa cu ajutorul tastelor de navigare în orice zonă a foii de calcul.
La intersecţia unui rînd cu o coloană se află CELULA (cell). Celula este considerată
unitatea elementară de prelucrare dintr-o foaie de calcul. Fiecare celulă este unic identificată prin
adresa sa, reprezentând coloana şi rîndul din care face parte (ex. A1). În celulele foii de calcul se
pot înscrie diferite date: numere, formule de calcul, funcţii, texte alfanumerice. Utilizatorul poate
plasa cursorul pe oricare celulă din foaia de calcul şi poate folosi adresele celulelor în orice formulă
de calcul. Datele conţinute în celulele foii de calcul pot fi supuse diverselor prelucrări: operaţii
aritmetice, operaţii logice, sortări, calcule statistice, etc. Se pot prelucra coloane sau rânduri întregi,
sau, se pot face operaţii de calcul matricial. Rezultatele pot fi redate sub formă grafică sau pot fi
tipărite la imprimantă.
Punctul forte al programelor de calcul tabelar îl constituie faptul că la orice schimbare a
unei date din oricare celulă a unui tabel creat pe foaia de calcul, se refac simultan toate calculele,
din întregul tabel, care intră în relaţie cu celula respectivă. O astfel de facilitate este deosebit de
utilă în situaţiile decizionale, când dorim să aflăm răspunsuri la întrebări de genul: ce-ar fi dacă
schimb aceste date cu altele? Orice variantă poate fi încercată operând modificările dorite şi pe
5
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
ecran vom avea instantaneu rezultatele calculelor. Nu sunt suficient de bune aceste rezultate, nu-i
nimic, revenim la varianta iniţial.
Generaţia programelor de calcul tabelar a fost inaugurat de apariţia în anul 1970 a
programului VISICALC (VISIble CALCculator) urmat de produsul LOTUS al firmei cu acelaşi
nume. Competiţia în lumea programelor de calcul tabelar este dură. Dacă într-o vreme firma
BORLAND cu produsul QUATTRO luase un oarecare avans, produsul EXCEL al firmei
MICROSOFT intră în forţă pe piaţă relansând astfel din nou competiţia în domeniu. Ca răspuns la
aceast provocare, firma LOTUS a lansat versiunea LOTUS for Windows cu o foaie de calcul
tridimensional. QUATTRO apare şi el în versiunea for Windows integrat în pachetul COREL
WORDPERFECT SUITE un concurent redutabil al produsului MICROSOFT OFFICE. Dar nu
trebuie neglizat nici producătorii de software autohtoni. INTERSOL SRL prin produsul
RomanianOffice a reuşit să adapteze şi să dezvolte codul sursă al proiectului OpenOffice.org într-o
suită profesională de aplicaţii de birou special realizată pentru piaţa românească. Dintre
componentele acestei suite se remarcă aplicaţia de calcul tabelar CALC, care oferă posibilitatea
analizei şi structurării datelor în funcţie de necesităţile utilizatorului. Printre caracterisiticile acestei
aplicaţii remarcăm: sortarea, filtrarea şi validarea cu uşurinţă a datelor, analiza complexă a
tabelelor, folosind instrumentul DataPilot, executarea cu succes a calculelor evoluate cu ajutorul
numeroaselor funcţii matematice şi statistice integrate. Rezultatele analizelor numerice pot fi
ilustrate prin diverse tipuri de grafice şi diagrame, generate rapid folosind instrumentul AutoFormat.
1.2 Concepte ale utilizării foilor electronice de calcul
A) Foaia de calcul
Toate programele de calcul tabelar operează cu o serie de concepte1 specifice care se
raportează la conceptul fundamental de foaie de calcul (Sheet). Aşa cum s-a menţionat, foaia de
calcul tabelar, în imaginea utilizatorului, este o matrice cu ale cărei elemente se pot face orice fel de
calcule aritmetico-logice.
Componentele elementare ale unei foi de calcul sunt CELULE dispuse pe linii şi coloane, în
ele putându-se introduce de la tastatură date şi formule de calcul.
Pentru a înţelege tehnica de lucru cu un procesor de calcul tabelar, este necesar să explicăm
următoarelor concepte de bază: registru (BOOK), rând (ROW), coloană (COLUMN), celulă 1 Aceste concepte sunt particularizate pe procesorul de tabele Microsoft Excel XP
6
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
(CELL), adresă (ADDRESS), zonă (RANGE), etichetă (LABEL), formulă (FORMULA), funcţie
(FUNCTION).
B) Registru
Pentru mai multă flexibilitate în prelucrarea datelor, programele de calcul tabelar sub
Windows generează în momentul lansării în execuţie trei foi de calcul independente denumite iniţial
Foaie1, Foaie2, Foaie3. Aceste foi sunt asemenea foilor unui registru, plasate virtual una după alta.
Cu un simplu clic oricare dintre aceste foi poate fi aduse în faţă pe ecranul de lucru. Utilizatorul
poate lucra individual cu fiecare foaie ca şi când aceasta ar fi singura foaie de calcul activă, dar
poate să utilizeze într-o foaie adresele şi datele conţinute de celelalte foi ale registrului. Registrul
conţine deci, un număr de foi care se prelucrează împreună într-o sesiune de lucru Excel în sensul
că:
1. prin lansarea în execuţie programul de calcul tabelar generează în memoria RAM, automat, un
registru cu mai multe foi de calcul tabelar independente;
2. salvarea unei foi de calcul într-un fişier pe disc echivalează cu salvarea întregului registru, cu
toate foile aferente, indiferent că au fost sau nu utilizate;
3. deschiderea unui fişier are ca efect aducerea în memoria RAM a registrului memorat în fişier cu
toate foile sale. Numărul de foi de calcul dintr-un registru este opţional iar utilizatorul poate să-
şi creeze câte foi doreşte într-o sesiune de lucru.
C) Rândul
Rândurile foii de calcul sunt numerotate în ordine crescătoare începând cu numărul 1 (nu
trebuie uitat că o foaie de calcul Excel are 65.536 de rânduri). Numerele rândurilor se afişează pe
partea stângă într-o bordură ce mărgineşte foaia de calcul.
D) Coloana
Coloanele foii de calcul sunt identificate printr-o literă sau o combinaţie de două litere. O
foaie de calcul are 256 de coloane. Prima coloană este denumită A, a doua coloană B, apoi după
terminarea literelor alfabetului se continuă cu combinaţii de două litere AA, AB, AC, etc.. Coloana
cu numele IV reprezintă ultima coloană a foii de calcul, coloana cu numărul 256.
7
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
E) Celula
Celula reprezintă spaţiul situat la intersecţia unei coloane cu un rând, care poate înregistra, la
un moment dat, un singur tip de date ce poate fi introdus de la tastatură sau poate rezulta în urma
unei anumite operaţii. Celula este unitatea elementară cu care operează programele de calcul
tabelar. Celula este folosită ca operand în formulele şi funcţiile de calcul. Iniţial toate celulele au
aceeaşi dimensiune, dar ulterior acestea se pot modifica, prin comenzi de formatare a tabelului.
Celula curentă este celula activă pe care este poziţionat indicatorul de celulă, un dreptunghi ce
semnifică poziţia curentă a cursorului. Întotdeauna, datele introduse de la tastatură, vor fi plasate în
celula curentă numai după apăsarea tastei ENTER sau a uneia din tastele de navigare (tastele cu
săgeţi).
F) Adresa
Adresa unei celule conţine informaţii pentru identificarea unică a celulei. Ea este alcătuită
din:
1. litera sau combinaţia de litere ce desemnează coloana;
2. numărul ce identifică rândul în care figurează celula.
Într-o celulă din foaia de calcul se pot introduce de la tastatură următoarele categorii
distincte de informaţii:
1. numere;
2. text, compus din caractere alfanumerice şi speciale, spaţii;
3. adresele altor celule;
4. secvenţe de comenzi pentru crearea de macroinstrucţiuni;
5. formule de calcul;
6. funcţii.
G) Zona sau domeniul
Zona este formată dintr-un grup de celule adiacente (una sau mai multe coloane sau
rânduri). O zonă nu poate avea decât o formă dreptunghiulară sau pătrată. Celulele situate într-o
zonă se prelucrează simultan în acelaşi mod (ex: ele se pot însuma, se pot afişa, se pot sorta, copia
sau muta în bloc).
O zonă este identificată şi adresată prin intermediul coordonatelor sale (Range).
8
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Coordonatele unei zone se dau precizând adresa celulei din colţul din stânga sus şi a celulei
din colţul dreapta jos, separate prin două puncte.
Folosind exemplul din figura nr. 1, cu reprezentarea omeniilor.putem identifica că Zona 1
are coordonatele B5:D9, Zona 2 are coordonatele B12:E12 iar Zona 3 are coordonatele F3:F10.
Figura nr. 1 Reprezentarea diferitelor tipuri de domenii
Zonelor pot avea asociat pe lângă adrese şi un nume (range name), pentru a face mai uşoar
redactarea formulelor sau a funcţiilor, figura nr. 2.
Figura nr. 2 Atribuirea unui nume pentru un domeniu
H) Formule
Formulele sunt expresii prin intermediul cărora se realizează calcule cu datele din celulele
9
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
foii de calcul. Formula se introduce în celula respectivă, este evaluată pe loc, iar pe ecran, în celulă
se afişează direct rezultatul.
O formulă se compune din operanzi şi operatori alcătuind expresii după modelul următor:
OPERAND operator OPERAND.
Expresiile alcătuite cu operatori aritmetici se numesc expresii aritmetice. Operatorii
aritmetici sunt : "+" pentru adunare, "-" pentru scădere, "*" pentru înmulţire, "/" pentru împărţire
“^” pentru exponenţiere.
Expresiile alcătuite cu operatori logici şi relaţionali se numesc expresii logice. Operatorii
logici şi relaţionali sunt: AND, NOT, OR, > (mai mare – greather), < (mai mic – less), = (egal –
equal);<> (diferit – is not equal).
În ceea ce priveşte operanzii aceştia pot fi: constante sau variabile.
Constantele sunt construcţii sintactice elementare ce nu-şi modifică valoarea pe parcursul
prelucrării. Se întâlnesc următoarele tipuri de constante:
1. constante numerice;
2. constante alfanumerice sau şiruri de caractere;
3. constante logice.
Constantele numerice sunt, fie numere întregi cu sau fără semn, fie numere reale. Un număr
poate avea până la maxim 15 cifre. Constantele alfanumerice reprezintă un text alcătuit din litere,
cifre sau caractere speciale, încadrat la dreapta şi la stânga de ghilimele. Numărul maxim de
caractere admis este de 256. Constantele logice TRUE pentru valoarea adevărat şi FALSE pentru
valoarea fals, se scriu ca atare, sau pot rezulta din evaluarea unor expresii logice.
Variabilele sunt reprezentate de adresele celulelor luate în calcul, sau funcţiile din
biblioteca de funcţii predefinite proprie fiecărui program de calcul tabelar. Pentru a atenţiona
programul că este vorba de o formulă , aceasta trebuie să înceapă cu un caracter special, şi
anume"=".
Regulile privind prioritatea operaţiilor sunt aceleaşi ca pentru orice expresie lineară
matematică. În expresiile complexe mai întâi se execută operaţiile aritmetice, apoi cele relaţionale şi
la sfârşit cele logice, rezultatul fiind desigur o constantă logică, însă această ordine poate fi
schimbată prin intermediul parantezelor.
În procesul de copiere sau mutare a formulelor, Microsoft Excel actualizează în mod
automat referinţele celulelor care constituie argumentele formulei respective.
Deoarece nu întotdeauna se doreşte actualizarea referinţelor, se poate recurge la utilizarea
10
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
adreselor absolute prin inserarea simbolului $ în faţa literei coloanei şi numărului rândului.
Putem distinge trei tipuri de adrese care pot fi implicate în scrierea unei formule:
1. Adrese absolute - acea parte a referinţei care nu se modifică în momentul în care formula se
mută sau se copiează;
2. Adrese relative - acea parte a referinţei care se modifică în momentul copierii formulei;
3. Adrese mixte - reprezintă o combinaţie a celor de mai sus iar la mutare sau copire permite
modificarea numai a rândurilor sau numai a coloanelor.
Exemplu de adrese: A1, $A$1, $A1, A$1
Încărcarea automată a celulelor unui domeniu se realizează cu ajutorul caracteristicii
CompletareAutomată (AutoFill), figura nr. 3, a cărei activare se realizează prin tragerea cursorului
de încărcare în afara celulei.
Figura nr. 3 Evidenţierea cursorului de încărcare pentru
completarea unei celule cu diverse informaţii
Se poate utiliza această caracteristică în următoarele situaţii:
1. Încărcarea celulelor cu o serie predefinită (lunile anului, serie liniară, etc.);
2. Copierea ca atare a unei celule dacă aceasta conţine o valoare numerică sau de alt tip;
3. Încărcarea automată a unei zone cu formula conţinută în prima celulă selectată.
I) Funcţii
Pentru o serie de calcule mai complexe procesoarele de calcul tabelar dispune de un număr
apreciabil de funcţii predefinite sau care se pot defini (funcţii proprii). Aceste funcţii realizează
calcule standard cu datele unei celule, sau a unei zone de celule. O funcţie are un nume unic şi o
listă de argumente.
Forma generală a unei funcţii este următoarea:
= nume funcţie(arg1,arg2,...,argn)
O funcţiei începe întotdeauna cu caracterul ”=”. Numele se compune din două sau mai multe
11
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
caractere. Argumentele unei funcţii pot fi: adrese de celule, adrese de zone de celule, constante
sau chiar alte formule şi funcţii. Argumentele trebuie separate prin virgulă (,) sau punct şi virgulă
(;), în funcţie de setările implicite (Panou de control – Setări regionale şi lingvistice). Dacă într-o
funcţie un argument este omis intenţionat, atunci în locul lui trebuie pusă o virgulă. Rezultatul
evaluării unei funcţii este afişat în celula în care s-a introdus textul funcţiei.
Ca urmare a incorectitudinii executării unor operaţii pot apare diferite tipuri de mesaje de
eroare (valabile în aplicaţia Microsoft Excel XP), dintre care menţionăm în tabelul nr. 1
următoarele:
MESAJ SEMNIFICAŢIE
# Apariţia unei erori în timpul calculării unei formule
###### Lăţimea celulei este insuficientă pentru vizualizarea
rezultatelor
#DIV/0 Se încearcă împărţirea la 0
#NAME? Nerecunoaşterea unei funcţii sau nume de zonă
#NUM! Există o problemă neindentificată legată de un
număr
#N/A Formula vizează o celulă în cadrul căreia nu există
nici o valoare
#REF! Formula nu poate fi aplicată
#VALUE! Argumentul din formulă este incorect
Tabelul nr. 1 Tipuri de mesaje de eroare
Există o gamă bogată de funcţii (aprox. 240) în Microsoft Excel, ce sunt grupate în nouă
categorii, de la funcţii matematice, financiare, statistice până la cele mai diverse funcţii de
manipulare a conţinutului celulelor foii de calcul. Principalele categorii de funcţii sunt identificate
în tabelul nr. 2. O listă complet a acestor funcţii, precum şi modul lor de apelare şi utilizare, se
poate obţine cu ajutorul meniului de asistenţă (Help).
12
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
TIPUL FUNCŢIEI ACŢIUNEA EXEMPLE
Matematice Calcule aritmetice şi funcţii
trigonometrice
ABS, COS, SIN, TAN,
LOG, LN, SRT, PI,
ROUND, EXP, MOD
Statistice Calcul de indicatori satistici
pe serii de date
AVERAGEG, COUNT,
MAX, MIN, STD, SUM,
VAR
Financiare Calcul dobânzi, rate,
împrumuturi, termene
SLN, STD, DDB, RATE,
TERM, FV, PMT, IPMT,
PV
Logice Evaluare expresii logice IF, FALSE, TRUE,
Data şi ora Calcul şi afişare data
calendaristic şi ora
DATE, DAY, HOUR,
MONTH, SECOND,
YEAR, TIME
Calcule în baze de
date
Calcule de indicatori într-o
bază de date
DAVERAGE, DCOUNT,
DMAX, DMIN, DSTD,
DSUM, DVAR
Prelucrări şiruri de
caractere
Prelucrarea unor şiruri de
caractere
CLEAN, CHAR, SEARCH,
VALUE, REPEAT, LEN
Speciale Diverse evaluări ale datelor
din celulele foii de calcul
COL, ROW, ISERR, CEL,
VLOOKUP,HLOOKUP
Tabelul nr. 2 Gama de funcţii utilizate de Microsoft Excel
13
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
1.3 Prezentarea procesorului de tabele Microsoft Excel XP
Pentru expunerea principiile generale de lucru cu un program de calcul tabelar şi construirea
exemplelor practice ne vom ajuta de Microsoft EXCEL XP localizat în limba română care face
parte din ediţia Office XP Professional.
Imediat după lansarea în execuţie, programul Excel afişează un mediu de lucru care respectă
structura şi grafica prietenoasă specifică aplicaţiilor din suita Microsoft Office. Elementele
principale ale aplicaţiei Microsoft Excel Xp sunt redate în figura nr. 4.
Figura nr. 4 Elementele principale
ale ecranului Excel
Prezentarea butoanelor de comandă aflate pe bara de instrumente Standard şi bara de
14
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
instrumente pentru Formatare este realizată cu ajutorul următoarelor imagini, figurile nr. 5 şi 6 în
care s-a detaliat acţiunea ce rezultă din folosirea butonului respectiv.
Figura nr. 5 Semnificaţia butoanelor din bara
de instrumente Standard
Figura nr. 6 Semnificaţia butoanelor din bara
de instrumente Formatare
1.4 Lucrul în foaia de calcul
a) Navigarea prin foaia de calcul
Pentru introducerea datelor, într-o celulă anume este necesar ca acestă celulă să fie selectată
ca fiind celulă curentă. Pentru aceasta trebuie să deplasăm "cursorul" la adresa care desemnează
celula în cauză. Deplasarea ”cursorului” prin foaia de calcul se realizează în mai multe moduri:
- cu ajutorul tastelor direcţionale;
- cu ajutorul mouse-ului;
- cu comanda Salt la… din meniul Editare (sau cu tasta F5).
Acţionând tastele direcţionale ne deplasăm la stânga, la dreapta, în sus, în jos cu câte un
rând sau o coloană. Tastele Pg Up respectiv Pg Dn ne permit să ne deplasăm cu un ecran (cca. 22
rânduri) în sus sau în jos. Tasta Tab respectiv combinaţia de taste Shift+Tab ne permite deplasarea
15
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
cu câte un ecran la dreapta respectiv la stânga. Cu un singur clic al mouse-ului pe celula dorită,
realizăm deplasarea cursorului la acea celulă. Pentru deplasarea rapidă la o celulă a foii de calcul
apelăm comanda Salt la… din meniul Editare (sau combinaţia Ctrl+G). Acestei comenzi trebuie
să-i furnizăm şi adresa celulei la care dorim să ajungem. Dacă suntem atenţi vom observa că în mod
normal se deplasează numai dreptunghiul luminos iar informaţiile afişate pe bordura orizontală şi
verticală rămân neschimbate atâta timp cât rămânem în cadrul celor 8 coloane şi 20 linii afişate
initial pe ecran. Dacă apăsăm tasta Scroll Lock vom remarca faptul că se deplasează textul scris
între cele două borduri, dreptunghiul nostru luminos stând pe loc. Revenirea la stilul obişnuit de
deplasare se face apăsând încă o dată tasta Scroll Lock.
b) Selectarea celulelor de lucru
Selecţia unei celule, pentru a fi obiectul unei prelucrări, echivalează cu plasarea cursorului
pe celula respectivă. Cu alte cuvinte, celula curentă, marcată de dreptunghi, este celula activă, care
urmează a fi prelucrată. Aşa cum am făcut precizarea, se pot prelucra cu o singură comandă şi un
grup de celule, care alcătuiesc o zonă de celule. In vederea prelucrării unei zone aceasta se indică
prin coordonatele sale sau se marchează de la tastatură ori cu mouse-ul prin glisare cu butonul
stânga apăsat. Zona marcată apare în video-revers şi se consideră că a fost selectată. Selectarea unei
zone de la tastatură se realizează apăsând tasta Shift şi una din tastele cu săgeţi. Un rând întreg se
selectează apăsând tastele Shift+Spaţiu, sau clic pe numărul rândului din bordura verticală a foii. O
coloană întreagă se selectează apăsând tastele Ctrl+Spaţiu, sau clic pe numele coloanei din bordura
orizontală a foii. Foaia de calcul integral, se selectează apăsând simultan tastele Ctrl+Shift+Spaţiu
sau clic pe colţul stânga sus al foii de calcul aflat la intersecţia celor două borduri. In interiorul unei
zone selectate nu ne putem deplasa de la o celulă la alta cu tastele cu săgeţi. Deplasarea totuşi a
cursorului prin zona selectată de la o celulă la alta se face cu tasta Enter, iar în sens invers cu tastele
Shift + Enter.
1. 5 Crearea unui tabel
Specificaţii generale privind crearea tabelelor
Crearea unui tabel, fie el şi un tabel simplu, necesită parcurgerea a cel puţin două etape:
1. proiectarea tabelului;
2. introducerea datelor .
16
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
a) Proiectarea tabelului
Proiectarea este prima etapă în procesul de creare a unui tabel. Un tabel, în accepţiunea
programelor de calcul tabelar se defineşte prin două elemente: structură şi conţinut.
Structura unui tabel are în vedere elemente generale şi stabile ale tabelului:
1. titlul tabelului;
2. denumirile coloanelor (sau capul de tabel);
3. denumirile rândurilor;
4. formulele şi funcţiile de calcul;.
Aceste elemente se stabilesc clar de la început şi se introduc ca elemente fixe ale tabelului.
Conţinutul este reprezentat de datele propriu-zise. Ele sunt elementele variabile dintr-un
tabel, în sensul că se schimbă în mod firesc atunci când reluăm programul de calcul (ex: stocurile,
facturile, salariile, se prelucrează zilnic sau lunar, de aici rezultă că se reiau mereu calculele dar cu
alte seturi de date însă pe aceeaşi structură).
În funcţie de natura datelor de intrare şi a rezultatelor, trebuie stabilite încă din etapa de
proiectare o serie de parametri. Operaţiile de stabilire a acestor parametri sunt numite setări şi se
referă la:
a. dimensiunea coloanelor;
b. alinierea datelor în cadrul unei celule sau pe întreaga coloană ;
c. poziţia punctului zecimal;
d. opţiuni speciale de afişare a rezultatelor.
În cadrul procedurii de creare a unui tabel putem lucra cu setările implicite ale programului
conform cărora:
a. celula are o mărime standard de 9 caractere;
b. alinierea textului se face implicit la stânga, iar a numerelor implicit la dreapta;
c. numărul de zecimale implicit este de două, etc.
Schimbarea acestor setări pentru a corepunde cerinţelor problemei de rezolvat vor fi arătate
pe parcursul capitolul II, rezervat aplicaţiilor practice.
17
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
b) Introducerea datelor în tabel
Sunt câteva reguli ce trebuie respectate în momentul introducerii datelor într-un tabel, în
funcţie de tipul acestora: numerice, şir de caractere, formule sau funcţii. Iată regulile generale:
1. se selectează, mai întâi, celula în care se introduc datele, pentru că datele introduse de la
tastatură sunt plasate întodeauna în celula curentă;
2. datele tastate sunt afişate mai întâi în bara de formule, ca şi adresa celulei curente, ele fiind
transferate în celulă pe foaia de calcul numai la apăsarea tastei Enter sau a unei taste de
deplasare a cursorului; până nu apăsăm o astfel de tastă mai putem efectua corecţii asupra
datelor tastate;
3. la terminarea tastării datelor putem apăsa:
3.1. Enter şi cursorul se mută în jos cu un rând;
3.2. Tab şi cursorul se mută la dreapta cu o coloană;
3.3. tastă direcţională şi cursorul se mută în direcţia săgeţii.
b.1) Introducerea datelor de tip text
Textul se introduce prin dactilografiere simplă de la tastatură. Excel consideră şirul de
caractere introdus drept un text dacă acesta începe cu o literă . Dacă este aşa, şirul de caractere
poate să conţină şi numere şi caractere speciale. În situaţia în care dorim totuşi să fie considerat text
un şir care începe cu o cifră atunci trebuie să începem prin a tasta mai întâi un apostrof. Apostroful
(‘) este un prefix care precede orice şir de caractere determinând alinierea textului în celulă la
stânga, care este de fapt şi alinierea implicită a oricărui text. Textul care depăşete spaţiul celulei
curente nu va fi trunchiat, ci va ocupa un număr mai mare de celule spre dreapta. Mărimea unei
coloane se poate însă ajusta astfel încât să cuprindă tot textul care a depăşit mărimea celulei (meniul
Formatare – opţiunea Coloană). CompletareAutomată este una din opţiunile uilizate pentru
creşterea vitezei de introducere a textelor. Dacă această opţiune este activă atunci textul din celula
precedentă este repetat în celula curentă cu condiţia să înceapă cu aceeaşi literă. Excel ne permite să
folosim o listă cu textele deja introduse în coloana curentă, listă care poate fi afişată cu un clic
dreapta urmat de comanda Alege din listă. Activarea/dezactivarea opţiunii CompletareAutomată
pentru Excel se face astfel:
1 activăm meniul Instrumente;
2 selectăm comanda Optiuni;
3 în pagina Editare, în caseta Activare CompletareAutomată pentru Celulă plasăm sau
18
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
eliminăm bifa de activare.
b.2) Introducerea datelor de tip număr
De la bun început facem precizarea că numerele se tastează direct începând cu prima cifră
semnificativă sau semnul algebric. Mulţi începători fac greşeala de a încerca să centreze numerele
în celulă, tastând la început spaţii înaintea primei cifre semnificative a numărului. Este o greşeală şi
ea este semnalată doar când folosim aceste celule în calcule. Calculele nu se pot face pentru că,
tastarea unui spaţiu înaintea numărului face ca informaţia din celulă să fie considerată drept un şir
de caractere, spaţiul fiind tratat drept un caracter alfabetic. Numerele se introduc respectând
următoarele reguli:
1. trebuie să înceapă cu o cifră sau cu unul din semnele algebrice +/- sau semnul valutar ($);
1. pot fi cuprinse între 10-99 şi 1099 ;
2. numerele pot fi întregi sau reale, partea întreagă se desparte de partea zecimal printr-un punct;
3. nu se admit spaţii sau virgule ca separatori;
4. se poate folosi şi formatul ştiinţific de scriere în virgulă mobilă, de exemplu: 8.07E + 05 =
807.000 Numerele prea mari, la introducere sunt trunchiate, iar rezultatele prea mari fac să se
tipărească un şir de asteriscuri (#######) în locul rezultatului aşteptat. Atunci, va trebui să
procedăm la mărirea coloanei în cauză. Alinierea numerelor se face implicit la dreapta, în
conformitate cu poziţia punctului zecimal.
Completarea automată este o opţiune care înlesneşte completarea unei coloane cu numere în
ordine strict crescătoare sau în altă ordine (zilele săptămânii, lunile, trimestrele anului). Pentru
aceasta vom proceda astfel:
1. introducem primul element, un număr, Monday sau January sau altă zi ori lună;
2. plasăm cu grijă cursorul pe pătrăţelul din colţul dreapta jos al pointerului de adresare al celulei
curente, care se numeşte “cursor de încărcare”, moment în care apare o cruciuliţă;
3. cu butonul stânga apăsat se glisează mouse-ul într-una din direcţiile avute în vedere ceea ce va
avea ca efect umplerea celulelor cu numere în ordine strict crescătoare, cu celelalte zile ale
săptămânii sau cu lunile anului.
b.3) Introducerea formulelor
Formulele de calcul se introduc de regulă începând cu semnul "=". Formulele se scriu cursiv
cu sau fără spaţii. Se admit paranteze, dar atenţie, numărul de paranteze închise trebuie să fie egal
19
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
cu numărul de paranteze deschise. In momentul introducerii, textul formulei apare în bara de
formule în dreptul adresei celulei curente. După apăsarea tastei Enter, sau a altei taste de încheiere a
textului, în celula curentă apare rezultatul calculelor. Ori de câte ori deplasând pointerul de
adresare, cursorul întâlneşte o celulă al cărei conţinut s-a obţinut printr-o formulă de calcul, pe bara
de formule vom putea citi textul formulei. Atunci când se efectuează corecţii masive în formulele
unui tabel, sau pentru întocmirea documentaţiei, putem modifica opţiunea standard de afişare şi în
locul rezultatelor în celulele foii de calcul putem să vizualizăm formulele de calcul folosite (meniul
Instrumente => Opţiuni => fişa Vizualizare => caseta de validare Formule).
Copierea formulelor
Posibilitatea copierii formulelor reprezintă o facilitate remarcabilă a procesoarelor de calcul
tabelar. O formulă dintr-o celulă poate fi copiată, deci reprodusă, într-una sau mai multe celule
adiacente, adresele celulelor incluse în formulă modificându-se automat în mod corespunzător.
Dacă zona de celule în care se face copierea este situată în jos, pe coloană, sub celula sursă, numele
coloanelor se păstrează constant, modificându-se corespunzător numai adresele rândurilor. La fel,
dacă celula se copiază într-o zonă de celule dispusă orizontal, pe acelaşi rând, atunci rămâne
constant numărul rândului dar se modifică adresa coloanelor. In acest fel formulele de calcul îşi
păstrează semnificaţia logică, respectând algoritmul de prelucrare, doar că se translatează adresele
celulelor cu care se operează.
b.4) Introducerea funcţiilor
Funcţiile se introduc în celula curentă începând întotdeauna cu simbolul simbolul ”=”.
Textul funcţiei nu admite spaţii. Se pot folosi numai paranteze rotunde pentru gruparea calculelor.
Trebuie respectat întocmai sintaxa funcţiei şi anume: numele, numărul, tipul şi ordinea
argumentelor. Reamintim că în unele funcţii un argument poate fi înlocuit cu o valoare implicită,
dacă pe poziţia sa în lista de argumente plasăm o virgulă. Procesorul de calcul tabelar nu trece mai
departe, dacă o funcţie este greşită sintactic. Ea trebuie scris corect, iar dacă este cazul se solicit
ajutor apăsând tasta F1. Procesorul Excel dispune de facilitatea - expertul funcţiilor (FUNCTION
WIZARD). Acestă componentă, al cărui icon are simbolul fx ne călăuzeşte pas cu pas în alegerea
funcţiei, operanzilor şi operatorilor cu ajutorul casetei de dialog care se afişează pentru fiecare
funcţie selectată .
20
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
c) Corecţii asupra datelor introduse în tabel
Corecţiile se pot face în două faze în funcţie de momentul sesizării greşelilor. Astfel,
greşelile de introducere a datelor se pot rezolva pe loc, dacă nu a fost apăsat încă tasta Enter sau una
din tastele cu săgeţi. Se şterg caracterele eronate şi se tastează cele corecte. Dacă însă greşelile au
fost sesizate după ce a fost apăsată tasta Enter, iar datele au ajuns în celulă, sau dacă eroarea a fost
observată mai târziu, atunci pentru corectare procedăm astfel:a) plasăm cursorul pe celula cu date
eronate; b) apăsăm tasta F2-Editare şi operăm corecţiile c) încheiem cu Enter, săgeată ori Tab.
21
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţia informatică propusă: generarea unei serii de date,
validări de tip listă, formatări condiţionale, formatări ale datelor introduse în celule,
utilizarea funcţiei logice – IF, funcţiilor de tip dată calendaristică – DATE, YEAR,
MONTH, DAY, formule de calcul simple.
Se dă un tabel care reprezintă situaţia contractelor de asigurare încheiate de agenţii
unei societăţi de asigurări, exemplificat în figura nr. 7.
Figura nr. 7
Să rezolve următoarele cerinţe asupra tabelului:
1) Să se construiască în foaia de calcul activă tabelul conform figurii nr. 7;
2) Numărul contractului şi data încheierii contractului se vor genera printr-o serie de date
liniară;
3) Data expirării contractului se va calcula adunând numărul de ani pentru care este încheiat
contractul la data încheierii acestuia;
22
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
4) Valuta se alege dintr-o listă derulantă cu următoarele elemente (USD, LEI, EURO);
5) Valoarea contractului se va afişa cu verde pentru contractele în USD, cu roşu pentru
EURO, cu mov pentru LEI;
6) Valoarea în lei se va calcula în funcţie de cursul valutei în care s-a încheiat contractul;
7) Obiectul contractului se va selecta dintr-o listă derulantă;
8) Comisionul firmei se va calcula astfel:
8.1) Pentru valori între 0-10.000.000 lei, comision 1,5%;
8.2) Pentru valori între 10.000.001-20.000.000 lei, comision 1,4%;
8.3) Pentru valori între 20.000.001-100.000.000 lei, comision 1,3%;
8.4) Pentru valori peste 100.000.000 lei, comision 1,2%;
8.5) Pentru contractele încheiate în lei comision 1,6%.
9) Comisionul agentului se va calcula astfel:
9.1) Pentru valoare contract între 0-250.000.000 lei, încheiat în lei – 10% din
comisionul firmei;
9.2) Pentru valoare contract între 0-250.000.000 lei, încheiat în valută – 15% din
comisionul firmei;
9.3) Pentru valoare contract peste 250.000.000 lei, încheiat în lei – 15% din comisionul
firmei;
9.4) Pentru valoare contract peste 250.000.000 lei, încheiat în valută – 20% din
comisionul firmei.
10) Suma netă încasată de bancă se calculează între comisionul agenţiei şi cel încasat de agent.
Rezolvarea cerinţelor problemei:
1) Pentru a răspunde primei cerinţe se va proceda astfel:
a) Se selectează zona A6:M6 şi cu ajutorul opţiunii Format din meniul Celulă, unde se vor bifa
căsuţele corespunzătoare din figura nr. 8.
23
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 8 Alegerea formatărilor pentru font şi chenar
b) pentru zona A7:M19 în afara formatărilor aplicate cu ajutorul opţiunilor din figura nr. 8 se va
folosi şi opţiunile din figura nr. 9.
Figura nr. 9 Alegerea culorii de fundal
2) Pentru a genera seria de date corespunzătoare coloanei Nr. Contract metoda utilizată constă în
selectarea celulelor A7:A8 (A7 reprezintă baza de plecare iar A8 baza+pasul) şi extinderea acestei
selecţii cu ajutorul instrumentului AutoCompletare (AutoFill) conform figurii nr. 10
24
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 10 Construirea unei serii liniare cu ajutorul
componentei AutoCompletare
Exerciţiu:
? Folosind aceeaşi tehnică ca la punctul 2 să se genereze o serie de tip dată
calendaristică pentru coloana Data încheierii.
3) Data expirării contractului se obţine cu ajutorul formulei din figura nr. 11.
Figura nr. 11
Observaţie: Valorile din coloana Perioada (de tip număr) pentru a arăta şi perioada (ani)
adiacent lor au fost supuse unei formatări particularizate aşa cum reiese din figura nr. 12.
Figura nr. 12
25
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Pentru afişarea datei calendaristice (în celula F1) de forma luna-an asupra funcţiei
TODAY() care redă data curentă a sistemului s-a operat următoarea formatare particularizată
conform figurii nr. 13.
Figura nr. 13
4) Pentru realizarea unei validări de tip listă care să permită utilizatorului (în cazul coloanei Valuta)
să introducă sau să aleagă valuta dintr-o listă s-a procedat conform figurii nr. 14.
Figura nr. 14
După definirea acestei validări la nivelul celulei G7, cu ajutorul instrumentului
AutoCompletare (AutoFill) se va copia validarea în domeniul G8:G19.
26
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Exerciţiu:
Corespunzător tehnicii descrise anterior să se procedeze şi pentru coloana Obiect
contract, ştiindu-se că lista de valori cuprinde: Autoturism, Casă, Barcă,
Apartament.
?
5) Pentru a putea afişa cu diverse culori valorile din coloana Valoare contract în funcţie de valorile
coloanei Valuta se va folosi o formatare condiţională aplicată valorilor din domeniul H7:H19
conform cu figura nr. 15.
Figura nr. 15
Alegerea unei acţiunii, figura nr. 16 (schimbarea culorii şi alte formatări posibil de aplicat
unei celule) în cazul în care condiţia (ex. G7=”USD”) este adevărată va fi realizată prin selectarea
elementelor specifice din caseta Formatare Celulă care apare atunci când este apăsat butonul
Format…. din figura nr. 15.
27
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 16
6) Pentru calculul valorii în lei a contractului de asigurare se va folosi expresia de calcul evidenţiată
în figura nr. 17
Figura nr. 17
Observaţie: Pentru înţelegerea acestei funcţii imbricate (funcţie în interiorul altei funcţii)
este redat în continuare pseudocodul:
Dacă1 Valută = ”USD”
Atunci Valoare Lei= Valoare Contract*Curs USD
Altfel
Dacă2 Valută=”EURO”
Atunci Valoare Lei= Valoare Contract*Curs Euro
Altfel Valoare Lei= Valoare Contract (exprimat în lei)
Sfârşit Dacă2
Sfârşit Dacă1
28
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Remarcă: Priviţi modul de închidere al structurilor condiţionale – prin includerea uneia în
interiorul celeilalte.
Implementarea acestui pseudocod în Excel se realizează cu ajutorul funcţiei logice IF care
are următoarea sintaxă:
=IF(CONDIŢIE, Acţiune în caz de adevăr, Acţiune în caz de fals)
Observaţie: Adresele Cursului Euro si USD s-au exprimat sub forma unor referinţe
absolute pentru ca la copierea acestei formule în domeniul I7:I19 să nu se producă decalarea lor.
8) Pentru a calcula Comisionul Firmei conform cerinţelor formulate (pe tranşe de valori) se va
utiliza următorul pseudocod:
Dacă1 Valută=”EURO” sau ”USD”
Atunci
Dacă2 Valoare Lei>=0 şi <=10.000.000
Atunci Comision firmă= 1,5%*Valoare Lei
Altfel
Dacă3 Valoare Lei>=10.000.001 şi <=20.000.000
Atunci Comision firmă= 1,4%*Valoare Lei
Altfel
Dacă4 Valoare Lei>=20.000.001 şi <=100.000.000
Atunci Comision firmă= 1,3%*Valoare Lei
Altfel Comision firmă= 1,3%*Valoare Lei
Sfârşit Dacă4
Sfârşit Dacă3
Sfârşit Dacă2
Altfel Comision firmă= 1,6%*Valoare Lei
Sfârşit Dacă1
Implementarea acestei funcţii în Excel se realizează cu formula corespunzătoare din figura
nr. 18.
Figura nr. 18
29
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Observaţie: 1) Pentru a putea alege EURO sau USD s-a folosit funcţia logică:
OR( arg1, arg2,………,argn)
Rezultatul evaluării globale a acestei funcţii este TRUE dacă numai unul din argumente
este adevărat şi FALSE dacă toate argumentele de evaluat sunt false.
2) Pentru a putea testa apartenenţa unei valori între două borne (minim şi
maxim) s-a utilizat funcţia logică:
AND(arg1, arg2,………,argn)
Rezultatul evaluării acestei funcţii este TRUE dacă toate argumente sunt adevărate şi
FALSE dacă unul din argumentele de evaluat este fals.
Exerciţiu:
Pentru a calcula comisionul agentului conform logicii şi tehnicii expusă mai sus
se va proceda în mod similar. ?
10) Suma netă se va calcula cu formula din figura nr. 19.
Figura nr. 19
30
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţia informatică propusă: formatări aplicate celulelor,
inserarea obiectelor de tip WordArt, utilizare formulelor de calcul, utilizarea funcţiei
logice – IF, funcţiei dată calendaristică – TODAY() şi a funcţiilor proprii definite de
utilizator (VBA).
Se dă următorul tabel, figura nr. 20, ce reprezintă statul de salarii pentru salariaţii
firmei S.C. DESIGN MOB SRL.
Figura nr. 20
31
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Pe baza tabelului anterior să se răspundă la următoarele cerinţe:
1) Să se insereze un comentariu celulelor F4, G4, H4 care are drept sursă:
F4 – contribuţie pentru pensie suplimentară
G4 – contribuţie pentru protecţia socială a şomerilor
H4 – contribuţie pentru asigurări sociale de sănătate
2) Să se calculeze elementele aferente statului de salariu conform formulelor din rândul 5 al
tabelului
3) Să se calculeze impozitul pe salarii prin două metode:
3.1) utilizând funcţiile simple
3.2) printr-o funcţie proprie (impozit) scrisă în VBA
4) Să se calculeze cheltuielile suportate de societate
Rezolvarea cerinţelor problemei:
1) Ataşarea de comentarii unor celule este o operaţiune care se poate realiza printr-un clic cu
butonul drept pe celula în cauză şi alegere din listă a opţiunii Inserare Comentariu, ca in figurile nr.
21 şi 22.
Exerciţiu:
Figura nr. 21 Figura nr. 22
? Urmând exemplu prezentat să se introducă comentariul aferent pentru celule G4, H4.
2) Pentru calculul elementelor componente ale statului de salarii se vor utiliza următoarele formule:
SPOR VECHIME: =10%*Salariul de bază
SALARIUL BRUT: =Salariul de bază+Spor Vechime
32
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
CAS: =Salariul Brut*9,50%
ŞOMAJ: =Salariul de bază*1%
SĂNĂTATE: =Salariul Brut*6,50%
CHELTUIELILE PROFESIONALE: =Deducere personală*15%
VENITUL NET: =Salariul Brut -( CAS+ ŞOMAJ+ SĂNĂTATE+ CH PROF)
VENIT BAZĂ DE CALCUL: =VENITUL NET-DEDUCEREA PERSONALĂ
SALARIUL NET: =Salariul Brut -( CAS+ ŞOMAJ+ SĂNĂTATE+ IMPOZIT SALARII)
3) Formula de calculul a impozitului pe profit construită cu ajutorul funcţiei logice IF este
următoarea:
Figura nr. 23
Observaţie: Pentru a vă uşura munca de a construi o formulă este bine să procedaţi înainte
la scrierea pseudocodului aşa cum a fost arătat la Aplicaţia 1.
O variantă mult mai elegantă şi totodată mai flexibilă o reprezintă scrierea unei funcţii
proprii de calcul în limbajul de programare Visual Basic for Applications (VBA).
Funcţia de calcul pentru calculul impozitului în VBA este redată în figura nr. 24.
Figura nr. 24
33
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
4) Pentru calcul cheltuielilor suportate de unitate procentele respective se aplică la salariul de
bază. În figura nr. 25 sunt calculate deja aceste cheltuieli, ele fiind un instrument de control pentru
obţinerea lor de către studenţi.
Figura nr. 25
5) În final sunt aplicate foii de calcul câteva operaţii de cosmetizare care constau în: a) crearea cu
ajutorul instrumentului WordArt a numelui unităţii şi b) afişarea sub formă particularizată a lunii şi
anului pentru care se întocmeşte statul de salarii.
a) Secvenţa pentru construirea numelui unităţii cu ajutorul instrumentului WordArt este ilustrată în
figurile nr. 26, 27, 28.
Figura nr. 26
Figura nr. 27
Figura nr. 28
34
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
b) Definirea unui format particularizat pentru afişarea datei calendaristice curente (funcţia
TODAY()) este reliefată in figura nr. 29.
Figura nr. 29
Imaginea completă a aplicaţiei rezolvate în totalitate este expusă în figura nr. 30
Figura nr. 30
35
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
O aplicaţie mai complexă privitor la statul de salarii este expusă în continuare:
O societate comercială doreşte să informatizeze activitatea de gestiune a salariaţilor şi
a drepturilor băneşti aferente acestora. Structurarea aplicaţiei pentru a fi rezolvată s-a făcut
în două foi de calcul. Prima numită ”baza de date”, figura nr. 31, conţine toate informaţiile
necesare despre: salariaţi şi drepturile lor salariale. A doua foaie de calcul numită „stat”
conţine elementele de calcul pentru determinarea salariului net al unui angajat într-o anumită
lună, figura nr. 40.
Figura nr. 31 Foaia de calcul considerată nomenclatorul unităţii
Asupra foii numită baza de date să se rezolve următoarele cerinţe:
1) Să se supună coloana Marca unei validări încrucişate în funcţie de valorile coloanei
Compartiment;
2) Să se supună coloana Compartiment unei validări încrucişate în funcţie de valorile coloanei
Filiala;
3) Să se definească o validare de tip listă pentru coloana Filiala cu următoarele valori:
Bucureşti, Ploieşti;
36
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
4) Să se supună unei validări coloana Salariul tarifar astfel încât pentru fiecare funcţie din
cadrul societăţii să se permită introducerea unei valori conform tabelului de la coordonatele
J20:L24;
5) Să se calculeze vechimea în ani în funcţie de data angajării;
6) Să se calculeze Sporul de vechime (atât prin funcţii standard cât şi printr-o funcţie definită
de utilizator) în funcţie de valorile coloanei Vechime conform următorului algoritm:
6.1 vechime<=3 ani – spor =0
6.2 vechime<=5 ani – spor =5%
6.3 vechime<=10 ani – spor =10%
6.4 vechime<=15 ani – spor =20%
6.5 vechime>20 ani – spor =25%
7) Câte angajări au fost făcute in luna curenta pe total întreprindere sau la nivelul unui
compartiment ales printr-o lista derulantă;
8) Să se afişeze salariaţii care au fost angajaţi in anul curent şi au salariul tarifar sub media
salariilor;
9) Să se afişeze intr-o celula a foii de calcul numărul de angajaţi ai societăţii care beneficiază
de un spor de vechime cuprins intre 5%-20%;
10) Să se afişeze lista economiştilor care au fost angajaţi in ultimii 3 ani de zile pe funcţia de
merceolog.
Rezolvarea cerinţelor problemei:
1) Validarea de tip listă aplicată elementelor coloanei Marca se realizează cu ajutorul opţiunii
Validare… din meniul Date. În caseta de dialog apărută ca urmarea a acestei alegeri, figura nr. 32,
se selectează tipul validării (Listă) şi se introduce sursa de date pentru generarea listei (aceasta este
reprezentată de o formulă complexă).
37
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 32
Formula completă ce alcătuieşte sursa listei este redată în continuare:
=IF(D2="contabilitate";$L$4:$L$14;IF(D2="marketing";$M$4:$M$12;$K$4:$K$14))
Temă:
Pentru cerinţa 2 procedând identic ca la punctul 1 să se supună unei validări
încrucişate elementele coloanei Compartiment în funcţie de valorile coloanei
”Filială”.
?
3) Pentru coloana Filiala validarea de tip listă este următoarea, figura nr. 33.
Figura nr. 33
38
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
4) Validarea de tip număr întreg aplicată coloanei Salariu tarifar este ilustrată în figura nr. 34.
Figura nr. 34
Observaţie: La această validare care acceptă introducerea de valori situate între o bornă
minimă si una maximă, limitele sunt stabilite cu ajutorul funcţiei de consultare verticală
VLOOKUP().
Sintaxa funcţiei este următoarea:
=VLOOKUP (cheia de consultare; tabelul cu informaţii în care sunt căutate datele;
numărul coloanei din tabel ce returnează valoarea potrivită)
5) Pentru calculul vechimii în ani în funcţie de data angajării s-a utilizat următoarea formulă:
=INT((TODAY()-F2)/365)
Utilizarea în formula anterioară a funcţiei INT() este dictată de returnarea unor numere care
să nu aibă parte zecimală. Funcţia are următorul rol: Rotunjeşte prin lipsă la cel mai apropiat
număr întreg.
Observaţie: Rezultatul următoarei expresii TODAY()-F2 este reprezentat de numărul de
zile care există între cele două date calendaristice. Acest rezultat trebuie împărţit la 365 pentru a
afla numărul de ani.
39
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
6) Calculul sporului de vechime în condiţiile enunţate mai sus se realizează:
* prin funcţii ale programului
=IF(H2<=3;0%;IF(AND(H2>3;H2<=5);3%;IF(AND(H2>5;H2<=10);5%;IF(AND(H2>10
;H2<=15);10%;IF(AND(H2>15;H2<=20);15%;20%)))))
* prin funcţii definite de utilizator, figura nr. 35.
Figura nr. 35
7) Pentru a afla câte angajări au fost făcute in luna iunie 2004 pe total întreprindere sau la nivelul
unui compartiment ales dintr-o listă derulantă se procedează în modul următor:
a) definirea zonei de criterii, figura nr. 36, unde:
Figura nr. 36
a1) primul criteriu constă in extragerea înregistrărilor care se potrivesc lunii iunie
=MONTH(F2)=6
a2) al doilea criteriu presupune fixarea anului curent
= YEAR(F2)=2004
a3) al treilea criteriu permite selectarea dintr-o lista (vezi întrebarea 3) a
compartimentului dorit
b) construirea funcţie de tip bază de date care corespunde cerinţei formulate mai sus.
=DCOUNT(tabel;6;A27:C28)
40
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
8) Pentru a afişa câţi salariaţi au fost angajaţi în anul curent şi au salariul tarifar sub medie se
procedează astfel:
a) definirea zonei de criterii, figura nr. 37, unde:
Figura nr. 37
a1) primul criteriu are rolul de a fixa anul
= YEAR(F2)=2004
a2) al doilea criteriu determină toate valorile din coloana salariu tarifar mai mici
decât media lor conform funcţiei
= G2<AVERAGE(G2:G22)
b) extragerea cu ajutorul filtrului avansat a înregistrărilor care se potrivesc criteriilor de
mai sus, figura nr. 38
Figura nr. 38
9) Pentru a determina numărul de angajaţi care beneficiază de un spor de vechime cuprins între 5%
- 20% se va folosi următoarea funcţie de tip bază de date, figura nr. 39.
Figura nr. 39
41
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
42
1) Cu ajutorul funcţiei de consultare verticala (VLOOKUP) să se completeze valorile
coloanelor: Nume salariat, Filială, Compartiment, Funcţie, Salariu tarifar, Spor vechime
(consultarea cu toate testele se va efectua asupra unei singure coloane);
În partea a doua a acestei aplicaţii asupra foii numită stat de salarii, figura nr. 40, să se
rezolve următoarele cerinţe:
3) Impozitul se va calcula printr-o funcţie proprie.
2) Să se calculeze elementele: Salariu brut (Salariu tarifar + Spor vechime), Salariu net
(Salariu brut – Impozit)
4) Determinaţi numărul de angajaţi care au peste 15 ani vechime.
3) Determinaţi totalul salariilor tarifare pentru cei cu o tranşă de vechime mai mică de
20%.
2) Care este cea mai frecventă tranşă de vechime.
1) Determinaţi numărul de angajaţi din cadrul compartimentului contabilitate.
Teme de lucru individual:
Observaţie: dispunerea celor două criterii pe acelaşi rând (49) presupune îndeplinirea lor
simultană în cadrul funcţiei (SI … SI ….).
10) Pentru afişarea listei economiştilor care au fost angajaţi în ultimii 3 ani pe funcţia de
merceolog se va proceda întocmai ca la punctul 8 cu stabilirea unor noi criterii de selecţie.
?
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
43
Figura nr. 40
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
1) Pentru uşurinţa în introducerea datelor aferente fiecărui angajat se va utiliza funcţia de consultare
verticală VLOOKUP.
Sintaxa acestei funcţii folosită pentru a completa elementele coloanei Nume salariat este:
=IF(OR(ISBLANK($A3);ISTEXT($A3);A3<=0);"verificati marca introdusa";
IF($A3<>VLOOKUP($A3;tabel;1);"marca introduse nu se regaseste in tabelul de
consultare";VLOOKUP($A3;tabel;2)))
Funcţia VLOOKUP are următoarea sintaxă:
=VLOOKUP (cheia de consultare; tabelul cu informaţii în care sunt căutate datele;
numărul coloanei din tabel ce returnează valoarea potrivită)
Observaţie: Asupra tabelei de consultare aflată in foaia de calcul Baza de Date trebuie
efectuate două operaţii de ajustare:
• Atribuirea unui nume (tabel) domeniului A1:I22
• Ordonarea tabelului din această zonă după valorile primei coloane (Marca) ce reprezintă
cheia de consultare utilizată în cadrul funcţiei.
2) Elementele coloanei Salariu brut şi Salariu net se calculează cu ajutorul următoarelor formule:
* Salariu brut =G3+G3*H3
* Salariu net =I3-J3
3) Codul VBA ce permite calculul elementelor din coloana Impozit printr-o funcţie proprie este
următorul:
Function impozit(TipContract, SalariuBrut)
Select Case TipContract
Case Is = "colaborator"
If SalariuBrut <= 500000 Then
impozit = SalariuBrut * 0.1
ElseIf SalariuBrut > 500000 And SalariuBrut <= 1500000 Then
impozit = 50000 + 0.2 * (SalariuBrut - 500000)
ElseIf SalariuBrut > 1500000 And SalariuBrut <= 2500000 Then
impozit = 250000 + 0.25 * (SalariuBrut - 1500000)
ElseIf SalariuBrut > 2500000 And SalariuBrut <= 3500000 Then
44
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
impozit = 500000 + 0.3 * (SalariuBrut - 2500000)
Else: impozit = 800000 + 0.4 * (SalariuBrut - 3500000)
End If
Case Is = "permanent"
If SalariuBrut <= 744000 Then
impozit = SalariuBrut * 0.21
ElseIf SalariuBrut > 744000 And SalariuBrut <= 2231000 Then
impozit = 156240 + 0.3 * (SalariuBrut - 744000)
ElseIf SalariuBrut > 2231001 And SalariuBrut <= 3718000 Then
impozit = 602340 + 0.38 * (SalariuBrut - 2231000)
Else: impozit = 1167400 + 0.45 * (SalariuBrut - 3718000)
End If
End Select
End Function
Observaţie: Faţă de exemplul de la problema precedentă unde am calculat impozitul cu
ajutorul unei funcţii proprii construită pe baza structurii de control: IF … THEN …. ELSE, am
selectat pentru problema curentă structura de control: SELECT CASE. Cuvintele îngroşate din cadrul
funcţiei reprezintă cuvinte cheie şi trebuie înscrise întocmai ca în exemplul arătat.
Teme de lucru individual: ? 1) Să se afişeze salariaţii de la compartimentul "oficiu de calcul" care au salariul brut mai
mare decât media salariilor brute.
2) Să se afişeze salariaţii care au funcţia de economist sau programator
3) Să se reprezinte grafic numărul economiştilor si cuantumul salariilor acestora pentru
toate compartimentele funcţionale aferente societăţii comerciale
4) Să se sintetizeze informaţia intr-un tabel in care sa să afişeze pentru fiecare
compartiment funcţional valoarea maxima, minima si media salariului brut, după care sa se
genereze o diagrama de amplitudine
5) Să se calculeze media salariilor pentru funcţia "economist" in cadrul compartimentului
Contabilitate
45
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
6) Să se calculeze total salarii tarifare si media acestora generând un tablou de sintetizare
a informaţiei pe funcţii si pe anii in care s-au făcut angajări, având ca element centralizator
Filiala
7) Să se sintetizeze informaţia calculându-se media salariilor pe funcţii si compartimente
pentru salariaţii cu salariul peste medie
8) Distribuitorii unei firme sunt plătiţi în funcţie de valoarea vânzărilor. Dacă valoarea
vânzărilor este mai mică de 5000000 comisionul este 0%, între 5000000 şi 30000000 comisionul
este de 4%, între 30000000 şi 70000000 comisionul este de 7% iar peste 70000000 comisionul este
de 10%. Pentru o valoarea a vânzărilor de 80000000 determinaţi care este comisionul obţinut de
distribuitor (se foloseşte funcţia VLOOKUP)?
46
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţia informatică propusă: calcul tabelar, funcţii financiare:
AMORLINC, AMORDEGR, SLN, SYD, DB, DDB.
Societatea comercială „ONYX” S.A. achiziţionează un echipament tehnologic la valoarea
de intrare de 50.000.000 lei şi cu o durată normală de utilizare de 10 ani. Să se calculeze într-o
foaie de calcul amortizarea anuală luând în consideraţie următoarele regimuri de amortizare:
Liniar; Degresiv; Accelerat.
Transpunerea în foaia de calcul a elementelor de intrare se evidenţiază în figura nr. 41.
Figura nr. 41
Observaţie: Pentru elementele din zona Date calculate s-au utilizat următoarele formule de
calcul:
Rata anuală a amortizării: =1/B5
Data sfârşitului perioadei pentru primul amortisment: =DATE(YEAR(B6);12;31)
Funcţie =DATE( ) întoarce numărul seriei secvenţiale care reprezintă o anumită dată. Dacă
formatul celulei a fost Standard înainte ca funcţia să fie introdusă, rezultatul este formatat sub formă
de dată calendaristică.
47
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Funcţia DATE este foarte utilă în formulele în care anul, luna şi ziua nu sunt constante ci sunt la
rândul lor formule ca în exemplul următor.
Data sfârşitului perioadei pentru ultimul amortisment:
= DATE(YEAR(B6)+B5;MONTH(B6);DAY(B6))
Tabelul de amortizare în regim liniar este construit in foaia de calcul conform figurii nr. 42.
Figura nr. 42
Pentru calculul Amortismentului anual s-a utilizat următoarea funcţie:
=SLN (valoarea de inventar;valoarea reziduala; durata normala de funcţionare)
Funcţia calculează amortismentul liniar aferent unei imobilizări ţinând cont de: valoare de
inventar dată; valoarea reziduală estimată şi durata normală de utilizare a bunului.
Formulele efective pentru calculul Amortismentului în fiecare an, a Amortizării cumulate şi
a Valorii Contabile sunt:
Amortismentul =SLN($B$4;;$B$5)
Amortizării cumulate =B17+C16
Valorii Contabile =$B$4-C17
Tabelul de amortizare în regim liniar ţinând cont de fracţiunea de an (prorata temporis) când a
fost dat în utilizare bunul este construit în foaia de calcul conform figurii nr. 43.
48
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 43
Formulele ce permit calcularea amortizării liniare în acest caz sunt următoarele:
1) Amortismentul anual =AMORLINC($B$4;$B$6;$B$10;0;ROW()-16;$B$9;3)
Sintaxa funcţiei utilizate mai sus este următoarea:
AMORLINC(Cost; DatăCumpărare;PrimaPerioadă;ValoareReziduală;Perioadă;Rată;Bază) Unde:
Cost - este costul mijlocului fix.
DatăCumpărare - este data achiziţionării mijlocului fix.
PrimaPerioadă - este data de sfârşit a primei perioade.
ValoareReziduală - este valoarea reziduală la sfârşitul duratei de viaţă a mijlocului fix.
Perioadă - este perioada pentru care se calculează amortizarea, acest număr trebuie să fie egal
cu 0 pentru prima perioadă. Soluţia pentru a genera acest număr o reprezintă funcţia ROW()
care generează automat numărul de linie în care se găseşte editată funcţia respectivă.
Rată - este rata amortizării.
Bază - este baza utilizată pentru an.
Bază Sistemul de dată calendaristică
0 360 zile (metoda NASD)
1 Actual
3 365 zile într-un an
4 360 zile într-un an (metoda europeană)
49
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
2) Amortizarea cumulată pentru perioada N se calculează adunând amortizarea cumulată din
perioada N-1 cu amortismentul din perioada N conform formulei =H16+G17
3) Valoarea contabilă se calculează ca diferenţă între valoarea de inventar şi amortizarea
cumulată =$B$4-H17
Tabelul de amortizare în regim degresiv este construit in foaia de calcul conform figurii nr. 44.
Figura nr. 44
Formulele ce permit calcularea amortizării degresive în acest caz sunt următoarele:
1) Amortismentul periodic:
=IF(H32*$I$29>SLN($B$4;0;$B$5);H32*$I$29;H32/($B$5-F32+1))
De remarcat că se calculează amortizarea degresivă (cu cota de 20%) până în momentul în care
aceasta este mai mare decât cea calculată conform metodei liniare, de aici încolo se va utiliza
amortizarea liniară (prin împărţirea valorii rămase la numărul de perioade cât se va mai utiliza mijlocul
fix.
2) Valoarea rămasă =H32-G32
Tabelul de amortizare în regim degresiv ţinând cont de fracţiunea de an (prorata temporis) când
a fost dat în utilizare bunul este construit in foaia de calcul conform figurii nr. 45.
50
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 45
Formulele ce permit calcularea amortizării degresive în acest caz sunt următoarele:
1) Amortismentul periodic =AMORDEGRC($B$4;$B$6;$B$10;0;ROW()-31;$B$9;3)
Funcţia întoarce amortizarea pentru fiecare perioadă contabilizată. Această funcţie este
disponibilă pentru sistemul de contabilitate francez. Dacă un mijloc fix este achiziţionat la mijlocul
perioadei contabilizate, este luată în calcul amortizarea proporţională. Funcţia este similară cu
AMORLINC, cu excepţia faptului că este aplicat în calcule un coeficient de amortizare în funcţie de
durata de viaţă a mijloacelor fixe.
2) Amortizarea cumulată =B17+C16
3) Valoarea contabilă =$B$4-C17
Tabelul de amortizare în regim accelerat este construit in foaia de calcul conform figurii nr. 46.
Figura nr. 46
51
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Formulele ce permit calcularea amortizării degresive în acest caz sunt următoarele:
1) Amortismentul corespunzător primei perioade (când se amortizează accelerat)
=$D$48*$C$46
2) Amortismentul corespunzător următoarelor perioade (când se amortizează liniar)
=$C$48/9
3) Valoarea rămasă aferentă primei perioade: =$D$48-B48
4) Valoarea rămasă aferentă celorlalte perioade: =C48-B49
Alte metode de amortizare care însă nu sunt utilizate de legislaţia din ţara noastră sunt ilustrate
în figura nr. 47.
Figura nr. 47
1) SYD (amortismentul degresiv absolut) - întoarce suma valorilor anuale amortizate (deprecierea)
pentru un mijloc fix, pentru o perioadă specificată.
Sintaxa funcţiei este: SYD(cost; valoare reziduală; durata de viaţă; perioada)
Perioada – perioada pentru care se amortizează bunul <durata de viaţă şi perioada trebuie să
facă referire la aceeaşi perioadă de timp (lună, an)>.
Funcţia utilizată în tabel =SYD($B$62;;$B$63;A69)
52
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
2) VDB (amortismentul degresiv absolut) - Întoarce amortizarea unui mijloc fix pentru orice
perioadă specificată, inclusiv perioade parţiale, utilizând metoda balanţei dublu descrescătoare sau o
altă metodă pe care o specificaţi. VDB este valabilă pentru balanţe cu descreşteri variabile.
Sintaxa funcţiei:
=VDB(cost; valoare reziduală;durată de viaţă;prima perioada; ultima perioadă; factor; fără comutare)
Unde:
Cost - este costul iniţial al mijlocului fix.
Valoarea reziduală - este valoarea la sfârşitul amortizării (uneori denumită valoare reziduală a
mijlocului fix).
Durată viaţă - este numărul de perioade de-a lungul cărora se amortizează mijlocul fix (uneori
denumită durata de utilizare a mijlocului fix).
Prima perioadă - este perioada de început pentru care vreţi să calculaţi amortizarea. Prima perioadă
trebuie să folosească aceleaşi unităţi ca şi argumentul durată viaţă.
Ultima perioadă - este perioada de sfârşit pentru care vreţi să calculaţi amortizarea. Ultima perioadă
trebuie să folosească aceleaşi unităţi ca şi argumentul durată viaţă.
Factor - este rata de descreştere a balanţei. Dacă argumentul factor este omis, se presupune a fi 2
(metoda balanţei dublu descrescătoare). Modificaţi argumentul factor dacă nu vreţi să utilizaţi metoda
balanţei dublu descrescătoare. Pentru o descriere a metodei balanţei dublu descrescătoare, vezi DDB.
Fără comutare - este o valoare logică ce specifică dacă să comute la amortizarea în linie dreaptă
atunci când amortizarea este mai mare decât cea reieşită din calculul balanţei descrescătoare.
• Dacă fără comutare este TRUE, Microsoft Excel nu comută la amortizarea în linie dreaptă chiar
dacă amortizarea este mai mare decât cea reieşită din calculul balanţei descrescătoare.
• Dacă fără comutare este FALSE sau omis, Excel comută la amortizarea în linie dreaptă atunci
când amortizarea este mai mare decât cea reieşită din calculul balanţei descrescătoare.
Toate argumentele, cu excepţia argumentului fără comutare trebuie să fie numere pozitive.
Funcţia utilizată în foaia de calcul:
=VDB($B$62;;$B$63;$A$68;A69)
3) DDB (amortismentul degresiv absolut) - întoarce amortizarea unui mijloc fix pentru o perioadă
determinată, utilizând metoda balanţei dublu-regresive sau o altă metodă dorită.
Sintaxa funcţiei: =DDB(cost;valoare reziduală;durata de viaţă;perioada;factor)
53
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Unde:
Cost - este costul iniţial al mijlocului fix.
Valoarea reziduală - este valoarea la sfârşitul amortizării (uneori denumită valoare reziduală a
mijlocului fix).
Durată de viaţă - este numărul de perioade de-a lungul cărora se amortizează mijlocul fix (uneori
denumit durata de utilizare a mijlocului fix).
Perioadă - este intervalul de timp pentru care se calculează amortizarea. Perioadă trebuie să utilizeze
aceleaşi unităţi de măsură ca şi durată viaţă.
Factor - este rata (un coeficient) la care balanţa se dezechilibrează. Valoarea implicită este 2 (metoda
balanţei dublu-regresive).
Funcţia utilizată în foaia de calcul:
=DDB($B$62;;$B$63;A69;0,5)
4) BD (amortismentul degresiv absolut) - întoarce amortizarea unui mijloc fix pentru o perioadă
specificată, utilizând metoda balanţei cu descreştere fixă
Sintaxa funcţiei: =DB(cost;valoare reziduală;durata de viaţă;perioada;lună)
Argumentele funcţiei sunt aceleaşi cu cele folosite la funcţiile anterioare, cu excepţia
următorului:
Lună - este numărul de luni din primul an. Dacă lună este omis, se consideră egal cu 12.
Funcţia utilizată în foaia de calcul:
=DB($B$62;;$B$63;A69)
Utilizand instrumentele de asistare a deciziei de care dispune procesorul de tabele Excel (tabele
de ipoteze, scenariile, rezolvitorul) să se rezolve urmatoarele cerinţe utilizând aceleaşi date de intrare
folosite şi până acum şi metodele de amortizare degresivă SYD, DB.
1) Să se calculeze valoarea amortismentului anual degresiv absolut pentru imobilizarea
achiziţionată printr-o tehnică de simulare pentru mai multe variante de durate normale de
funcţionare ( de la 1 la 10 ani) şi pentru mai multe perioade de calcul (de la 1 la 10 ani).
54
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Pentru a putea rezolva o astfel de cerinţă se apelează la tabela de ipoteze cu una sau două
variabile iar un rol important îl constituie modul de construire al ei aşa cum se exemplifică în figurile
nr. 48 şi 49.
Figura nr. 48
Figura nr. 49
O combinaţie utilizată în mod frecvent este între tabela de ipoteze cu o singură variabilă şi
funcţiile de tip bază de date: DSUM, DAVERAGE, DCOUNT, DMAX, DMIN. Sintaxa acestor funcţii
este identică astfel încât ea va fi exemplificată asupra funcţiei DSUM.
=DSUM(bază de date;câmp;criteriu)
55
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Unde:
Bază_date - este zona de celule care formează lista sau baza de date. O bază de date este o listă
de date înrudite în care rândurile cu informaţiile înrudite sunt înregistrări, iar coloanele de date
sunt câmpuri. Primul rând din listă conţine etichete pentru fiecare coloană.
Câmp - arată care coloană este utilizată în funcţie. Câmpul poate fi dat ca text, cu eticheta
coloanei cuprinsă între ghilimele, cum ar fi "Nume produs" sau ca un număr care reprezintă
poziţia coloanei în cadrul listei: 1 pentru prima coloană, 2 pentru a doua coloană, etc.
Criterii - este zona de celule care conţin condiţiile specificate. Se poate utiliza orice zonă
pentru argumentul criterii, atâta timp cât aceasta include cel puţin o etichetă de coloană şi cel
puţin o celulă sub eticheta coloanei, în care este specificată o condiţie pentru coloană.
Rezolvarea efectivă a cerinţei nr. 1 este ilustrată în figura nr. 50
Figura nr. 50
Generarea acestui tabel de ipoteze cu două variabile a presupus urmatorele etape de lucru:
a) aranjarea in foia de calcul a celor doua ipoteze conform celor prezentate anterior, figura nr. 49
b) calcularea amortizării degresive (celula B90) cu ajutorul datele care se află în domeniul B85:B87.
Formula de calcul utilizată este: =IF(ISERROR(SYD(B85;;B86;B87));0;SYD(B85;;B86;B87)); se
remarcă prezenta funcţiei ISERROR() ce returnează valoarea 0 în momentul în care funcţia SYD() va
returna o eroare.
56
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
c) extinderea acestei formule luând în considerare cele două variabile se face cu ajutorul comenzii Date
/Tabel conform cu figura nr. 51.
Figura nr. 51
d) Stabilirea unui fundal de culoare gri pentru valorile amortizării degresive egale cu 0 a presupus
stabilirea unei formatări condiţionale (Format/Formate condiţionale) asupra zonei C91:L100 aşa cum
se observă din figura nr. 52.
Figura nr. 52
2) Să se creeze un scenariu prin care să se simuleze pentru imobilizarea achiziţionată efectele
conjugate ale valorii reziduale, ale duratei normale de funcţionare şi numărul de luni de utilizare
pe an a imobilizării, asupra mărimii amortismentului degresiv absolut, calculat prin funcţia DB.
Să se prezinte cele trei variante simulate într-un tablou de sinteză.
Plecând de la datele iniţiale ale problemei, evidenţiate în figura nr. 53, pentru a rezolva enunţul
s-au parcurs următorii paşi:
a) denumirea celulelor luate în calcul opţiunea Inserare/Nume, figura nr. 54;
b) lansarea în execuţie a managerului de scenarii opţiunea Instrumente/Scenarii, figura nr. 55;
c) introducerea valorilor conform scenariului creat (Varianta Optimista), figura nr. 56;
d) Generarea unui tablou de sinteza (figura nr. 57) pe baza celor trei scenarii construite, prin apelarea
butonului Rezumat ..., din caseta de dialog Manager de scenarii, figura nr. 55.
57
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 53
Figura nr. 54
Figura nr. 55
58
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 56
Figura nr. 57
3) Să se optimizeze amortizarea alegându-se ca soluţie cea mai mică valoare anuală a acesteia în
condiţiile în care se utilizează metoda DB de amortizare. Valoarea reziduală trebuie să fie cel
puţin 5% din valoarea de inventar şi cel mult 10%; durata în ani trebuie să fie cuprinsă între 3 şi
10 ani, perioada pentru care se calculează amortizarea trebuie să fie cuprinsă între 3 şi 10 ani,
iar valoarea de inventar trebuie să fie de peste 2000000 lei. Rezultatele se vor salva într-un
scenariu intitulat "Amortizarea minimă".
Plecand de la datele initiale ale problemei, figura nr. 58, pentru a rezolva enunţul s-au parcurs
următorii paşi:
a) denumirea celulelor luate în calcul opţiunea Inserare / Nume, idem figura nr. 54;
b) lansarea în execuţie a instrumentului corespunzător Instrumente/Rezolvitor....;
c) stabilirea criteriilor conform cerinţelor, figura nr. 59 şi figura nr. 60;
d) generarea pe baza celor specificate a scenariului Amortizare minimă, figura nr. 61.
59
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 58
Figura nr. 59
Figura nr. 60
60
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 61
61
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţia informatică propusă: formatări asupra celulelor din
foaia de calcul, utilizarea de formule simple pentru rezolvarea cerinţelor aplicaţiei.
Societatea comercială ONIX S.A. doreşte să-şi majoreze capitalul social prin emisia de
acţiuni. Pe baza datelor din figura nr. 62 să se calculeze: Valoarea matematică contabilă nouă
(VMC), Dreptul de subscripţie, Raportul acţiuni vechi/acţiuni noi.
Elementele teoretice utilizate pentru a rezolva cerinţa de mai sus sunt expuse în continuare:
În cazul creşterii capitalului social prin emisia de noi acţiuni (preţul de emisiune al noilor
acţiuni este mai mic decât valoarea matematică contabilă a acţiunilor vechi), se produce “efectul de
dilatare” a capitalului care generează la rândul său o pierdere pentru acţionarii vechi care nu participă la
subscrierea de noi acţiuni. Pentru a compensa această pierdere se apelează la Drepturile Preferenţiale
de Subscriere (DS-uri).
Drepturile preferenţiale de subscriere sunt titluri negociate la bursă care intră în paritate cu
acţiunile vechi. Mărimea lor se calculează ca diferenţă între VMC veche şi VMC nouă a unei acţiuni.
Raţionamentul de calcul a valorii teoretice a unui DS este următorul:
- capital propriu înaintea creşterii prin aporturi noi: N x V
- creşterea: N’ x E
- capitalul propriu rezultat în urma creşterii: (N x V) + (N’ x E)
- valoarea matematică contabilă a unei acţiuni după creşterea
capitalului propriu: [(N x V) + (N’ x E)]/( N+N’)
- valoarea teoretică a dreptului de subscriere (DS): [N’ x (V - E)]/(N+N’)
Legendă:
N = numărul acţiunilor vechi;
N’ = numărul acţiunilor noi;
V = valoarea matematică contabilă a acţiunilor vechi;
E = preţul de emisiune al acţiunilor noi
62
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Datele iniţiale ale problemei, figura nr. 62.
Figura nr. 62
Rezolvarea problemei este ilustrată în figura nr. 63.
Figura nr. 63
Formulele utilizate pentru calculul elementelor din figura nr. 63 sunt următoarele:
Total capital propriu: =B35+B34
Creşterea capitalului propriu: =SUM(B38:B39)
Creşterea capitalului social: =F25*F26
Prime de emisiune: =F25*(F27-F26)
Valoarea matematică contabilă nouă: =(B36+B37)/(B28+F25)
Raportul acţiuni vechi/acţiuni noi: =F25/B28, formatat sub formă de fracţie conform
figurii nr. 64
Dreptul de subscripţie (DS): =B30-B40
63
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 64
Stabilirea formatului de afişare al valorilor sub forma 260.000.000 lei presupune selectarea
celulei pentru care se doreşte aplicarea formatări şi alegerea formatului corespunzător conform figurii
nr. 65
Figura nr. 65
Stabilirea unui fundal de culoare gri pentru zona de tabel care afişează valorile numerice a
presupus selectarea zonei B34:B42 şi alegerea fundalului cu ajutorul opţiunii corespunzătoare, identic
cu figura nr. 66.
64
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Figura nr. 66
? Un întreprinzător doreşte să facă o investiţie şi face câteva estimări privind
valoarea investiţiei, cheltuielile anuale şi veniturile anuale. Să se determine anul
(se afişează o săgeata) în care investiţia devine profitabilă (venitul total depăşeşte
cheltuielile totale).
Imaginea aplicaţie cu titlul de cheie de control este dată în imaginea nr. 67. Dumneavoastră vă
revine sarcina de-a încerca să o rezolvaţi. SUCCES!!
Figura nr. 67
65
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţia informatică propusă: formatări asupra celulelor din foaia
de calcul, utilizarea funcţiilor financiare PMT, FV, PV, RATE, NPV, IRR, utilizarea
instrumentului Căutare Rezultat pentru optimizarea contului de rezultate.
Societatea comercială ONIX S.A. doreşte să obţină disponibilităţi băneşti prin emisia de
obligaţiuni. Pe baza figura nr. 68 care reflectă datele problemei să se genereze planul de
amortizare (rambursarea împrumutului).
Elementele teoretice utilizate pentru a putea rezolva cerinţa de mai sus sunt expuse în
continuare. Împrumuturile din emisiunea de obligaţiuni reprezintă contravaloarea obligaţiilor emise
prin subscripţie publică, potrivit legii. Denumite şi credite obligatare (obligatari = deţinători de
obligaţiuni), împrumuturile din emisiunea de obligaţiuni sunt realizate de către societăţile pe acţiuni în
vederea atragerii unor surse pe termen mediu şi lung în scopul finanţării investiţiilor.
Obligaţiunile sunt titluri de valoare negociabile emise, de regulă, de societăţile pe acţiuni,
constituind un împrumut colectiv acordat societăţii de o masă de creditori, pe termen lung.
Referitor la înregistrarea în contabilitate a unui împrumut din emisiunea de obligaţiuni, sunt
relevante următoarele informaţii:
a) obligaţiunile pot fi emise pentru o sumă care nu depăşeşte ¾ din capitalul vărsat existent, conform
ultimului bilanţ aprobat;
b) valoarea nominală unitară a unei obligaţiuni care nu poate fi mai mică de o anumită limită şi în
funcţie de care se calculează dobânda;
c) valoarea de emisiune a obligaţiunilor, adesea inferioară valorii nominale ;
d) dobânda aferentă, ca rată anuală;
e) termenul de rambursare ≥ 1 an;
f) valoarea de rambursare, de regulă mai mică decât cea de emisiune .
66
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
Pentru calculul anuităţii2 se foloseşte formula:
n-r)(1-1
r * C A+
=
unde: A = anuitatea de rambursat, C = valoarea creditului, r = rata dobânzii, n = termenul de
rambursare (ani).
Ca modalităţi de rambursare a împrumutului din emisiunea de obligaţiuni, pot fi:
- tragerea la sorţi (loterie) a) anuitate constantă = rată variabilă + dobândă variabilă;
b) anuitate variabilă = rată constantă + dobândă variabilă.
- la scadenţa globală (finală);
- prin tranşe egale;
- răscumpărare la bursa de valori
Figura nr. 68
Planul de amortizare al împrumutului din emisiunea de obligaţiuni construit pe baza unei
anuităţi variabile este expus în figura nr. 69.
Figura nr. 69
67
2 Anuitate=Rata de rambursat+Dobânda Aferenta
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
68
Comentariu: Conform tabelului din figura nr. 71 cei care optează pentru rate descrescătoare
vor plăti mai putini bani decât in cazul în care vor alege să ramburseze creditul în rate lunare egale.
Să se genereze tabloul de rambursare al unui credit aprobat în condiţiile din figura nr. 70.
Creditele bancare sunt o componentă a capitalurilor permanente, fiind o sursă de finanţare
durabilă pentru întreprindere, termenele de contractare variind între 1 – 5 ani pentru creditele bancare
pe termen mediu şi peste 5 ani pentru creditele bancare pe termen lung.
Un exerciţiu mult mai complex sub aspectul formulelor implicate în calcul îl reprezintă tabloul
de rambursare al unui împrumut din credite bancare pe termen mediu şi lung.
Tabloul de rambursare a fost construit avându-se în vedere un exemplu de asemănător utilizat în
sistemul bancar punându-se în evidenţă cele două modalităţi de rambursare: Rate lunare totale egale,
Rate lunare de credit egale, figura nr. 71.
Dobânda =15%*E46 Sold rămas (datorat la început de an) =E45-D45
Anuitatea =C46+D46 Rata împrumutului =$E$45/$D$37
Formulele de calcul utilizate pentru calcularea elementelor din tabelul anterior sunt
următoarele:
Figura nr. 70
Aplicaţii financiar – contabile rezolvate cu Microsoft Excel XP
69
Figura nr. 71
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
I) În cazul Ratelor lunare totale egale, Rata totală antecalculată a fost determinată cu ajutorul
funcţiei financiare PMT(). Sintaxa acestei funcţii este următoarea:
=PMT(rate; nper; pv; fv; type)
Unde:
Rată - este rata dobânzii pentru împrumut.
Nper - este numărul total de plăţi pentru împrumut.
Pv - este valoarea actualizată sau suma totală pe care o valorează în prezent o serie de plăţi
viitoare, denumită şi capital de bază.
Fv - este valoarea viitoare sau balanţa în numerar la care vreţi să ajungeţi după efectuarea
ultimei plăţi. Dacă Fv este omis, se presupune a fi 0 (zero), ceea ce înseamnă că valoarea
viitoare a împrumutului este 0.
Tip - este numărul 0 (zero) sau 1 şi indică momentul când sunt datorate plăţile.
Convenţie: În utilizarea funcţiilor financiare s-a convenit că ieşirile de numerar sunt
reprezentate prin numere negative, iar încasările, prin numere pozitive.
Utilizarea acestei funcţii în rezolvarea problemei are următoarea formă:
=PMT($C$69/12;$C$70*12;-$C$67)
Observaţie: Trebuie să existe consecvenţă în legătură cu unităţile de timp utilizate pentru
specificarea argumentelor rată şi nper. De exemplu, dacă efectuaţi plăţi lunare la un împrumut atunci
împărţiţi rata anuală dobânzii la 12 luni şi totodată înmulţiţi perioada (exprimată în ani) cu 12 luni.
Celelalte elemente pentru calcularea tabloului de rambursare sunt:
Dobândă =$C$67*($C$69/12)
Credit =C83-E83
Sold ct. împrumut =C67-D83
II) În cazul Ratelor de credit lunare fixă elementele de calcul sunt următoarele:
Dobândă =C67*($C$69/12)
Credit =$C$67/$C$68
Sold ct. Împrumut =C67-H83
Rata totala antecalculată =H83+I83
70
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Pe baza datelor aferente creditului şi a tabelului de rambursare a creditului să se răspundă la
următoarele cerinţe:
a) Să se reprezinte grafic, în cazul Variantei I de rambursare a creditului, ponderea
dobânzii lunare în rata totală lunară de plată aferentă împrumutului, evoluţia descrescătoare a
dobânzii şi crescătoare a ratelor de credit, pentru cei 3 ani.
Pentru rezolvarea acestei cerinţe se pleacă de la tabloul de rambursare al creditului şi se
selectează iniţial seriile de date ce reprezintă ”Dobândă lunară” (E83:E118) şi ”Rata totala
antecalculată” (C83:C118). După efectuarea acestor selecţii se acţionează butonul Expert Diagramă,
aflat pe bara de instrumente standard. Activarea acestui buton declanşează generarea
reprezentării grafice pe parcursul a patru paşi.
Pasul 1 Se alege tipul de grafic linear (Linie). O previzualizare se poate realiza apăsând butonul
”Apăsaţi şi ţineţi apăsat pt. vizualizare eşantion”.
Pasul 2 Prin apăsarea butonului Următorul se trece la pasul 2 unde se va introduce încă o serie
de date ”Dobândă lunară”. Dublarea acestei serii este necesară pentru transformarea seriei liniare nou
introduse într-o serie sectorială (de structură, a dobânzii totale). Adăugarea serie se face cu ajutorul
fişei Serie, prin apăsarea butonului Adăugare şi specificarea adresei seriei de date (E83:E118) în
caseta Valoare.
Etichetele reprezintă categoriile axei absciselor şi se pot specifica furnizând adresa acestora în
caseta ”Etichetele axei categoriei (X)” (A83:A118)
Pasul 3 şi 4 Generarea reprezentării grafice continuă cu specificarea titlului graficului şi al
axelor, stabilirea legendelor şi amplasarea graficului.
După ce reprezentare (cu trei serii de date) a fost generată se selectează din cadrul graficului
seria 3 şi se schimbă tipul reprezentării liniare într-unul sectorial (Meniul Diagramă, opţiunea Tip
Diagramă – Structură radială). Graficul construit celor enunţate mai sus se poate observa în figura
nr. 72.
71
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 72
b) Să se calculeze valoarea vărsământului anual către banca prin luarea in
considerare a mai multor rate ale dobânzii si mai multe perioade de rambursare.
Reprezentaţi grafic vărsămintele anuale pentru ratele dobânzii aferente anului 5. ?
Rezolvarea acestei cerinţe presupune apelarea la tabela de ipoteze cu două variabile descrise la
pagina 56. Figura nr. 73, reprezintă soluţia autorului, fiind totodată o cheie de control pentru studenţi în
încercarea acestora de-a rezolva singuri problema propusă.
72
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 73
Pentru crearea reprezentării grafice cerute la acest punct se va proceda identic cu ceea ce s-a
precizat la cerinţa anterioră (seriile de date sunt: E130:K130, E135:K130) însă singura diferenţă o
reprezintă faptul că din cauză că cele două serii de date conţin valori necomparabile ca ordin de
mărime, seria E130: K130 va fi reprezentată grafic pe o a doua axă Y, figura nr. 74. Imaginea finală a
graficului este evidenţiată în figura nr. 75.
73
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 74
Figura nr. 75
74
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
c) Care ar trebui sa fie perioada de rambursare a creditului astfel încât plăţile lunare să
fie de 2.500.000 lei.
Un alt instrument de asistare a deciziei, din suita ”Ce se întâmplă dacă?”, de care dispune
procesorul de tabele Excel îl reprezintă Căutarea rezultatului (eng. Goal Seek). Acesta se utilizează
atunci când se cunoaşte rezultatul dorit al unei singure formule, dar nu se cunosc valorile de intrare ce
conduc la acel rezultat. În timpul căutării rezultatului, Microsoft Excel modifică valoarea dintr-o celulă
specificată până când o formulă ce depinde de această celulă revine la rezultatul dorit.
Rezolvarea cerinţei de mai sus este ilustrată în figura nr. 76.
Figura nr. 76
Paşii parcurşi pentru determinarea valorii căutate sunt:
1) selectarea celulei care urmează a fi optimizată (C150). Aceasta celulă trebuie să conţină o formulă în
care să existe referinţe de celule care prin modificarea valorii conţinute în ele să conducă la rezultatul
dorit.
2) Apelarea opţiunii Instrumente/Căutare Rezultat… Figura nr. 77 ilustrează modalitatea de
completare a casetei Căutare Rezultat pentru a rezolva cerinţa de la punctul c)
Figura nr. 77
75
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
? O firmă vinde televizoare în rate. Să se determine, totalul de plată şi să se
construiască tabele amortizării în cazul în care un cumpărător achiziţionează un
televizor în valoare de 12.000.000lei. Se va considera că a fost plătit un avans de
4.000.000 lei şi că televizorul va fi plătit în 12 rate lunare cu o dobândă de 40%. Să
se construiască foia de calcul cu ajutorul căreia să se calculeze automat aceste
valori. La proiectarea foii de calcul se va avea în vedere că dobânda se poate
modifica în timp.
Foia de calcul unde este proiectată aplicaţia este oferită în figura nr. 78 cu titlul de cheie de
control, relaţiile de calcul şi celelalte elemente ale foii revenindu-vă sarcina de-a le deduce.
Figura nr. 78
76
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
În continuare vor fi prezentate în cadrul unor aplicaţii şi alte funcţii financiare (FV, PV, NPV,
IRR) care să vă ajute în procesul de învăţare al procesorului de tabele Microsoft Excel.
A) O persoană vrea să investească bani pentru un proiect care va fi realizat peste un an.
Persoana depune 1000 € într-un cont de economii cu o dobândă de 6% pe an, iar ulterior
persoana va depune câte 100 € la începutul fiecărei luni pe toată durata depozitului. Care este
valoarea depozitului la finalul contractului?
Rezolvarea acestei probleme este ilustrată în figura nr. 79.
Figura nr. 79
Funcţia utilizată pentru a afla valoarea finală a contractului este:
=FV(B2/12;B4*12;-B3;-B1;1)
Această funcţie calculează valoarea finală pentru o serie de încasări/ plăti egale, făcute într-un
număr de perioade, cu o anumită dobândă.
B) O persoană îşi poate permite să plătească 200€ pe lună în următorii 10 ani. Dobânda curentă
pe piaţă la creditele ipotecare este 10,1%. Cât de mare este împrumutul pe care şi-l poate permite
persoana?
Rezolvarea acestei probleme este ilustrată în figura nr. 80.
Figura nr. 80
77
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Funcţia utilizată pentru a afla valoarea împrumutului este:
=PV(B11/12;B10*12;-B9)
Această funcţie calculează valoarea prezentă pentru o serie de încasări/ plăti egale, făcute într-
un număr de perioade, cu o anumită dobândă.
Un întreprinzător care face o investiţie într-un obiectiv economic câştigă un venit
net anual de 5.000.000, timp de 10 ani cât este durata de funcţionare a obiectivului.
Care este valoarea prezentă a acestui flux de venituri în momentul investiţiei, la o
valoare a ratei dobânzii de 32%.
?
C) Pentru o investiţie trebuie plătiţi 100000€ timp de un an. În următorii trei ani se obţin venituri
anuale de 42000€, 50000€, 69000€. Dobânda anuală este de 10%. Să se calculeze valoarea netă
actualizată a investiţiei.
Rezolvarea acestei probleme este ilustrată în figura nr. 81.
Figura nr. 81
Funcţia utilizată pentru a afla valoarea actualizată a fluxului de venituri/cheltuieli este:
=NPV(B18;-B16;B19;B20;B21)
Funcţia NPV calculează valoarea netă actualizată a unei investiţii prin utilizarea unei rate de
actualizare (rata de scont) şi a unei serii de plăţi (valori negative) şi încasări (valori pozitive) viitoare.
Sintaxa funcţiei este: NPV(rata;valoare1;valoare2; ...)
unde:
valoare1, valoare2, ... trebuie repartizate la aceleaşi intervale de timp şi trebuie efectuate la
sfârşitul fiecărei perioade.
78
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
rata - rata de actualizare pe parcursul unei perioade. Această rată poate fi reprezentată de rata
inflaţiei sau rata dobânzii unei investiţii concurente.
NPV este asemănătoare cu funcţia PV (valoarea actualizată). Diferenţa semnificativă dintre PV
şi NPV este că:
PV acceptă fluxuri de numerar care au loc fie la începutul, fie la sfârşitul perioadei;
fluxurile de numerar din PV trebuie să fie constante pe toată perioada investiţiei.
D) O persoană doreşte să înceapă o afacere, iar pentru aceasta are nevoie de 100000€. Veniturile
estimate în primii cinci ani sunt: 42000€, 50000€, 69000€, 65000€, 70000€. Care este rata internă
de rentabilitate a investiţiei.
Rezolvarea acestei probleme este ilustrată în figura nr. 82.
Figura nr. 82
Funcţia utilizată pentru a afla valoarea ratei interne de rentabilitate este:
=IRR(B26:B31)
IRR este înrudită cu funcţia NPV, funcţia pentru calculul valorii nete actualizate astfel încât rata
rentabilităţii calculată de IRR este rata dobânzii corespunzătoare unei valori nete actualizate egale cu
zero.
E) Un proiect necesită un volum de investiţii de 45.000.000 lei. Durata de execuţie a proiectului
este de doi ani, iar durata de viaţă economică este de 7 ani. Fluxul tranşelor anuale pentru
investiţii, cheltuieli de exploatare şi încasări sunt cele din figura nr. 83.
Să se calculeze:
Venitul net actualizat pentru o rată de actualizare de 20%
79
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Rata internă de rentabilitate a proiectului.
Rezolvarea acestei probleme este ilustrată în figura nr. 83.
Figura nr. 83
Relaţiile de calcul pentru construirea tabelului din imaginea anterioara sunt:
Fluxul de numerar (B96) =B95-B94-B93
Venitul net actualizat =NPV(B90;B96:J96)
Rata interna de rentabilitate =IRR(B96:J96)
80
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Reprezentarea datelor sub formă de diagrame, construirea şi modificarea diagramelor, tipuri
de diagrame (liniare, histograme, sectoriale, grafice cu valori necomparabile, bursiere, mixte,
diagrama Gant)
Grafice liniare: Sunt reprezentări grafice sub forma de linii frânte sau curbe ce semnifică evoluţia
şi tendinţa fenomenelor studiate.
Pe baza datelor din tabelul aferent figurii nr. 84 să se construiască graficul ce arată
evoluţia vânzărilor de produse pe trimestre, figura nr. 85.
Figura nr. 84
81
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 85
Paşii parcurşi pentru a obţine graficul din figura nr. 85 sunt următorii:
Pasul 1. Selecţia datelor reprezentate grafic, în exemplul nostru B4:E6
Pasul 2. Apelarea componentei Excel – Expert Diagramă.
Pasul 3. Din caseta de dialog ce apare ca urmare a declanşării procesului de realizare a unui grafic se
alege:
Punctul 3.1 Tipul graficului, figura nr. 86
Punctul 3.2 Modul de interpretare a
seriilor de date (pe rânduri sau coloane) şi
eventual denumirea seriilor sau adăugarea unora
noi, figura nr. 87
Punctul 3.3 Stabilirea titlului diagramei,
titlurilor pentru cele două axe, poziţia legendei,
etichetelor de date (nume serie, nume categorie,
valoare), conform figurii nr. 88
Punctul 3.4 Amplasarea diagramei (foaie
noua sau obiect în foaia curentă), conform figurii
nr. 89
Figura nr. 86
82
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 87
Figura nr. 88
83
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 89
Grafice de tip histogramă (diagramă cu coloane): Sunt diagrame ce ilustrează grafic
modificările suferite în acelaşi interval de timp de diferitele date de reprezentat, precum şi
comparaţia între anumite elemente analizate.
Utilizând tabelul din figura nr. 84 să se construiască graficul de tip diagramă cu coloane
care să evidenţieze evoluţia vânzărilor de produse.
Ca element de control vă este oferit în figura nr. 90 imaginea graficului cerut mai sus.
Figura nr. 90
84
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Grafice de tip histogramă (diagramă cu bare) sunt asemănătoare cu tipul de grafic precedent,
cu singura diferenţă că cele două axe au o orientare inversă.
Utilizând tabelul din figura nr. 84 se construieşte graficul de tip diagramă cu bare care
evidenţiază evoluţia vânzărilor de produse.
Figura nr. 91
Observaţie: Procesul de construire a graficului este în toate cazurile asemănător şi conform
procedurii descrise la graficul de tip liniar. Modificările ulterioare asupra graficului pentru a arăta ca
cel din imagine anterioară sunt evidenţiate în figura nr. 92.
85
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 92 Inserarea unei imagini ca fundal pentru grafic
Grafice sectoriale: Sunt diagrame de structură care semnifică părţi ale elementului analizat,
raportate la întreg.
Observaţie: pentru construirea unui grafic sectorial se va alege o singură serie de date deoarece
este vorba de disocierea unui element în părţile sale componente.
Utilizând tabelul din figura nr. 93 să se construiască graficul sectorial ce evidenţiază
structura salariilor brute la nivelul fiecărui compartiment.
Figura nr. 93
86
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
A) Obţinerea datelor necesare cu ajutorul tabelei de ipoteze cu o singură variabilă, figura nr. 94.
Figura nr. 94
B) Obţinerea datelor necesare cu ajutorul opţiunii Date/Subtotaluri…, după ce în prealabil
tabelul a fost ordonat după câmpul Compartiment, figura nr. 95.
Figura nr. 95
87
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Utilizând tabelul din figura nr. 93 să se construiască un grafic în două axe y (grafic cu
valori necomparabile) care să evidenţieze suma salariilor brute şi numărul de angajaţi aferenţi
fiecărui compartiment.
Un astfel de grafic a mai fost utilizat pe parcursul prezentei lucrări astfel încât vă revine sarcina
de-a rezolva cerinţa de mai sus. Datele necesare graficului sunt obţinute cu ajutorul tabelei de ipoteze
cu o singură variabilă şi în care vor exista două funcţii generatoare de rezultate (prima SUM pentru
suma salariilor brute şi a doua COUNT pentru numărul angajaţilor aferenţi fiecărui compartiment).
Imaginea graficului ca element de control vă este oferit în figura nr. 96.
Figura nr. 96
Grafice bursiere: Sunt potrivite pentru a ilustra variaţii in timp a datelor
Să se reprezinte printr-un grafic de amplitudine, cursul valutar al dolarului la casele de
schimb valutar, conform datelor din figura nr. 97. Acest tip de grafic (Stoc) necesită existenţa a trei
serii de date: valoare minimă – valoarea minimă a cotaţiei dolarului, valoare medie – valoarea medie
a cotaţiei dolarului, valoare maximă - valoarea minimă a cotaţiei dolarului, obţinute prin aplicarea
88
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
funcţiilor statistice MIN(), MAX(), AVERAGE(). Reprezentarea grafică a celor trei serii de date este
redată în figura nr. 98.
Figura nr. 97
Figura nr. 98
Utilizând tabelul din figura nr. 94 să se construiască graficul de tipul: diagrama de
amplitudine+grafic liniar ce evidenţiază salariul minim, maxim şi mediu pe fiecare compartiment
funcţional. În acelaşi plan al diagramei, salariul mediu la nivelul fiecărui compartiment va fi o
transformat într-o reprezentare liniară.
Sursa de date pentru acest grafic va fi un tabel de ipoteze cu o singură variabilă şi trei formule
generatoare de rezultate (construite cu ajutorul funcţiilor de tip baze de date: DMAX, DMIN,
DAVERAGE).
89
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Observaţie: pentru generarea acestui tip de grafic, după crearea celui de tip stoc (cu trei serii de
date), seria de date ce reprezintă salariul mediu la nivelul fiecărui compartiment se va selecta şi se va
alege pentru aceasta o reprezentare liniară.
Graficul creat este evidenţiat în figura nr. 99
Figura nr. 99
Transformarea serie ce reprezintă salariul mediu într-o reprezentare liniară este arătată în figura
nr. 100.
Figura nr. 100
90
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Grafice mixte:
Să se reprezinte cu ajutorul unui grafic mixt – evoluţia vânzărilor de produse în fiecare
trimestru dar şi structura valorică a vânzărilor totale de produse aferent fiecărui trimestru.
Sursa de date pentru acest grafic este reprezentată de tabelul din figura nr. 101.
Figura nr. 101
Reprezentarea selectivă a datelor
Să se reprezinte cu ajutorul unui grafic selectiv structura vânzărilor aferente produsului 1
în trimestrul 1 şi trimestrul 4.
Crearea acestui tip de grafic (structură inelară) seamănă cu crearea unui de tip structură numai
că la final se mai fac câteva corecţii. În acest sens pentru datele care nu se doresc a fi incluse în grafic
91
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
se va selecta reprezentarea grafică şi din meniul rapid (clik dreapta) se va alege opţiunea Formatare
serie de date, unde atât suprafaţa cât şi bordura vor fi dezactivate prin alegerea opţiunii Nici una. În
final segmentul de grafic selectat va fi incolor iar în plus se va şterge şi eticheta de date. Rezultatul
acestor selecţii îl reprezintă graficul din figura nr. 102.
Figura nr. 102
Să se creeze un grafic de tip mixt în care să fie evidenţiată evoluţia vânzărilor de
produse în cele 4 trimestre dar şi structura valorică a totalului vânzărilor de
produse. Datele pentru care se va construi graficul sunt ilustrate în tabelul din
figura nr. 103.
?
Figura nr. 103
92
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Să se construiască următoarele tipuri de grafice (diagramă cu suprafaţă, grafic de
tip radar, grafic de tip XY - prin puncte) pe baza unor tabele concepute de
dumneavoastră. ?
Prognoza şi arătarea tendinţei cu ajutorul diagramelor
Liniile de tendinţă sunt utilizate pentru a afişa grafic tendinţele existent în date şi pentru a
analiza probleme de prognoză. Astfel de analize sunt intitulate de asemenea şi analize de regresie.
Utilizând analize de regresie, se poate extinde o linie de tendinţă într-o diagramă peste datele actuale
pentru a prognoza valori viitoare. În exemplul următor se poate observa previzionarea vânzărilor de
echipamente (calculatoare) până la finalul anului cunoscând datele pentru primele 5 luni.
Plecând de la tabelul din figura nr. 104 s-a reuşit cu ajutorul diagramei să se traseze o linie de
tendinţă a vânzărilor de echipamente pentru tot anul utilizând pentru prognoză tipul de tendinţă/
regresie logaritmică, figura nr. 105. O linie de tendinţă logaritmică este cea mai potrivită curbă care se
utilizează atunci când rata de modificări în date creşte sau descreşte rapid, apoi se menţine constantă. O
linie de tendinţă logaritmică poate utiliza valori negative şi/sau pozitive.
Figura nr. 104
93
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 105
Grafice particularizate
Există posibilitatea în Excel de-a utiliza pe lângă tipurile standard şi diagrame particularizate.
Procedura de creare a unei astfel de diagrame este cea amintită în acest capitol cu singura diferenţă că
se va alege tipul de diagramă dorită din fişa Tipuri particularizate.
Pe baza datelor din figura nr. 106 să se construiască o diagramă particularizată, figura nr. 107,
care evidenţiază vânzările de produse aferente lunilor: ian, feb, martie din anii: 1996, 1997, 1998, 1999.
Figura nr. 106
Figura nr. 107
94
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Tehnici profesioniste de construire a diagramelor Tehnicile profesioniste sunt acele elemente care diferenţiază impactul diagramelor asupra publicului. În această prezentare sunt expuse câteva elemente fundamentale ale formatărilor ce pot fi aplicate unei diagrame, tabelul nr. 3. Exemplificarea elementelor din tabelul nr. 3 este realizată într-o diagramă de tip Gant ilustrată în figura nr. 108.
Elementul diagramei Format sugerat
Dimensiunea titlului diagramei Titlu standard, 20 puncte Stilul titlului diagramei Arial, Bold Dimensiunea titlului axei Y Titlu standard axă Y, 14 puncte Stilul titlului axei Y Bold Marcajele axei Y În interior (Inside) sau nici unul (None) Stilul axei Y Linie groasă Dimensiunea titlului axei X Titlu standard axă X, 14 puncte Stilul titlului axei X Bold Marcajele axei X Nici unul (None) Stilul axei X Linie groasă Chenarul seriei de date Nici unul Seria de date (fără accentuare) Nuanţă deschisă Seria de date (cu accentuare) Nuanţă închisă Linii de tendinţă La fel ca serie de date pe care se bazează linia de tendinţăStilul liniilor de tendinţă Linie subţire punctată Chenarul legendei Nici unul Stilul de font al legendei Bold Dimensiunea fontului legendei Legendă standard, 10 puncte Amplasarea legendei Dreapta sus sau dreapta
Tabelul nr. 3 Formatări speciale aplicate diagramelor
95
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 108
96
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţia informatică propusă: extragerea datelor cu ajutorul
filtrului automat şi avansat, funcţii de tip bază de date, utilizarea funcţiilor pentru
prelucrarea şirurilor de caractere, funcţii de tip dată calendaristică.
Filtrarea este un mod rapid şi uşor de a găsi şi de a lucra cu un subset de date dintr-o listă. O
listă filtrată afişează numai rândurile care îndeplinesc criteriul specificat pentru o coloană. Microsoft
Excel furnizează două comenzi pentru filtrarea listelor, meniul Date, opţiunea Filtrare:
• Filtrare automată, care include filtrare după selecţie, pentru criterii simple
• Filtrare complexă, pentru criterii mai complexe
Spre deosebire de sortare, filtrarea nu rearanjează o listă. Filtrarea ascunde temporar rânduri pe
care nu le doriţi să le afişaţi. Când Excel filtrează rânduri, este posibil să editaţi, să formataţi, să
reprezentaţi grafic şi să imprimaţi subsetul din listă fără a-l rearanja sau a-l muta.
A) Filtrarea automată a datelor dintr-o listă
Plecând de la lista angajaţilor unei companii (se oferă informaţii despre: Nume, Prenume,
CNP, Data Naşterii, Vârsta) figura nr. 109, să se filtreze automat lista pentru a răspunde
următoarelor cerinţe:
1) Să se afişeze indivizii cu numele de ”Campeanu”;
2) Sa se extragă toţi indivizi de sex masculin şi să se determine numărul lor;
3) Să se extragă toţi indivizi de sex masculin al căror nume începe cu "C";
4) Să se extragă toţi indivizi de sex masculin al căror prenume începe cu "D" sau "G";
5) Să se afişeze toţi indivizi de sex masculin cu vârsta intre 18 si 50 de ani;
6) Sa se afişeze toţi indivizi al căror nume începe cu următoarea secvenţa de caractere "AC*".
97
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 109
In lista de mai sus elementele coloanelor: Data Naşterii şi Vârsta au fost calculate. Formulele
utilizate pentru afişarea valorilor în cele două coloane sunt:
Data naşterii:
=DATE(VALUE(CONCATENATE("19";MID(C2;2;2)));VALUE(MID(C2;4;2));VALUE(MID(
C2;6;2)))
Observaţie: Remarcaţi utilizarea funcţiilor pentru manipularea şirurilor de caractere care au
ca semnificaţie: CONCATENATE() - uneşte mai multe şiruri text într-unul singur, MID() - întoarce
un anumit număr de caractere dintr-un şir de text, începând din poziţia specificată, pe baza numărului
de caractere specificat; dar şi a funcţiei pentru date calendaristice DATE() - întoarce numărul seriei
secvenţiale care reprezintă o anumită dată.
98
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Vârstă:
=INT(DAYS360(D2;TODAY())/360)
Observaţie: Funcţiei Days360() este utilizată pentru a afla numărul de zile între două date
calendaristice.
1) Pentru a afişa indivizii cu numele specificat în cerinţă se va selecta din lista derulantă
corespunzătoare coloanei ”Nume” valoarea cerută ”Campeanu”, figura nr. 110.
Figura nr. 110
2) Pentru determinarea indivizilor de sex masculin se utilizează următorul criteriul de filtrare aplicat pe
coloana ”Cnp”, figura nr. 111.
Figura nr. 111
99
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Numărul indivizilor de sex masculin din lista ce rezultă în urma aplicării filtrului de mai sus se
determină cu formula:
=COUNTA(B2:B1954) – funcţie utilizată pentru a numără celulele care nu sunt goale
(conţinutul celulelor poate fi de tip text, număr, dată calendaristică)
3) Pentru a extrage toţi indivizi de sex masculin al căror nume începe cu "C" asupra filtrului de la
punctul doi se va aplica filtrul din figura nr. 112. Construirea unui alt filtru care se bazează pe
rezultatele unui filtru anterior se numeşte filtrare în cascadă.
Figura nr. 112
4) Pentru a extrage toţi indivizi de sex masculin al căror prenume începe cu "D" sau "G" asupra filtrului
folosit la punctul doi se va aplica în continuare următorul filtru, figura nr. 113.
Figura nr. 113
5) Pentru a afişa toţi indivizi de sex masculin cu vârsta între 18 şi 50 de ani, în afara unui filtru de
selecţie simplu aplicat asupra coloanei ”Cnp” prin care se extrag indivizii de sex masculin (codul
începe cu 1) asupra coloanei ”Vârsta” va fi aplicat următorul filtru definit, figura nr. 114.
100
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 114
6) Pentru a afişa toţi indivizi al căror nume începe cu următoarea secvenţă "AC*" trebuie construit
următorul filtru definit, figura nr. 115.
Figura nr. 115
Se dă următorul tabel, figura nr. 116, ce reprezintă evidenţa clienţilor societăţii
XY SRL privind livrările de mărfuri către aceştia. ?
Cu ajutorul filtrului automat să se răspundă la următoarele cerinţe:
1. Să se afişeze lista clienţilor din Constanta;
2. Să se afişeze lista clienţilor din Bucureşti si din Braşov;
101
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
3. Să se afişeze lista clienţilor din Bucureşti care nu au plătit facturile;
4. Să se afişeze facturile neîncasate a căror valoare depăşeşte cuantumul de 1mil.;
5. Să se afişeze facturile emise în cursul anului trecut;
6. Să se afişeze facturile a căror valoare este cuprinsă între 10mil. si 20mil.;
7. Să se reprezinte grafic structura valorică a facturilor neplătite de "Client1".
Figura nr. 116
B) Filtrarea complexă a datelor dintr-o listă
În cazul în care datele care trebuie obţinute dintr-o listă sunt specificate prin criterii complexe
care includ, sau nu, şi funcţii de tip bază de date atunci este necesar să se utilizeze comanda Filtrarea
complexă (Date/ Filtrare/ Filtrare Complexă). Comanda Filtrare complexă poate filtra o listă pe loc ca
şi comanda Filtrare automată, cu deosebirea că aceasta nu afişează liste verticale pentru fiecare antet
de coloană din care să se poată extragă valoarea căutată. În loc de acestea se tastează criteriile (condiţii
specificate pentru a limita înregistrările incluse în setul rezultat a unei filtrări) după care se realizează
filtrarea cu ajutorul criteriilor construite separate.
Asupra unui tabel, figura nr. 117, ce reprezintă vânzările unui magazin de aparatură electro-
casnică pe parcursul unei zile se va exemplifica tipurile de criterii complexe care se pot utiliza în
procesorul de tabele Microsoft Excel.
102
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 117
a) condiţii multiple într-o singură coloană: Să se afişeze rândurile care conţin fie înregistrarea Banu
Gabriela fie Florea Silvia în coloana Nume vânzător.
Câmpul de criterii, şi caseta de dialog în care se specifică toţi parametrii acestui filtru avansat
sunt ilustrate în figura nr. 118.
Figura nr. 118
Notă: Sunt afişate acele rânduri care conţin în coloana precizată fie Banu Gabriela fie Florea
Silvia. Evaluarea se face având în vedere operatorul logic SAU.
b) o condiţie în două sau mai multe coloane: Să se afişeze rândurile care conţin următorul criteriu:
Nume vânzător: Dumitru Cristian, Nume produs: Televizor Panasonic.
103
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Câmpul de criterii şi caseta de dialog în care se specifică toţi parametrii acestui filtru avansat
sunt ilustrate în figura nr. 119.
Figura nr. 119
c) o condiţie specificată într-o coloană sau alta: Este utilizată pentru a găsi datele care satisfac fie o
condiţie într-o coloană, fie o condiţie în altă coloană.
Să se afişeze toate rândurile care conţin fie înregistrarea Banu Elena în coloana Nume
vânzător, fie Televizor ALIEN în coloana Nume produs fie o valoare >8000000 în coloana Valoare,
figura nr. 120.
Figura nr. 120
104
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
d) condiţii create ca rezultat al unei formule: Aveţi posibilitatea de a utiliza o valoare calculată, care
este rezultatul unei formule, drept criteriu. La folosirea unei formule pentru crearea unui criteriu, nu
trebuie utilizată o etichetă de coloană pentru o etichetă de criteriu.
Să se afişeze rândurile care au în coloana C o valoare mai mare decât media domeniului
C2:C12.
Criteriul utilizat, caseta de dialog Filtrare Complexă şi rezultatul filtrării sunt prezentate în
figura nr. 121.
Figura nr. 121
Observaţii:
Formula utilizată pentru o condiţie trebuie să utilizeze o referinţă relativă pentru a face referire
la eticheta coloanei (de exemplu, Valoare) sau câmpul corespondent din prima înregistrare (în
exemplul nostru C2). Toate celelalte referinţe din formulă trebuie să fie referinţe absolute şi
formula trebuie să evalueze TRUE sau FALSE.
105
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Când evaluează datele, Microsoft Excel nu face deosebirea între literele mari şi literele mici şi
în concluzie este bine ca etichetele criteriilor să se completeze prin copierea lor din tabelul
supus filtrării.
? Se dă următorul tabel ce reprezintă un centralizator al facturilor emise către diverse
firme de S.C. ELPIS S.R.L, figura nr. 122.
Figura nr. 122
Să se răspundă la următoarele cerinţe:
1) Să se afişeze lista clienţilor care nu au plătit factura şi au depăşit termenul de scadentă;
2) Să se afişeze facturile emise în luna curentă;
3) Să se afişeze lista clienţilor care au majorări de întârziere cuprinse între 20% si 80% din
valoare (câmpul de rezultate);
4) Să se afişeze lista clienţilor care au majorări de întârziere ce depăşesc 1/3 din media valorilor;
5) Să se afişeze toate facturile emise in a doua jumătate anului 2003 (câmp de rezultate: Nr.
factura, Data facturii, Suma datorata).
106
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţia informatică propusă: întocmirea situaţiilor financiare
(bilanţ, cont de profit şi pierdere) plecând de la balanţă, urmărirea cu ajutorul graficelor a
indicatorilor de rezultate din bilanţul contabil al unei organizaţii
1. Utilizarea balanţei de verificare pentru obţinerea informaţiilor financiar-contabile de sinteză
Balanţa de verificare, figura nr. 123 reprezintă finalitatea centralizării în dinamică a operaţiilor
contabile, înainte ca acestea să fie sintetizate în Bilanţul Contabil şi în Contul de profit şi pierdere.
Fig. nr. 123
107
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Construirea balanţei de verificare cu ajutorul procesorului de tabele Excel se supune
următoarelor cerinţe:
A. calcularea rubricilor de total sume debitoare/ creditoare şi a soldurilor finale debitoare/
creditoare, pentru fiecare cont în parte.
Total sume curente debitoare sau creditoare se calculează prin formule simple ce adiţionează pe
linie pentru fiecare cont în parte, conţinutul celulelor ce conţin soldurile iniţiale debitoare sau
creditoare şi total rulaje debitoare sau creditoare, conform formulei de calcul: =F6+D6
Soldurile finale se calculează în urma unui test realizat asupra conţinutului rubricilor de total
sume debitoare sau creditoare. Astfel, dacă total sume debitoare sunt mai mari decât total sume
creditoare, rezultă că este vorba de un cont de activ care are sold debitor. Testul făcut invers generează
solduri creditoare pentru conturile de pasiv. Formulele de calcul sunt următoarele:
a) pentru total sume debitoare: =IF($H6>$I6;($H6-$I6);0)
b) pentru total sume creditoare: =IF($H6>$I6;0;($I6-$H6))
B. calcularea automată a totalurilor (pe verticală), pentru fiecare clasă de conturi cu ajutorul
opţiunii de subtotalizare, figura nr. 124 şi a totalurilor generale.
Totalurile pe clase de conturi se pot calcula, după efectuarea unor mici corecţii aplicate balanţei
de verificare, cu ajutorul opţiunii Subtotal meniul Data. Aceste mici corecţii aplicate balanţei constau
în: a) scrierea tuturor conturilor din 3 cifre şi cu o zecimală (ex. pentru contul 1012 Capital subscris
vărsat s-a utilizat scrierea 101,2); b) ordonarea întregii balanţe de verificare după rubrica cont, conform
figurii nr. 125; c) extragerea caracterului corespunzător clasei de conturi pentru folosirea acestei rubrici
drept criteriu de totalizare în componenta de Subtotal…. Extragerea unui caracter (primul de fapt) este
realizată cu ajutorul următoarei formule: =MID(TEXT(B6;"0");1;1)
Totalul general pentru fiecare rubrică a balanţei se poate calcula, fie însumând totalurile
intermediare pentru fiecare clasă în parte, fie în mod direct pentru tot domeniul avut în vedere. Totalul
general corespunzător Solului final debitor (coloana J) este redat în formula următoare:
=SUM(J6:J101)
108
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 124
Figura nr. 125
109
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
2. Modelarea bilanţului contabil plecând de la balanţa de verificare
Procedeul de generare a bilanţului contabil, cu ajutorul procesorului de tabele Excel, presupune
preluarea soldurilor iniţiale şi finale din balanţa de verificare, într-un post bilanţier, printr-o formulă de
agregare cu referinţe 3D (în ipoteza în care balanţa de verificare şi bilanţul contabil se găsesc în foi de
calcul diferite) conform următoarei formule:
Bilanţ!Post bilanţier_de Activ/Pasiv ”cont X”= Balanţa!Sold_iniţial/final (D/C) ”Cont A” +/-
Balanţa!Sold_iniţial/final (D/C) ”Cont B” +/- Balanţa!Sold_iniţial/final (D/C) ”Cont X” /1000
Semnificaţia acestei formule de agregare a soldurilor iniţiale sau finale în posturi bilanţiere este
următoarea: un post bilanţier oarecare ”X” reprezintă o grupare predefinită ( aditivă sau substractivă)
de solduri iniţiale sau finale din balanţa de verificare. Rezultatul acestei grupări este divizat la 1.000,
datorită faptului că soldurile balanţei sunt în lei, iar posturile bilanţiere se raportează în mii lei.
Preluarea practică a tuturor conturilor din balanţă într-un post bilanţier se realizează cu ajutorul
următoarei formule, figura nr. 126 aplicată pentru calcularea soldului la începutul anului (coloana C din
Bilanţ):
Figura nr. 126
Observaţie: Utilizarea Funcţiei de consultare şi informare VLOOKUP() cu parametrul FALSE
are următoarea raţiune: în momentul căutării unui anumit cont în balanţa de verificare dacă acesta nu
110
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
exista formula implicită (fără acest parametru) întorcea ca rezultat valoarea celui mai apropiat cont de
cel căutat iar rezultatul era incorect calculat. Apelarea la parametrul FALSE realizează o căutarea
exactă iar dacă contul nu se găseşte rezultatul întors este N/A (Not Avaible). Pentru evitarea acestui din
urmă obstacol se mai remarcă folosire în interiorul formulei şi a funcţiei ISERROR() care are menirea
de-a verifica rezultatul funcţiei anterioare şi de-a afişa valoarea 0 dacă nu a fost găsită nici o valoare.
Utilizarea funcţiei VlookUp() se justifică prin faptul că oferă mai multă flexibilitate aplicaţiei
decât în cazul simplei agregări a conturilor componente a unui post bilanţier prin intermediul
referinţelor 3D, iar eventualele modificări ale legislaţiei în sensul adăugării sau suprimării de conturi nu
îngreunează actualizarea aplicaţiei.
Pentru o mai bună şi uşoară asimilare a formulei din figura nr. 126 aceasta este explicată cu
ajutorul pseudocodului:
Dacă1 Rezult evaluării funcţiei VlookP(aplicată la ct. A) = False
Atunci Afişează 0
Altfel afisează valoarea returnată de funcţia VlookUp (aplicată la ct. A)
Sfârşit Dacă1
Dacă2 Rezult evaluării funcţiei VlookP(aplicată la ct. B) = False
Atunci Afişează 0
Altfel afisează valoarea returnată de funcţia VlookUp (aplicată la ct. B)
Sfârşit Dacă2
.
. Dacăn Rezult evaluării funcţiei VlookP(aplicată la ct. N) = False
Atunci Afişează 0
Altfel afisează valoarea returnată de funcţia VlookUp (aplicată la ct. N)
Sfârşit Dacăn
Totalizarea unor posturi bilanţiere într-o categorie sintetică (ex. Active Imobilizate Total) se
face cu ajutorul unei formele ce adună valorile corespunzătoare din domeniul indicat, figura nr. 127.
111
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 127
Microsoft Excel are posibilitatea de a ataşa unei foi electronice de calcul un ”plan
desfăşurător”, plan ce poate fi afişat ”în extenso” cu toate detaliile sau ”restrâns” când detaliile sunt
ascunse, figura nr. 128. Rolul unui plan desfăşurător este acela de a reuşi să facă o sintetizare a
valorilor existente în foia electronică de calcul, reuşind astfel ca informaţia să fie mai uşor de citit şi
comparat (permite afişarea sau ascunderea până la opt nivele de detaliu din rânduri sau coloane unei foi
de calcul).
Aşa cum se poate observa din imaginea alăturată crearea automată a unui plan desfăşurător este
însoţită de alegerea din meniul Date a opţiunii Grupare şi schiţare – Elaborare automată schiţă.
112
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 128
3. Modelarea contului de profit şi pierdere plecând de la balanţa contabilă
Formule utilizate pentru construirea contului de profit şi pierdere sunt de două categorii: 1)
formule generice de agregare a sumelor aferente conturilor de venituri şi cheltuieli provenite din
balanţa de verificare şi 2) formule de agregare simplă (ex. posturile de venituri totale, cheltuieli totale)
sau condiţionată (pentru conturile de rezultate).
Prima categorie de formule îmbracă următoarea formă:
113
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Post Cont Rezultate ”X” (Venit/Cheltuiali)=Balantă!Total_Sume_Cumulate_Cont_A de
clasă_600/700 (D/C) +/- Balantă!Total_Sume_Cumulate_Cont_B de clasă_600/700 (D/C)+/-
…… Balantă!Total_Sume_Cumulate_ContX de clasă_600/700 (D/C)
Ilustrarea acestui tip de formulă aplicat foii de calcul ”cont de profit şi pierdere” este arătată în
figura nr. 129.
Figura nr. 129
A doua categorie de formule sintetizează în trepte posturile contului de profit sau pierdere. La
acest nivel există atât formule de agregare simplă a diverselor elemente de veniturile sau cheltuielile
grupate din punct de vedere funcţional figura nr. 130, dar şi formule de agregare condiţională ce permit
compararea maselor de venituri sau cheltuieli pentru calcularea rezultatului (profit/pierdere) ce rezultă
din aceste comparaţii, figura nr. 131. O particularitate a acestor formule de calcul o reprezintă faptul că
rezultatele aflate pe o treaptă superioară iau în consideraţie rezultatele calculate cu ajutorul formulelor
de agregare simplă (ex. în figura nr. 131 rezultatul din exploatare ia în calcul atât veniturile din
exploatare cât şi cheltuielile de exploatare).
114
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 130
Figura nr. 131
Observaţie: Din cauza îndeplinirii condiţiei din formulă rezultatul a fost calculat pentru rubrica
PROFIT. În cazul opus în care există posibilitatea de a avea PIERDERE formula care calculează
acest rezultat este: =IF(D36>=D15;D36-D15;0).
115
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
4. Modelarea soldurilor intermediare de gestiune şi a capacităţii de autofinanţare
Datele din contul de profit şi pierdere pot fi sintetizate în tablouri analitice cu un grad mare de
regrupare a informaţiei financiare. Aceste tablouri se concretizează în aplicaţia de faţă în elaborarea
tabloului soldurilor intermediare de gestiune (SIG) şi a capacităţii de autofinanţare (CAF), figura nr.
132.
Figura nr. 132
116
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Tabloul soldurilor intermediare de gestiune (care integrează şi CAF) se compune: pe de-o parte,
din posturi ”elementare” extrase din contul de profit sau pierdere, iar pe de altă parte din agregatele
financiare care rafinează rezultatul exerciţiului, şi anume: marja comercială (venituri din vânzarea
mărfurilor – costul mărfii vândute); valoarea adăugată (Marja Comerciala + Producţia exerciţiului -
SUM (cheltuieli cu materiile prime până la cheltuieli cu serviciile executate de terţi); excedentul brut
din exploatare (Valoarea adăugata + Subvenţii din exploatare - Cheltuielile cu impozite si taxe -
Cheltuielile cu personalul); rezultatul exploatării (Excedentul Brut din Exploatare + Alte venituri din
exploatare + Venituri din provizioane din exploatare - Alte cheltuieli din exploatare - Cheltuieli cu
amortizarea si provizioanele); rezultatul curent (Venituri excepţionale + Venituri excepţionale din
provizioane - Cheltuieli Excepţionale - Cheltuieli excepţionale privind amortizările si provizioanele),
rezultatul exerciţiului (Rezultatul Curent + Rezultatul excepţional - Cheltuieli privind impozitul pe
profit) şi capacitatea de autofinanţare (Venituri din vânzarea mărfurilor – Cheltuieli cu mărfurile
vândute + Producţia vândută + Producţia stocată + Venituri din producţia imobilizată - SUM (cheltuieli
cu materiile prime până la cheltuieli cu serviciile executate de terţi) + Subvenţii din exploatare -
Cheltuieli cu impozitele si taxe - Cheltuieli cu personalul + Alte Venituri din exploatare - Alte
cheltuieli din exploatare + Venituri financiare - Cheltuielile financiare + Veniturile excepţionale -
Cheltuielile excepţionale - Cheltuielile privind impozitul pe profit.
? De calculat capacitatea de autofinanţare conform relaţiei de calcul: agregatul
EBE la care se adună sau se scad posturile de venituri şi cheltuieli financiare şi
excepţionale.
Un exerciţiu mai complex care înglobează o serie de elemente expuse anterior se referă la
evidenţierea rezultatelor unei societăţi cu ajutorul reprezentărilor grafice.
Punctul de pornire pentru rezolvarea acestei cerinţe îl constituie un extras din situaţiile
financiare ale unei societăţi pe patru ani (1999 – 2003) conform figurii nr. 133. În cadrul tabelului care
arată date semnificative din bilanţul contabil, contul de profit şi pierdere şi date cu caracter informativ
au fost făcute simple agregări pentru determinarea Activului total (active imobilizate + active
circulante + conturi de regularizare şi asimilate), Pasivului total (capitaluri proprii + provizioane
117
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
pentru riscuri şi cheltuieli + conturi de regularizare şi asimilate) şi Profitului/Pierderii nete a
exerciţiului.
Figura nr. 133
118
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Pe baza datelor din tabelul aferent figurii nr. 133 au fost construite următoarele reprezentări
grafice:
Figura nr. 134
Figura nr. 135
119
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 136
Figura nr. 137
120
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 138
Figura nr. 139
121
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Reluând problematica instrumentelor de asistare a deciziei plecând de la contul de
rezultate previzional să se rezolve cu ajutorul componentei Căutare Rezultat
următoarea cerinţe:
?
1. Cu cât trebuie să crească cifra de afaceri astfel încât rezultatul să fie pozitiv? Se utilizează
pentru rezolvarea acestei cerinţe tabela de ipoteze cu o singură variabilă.
2. Ilustrarea variaţiei rezultatului in funcţie de variaţia procentului de creştere a cifrei de
afaceri şi a cheltuielilor. Se utilizează pentru rezolvarea acestei cerinţe tabela de ipoteze cu
două variabile
Imaginea foii electronice de calcul în care autorul a rezolvat cerinţele, dată sub formă de cheie
de control este evidenţiată în figura nr. 140.
Figura nr. 140
122
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Elemente exemplificate în aplicaţie: obţinerea informaţiilor de sinteză cu ajutorul
rapoartelor PivotTablele şi PivotChart.
Având în vedere figura nr. 141 ce reprezintă situaţia de plata pe primul trimestru a anului 2004
a angajaţilor unei societăţi comerciale care dispune de mai multe filiale, să se obţină cu ajutorul
rapoartelor de tip PivotTable şi PivotChart următoarele situaţii:
Figura nr. 141
123
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
1. Care este suma salariului trimestrial încasat de fiecare angajat al societăţii?
2. Care este suma salariului trimestrial încasat de fiecare angajat al societăţii dintr-o anumită
filială?
3. Care este clasamentul salariaţilor după suma salariului trimestrial încasat?
4. Care este topul primilor 5 salariaţi ţinând cont de beneficiile obţinute?
5. Care este centralizarea salariilor pe parcursul lunilor din primul trimestru?
6. Care sunt veniturile unei persoane in decursul primului trimestru?
7. Care este fondul de salarii acordat salariaţilor în fiecare lună a primului trimestru la oricare
dintre filiale (raport PivotTable şi PivotChart)?
Înainte de-a începe rezolvarea cerinţelor trebuie explicate noţiunile importante referitoare la
acest gen de rapoarte (PivotTable şi PivotChart).
Tabela pivot, ca instrument de asistare a deciziei reprezintă o facilitate prin care datele dintr-o
foaie de calcul pot fi permutate pentru a se pune în evidenţă noi informaţii. Ea permite crearea unui
tablou de sinteză în care rubricile unui tabel sau unei baze de date pot fi permutate pe linie sau pe
coloană, asupra datelor operându-se agregări şi calcule sub formă de totaluri, medii, min, max. etc.
Aceste operaţii sunt alese dintr-o listă de funcţii predefinite şi pot fi operate calcule diferite
asupra aceloraşi elemente supuse sintetizării.
Tabela pivot este un instrument care permite o asociere foarte elastică a unor
câmpuri într-o manieră interactivă, fapt ce duce la regruparea datelor şi prezentarea
acestora într-un mod sintetic.
Procedura principală de construirea a unui raport PivotTable va fi explicată prin rezolvarea
cerinţei numărul 1 urmând ca la rezolvarea celorlalte să fie afişate doar elementele ce particularizează
raportul respectiv.
1) Pentru a determina suma salariului încasat de fiecare angajat al societăţii în primul trimestru al
anului 2004 cu ajutorul instrumentului PivotTable se procedează în modul următor:
A) Apelarea opţiunii Raport PivotTable şi PivotChart din cadrul meniului Date. Pentru a uşura
procesul de construire a raportului este recomandat ca indicatorul de celulă activă să fie plasat în
interiorul tabelului care constituie sursa de date.
124
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
B) Declanşarea procesului de construire a raportului (Pasul 1) prin specificarea datelor de analizat şi
tipul raportului obţinut, figura nr. 142.
Figura nr. 142
C) Specificarea adresei sursei de date, dacă s-a omis remarca făcută la punctul A, figura nr. 143.
Figura nr. 143
D) Specificarea locului unde va fi construit raportul, figura nr. 144.
Figura nr. 144
125
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Lucrul cel mai important în definirea unui raport PivotTable îl reprezintă aspectul sau structura
acestuia. Aspectul poate fi definit în cadrul pasul 3 al procesului de construire prin apelarea butonului
de comandă Aspect…, figura nr. 145.
Figura nr. 145
Observaţie: În funcţie de cerinţa de rezolvat rubricile tabelului (sursei de date) identificate prin
butoanele plasate în partea dreaptă a figurii nr. 144 (ex: Marca, Filială, Salariu) se plasează în unul din
câmpurile raportului PivotTable.
Tipuri de câmpuri existente în raportul PivotTable:
Câmp rând: ajută la poziţionarea elementelor câmpului (rubricii) pe linie;
Câmp coloană: ajută la poziţionarea elementele câmpului (rubricii) pe coloană
Câmp pagină: permit filtrarea întregului raport PivotTable pentru a afişa datele pentru un singur
element sau pentru toate elementele.
Câmp de date:câmpurile de date furnizează valorile datelor de rezumat. De obicei, câmpurile de date
conţin numere, care sunt combinate cu funcţia de rezumare Sum, dar câmpurile de date pot conţine şi
texte, caz în care raportul PivotTable utilizează funcţia de rezumare Count.
Imaginea de ansamblu a raportului de tip PivotTable rezolvat pentru a răspunde cerinţei
numărul unu este ilustrată în figura nr. 146.
126
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 146
În imaginea anterioară sunt identificate următoarele elemente care definesc aspectul unui raport PivotTable:
Suprafeţe de fixare
Figura nr. 147
Listă de câmpuri - fereastră care listează toate câmpurile disponibile din sursa de date pentru a le utiliza în raportul PivotTable
127
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 148
2) Care este suma salariului trimestrial încasat de fiecare angajat al societăţii dintr-o anumită
filială?
Pentru a rezolva această cerinţă se parcurg aceeaşi paşi ca la cerinţa anterioară dar la definirea
aspectului raportului PivotTable se va ţine cont de figura nr. 149.
Figura nr. 149
Imaginea finală a raportului construit în care se observă salariul trimestrial încasat de angajaţii
filialei Bucureşti corespunde imaginii nr. 150.
128
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 150
3) Care este clasamentul salariaţilor după suma salariului trimestrial încasat?
Aspectul raportului care rezolvă această cerinţă este identic cu cel anterior, iar pentru imaginea
finala a raportului în cadrul câmpului de tip pagină s-a lăsat opţiunea implicită Toate.
Un top al salariilor încasate de salariaţii organizaţiei a fost realizat prin alegerea opţiunii
Ordonare descrescătoare după Suma salariului net corespunzător figurii nr. 151. Caseta de dialog a
fost activată cu ajutorul opţiunii Sortare şi top 10 … corespunzător figurii nr. 152.
Figura nr. 151
129
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 152
4) Care este topul primilor 5 salariaţi ţinând cont de beneficiile obţinute?
Pentru rezolvarea acestei cerinţe se parcurg aceeaşi paşi ca la cerinţa precedentă iar singura
modificare o reprezintă alegerea opţiunii de afişare a primilor 5 salariaţi corespunzător salariului primit,
cu ajutorul opţiunii din figura nr. 153.
Figura nr. 153
5) Care este centralizarea salariilor pe parcursul lunilor din primul trimestru?
Aspectul raportului PivotTable în acest caz este corespunzător celui din figura nr. 154.
130
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 154
Rezultatul construirii raportului conform specificaţiilor din figura nr. 155 este următorul:
Figura nr. 155
Observaţie: Pentru obţinerea raportului din figura nr. 155 s-a procedat la gruparea datelor ce
corespund rubricii Data încasării după unitatea de timp – lună, conform figurii nr. 156
131
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 156
6) Care sunt veniturile unei persoane in decursul primului trimestru?
Aspectul raportului PivotTable în acest caz este corespunzător celui din figura nr. 157.
Figura nr. 157
Rezultatul construirii raportului conform figurii anterioare este următorul:
132
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 158
Pentru a obţine raportul din figura nr. 158, raportul implicit a fost supus AutoFormatării
corespunzător figurii nr. 159.
Figura nr. 159
133
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Să se construiască următoarele rapoarte de tip PivotTable:
Cât reprezintă venitul unei persoane în totalul general al fondului de salarii al
societăţii? ? Care este salariul mediu în cadrul unui compartiment dintr-o anumită filială pe
total trimestru şi intervalele de timp din cadrul trimestrului?
7) Care este fondul de salarii mediu acordat salariaţilor în fiecare lună a primului trimestru la
oricare dintre filiale (raport PivotTable şi PivotChart)?
Aspectul raportului PivotTable în acest caz este corespunzător celui din figura nr. 160.
Figura nr. 160
Rezultatul construirii raportului conform figurii anterioare este ilustrat în figura nr. 161.
Figura nr. 161
134
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Pentru a obţine fondul de salariu mediu s-a optat pentru următoarea selecţie în caseta de dialog
Câmp PivotTable, figura nr. 162, activată cu dublu clic pe butonul Sumă Salariu Net din figura nr.
157.
Figura nr. 162
Pentru obţinerea raportului PivotChart din figura nr. 164 se acţionează asupra butonului Expert
diagramă situat pe bara cu instrumente PivotTable.
Figura nr. 163
135
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 164
136
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Subiectul nr. 1
Plecând de la tabelul din figura nr. 165 să se rezolve următoarele cerinţe:
Figura nr. 165
1). Folosind funcţiile aritmetice corespunzătoare să se calculeze valoarea totală a producţiei de petrol în
celula D9 şi rădăcina pătrată din valoarea totală a producţie în celula D11.
2). Să se creeze următoarele tipuri de grafice:
• Grafic de tip histogramă cu bare orientate vertical pentru producţia de petrol (mil. barili)
prelucrată în perioada 1990 – 1995;
• Grafic de tip pie (de structură) pentru valoarea producţiei de petrol prelucrată in perioada
1990 – 1995.
3). a) Care este valoarea producţiei de petrol în anii in care preţul a depăşit 18$/baril.
b) Determinaţi numărul valorilor din domeniul B3:B8 care îndeplinesc următoarea cerinţă:
“Producţia > 9000”.
4). Introduceţi un comentariu ce are ca text: “sursa British Petroleum” celulei care conţine titlul
tabelului. Conţinutul celulei de comentariu trebuie formatată astfel:
• backgroundul sa fie de culoare portocaliu;
• textul sa fie de culoare albastru şi scris îngroşat.
137
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Subiectul nr. 2
Fiind dat următorul tabel care reprezintă situaţia şcolară a studenţilor din grupa 201 la disciplina
Birotică, să se rezolve cerinţele:
Figura nr. 166
1). Construiţi tabelul din figura nr. 165 şi totodată respectă următoarele cerinţe:
• antetul scris cu litere îngroşate, aliniat la centrul celulelor, fundalul de culoare portocaliu;
• pentru col. Nr. Crt. realizaţi o serie liniara cu baza 1 si pasul 1 (ex. 1, 2, 3, …., n);
• in domeniul A12:G10 creaţi o serie exponenţiala cu baza 3, puterea 2 si ultimul termen 250;
• mediile calculate să fie afişate sub următoarea formă 9,40.
2). Determinaţi media aritmetică şi armonica a fiecărui student pe baza notelor obţinute, folosind
funcţiile statistice corespunzătoare.
3). Aplicaţi domeniului c3:e7 o formatare condiţională astfel incit notele sub 5 sa fie afişate cu font
roşu.
4.) Cu ajutorul funcţiilor statistice determinaţi nota acordată cel mai frecvent, numărul de apariţii al
acesteia şi numărul de studenţi care au media aritmetica peste 7.
Subiectul nr. 3
Fiind tabelul ce reprezintă valoarea producţiei de oţel in perioada 1990 – 1995 să se rezolve
următoarele cerinţele:
138
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Figura nr. 167
1). Ce reprezintă un domeniu de celule. Exemplu de domeniu de celule continue şi discontinue si
modul de realizare al selecţiei acestora.
2). Calculaţi: Cantitatea Totala (celula C8) produsă în perioada 90 – 95, valoarea procentuală a
producţiei realizate in fiecare an corespunzător formulei: Cantitatea produsăi / Cantitatea Totala
3). Aplicaţi o formatare condiţională datelor (B2: B7) din tabelul de mai sus astfel încât acestea să fie
afişate îngroşat şi cu roşu pentru cantitatea produsă care depăşeşte 5.000 tone.
4). Reprezentaţi grafic sub forma de diagramă de structura (pie) datele ce reprezintă cantitatea de oţel
produsă.
5). Folosind funcţiile statistice determinaţi in domeniu C2:C7:
a) numărul de celule ce îndeplinesc condiţia: “cantitatea produsa > 4000”
b) valoarea mediană a acestui domeniu.
Subiectul nr. 4
Fiind dat următorul tabel ce reprezintă statul de plată pentru salariaţii firmei AS să se rezolve
cerinţele următoare:
Figura nr. 168
139
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
1). Pentru coloana “Funcţia” introduceţi o validare de tip listă astfel incit să nu accepte decât valorile:
Ing., Ec., Maistru, Muncitor
2). Determinaţi Impozitul datorat conform următorului criteriu:
daca salariul brut este intre 1.8 mil. si 2.5 mil. lei
atunci coeficientul de impozit este de 10%
altfel coeficientul de impozit este de 14%
sfârşit dacă
3) Determinaţi cu ajutorul formulelor de calcul: Salariul net (salariul brut – impozit), Total salariu brut,
Total salariu net, Total impozit
4). Determinaţi salariului mediu pentru indivizii cu funcţia de ING.
5). Construiţi un grafic care să evidenţieze evoluţia impozitului aferent fiecărui salariat.
Subiectul nr. 5
O persoana fizica doreşte achiziţionarea unui mijloc fix in valoare de 32.000.000 lei. Pentru a
obţine banii recurge la un împrumut bancar în următoarele condiţii:
• Durata împrumutului 5 ani;
• Credit acordat de banca 67% din valoarea mijlocului fix;
• Rata anuala a dobânzii 51%.
Să se rezolve următoarele cerinţe:
1) Determinaţi valoarea împrumutului ?
2) Determinaţi valoarea vărsământului lunar şi anual ?
3) Determinaţi costul împrumutului ?
4) Determinaţi dobânda totală suportată de persoana fizica ?
5) Utilizând instrumentul de optimizare Căutare Rezultat (Goal Seek) determinaţi care va fi durata
împrumutului astfel încât clientul sa plătească anual suma de 12.000.000 lei.
140
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Subiectul nr. 6
Se dă următorul tabel ce reprezintă centralizatorul vânzările de produse ale firmei AS
Figura nr. 169
1). Realizaţi un tabel în foia de calcul care să fie identic cu cel din figura nr. 168 şi în plus să conţină şi
următoarele cerinţe:
• elementele coloanei ”Localitate” vor fi validări de tip lista ce conţin (TGV, B, BZ);
• valorile coloanei ”Cod client” vor fi o serie numerica cu baza 1 şi pasul 1;
• valorile din coloana ”Valoare” să fie cuprinse între (1 mil. şi 9 mil.);
• elementele din antetul tabelului sunt scrise conform următoarelor caracteristici: font Arial,
mărime 11, stil italic, centrat, culoare verde.
2). Determinaţi suma facturilor pentru clienţii din Localitatea TGV
3). Calculaţi Majorări conform următorului algoritm:
Daca Plătit = “Nu”
Atunci
Daca (Data curenta – Data facturării) >=30 zile
Atunci majorare = valoare * 0.1
Altfel majorare = valoare * 0.05
Sfârşit Dacă
Altfel 0
Sfârşit Dacă
4). Folosind funcţii de tip bază de date determinaţi:
• numărul clienţilor care şi-au plătit factura;
141
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
• care valoarea totală a facturilor ce au majorări >50000 si <150000.
5). Realizaţi un filtru pentru a afişa clienţii din Bucureşti care nu si-au plătit factura.
Subiectul nr. 7
Se dă următorul tabel ce reprezintă structura elementelor (venituri şi cheltuieli) pentru realizarea
unei comenzi:
Figura nr. 170
1). Să se calculeze: VENITURI, TOTAL CHELTUIELI, PROFIT cu ajutorul unor formule de calcul
simple.
2). Formataţi tabelul creat de voi astfel incit sa fie identic cu cel din figura nr. 169 şi în plus respectă
următoarele cerinţe: elementele primei coloane sunt scrise cu Arial, mărime 11, îngroşat, elementele
coloanei a doua sunt reprezentate sub forma următorului format de afişare – 2.000.000,00 lei.
3). Copiaţi tabelul intr-o altă locaţie (A15) a foii de calcul. Optimizaţi “PROFITUL” din tabelul obţinut
prin copiere astfel încât să se ajungă la suma de 3.000.000 lei, schimbând numărul de bucăţi fabricate.
4). Realizaţi două scenarii numite: Varianta Optimista si Varianta Pesimista in care sunt luate în
calcul următoarele elemente: NrBucFab, PretVz, Venituri, TotalCheltuieli, Profit. Valorile pentru cele
două scenarii sunt:
Varianta Pesimista Varianta Optimista
NrBucFab = 4500 NrBucFab = 5100
PretVz =1900 PretVz = 2000
5). Să se genereze un raport in care sa se poată compara elementele curente ale tabelului cu cele ale
celor două scenarii în vederea îmbunătăţirii activităţii firmei.
142
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Subiectul nr. 8
Se dă următorul tabel ce reprezintă evidenţa studenţilor de la Colegiul Economic:
Figura nr. 171
Să se rezolve următoarele cerinţe:
1). Formataţi tabelul astfel:
a) antetul scris îngroşat cu font alb pe un fundal negru;
b) inseraţi un chenar tabelului format dintr-o linie punctata;
c) validaţi elementele coloanei Specializarea astfel încât să accepte doar valorile (BI, BA, MU,
CGF)
2). Determinaţi Restul de plata (cu ajutorul formulelor matriciale) şi aplicaţi o formatare condiţională
coloanei Nume astfel incit cei care nu şi-au achitat taxa (Rest de plata>0) să apară scrişi cu font roşu pe
un fundal albastru.
3). Determinaţi cu ajutorul funcţiilor pentru baze de date (DSUM) restul de plata pentru studenţii
specializării BI
4). Să se afişeze cu ajutorul filtrului automat studenţii specializărilor : BI, MU, BA.
5). Să se afişeze cu ajutorul filtrării complexe studenţii specializărilor BI, CGF care au un rest de plată
mai mare de 4 mil. lei.
143
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Subiectul nr. 9
Se dă următorul tabel:
Figura nr. 172
Să se răspundă cerinţelor:
1). Formataţi tabelul astfel:
a) antetul scris îngroşat şi cu font alb pe un fundal negru;
b) inseraţi un chenar tabelului format dintr-o linie îngroşată;
c) valorile coloanei Funcţie să accepte doar elemente listei: Director, Contabil, Distribuitor,
Secretara.
2) Cu ajutorul funcţiei de consultare VLOOKUP să se aducă din tabelul nomenclator următoarele
elemente: Nume, Prenume, Firma, Adresa, Departament, realizând o consultare cu test de existenta a
cheii pentru coloana Marca în tabelul stat.
3). Sa se calculeze rubricile tabelului (C.A.S, SOMAJ, SALARIU NET) cu ajutorul formulelor de
calcul simple.
4). Să se afişeze cu ajutorul filtrului automat înregistrările din Bucureşti si Braşov.
5). Să se afişeze cu ajutorul filtrului complex rândurile ce corespund următorului criteriu:
• firmele Tasmina, Agif, Riva;
• funcţia persoanelor selectate este Director.
144
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Subiectul nr. 10
Se dă următorul tabel:
Figura nr. 173
1). Să se formateze tabelul de mai sus astfel:
a) Antetul : scris îngroşat cu font alb pe un fond negru
b) Zona cu date : fond gri
c) inseraţi un chenar exterior tabelului format dintr-o linie dublă
2). Cu ajutorul funcţiei IF sa se determine calificativul obţinut de fiecare student ţinând cont de
următoarele restricţii:
a) Daca studenţii au mai puţin de 5 credite se obţine calificativul “Exmatriculat”;
b) Daca studenţii au intre 5 si 8 credite se obţine calificativul “Repeta anul”;
c) Daca studenţii au peste 8 credite se obţine calificativul “Trecut in anul următor”.
3). Cu ajutorul funcţiei pentru baze de date sa se determine:
a) media notelor aferente studenţilor din anul 1;
b) numărul de studenţi care repetă anul.
4). Realizaţi o filtrare automata pentru tabel astfel incit sa rămână afişaţi studenţii anului 1 cu media
<5.
5). Să se realizeze un grafic liniar care evidenţiază rezultatele obţinute (media) de studenţi.
145
Aplicaţii financiar-contabile rezolvate cu Microsoft Excel XP
Subiectul nr. 11
Se dă următorul tabel:
Figura nr. 174
1) Să se calculeze elementele: Total Cheltuieli (Total cheltuieli=Cost bunuri vândute + Cheltuieli
vânzare + Cheltuieli diverse) şi Venit Net (Încasări - Total cheltuieli)
2) Să se creeze o diagramă care arată structura Cheltuielilor (Cost bunuri vândute, Cheltuieli vânzare,
Cheltuieli diverse) aferente Trim. 3.
3) Să se aplice formatările corespunzătoare domeniului B3:E6 pentru a afişa valorile însoţite de
simbolul monetar ”lei” şi 2 zecimale (ex; 256.800,00 lei)
4) Să se introducă la dreapta tabelului o coloană intitulată ”Total An” unde se va totaliza cu ajutorul
funcţie Sum valorile din cele patru trimestre.
5.) Să se realizeze cu ajutorul instrumentului căutare rezultat o modelare a elementului Venit Net (pe
total an) pentru a ajunge la o valoare de 360.000 lei modificând cheltuielile diverse.
146
BIBLIOGRAFIE
1. Ionescu Bogdan, Pana Adrian, Ionescu Iuliana, Mareş Veronica, Mirela Oancea, Mihailov Gabriel, Stanciu Andrei, Manea Alexandru, Boldeanu Dana
Office 2000/Xp Aplicaţii şi teste rezolvate, Editura InfoMega, 2003
2. Ionescu Bogdan, Pana Adrian, Ionescu Iuliana, Stanciu Andrei, Mirela Oancea, Gavrilă Alexandru
Aplicaţii informatice pentru birotică şi comunicaţii, Editura InfoMega, 2001
3. Ionescu Iuliana – Modele informatice privind gestiunea trezoreriei întreprinderii, Editura InfoMega, 2002 4. Dorin Zaharie, Felicia Albulescu, Irina Bojan, Veronica Ivancenco, Corina Vasilescu
Sisteme informatice pentru asistarea deciziei, Editura Dual Tech, 2001
5. http://www.microsoft.com/Romania/Office/Office_XP/Articol_Excel 6. Maria Niculescu – Diagnostic global strategic, Editura Economică, 1997. 7. *** Documentaţie Microsoft Excel Xp.
Top Related