Bài giảng Tin học ứng dụng - Chương 3: Ứng dụng Excel giải các bài toán kinh tế

nh IRR:
 – Sử dụng phương pháp đồ thị;
 – Sử dụng hàm
 IRR(values, [guess])
 với
 values: vùng giá trị lưu lượng tiền
 mặt. guess: giá trị tiên đoán.
10/1/2018 Úng dụng Excel trong kinh tế 41
Ví dụ 1: Tính NPV
• 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 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?
10/1/2018 Úng dụng Excel trong kinh tế 42
Ví dụ 1: Tính NPV (tt)
 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
10/1/2018 Úng dụng Excel trong kinh tế 43
10/1/2018 Úng dụng Excel trong kinh tế 44
Ví dụ 2: Tính IRR
• 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
 0 -100000 4 30000
 1 15000 5 35000
 2 20000 6 40000
 3 25000
10/1/2018 Úng dụng Excel trong kinh tế 45
Ví dụ 2: Tính IRR
10/1/2018 Úng dụng Excel trong kinh tế 46
Ví dụ 3: 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.
10/1/2018 Úng dụng Excel trong kinh tế 47
Ví dụ 3: 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.
10/1/2018 Úng dụng Excel trong kinh tế 48
10/1/2018 Úng dụng Excel trong kinh tế 49
3.3. Bài toán tìm phương án SX-KD tối ưu
• Giới thiệu
• 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
10/1/2018 Úng dụng Excel trong kinh tế 50
Giới thiệu
• Bài toán tối ưu: BT tìm lời giải tốt nhất (hoặc
 gần tốt nhất) trong tất cả các lời giải khả thi.
• Phổ biến trong các lĩnh vực SX – KD - VT.
• Ví dụ: 
 – BT Lập kế hoạch tối ưu (CP nhỏ nhất/LN 
 cao nhất) để SX/KD một/một số loại SP 
 nào đó.
 – BT tìm phương án tối ưu (CP nhỏ nhất) để
 vận chuyển hàng từ một số địa điểm tới
 một số địa điểm khác.
10/1/2018 Úng dụng Excel trong kinh tế 51
Giới thiệu (tt)
• Sử dụng Excel để giải bài toán tối uu:
 – Mô hình hóa bài toán;
 – Xây dựng bảng tính;
 – Sử dụng công cụ Solver để tìm lời giải
 tối ưu.
• Lời giải: Phương án SX-KD tối ưu (có Chi 
 phí thấp nhất (Lợi nhuận cao nhất).
10/1/2018 Úng dụng Excel trong kinh tế 52
Mô hình hóa bài toán
• Mô hình chung của các bài toán tối ưu
 BÀI TOÁN
 MỤC TIÊU
 PHƯƠNG ÁN RÀNG BUỘC
 (LÒI GIẢI)
 LN MAX/CP 
 MIN 
10/1/2018 Úng dụng Excel trong kinh tế 53
Mô hình hóa bài toán tt)
• Tập biến độc lập X = {x1, x2,  xn }
• Ràng buộc:
 퐹1( 1, 2,  , 푛)휃 1
 퐹2( 1, 2,  , 푛)휃 2
 퐹 ( 1, 2,  , 푛)휃 
 ( {>, <, =})
10/1/2018 Úng dụng Excel trong kinh tế 54
Mô hình hóa bài toán tt)
• Một số ràng buộc khác:
 – Ràng buộc nguyên ;
 – Ràng buộc không âm;
 – Ràng buộc nhị phân (giá trị 1 hoặc 0));
 – 
• Hàm mục tiêu G(x1, x2,  xn)
10/1/2018 Úng dụng Excel trong kinh tế 55
Mô hình hóa bài toán (tt)
• Yêu cầu: Tìm bộ giá trị (x1, x2,  xn) thỏa
 mãn tập ràng buộc sao cho G có giá trị
 lớn nhất (hoặc nhỏ nhất).
10/1/2018 Úng dụng Excel trong kinh tế 56
Ví dụ 1: Bài toán 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 :
 Nguyên Sản phẩm
 Liệu A B C
 1 1.5 1.8 1.6
 2 2 3 2.4
