Realizarea si utilizarea produselor software - Serviciul comercializare titei din cadrul S.N.P Petrom S.A.

Academia de Studii Economice

Faculatatea de Cibernetica, Statistica si Informatica Economica

Realizarea si utilizarea produselor software

Serviciul comercializare titei

din cadrul S.N.P Petrom S.A.

Activitatea departamentului de comercializare titei si gaze din cadrul S.N.P. Petrom S.A. este axata in principal pe vanzarea de titei, gaze si alte produse derivate catre diversi clienti. Servicul comercializare titei din cadrul acestui department este cel care se ocupa cu inregistrarea documentelor emise diversilor clienti.

Tipurile de produse comercializate sunt: titei condensate si titei C Selectionat, codificare c resprectiv t. Produsele pot fi achizitionate de firme din afara sistemului (terti) dar si de firme din sistemului(sist), in acest caz nemaicalculandu-se impozitul aferent acestei tranzactii. In situatia in care produsul este comercializat in afara sistemului este emisa o factura, in caz contrar un aviz.

Se dau urmatoarele date referitare la servicul de comercializare titei din cadrul S.N.P Petrom S.A. pentru anul 2005:

Fig. 1 - foaie de calcul Date

Se mai cunoaste ca pentru anul 2005 impozitul a fost stabilit la 4 euro/tona, tva-ul este de 19% iar cursul de schimb se determina trimestrial, reprezentand cursul de schimb stabilit de BNR in ultima zi a trimestrului anterior.

Plata facturii se realizeaza in doua transe: la 25 de zile cand se plateste TVA-ul si impozitul aferente tranzactiei si la 60 de zile (urmatoarele 35 de zile) cand urmeaza sa se incaseze venitul din vanzare (diferenta dintre valoarea vanzarii, TVA si impozit).

Se cere:

o foaie de calcul "Date" pentru inregistrarea informatiilor generale referitoare la tranzactie (fig. 1);

o foaie de calcul "Facturi" in care, pe langa beneficiar, se mai inregistreaza data emiterii facturii (data curenta), scadenta la 25 de zile, scadenta la 60 zile, cantitatea achizitionata, reducere acordata, pretul etc (vezi ANEXA fig 1);

o foaie de calcul "Impozit" in care se determina impozitul aferent tranzactiei care trebuie platit (vezi ANEXA fig 2);

sa se introduca validari pentru celulele care contin date calendaristice, data trebuie sa fie o data valida din anul 2005, si pentru tipul documentului, acesta trebuie sa fie factura sau aviz;

sa se introduca urmatoarele coloane in foia de calcul "Facturi":

valoare(RON)=pret*cantitate;

TVA(RON)=0.19*valoare(RON);

Valoarea totala (RON)= valoare+TVA

sa se defineasca o functie care sa intoarca reducerea acordata unui beneficiar avandu-se in vedere cantitatea achizitionata astfel:

reducere=

se sa introduca urmatoarele coloane in foia de calcul "Impozit":

impozit(RON)=impozit(euro)*curs(euro);

valoare Impozit=impozit*cantitate;

valoare la scadenta de 25 zile=TVA+valoare Impozit;

valoare la scadenta de 60 zile=valoare totala-valoare la scadenta de 25 zile.

sa se reprezinte grafic cantitatile de titei vandute diversilor beneficiari pe anul 2005;

sa se reprezinte grafic evolutia vanzarilor pe cele 4 trimestre ale anului 2005;

sa se realizeze un scenariu pentru a evidentia importanta modificarii TVA-ului, impozitului, cantitatii vandute si pretului mediu asupra venitului rezultat din comercializarea prodului titei C selectionat. Cursul de schimb se considera 1 euro=3,55 lei;

sa se determine impozitul in cazul in care valoarea impozitului este 850, cursul de schimb 3,4 iar cantitatea achizitionata 30 tone;

