Bài giảng Tin học ứng dụng Chương 2 ĐH Ngân hàng TP.HCM

BÀI GIẢNG MÔN TIN HỌC ỨNG DỤNG

CHƯƠNG 2

ỨNG DỤNG EXCEL ĐỂ GIẢI QUYẾT MỘT SỐ BÀI TOÁN TRONG KINH TẾ

Bộ môn Khoa học máy tính Khoa Hệ thống thông tin quản lý Trường ĐH Ngân hàng Tp HCM

3/21/2016 Úng dụng Excel trong kinh tế 1

Mục tiêu

• Tóm tắt cơ sở lý thuyết, sử dụng phần mềm Excel để giải quyết một số bài toán cơ bản trong phân tích kinh doanh, tài chính và đầu tư.

3/21/2016 Úng dụng Excel trong kinh tế 2

Nội dung

1. Bài toán tiết kiệm và trả góp 2. Bài toán lựa chọn phương án đầu tư 3. Bài toán điểm hòa vốn 4. Bài toán tìm phương án tối ưu

3/21/2016 Úng dụng Excel trong kinh tế 3

1. Bài toán tiết kiệm và trả góp

• Lãi kép • Bài toán tiết kiệm • Bài toán trả góp • Giá trị hiện tại, giá trị tương lai, lãi suất và số

kỳ của dòng tiền đều

• Tính toán với các hàm tài chính của Excel

3/21/2016 Úng dụng Excel trong kinh tế 4

Lãi kép

• Bài toán Một khách hàng gửi số tiền 100 triệu

đồng vào ngân hàng với lãi suất cố định 10%/năm, tính lãi cuối mỗi năm.

• Sau 5 năm, khách hàng tới thanh toán. Hỏi khách nhận được bao nhiêu tiền trong các trường hợp: – Lãi không nhập gốc sau mỗi năm. – Lãi nhập gốc sau mỗi năm.

3/21/2016 Úng dụng Excel trong kinh tế 5

Lãi kép (tt)

• Tổng quát: Đầu tư số tiền P vào một dự án với lãi suất (suất sinh lời) r%/kỳ, tính lãi cuối mỗi kỳ.

• Yêu cầu: Tính số tiền F được nhận sau n kỳ

trong các trường hợp lãi không nhập gốc và lãi nhập gốc sau mỗi kỳ.

3/21/2016 Úng dụng Excel trong kinh tế 6

Lãi kép (tt)

• Lãi không nhập gốc (lãi đơn): Lãi cố định mỗi kỳ: P * r

F = P + m * (P*r) = P * (1 + n*r) • Lãi nhập gốc sau mỗi kỳ: P1 = P + P*r = P * (1 + r) P2 = P1 + P1 *r = P1 * (1 + r) = P * (1 + r)2 F = Pn = Pn-1 + Pn-1 *r = Pn-1 * (1 + r) = P * (1 + r)n

3/21/2016 Úng dụng Excel trong kinh tế 7

Ví dụ: Số tiền tích lũy sau mỗi năm

3/21/2016 Úng dụng Excel trong kinh tế 8

Bài toán tiết kiệm

• Bài toán Một khách hàng gửi 100 triệu đồng vào ngân hàng với lãi suất cố định 10%/năm, tính lãi nhập gốc cuối mỗi năm.

• Cuối mỗi năm, khách gửi thêm 1 triệu đồng vào số tiền tiết kiệm trên. Hỏi 5 năm, số tiền khách có số tiền là bao nhiêu?

3/21/2016 Úng dụng Excel trong kinh tế 9

Bài toán trả góp

• Bài toán Một khách hàng vay ngân hàng 100 triệu đồng, thời hạn 5 năm với lãi suất cố định 10%/năm, trả góp cuối mỗi năm với số tiền đều nhau. Hỏi số tiền khách phải trả cuối mỗi năm?

3/21/2016 Úng dụng Excel trong kinh tế 10

Tổng quát