10/1/2018 Úng dụng Excel trong kinh tế 57
Ví dụ 1: Bài toán 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.
10/1/2018 Úng dụng Excel trong kinh tế 58
Mô hình hóa bài toán
• 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
10/1/2018 Úng dụng Excel trong kinh tế 59
Mô hình hóa bài toán (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.
10/1/2018 Úng dụng Excel trong kinh tế 60
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.
10/1/2018 Úng dụng Excel trong kinh tế 61
 VD 2: Bài toán
 XĐ khẩu phần thức ăn (tt)
 Hàm lượng dinh Thức ăn
 dưỡng (trên 1g A B
 thức ăn)
 Protit 0.1 0.2
 Lipit 0.1 0.1
 Glucit 0.7 0.6
 Giá bán (trên 1g) 4 6
10/1/2018 Úng dụng Excel trong kinh tế 62
Mô hình hóa bài toán
• 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
10/1/2018 Úng dụng Excel trong kinh tế 63
Mô hình hóa bài toá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.
10/1/2018 Úng dụng Excel trong kinh tế 64
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.
10/1/2018 Úng dụng Excel trong kinh tế 65
Ví dụ 1: Lập KHSX tối ưu (tt)
10/1/2018 Úng dụng Excel trong kinh tế 66
Ví dụ 2: Khẩu phần ăn tối ưu (tt)
10/1/2018 Úng dụng Excel trong kinh tế
Sử dụng công cụ Solver để tìm phương án
tối ưu
• Solver: Công cụ tìm phương án tối ưu
 theo nhiều tiêu chí (max, min, value.).
• Cho phép tính giá trị nhiều biến độc lập
 thỏa mãn một số ràng buộc xác định.
• Có nhiều thuật toán tìm nghiệm.
• Chức năng Solver Results cho phép tạo
 báo cáo kết quả.
• Công cụ Add – In, cần cài thêm trước khi
 sử dụng.
10/1/2018 Úng dụng Excel trong kinh tế 68
Sử dụng công cụ Solver để tìm phương án
tối ưu (tt)
 Cài thêm
 Solver
10/1/2018 Úng dụng Excel trong kinh tế 69
 Tiêu chí Ô đích
 Hộp thoại
 Solver
Biến độc lập
 Ràng buộc
 10/1/2018 Úng dụng Excel trong kinh tế 70
Ràng buộc trong Solver
 Ô công thức Toán tử so sánh Giá trị hằng
 ▪ Toán tử so sánh: >=, <=, =, int (số
 nguyên), bin (giá trị nhị phân), dif (khác).
10/1/2018 Úng dụng Excel trong kinh tế 71
Ví dụ: Lập KHSX tối ưu (tt)
10/1/2018 Úng dụng Excel trong kinh tế 72
10/1/2018 Úng dụng Excel trong kinh tế 73
10/1/2018 Úng dụng Excel trong kinh tế 74
Ví dụ: Khẩu phần ăn tối ưu (tt)
10/1/2018 Úng dụng Excel trong kinh tế 75
10/1/2018 Úng dụng Excel trong kinh tế 76
 Tham khảo
10/1/2018 Úng dụng Excel trong kinh tế 77
Một số thông báo lỗi thường gặp
• Solver could not find feasible solution: Không
 có lời giải chấp nhận được giá trị khởi đầu của
 các biến số quá xa các giá trị tối ưu.
• The maximum iteration was reached, continue
 anyway? Số bước lặp đã đạt đến giá trị giới hạn
 được cho.
• The maximum time limit was reached,
 continue anyway? Thời gian chạy vượt quá giới
 hạn lựa chọn.
10/1/2018 Úng dụng Excel trong kinh tế 78
3.4. Bài toán điểm hòa vốn
• Giới thiệu
• Tóm lược lý thuyết
• 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
10/1/2018 Úng dụng Excel trong kinh tế 79
Giới thiệu
• Doanh nghiệp lập kế hoạch SX-KD một
 loại sản phẩm dựa trên các yếu tố dự kiến
 về sản lượng, thời gian,, giá, chi phí.
• Yêu cầu: Xác định điểm hòa vốn (khối
 lượng hòa vốn, doanh thu hòa vốn, thời
 gian hòa vốn, công suất hòa vốn, ), vẽ
 biểu đồ hòa vốn cho doanh nghiệp.
10/1/2018 Úng dụng Excel trong kinh tế 80
Tóm lược lý thuyết
• Một số khái niệm cơ bản:
 – Điểm hòa vốn: Qui mô SX-KD, tại đó
 Tổng doanh thu = Tổng chi phí.
 – Số lượng SP hòa vốn (SLHV)
 – Doanh thu hòa vốn
 – Thời gian hòa vốn
 – Công suất hòa vốn: tỷ lệ KLHV trên tổng
 KL SP trong kỳ
10/1/2018 Úng dụng Excel trong kinh tế 81
Tóm lược lý thuyết (tt)
• Một số khái niệm cơ bản (tt)
 – Định phí (ĐP)
 – Biến phí
 – Hiệu số gộp: hiệu số Giá bán – Biến phí
 ĐV
10/1/2018 Úng dụng Excel trong kinh tế 82
Tóm lược lý thuyết (tt)
• Phương pháp xác địnhđiểm hòa vốn
 – Phương pháp đại số;
 – Sử dụng công cụ Goal Seek trên Excel.
10/1/2018 Úng dụng Excel trong kinh tế 83
Tóm lược lý thuyết (tt)
• Các công thức liên quan:
 ổ푛𝑔 Đ푃
 푆ố 푙ượ푛𝑔 =
 𝑖á á푛 − 𝑖ế푛 ℎí Đ 
 ổ푛𝑔 Đ푃
 =
 𝑖ệ 푠ố 𝑔ộ 
 표 푛ℎ 푡ℎ = 푆퐿 ∗ 𝑖á á푛
 푆퐿 
 ô푛𝑔 푠 ấ푡 =
 푆퐿푆푃 ự 𝑖ế푛
10/1/2018 Úng dụng Excel trong kinh tế 84
Tóm lược lý thuyết (tt)
 표 푛ℎ 푡ℎ 
 ℎờ𝑖 𝑔𝑖 푛 =
 표 푛ℎ 푡ℎ 푄푛𝑔à 
 = ô푛𝑔 푠 ấ푡 ∗ ℎờ𝑖 𝑔𝑖 푛 ự 𝑖ế푛
 푆퐿 
 = ∗ ℎờ𝑖 𝑔𝑖 푛 ự 𝑖ế푛
 푆퐿푆푃 ự 𝑖ế푛
10/1/2018 Úng dụng Excel trong kinh tế 85
Sử dụng công cụ Goal Seek để tìm điểm
hòa vốn
• Goal Seek: thành phần trong bộ công cụ
 Data | What – If của Excel.
• Cho hàm số y = f(x) => Tìm x sao cho f(x)
 a
• Xây dựng bảng tính: 
 – X: ô biến độc lập, khởi đầu bằng giá trị
 tiên đoán bất kỳ.
 – y: ô công thức (f(x)).
10/1/2018 Úng dụng Excel trong kinh tế 86
Ví dụ: Xác định điểm hòa vốn
• Công ty X lập kế hoạch sản xuất sản phẩm A
 với số lượng 3000 SP, thực hiện trong 6 tháng.
 Để 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.
10/1/2018 Úng dụng Excel trong kinh tế 87
Ví dụ: Xác định điểm hòa vốn (tt)
• Yêu cầu: Xác định điểm hòa vốn (Số
 lượng SP hòa vốn, doanh thu hòa vốn, 
 công suất hòa vốn, thời gian hòa vốn) cho
 công ty.
10/1/2018 Úng dụng Excel trong kinh tế 88
Xây dựng bảng tính
10/1/2018 Úng dụng Excel trong kinh tế 89
Tìm điểm hòa vốn với công cụ
Goal Seek 
10/1/2018 Úng dụng Excel trong kinh tế 90
Tìm điểm hòa vốn với công cụ
Goal Seek (tt)
10/1/2018 Úng dụng Excel trong kinh tế 91
Vẽ đồ thị điểm hòa vốn
• Lập bảng số liệu vẽ bểu đồ (Khối lượng
 SP, Doanh thu, Tổng ĐP, Tổng BP, Tổng 
 CP);
• Tạo và hiệu chỉnh biểu đồ.
10/1/2018 Úng dụng Excel trong kinh tế 92
Vẽ đồ thị điểm hòa vốn
10/1/2018 Úng dụng Excel trong kinh tế 93
Ghi chú
• Tương tự, có thể sử dụng công cụ Soal
 Seek để xác định giá bán hòa vốn và trả
 lời nhiều câu hỏi khác.
10/1/2018 Úng dụng Excel trong kinh tế 94
3.5. PT độ nhạy và PT tình huống
• Giới thiệu
• Phân tích độ nhạy
• Phân tích tình huống
10/1/2018 Úng dụng Excel trong kinh tế 95
Giới thiệu
• Doanh nghiệp lập kế hoạch SX – KD, tính
 doanh thu, chi phí, lợi nhuận, nhu cầu về
 vốn dựa trên các dự báo về sản lượng, 
 giá cả cùng một loạt các yếu tố khác.
 Điều gì xảy ra
 nếu ?
10/1/2018 Úng dụng Excel trong kinh tế 96
Giới thiệu (tt)
• Một số kỹ thuật phân tích rủi ro
 – Phân tích độ nhạy;
 – Phân tích tình huống;
 – Phân tích mô phỏng;
 – Phân tích rủi ro với các hàm XS – TK.
10/1/2018 Úng dụng Excel trong kinh tế 97
Phân tích độ nhạy
• Cho phép đánh giá mức độ ảnh hưởng của các
 yếu tố đầu vào đối với kết quả bài toán.
• Ý nghĩa:
 – Cung cấp các thông tin về mức độ biến thiên
 có thể có của các thông số cần biết.
 – Giúp các nhà QL xác định được các yếu tố có
 ảnh hưởng mạnh nhất tới kết quả dự báo để
 có các quyết sách phù hợp.
10/1/2018 Úng dụng Excel trong kinh tế 98
Phân tích độ nhạy (tt)
• Kỹ thuật phân tích độ nhạy:
 – Phân tích độ nhạy một chiều;
 – Phân tích độ nhạy hai chiều.
10/1/2018 Úng dụng Excel trong kinh tế 99
Phân tích độ nhạy một chiều
• Cho phép lần lượt đánh giá mức độ tác động
 của từng yếu tố đầu vào tới kết quả bài toán.
• Ví dụ: Công ty XYZ lập kế hoạch sản xuất năm
 2018 cho loại hàng A (xem bảng). Lập bảng
 phân tích độ nhạy một chiều nhằm đánh giá
 mức độ tác động của giá bán, và chi phí NVL 
 chính tới lợi nhuận.
10/1/2018 Úng dụng Excel trong kinh tế 100
Phân tích độ nhạy một chiều (tt)
10/1/2018 Úng dụng Excel trong kinh tế 101
• Thực hiện: 
 – Tạo bảng phân tích độ nhạy một chiều;
 – Quét vùng bảng phân tích:
 – Mở hộp thoại Data Table (lệnh Data | 
 What – If analysis | Data Table).
 – Chỉ định ô dữ liệu;
 – Kết thúc.
10/1/2018 Úng dụng Excel trong kinh tế 102
Phân tích độ nhạy một chiều (tt)
10/1/2018 Úng dụng Excel trong kinh tế 103
Phân tích độ nhạy một chiều (tt)
10/1/2018 Úng dụng Excel trong kinh tế 104
Phân tích độ nhạy hai chiều
• Cho phép lần lượt đánh giá tác động đồng thời
 của hai yếu tố đầu vào tới kết quả bài toán.
• Ví dụ: Lập bảng phân tích độ nhạy hai chiều
 nhằm đánh giá mức độ tác động của giá bán, 
 và chi phí NVL chính tới lợi nhuận mặt hàng A 
 của công ty XYZ.
10/1/2018 Úng dụng Excel trong kinh tế 105
Phân tích độ nhạy hai chiều (tt)
10/1/2018 Úng dụng Excel trong kinh tế 106
Phân tích độ nhạy hai chiều(tt)
10/1/2018 Úng dụng Excel trong kinh tế 107
Phân tích tình huống
• Cho phép khảo sát sự ảnh hưởng của một
 nhóm các yếu tố đầu vào đối với kết quả của
 bài toán trong một số trường hợp nhất định.
• Ví dụ lập bảng phân tích tình huống cho công
 ty XYZ theo các tình huống được cho.
10/1/2018 Úng dụng Excel trong kinh tế 108
Phân tích tình huống (tt)
 Mục Tốt BT Xấu
 Sản lượng 40,000 35,000 20,000
 CP NVL chính 4.0 4.5 4.0
 Giá bán 9.0 8.5 8.0
10/1/2018 Úng dụng Excel trong kinh tế 109
Phân tích tình huống (tt)
• Thực hiện trên Excel
 – Mở hộp thoại Scenario Menager (Data | 
 What If | ScenarioManager);
 – Nhập (Add) các tình huống (tên, danh
 sách ô biến, giá trị);
 – Chỉ định ô kết quả (nhấp Summary);
 – Kết thúc lệnh (nhấp OK).
10/1/2018 Úng dụng Excel trong kinh tế 110
Phân tích tình huống (tt)
Hộp thoại
 Scenario
 Manager
 Thêm tình
 Huống
10/1/2018 Úng dụng Excel trong kinh tế 111
Phân tích tình huống (tt)
 Khai báo
 tình huống
10/1/2018 Úng dụng Excel trong kinh tế 112
Phân tích tình huống (tt)
 Giá trị các biến trong tình huống
10/1/2018 Úng dụng Excel trong kinh tế 113
Phân tích tình huống (tt)
 Ô kết quả
10/1/2018 Úng dụng Excel trong kinh tế 114
Phân tích tình huống (tt)
10/1/2018 Úng dụng Excel trong kinh tế 115
Đọc thêm
• Một số kỹ thuật phân tích rủi ro (tt)
 – Phân tích mô phỏng;
 – Phân tích rủi ro với các hàm XS - TK.
• Bài toán lập báo cáo tài chính dự toán
 theo tỷ lệ phần trăm doanh thu
• Bài toán đầu tư tài chính
• Bài toán định giá trị doanh nghiệp
10/1/2018 Úng dụng Excel trong kinh tế 116
 Q&A???
10/1/2018 Úng dụng Excel trong kinh tế 117
 Chương kế tiếp 
ỨNG DỤNG CÔNG CỤ 
 QUẢN LÝ DỰ ÁN