ACADEMIA DE STUDII ECONOMICE BUCURESTI Facultatea de Management

Disciplina: BIROTIC

EXCEL Pentru avansai

Autori: Prof. Univ. Dr. ION NAFTANAILA

PAUL BRUDARU

- 2003 -

2

Cuvnt nainte

Aceast carte se adreseaz studenilor facultii Management din cadrul Academiei de Studii Economice Bucureti i este destinat dezvoltrii abilitii de rezolvare a problemelor manageriale utiliznd facilitile programului Microsoft Excel. S-a urmrit ilustrarea principalelor tipuri de aplicaii, n general cunoscute de la disciplinele de specialitate, ns pe fondul unui suport software foarte avansat care permite studenilor, economitilor i cadrelor de conducere o rezolvare simpl i elegant. Majoritatea aplicaiilor sunt rezolvate i ilustrate pas cu pas, dar exist i un numr de probleme propuse destinat studiului individual.

n mod intenionat, nu s-a dorit ca aplicaiile s fie dependente de mbuntirile introduse de programul Excel XP tocmai pentru a putea fi utilizate versiunile curente de pe pia.

Ne exprimm sperana c cititorii vor gsi aceste aplicaii ca fiind interesante i utile i c, prin intermediul acestora, i vor dezvolta gustul pentru modelarea cantitativ i calitativ, n vederea prelucrrii i prezentrii de o manier profesional a aplicaiilor cele mai frecvente din domeniul managerial.

Autorii

3

CUPRINS

1. MODELE DE ANALIZ FINANCIAR ...................................................................................5 1.1. CALCULE CU RATA DOBNZII .....................................................................................................5

1.1.1. Dobnda simpl .................................................................................................................5 1.1.2. Dobnda compus..............................................................................................................6 1.1.3. Efectul schimbrii perioadei de compunere .....................................................................9 1.1.4. Valoarea viitoarea a plilor constante periodice...........................................................11

1.1.4.1. Calculul explicit ........................................................................................................11 1.1.4.2 Calculul algebric ........................................................................................................12 1.1.4.3. Calculul pe baza formulei din foaia de calcul..........................................................13 1.1.4.4. Calculul amortismentului..........................................................................................14

1.1.5. Caracteristicile foii de calcul...........................................................................................14 1.1.5.1. Comanda Fill .........................................................................................................14 1.1.5.2 Utilizarea creativ a opiunii de copiere ....................................................................14

1.2. MPRUMUTURI IPOTECARE........................................................................................................17 1.2.1 mprumuturi cu rambursri ntr-o singur tran............................................................17 1.2.2 Rambursri cu amortismente constante............................................................................19 1.2.3. Rambursri cu anuiti constante. ...................................................................................20 1.2.4. mprumuturi ipotecare. ....................................................................................................21 1.2.5 Rate lunare ipotecare........................................................................................................25 1.2.6 Durata mprumutului ipotecar ..........................................................................................26

1.3.VALOAREA ACTUALIZAT A RAMBURSRILOR .........................................................................30 1.3.1 Actualizarea i valoarea actualizat................................................................................30 1.3.2. Valoarea actualizat a creditelor de studiu i anuitile.................................................32 1.3.3 O privire de ansamblu asupra fluxurilor monetare constante, formule asociate i contracte.....................................................................................................................................35 1.3.4. Pensiile.............................................................................................................................37

2. MODELE DE OPTIMIZARE ....................................................................................................40 2.1. PROGRAMARE LINIAR.............................................................................................................40 2.2. MODELE DE PLANIFICARE AGREGAT ......................................................................................48 2.3. PROBLEME DE TRANSPORT .......................................................................................................53

3. TEHNICI DE PREVIZIUNE......................................................................................................57 3.1. MEDII MOBILE ..........................................................................................................................57 3.2. NIVELAREA EXPONENIAL SIMPL.........................................................................................60 3.3. METODA HOLT PENTRU SERII CRONOLOGICE ...........................................................................63 3.4. METODA WINTERS PENTRU SEZONALITATE .............................................................................66 3.5. REGRESIE LINIAR SIMPL .......................................................................................................71

4. MODELE DE ANALIZ DECIZIONAL ..............................................................................75 4.1. REZOLVAREA PROBLEMELOR CU GOAL SEEK I UTILIZAREA SCENARIILOR PENTRU COMPARAREA SOLUIILOR ..............................................................................................................75 4.2. TABELE PIVOT ..........................................................................................................................79 4.3. PRAGUL DE RENTABILITATE (BREAK EVEN ANALYSIS) .............................................................85

4

5. CONCEPTELE DE BAZ ALE EVALURII PROIECTULUI ...........................................89 5.1. INTRODUCERE ..........................................................................................................................89 5.2. VALOAREA ACTUALIZAT NET A FLUXULUI MONETAR ..........................................................90 5.3 APLICAIE ASUPRA PROIECTULUI DE PROPRIETI IMOBILIARE.................................................91 5.4. RATA POTRIVIT A DOBNZII N SCOPURI DE ACTUALIZARE (DISCONTARE) .............................92 5.5. RATA RENTABILITII MPRUMUTULUI ....................................................................................93

5

1. Modele de analiz financiar

1.1. Calcule cu rata dobnzii Acest capitol trateaz elementele fundamentale ale economiei financiare. Mai nti sunt prezentate formulele pentru dobnda simpl i compus, i sunt obinute formulele corespondente pentru diferite perioade de compunere. Apoi, sunt explicate i obinute formulele pentru valorile viitoare ale plilor constante periodice.

n acest capitol vei nva urmtoarele comenzi i funcii ale foii de lucru: FV (Rata Dobnzii, Termen, Valoare) Edit Fill.

1.1.1. Dobnda simpl Exemplu. Pe 1 ianuarie, anul 1, o persoan cumpr un certificat de investiie garantat pe 5 ani (GIC) pentru $1,000 sau un titlu de aceeai valoare, cu o rat a dobnzii de 8% care trebuie s fie pltit anual. Chiar i pentru investiii aa simple foile de lucru sunt folositoare pentru calcule i pentru a prezenta rezultatele.

Foaia de lucru pentru aceast investiie poate fi mprit n dou, o parte pentru date i una pentru calcule, vezi figura 1.1. Seciunea datelor conine datele principale, care sunt valoarea i rata dobnzii, A1 respectiv A2. Celula D1 care conine valoarea $ 1,000 e denumit A, la fel ca celula C1. Celula D2, care conine rata dobnzii de 8%, e denumit IR, nume introdus n C2. (Pentru anumite motive R nu e nume valabil). Foile registrului de lucru pentru acest capitol conin date asemntoare dar diferite. E convenabil s dm datelor acelai nume n fiecare foaie, dar asta e posibil doar dac

Figura 1.1 Investiie cu dobnd simpl numele se refer numai la datele foii la care facem referire. n general, numele sunt valabile pentru toate foile registrului de lucru, ceea ce nseamn c sunt nume pentru suprafaa registrului de lucru. E de asemenea posibil s avem nume pentru suprafaa foii care sunt valabile doar pentru foaia n care se afl. Numele suprafeei foii sunt definite, folosind comenzile Insert, Name, Define, de un nume precedat de numele foii plus semnul exclamrii.

6

Dac celula D1 corespunde denumirii A n suprafaa foii, numele ei ar trebui introdus ca PANEL 1,2!A, unde PANEL 1,2 este numele primei foi. n formulele din foaia PANEL 1,2, numele A corespunde celulei D2 a acelei foi. n alte foi numele A se poate referi la numele suprafeei unei foi pentru acea foaie sau la numele suprafeei unui registru de lucru. n foile registrului de lucru pentru acest capitol, toate numele sunt la nivelul foii. Numele suprafeei foii sunt urmate de numele foii cnd comenzile Insert, Name, Define, prezint numele existente. n calculele urmtoare la nceputul fiecrui an sau perioade va fi prezentat situaia i anii sau perioadele vor fi numite Anul 1, Anul 2, .a.m.d. anumite formule se pot schimba. Valoarea banilor nscris n contul bancar al acelei persoane, numit i flux monetar, la nceputul fiecrui an i creditul i dobnda de peste ani, apar n partea de calcule a foii de lucru. Pentru primul an, creditul care reprezint valoarea pentru care e pltit dobnda, dat de C5, e introdus ca = A, i dobnda din D5 ca = IR * C5. Fluxul monetar de la nceputul Anului 2 din celula B5 este dobnda pe Anul 1, dat de =D5. Creditul pentru Anul 2 l egaleaz pe cel din Anul 1 astfel nct C6 conine =C5. Dobnda din Anul 2 este, din nou, rata dobnzii nmulit cu creditul, =IR*C6 aa c D6 e copiat din D5. Apoi domeniul B6:D6 e copiat n B6:D9. La nceputul Anului 6, creditul e napoiat mpreun cu dobnda pe Anul 5, aa c B10 conine = C9 + D9. Celula B12 conine suma fluxului monetar, = SUM (B5:B10) care e copiat n D12 ca sum a dobnzilor de peste ani. Figura 1.2. ne d fluxul monetar, creditul i dobnda pe 6 ani. Acest tabel e obinut genernd un grafic cu benzi de la domeniul A4:D10, folosind prima linie pentru legend i prima coloan pentru valorile categoriilor. n acest caz, dobnda e pltit imediat dup ce a fost ctigat. Aceasta este numit dobnda simpl. Valoarea total a dobnzii primite pentru cei 5 ani e 5*80 = $400, care e obinut nsumnd fluxurile monetare sau coloana dobnzilor.

1.1.2. Dobnda compus Este de asemenea posibil ca n fiecare an dobnda s fie adugat la credit, aa c ea ctig dobnd, i ca singura plat s fie la nceputul Anului 6. Aceasta este numit dobnd compus i e ilustrat n Figura 1.3 n acest caz creditul pentru Anii 2-5 egaleaz creditul ultimului an plus dobnda pe acel an, astfel nct coninutul lui C6 este = C5 + D5, fiind copiat n jos. Domeniul B6:B9 este gol, n timp ce restul foii de lucru e la fel.

-$1,500

-$1,000

-$500

$0

$500

$1,000

$1,500

1 2 3 4 5 6

Flux monetarPrincipalDobanda

Figura 1.2 Flux monetar, Credit i dobnd pentru un titlu financiar

pe 5 ani cu Dobnd Simpl

7

Figura 1.3 Investiie cu Dobnd Compus

Figura 1.4 prezint fluxul monetar, creditul i dobnda n forma graficului cu bare. Sunt doar dou fluxuri monetare, unul la nceput i unul la sfrit. Este evident c banii depui cu dobnda compus acumuleaz dobnd mai repede dect cu dobnda simpl, pentru c nsumnd fluxurile monetare acum rezult $469, cu $69 mai mult dect la dobnda simpl. Algebric, e mai uor de vzut c n fiecare an creditul e multiplicat de factorul 1+r, aa c, dac o valoare A e depus cu dobnd compus cu o rat anual a dobnzii r, valoarea sa dup t ani va fi: A(1+r)t Aceast formul reprezint valoarea viitoare a sumei A dup t perioade ale investiiei cu dobnd compus la rata r.

-$1,500

-$1,000

-$500

$0

$500

$1,000

$1,500

$2,000

1 2 3 4 5 6

Flux monetarAnuitateDobanda

Figura 1.4 Flux monetar, Credit i dobnda pentru un titlu pe 5 ani cu Dobnda Compus

8

Figura 1.5 Rezultatele Dobnzii Compuse

Figura 1.5 prezint valorile viitoare pentru A = $1,000, i un numr de valori pentru t i r, n timp ce Figura 1.6 prezint aceste valori n forma grafic. Celula B4 conine formula