• Cho dự án thực hiện trong n kỳ với số tiền đầu tư ban đầu P, lãi suất (suất sinh lời)/kỳ r, số tiền đầu tư thêm (hoặc rút ra) mỗi kỳ A. Hỏi giá trị tương lai F (giá trị tích lũy/còn lại) của dự án?

• P, F, A: các dòng tiền của dự án. • A bằng nhau cho các kỳ => dòng tiền đều. • Dòng tiền vào (nhận, vay) mang dấu +, dòng

tiền ra (đầu tư, trả, cho vay) mang dấu -.

Úng dụng Excel trong kinh tế 3/21/2016 11

Giá trị hiện tại, giá trị tương lai, lãi suất, số kỳ của dòng tiền đều

• Giá trị tương lai của dòng tiền đều:



n 1 





) r r

• Giá trị hiện tại của dòng tiền đều:



 A

FV  r

)

1( r

 ) r 1( 

 n )

3/21/2016 Úng dụng Excel trong kinh tế 12

Tính toán với các hàm tài chính Excel

• Tham số chung của các hàm tài chính trong Excel

pV fV Rate nper Pmt Type

: giá trị hiện tại. : giá trị tương lai. : lãi suất/suất sinh lời/suất chiết khấu mỗi kỳ. : số kỳ. : số tiền chi trả mỗi kỳ. : kiểu chi trả (1 –đầu kỳ, 0 –

cuối kỳ). Giá trị mặc định của Type là 0.

3/21/2016 Úng dụng Excel trong kinh tế 13

Tính toán với các hàm tài chính Excl (tt) • Tính giá trị tương lai của các dòng tiền

FV(rate, nper, pmt, [pV], [type])

• Tính số tiền trả mội kỳ

PMT(rate, nper, pv, [fV], [type])

• Tính giá trị hiện tại

PV(rate, nper, pmt, [fV], [type])

• Tính số kỳ

NPER(rate, pmt, pv, [fV], [type])

• Tính lãi suất:

RATE(nper, pmt, pv, [fV], [type])

3/21/2016 Úng dụng Excel trong kinh tế 14

Ví dụ 1: Tính giá trị tương lai của các khoản tiết kiệm

• Một khách hàng gửi 100 triệu đồng vào ngân hàng với lãi suất cố định 10%/năm, tính lãi nhập gốc cuối mỗi năm.

• Cuối mỗi năm, khách gửi thêm 10 triệu đồng vào số tiền tiết kiệm trên. Hỏi 5 năm, số tiền khách có số tiền là bao nhiêu?

3/21/2016 Úng dụng Excel trong kinh tế 15

Thực hiện

3/21/2016 Úng dụng Excel trong kinh tế 16

Ví dụ 2: Tính số tiền trả đều mỗi kỳ

• Một khách hàng vay ngân hàng 100 triệu đồng, thời hạn 2 năm (24 tháng), lãi suất 1%/tháng, trả gốc + lãi cuối mỗi tháng với số tiền đều nhau. Hỏi số tiền khách phải trả mỗi tháng?

3/21/2016 Úng dụng Excel trong kinh tế 17

Thực hiện

3/21/2016 Úng dụng Excel trong kinh tế 18

Ví dụ 3: Ra quyết định đầu tư

• Công ty X muốn đầu tư vào một dự án. Các nghiên cứu cho thấy rằng công ty phải bỏ ra $1,000,000 vốn đầu tư ban đầu, và sau đó sẽ thu về $140,000 mỗi năm trong 12 năm kế tiếp. Nếu không, công ty có thể đầu tư vào các dự án khác với lãi suất 8%/năm. Công ty có nên thực hiện dự án này hay không?

3/21/2016 Úng dụng Excel trong kinh tế 19

Thực hiện

• Tính giá hiện tại của các khoản thu về (chiết khấu

8% = suất sinh lời của các dự án khác)

• Giá HT của các của các khoản thu lớn hơn số tiền

