Bài giảng Tin học ứng dụng trong kinh doanh - Chương 2: Ứng dụng Excel trong quản lý tài chính - Nguyễn Kim Nam

Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 23 Khoa QTKD - ThS. Nguy ễn Kim Nam 24
 4
 10/21/2012
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư
 Ví d 3: Ví d 4:
 C vào ngày 1/1 hàng nm bn gi vào tài kho n ti t ki m  ngân Gi  s bây gi  bn mua mt chi c xe máy airblook tr  giá 50 tri u.
 hàng BIDV mt kho n ti n 10 tri u  ng. K gi  u tiên là vào Ca hàng bán xe cho bn tr  ngay 10 tri u và 40 tri u còn li tr 
 ngày 1/1/2010 và k gi cu i cùng là ngày 1/1/2015. Hi ngày dn (tr  góp) trong vòng 36 tháng. Nh ng mi tháng ca hàng yêu
 1/1/2016 bn có bao nhiêu ti n trong tài kho n ti t ki m. Bi t lãi cu bn tr  mt kho n ti n  u là 1,5 tri u. K tr  góp  u tiên là
 su t ngân hàng tr  c  nh là 10%/n m. sau khi mua mt tháng. Nu lãi su t ngân hàng c  nh là 1%/tháng
n n Kim Nam n Kim Nam thì tng s ti n mà bn tr   mua xe sau 36 tháng là bao nhiêu?
 ây là bài toán có dng  u k nên ta nh p s li u nh  sau: 
 Bn nh p vào bng tính nh  sau:
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 25 Khoa QTKD - ThS. Nguy ễn Kim Nam 26
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư
 Các hàm tính giá tr  ti n t theo th i gian Ví d 5:
 
 Hàm PV: tính giá tr  hi n ti khi bi t giá tr  tơ ng lai (Fv) ho c giá tr  Vào ngày 1/1/2015 bn mu n có 100 tri u trong tài
 ti n  u (Pmt) hàng k. kho n ti t ki m thì ngày 1/1/2010 bn ph i gi vào tài
 
 Công th c: =PV(rate, nper, pmt,[Fv],[ type]) kho n ti t ki m là bao nhiêu ti n? Bi t lãi su t ngân
 Trong ó: hàng tr  c  nh là 10%/n m.
n n Kim Nam n Kim Nam
 Rate: lãi su t m i k   Bn nh p d li u vào bng tính và tính toán nh  sau:
 Nper: t ng s  k  tính lãi
 Pmt: s  ti n ph i tr   u trong m i k , n u b  tr ng thì coi là 0
 Fv: giá tr  tư ng lai, n u b  tr ng thì coi là 0
Khoa Khoa QTKD - ThS. Nguy Type: là hình th c thanh toán. N u type = 1 thì thanh toán  u Khoa QTKD - ThS. Nguy
 k ,nu type = 0 thì thanh toán vào cu i m i k  (nu b  tr ng thì mc 
  nh =0).
 Khoa QTKD - ThS. Nguy ễn Kim Nam 27 Khoa QTKD - ThS. Nguy ễn Kim Nam 28
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư
 Ví d 6: Ví d 7:
 C vào ngày 1/1 hàng nm bn gi vào tài kho n ti t ki m  ngân Gi  s bây gi  bn mua mt chi c xe máy airblook tr  giá 50 tri u.
 hàng ACB mt kho n ti n 10 tri u  ng. K gi  u tiên là vào Ca hàng bán xe cho bn tr  ngay 10 tri u và 40 tri u còn li tr 
 ngày 1/1/2010 và k gi cu i cùng là ngày 1/1/2015. Hi s ti n dn (tr  góp) trong vòng 36 tháng. Nh ng mi tháng ca hàng yêu
 bn gi nh  trên s tơ ng ơ ng vi bao nhiêu ti n vào ngày cu bn tr  mt kho n ti n  u là 1,5 tri u. K tr  góp  u tiên là
 1/1/2009. Bi t lãi su t ngân hàng tr  c  nh là 10%/n m. sau khi mua mt tháng. Nu lãi su t ngân hàng c  nh là 1%/tháng
n n Kim Nam n Kim Nam thì giá tr  hi n ti ca s ti n mà bn tr  góp  mua xe sau 36 tháng
 Bn nh p d li u vào bng tính và tính toán nh  sau (cu i k): 
 là bao nhiêu?(không tính kho n tr  ngay)
 Bn nh p vào bng tính nh  sau:
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 29 Khoa QTKD - ThS. Nguy ễn Kim Nam 30
 5
 10/21/2012
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 
 Hàm PMT (Payment): tính giá tr   u tng k
 khi bi t giá tr  tơ ng lai ho c hi n ti.
 
 Cú pháp: =PMT(rate, nper,pv,[fv],[ type])