= A * (1+B$3)^$A4 care e copiat n B4:F9. Observai efectul unei rate a dobnzii mai mari pentru perioade mai lungi. Pentru r = 2-3% e nevoie de 30 de ani pentru a dubla valoarea original. Pentru r = 5%, e nevoie de 15 ani, pentru r = 10%, 7 ani i pentru r = 15%, doar 5 ani. Rata dobnzii pe termen lung a variat istoric de la 3% la 15%, cu o medie n jur de 10% pe ultimii 10 ani. Dar media ratei inflaiei pe ultimii 10 ani a fost de 5%, aa c rata dobnzii reale a fost n jur de 5%. Ratele dobnzii pot diferi pe termen scurt i lung. De exemplu, un certificat de investiie garantat pe un an poate da o dobnd de 6%, unul pe 5 ani de 7%, n timp ce un titlu guvernamental pe 10 ani poate da 8%. Urmtoarele sunt bazate pe o rat a dobnzii la fel pentru toate termenele.

Figura 1.6 Efectul Dobnzii Compuse

Formula valorii viitoare este, bineneles, valabil pentru toate tipurile de cretere procentual constant, precum creterea economic pe termen lung exprimat prin creterea Produsului intern brut (PIB) sau venitului pe cap de locuitor, inflaiei, creterii populaiei, folosirii resurselor i degradrii mediului, dac numim doar cteva. Din Figurile 1.5 i 1.6 observm c o cretere de 2-3% duce la o dublare ntr-o generaie, adic dup 30 ani, o cretere de 5% duce la dublare dup 15 ani, de 10% dup 7 ani i de 15% dup 5 ani.

$0$10,000$20,000$30,000$40,000$50,000$60,000$70,000

1 2 3 4 5 6

2%

3%

5%

10%

15%

9

1.1.3. Efectul schimbrii perioadei de compunere Ce se va ntmpla dac n loc s fie fcut n fiecare an, compunerea e fcut la fiecare jumtate de an? Dac rata anual a dobnzii e 8%, ea trebuie s fie 4% pentru jumtate de an, dar apoi vor fi de 2 ori mai multe perioade pentru care s se compun. Pentru exemplul pe 5 ani, valoarea total de plat va fi atunci:

pentru c e mai mult dect $1,469, s-ar putea s dorim s facem compunerea mai des de doua ori pe an. Compunnd de n ori pe an, rezultatul este:

76.469,1365

08,01000,1365*5

=

+ De exemplu, compunnd zilnic cu o rat anual de 8% rezultatul este:

tn

nrA

+1 Dac valoarea iniiala este A, rata dobnzii este 100% i numrul de ani este t, valoarea dup t ani compunnd de n ori pe an, este

Figura 1.7 Rata rentabilitii a $1,000 pe 5 ani pentru perioade diferite de compunere Pentru a afla ce se ntmpl pentru n, expresia de deasupra e rescris cu m=n/r.

rtrtm

mAe

mALim =

+

11

718.211 =

+=

m

m mALime

unde

este baza logaritmilor naturali.

24.480,1208,01000,1

10

=

+

10

Pentru A = 1,000, r=8% si t=5 ani, gsim 1491.82 O extindere a rezultatelor de compunere pentru diferite perioade cu rate anuale ale dobnzii variind ntre 2-20% e prezentat n Figura 1.7 Observai c diferite perioade de compunere duc la rezultate semnificativ diferite doar pentru rate ale dobnzii mai nalte. Unele bnci i fac reclam c conturile lor de economii sunt cu compunere zilnic. Ultima linie a tabelului arat creterea n schimbul compunerii zilnice fa de compunerea anual.

rrc +=

+ 112

)12(112

Faptul c multiple compuneri n cadrul unui an cresc rata rentabilitii e echivalent cu o cretere a ratei anuale a dobnzii. Pentru o rat anual a dobnzii cu compunere lunar rc (m), beneficiul dup un an e egal cu 1 + echivalentul ratei anuale a dobnzii cu compunerea anual, r:

( )1

1212

112

+= crr aadar: Dac compunerea are loc de n ori pe an cu o rat anual a dobnzii de rc(n) echivalentul ratei dobnzii cu compunere anual este:

1))(

1(' += ncnnr

r (1)

( )( ) 11)( == cr rEXPer c (2) Figura 1.8 prezint ratele dobnzii echivalente cu ratele anuale ale dobnzii cu compuneri

mai frecvente. Formula pentru celula C2 este:

= (1+C$1/$B2)^$B2-1 care e copiat n C2:16. Celula C7 conine formula = EXP (C$1)-1, care e copiat n dreapta. Observai c pentru rate mai joase, compunerile mai frecvente nu provoac o mare diferen,

dar la 10% diferena e aproape de 0,5%, la 14%, 1%, i la 20%, 2%

Figura 1.8 Rate ale dobnzii echivalente cu Ratele Anuale ale Dobnzii cu Compunere Pe de alt parte, s-ar putea s dorim s aflm rata anual cu compunere de n ori pe an, rc(n),

care e echivalent cu o rat anual r. Folosind ecuaia (1), avem

( ) ( ){ }11 /1 += nc rnnr Rezolvnd pentru rc(n), aflm:

Echivalentele pentru ratele anuale ale dobnzii sunt prezentate n Figura 1.9 Celula C2 e bazat pe formula:

( ) rnnr nc +=

+ 11

11

= $B2*((1+C$1)^(1/$B2)-1), care e copiat n C3:L6

( ) re cr += 1 Pentru cazul infinit avem din relaia (2): ( ) )1ln( rrc += astfel c: Celula C7 e dat de formula: =LN(1+C$1) care e copiat n dreapta Rezultatul e c n cazul n care compunerea are loc mai frecvent rata anual a dobnzii pe care sunt bazate plile ar trebui s fie mai sczut. De exemplu, dac avem un mprumut studenesc cu o rat simpl a dobnzii de 10%, i dobnda trebuie pltit lunar, calculele dobnzii ar trebui s fie bazate mai degrab pe o rat anual de 9,57% dect de 10%.

Figura 1.9 Rate Anuale echivalente ale Dobnzii Compuse

1.1.4. Valoarea viitoarea a plilor constante periodice Dac aceeai sum de bani este primit n fiecare an timp de mai muli ani, acest lucru se numete ANUITATE: o anuitate poate avea termen fix, adic suma de bani este pltit pe o perioad dat de ani, sau poate fi bazat pe durata de via a persoanei, situaie n care este pltit atta timp ct acea persoan triete. n cazul nostru vom opera doar cu anuiti la termen fix. Figura 1.10 prezint plile unei anuiti de 10 ani cu o sum anual de 10.000$. Aceleai valori pot reprezenta sume anuale puse ntr-un cont de economii. n ambele cazuri, ne putem ntreba care va fi la finele celor 10 ani, valoarea total viitoare a sumelor anuale dac rata dobnzii este de 8%. Aceast valoare viitoare poate fi calculat prin 3 metode : - Calculul explicit; - Calculul algebric; - Calculul pe baza formulei foii de calcul.

1.1.4.1. Calculul explicit

Aceast valoare viitoare poate fi obinut prin determinarea valorii viitoare a fiecrei sume anuale n Anul 10 i adunndu-le. Acest lucru este realizat n coloana C a tabelului 1.10. Celula C5 conine formula: =B5*(1+IR)^($A$14-$A5), care este copiat n domeniul C5:C14. Valoarea viitoare dorit va fi regsit ca suma acestui rnd, care este 144,866$ din care 44,866$ reprezint dobnda acumulat.

12

Figura 1.10 Aceeai schem se poate aplica unui mprumut constnd dintr-o sum constant anual, acordat pe o perioad de 4 ani. Atunci valoarea viitoare este valoarea total a mprumutului cnd ultima sum de bani este primit. Aceeai valoare viitoare poate fi utilizat pentru ceea ce se numete o ipotec invers. Pentru o astfel de ipotec se ntocmete un contract, prin care posesorul unei proprieti, de exemplu un pensionar, primete, s spunem, 10.000$ pe an pe o perioad de 10 ani, de la o instituie financiar. Dup 10 ani proprietatea este vndut i valoarea viitoare a anuitii se regsete n suma ncasat prin vnzare. Dac rata dobnzii este de 8%, valoarea viitoare a anuitii va fi 144,866$, dup cum se vede n figura 1.10. Sumele anuale primite sunt de fapt mprumuturi n care proprietatea este garania mprumutului.

1.1.4.2 Calculul algebric

Foaia de lucru face mai uoar realizarea calculelor cerute, dar este posibil s obinem o formul algebric mult mai concis. Considerm cantitatea anual a anuitii A, durata ei (numrul de ani) t i rata dobnzii r. Dup un an suma va fi A, dup 2 ani va fi A+(1+r)A, dup 3 ani va fi A+(1+r)2 A2 i dup t ani:

{1+(1+r)+(1+r)2++(1+r)t-1}A=(1+R+R2++Rt-1)A, (3) unde R=1+r. Formula pentru nsumarea seriilor geometrice cu raia R, primul termen F i ultimul termen L, este :

RRLF

1,

13

Figura 1.11

$0$20,000$40,000$60,000$80,000

$100,000$120,000$140,000$160,000$180,000$200,000

5 10 15 20 25 30

Anul

Valo

area

viit

oare 2%

3%5%10%15%

Figura 1.12 Vom avea atunci pentru (3):

,111

1))((1 1 A

rRA

RRA

RRR ttt =

=

de unde rezult c formula pentru valoarea viitoare, notat cu Pt este :

Pt= .*)/)1)1(((1)1( ^ ArtrArr t +=+ (4)

1.1.4.3. Calculul pe baza formulei din foaia de calcul

Formula (4) este inclus ntre funciile foii de calcul ca formula valorii viitoare: =FV(Rata dobnzii, Durata, - Cantitate anual)=FV(r, t, -A)

Reinei faptul c anuitatea ar trebui introdus cu semnul minus, deoarece aceasta poate fi considerat o plat, ce duce la un flux de numerar pozitiv.

n tabelul 1.10, celulei B4 i este atribuit formula : =FV(B$3, $A4, -A), care este copiat la B4:F9. Tabelurile 1.11 i 1.12 ofer valorile viitoare ale amortismente pentru un numr de ani ce variaz de la 1-30, i pentru rate ale dobnzii ce variaz de la 2-15%. Cu economii anuale de 1000$, poate fi atins o sum de 100000$ n 20 ani dac rata dobnzii este 15%, sau n 25 ani, pentru o rat de 10%.

14

1.1.4.4. Calculul amortismentului

Ce valoare ar trebui s aib A pentru a obine o valoare Pt dat? Obinem valoarea lui A rezolvnd (3):

A= .1)1( ttP

rr

+

De exemplu, pentru a acumula 1.000.000$ n 20 de ani, cnd rata dobnzii este 5%, avem nevoie

de economii anuale de 30.243$.

Tabelul 5.13 ofer, pentru o valoare viitoare de 1.000.000$, anuitile necesare pentru diferite rate a dobnzii i durate. Celula B5 conine formula:

=PT*B$3/((1+B$3)^$A4-1), care este copiat la B4:F9.

Tabelul 5.14 ofer aceleai rezultate sub form grafic. Pentru o rat a dobnzii sau

randament de 10%, economii anuale de aproape 6000$ sunt suficiente pentru a deveni un milionar n 30 ani.

1.1.5. Caracteristicile foii de calcul

1.1.5.1. Comanda Fill n multe cazuri o foaie de calcul conine coloane sau rnduri cu numere cum ar fi 1 2 3 4 sau 1994 1995 2019. Acest lucru poate fi realizat introducnd mai nti n primele dou celule ale rndului numerele corespunztoare cum sunt 1,2, i apoi, dup ce selectm tot rndul ce urmeaz a fi completat, utiliznd comanda Edit, Fill, Auto Fill. Alternativ, prima valoare din domeniul poate fi introdus i apoi selectat domeniul, dup care este selectat comanda Edit , Fill, Series cu o valoarea pasului. Type Linear genereaz serii aritmetice. O serie geometric poate fi generat utiliznd Type Growth.

1.1.5.2 Utilizarea creativ a opiunii de copiere

Mai devreme s-a artat c multe pri ale foii de calcul pot fi copiate din alte pri, astfel crendu-se foi de calcul mari cu un efort relativ redus. Dar comanda Copy poate fi utilizat i n alte moduri creative, dup cum urmeaz.

15

Figura 1.13

$0$20,000$40,000$60,000$80,000

$100,000$120,000$140,000$160,000$180,000$200,000

5 10 15 20 25 30

Anul

Sum

a an

uala 2%

3%5%10%15%

Figura 1.14

Figura 1.15

1.1.5.2.1. Suma cumulat Lum n considerare un cont curent cu o balan iniial i un numr de nregistrri ce urmeaz a fi introduse sau sczute, vezi tabelul 5.15.

16

Dup introducerea fiecrei nregistrri ar trebui s vedem balana. Acest calcul necesit suma cumulat a tuturor articolelor. Prin urmare, celula C3 ar trebui s conin =C2+B3, celula C4, =C3+B4, .a.m.d. Pentru a evita introducerea separat n fiecare celul, doar celula C3 va fi completat manual, iar apoi coninutul ei va fi copiat n C3:C6. Deoarece foaia de calcul utilizeaz adrese de celule relative cnd copiaz, pentru C4 coninutul lui C3 va fi introdus ca =C3+B4 .a.m.d. Dac C3 ar fi fost goal, rezultatul ar fi fost acelai, deoarece celulele goale au valoare numeric 0. Celulele ce au o etichet au de asemenea valoarea 0, astfel nct, dac rndul 2 este ters, celula C2 care rezult de aici are formula = C1+B2 i afieaz valoarea 1000.

1.1.5.2.2. Prima scdere

Dac se cunoate coninutul coloanei Balan, dar cel al coloanei Cantitate nu se cunoate, poate fi calculat printr-o metod asemntoare. Celula C3 va fi atunci =B3-B2, iar aceast formul este copiat de sus n jos, vezi Tabelul 5.16. n acest mod, primele scderi ntr-o serie pot fi generate uor.

Figura 1.16

1.1.5.2.3. Seriile geometrice i aritmetice

Copierea cu referine relative de celul poate fi utilizat pentru generarea de serii aritmetice i geometrice. De exemplu, dac este nevoie de factorii dobnzii compuse 1, 1+r, (1+r)2, , vom introduce 1 n A1, =(1+r)* A1 n A2 i apoi vom copia A2 de sus n jos, ceea ce va duce la seria geometric necesar, vezi tabelul 5.17. Pentru o serie aritmetic se nlocuiete * cu +.

Figura 1.17 Probleme

1. O persoan se decide s economiseasc n fiecare lun 100$, pentru un anumit numr de ani. Rata dobnzii este dat ca un procent anual ce se calculeaz lunar. Utiliznd funcia valorii viitoare , creai un tabel pentru valoarea economiilor peste 15-25 de ani, de la an la an, pentru rate ale dobnzilor de 8-12%, ce variaz cu 0,5%.

17

2. Fondul de studiu

Tocmai ai devenit printe. Vrei ca, la vrsta de 19 ani copilul s dispun de un fond de studiu de 40000$. ncepei s depunei o sum fix de bani n acest fond n fiecare an de la naterea copilului.

a) Ct ar trebui s depunei n acest fond n fiecare an dac rata dobnzii este 8%? b) Aflai valoarea viitoare a fiecrei contribuii anuale i valoarea total corespondent. c) Considernd c la 19, 20, 21 i 22 de ani este nevoie de 10000$, care sunt sumele

