Proiect Excel-WINQSB-SAS - Pachete Software

download Proiect Excel-WINQSB-SAS - Pachete Software

of 48

description

Proiect pachete software

Transcript of Proiect Excel-WINQSB-SAS - Pachete Software

Proiect Excel-WINQSB-SAS - Pachete Software

Academia de Studii Economice Bucureti

Facultatea de Cibernetic Statistic i Informatic EconomicPROIECT PACHETE SOFTWARE

MS Excel - WinQSB - SASBucureti 2010SC NaturaVit SA este unul din principalii producatori de produse naturiste din Romania precum ceaiuri, tincturi, uleiuri si unguente din extracte naturale. Cu sediul in Bucuresti, compania detine 3 centre de productie in Bacau, Campina si Slatina si 5 magazine proprii in apropierea centrelor de productie, in Bucuresti si Galati.In tabelul de mai jos sunt specificate cantitatile si preturile sortimentelor produse si comercializate in luna martie 2010:Tip produsSortimentprodusCantiti produse01.03.2010Costproductie (buc)Cost totalproductie (lei)Cantitati vandute 31.03.2010Pret produscu TVAVenituri totale (lei)

ceaiiasomie5001,60800,003942,38937,72

ceaimusetel5001,80900,005002,681.338,75

ceaislabit5002,701.350,004204,021.686,83

ceaifructe4502,20990,004503,271.472,63

ceaiceai verde4503,001.350,003754,461.673,44

ceaihepatic4503,101.395,004234,611.950,56

ceaitonic4804,001.920,004805,952.856,00

ceaidetoxifiere4804,202.016,004806,252.998,80

tincturacastane2006,501.300,00749,67715,49

tincturaaloe vera3008,002.400,0030011,903.570,00

tincturabrusture2507,201.800,0019910,712.131,29

tincturabusuioc2004,00800,00755,95446,25

tincturapapadie2505,001.250,002277,441.688,31

tincturasalvie2505,901.475,001028,78895,18

tincturasoc3006,001.800,002868,932.552,55

uleiarnica1007,00700,0010010,411.041,25

uleiantireumatic2509,802.450,0025014,583.644,38

uleinuca10017,501.750,008726,032.264,72

uleimigdale10026,502.650,009639,423.784,20

uleimixt2505,501.375,002508,182.045,31

unguentsalva4001,30520,003661,93707,75

unguentsunatoare4001,80720,004002,681.071,00

unguentgalbenele4504,602.070,004506,843.079,13

unguentcatina4503,201.440,002314,761.099,56

unguentpropolis4004,001.600,004005,952.380,00

unguentrasina4005,002.000,003397,442.521,31

A. Microsoft Office 2007Functii predefinite

1. a) Sa se calculeze care sunt costurile totale si profitul total obtinut din vanzarea produselor pe luna martie.

b) Se foloseste functia matematica SUM(Number 1;Number 2..) pentru coloanele Cost total productie si Profit.c) Se obtin urmatoarele rezultate:Tip produsSortimenteprodusCantiti produse01.03.2010Cost de productie (buc)Cost totalproductie (lei)Cantitati vandute 31.03.2010Pret produscu TVAVenituri totale (lei)Profit

TOTAL38.821,0053.586,8914.765,89

d) Se observa ca firma are un profit total de 14 765,89 RON pe luna martie 2010.

2. a) Sa se afiseze care produse se afla in stoc la sfarsitul lunii si care au fost vandute integral.b) Se foloseste functia logica IF (logical test; value if true; value if false) astfel: daca valoarea din coloana Cantitati ramase este egala cu 0 atunci functia IF va returna in coloana Stoc mesajul Vandut; in caz contrar va returna Stoc.

c) Formula pentru coloana Stoc este : =IF(L2:L27=0;"Vandut";"Stoc"). Rezultatele obtinute se pot vedea in foia de lucru Date a documentului Excel NaturaVit.Functii financiare

