Seminar Microsoft Excel partea 2

1. Recapitulare formule i funcii

Nr. Pas Mod de rezolvare OBS. 1. O firm importatoare de produse de birotic a

importat n primele ase luni ale anului trei categorii de produse: creioane, pixuri i markere. Firma a vndut n fiecare lun toate produsele importate. Cunoscndu-se cantitile importate i preurile de import, precum i ali indicatori ai activitii firmei, s se determine profitul net pe fiecare din cele ase luni

2. n fiierul Seminar2 Excel.xls, activai foaia de calcul Date.

3. Calculai valoarea n vam lunar pentru fiecare categorie de produse.

- Se calculeaz celulele de pe randurile 5, 8 i 11, astfel: Valoare = Cantitate* Pre

4. S se determine valoarea total n vam lunar Celulele de pe randul 13.

n acest seminar vei utiliza urmtoarele funcionaliti ale pachetului software: Recapitulare formule i funcii Grafice Scenarii Goal Seek

(pentru toate categoriile de produse). 5. S se determine Cheltuielile cu importul aferente

fiecrei luni. - Se calculeaz celulele de pe randul 13. - Formula de calcul: Cheltuieli cu importul = Valoarea

in vam*(1-Taxa vamala)

Atenie la folosirea adresrii absolute!

6. S se determine veniturile din vanzri, prin aplicarea adaosului comercial (abreviat A.C.)

- Se calculeaz celulele de pe randul 23, dup formula: Venituri din vanzari = Cheltuielile cu importul * (1+Adaos comercial)

Atenie la folosirea adresrii absolute!

7. S se culculeze cheltuielile cu salariile, tiind c fondul de salarii n primele dou luni a fost de 20000 RON, dup care, din luna martie a crescut cu 15% i a rmas constant pn la sfritul perioadei.

Celulele de pe randul 16.

8. Conducerea firmei a hotrt s acorde prime angajailor si, dac Veniturile din vnzri depesc un anumit plafon. Dac n luna respectiv Veniturile din vnzri au depit plafonul (Plafon :30000 RON), se acord o prima (Prima :10%) din diferena dintre Veniturile din vnzri iPlafon.

- Se calculeaz celulele de pe randul 17, prin folosirea funciei logice IF:

o Condiie: Venituri vanzari > Plafon o Ramura DA: (Venituri vanzari Plafon)* Prima o Ramura NU: 0

9. S se determin taxele i impozitele aferente salariilor.

- Se calculeaz celulele de pe randul 18, dup formula: Taxe si impozite = (Fond Salarii + Prime) * ( Imp. Sal + taxe (CAS))

10. Calculai Cheltuielile salariale totale ca sum dintre Fond salarii, Prime, Taxe si impozite.

Celulele de pe randul 19.

11. Se cunoate c, n fiecare lun, cheltuielile cu chiriile i regia au fost n valoare de 10000 RON.

Celulele de pe randul 20.

12. Stabilii cheltuielilel unare totale, sa sum dintre Cheltuielile cu importul, Cheltuielile Salariale totale i Cheltuielile fixe.

Celulele de pe randul 21.

12. Calculai profitul brut. - Se calculeaz celulele de pe randul 25, dup formula: Profit brut = Venituri din vanzari Total Cheltuieli

13. Calculai profitul net. - Se calculeaz celulele de pe randul 26, dup formula: - Profit net = Profit brut *(1- Impozit profit)

2. Grafice

Nr. Pas Mod de rezolvare OBS. 1. n fiierul Seminar2 Excel.xls, activai foaia de

calcul Date

2. Creai un grafic care s prezinte structura cantitilor importate n luna Ianuarie.

- Selectai celulele care conin cantitile importate n Ianuarie (C3, apoi innd apsat, C6 i C9)

- Insert->Chart (Chart Wizard) o Selectai tipul de grafic (Pie) o Series in: Columns o Completai corespunztor Titlul,