anuale? d) Considerai c un plus de 1000$ este pus la natere de o alt persoan. Care va fi atunci

rspunsul la punctul c? e) ntrebarea de mai sus presupune faptul c este posibil s investeti la aceeai rat a

dobnzii n fiecare an. Este acest lucru realistic? Dac ratele dobnzilor se modific n timp, cum pot fi atinse scopurile fondului de studiu?

f) Cum poate fi luat n considerare inflaia? Care ar fi rspunsurile la ntrebarea c) pentru rate ale dobnzii reale de 3%, 4% i 5%?

1.2. mprumuturi ipotecare

Acest capitol prezint elementele de baz ale mprumuturilor i mprumuturile ipotecare. O

atenie deosebit este dat mprumuturilor ipotecare, ntru-ct muli oameni sunt implicai n aceast problem. Se explic mprumuturile cu rate constante i cu plat total constant. Sunt deduse formulele pentru plata total constant a ratelor de mprumut i pentru plile dobnzii. Perioadele de amortizare i termenele mprumuturilor ipotecare sunt prezentate separat. Formulele referitoare la ratele dobnzii compuse pentru diferite perioade sunt utilizate pentru a calcula plata ipotecilor lunare.

n acest capitol vei nva urmtoarele comenzi i funcii ale foii de calcul.

PMT (rata dobnzii, numrul perioadelor, credit)

1.2.1 mprumuturi cu rambursri ntr-o singur tran.

Un mprumut este un contract ntre debitor i creditor. Creditorul i furnizeaz o sum de bani debitorului, care este restituit mai trziu, i a crei dobnd este pltit. n majoritatea cazurilor se fixeaz o rat a dobnzii precum i termenele de plat ale acestora. n funcie de termenele de plat stabilite se disting diferite tipuri de mprumuturi i anume, mprumuturi cu rambursri ntr-o singur tran, mprumuturi cu rambursri n trane constante periodice, i mprumuturi cu anuiti constante.

Din punctul de vedere al bnci sau al oricrui debitor un mprumut poate fi privit ca o investiie cu dobnd fix. De exemplu, dac o banc vinde un GIC de 5 ani de 1000 $ cu o rat a dobnzii de 8%, ea de fapt mprumut de la cumprtor 1000$, pentru care pltete 8% dobnd, i cruia i napoiaz banii dup 5 ani. Deci banca primete la nceputul anului 1, 1000$, pltete 8% din 1000$ sau 80$ n fiecare din urmtorii 4 ani, n timp ce n ultimul an pltete 80$ plus creditul mprumutat (vezi figura 1.18, care este la fel ca figura 1.1 din capitolul precedent, exceptnd faptul c toate fluxurile monetare au semnul opus). Figura 1.18a prezint sumele iar figura 1.18b formulele.

18

Figura 1.19. prezint graficul corespunztor fluxurilor monetare. Ea indic faptul c debitorul primete o sum iniial, care trebuie rambursat mpreun cu dobnda n urmtoarele perioade. Acesta este un model tipic de mprumut n care se primete doar o sum iniial. Majoritatea mprumuturilor ce vor fi discutate sunt de acest tip.

Suma mprumutat este numit credit. Fluxurile monetare negative ale urmtoarelor perioade constituie dobnda i plata creditului. Plata este numit i amortizarea mprumutului. Amortizarea poate lua forme diferite. Numrul perioadelor n care creditul este rambursat se numete perioad de amortizare.

n cazul de mai sus ntregul credit a fost pltit dup 5 ani i dobnda a fost pltit la fiecare perioad intermediar i n ultima perioad. ntruct doar dobnda este pltit creditorului n fiecare an, creditul rmne acelai, i rata dobnzii trebuie s fie egal n fiecare an.

Figura 1.18a GCI ca mprumut (prezentare numeric

Figura 1.18b GCI ca mprumut (prezentare analitic)

Un caz special al acestui tip de mprumut este atunci cnd un mprumut nu este niciodat rambursat, ceea ce nseamn c perioada de amortizare este infinit. Atunci ratele dobnzilor tind la infinit. creanele de acest fel, numite perpetue, au fost folosite cndva de guvernul Regatului Unit. De atunci nu au mai fost folosite pentru c au fost rscumprate debitorului.

19

$1,500

$1,000

$500

$0

$500

$1,000

$1,500

1 2 3 4 5 6

ani

FLU

X M

ON

ETA

R

flux monetar

Figura 1.19 Fluxul monetar al debitorului

1.2.2 Rambursri cu amortismente constante.

Se consider urmtorul caz n care rambursarea este mprit ntr-un anumit numr de perioade. Figurile 1.20 i 1.21 prezint acest lucru pentru aceleai date ca n figura 1.18. Perioada de amortizare este de 5 ani, i rata n fiecare an este deci 1000/5=200, astfel c celulele E6:E10 conin formula

=A/AP.

Creditul din anul 2 l egaleaz pe cel din anul 1, minus amortismentul, aadar C7 are formula

=C6 E6.

Formula dobnzii n celula D6 este

=IR*C6.

Fluxul monetar pentru anul 2 egaleaz (minus) anuitatea anului 1, aadar formula pentru B7 este

= (D6 + E6).

Aceste formule pot fi copiate de sus n jos. Aceasta conduce la un credit 0 n anul 6, aadar mprumutul a fost n ntregime pltit. Observai faptul c suma total a dobnzii pltite este acum de 240$ fa de 400$ n cazul precedent, ceea ce este explicat de ratele pariale anticipate.

mprumutul cu rat constant este mai atractiv pentru debitor dect mprumutul cu o singur rat, pentru c ratele sunt desfurate n timp i sunt de asemenea mai mici, fiind, pentru muli oameni, mai uor de utilizat.

20

Figura 1.20. Amortizarea mprumutului cu amortismente constante.

.

$400

$200

$0

$200

$400

$600

$800

$1,000

$1,200

1 2 3 4 5 6

ani

flux

mon

etar

flux monetar

Figura 1.21. Fluxul monetar pentru mprumuturi cu amortismente constante

1.2.3. Rambursri cu anuiti constante. mprumuturile cu rate egale au aceeai anuitate n fiecare an, dar ratele dobnzii descresc cu

timpul, iar creditul devine mai mic. n exemplul din figura 1.20., ratele dobnzii variaz de la 80$ la 16$, aadar anuitatea la sfritul anului 1 este de 280$, iar la sfritul anului 5 de 216$. Poate fi mai convenabil pentru creditori s aib un tabel de amortizare ntruct anuitile sunt identice n fiecare an. Problema const n calcularea acestei anuiti. n primul rnd se va deduce o formul algebric.

S zicem c aceast anuitate este A, creditul mprumutat iniial P, perioada de amortizare t ani, i rata dobnzii r. Suma datorat dup t ani este, conform formulei dobnzii compuse:

(1+r)tP.

Aceast sum trebuie s fie egal cu valoarea anuitilor viitoare A timp de t ani, [(1+r)t1]/r*A,

vezi formula (3) din capitolul precedent. Deci avem ecuaia:

(1+r)tP=[(1+r)t1]/r*A.

Substituind 1+r=R, putem scrie aceasta ca

21

RtP=(Rt1)/r*A

Rezolvnd pentru A, gsim:

A={(rRt)/(Rt1)}P={r/[1(1+r)-t]}*P=(r/(11+r)-t))P. (1)

Formula poate fi scris n foaia de calcul astfel:

=P*IR/(1(1+IR)^AP)

n cazul de fa, avem P=1000, r=8%, i t=5, astfel

A=0,08/(1(1+0,08)-5)*1000=250,46.

O funcie special a foii de calcul. PMT(r,t,P)=PMT(IR,AP, P)

d acelai rezultat. Se observ semnul minus n faa lui P. Aceasta le permite lui A i lui P s aib semne opuse, reflectnd faptul c unul reprezint plat i cellalt ncasare.

1.2.4. mprumuturi ipotecare.

Cnd o proprietate imobiliar este cumprat, o parte a preului de achiziie este de obicei finanat printr-un mprumut ipotecar. Acesta este un mprumut n care proprietatea imobiliar servete drept garanie pentru rambursare mprumutului i plata dobnzii. n cazul neplii, creditorul are dreptul de a vinde proprietatea n locul obinerii sumei datorate.

Ca exemplu, fie un mprumut ipotecar cu un capital de 50.000$ ce trebuie rambursat, sau amortizat, n 10 ani, astfel c perioada de amortizare este 10 ani. Pentru trane egale, amortismentul de 50.000/10=5.000$. Rata dobnzii este 10%. Aceast rat a dobnzii este fixat pentru termenul ipotecii. Se presupune c termenul este acelai cu perioada de amortizare, sau, dac sumele sunt egale, ipoteca poate fi rennoit la aceeai rat a dobnzii, dup o scurt perioad.