3. a) SC NaturaVit SA isi propune sa-si dezvolte activitatea prin deschiderea unui magazin in Cluj. Pentru aceasta are nevoie de un capital de 14 000 Euro (56 000 lei). Banca ii acorda imprumutul cu o dobanda de 16 % pe an, pe o perioada de 5 ani. b) Se aplica functia financiara PMT (rata_dobanda, reper, vp, vv, tip), c) Formula pentru plata lunara este =-PMT(0,0133;60;56000). Se observa ca firma trebuie sa plateasca lunar 1.360,62 lei pentru a achita integral imprumutul:

Suma imprumutata56.000

Dobanda anuala16%

Perioada creditului5

Plata lunara1.360,62 lei

4. a) Daca firma isi permite sa economiseasca lunar 1000 lei timp de 3 ani, la o dobanda de 3,2 % pe an, ar fi mai rentabila economisirea decat imprumutul? b) Se aplica functia FV (rata_dobanda, nr_rate, platt, vp, tip)c) Formula pentru Suma obtinuta este =-FV(0,032;36;1000;0;0). Se observa ca in 3 ani firma ar putea economisi peste 65 000 lei. Depozit lunar1.000

Rata dobanzii3,20%

Perioada depozitului3

Suma obtinuta65.872,34 lei

d) CONCLUZIE: Economisirea ar fi mai rentabila decat imprumutul deoarece intervalul de timp si rata lunara sunt mai mici decat in cazul imprumutului.

Goal Seek

5. a) In cadrul companiei exista 5 departamente: Aprovizionare, Productie, Vanzari, Resurse umane si Marketing. Fiecarui departament in parte i se repartizeaza un buget initial. observa ca unele departamente se incadreaza in bugetul repartizat, altele il depasesc. Cu cat ar trebui sa creasca bugetul departamentului Productie astfel incat diferenta totala sa fie 0?b) Se dau urmatoarele date:Buget repartizatBuget utilizatDiferenta

Aprovizionare7.9007.800100

Productie9.00010.070-1.070

Vanzari1.7001.600100

Resurse umane10.00010.0000

Marketing3.6003.58020

Total32.20033.050-850

Pentru a echilibra situatia se redistribuie bugetul cu facilitatea Goal-Seek din meniul Data->Data Tools->What if Analysis astfel:

c) Se obtine urmatorul tabel, cu valoarea 9.850 lei Bugetul repartizat pt departamentul ProductieBuget repartizatBuget utilizatDiferenta

Aprovizionare7.9007.800100

Productie9.85010.070-220

Vanzari1.7001.600100

Resurse umane10.00010.0000

Marketing3.6003.58020

Total33.05033.0500

d) CONCLUZIE: Bugetul departamentului de Marketing ar trebui marit cu 850 de lei:

1070 (diferenta initiala) 220(diferenta cand totalul este 0) = 850 lei.Pivot Table6. a) Pentru a putea sti volumul productiei viitoare, este necesar sa realizeze o analiza a produselor din stoc precum si sa comparam vanzarile pe fiecare tip de produs in parte ca mai apoi sa realizeze strategii pentru marirea profitului.b) Se realizeaza un raport cu ajutorul functiei Pivot Table din meniul Insert:

c) Rezulta urmatorul tabel, cu 4 coloane: Produse, Profit produse in stoc, Profit produse vandute si Profit total:Sum of ProfitColumn Labels

Row LabelsStocVandutGrand Total

ceai1591,541252840,1754431,71625

ceai verde323,4375323,4375

detoxifiere982,8982,8

fructe482,625482,625

hepatic555,55875555,55875

iasomie375,72375,72

musetel438,75438,75

slabit336,825336,825

tonic936936

tinctura1848,567511703018,5675

aloe vera11701170

brusture331,29331,29

busuioc-353,75-353,75

castane382,3625382,3625

papadie438,3125438,3125

salvie297,8025297,8025

soc752,55752,55

ulei1648,918752205,93753854,85625

antireumatic1194,3751194,375

arnica341,25341,25

migdale1134,21134,2

mixt670,3125670,3125

nuca514,71875514,71875

unguent1320,6252140,1253460,75

catina611,56611,56

galbenele1009,1251009,125

propolis780780

rasina521,3125521,3125

salva187,7525187,7525

sunatoare351351

Grand Total6409,65258356,237514765,89