societatea produce doua tiputi de titeti: condenstat (c) si C selectionat (t). Modificand parametrii pret mediu, cantitate si impozit, sa se optimizeze modelul prin maximizarea venitului din vanzari al societatii;

sa se dispuna informatiile referitoare la inregistrarea facturilor intr-o tabela pivot pentru o mai buna vizualizare a acestora.

A.    realizarea si formatarea foilor de calcul

Au fost create 3 foie de calcul pentru a retine datele problemei:

"Date" - pentru datele generale ale problemei: luna, an , trimestru, semestru, beneficiar, prestator, sucursala, tipul produsului, tipul documentului, seria documentului ( fig 1);

"Facturi" - date referitoare la tipul documentului: beneficiar, produs, data emiterii, data scadentei, cantitate, pret, valoarea, TVA, reducere, valoare totala (vezi ANEXA fig 1);

"Impozite" - pentru calcularea impozitului aferent fiecarei tranzactii: beneficiar, tipul documentului, tipul produsului, impozit, curs, valoarea impozitului, suma care trebuie platita dupa 25 zile (TVA+impozit), venitul din vanzare (vezi ANEXA fig 2).

B.    formatare de celule

Pentru a referii celulele din foile de calcul s-au folosit referinte absolute si relative, etichete si referinte 3-D.

exemplu de referinte absolute si relative

in foaia de calcul "Facturi", valoarea totala (N7) se calculeaza ca suma intre tva(M7) si valoarea vanzarii(pret*cantitate)(L7) din care se scade reducerea acordata(O7): N7=M7+L7-O7;

in foia de calcul "Impozit", impozitul in lei se calculeaza ca produs intrecursul de schimb si impozitul in euro: L7=J7*K7;

exemplu de folosire a etichetelor:

in foia de calcul "Scenariu" (fig 3), celulelor le-au fost associate etichete cu ajutorul optiunii Insert->Name->Define.

exemplu de referinte 3-D:

in foaia de calcul "Impozit", pentru a calcula valoarea impozitului ca produs intre impozitul in lei si cantitate, cantitatea afland-se in foia de calcul "Facturi": M7=Facturi!J7*L7.

Pentru a restictiona valorile care pot fi introduce intr-o foaie de calcul s-a folosit optiunea Validation din meniul Data. Tipurile de restictii folosite in proiect sunt:

restictie de tip Data

in foaia de calcul "Facturi" se verifica continutul coloanei Data doc care trebuie sa fie o data calendaristica din intervalul 01.01.2005 - 31.12.2005;

restrictie de tip Text Length

in foaia de calcul "Date" se stabileste ca lungimea campului tip document sa aiba intre 4 si 8 caractere, in caz contrat afisandu-se un mesaj de eroare corespunzator.

C.    functii si formule de calcul

O formula reprezinta o ecuatie cu ajutorul careia se opereaza cu datele dintr-o foie de calcul. Formulele incep intotdeauna cu semnul "=". In cadrul foilor de calcul s-au utilizat formule ce contin referinte precum si formule ce contin functii.

Formule ce contin referinte

in foia de calcul "Facturi", continutul coloanei valoarea(RON) (L7) se calculeaza ca produs intre cantitate si pret: = J7*K7;

in foaia de calcul "Facturi", valoarea totala (N7) se calculeaza ca suma intre tva(M7) si valoarea vanzarii(pret*cantitate)(L7) din care se scade reducerea acordata(O7): =M7+L7-O7;

in foaia de calcul "Impozit", pentru a calcula valoarea impozitului (M7) ca produs intre impozitul in lei si cantitate, cantitatea afland-se in foia de calcul "Facturi": =Facturi!J7*L7.

Formule ce contin functii:

functii de tip data calendaristica:

in foaia de calcul "Facturi", pentru campul Data doc, a fost utilizata functia calendaristia Today() care intoarce data curenta.

functii logice:

in foaia de calcul "Date", pentru coloana SIST/TERTI, s-a folosit functia logica IF ca completeaza celula cu S(pentru aviz) sau T(pentru factura) conform campului din coloana tip document: =IF(L7='factura';'T';'S');