n n Kim Nam n Kim Nam
 
 Ví d 8:
 Bn vay ngân hàng 50 tri u  ng tr  góp vào
 cu i mi tháng, tr  trong vòng 48 tháng. Hi s
Khoa Khoa QTKD - ThS. Nguy ti n mi tháng bn tr  cho ngân hàng là bao Khoa QTKD - ThS. Nguy
 nhiêu? Nu bi t lãi su t ngân hàng c  nh là
 1,2%/tháng.
 Khoa QTKD - ThS. Nguy ễn Kim Nam 31 Khoa QTKD - ThS. Nguy ễn Kim Nam 32
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
n n Kim Nam n Kim Nam
 
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 33 Khoa QTKD - ThS. Nguy ễn Kim Nam 34
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 
 Hàm IPMT (Interest Payment): Tính ti n lãi 
 ph i tr  hàng n m
 Cú pháp =IPMT(rate,per,nper,pv,fv,type) 
 Trong ó: 
n n Kim Nam n Kim Nam
  Rate: lãi su t 
 Nper: t ng s  l n thanh toán 
 Per: kì c n tính lãi 
 PV: kho n vay hi n t i 
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 FV: kho n ti n còn l i khi  n kì thanh toán 
 Type: =1  u kì,=0 cu i kì
 Khoa QTKD - ThS. Nguy ễn Kim Nam 35 Khoa QTKD - ThS. Nguy ễn Kim Nam 36
 6
 10/21/2012
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 Ví d 9: 
 Hàm RATE: Tính lãi su ất
 Nu vay ngân hàng m t kho n ti n 1000$ v i lãi Cú pháp: = RATE(nper, pmt, pv,[fv],[type],[guess])
 su t 2%/n m trong 5 n m thì l  ng ti n ph i tr  Ví d 10:
 lãi m i n m là bao nhiêu? Mt kho n vay $8000 ban  u  c  ngh  thanh toán 
n n Kim Nam n Kim Nam
  $200/tháng liên t c trong 4n m (48 tháng). H i lãi su t 
 (su t chi t kh u) c a kho n vay này là bao nhiêu?
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 37 Khoa QTKD - ThS. Nguy ễn Kim Nam 38
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 
 Hàm FVSCHEDULE : Tính giá tr  tơ ng lai khi lãi su t
 thay  i
 Cú pháp: = FVSCHEDULE(principal, schedule)
 
 Trong ó: Principal là giá tr  hi n t i, Schedule là các 
 lãi su t t ng k  trong kho ng th i gian nghiên c u.
n n Kim Nam n Kim Nam
 Ví d 11: 
 Mt ng  i vay 1000$ trong 3 n m v i lãi su t l n l  t là 
 20%/n m th  nh t,15%/n m th  2 và 10%/n m th  3. 
 Hi sau 3 n m c  lãi và g c c n ph i thanh toán bao 
Khoa Khoa QTKD - ThS. Nguy nhiêu ti n? Khoa QTKD - ThS. Nguy Tơ ng t các bn có th  tính  c th i gian
 thông qua hàm nper.
 Khoa QTKD - ThS. Nguy ễn Kim Nam 39 Khoa QTKD - ThS. Nguy ễn Kim Nam 40
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
  Các hàm ánh giá hi u qu  vn  u tư Ví d:
 
 Hàm NPV (Net Present Value): Là giá tr  hi n Tính NPV cho mt d án  u t có  u t ban  u
 ti thu n. Nói cách khác NPV là ti n li ca mt là 1 t  ng, doanh thu hàng nm là 0.5 t, chi
n n Kim Nam n Kim Nam
 d án quy v giá tr  hi n ti sau khi ã hoàn   phí hàng nm là 0.2 t, th i gian th c hi n d án
 vn  u t. là 4 nm, có lãi su t chi t kh u là 8%/n m.
 
 Công th c:
Khoa Khoa QTKD - ThS. Nguy Trong ó: Khoa QTKD - ThS. Nguy
 Rt: Doanh thu c a d  án  n m th  t
 Ct: Chi phí v n hành c a d  án  n m th  t
 It: Chi phí  u t   n m th  t
 n: Th i gian th c hi n d án, r = su t chi t kh u
 Khoa QTKD - ThS. Nguy ễn Kim Nam 41 Khoa QTKD - ThS. Nguy ễn Kim Nam 42
 7
 10/21/2012
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 
 Hàm IRR (Internal Rate of Return): Su t sinh
 li ni ti, nói cách khác là mt su t chi t kh u
 mà làm cho NPV =0.
 
 Cú pháp: = IRR (values, guess)