Legenda i Etichetele o Place Chart - As New Sheet: Structura

3. Modificai legenda, astfel nct datele s fie uor de neles

- Selectai graficul - Chart->Source Data, Series - Category Labels: selectai A3, apoi innd

apsat, A6 i A9 - OK

4. Adugai i alte informaii pe grafic (valoarea, procentul, numele pentru fiecare felie)

- Selectai graficul - Chart->Chart Options, Data Labels - Category Name, Value, Percentage - OK

5. Creai un grafic cu bare verticale pentru a evidenia evoluia preului celor trei articole pe

- Datele de selectat: C4:H4; C7:H7; C10:H10 (Cu ajutorul tastei !!)

parcursul celor 6 luni - Chart Type: Columns - Opiunis: X-Axis Luna, Y-Axis Preul - Titlu: "Evolutia Pretului - Place Chart - As New Sheet (EvolutiaPretului)

6. Modificai tipul de grafic - Selectai datele din grafic o Chart->Chart Type

7. Creai o legend cu sens - Selectai graficul o Chart->Source Data, Series o Series Name (un nume pentru fiecare

set de date: Creioane, Pixuri, Markere) o Category X Axis Labels (C2:H2)

8. Adaugai alte informaii pe graphic (Valoarea) - Selectai graficul o Chart->Chart Options, Data Labels o Value

9. Salvai fiierul

3. Scenarii

Nr. Pas Mod de rezolvare OBS. 1. In fiierul Seminar2 Excel.xls, activai foaia de

calcul Date

2. Se presupune c taxele vamale scad, de la 5% la 3%. Cum se modific profitul net?

- Tools->Scenarios, Add - Scenario Name: taxe imp3% - Changing Cells: K3, OK - New Value: 0,03, OK

3. Creai un raport pe baza scenariului - Selectai opiunea Summary - Result cells: C26:H26 (Profitul net)

5. Salvai fiierul

4. Goal Seek (Cutarea valorii int)

Nr. Pas Mod de rezolvare OBS. 1. n fiierul Seminar2 Excel.xls, activai foaia de

calcul Date

2. Deteminai ct ar fi trebuit s fie adaosul comercial, astfel nct firma s obin n luna iunie un profit de 32000 RON.

- Tools->Goal Seek: o Set cell: profit net iunie o To value: 32000 o By changing cell: Adaos comercial

- Apsai OK dac vrei s pstrai noile valori, sau Cancel, n caz contrar

3. S-a decis ca un capital de 10,000 s fie depus la banc sub forma unui depozit avnd dobnda anual de 3%. tiind c dobnda este compus, s-a calculat (prin dou variante) capitalul final care se obine dup o perioad de 5 ani.

Deschidei fiierul Seminar2 Excel.xls, foia de calcul Goal Seek i analizai modul de calcul al dobnzii compuse, prin cele dou variante.

4. Dorim ca dup perioada de cinci ani s avem n cont un capital final de 15.000 . Ct de mare trebuie s fie capitalul iniial, dac se acord acceai dobnd?

Pentru ambele variante aplicai paii urmtori: Tools->Goal Seek:

o Set cell: capitalul final dup 5 ani o To value: 15000

Prin Goal Seek se poate defini o singur celul care i modific valoarea!

Presupunem c celula D (celula rezultat) se calculeaz pe baza celulelor A, B i C (celule parametru), adic D = f(A, B, C). Facilitatea Goal Seek ne permite ca, stabilind o valoare pentru celula rezultat, i cunoscnd, de exemplu valorile parametrilor B i C, s se calculeze valoarea parametrului A. Prin Goal Seek putem spune c se realizeaz un drum invers fa de modul obinuit de calcul a valorii unei celule n MS Excel. De precizat c pot exista oricte celule parametru care s influeneze celula rezultat, dar c se poate determina numai valoarea unei singure celule parametru.

o By changing cell: capitalul iniial la nceputul celor 5 ani

Seminar Microsoft Excel partea a III-a