in foia de calcul "Facturi", celula din coloana "Beneficiar" se completeaza automat cu informatia din celula corespunzatoare a coloanei Beneficiar din foia de calcul "Date" pentru care numarul curent corespunde cu cel din foia "Date": =IF(Date!A16=A7;Date!F16;' ');

in foia de calcul "Facturi", coloana TVA se completeaza cu 19% din valoarea vanzarii(pret*cant) in cazul in care tipul documentului este factura sau cu 0 in caz contrat: =IF(E7='factura';L7*19/100;0);

functii matematice:

in foia de calcul "Facturi" s-a folosit functia matematica SUM(lista) pentru a insuma elementele pe coloana: valoare(RON), TVA, valoarea totala;

tot in foia de calcul "Facturi" este folosita si functia matematica SUMIF() pentru a determina cantitatile si pretul mediu pentru cele doua tiputi de titei; pentru determinarea cantitatii vandute de titei condensat: =SUMIF(C7:C20;'t';J7:J20), iar pentru pretul mediu: =SUMIF(C7:C20;'t';K7:K20)/8;

functii statistice:

s-au folosit functiile statistice MIN(lista), MAX(lista) si AVERAGE(lista) pentru a determina cantitatea si valoarea incasarilor minime, maxime si medii;

functii definite de utilizator

s-a definit o functie numita reducere() care primeste ca parametru cantitatea achizitionata de beneficiar. In functie de cantitatea achizitionata beneficiarului I se acorda o reducere conform cerintei. Functia a fost implementata cu ajutorul editorului Visual Basic, fiind definite astfel:

Function reducere(cant)

If (cant <= 500 And cant > 100) Then

reducere = 0.05

Else

If (cant > 500 And cant <= 1000) Then

reducere = 0.1

Else

If (cant > 1000 And cant <= 3000) Then

reducere = 0.15

Else

If (cant > 3000 And cant <= 6000) Then

reducere = 0.2

Else

If (cant > 6000) Then

reducere = 0.25

Else

reducere = 0

End If

End If

End If

End If

End If

End Function

D.    grafice

Sa se reprezinte grafic cantitatile de titei vandute diversilor beneficiari pe anul 2005. Pentru a realiza aceasta cerinte s-a folosit un grafic placinta(pie). Datele utilizate se regasesc in urmatorul tabel:

S-a folosit faculitatea SUBTOTAL din meniul Data pentru a determina cantitatile vandute pe fiecare beneficiar sip e total.

Graficul rezultat:

Interpretare: din graficul 'Structura cantitatilor vandute pe beneficiari' se oberva ca cea mai mare cantitate de produs, 9.371,0 tone, respectiv 71% a fost achizitionata de BRAZI. Pe pozitia a doua se situeaza STEAUA ROMANA care a achizitionat 17% din cantitatea totala, echivalentul a 2.279 tone. ARPECHIM a achizitionat 1.502,6 tone in timp ce ROBALIM doar 124 tone, respectiv 1% din cantitatea totala.

Sa se reprezinte grafic evolutia vanzarilor pe cele 4 trimestre ale anului 2005. Pentru a realiza aceasta cerinte s-a folosit un grafic sub forma de bare verticale. Datele utilizate se regasesc in urmatorul tabel:

Graficul rezultat:

Interpretare: din graficul 'Evolutia vanzarilor pe trimestre' se observa ca in primul trimestru al anului valoarea vanzarilor a fost de 1782433 lei. In trimestrul doi vanzarile au crescut foarte mult atingand suma de 11833012,66 lei. In lunile iulie, august, septembrie, respectiv trimestrul trei, s-a inregistrat     o scadere brusca a vanzarilor, ajungand la 163975,574. In ultimul trimestru al anului vanzarile au mai crescut dar mult sub nivelul celor din trimestrul 2.

E.    previzionarea unor valori cu ajutorul analizei What-If

