Sử dụng trình giải trong Excel để tìm kết hợp các khoản đầu tư vốn tối đa hóa tổng lợi nhuận.

Hình thành mô hình

Mô hình chúng ta sẽ giải quyết trông như sau trong Excel.

Capital Investment in Excel

  1. Để hình thành mô hình lập trình số nguyên nhị phân (BIP) này, hãy trả lời ba câu hỏi sau.

\ a. Các quyết định được thực hiện là gì? Đối với vấn đề này, chúng ta cần Excel để tìm ra những khoản đầu tư vốn nào cần thực hiện (Có = 1, Không = 0).

\ b. Những ràng buộc đối với những quyết định này là gì? Thứ nhất, số vốn sử dụng của các khoản đầu tư không được vượt quá số vốn hạn chế hiện có (50). Ví dụ, đầu tư Một sử dụng 12 đơn vị vốn. Thứ hai, chỉ có thể đầu tư Một hoặc đầu tư Hai.

Thứ ba, chỉ có thể đầu tư Ba hoặc đầu tư Bốn. Thứ tư, đầu tư Sáu và đầu tư Bảy chỉ có thể được thực hiện nếu đầu tư Năm được thực hiện.

\ c. Thước đo tổng thể về hiệu suất cho những quyết định này là gì? Thước đo tổng thể về hiệu suất là tổng lợi nhuận của các khoản đầu tư vốn được thực hiện, vì vậy mục tiêu là tối đa hóa số lượng này.

  1. Để làm cho mô hình dễ hiểu hơn, hãy đặt tên cho các phạm vi sau.

Range Name

Cells

Profit

C5:I5

YesNo

C13:I13

TotalProfit

M13

  1. Chèn năm hàm SUMPRODUCT sau đây.

Sumproduct Functions

Giải thích: ô K7 (số vốn được sử dụng) bằng sản phẩm của phạm vi C7: I7 và YesNo, ô K8 tương đương với sản phẩm của phạm vi C8: I8 và YesNo, v.v.

Tổng lợi nhuận bằng kết quả của Lợi nhuận và Có Không.

Thử và Lỗi

Với công thức này, việc phân tích bất kỳ dung dịch thử nào trở nên dễ dàng.

  1. Ví dụ, nếu chúng ta thực hiện đầu tư Một và Hai, ràng buộc thứ hai bị vi phạm.

Second Constraint Violated

  1. Ví dụ, nếu chúng ta thực hiện đầu tư Sáu và Bảy, mà không thực hiện đầu tư Năm, ràng buộc thứ tư sẽ bị vi phạm.

Fourth Constraint Violated

  1. Tuy nhiên, đầu tư Một, Năm và Sáu là được. Mọi ràng buộc đều được thỏa mãn.

All Constraints Satisfied

Không nhất thiết phải sử dụng thử và sai. Tiếp theo, chúng tôi sẽ mô tả cách sử dụng Excel Solver để nhanh chóng tìm ra giải pháp tối ưu.

Giải quyết mô hình

Để tìm ra giải pháp tối ưu, hãy thực hiện các bước sau.

  1. Trên tab Dữ liệu, trong nhóm Phân tích, hãy bấm Bộ giải.

Click Solver

Lưu ý: không tìm thấy nút Solver? Nhấp vào đây để tải phần bổ trợ Solver.

Nhập các thông số của bộ giải (đọc tiếp). Kết quả phải phù hợp với hình dưới đây.

Solver Parameters

  1. Nhập TotalProfit cho Mục tiêu.

  2. Nhấp vào Max.

  3. Nhập YesNo cho các ô có thể thay đổi.

  4. Nhấp vào Thêm để nhập ràng buộc sau.

Constraint

  1. Nhấp vào Thêm để nhập ràng buộc sau.

Binary Constraint

Lưu ý: các biến nhị phân là 0 hoặc 1.

  1. Chọn ‘Làm cho các biến không bị ràng buộc thành không phủ định’ và chọn ‘Simplex LP’.

  2. Cuối cùng, nhấp vào Giải quyết.

Kết quả:

Solver Found a Solution

Giải pháp tối ưu:

Capital Investment Result

Kết luận: đầu tư Hai, Bốn, Năm và Bảy là tối ưu.

Giải pháp này cho lợi nhuận tối đa là 146. Tất cả các ràng buộc đều được thỏa mãn.