đầu tư => có thể đầu tư.

3/21/2016 Úng dụng Excel trong kinh tế 20

Ví dụ 4: Tính số kỳ tiết kiệm/trả góp

• Một khách hàng gửi 100 triệu đồng vào ngân hàng với lãi suất cố định 10%/năm, tính lãi nhập gốc cuối mỗi năm. Cuối mỗi năm, khách gửi thêm 10 triệu đồng vào sổ. Hỏi sau bao nhiêu năm khách có số tiền tích lũy 300 triệu đồng?

3/21/2016 Úng dụng Excel trong kinh tế 21

Thực hiện

3/21/2016 Úng dụng Excel trong kinh tế 22

Ví dụ 5: Tính lãi suất

• Một tiểu thương vào 10 triệu đồng của người quen, sau trả góp trong 12 tháng, mỗi tháng trả 1 triệu đồng. Tính lãi suất mà người này phải trả.

3/21/2016 Úng dụng Excel trong kinh tế 23

Thực hiện

3/21/2016 Úng dụng Excel trong kinh tế 24

2. Bài toán lựa chọn phương án đầu tư

• Cho dự án đầu tư thực hiện trong nhiều kỳ với

dòng tiền (thu, chi) thay đổi trong các kỳ. • Nên đàu tư vào dự án được nêu hay gửi tiết kiệm/đầu tư vào các dự án khác vói lãi suất cho sẵn ?

• Phương pháp: Phân tích NPV và/hoặc IRR.

3/21/2016 Úng dụng Excel trong kinh tế 25

Giá trị hiện tại ròng (NPV)

• Giá trị hiện tại ròng (Net Present Value – NPV): Tổng giá trị hiện tại (đã chiết khấu) của các dòng tiền trong dự án.

• Sử dụng để quyết định đầu tư dự án

– NPV <0 : không đầu tư. – NPV = 0: có thể đàu tư/không đầu tư. – NPV > 0: nên đầu tư. – Nhiều dự án: chọn dự án có NPV lớn nhất

3/21/2016 Úng dụng Excel trong kinh tế 26

Giá trị hiện tại ròng (tt)

• Bài toán Công ty X muốn đầu tư vào một dự án với thời hạn13 năm với dòng tiền dự báo được nêu trong bảng. Nếu không đầu tư vào dự án này, công ty có thể đầu tư vào các dự án khác với tỷ suất lợi nhuận 8% /năm. Công ty có nên đầu tư vào dự án này không?

3/21/2016 Úng dụng Excel trong kinh tế 27

Giá trị hiện tại ròng (tt)

Năm 0 1 2 3 4 5 6

Lợi nhuận - 10,000,000 -8,000,000 0 1,000,000 2,000,000 3,000,000 4,000,000

Năm 7 8 9 10 11 12 13

Lợi nhiận 5,000,000 6,000,000 5,000,000 4,000,000 3,000,000 2,000,000 1,000,000

3/21/2016 Úng dụng Excel trong kinh tế 28

3/21/2016 Úng dụng Excel trong kinh tế 29

Hàm NPV

• Cú pháp

NPV(rate, value1, value2,…)

Với rate : lãi suất value1, value2,… : khoản chi trả cuối các kỳ

(bắt đầu từ kỳ 1).

3/21/2016 Úng dụng Excel trong kinh tế 30

Lãi suất nội (IRR)

• NPV dự án phụ thuộc vào tỷ suất hoàn vốn (Lãi

suất) => giảm khi lãi suất tăng.