d) Din tabelul tabelul de mai sus reiese ca, desi exista produse ramase in stoc, exista totusi profit. Astfel pentru produsele din categoria ceai sunt cele mai mari profituri.Subtotaluri7. a) Sa se afle ce vanzari a avut fiecare tip de produs in parte.b) Se foloseste metoda Subtotal din meniul Data->Outline

c) Se obtine tabelul urmator, in sheet-ul Subtotal din foia de calcul Excel NaturaVit:

Tip produsSortimenteprodusCantiti produse01.03.2010Cost de productie (buc)Cost totalproductie (lei)Cantitati vandute31.03.2010Pret vanzare(lei/buc)TVAPret produscu TVAVenituri totale (lei)ProfitCantitati ramase31.03.2010

ceaiiasomie5001,60800,003942,000,382,38937,72137,72106

ceaimusetel5001,80900,005002,250,432,681.338,75438,750

ceaislabit5002,701.350,004203,380,644,021.686,83336,8380

ceaifructe4502,20990,004502,750,523,271.472,63482,630

ceaiceai verde4503,001.350,003753,750,714,461.673,44323,4475

ceaihepatic4503,101.395,004233,880,744,611.950,56555,5627

ceaitonic4804,001.920,004805,000,955,952.856,00936,000

ceaidetoxifiere4804,202.016,004805,251,006,252.998,80982,800

ceai Total352214.914,724.193,72

tincturacastane2006,501.300,00748,131,549,67715,49-584,51126

tincturaaloe vera3008,002.400,0030010,001,9011,903.570,001.170,000

tincturabrusture2507,201.800,001999,001,7110,712.131,29331,2951

tincturabusuioc2004,00800,00755,000,955,95446,25-353,75125

tincturapapadie2505,001.250,002276,251,197,441.688,31438,3123

tincturasalvie2505,901.475,001027,381,408,78895,18-579,82148

tincturasoc3006,001.800,002867,501,438,932.552,55752,5514

tinctura Total126311.999,071.174,07

uleiarnica1007,00700,001008,751,6610,411.041,25341,250

uleiantireumatic2509,802.450,0025012,252,3314,583.644,381.194,380

uleinuca10017,501.750,008721,884,1626,032.264,72514,7213

uleimigdale10026,502.650,009633,136,2939,423.784,201.134,204

uleimixt2505,501.375,002506,881,318,182.045,31670,310

ulei Total78312.779,863.854,86

unguentsalva4001,30520,003661,630,311,93707,75187,7534

unguentsunatoare4001,80720,004002,250,432,681.071,00351,000

unguentgalbenele4504,602.070,004505,751,096,843.079,131.009,130

unguentcatina4503,201.440,002314,000,764,761.099,56-340,44219

unguentpropolis4004,001.600,004005,000,955,952.380,00780,000

unguentrasina4005,002.000,003396,251,197,442.521,31521,3161

unguent Total218610.858,752.508,75

Grand Total7.75450.552,3911.731,39

TOTAL6.76738.821,007.75450.552,3911.731,39

d) Din tabelul de mai sus rezulta ca cele mai vandute produse sunt cele din categoria Ceai, cu 3622 unitati vandute si cu un profit de peste 4.430 lei.Grafice

8. a) Sa se reprezinte grafic ponderea de profit pe care o au cele 4 categorii de produseb) Se selecteaza coloana Profit din Tabelul 7 si se insereaza un grafic Pie Chartc) Se obtine urmatorul grafic:

d) Se observa ca produsele din categoria Tincturi au cel mai mic profit, iar cele din categoria Uleiuri au cel mai mare profit, profitul fiind proportional cu numarul de bucati produse

9. a) Sa se reprezinte grafic ponderea de unitati vandute si unitati ramase pentru cele 26 de sortimenteb) Se selecteaza coloanele Cantitati Produse si Cantitati Ramase si se insereaza un Bar Chart dupa cum urmeaza:

c) Rezulta urmatorul grafic:

B. WinQSB

Drumuri de valoare optima