n n Kim Nam n Kim Nam
  Trong ó:
 
 value: dãy ô ch a giá tr  c a dòng ti n c n tính 
 IRR.
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy 
 Guess: là giá tr  d  oán g n v i IRR. N u b  
 qua tham s  này, Excel s  gán cho guess =10%. 
 a s  các tr  ng h p không c n nh p giá tr  
 guess này.
 Khoa QTKD - ThS. Nguy ễn Kim Nam 43 Khoa QTKD - ThS. Nguy ễn Kim Nam 44
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 Ví d:
 Mt d án  u t tính  n th i im d án bt  u
 i vào ho t  ng sn xu t vi vn  u t là 100
n n Kim Nam n Kim Nam
 tri u USD, doanh thu hàng nm ca d án là 50 
 tri u USD. Chi phí hàng nm là 20 tri u USD,
  i ca d án là 5 nm. Hãy xác  nh t su t
 hoàn vn ni b bi t lãi su t vay dài hn là
Khoa Khoa QTKD - ThS. Nguy 12%/n m. Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 45 Khoa QTKD - ThS. Nguy ễn Kim Nam 46
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
  Mt s hàm khác: Ví d:
 
 Hàm EFFECT Có 2 ph ơ ng án vay ti n vi mc lãi su t danh
 Tính lãi su t th c t  hàng n m cho m t kho n  u ngh a và s ln tính lãi tơ ng ng cho theo
n n Kim Nam n Kim Nam
 t  bng. Hãy la ch n ph ơ ng án vay.
 - Cú pháp: =EFFECT(Nominal_rate, npery)
 Trong ó:
Khoa Khoa QTKD - ThS. Nguy Nominal_rate là lãi su t danh ngh a Khoa QTKD - ThS. Nguy
 npery là s  k  tính lãi trong n m
 Khoa QTKD - ThS. Nguy ễn Kim Nam 47 Khoa QTKD - ThS. Nguy ễn Kim Nam 48
 8
 10/21/2012
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 
 Hàm NOMINAL
 - ây là hàm tính ng  c c a hàm EFFECT .Tính 
 lãi su t danh ngh a hàng nm cho m t kho n 
n n Kim Nam n Kim Nam
   u t .
 - Cú pháp: =NOMINAL(Effect_rate, npery)
 Trong ó: Effect_rate là lãi su t th c t 
 npery là s  k  tính lãi trong n m
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 49 Khoa QTKD - ThS. Nguy ễn Kim Nam 50
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 Các hàm tính giá tr ị đầ u tư ch ứng khoán Ví d:
 
 Hàm ACCRINTM (Accrued Interest at Tính lãi gp cho mt trái phi u kho bc phát hành ngày
 Maturity) 15/02/2005 và ngày ti hn là 18/03/2006 có t su t là
n n Kim Nam n Kim Nam 4%/n m và giá tr  cu n phi u là 1000$. (tính mt nm
 - Tính lãi gp cho mt ch ng khoán tr  vào ngày ti hn  có 365 ngày).
 - Cú pháp: = ACCRINTM(issue, maturity, rate, par, basis) =ACCRINTM("02/15/05","03/18/06",0.04,1000,1)=
 Trong ó: issue là ngày phát hành, maturity là ngày ti 43.397 $
 hn, rate là t su t ca cu n phi u,par là giá tr  mi cu n
Khoa Khoa QTKD - ThS. Nguy phi u. Nu b qua Excel s gán là $1000,basis là s Khoa QTKD - ThS. Nguy
 ngày cơ s.Nu basis = 0 thì nmcó 360 ngày, basis = 1
 thì nm có 365 ngày.
 Khoa QTKD - ThS. Nguy ễn Kim Nam 51 Khoa QTKD - ThS. Nguy ễn Kim Nam 52
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 
 Hàm INTRATE (Interest Rate) Ví d:
 - Tính lãi suât c a m t ch ng khoán  c  u t  Tính lãi su t cho m t ch ng khoán có ngày thanh toán là 
 ht. 01/02/2005, ngày t i h n là 18/06/2006, ti n  u t  là 10 
n n Kim Nam n Kim Nam 000$, ti n thu  c là 12 000$, c ơ s  là 0.
 
 - Cú pháp: =INTRATE(settlement,maturity, =INTRATE("02/01/05","06/18/06",10000,12000,0)=0.145
 investment, redemption, basis)
 Trong ó: settlement là ngày thanh toán, maturity
 là ngày ti hn,investment kho n ti n  u