1. Solver

Nr. Pas Mod de rezolvare OBS. 1. In fiierul Seminar Excel 3.xls, activai foaia de

calcul Solver

2. Specificai parametrii pentru problema dat Tools->Solver Target Cell: D18, Max (Profitul total) Changing Cells: D9:F9 (Cantitatile de produs) Restrictions: Add C11:C15 = 0

3. Gsii soluia optim Solve 4. Salvai rezultatul gsit sau revenii la problema

iniial

5. Salvai fiierul

2.Liste (simularea lucrului cu baze de date n Excel) Nr. Pas Mod de rezolvare OBS. 1. In fiierul Seminar Excel 3 foaia de calcul

Vanzari

n acest seminar vei utiliza urmtoarele funcionaliti ale pachetului software: Solver Lucrul cu meniul Data: Form, Sort, Filter, Subtotals, validare de celule, Pivot Table

2. Adaugai o nou nregistrare Selectai domeniul de celule A1: H16 Data->Form; selectai butonul New Adugai o nou nregistrare valid

Cnd se lucreaz cu astfel de tabele, trebuie ca cel puin o celul din list s fie selectat

3. Utilizai diverse criterii de cutare pentru vanzari Data->Form; selectai butonul Criteria 1. Toate vanzarile care au client Real 2. Toate vanzarile din luna martie

Pentru a parcurge toate nregistrrile care satisfac criteriul se apas butonul Find Next

4. Validai datele din list astfel nct n cmpul Luna s poat fi introduse numai valori ntre1 i 12

Selectai domeniul de celule A2:A16 Data->Validation Completai n fereastra Data Validation informaiile:

a. Settings Allow: Whole Number, Between, Min:1, Max:12

b. Input Message Titlu: Luna; Message: Introducei un numar ntreg ntre 1 i 12!

c. Error Alert Style: Stop; Title: Luna; Message: Valoare greit!

Introducei valori corecte i greite n celulele pentru care s-a realizat validarea

5. Selectai din list numai vanzarile din luna februarie, ale furnizorului Stabilo

Selectai domeniul de celule A1: G16 Data->Filter->AutoFilter Executai un click pe sgeata aprut n dreptul lunii i selectati 2. Similar pentru furnizor.

6. Revenii la situaia iniial Data->Filter->AutoFilter

7. Sortai lista dup cmpurile Client i Articol Data->Sort (Client, Articol)

8. Calculai valoarea total a vnzrilor pentru fiecare client i per total

Data->Subtotals Lista trebuie mai nti sortat dup Client!

9. Revenii la situaia iniial Data->Subtotals: Remove All

10. Realizai un raport privind valoarea vnzrilor ctre fiecare client, evideniind ct a cumprat clientul de la fiecare furnizor, care s arate ca cel din figura de mai jos.

Data-> PivotTable and PivotChart-Report Pasul 1 - Selectai opiunile urmtoare: - Microsoft Office Excel list or database - Pivot Table Pasul 2 Selectai domeniul de celule pentru care vrei s realizai tabelul pivot, incluznd obligatoriu i capul de tabel al listei Pasul 3 Alegei locul n care vrei s plasai tabelul pivot (New Worksheet) Tragei cu Drag&Drop cmpurile din Pivot Table Field List pe suprafaa de lucru, astfel: - n Drop Raw Fields Here plasai cmpul care

reprezint criteriul de grupare pe orizontal - n Drop Column Fields Here plasai cmpul care

reprezint criteriul de grupare pe vertical - n Drop Data Fileds Here plasai cmpul care

reprezint informaiile de baz pentru care realizai raportul

Filtrai datele din raport astfel nct s cuprind numai comenzile nregistrate n luna februarie, plasnd cmpul dup care realizai filtrarea n Drop Page Fileds Here.

11. Salvai fiierul

Valoare

Furnizor Client

Seminar2 -Excel.pdfSeminar_3_Excel.pdf