a) Firma are la dispozitie mai multe variante tehnologice de prelucrare a tincturii. Fiecare varianta are mai multe faze/stadii. Fiecarei faze de prelucrare ii corespunde un cost, firma urmand sa aleaga varianta care ii permite un cost minim.

b) Se foloseste metoda NET (Network Modelling) dupa cum urmeaza:

Selectam Shortest Path Problem si introducem numarul de noduri:6

Vom introduce in tabelul alaturat costurile pentru fiecare arc:

Alegem care este nodul de start si nodul final:

c) Avem ca rezultat urmatorul tabel:

d) Se observa ca cel mai ieftin proces de productie cuprinde 2 faze: Materii prime->Faza2->Faza5->Produs Final si costa 10 euro/buc

Problema de transporta) NaturaVit SA isi desfasoara activitatea in 3 fabrici diferite si isi distribuie produsele catre 5 magazine, toate aflate in diverse orase. Cum costurile de productie sunt identice in toate cele 3 fabrici, singurile costuri relevante sunt ce de de transport intre fabrici si magazine. Stiind capacitatile de productie si necesarul magazinelor, se doreste a se determina modul de livrare a produselor din fabrici catre magazine pentru un cost de transport nimim.

M1M2M3M4M5Capacitate

C1125341000

C2421211200

C3531321400

Necesar800750750600700

b) Se foloseste metoda NET (Network Modelling) dupa cum urmeaza: Selectam Transportation Problem si introducem 3 la numarul de surse si 6 la numarul de destinatii:

Introducem datele din tabelul 11 Vizualizam graful aferent problemei:

c) In urma selectarii optiunii Solve the problem, avem rezultatul de mai jos:

d) Costul total de transport este minim 5600 euro daca :

Fabrica 1 aprovizioneaza Magazinul 1 cu 800 de buc. si Magazinul 2 cu 200 de buc.

Fabrica 2 aprovizioneaza Magazinul 2 cu 500 de buc. si Magazinul 5 cu 700 de buc.

Fabrica 3 aprovizioneaza Magazinul 3 cu 50 de buc, Magazinul 3 cu 750 de buc si Magazinul 4 cu 600 de buc.In continuare vom folosi analiza What If pentru a analiza cum se modifica costul total daca creste costul de transport de la o fabrica la un magazin.

1. a) Daca costul de transport intre Fabrica 3 si Magazinul 3 creste, devenind 3 euro cum se modifica costul total?

b) In meniul Solve and analyse alegem optiunea Perform What if Analysis si modificam costul transportului de la fabrica 3 la magazinul la 3:

c) In urma selectarii optiunii de mai sus, avem rezultatul:

d) Costul total de transport este minim 6350 euro daca :

Fabrica 1 aprovizioneaza Magazinul 1 cu 800 de buc. si Magazinul 2 cu 200 de buc.

Fabrica 2 aprovizioneaza Magazinul 3 cu 750 de buc. si Magazinul 5 cu 450 de buc.

Fabrica 3 aprovizioneaza Magazinul 2 cu 550 de buc, Magazinul 4 cu 600 de buc si Magazinul 5 cu 250 de buc.

2. a) Daca din cauza disponibilizarii personalului fabrica 2 isi reduce capacitatea la doar 600 de bucati, cu cat se schimba costul minim de transport?

b) In meniul Solve and analyse alegem optiunea Perform What if Analysis si modificam capacitatea fabricii 2 din 1200 in 600:

c) In urma selectarii optiunii de mai sus, avem rezultatul:

d) Costul total de transport scade la 4400 euro daca :

Fabrica 1 aprovizioneaza Magazinul 1 cu 800 de buc. si Magazinul 2 cu 200 de buc.

Fabrica 2 aprovizioneaza Magazinul 2 cu 550 de buc. si Magazinul 5 cu 50 de buc.

Fabrica 3 aprovizioneaza Magazinul 3 cu 750 de buc si Magazinul 5 cu 650 de buc.OBS: Costul minim nu include si aprovizionarea magazinului 4.C. SAS Enterprise

Se doreste o analiza statistica a datelor referitoare la activitatea pe luna martie 2010 a companiei NaturaVit. Compania detine urmatoarele informatii:

TipprodusSortimenteprodusCantitati produseCost produsCost total