Scenariu: sa se realizeze un scenariu pentru a evidentia importanta modificarii TVA-ului, impozitului, cantitatii vandute si pretului mediu asupra venitului rezultat din comercializarea prodsului titei C selectionat. Cursul de schimb se considera 1 euro=3,55 lei.

Din foia de calcul "Facturi" au fost extrase urmatoarele date referitoare la produsul titeti C selectionat:

Pentru a rezolva cerinta datele s-au centralizat astfel:

Generarea unui scenariu se realizeaza cu ajutorul opriunii Scenario din meniul Tools. Se considera un scenariu pesimist cel pentru care TVA-ul este de 22%, impozitul creste la 5% iar cantitatea vanduta scade la 12.000. Pretul mediu este de 1.250 RON.

Un scenariu optimist este cel in cate TVA-ul ar scadea de la 19% la 17% iar impozitul la 3 euro, in timp ce cantitatea vanduta ar creste pana la 18000 tone si pretul al atinge un maxim de 1.450 RON.

Dupa cum se observa, in cazul scenariului pesimit veniturile ar scadea cu 3.331.420,85 RON iar pentru cel optimist el ear creste, ajungand la 212.703.300 RON.

Goal Seek

Sa se determine impozitul in cazul in care valoarea impozitului este 850, cursul de schimb 3,4 iar cantitatea achizitionata 30 tone.

Se considera linie din foaia de calcul "Impozit":

Pentru determinarea impozitului am utilizat facilitatea Goal Seek din meniul Tools.

Valoarea impozitului se calculeaza cu ajutorul formulei:

valoare impozit=cantitate*impozit*curs.

Rezultatul obtinut este:

Interpretare: pentru cazul in care valoarea impozitului este 850, cursul de schimb 3,4 iar catitatea achizitionata de 30 tone, s-a obtinut un impozit de 8,33 euro.

Solver

Societatea comercializeaza doua tiputi de titeti: condenstat (c) si C selectionat (t). Modificand parametrii pret mediu, cantitate si impozit, sa se optimizeze modelul prin maximizarea venitului din vanzari al societatii.

Se vor considera urmatoarele restictii:

pretul mediu trebuie sa se incadtreze intre 1,250 lei si 1,450 lei pentru ambele tipuri de produse;

titeiul condensat vandut nu poate depasi 20,00 tone iar cel C selectionat 1,000 tone;

impozitul poate fi de 3, 4 si 5 euro. Cursul mediu este considerat 3,55 lei.

Datele initiale se gasesc in urmatorul table, ele se centralizeaza atat pe fiecare produs cat sip e ansamblul societatii:

Obiectivul este acela de maximizare a venitului la nivelul societatii. Parametrii pret mediu, cantitate, impozit, vor trebui modificati astfel incand valorile acestora supuse restictiilor sa genereze un venit maxim. Pentru aceasta s-a folosit facilitatea Solver din meniul Tools.

Rezultatul optimizarii este dat in tabelul de mai jos:

Pentru un impozit de 3 euro, un pret mediu de 1.450 RON, cele doua produse urmand a fi achizitionate in cantitatile 20.000 tone cel condensate si 1.000 tone cel C selectionat s-a obtinut un venit de 24.440.850 RON.

Pivot

Sa se dispuna informatiile referitoare la inregistrarea facturilor intr-o tabela pivot pentru o mai buna vizualizare a acestora.

Tabela pivot va trebui sa permina vizualizarea, pentru fiecare beneficiar, a documentului emis catre acesta si data emiterii, precum si cantitatea achizitionata, valoarea acesteia, TVA-ul aferent si reducerea acordata.

Pentru generarea tabelei pivot se for folosii datele din foia de calcul "Facturi" (vezi ANEXA fig 1).

Tabela pivot generate cu optiunea Pivot Table din meniul Data conform cerintelor este redata in continuare:

ANEXA

Figura1 - tabelul "Facturi"

Figura 2 - tabelul "Impozit"