Khoa Khoa QTKD - ThS. Nguy t,redemption là kho n ti n thu  c vào ngày Khoa QTKD - ThS. Nguy
 ti hn, basis là s ngày cơ s
 Khoa QTKD - ThS. Nguy ễn Kim Nam 53 Khoa QTKD - ThS. Nguy ễn Kim Nam 54
 9
 10/21/2012
 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 22-- NG D NG EXCEL  ÁNH GIÁ HI U QU   U T Ư 
 
 Hàm RECEIVED Ví d:
 - Tính s  ti n thu  c vào ngày t i h n c a m t Tính s ti n thu  c vào ngày ti hn ca mt tín phi u
 ch ng khoán  c  u t h t. kho bc  c  u t ht có ngày thanh toán là
n n Kim Nam n Kim Nam 18/05/2004, ngày ti hn là 18/07/2006, ti n  u t là 20
 - Cú pháp:  000$, t su t chi t kh u là 5.85%, cơ s là 1.
 =RECEIVED(settlement, maturity, investment, =RECEIVED("05/18/04","07/18/06",20000,5.85%,1) =22
 discount, basis) 900.6$
 Trong ó: discount là t  su t chi t kh u, các tham 
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 s khác t ư ng t  hàm INTRATE
 Khoa QTKD - ThS. Nguy ễn Kim Nam 55 Khoa QTKD - ThS. Nguy ễn Kim Nam 56
 33-- NG D NG EXCEL  PHÂN TÍCH HÒA V N 33-- NG D NG EXCEL  PHÂN TÍCH HÒA V N
 Công ty SX gch ch u nhi t ư c tính bi n phí
 ng dng Goal seek  phân tích hòa vn:  sn xu t 1 tn gch ch u nhi t là 2.7 tr ,
 
 Gi  s ta  t giá bình quân trên th  tr ư ng là 3.4 tr , ư c
 Q: Sn l ng hoà vn bi t  nh phí hàng tháng ca côngty là 650
n n Kim Nam n Kim Nam
 B:  nh phí  tr , nh ư vy công ty cn sn su t và tiêu th 
 p: Giá bán 1 sn ph m bao nhiêu tn gch  hoà vn?
 v: Bi n phí 1 sn ph m To vùng d li u nh ư sau:
 
 Doanh thu = Sn l ng * giá bán = p*Q
 
 Chi phí = Bi n phí +  nh phí = v*Q + B
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 
 Khi ó doanh nghi p hòa vn khi
 Doanh thu – Chi phí = 0
 p*Q – (v*Q +B) = 0 => Qhv = B/(p-v)
 Khoa QTKD - ThS. Nguy ễn Kim Nam 57 Khoa QTKD - ThS. Nguy ễn Kim Nam 58
 33-- NG D NG EXCEL  PHÂN TÍCH HÒA V N 33-- NG D NG EXCEL  PHÂN TÍCH HÒA V N
 
 Giá bán ly  vùng d li u (C7=$B$3)
 
 Doanh thu = Giá bán * Sn l ng (C8=C6*C7)
 
 Tng bi n phí = Bi n phí ơ n v * Sn l ng
 (C9=$B$4*C7)
n n Kim Nam n Kim Nam
  
  nh phí ly trên vùng d li u (C10=$B$2)
 
 Tng chi phí = Tng bi n phí+  nh phí
 (C11=C9+C10)
 
 Li nhu n = Doanh thu - Tng chi phí (C12=C8-
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy C11)
 Khoa QTKD - ThS. Nguy ễn Kim Nam 59 Khoa QTKD - ThS. Nguy ễn Kim Nam 60
 10
 10/21/2012
 33-- NG D NG EXCEL  PHÂN TÍCH HÒA V N 33-- NG D NG EXCEL  PHÂN TÍCH HÒA V N
 
  t con tr  ti v trí ô li nhu n 
 Hàm mc tiêu Goal seek tìm ra kt qu  s thông
 
 Vào Tool\Goal Seek báo Goal seeking with C12 found a solution,
 click OK  hoàn tt, khi ó ta s th y ô C6 có giá
 
 Khi hp tho i Goal Seek xu t hi n,  vùng Set cell
 tr  là 929 là sn l ng im hòa vn và ô C12 có
 s có  a ch  C12 ( là  a ch  giá tr  ô li nhu n),
n n Kim Nam n Kim Nam giá tr  là 0 là li nhu n im hòa vn. Kt qu :
 nh p giá tr  0 vào vùng To value , nh p  a ch  C6 
 vào vùng By changing cell (là ô cn thay  i giá
 tr   ô C12  t giá tr  0), sau ó click OK  thi
 hành.
Khoa Khoa QTKD - ThS. Nguy Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 61 Khoa QTKD - ThS. Nguy ễn Kim Nam 62
 33-- NG D NG EXCEL  PHÂN TÍCH HÒA V N
 
 V  th   xác  nh im hòa vn
n n Kim Nam

Khoa Khoa QTKD - ThS. Nguy
 Khoa QTKD - ThS. Nguy ễn Kim Nam 63
 11