Foaia de calcul de la 1.22. prezint calculaia ipotecii. Ratele sunt mereu 5.000$. creditul fiecrui an egaleaz creditul ultimului an, minus anuitatea, aadar celula B7 are ca formule B6-C6, care este copiat la B7:B15. Dobnda este egal cu 10% din credit aadar C6 are ca formule B6*IR, care este copiat de sus n jos. n final, anuitate egaleaz suma rmas de plat.

22

Figura 1.22a Amortizarea ipotecii cu amortismente constante (prezentare numeric)

Figura 1.22b Amortizarea ipotecii cu amortismente constante (prezentare analitic)

Dezavantajul acestui tabel este c sumele de plat variaz de la 10.000$ n anul 1 la 5.500$

n anul 10. Din acest motiv, tabelul de amortizare n cazul mprumuturilor rambursabile prin rate constante este mai atractiv.

23

Figura 1.23. Rambursarea ipotecii cu amortismente constante

Amortizarea corespunztoare a ipotecii este dat n figura 1.24.

$0$1,000$2,000$3,000$4,000$5,000$6,000$7,000$8,000$9,000

1 2 3 4 5 6 7 8 9 10 11

ani

dobndamortismente

Figura 1.24 Amortizarea ipotecii cu anuiti constante

24

Figura 1.25 Anuiti constante

Formula (1) este inclus n funcia PMT:

=PMT(Rata Dobnzii, Termen, Credit)

Pentru exemplul pe care l avem n E6:E15:

=PMT(IR, AP, P) = 8,137.

Suma din D6 este anuiti minus dobnda, adic E6C6. Dup anuitatea constante din anul 10, creditul este 0.

Cu amortismente constante de 5.000$ pe an creditul descrete linear de la 50.000$ la 0 n decursul celor 10 ani. Acest lucru nu este valabil pentru anuiti constante, ntruct n anul iniial ratele dobnzii sunt mari i amortismente mici, spre deosebire de ultimul an al mprumutului. Figurile 1.9 i 1.10 prezint creditul rmas pentru ambele tipuri de finanare ipotecar. Observai faptul c sunt necesari 5 ani pentru a napoia primii 20.000$ ai mprumutului. Pentru perioade mai lungi i rate ale dobnzii mai mari acest fapt este chiar mai evident.

Observai faptul c suma ratelor dobnzii este diferit pentru cele dou tabele: 27.500$ n cazul amortismentelor constante fa de 31.380 pentru anuiti constante. Diferena se datoreaz faptului c n ultimul caz creditul este napoiat mai ncet, astfel nct se datoreaz o dobnd mai mare. Pentru ambele metode valoarea capitalului este aceeai.

Figura 1.26 Dou metode de rambursare a creditului rmas

25

$0

$10,000

$20,000

$30,000

$40,000

$50,000

$60,000

1 2 3 4 5 6 7 8 9 10 11

ani

cred

it

Figura 1.27 Pli difereniate ale prii din credit rmas nepltit

1.2.5 Rate lunare ipotecare Majoritatea ipotecilor contractate pe case sunt cu rate ce se pltesc lunar. Acestea ar putea fi aproximate mprind anuitatea la12: 8,137/12=678,11$. Totui, dac aceste sume sunt pltite anticipat anuitii, proprietarul casei ipotecate i pierde interesul privind plata sumelor anticipate. De aceea vom trece la folosirea perioadelor lunare, astfel nct rata dobnzii s fie 10/12% i numrul de perioade s fie 10*12. Folosind formula pentru plii (4), sau funcia de pe foaia de calcul, gsim: =PMT(0.1/12,12*10,-50000)=$660.75. Dar aceste sume urmeaz s fie recalculate de 12 ori pe an la rata dobnzii anuale de 10%, ceea ce este mai costisitor. Se dorete ca rata dobnzii anuale recalculat lunar s fie echivalent cu calculul anual al dobnzii la o rat de 10%. Dac rata dobnzii cutat este notat r c (m), atunci r c (m) se determin, conform explicaiilor anterioare, astfel: r c (m)=12((1+0.10)

12/1 -1), Ca urmare rata lunar a dobnzii corespunztoare este:

12)(mrc =(1+0.10) 12/1 -1=0.0080.

Aceasta poate fi nlocuit n funcia plii (4) sau n funcia de pe foaia de calcul: =PMT(1.1^(1/12)-1,12*10,-50000)=$648.88. Aceasta reprezint cu 4,5% mai puin dect prima aproximare. In anumite cazuri, cota ratei dobnzii ipotecare se bazeaz pe plata a jumtate din anuitate. n astfel de cazuri, aceasta rat trebuie mai nti s fie convertit conform ratei anuale, folosind formula:

26

r a =(1+r b /2)

2 -1, Unde r a a reprezint rata anual si este jumtate din anuitate (nlocuind rata anual). Pentru 10%, vom obine: r a =1.05

2 -1=10.25% Anuitile corespunztoare pentru fiecare lun devin: =PMT(1.1025^(1/12)-1,10*12,-P)=$655.17, Ceea ce este cu aproape 1% mai mult dect suma de 648,88$, calculat la o rata anual de 10%

1.2.6 Durata mprumutului ipotecar Durata mprumutului ipotecar reprezint perioada pentru care este negociat rata dobnzii. Durata poate fi egal cu perioada amortizrii, dar de obicei este mai scurt. n timp ce perioada amortizrii poate dura pn la 25 de ani, durata, n cele mai multe cazuri nu poate depi 10 ani. Durata unei ipoteci este important, deoarece rata dobnzii se poate schimba pe parcursul acesteia, astfel, atunci cnd rata dobnzii este negociat pentru o alt durat, plile lunare se schimb. Rata dobnzii poate fi diferit pentru durate diferite. Figura 1.28 prezint ratele pentru durate care variaz de la 6 luni la 10 ani la un anumit moment. Pentru 10 ani, rata este cu 50% mai mare dect pentru 6 luni.

Figura 1.28 Rate ale dobnzii pentru durate diferite. Figura 1.29 prezint o expunere grafic folosind un sistem de axe XOY sau scatter graph. Alegerea unei durate mai lungi ofer protecie pentru viitoarele creteri ale ratei dobnzii, n timp ce o durat scurt i ofer avantajul ratelor mai mici.

27

0%100%200%300%400%500%600%700%800%900%

1000%

1 2 3 4 5 6 7 8

an

rata

dob

anzi

i

Figura 1.29 Rate ale dobnzii i durate ale mprumuturilor ipotecare Figurile 1.30. si 1.31. exemplific cele discutate n cazul n care creditul este 100.000$ i perioada de amortizare de 25 de ani. Se presupune c ratele dobnzii sunt alctuite din cote bianuale, dar exprimate n procente anuale. Dac ratele bianuale variaz de la 5% la 15 %, ratele anuale corespunztoare variaz de la 5,06% la 15,56%, vezi B7/B17, unde B7 are formula: =(1+A7/2)^2-1 Plile lunare reprezentate n coloana C sunt obinute folosind formula pentru C7: =PMT((1+B7^(1/12)-1.AP*12,-P).

Figura 1.30 Plile lunare cu rate ale dobnzii ce variaz

28

Se poate observa c plile lunare variaz foarte mult n funcie de rata dobnzii. Un mprumut ipotecar pe care i-l poi permite cu uurin la o rat a dobnzii de 6% pltit n rate lunare de 640$, poate fi peste mna la o rat a dobnzii de 12% cu rate lunare de 1.032$. O cretere a ratei dobnzii cu 1% produce o cretere a ratei lunare cu 60-70$. De obicei, duratele vor fi alese pe baza capacitii de plat a ratelor lunare i pe baza previziunilor privind ratele dobnzii. Pentru perioadele de amortizare mai scurte , plile lunare vor fi mai puin sensibile la rata dobnzii, deoarece partea rambursat din plata total va fi mai mare i partea de dobnd mai mic.

Figura 1.31 Relaia dintre plile lunare i rata dobnzii Probleme 1. S se creeze un tabel de pli lunare pentru o rat a dobnzii bianual ce variaz de la 6% la

12% i creditul variind ntre 60.000-120.000$, presupunnd c perioada de amortizare este de 20 de ani.

2. S se creeze un tabel de pli lunare pentru o rat a dobnzii bianual ce variaz de la 6% la 12% i cu perioade de amortizare ce variaz ntre 15-25 de ani, presupunnd c suma iniial este de 100.000$.

3. S se creeze un tabel de pli lunare ale creditului ce variaz ntre 60.000$-120.000$ i cu perioade de amortizare variind ntre 15-25 de ani, presupunnd c rata dobnzii bianual este 7%.

4. S se gseasc n cazul figurii 1.7 alocarea unor pli sptmnale sau la 2 sptmni. 5. Ce combinaii ale creditului i ale perioadei de amortizare ne putem permite pentru plata lunar

de 1000$ i o rat a dobnzii de 7%? 6. S se gseasc mrimea plilor lunare pentru ratele dobnzii din tabloul 6.5, avnd un credit de

100.000$ i o perioad de amortizare de 25 de ani. 7. Pentru o plat lunar n valoare de 1.000$ i pentru o perioad de amortizare de 25 de ani,

utiliznd rate ale dobnzii corespunztoare duratelor din figura 1.23, stabilii ce valori ale creditului ni le putem permite.

8. Finanarea ipotecilor

$0

$200

$400

$600

$800

$1,000

$1,200

$1,400

1 2 3 4 5 6 7 8 9 10 11 rata bi-anuala

plat

i ipo

teca

re

29

Se presupune c un cuplu cumpr o cas cu un mprumut ipotecar n valoare de 100.000$, contractat pe un termen de 20 de ani. Rata cuvenit a dobnzii este de 8,75%. Ipoteca este pltit folosind o sum anual pentru dobnd i pentru amortismente constante.

a. Construii tabelul de amortizare al ipotecii, alctuit din credit, dobnd, amortisment i anuitate pe o perioad de 20 de ani. Folosii o seciune de date cu un domeniu de nume pentru date. Tabelul ar trebui s fie bine alctuit, cu formatele i protecia corespunztoare.

b. Editai tabelul cu rambursarea ipotecii. c. (i) Care va fi valoarea anuitilor dup 20 de ani?

(ii) Dac n loc de anuiti s-ar efectua pli lunare, care este echivalentul n cazul plilor lunare?

(iii) Pentru o rat anual de 8,75%, ce mprumut ipotecar acordat poate fi obinut cu anuiti constante valornd 12.000$ pe parcursul a 20 de ani? (iv) Care este mprumutul ipotecar acordat pentru pli lunare n valoare de 1.000$? (v) Pentru ce valoare a ratei dobnzii, un mprumut de 100.000$ are o rat lunar de 1.000$ pe parcursul a 20 de ani?

d. Se presupune c rata dobnzii variaz la fiecare 5 ani, iar n ultimele 4 perioade de 5 ani ia valorile 8,75%, 11,75%, 9,25% i 7,50%. Rambursrile n primii ani sunt fcute ca i cnd rata dobnzii ar fi fost 8,75% pentru durata urmtorilor 20 de ani, n urmtorii 5 ani se presupune c rata dobnzii ar fi fost de 11,75% pentru urmtorii 15 ani rmai etc. Construii tabelul amortizrii anuale a ipotecii folosind o seciune extins de date.

e. Modificai aceast structur prin implementare folosind IF, avnd n vedere c anuitile nu depesc cel mult 12.000$ n oricare dintre ani. Folosii o celul special pentru afiarea maximului anuitii. Pentru ce valoare maxim poate fi pltit o ipotec n doar 20 de ani?

9. Cazul achiziionrii imobilului Un cuplu intenioneaz s achiziioneze o cas n valoare de 100.000$, ce va fi pltit dintr-un capital propriu de 50.000$ i dintr-o ipotec valornd 50.000$. Rata dobnzii pentru ipotec este de 10% pe an, cu anuiti constante. Perioada de amortizare este de 25 de ani.

a. ntocmii o foaie de calcul n care s indicai pentru fiecare an creditul nepltit, dobnda scadent, amortismentele i anuitile cu rate constante. Folosii o seciune special pentru date, care ar trebui s aib domenii de nume. Imprimai foaia de calcul.

b. Presupunem c valoarea proprietii crete cu 5% n fiecare an. Determinai valoarea pe care o va avea casa peste 25 de ani i valoarea efectiv a casei (valoarea casei minus creditului rmas de rambursat).

c. Presupunem c impozitele i costurile de ntreinere n acest an sunt de 3.000$ i c n anii urmtori vor crete cu 3% pe an. Presupunem c aceast cas va fi vndut n 2016 la valoarea pe care o va avea la acel moment. Care va fi valoarea costurilor totale dup 25 de ani de folosin a casei? Orice cost aprut n primii ani va trebui calculat n anul n care casa va fi vndut la o rat a dobnzii de 10%?

d. n loc de a deine o cas pe o perioad de 25 de ani, aceasta ar putea fi nchiriat cu 12.000$ pe an, chiria crescnd cu 6% pe an. Care va fi valoarea total a costurilor dup 25 de ani de nchiriere? Care este relaia de ordine dintre acest lucru i deinerea casei n proprietate?

30

1.3.Valoarea actualizat a rambursrilor Acest capitol introduce conceptul de actualizare i valoare actualizat aplicate sumelor constante care sunt achitate dup termen, i asociate cu exemple. n primul rnd sunt explicate conceptele de actualizare i valoare actualizat. Apoi sunt deduse formule pentru valoarea actualizat a creditelor anuale i a altor anuiti. Se ofer o prezentare de ansamblu a diverselor situaii, contracte, i formule asociate fluxurilor monetare constante. n final, se analizeaz o cerere de pensionare n acest capitol vei nva urmtoarele comenzi i funcii ale foii de calcul: PV (rata dobnzii, durata, suma de plat).

1.3.1 Actualizarea i valoarea actualizat Se presupune c exist posibilitatea de achiziionare a unui bun care pe durata unui an poate fi vndut la 1.000$. Ct suntem dispui s pltim acum? La aceast ntrebare se poate rspunde dac se cunoate ct de mult suntem dispui s pltim acum pentru a obine 1$ anul viitor. Se presupune c suma actual de bani este depus ntr-un cont de economii cu o dobnd de r% pe an. Aceasta nseamn c 1$ pltit acum aduce 1$+r ( unde r este exprimat unitar, astfel nct 5% nseamn 0,05) un an mai trziu. Deci, mprind cu 1+r, avem c 1$/ (1+r) n prezent este echivalentul a 1$ un an mai trziu. Astfel, se poate concluziona c 1.000$/(1+r) n momentul de fa este echivalentul a 1.000$ un an mai trziu. Dac cea mai bun alternativ de utilizare a banilor este un cont de economii cu o dobnd de r%, costul de oportunitate a 1$ n prezent este 1+r n uniti monetare n anul viitor. Dac X este suma n dolari pe care suntem dispui s o pltim n prezent pentru a obine 1.000$ anul viitor, avem pentru aceast sum: X(1+r) = 1.000, astfel nct

X =r+1

000.1 .

X este definit ca valoarea actualizat a 1.000$. Pentru r=0,1 rezult c X=1.000/(1.1) = 909,09. Suma de 1.000$ este actualizat la 909,09 prin nmulirea cu factorul de actualizare

r+1

1 = 0,90909.

Dac se preconizeaz s se obin 1.000$ n doi ani, valoarea prezent este supus dobnzii compuse: X(1+r)2 =1.000, astfel nct,

X = 2)1(000.1

r+ . Pentru o perioad de doi ani factorul de actualizare este 1/(1,1)2 = 0,826.

31

n general, valoarea actualizat a unei sume de bani A disponibil dup t ani, pentru o rat a dobnzii r, este:

trA

)1( + . Actualizarea sau ncasarea valorii actualizate este evident procesul invers al dobnzii compuse. Figura 1.31 prezint valoarea actualizat a 1.000$ pentru diferite rate ale dobnzii i pentru un numr diferit de ani. C1 reprezint suma A i B4 conine formula A/(1+$A4)^B$3, care este copiat n B4:O9. Figura 1.32 prezint aceeai informaie sub form grafic. De o importan practic deosebit este scderea rapid a valorii actualizate pentru un t cresctor cnd ratele dobnzii depesc, s zicem, 5%. O valoare actualizat sczut nseamn c suma de 1.000$ este mai puin semnificativ. Cu rate ale dobnzii mai mari de 10%, sumele viitoare devin nesemnificative peste 20 de ani.

Figura 1.31 Valoarea actualizat a 1.000 $ pentru rate ale dobnzii i ani diferii Figura 1.32 prezint aceleai rezultate sub form grafic. Pentru o rat a dobnzii de 10% sau mai mare, sumele n anul 7 sau mai trziu sunt mai mult dect njumtite i tind ctre valoarea nul. Reducnd sumele cu rate ale dobnzii mai mari se ajunge la o micorare astfel nct numai sumele din viitorul apropiat conteaz. Numai rate ale dobnzii de 2% fac s conteze intervalele de timp, considerate ca generaii, de circa 30 de ani. A se nota c s-a considerat implicit aceeai rat a dobnzii pentru toi anii. n anumite cazuri acest lucru poate s nu fie corect, mai ales dac sunt implicate ambele fluxuri monetare din primii ani i 10 sau 20 de ani mai trziu, pentru c ratele dobnzii la datoria public pe durate de timp diferite variaz.

Figura 1.32 Valoarea actualizat a 1.000$

32

1.3.2. Valoarea actualizat a creditelor de studiu i anuitile Se presupune c se acord un credit de studiu de 10.000$ pe an pentru fiecare din urmtorii 10 ani. Ct valoreaz acesta acum dac rata dobnzii n vederea reducerii este de 5%? O serie de pli egale de-a lungul unui numr de ani se numete anuitate, iar numrul de ani de plat reprezint durata anuitii. Cteodat o anuitate se achit atta timp ct persoana respectiv este n via, dar nu vom lua n considerare astfel de anuiti aici. n primul rnd este prezentat o foaie de calcul pentru aceast problem, apoi o abordare algebric, i n final funcia corespunztoare foii de calcul. Figura 1.33 prezint fluxul monetar al anuitii aplicate pe 10 ani, att n prezentare numeric ct i analitic, vezi de asemenea figura 1.34. Oricare dintre sume poate fi actualizat la anul 0, lucru care se realizeaz prin mprirea sumei n anul t prin (1+r) t . Celula C5 are ca formul =B5/(1+IR)^A5, care este copiat n jos. Rezultatul sumei este 77.217$, care este valoarea actualizat pentru 10 ani de plat. Este mult mai convenabil s utilizm o formul algebric care este dedus dup cum urmeaz. Fie suma de plat A, rata dobnzii r, si durata t. Valoarea actualizat a 1$ de plat n Anul 1 este:

d=r+1

1 ,

i deci 1$ de plat n anul t este d t . Deci valoarea actualizat total P0 pentru suma A, achitat n fiecare an timp de t ani este:

P0 = (d+d2 ++dt )A= ddd t

1)1( A=

rd t )1( A. (1)

Figura 1.33.a: Valoarea actualizat a unui credit anual (prezentare numeric)

33

Figura 1.33.b Valoarea actualizat a unui credit anual (prezentare analitic)

Figura 1.34. Flux monetar actualizat i neactualizat pentru o anuitate

Expresia de dup cea de-a doua egalitate este obinut folosind formula seriei geometrice. Pentru A=10.000, r=5%, i 10 ani am obinut P0= 77.217$. Pentru o durat a anuitii t obinem pentru anuitate valoarea actualizat: P0 = r

A = 100.000$.

Aceast formul este adevrat pentru aa-numitele datorii perpetue care nu sunt niciodat restuite, dar pentru care se continu s se plteasc aceeai rat a dobnzii. Formula (1) este asociat formulei (4) din capitolul anterior:

A= trr

+ )1(1 P0 . Relaia dintre P i A este aceeasi, doar c este rezolvat pentru valoarea actualizat P0 n (1), i pentru A n (4). Formula (1) este implementat n foaia de calcul ca funcie PV care are trei argumente: PV(dobnda, durata, -suma).

34

Pentru exemplul nostru avem: PV(r,t,-A) = PV(10%,10,-10.000) = 77.217$. Anuitile sunt acordate, de obicei, indivizilor de ctre bnci sau alte instituii financiare cu preul egal cu valoarea actualizat dat n relaia (1). Anuitile cu termen fixat sunt pentru bnci similare cu GIC doar c sunt implicate durate diferite.

Figura 1.35 Calculul plii unei datorii

Figura 1.36 Flux monetar pentru 1.000$ datorie

35

Aceleai formule pot fi utilizate pentru a evalua plata datoriilor. Se presupune c 1.000$ datorie pot fi rscumprai la 1.075$. Acesta aduce un beneficiu cu o dobnd anual de 5% i va fi restituit dup 10 ani. Aceast propunere este atrgtoare dac banca ofer un cont de economii cu o dobnd anual de 4% ? Pentru a rspunde la aceast ntrebare, preul de achiziie de 1.075$ la nceput trebuie comparat cu beneficiile viitoare. Aceasta se realizeaz folosind conceptul de valoare actualizat, dup cum va fi explicat mai jos. Investiia n datorie presupune un anumit flux monetar, care nseamn intrri i iesiri de bani asociate acesteia. Fluxul monetar pentru investiia n datorie este redat n figura 1.35 i ilustrat n figura 1.36. Problema evalurii unei datorii de 1.000$ poate fi rezolvat n acelai mod ca cea a gsirii valorii anuitii. n primul rnd, fluxurile monetare ale anilor urmtori pot fi actualizate la valoarea de nceput, dup ce sunt nsumate cu preul de achiziie. Acest lucru este realizat n coloana C a figurii 7.5. Suma rezultat a fluxurilor monetare actualizate este 6$, astfel nct rscumprnd o datorie la 1.075$ se obin 6$ folosind o rat a dobnzii de 4% n vederea reducerii. Valoarea actualizat a plilor dobnzii constante de 50$ pe parcursul celor 10 ani poate fi calculat folosind formula valorii actualizate: = PV (DIR, 10,-IR*A) care are ca rezultat 406$, vezi celula E1 n figura 1.35. Reachitarea de ctre debitor poate fi actualizat spre valoarea de nceput folosind formula: =1.000(1+r)^10=A/(1+DIR)^10=676$, vezi E2. Dup scderea preului de achiziie de 1.075$, este regsit aceeai valoare de 6$.

1.3.3 O privire de ansamblu asupra fluxurilor monetare constante, formule asociate i contracte Deoarece valorile actualizate i viitoare ale fluxurilor monetare i echivalentelor lor au fost discutate n diverse contexte, este necesar o prezentare de ansamblu i o comparaie. Vor fi luate ca exemplu fluxuri monetare de 10.000$ care se obin n timp de 1 pn la 10 ani. Rata dobnzii este de 10%. Fluxul monetar poate fi de intrare (pozitiv) sau de ieire (negativ), vezi figura 1.37. Valoarea actualizat a acestui flux monetar, care reprezint valoarea n anul 0, este:

P0= rr t+ )1(1 A=PV(r, t, -A)=PV(10%, 10, -10.000)=77.217$

Valoarea viitoare a fluxului monetar care reprezint valoarea n anul 10 este:

P10= rr t 1)1( + A=FV(r, t, -A)=FV(10%, 10, -10.000)=125.779$.

O anuitate este un contract dintre o persoan i o instituie financiar prin care persoana pltete o sum de bani P0 n anul 0, pentru a avea un flux de intrare de A timp de t ani. Instituia financiar stabilete rata dobnzii r n funcie de care este calculat P0. Intrrile si ieirile de fluxuri monetare ale persoanei sunt prezentate n partea stng a figurii 1.37. i n partea (a) a figurii 1.38. Pentru instituia financiar intrrile i ieirile sunt inversate. Valoare prezent combinat a intrrilor i ieirilor evaluate la o rat a dobnzii r=5%, este exact 0, pentru c preul anuitii egaleaz valoarea actualizat a influxurilor anuale constante. Acelai lucru este valabil i pentru alte trei tipuri de contracte.

O ipotec cu anuiti constante este un contract prin care o persoan obine de la o instituie financiar un credit P0 n anul 0 i face pli de A pe parcursul urmtorilor t ani. Expresia (2) este relaia dintre P0 i A, dar n cazul unei ipoteci primul determinat este P0, iar apoi se determin A

36

utiliznd funcia PMT. Pentru valorile lui A, r i t de 10.000$, 10% i respectiv 10 ani fluxurile monetare ale persoanei sunt cele indicate n coloanele denumite Ipoteca din figura 1.37 i n partea (b) a figurii 1.38

Din figura 1.37 i prile (a) i (b) ale figurii 1.38 rezult c fluxurile monetare pentru ipotec sunt exact inversul celor pentru rent. Dac o persoan dorete s obin o rent i o alta are nevoie de un mprumut pentru o ipotec i cad de acord asupra sumei A, duratei t i ratei dobnzii r, nu mai au nevoie de intermedierea unei instituii financiare.

Valoarea viitoare reprezint totalul fluxurilor monetare constante la sfritul celor t ani. Att ridicarea ipotecii ct i contractul de economii se folosesc de aceast echivalen. n cazul ridicrii ipotecii o persoan primete suma A timp de t ani, dar trebuie s restituie echivalentul sumei la sfrit, vezi coloana corespunztoare din figura 1.37 i partea (c ) a figurii 1.38.

Figura 1.37 Fluxurile monetare pentru patru tipuri de contracte

(a) Anuitate (b) Ipoteca

37

(b) Ridicarea ipotecii (d) Contract de economii Figura 1.38 Fluxurile monetare pentru patru tipuri de contracte

n cazul unui contract de economii o persoan pltete o sum A de-a lungul a t ani i primete suma Pt dup t ani, vezi ultima coloan a figurii 1.37. i partea (d) a figurii 1.38. Intrrile i ieirile de fluxuri monetare sunt inverse fa de cazul ridicrii ipotecii. Se observ dou tipuri de simetrie n aceste contracte. Trecnd de la o rent la o ipotec i vice versa se schimb sensul tuturor fluxurilor monetare. Trecnd de la o rent la o ridicare a ipotecii i vice versa se schimb poziiile sumelor constante A i capitalizrile sumelor echivalente acestora, P0 i Pt. Acelai lucru este valabil i n cazul ipotecii i al contractelor de economii. n cazul anuitii se pltete n ntregime suma pentru influxurile monetare nainte ca acestea s fie ncasate, ceea ce nseamn o investiie, iar n cazul ridicrii ipotecii dup ce fluxul a fost ncasat, ceea ce reprezint un mprumut. n cazul unui mprumut pentru o ipotec se primesc mai nti banii i apoi se returneaz ntr-un flux de sume constante, iar n cazul unui contract de economii se pltete mai nti un flux de sume constante i se primete n sum total la sfrit.

1.3.4. Pensiile Conceptele i formulele pentru plile periodice se pot aplica i la determinarea pensiilor. n cele ce urmeaz se prezint un caz standard pentru stabilirea fondurilor de pensii. S presupunem c o persoan muncete 40 de ani, de la 25 de ani pn la 65 de ani. Salariul iniial este de 25.000$, care crete cu 3% pe an pn la vrsta de 55 de ani, dup care acesta rmne constant. Dousprezece procente din salariu sunt livrate anual ntr-un fond de pensii, unde se cumuleaz cu o rat a dobnzii de 5%. Fondul de pensii este utilizat pentru a asigura o pensie anual constant pentru 20 de ani. Ne intereseaz sumele anuale ale pensiilor i pensia lunar.

Figura 1.39. reprezint o parte a foii de calcul pentru aceste calcule. Informaiile introduse sunt n concordan cu realitatea.

Figura 1.39: Informaiile i contribuiile la pensii

38

Se pot deduce formule algebrice pentru calculul salariilor, contribuiilor la fondul de pensii, i pentru pensia nsi. Totui formulele rezultate sunt complicate i pot aprea erori frecvente. Este preferabil s se introduc pentru fiecare an, ncepnd cu 25 pn 84 de ani, diferitele sume care sunt uor de determinat. Pe lng aceasta, o astfel de prezentare anual face ntreagul proces mai uor de neles. n primul rnd trebuie determinate salariile pentru munca prestat. Celula 8 conine ca i formul =SS, i celula B9, =B8*(1+SI), care este copiat n jos pn la vrsta de 55 de ani. Salariile pn la vrsta de 64 de ani sunt apoi egalizate cu cele de 55 prin formula =$B$38. Contribuia pentru pensii pe fiecare an reprezint 12% din salariul anului respectiv, astfel nct celula C8 conine ca formul =PC*B8, care este copiat n jos. Suma cumulat pentru fondul de pensii pentru un anumit an este egal cu cea din anul precedent, plus dobnda, plus contribuia pentru anul respectiv, astfel nct coninutul celulei D8 este dat de formula =C8+(1+IR)*D7, care este copiat n jos. Rezultatul este suma de 554.525$ corespunztoare vrstei de 64 de ani, vezi figura 1.310. Pensia care se acord pe 20 de ani trebuie s se bazeze pe aceast sum. Ea este dedus folosind formula: =PMT(IR,20,-$D$47) n celula E48, i este copiat n jos. Pentru a verifica dac fondurile sunt epuizate dup vrsta de 84 de ani, n celula D48 este redat formula =(1+IR)*D47-E48, care este copiat n jos i care va deveni ntr-adevr 0 n celula D67.

Figura 1.40 Date de intrare pentru calculul pensiei

Cum pensiile sunt pltite de obicei, mai degrab lunar dect anual, trebuie calculat pensia lunar ceea ce se face utiliznd formula: =PMT((1+IR)^(1/12)-1,12*20,-D47)=3.626$. A se observa c se utilizeaz i rata lunar a dobnzii. Figura 1.41 prezint rezultatele calculului pensiei. De asemenea indic faptul c pensia anual reprezint 73% din ultimul salariu. n anumite cazuri, drepturile la pensie sunt indicate n procente aplicate ultimului salariului pe an de munc. n cazul nostru, acestea se ridic la 1,83%.

Figura 1.41 Rezultatele calculului pensiei Figura 1.42 reprezint un grafic al fondului de pensii colectate de la nceputul perioadei de munc, de la vrsta de 25 de ani pn la epuizarea fondului la vrsta de 84 de ani. La nceput,

39

fondul se adun treptat i crete rapid spre sfritul perioadei de munc. Acelai lucru este valabil i n cazul acordrii pensiei, cnd declinul ncepe treptat, dar este mult mai rapid spre sfrit.

Figura 1.42 Fondul pensiilor colectate pe perioada de munc i fondul de pensii

Un grafic asemntor poate fi construit i pentru fluxul monetar asociat pentru persoana n

cauz, vezi figura 1.43. Potrivit metodei dobnzii compuse, o economisire a unei sume relativ mici

pe an timp de 40 de ani de munc asigur un venit rezonabil n cei 20 de ani de acordare a pensiei.

Figura 1.43 Fluxul monetar asociat pensiei

Probleme: 1. Pentru o rat a dobnzii de 7%, care este valoarea actualizat a unei datorii cu dobnd de 10% pltit anual, i care va fi restituit la 31 decembrie 1999? 2. Drepturile la pensie reprezint suma pensiei anuale exprimat ca procent din ultimul salariu pe an de munc. Care este dreptul la pensie n acest caz? Calculai dreptul la pensie pentru valori ale contribuiilor la pensie de 10%, 12% i 14% i rate ale dobnzii de 3%, 4% i 5%. 3. n cazul pensiei se presupune c exist ntotdeauna o inflaie de 4%, i c salariile cresc i rata dobnzii dat reprezint singura cretere real. Modificai foaia de calcul n acest sens. Care este acum procentajul dreptului la pensie? Luai n considerare i ajustarea sumei pensiei anuale.

40

2. Modele de optimizare

2.1. Programare liniar

Problema pe care o vom examina este o problem tipic de programare liniar. Problema este de a selecta combinaia (mix-ul) de produse ce trebuie realizate pentru a obine profitul maxim.

n acest capitol vei nva urmtoarele comenzi i funcii ale foii de lucru: Funcia SUM; Funcia SUMPRODUCT Utilizarea Solver-ului Exemplu. Compania Rame SRL produce 4 tipuri de rame (avnd codurile 1,2,3 i 4). Cele 4

tipuri de rame sunt diferite n ce privete mrimea, forma i materialele utilizate. Fiecare tip necesit o anumit cantitate de munc, metal i sticl aa cum se vede n tabelul de mai jos. n acest tabel se prezint i preurile unitare de vnzare pentru fiecare tip de ram. n urmtoarea sptmn compania poate s cumpere 4000 de ore de munc, 6000 de kg de metal i 10000 de kg de sticl. Costurile unitare sunt de 8.00$ pe or, 0.50$ pe kg de metal i 0.75$ pe kg de sticl. De asemenea restriciile pieei fac mposibil vnzarea a mai mult de 1000 de rame de tipul 1, 2000 de rame de tipul 2, 500 de rame de tipul 3 i 1000 de rame de tipul 4. Compania dorete maximizarea profitului pe sptmn.

nainte de a prezenta modul de rezolvare a acesteia pe o foaie de calcul vom prezenta cteva noiuni legate de modul tradiional de abordare a programrii liniare. n aceast problem trebuie realizate 4 tipuri de rame codificate 1,2,3 i 4. Vom nota aceste tipuri de rame cu 4321 ,,, xxxx . Apoi, vom scrie profitul total i restriciile n funcie de x. n final, deoarece nu dorim s se produc cantiti nenegative vom aduga restricii care s specifice acest lucru. Formularea algebric a problemei arat n felul urmtor.

4321 3426 xxxxMaxim +++ (funcia obiectiv a profitului) cu restriciile:

4000232 4321 +++ xxxx (restricia de munc) 6000224 4321 +++ xxxx (restricia de metal) 10000226 4321 +++ xxxx (restricia de sticl)

10001 x (maxim vnzri rame tip 1) 20002 x (maxim vnzri rame tip 2) 5003 x (maxim vnzri rame tip 3) 10004 x (maxim vnzri rame tip 4) 0,,, 4321 xxxx (restricii de nenegativitate)

1. Date de intrare. Toate datele de intrare numerice i anume datele necesare pentru a forma obiectivul i restriciile vor aprea ntr-o form sau alta n cadrul modelului. Vom face convenia de a evidenia toate datele de intrare n cadrane umbrite. Numai n cazul n care datele se potrivesc mai bine n alt parte vom nclca aceast convenie.

2. Changing cells (prin modificarea celulelor). n locul utilizrii denumirilor de variabile vom folosi un set de celule care vor juca rolul variabilelor de decizie. Valorile din aceste celule pot fi schimbate pentru a optimiza funcia obiectiv. n Excel aceste celule sunt denumite

41

changing cells. Pentru a observa clar acest lucru vom folosi convenia de a ncadra changing cells cu o margine de culoare roie.

3. Target cell (prin setarea celulei). O celul denumit target cell conine valoarea obiectiv. Solver-ul variaz sistematic valorile din changing cells pentru a optimiza valoarea din target cell. Vom folosi convenia de a ncadra target cell cu o linie dubl neagr.

4. Restricii. Excel nu arat n mod direct restriciile pe foaia de calcul. De aceea trebuie s specificm restriciile n caseta de dialog Solver. De exemplu, putem realiza un set de restricii cu ajutorul formulei B15:D15=0.

n general rezolvarea complet a problemei presupune trei stadii. Primul stadiu este de a introduce toate datele de intrare, valorile iniiale pentru changing cells i formulele corespunztoare. Vom numi acest stadiu formularea problemei. Dup ncheierea primului stadiu se poate trece la cel de-al doilea ce const n utilizarea Solver-ului. n acest punct vom proiecta formal target cell, changing cells i constraints . Dac acest lucru s-a realizat bine rezolvarea cu Solver-ul este imediat. Al treilea pas const n analiza de senzitivitate. Cnd utilizm Solver-ul folosim cele mai bune estimri ale valorilor folosire pentru a obine soluia optim. Oricum, este important de vzut cum se modific soluia optim dac se schimb valorile de intrare. Solutia iniial

Figura 2.1

42

Dezvoltarea modelului pe foaia de calcul: 1. Date de intrare. Se introduc diverse date de intrare n domeniile B4:B6, B9:E12, B18:E18

i D21:D23.Se denumes domeniile ca n figura de mai sus n zona gri. (Insert Name) 2. Nivelurile de producie. Se introduce oricare patru valori n celulele B16:E16. Aceste

celule sunt de tip changing cells, adic celule unde vor fi plasate variabilele de decizie. Orice valori pot fi utilizate iniial; Solver va gsi valorile optime. Se observ c cele patru valori din figur nu pot fi optime deoarece nu satisfac toate restriciile. De exemplu, acest plan utilizeaz mai multe ore de munc i mai mult metal dect este disponibil, i produce mai multe produse din tipul 4 dect pot fi vndute. Oricum, nu trebuie s ne ngrijoreze n acest moment satisfacerea acestor restricii; Solverul se va ocupa de acest lucru mai trziu.

3. Resursele utilizate. Se introduce formula =SUMPRODUCT (B9:E9, Producie) n celula B21 i se copiaz n domeniul B22:B23. Aceste formule calculeaz necesarul de munc, metal i sticl utilizat de mixul curent de produse. Funcia SUMPRODUCT este foarte util n modelele de programare liniar. n exemplu de aici nseamn multiplicarea fiecrei valori din domeniul B9:E9 cu valoarea corespondent din domeniul Producie i apoi calcularea sumei acestora.

4. Venituri, costuri i profituri. De la rndul 25 n jos se formeaz o zon ce conine o sintez a valorilor monetare. De fapt, ceea ce dorim este profitul total n celula F32, dar este extrem de util s calculm datle de intrare pentru profitul total, ceea ce nseamn, veniturile i costurile asociate cu fiecare produs.Pentru a obine veniturile, se introduce formula =B12*B16 n celula B27 i se copiaz n domeniul C27:E27.Pentru costuri se introduce formula =$B4*B$16*B9 n celula B29 i se copiaz n domeniul B29:E31 (Se folosete un mixaj de referine absolute i relative pentru a realiza acest lucru). Apoi se calculeaz profiturile pentru fiecare produs prin introducerea formulei =B27-SUM(B29:B31) n celula B32 i se copiaz n domeniul C32:E32. n final se calculeaz totalul n coloana F prin nsumarea de-a lungul rndurilor cu funcia SUM.

Urmtorul pas const n a specifica coninutul celulelor de tip changing cells, al celulei de

tip target cell i restriciile (constraints) din caseta de dialog Solver pentru a gsi soluia optim. Oricum, nainte de a face acest lucru, este util s se ncerce cteva estimri n celulele de tip changing cells. De exemplu, este tentant s presupunem c tipul de ram cu cea mai mare marj de profit ar trebui produs n cea mai mare msur. Se ncepe prin introducerea de zero-uri n celulele de tip changing cells din domeniul B16:E16. Evident, cnd nu producem nimic profitul este 0. n continuare, din cauza faptului c tipul de ram 1 are cea mai mare marj de profit ( 6$) iar restricia sa de pia permite cel mult 1000 de rame, se vor introduce 1000 n celula B16. se observ c nici una dintre resurse nu a fost utilizat total. Deci, putem realiza nite rame de tipul 3, tipul cu urmtoarea marj de profit ca mrime. Datorit faptului c restricia de pia a tipului 3 permite cel mult 500 de rame, se vor introduce 500 n celula D7. nc mai exist disponibiliti din fiecare resurs. Aceasta ne permite s realizm rame de tipul 4, tipul cu urmtoarea marj de profit ca mrime. Oricum, maximul pe care l putem realiza din tipul 4 de rame este 250, pentru c la acest moment s-a utilizat complet timpul de lucru disponibil. Soluia rezultat este ilustrat n figura urmtoare.

43

Figura 2.2 Profitul corespunztor este 8750 $. n acel moment s-a produs maximul posibil din cele trei tipuri de rame cu cele mai nalte marje de profit. Oare acest lucru garanteaz c soluia cuprinde mixul optim de produse? Din pcate nu este aa. Soluia din figur nu este optim. Chiar i la nivelul acestui model de dimensiuni reduse este dificil s intuim soluia optim chiar dac folosim o procedur relativ inteligent de tipul ncercare-eroare. Problema este c un tip de ram cu o marj de profit nalt poate folosi o cantitate mare de resurse i mpiedica realizarea altor rame profitabile. Dei este instructiv s ncerci s intuieti soluia optim s-ar putea s nu o gsim niciodat. Din acest motiv este util s folosim Solver-ul. Utilizarea Solver-ului. Pentru a apela la Solver-ul din Excel se selecteaz din meniu Tools/ Solver. Va aprea caseta de dialog ilustrat n figura urmtoare. Ea conine trei seciuni importante care trebuie completate: celula tip Target cell, celule tip changing cells i restricii. Pentru problema mixului de produs le putem completa prin tastarea referinelor celulelor sau putem s ne poziionm pe ele i s le tragem n domeniile corespunztoare. De asemenea dac am denumi domeniile putem folosi aceste denumiri n locul adreselor aferente celulelor. (Dac decidem s ne poziionm pe ele i s le tragem, s-ar putea s fie nevoie s mutm caseta de dialog a Solver-ului pentru a nu fi n calea celulelor pe care dorim s le selectm)

44

Figura 2.3 1. Obiectiv. Se selecteaz celula F32 ce reprezint profitul total, drept celul de tip target

cells i se execut click asupra butonului de maximizare (Max). 2. Changing cells (prin modificarea celulelor). Se selecteaz domeniul ramelor produse

B16:E16 drept celule de tip changing cells. 3. Restricii. Se execut click pe butonul Add pentru a aduga urmtoarele restricii:

Necesar

45

Figura 2.4 5. Modelul liniar. Mai exist un ultim pas nainte de a executa click pe butonul Solve.

Solver folosete una dintre cele cteva metode numerice de rezolvare a diferitelor tipuri de probleme. Problemele liniare se pot rezolva cel mai eficient prin metoda simplex. Pentru ca Excel s foloseasc aceast metod se va valida opiunea Assume Liniar Model din aceeai caset de dialog a Solver-ului utilizat anterior.

6. Optimizare. Executai click pe butonul Solve n acest moment Solver-ul alege dintr-un numr de soluii posibile pn cnd identific soluia optim. Se poate urmri progresul operaiunii n zona din stnga jos a ecranului. Cnd operaiunea ia sfrit va aprea mesajul din figura urmtoare.

Figura 2.5 i putei spune Solver-ului s transforme valorile din celulele de tip changing cells

aducndu-le la starea original (non-optim) sau s rein valorile optime gsite. n majoritatea cazurilor se va alege probabil a doua variant. (n unele cazuri ns Solver-ul nu va gsi soluiile optime i va afia cteva mesaje de eroare. )

Se execut click pe OK pentru a pstra soluia dat de Solver. Se poate vedea soluia n figura urmtoare.

46

Figura 2.6 Planul optim este de a produce 1000 de rame de tip 1, 800 de rame de tip 2, 400 de rame de

tip 3 i nici o ram de tip 4. Acest plan se apropie de planul de producie iniial dar n cazul acestuia din urm se obine un profit cu 450 $ mai mare. De asemenea se folosete tot timpul de lucru disponibil i toat cantitatea de metal ns doar 8000 din cele 10000 de kg de sticl disponibile. n privina maximizrii vnzrilor planul optim ar putea produce mai multe rame de tipul 2,3 i 4. ( Dac ar exista mai mult timp de lucru i/sau mai mult metal disponibil). Aceast este o soluie tipic de programare liniar. Unele dintre restricii sunt respectate la limit n timp ce altele conin anumite rezerve. Exeprimentarea cu noi date de intrare. n cazul n care se dorete s se experimeteze cu noi date de intrare se pot schimba pur i simplu datele i se ruleaz din nou Solver-ul. S presupunem c preul de vnzare al ramei tip 4 a crescut de la 21.5$ la 26,5$ iar celelalte condiii sunt neschimbate. Prin creterea profitabilitii ramei 4 sperm c acesta va fi inclus n model. Este exact ceea ce se ntmpl. Programul optim conine acum 1000 de rame de tipul 1 i 1000 de rame de tipul 4 aa cum se poate vedea n urmtoarea figur.

47

Figura 2.7 Analiza de senzitivitate. Caseta de dialog Solver indic modul de abordare n acest caz. Prin selectarea opiunii Senzitivity Report se va obine o nou foaie cu multiple informaii despre senzitivitatea modelului. Raportul corespunztor exemplului nostru este prezentat n figura urmtoare.

Figura 2.8 Probleme:

1. O cofetrie produce dou tipuri de prjituri ( cu ciocolat i vanilie). Fiecare prjitur cu ciocolat se vinde cu 12$ iar fiecare prjitur cu vanilie cu 9$. Fiecare prjitur cu ciocolat necesit 20 de minute pentru copt i utilizeaz 4 ou. Fiecare prjitur cu vanilie necesit 40 de minute pentru copt i 1 ou. Timpul disponibil este de 8 ore i exist 30 de ou. Utilizai Solver-ul pentru a maximiza profitul.

2. n problema precedent timpul de copt pentru prjiturile cu vanilie ca urmare a unei noi tehnologii de realizare scade la 25 de minute. Utilizai Solver-ul pentru a maximiza profitul.

48

2.2. Modele de planificare agregat

Modelul permite ca nivelul forei de munc s se modifice n fiecare lun prin angajarea sau

concedierea de muncitori. De asemenea i se permite cererii s se acumuleze, adic s nu fie satisfcut la timp. Exemplul urmtor ilustreaz dou versiuni ale acestui model, unul bazat pe cerere reprogramat, altul fr cerere reprogramat.

Exemplu. n urmtoarele patru luni firma SuperPan, productoare de nclminte, va trebui

s satisfac ( la timp) urmtoarele cereri de perechi de pantofi: 3000 n luna 1, 5000 n luna 2, 2000 n luna 3 i 1000 n luna 4. La nceputul lunii 1 exist 500 de perechi n stoc iar firma are 100 de muncitori. Fiecare muncitor este pltit cu 1500 $ pe lun pentru un timp de lucru de 160 ore, program neincluznd ore suplimentare. Deci sunt pltii cu 9,38 $ pe or. Un muncitor poate presta pn la 20 ore suplimentare pe lun, fiind pltit cu 13 $ pentru fiecare or suplimentar. Este nevoie de 4 ore i de materii prime n valoare de 15 $ pentru a produce o pereche de pantofi. La nceputul fiecrei luni se pot angaja sau concedia muncitori. Costurile fixe pentru concediere sunt de 2000 de $ pe muncitor iar pentru angajare de 1600 $ pe muncitor. La sfritul fiecrei luni pentru fiecare pereche cheltuielile de stocare sunt de 3$. Preul de vnzare al pantofilor va fi constant n aceast perioad de 4 luni, deci nu va trebui ncorporat n model. Se pornete de la premisa c producia dat a unei luni se va utiliza pentru satisfacerea cererii acelei luni. SuperPan dorete s determine programul de angajri/concedieri i producie care s minimizeze costurile totale n perioada de 4 luni. Soluie. Pentru modelul problemei SuperPan va trebui s avem n vedere urmtoarele aspecte:

- Numrul de muncitori angajai/concediai i disponibili n fiecare lun. - Timpul de lucru i orele suplimentare din fiecare lun. - Capacitatea de producie (perechi de pantofi) i cantitile produse n fiecare lun. - Nivelurile stocurilor obinute n fiecare lun dup producie i la sfritul lunii dup

satisfacerea cererii). - Costuri lunare i costuri totale.

Desfurarea aplicaiei modelului tip foaie de calcul. Modelul tip foaie de calcul e ilustrat n figura urmtoare.

49

Figura 2.9 Paii sunt urmtorii:

1. Date de intrare. Se introduc datele de intrare n domeniul B4:B14 (fiecare dintre aceste celule are un domeniu i o denumire) i se introduc cererile lunare n domeniul Cerere.

2. Programul de angajri/concedieri i producie. Se introduc orice valori iniiale pentru numrul de perechi de pantofi produse n foecare lun n domeniul Producie, numrul de ore suplimentare utilizate n fiecare lun n domeniul OS, numrul de muncitori angajai n fiecare lun n domeniul Angajri i numrul de muncitori concediai n fiecare lun n domeniul Concedieri. Aceste patru domenii sunt celule de tip changing cells.

3. Muncitori disponibili n fiecare lun. n celula B17 se introduce numrul iniial de muncitori disponibili cu formula

=MuncitInit. Datorit faptului c numrul de muncitori disponibili la nceputul fiecrei luni (nainte de a angaja sau concedia) este egal cu numrul de muncitori ai lunii precedente se introduce formula

50

= B20 n celula C17 i se copiaz n domeniul D17:E17. Apoi, se calculeaz numrul net de muncitori existent dup angajare sau concediere prin introducerea formulei = B17+B18-B19 n celula B20 i se copiaz pe orizontal pe linia 20. 4. Capacitatea de efectuare de ore suplimentare. Deoarece fiecare muncitor disponibil poate

lucra pn la 20 de ore suplimentare pe lun se introduce formula =MaxOS*B20 n celula B25 i se copiaz n domeniul C25:E25 pentru a capacitatea de efectuare de ore suplimentare pentru lunile 2,3 i 4.

5. Capacitatea de producie. Deoarece fiecare muncitor lucreaz 160 ore pe lun se calculeaz timpul de lucru disponibil n luna 1 n celula B22 cu formula =TLStandard*B20 i se copiaz n domeniul C22:E22 pentru celelalte luni. Apoi se calculeaz numrul total de ore disponibile pentru producie n celula B27 cu formula =SUM(B22:B23) i se copiaz n domeniul C27:E27 pentru celelalte luni. Deoarece sunt necesare patru ore de lucru pentru o pereche de pantofi se introduce formula =B27/OrePerPereche n celula B32 i se copiaz n domeniul C32:E32.

6. Stocurile lunare. Exist dou rnduri pentru stocuri. Rndul 34 pentru stocurile post-producie i rndul 37 pentru stocurile finale, dup ce s-a satisfcut cererea. Pentru a le calcula se ncepe prin introducerea formulei = StocInit +B30 n celula B34. Apoi se introduce formula =B37+C30 n celula C34 i se copiaz n domeniul D34:E34. Astfel se coreleaz stocul lunar cu stocul final al lunii anterioare. n continuare se calculeaz stocurile finale introducndu-se formula =B34-B36 n celula B37 i se copiaz pe orizontal pe rndul 37.

7. Costurile. Se calculeaz costurile n rndurile 35-40 nmulind costurile unitare corespunztoare din rndurile 7-14 cu cantitile corespondente din foaia de calcul. De exemplu, pentru costurile de stocare se introduce formula =CostUnitStoc*B37 n celula B45 i se copiaz n domeniul C45:E45.n domeniul C45:E45. Apoi se calculeaz totalurile pe rnd i coloan n coloana F i rndul 46.

Utilizarea Solver-ului. Caseta de dialog a Solver-ului este ilustrat n figura urmtoare.

Figura 2.10 Se va minimiza costul total avnd grij s nu se depeasc restricia maxim de ore suplimentare i capacitate de producie i s se satisfac cererea la timp. Se introduce i o restricie de numr ntreg pentru numrul de muncitori angajai/concediai i nivelurile de producie. Pentru a introduce aceast restricie se procedeaz ca n figura urmtoare:

51

Figura 2.11 Pentru a asigura obinerea soluiei optime este indicat s se seteze tolerana din meniul Options la valoarea 0.

Modelul cu reprogramare permis. n multe situaii reprogramarea este permis; aceasta nseamn c cererea poate fi satisfcut mai trziu dect apare. Vom modifica modelul anterior pentru a permite reprogramarea. Vom presupune un cost unitar de 20$ la sfritul fiecrei luni pentru fiecare pereche de pantofi cerut i care nu a fost livratla sfrtul lunii. Acest lucru se paote modela uor dac se permite ca stocul final s fie negativ. De exemplu, dac stocul final al lunii 1 este 10 se va calcula un cost de penurie de 200 $. Modificarea modelului. Metoda este ilustrat n figura de mai jos. 1. Se introduce costul de penurie. Se introduce un nou rnd sub rndul 14 i se introduce costul de

penurie pe lun n celula B15. 2. Rnduri pentru penurie i surplus. Se introduc 5 rnduri (de la 38 la 42) ntre rndurile pentru

Cerere i Stoc Final. Domeniul B39:E40 vor fi celulele de tip cahnging cells. Domeniul Surplus din rndul 39 conine cantitatea aflat n stoc n timp ce domeniul Penurie calculeaz cantitatea lips ce ar fi putut fi vndut. Se introduc orice valori n aceste domenii.

3. Stoc final (pozitiv sau negativ). Se introduce formula =B39-B40 n celula B41 i se copiaz de-a lungul rndului 41. Astfel dac n domeniul Penurie se afl valori mai mari ca 0, stocul final va fi negativ indicnd o lips de produse. Deoarece am calculat stocul final in dou moduri pe rndurile 41 i 43 vom aduga o restricie suplimentare forndu-le s fie egale.

4. Cost de stocare. Se introduce un nou rnd (rndul 52)sub costul de stocare. Se modific costul de stocare cu formula =CostUnitStoc*B39 n celula B51 i se copiaz pe rndul 51.Se calculeaz costul de penurie n celula B52 cu formula =CostUnitPenurie*B40 i se copiaz pe rndul 52.

Utilizarea Solver-ului. Caseta de dialog A Solver-ului modificat este prezentat n figura de mai jos.

Figura 2.12

52

Modificrile sunt: -domeniile Surplus i Penurie sunt celule suplimetare de tip changing cells. -domeniul Net trebuie s fie egal cu domeniul Stocfin -domeniul ExistentLuna4 trebuie sa fie cel putin la fel de mare ca CerereaLuna4.

Figura 2.13 Probleme:

1. Motociclete S.A. i determin programul de producie pentru urmtoarele 4 trimestre. Cererea pentru motociclete este previzionat ca fiind: 40 n trimestrul 1, 70 pentru trimestrul 2, 50 pentru trimestrul 3 i 20 pentru trimestrul 4. Costurile asociate sunt:

- 400$ cost de producie pe motociclet

53

- la sfritul fiecrui trimestru costul de stocare este de 100$ pe motociclet. - Dac producia descrete de la un trimestru la altul se pltesc costuri suplimentare de

600 $ pe motociclet Cererea trebuie satisfcut n fiecare trimestru. nainte de trimestrul 1 s-au produs 50 de motociclete. La nceputul trimestrului 1 nu exist motociclete n stoc. S se minimizeze costul total al companiei pe urmtoarele 4 trimestre.

2. Referitor la problema precedent se presupune c Motociclete S.A. nu este obligat s

satisfac cererea trimestrial. Pentru fiecare trimestru n care cererea pentru motociclete nu este acoperit se calculeaz un cost de penurie de 110$ pe motociclet. Cererea poate fi reprogramat pentru trimestrele urmtoare dar trebuie satisfacut complet pn la finele ultimului trimestru.

2.3. Probleme de transport

n multe cazuri companiile realizeaz produse n locaii numite puncte de ofertare i

livreaz aceste produse ctre locaiile clienilor numite puncte de cerere. Fiecare punct de ofertare are o capacitate limitat de livrare i fiecare client trebuie s primeasc cantitatea de produse solicitat. Modelele cu foi de calcul se pot utiliza pentru a determina metoda de livrare cu costuri minime pentru a satisface cererile clienilor. n acest caz se va presupune c singurele livrri posibile sunt cele directe de la punctul de ofertare la cel de cerere; aceasta nseamn c nu este posibil nici o livrare ntre punctele de ofertare sau cele de cerere. Un astfel de tip de problem se numete problem de transport. Exemplu. Mix Electrica este o firm care cuprinde trei uzine furnizoare de electricitate care produc curent electric pentru patru orae. Fiecare uzin poate furniza cantitatea ilustrat n tabelul urmtor (n mil. kwh):

Oferta Uzina 1 35 Uzina 2 50 Uzina 3 40

Tabel 2.1.

Cererea maxim de curent electric este ilustrat de tabelul urmtor(n mil. kwh):

Cerere Ora 1 45 Ora 2 20 Ora 3 30 Ora 4 30

Tabel 2.2 Costul n $ al furnizrii unui milion kwh de ctre fiecare uzin pentru fiecare ora este ilustrat n tabelul urmtor:

54

Ora 1 Ora 2 Ora 3 Ora 4 Uzina 1 8 6 10 9 Uzina 2 9 12 13 7 Uzina 3 14 9 16 5

Tabel 2.3

Mix Electrica ncearc s gseasc costul minim de satisfacere a celor patru orae. Soluie. Pentru a stabili modelul pentru foaia de calcul pentru distribuia de curent electric a companiei Mix Electrica va trebui s avem n vedere urmtoarele:

- curentul electric livrat (n mil. kwh) de la fiecare uzin ctre fiecare ora - curentul electric total livrat de la fiecare uzin - curentul electric total primit de fiecare ora - costul total de transport Desfurarea aplicaiei. Modelul cu foi de calcul va fi ilustrat n figura urmtoare.

Figura 2.14

Pentru desfurarea aplicaiei sunt indicai urmtorii pai: 1. Date de intrare. Se introduc costurile de livrare unitare pentru fiecare ora n domeniul

Costuri de Livrare Unitare. Se introduc capacitile uzinelor n domeniul Capacitate i cererile oraelor n domeniul Cerere.

2. Cantiti livrate. Se introduc orice valori iniiale pentru livrrile fiecrei uzine ctre fiecare ora n domeniul Livrri. Acestea sunt celule tip changing cells.

3. Totaluri livrri. Se calculeaz cantitile livrate de fiecare uzin n domeniul TotLivrari prin nsumarea pe orizontal a valorilor rndului aparinnd domeniului Livrri. Similar se calculeaz cantitile livrate ctre fiecare ora n domeniul CantitatePrimita pe vertical a valorilor coloanei aparinnd domeniului Livrri.

4. Costul total de livrare. Se calculeaz costul total de livrare de la uzine ctre orae n celula TotCost cu formula

=SUMPRODUCT(CostUnitar, Livrari) Formula nsumeaz totalul produciei n funcie de costurile de livrare i cantitile livrate.

55

Folosirea Solver-ului. Caseta de dialog a Solver-ului este ilustrat n figura urmtoare:

Figura 2.15

Se va minimiza costul total de livrare pstndu-ne totui n limita capacitilor i satisfcnd cererea. Observaie. Pentru ca Solverul s rezolve corect problema trebuie validate opiunile Assume Non-Negative i Assume Linear Model din caseta Options.

Figura 2.16

Soluia optim ilustrat anterior poate fi reprezentat grafic astfel:

Figura 2.17

30

10

5 25

45 10 1

2

3

1

2

3

4

35 50 40

45 20 30 30

Livrri Uzine Orae Cereri

56

Un cost minim de 1020$ se obine prin utilizarea rutelor de livrare din figur. Probleme: 1. Oteluri S.A. produce trei tipuri de oel la diferite uzine. Timpul necesar pentru a produce o

ton de oel iar costurile asociate sunt artate n figura urmtoare. n fiecare sptmn 100 de tone pentru fiecare tip de oel trebuie produs. Fiecare uzin este deschis 40 de ore pe sptmn. Determinai modul n care trebuie minimizat costul de producie pentru companie.

2. Transporturi S.A. ofer bunuri ctre consumatori, fiecare dintre ei avnd nevoie de 30 de

uniti. Companie are dou depozite. n depozitul 1 40 de uniti sunt disponibile iar n depozitul 2, 30 de uniti sunt disponibile. Costul pentru a transporta o unita