ceaiiasomie5001,60200,00

ceaimusetel5001,80225,00

ceaislabit5002,70337,50

ceaifructe4502,20247,50

ceaiceai verde4503,00337,50

ceaihepatic4503,10348,75

ceaitonic4804,00480,00

ceaidetoxifiere4804,20504,00

tincturacastane2006,50325,00

tincturaaloe vera3008,00600,00

tincturabrusture2507,20450,00

tincturabusuioc2004,00200,00

tincturapapadie2505,00312,50

tincturasalvie2505,90368,75

tincturasoc3006,00450,00

uleiarnica1007,00175,00

uleiantireumatic2509,80612,50

uleinuca10017,50437,50

uleimigdale10026,50662,50

uleimixt2505,50343,75

unguentsalva4001,30130,00

unguentsunatoare4001,80180,00

unguentgalbenele4504,60517,50

unguentcatina4503,20360,00

unguentpropolis4004,00400,00

unguentrasina4005,00500,00

Pentru efectuarea analizei sunt necesare o serie de prelucrari asupra datelor in SAS

Crearea unei tabele

1. a) Se importa in SAS tabelul de mai sus (sheet-ul Date din foia de calcul Excel Costuri)b) Se deschide File->Import Data->Costuri->Open:

Se modifica formatul la cele 2 coloane ce contin valori exprimate in euro:

c) Se obtine urmatoarea tabela in SAS:

Importarea unui fisier flat

2. a) Se da o lista cu veniturile obtinute din vanzarea celor 4 categorii de produse, in luna martie 2010 (fisier text):ceaiiasomie394,000,60234,43

ceaimusetel500,000,67334,69

ceaislabit420,001,00421,71

ceaifructe450,000,82368,16

ceaiceai verde375,001,12418,36

ceaihepatic423,001,15487,64

ceaitonic480,001,49714,00

ceaidetoxifiere480,001,56749,70

tincturacastane74,002,42178,87

tincturaaloe vera300,002,98892,50

tincturabrusture199,002,68532,82

tincturabusuioc75,001,49111,56

tincturapapadie227,001,86422,08

tincturasalvie102,002,19223,79

tincturasoc286,002,23638,14

uleiarnica100,002,60260,31

uleiantireumatic250,003,64911,09

uleinuca87,006,51566,18

uleimigdale96,009,85946,05

uleimixt250,002,05511,33

unguentsalva366,000,48176,94

unguentsunatoare400,000,67267,75

unguentgalbenele450,001,71769,78

unguentcatina231,001,19274,89

unguentpropolis400,001,49595,00

unguentrasina339,001,86630,33b) Se cere sa se importe in SAS fisierul text delimitat prin Tab de mai sus:

File->Import Data.

Se selecteaza formatul fisierului: Delimited fields->Tab

Se defineste formatul campurilor fisierului:

c) In final se obtine tabela Venituri:

Interogari 3. a) Sa se afiseze produsele a caror costuri de productie sunt mai mici de 1 eurob) Se foloseste optiunea Query Builder din meniul Process Flow:

Se selecteaza coloanele Tip, Sortimente, Cantitati, Cost si Cost total:

Adaugam un filtru pentru coloana Cost de_productie, din meniul Filter Data:

Se selecteaza operatorul less than si valoarea 1(euro):

c) Se obtine urmatoarea tabela ce contine sortimentele cu un cost de productie mai mic decat 1 euro, in luna martie 2010:

Jonctiune interna4. a) Managerul general doreste sa afle care au fost costurile si profiturile pentru fiecare sortiment in parte. Deoarece aceste informatii sunt cuprinse in 2 tabele separate se va fac o jonctiune interna a lor pentru a intocmi un raport pe baza lorb) Se deschide optiunea Query Builder din meniul Process Flow si se apasa Join Tables:

Se apasa Computed Columns si apoi Advanced Expression:

Se scrie expresia: Venituri - Costuri de productie

Se denumeste noua coloana formata:

Se selecteaza coloanele care o sa apara in noua tabela:

c) Se obtine urmatoarea tabele :

Interogare cu parametru

