Tin học ứng dụng - Ứng dụng excel giải quyết một số bài toán trong kinh tế

4/20/2015 1 TIN HỌC ỨNG DỤNG ỨNG DỤNG EXCEL GIẢI QUYẾT MỘT SỐ BÀI TOÁN TRONG KINH TẾ namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số MỤC TIÊU • Hiểu bài toán dòng tiền, bài toán điểm hòa vốn, bài toán tối ưu trong kinh tế • Sử dụng được phần mềm Excel để giải quyết bài toán dòng tiền, bài toán điểm hòa vốn, bài toán tối ưu trong kinh tế namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số NỘI DUNG • Các hàm tài chính trong Excel • Phân tích điểm hòa vốn • Giải bài toán tối ưu 4/20/2015 2 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l CÁC HÀM TÀI CHÍNH TRONG EXCEL namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số THỰC HIỆN CÁC PHÉP TOÁN TÀI CHÍNH • Lãi ké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 • Giá trị hiện tại ròng và lãi suất nội của dự án đầu tư namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số LÃI KÉP (LÃI GỘ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. 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 4/20/2015 3 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số LÃI KÉP (LÃI GỘP) • 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ỳ namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số LÃI KÉP (LÃI GỘP) • Lãi không nhập gốc (lãi đơn): – Lãi cố định mỗi kỳ: P*r – Sau n kỳ, số tiền nhận được (gồm gốc và lãi nhận được) F = P + n*(P*r) = P * (1 + n*r) • Lãi nhập gốc sau mỗi kỳ: – Số tiền có sau kỳ 1: P1 = P + P*r = P * (1 + r) – Số tiền có sau kỳ 2: P2 = P1 + P1*r = P * (1 + r)2 – Số tiền có sau kỳ n: F = Pn = Pn-1 + Pn-1*r = P*(1 + r)n namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số LÃI KÉP (LÃI GỘP) Tham khảo: Tính lãi kép 4/20/2015 4 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số GIÁ TRỊ TƯƠNG LAI CỦA DÒNG TIỀN ĐỀU • Bài toán 1: Một khách hàng gửi tiết kiệm 100 triệu đồng vào ngân hàng với lãi suất cố định 10%/năm, 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ết kiệm trên. Sau 5 năm, khách hàng có số tiền là bao nhiêu? namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số SỐ TIỀN TRẢ ĐỀU MỖI KỲ • Bài toán 2: 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 • Số tiền khách hàng phải trả cuối mỗi năm? namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số DÒNG TIỀN ĐỀU • 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. – Giá trị tương lai F (giá trị tích lũy/còn lại) của dự án? • P, F, A: 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 - 4/20/2015 5 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số DÒNG TIỀN ĐỀU • Giá trị tương lai của dòng tiền đều: • Giá trị hiện tại của dòng tiền đều: r rAFV n 1)1( −+ ∗= n n n rr rA r FVPV )1( 1)1( )1( + −+ ∗= + = namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số CÁC HÀM TÀI CHÍNH TRONG EXCEL • Hàm tính giá trị tương lai của (dòng) tiền đều FV(rate, nper, pmt, [pV], [type]) – FV: Giá trị tương lai của dòng tiền – RATE: Lãi suất mỗi kỳ (cố định) – PMT: Số tiền nộp vào (rút ra) mỗi kỳ – PV: Số tiền hiện tại (nộp vào/rút ra ban đầu – kỳ 0) – TYPE: Kiểu chi trả (0-cuối kỳ, 1-đầu kỳ) namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số VÍ DỤ: TÍNH GIÁ TRỊ TƯƠNG LAI • Bài toán 1: Một khách hàng gửi tiết kiệm 100 triệu đồng vào ngân hàng với lãi suất cố định 10%/năm, 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ết kiệm trên. Sau 5 năm, khách hàng có số tiền là bao nhiêu? 4/20/2015 6 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số CÁC HÀM TÀI CHÍNH TRONG EXCEL • Hàm tính số tiền nộp vào/rút ra đều mỗi kỳ PMT(rate, nper, PV, [pV], [type]) – FV: Giá trị tương lai của dòng tiền – RATE: Lãi suất mỗi kỳ (cố định) – PMT: Số tiền nộp vào (rút ra) mỗi kỳ – PV: Số tiền hiện tại (nộp vào/rút ra ban đầu – kỳ 0) – TYPE: Kiểu chi trả (0-cuối kỳ, 1-đầu kỳ) namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số VÍ DỤ: TÍNH SỐ TIỀN TRẢ ĐỀU MỖI KỲ • 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. Tính số tiền khách hàng phải trả mỗi tháng? namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số CÁC HÀM TÀI CHÍNH TRONG EXCEL • Hàm tính giá trị hiện tại của dòng tiền PV(rate, nper, PMT, [FV], [type]) • Hàm tính số kỳ của dòng tiền NPER(rate, PMT, PV, [FV], [type]) • Hàm tính lãi suất của dòng tiền RATE(nper, PMT,PV, [FV], [type]) 4/20/2015 7 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số VÍ DỤ VỀ THỜI GIAN CỦA DÒNG TIỀ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, lãi nhập gốc cuối mỗi năm. • Cuối mỗi năm, khách hàng 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 ? namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số VÍ DỤ VỀ LÃI SUẤT • Một tiểu thương vay 10 triệu đồng của người quen, và 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ả. namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số GIÁ TRỊ HIỆN TẠI RÒNG • 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 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ư. – Đối với trường hợp có nhiều dự án: chọn dự án có NPV lớn nhất 4/20/2015 8 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số GIÁ TRỊ HIỆN TẠI RÒNG • Công ty X muốn đầu tư vào một dự án với thời hạn 13 năm với dòng tiền dự báo được (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? namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l Năm Lợi nhuận Năm Lợi nhiận 0 - 10,000,000 7 5,000,000 1 -8,000,000 8 6,000,000 2 0 9 5,000,000 3 1,000,000 10 4,000,000 4 2,000,000 11 3,000,000 5 3,000,000 12 2,000,000 6 4,000,000 13 1,000,000 23 GIÁ TRỊ HIỆN TẠI RÒNG namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số CÁC HÀM TÀI CHÍNH TRONG EXCEL • Hàm tính giá trị hiện tại ròng của dòng tiền NPV(rate, value1, value2, ) – Rate: lãi suất/suất sinh lời/suất chiết khấu – Value1, value2, : khoản chi trả/thu nhập vào cuối mỗi kỳ (bắt đầu từ kỳ 1) 4/20/2015 9 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số LÃI SUẤT NỘI • 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 • IRR là một tiêu chuẩn để đánh giá cơ hội đầu tư – Khi IRR lớn hơn hoặc bằng suất sinh lời kỳ vọng – Khi có nhiều dự án, lựa chọn dự án có IRR lớn nhất namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số CÁC HÀM TÀI CHÍNH TRONG EXCEL • Hàm tính lãi suất nội của dòng tiền IRR(values,[guess]) – Values: vùng giá trị vào/ra của dòng tiền – Guess: giá trị tiên đoán của lãi suất namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số VÍ DỤ LỰA CHỌN DỰ ÁN ĐẦ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. • 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. 4/20/2015 10 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l PHÂN TÍCH ĐIỂM HÒA VỐN CỦA DỰ ÁN namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số PHÂN TÍCH Đ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 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số 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 phí cố định (máy móc, thiết bị, nhà xưởng, chi phí quản lý ) 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 là 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. 4/20/2015 11 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số GIỚI THIỆU BÀI TOÁN • Tổng quát: Sản xuất/kinh doanh lại hàng A cới: – F: Tổng chi phí cố định, không phụ thuộc số lượng sản phẩm – V: Chi phí trực tiếp (sản xuất/bán hàng) cho 1 đơn vị sản phẩm – r: giá bán 1 đơn vị sản phẩm • Yêu cầu: Xác định số lượng sản phẩm tối thiểu để cân bằng giữa doanh thu và chi phí (điểm hòa vốn) namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số 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ỳ, bằng 0). • 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 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số XÂY DỰNG BẢNG TÍNH 4/20/2015 12 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số SỬ DỤNG CÔNG CỤ GOAL SEEK • 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)). • Gọi thực hiện – Data → What–If → Goal Seek namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số ĐỒ THỊ ĐIỂM HÒA VỐN namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l GIẢI BÀI TOÁN TỐI ƯU 4/20/2015 13 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số GIẢI BÀI TOÁ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 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số 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 θ∈ {>. <, =} • 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) namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số 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 • Bài toán qui hoạch phi tuyến tính 4/20/2015 14 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số BÀI TOÁN LẬP KẾ HOẠCH SẢN XUẤT 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 : • Mỗi sản phẩm A, B và C cho lợi nhuận lần lượt là 2, 4 và 3 USD. Hiện tại, xí nghiệp có 600 nguyên liệu 1 và 900 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 Nguyên Liệu Sản phẩm A B C 1 1.5 1.8 1.6 2 2 3 2.4 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số BÀI TOÁN XÁC ĐỊNH KHẨU PHẦN ĂN TỐI ƯU • 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ầ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. Hàm lượng dinh dưỡng (trên 1g thức ăn) Thức ăn A B Protit 0.1 0.2 Lipit 0.1 0.1 Glucit 0.7 0.6 Giá bán (trên 1g) 4 6 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số GIẢI BÀI TOÁN TỐI ƯU TRÊN EXCEL • Quy trình thực hiện – 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ả 4/20/2015 15 namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số CÔNG CỤ SOLVER • 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 – Ins → Go – Xuất hiện hộp thoại Add–In, chọn Solver Add–In namth@buh.edu.vn T in h ọ c ứ n g d ụ n g | B à i 2 : Ứ n g d ụ n g E x c e l những bước chập chững vào thế giới số TÓM TẮT NỘI DUNG • Các hàm tài chính trong Excel • Phân tích điểm hòa vốn • Giải bài toán tối ưu