• IRR (lãi suất nội ( Internal Rate of Return – IRR):

Lãi suất tại điểm NPV = 0

• Là một tiêu chuẩn để đánh giá các cơ hội đầu tư. • Hàm IRR(values, [guess]): nh IRR với

– values: vùng giá trị lưu lượng tiền mặt. – guess: giá trị tiên đoán.

3/21/2016 Úng dụng Excel trong kinh tế 31

Ví dụ

• Cho bảng lưu lượng tiền mặt của một dự án. Khảo sát mối quan hệ giữa NPV và lãi suất (từ 0% cho tới 21%)

Năm

Dòng tiền

Năm

Dòng tiền

4 5 6

30000 35000 40000

0 1 2 3

-100000 15000 20000 25000

3/21/2016 Úng dụng Excel trong kinh tế 32

3/21/2016 Úng dụng Excel trong kinh tế 33

Sử dụng IRR để so sánh các cơ hội đầu tư

• Quyết định đầu tư: khi IRR lớn hơn hoặc bằng

một tỷ suất nhất định do HĐQT ấn định.

• Có nhiều dự án để lựa chọn => chọn dự án có

IRR cao nhất.

3/21/2016 Úng dụng Excel trong kinh tế 34

Ví dụ: So sánh khả năng đầu tư

• Cho hai dự án A và B cùng có thời gian thực

hiện 6 năm với các thông tin sau: – Dự án A: đầu tư 3.5 triệu USD, thu về mỗi

năm 1.2 triệu USD.

– Dự án B: đầu tư 3.5 triệu USD, lần lượt thu về 0.9, 1.1, 1.3, 1.5, 1.2, 0.8 triệu USD trong các năm từ năm 1 tới năm 6.

3/21/2016 Úng dụng Excel trong kinh tế 35

Ví dụ: So sánh khả năng đầu tư (tt)

• Nếu không đầu tư vào các dự án trên, công ty có thể đầu tư vào các dự án khác với lại suất 8%/năm. So sánh các khả năng đầu tư trên theo phương pháp phân tích NPV và phân tích IRR.

3/21/2016 Úng dụng Excel trong kinh tế 36

3/21/2016 Úng dụng Excel trong kinh tế 37

3. Bài toán điểm hòa vốn

• Giới thiệu bài toán • Xây dựng bảng tính • Tìm điểm hòa vốn với công cụ Goal Seek • Vẽ đồ thị điểm hòa vốn

3/21/2016 Úng dụng Excel trong kinh tế 38

Giới thiệu bài toán

• Một xí nghiệp lập kế hoạch sản xuất sản phẩm A với số lượng 3000 đơn vị. Để thực hiện công việc trên, theo tính toán, công ty phải bỏ ra một khoản chi chí cố định (máy móc, thiết bị, nhà xưởng, chi phí quản lý…) 15 triệu USD. Mỗi sản phẩm làm ra có chi phí sản xuất + bán hàng 10,000 USD và bán được với giá 20,000 USD. Tính số sản phẩm tối thiểu phải sản xuất để hòa vốn.

Úng dụng Excel trong kinh tế 3/21/2016 39

Giới thiệu bài toán (tt)

• Tổng quát: Sản xuất/kinh doanh loại hàng A

với: – F: Tổng chi phí cố định, không phụ thuộc số

lượng SP (định phí).

– V: Chi phí trực tiếp (SX/bán hàng) cho 1

ĐVSP (chi phí đơn vị). – r: giá bán 1 đơn vị SP.

• Yêu cầu: Xác định số SP tối thiểu để cân bằng giữa doanh thu và chi phí (điểm hòa vốn).

3/21/2016 Úng dụng Excel trong kinh tế 40

Xây dựng bảng tính

• Ô dữ liệu: F, v, r. • Ô biến Q: Số lượng sản phẩm (giá trị bất kỳ). • Biến trung gian

– [TC] (Tổng chi phí) = F + v * Q – [DT] (Doanh thu) = r * Q

• Hàm mục tiêu

– [LN] (Lợi nhuận) = DT – TC

3/21/2016 Úng dụng Excel trong kinh tế 41

Xây dựng bảng tính

3/21/2016 Úng dụng Excel trong kinh tế 42

Sử dụng công cụ Goal Seek để tìm điểm hòa vốn với

• Cho hàm số y = f(x). • Goal Seek: tìm x sao cho f(x)  a cho trước. • Xây dựng bảng tính:

– X: ô biến, khởi đầu bằng giá trị tiên đoán

bất kỳ.

– y: ô công thức phụ thuộc x (f(x)).

• Vào Data  What – If  Goal Seek. Đặt Set cell [y] To value a By changing cell [x].

3/21/2016 Úng dụng Excel trong kinh tế 43

Tìm điểm hòa vốn với công cụ Goal Seek

3/21/2016 Úng dụng Excel trong kinh tế 44

Vẽ đồ thị điểm hòa vốn

3/21/2016 Úng dụng Excel trong kinh tế 45

4. Bài toán tìm phương án tối ưu

• Giới thiệu bài toán • Mô hình hóa bài toán • Xây dựng bảng tính • Tìm giải pháp tối ưu với công cụ Solver • Một số lỗi thường gặp

3/21/2016 Úng dụng Excel trong kinh tế 46

Giới thiệu bài toán

• Cho n biến độc lập x1, x2, … xn thỏa mãn điều

kiện ràng buộc:

(, , … , ) (, , … , ) … (, , … , )

Với  {>. <, =}

3/21/2016 Úng dụng Excel trong kinh tế 47

Giới thiệu bài toán (tt)

• Cho hàm mục tiêu G(x1, x2, … xn) • Yêu cầu: Tìm bộ giá trị (x1, x2, … xn) thỏa mãn tập ràng buộc {F1, F2, …, Fm} sao cho G cực đại (hoặc cực tiểu ).

3/21/2016 Úng dụng Excel trong kinh tế 48

Các dạng bài toán tối ưu

• Bài toán tìm cực đại. • Bài toán tìm cực tiểu • Bài toán qui hoạch tuyến tính (Linear

Programing).

• Bài toán qui hoạch phi tuyến tính (Nin-linear

Programing).

3/21/2016 Úng dụng Excel trong kinh tế 49

Ví dụ: Bài toán lập KHSX tối ưu

• Một xí nghiệp sản xuất 3 loại sản phẩm A, B, C từ 2 loại nguyên liệu 1 và 2 với định mức cho mỗi sản phẩm như sau :

Sản phẩm

Nguyên Liệu

1 2

A 1.5 2

B 1.8 3

C 1.6 2.4

3/21/2016 Úng dụng Excel trong kinh tế 50

Ví dụ: Bài toán lập KHSX tối ưu (tt)

Mỗi sản phẩm A, B nà C cho lợi nhuận lần lượt là 2, 4 và 3 đơn vị tiền tệ. Hiện tại, xí nghiệp có 600 đơn vị nguyên liệu 1 và 900 đơn vị nguyên liệu 2. Giả sử toàn bộ sản phẩm sản xuất ra đều có thể tiêu thụ hết, hãy lập kế hoạch sản xuất tối ưu cho xí nghiệp.

3/21/2016 Úng dụng Excel trong kinh tế 51

Bài toán lập KHSX tối ưu (tt)

• Các ô cơ bản: – Ô dữ liệu. – Ô biến độc lập (giá trị tùy ý). – Ô đích (công thức liên quan tới các biến). – Ô tham chiếu trong các ràng buộc (công

thức liên quan tới các biến).

3/21/2016 Úng dụng Excel trong kinh tế 52

Bài toán lập KHSX tối ưu (tt)

• Gọi x1, x2 nà x3 lần lượt là số sản phẩm A, B và

C được sản xuất. Ta có ràng buộc 1.5 x1 + 1.8 x2 + 1.6 x3 < 600 2 x1 + 3 x2 + 2.4 x3 < 900 x1, x2, x3 là số nguyên > 0

3/21/2016 Úng dụng Excel trong kinh tế 53

Bài toán lập KHSX tối ưu (tt)

• Lợi nhuận thu được

G = 2 x1 + 4 x2 + 3 x3

• Yêu cầu: tìm giá trị của x1, x2, x3 sao cho G cực

đại.

3/21/2016 Úng dụng Excel trong kinh tế 54

Ví dụ 2: Bài toán xác định khẩu phần thức ăn

• Một nhà chăn nuôi ước tính rằng, để phát triển tốt, mỗi ngày đàn vật nuôi của mình cần ít nhất 700g protit, 300g lipit và 4200g gluxit. Ngoài thị trường hiện có hai loại thức ăn A và B với hàm lượng dinh dưỡng và giá cả (cho 1g) nêu trong bảng dưới đây. Hãy xác định lượng thức ăn tối ưu cho đàn vật nuôi.

3/21/2016 Úng dụng Excel trong kinh tế 55

Bài toán xác định khẩu phần thức ăn (tt)

Thức ăn

Hàm lượng dinh dưỡng (trên 1g thức ăn) Protit

0.1

0.2

Lipit

0.1

Glucit

0.7

0.6

Giá bán (trên 1g)

3/21/2016 Úng dụng Excel trong kinh tế 56

Bài toán xác định khẩu phần thức ăn (tt)

• Gọi x1 và x2 lần lượt là số gram thức ăn A và B

cần mua. Ta có các ràng buộc:

Protit : 0.1 x1 + 0.2 x2 > 700 Lipit : 0.1 x1 + 0.1 x2 > 300 Gluxit : 0.7 x1 + 0.6 x2 > 4200

3/21/2016 Úng dụng Excel trong kinh tế 57

Bài toán xác định khẩu phần thức ăn (tt)

• Hàm chi phí

G = 4x1+ 6x2

• Vấn đề: Tìm các giá trị của x1 và x2 để G cực

tiểu.

3/21/2016 Úng dụng Excel trong kinh tế 58

Giải bài toán tối ưu trên Excel

• Quy trình:

– Xây dựng bảng tính để giải bài toán. – Sử dụng công cụ Solver để tìm phương án

tối ưu.

– Đánh giá kết quả.

3/21/2016 Úng dụng Excel trong kinh tế 59

Xây dựng bảng tính

• Các thành phần cơ bản: • Ô dữ liệu. • Ô biến độc lập, khởi đầu bằng các giá trị tiên

đoán.

• Ô công thức (hàm đích & vế trái các ràng

buộc), giá trị phụ thuộc vào các ô biến độc lập.

3/21/2016 Úng dụng Excel trong kinh tế 60

Sử dụng công cụ Solver để tìm phương án tối ưu.

• Solver: Công cụ Add – In, cần cài thêm trước

khi sử dụng. • Cài đặt Solver:

– Vào File/Options  Add – Uns  Go. – Xuất hiện hộp thoại Add – In, đánh dấu mục

Solver Add – In.

3/21/2016 Úng dụng Excel trong kinh tế 61

3/21/2016 Úng dụng Excel trong kinh tế 62

Các tính năng của Solver

• Cho phép ấn định giá trị ô đích theo nhiều tiêu chuẩn (max, min, value) bằng việc thay đổi giá trị của nhiều ô độc lập (By changing cells) thỏa mãn một số điều kiện ràng buộc nhất định. • Có nhiều thuật toán tìm nghiệm hơn Goal

Seek.

• Chức năng Solver Results cho phép tạo báo

cáo kết quả.

3/21/2016 Úng dụng Excel trong kinh tế 63

3/21/2016 Úng dụng Excel trong kinh tế 64

Hộp thoại Sover

• Set objective: Ô đích (To Max: lớn nhất, Min: nhỏ

nhất, Value: Giá trị).

• By Changing Variable Cells: Vùng biến. • Subject to Constraints: Các ràng buộc. • Select a Solving Method: Lựa chọn PP giải: – Simplex LP: Giải bài toán tuyến tính. – GRP Non-Linear: Giải bài toán phi tuyến. – Evolutionary: Giải bài toán phi phẳng (Non-

smooth).

3/21/2016 Úng dụng Excel trong kinh tế 65