5. a) Dorim sa se afiseze sortimentele al caror profit este negativ.

b) Pentru aceasta vom folosi o interogare cu parametru (in functie de ce valoare a profitului se afiseaza sortimentele ):

Se deschide optiunea Query Builder din meniul Process Flow, se apasa Add New Prompt si se seteaza urmatoarele proprietati:

Se adauga un filtru pentru a adisa doar acele sortimenete al caror profit este mai mic decat valoarea parametru introdusa de noi de la tastatura:

Se selecteaza coloanele care se vor afisa dupa interogare:

Dupa Run, va aparea o fereastra in care vom introduce de la tastatura valoarea maxima a profitului pentru care se vor afisa informatiile:

c) Obtinem urmatoarea tabela:

d) Se observa ca doar 4 sortimente au profit negativ, dintre care tinctura de castane si cea de salvie au pierderile cele mai mari.Grafic6. a) Se cere sa se creeze un grafic in care sa se reprezinte profitul pentru fiecare produs:b) Se selecteaza Bar Chart Wizard:

Se alege coloana Sortimente pe axa X si coloana Profit pe axa Y:

c) Se obtine urmatorul grafic:

d) Se observa ca exista 4 sortimente care au profit negativ de peste 100 euro si 6 sortimente care au profit de peste 200 euroJonctiune externa7. a) Unul din magazinele companiei a facut deja comanda de produse pentru luna urmatoare. Sa se afiseze care dintre produsele comandate se afla pe stoc in depozit

b) Se importa mai intai lista cu sortimentele produse si vandute in luna martie si cea cu produsele comandate pe luna aprilie:

se importa lista cu comenzile:

se importa lista cu produsele

se realizeaza un outer join intre cele 2 tabele astfel incat sa se afiseze produsele din tabela Comenzi care se afla pe stoc in tabele Stoc Se realizeaza un filtru care sa afiseze sortimentele care se afla pe stoc:

Se selecteaza coloanele care se vor afisa:

c) Se afiseaza sortimentele care au fost comandate si se afla pe stoc in depozit:

Prelucrari

8. a) Sa se listeze pentru toate tipurile de produse, cantitatile si costul total de productie:b) Se deschide meniul Describe->List Data:

c) Se obtine urmatoare lista:

9. a) Directorul departamentului de productie doreste sa optimizeze timpul de productie astfel ca trebuie sa stie numarul de sortimente si frecventa lorb) Se deschide meniul Describe-> One way frecvencies

c) Se afiseaza urmatoarea tabela:

d) Se observa ca 3 sortimente se produc in numar de 100 de bucati, 2 sortimente se produc in numar de 200 de bucati [] si 3 sortimente se produc in numar de 500 de bucati.

Distribution analysis10. a) Sa se faca o analiza statistica a produselor dupa cantitatile vandute:

b) Se deschide meniul Describe-> Distribution Analysis

c) Se obtin urmatoarele date:

d) Analizand datele de mai sus observam ca s-au vandut, in medie, 440 de bucati de sortimente de ceai si ca s-au vandut mai multe sortimente a cate 480 de bucati.Formate definite de utilizator11. a) Sa sa inlocuiasca valorile din coloana venituri cu intre . sieuro

b) Se defineste un format din meniul Data-> Define format:

Avand deschisa tabela Venituri, se deschide Query Builder si se selecteaza coloanele Sortimente,Cantitati si Venituri; se deschide Properties pentru coloana Venituri:

Se selecteaza User defined -> formatul Venituri:

c) Rezulta urmatoarea tabela:

Aplicarea unui stil asupra unui rezultat HTML

12. a) Sa se aplice un stil predefinit pentru tabela Freqencies:

b) Se apasa click dreapta pe SAS Report One Way Frequencies->Properties

Se alege un stil:

c) Se da dublu click pe raport si rezulta:

Crearea unui document compus cu document builder13. a) Sa se creeze un raport care sa cuprinda rezultatel obtinute in toate rapoartele listate in proiect

b) Se deschide Tools-> Create HTML Document

Se deschide fiecare raport HTML in parte:

Se alege stilul de reprezentare:

c) Se obtine documentul: