Sử dụng Bộ giải để xác định kết hợp sản phẩm tối ưu

Làm thế nào để tôi có thể xác định hỗn hợp sản phẩm hàng tháng giúp tối đa hóa lợi nhuận?

Các công ty thường cần xác định số lượng của mỗi sản phẩm để sản xuất trên cơ sở hàng tháng. Ở dạng đơn giản nhất, vấn đề kết hợp sản phẩm bao gồm cách xác định số lượng sản phẩm nên được sản xuất trong một tháng để tối đa hóa lợi nhuận. Hỗn hợp sản phẩm thường phải tuân theo các ràng buộc sau:

• Hỗn hợp sản phẩm không thể sử dụng nhiều tài nguyên hơn mức sẵn có.

• Mỗi sản phẩm đều có nhu cầu hạn chế. Chúng tôi không thể sản xuất nhiều hơn của một sản phẩm trong một tháng hơn nhu cầu đọc chính tả, bởi vì sản xuất dư thừa là lãng phí (ví dụ, một loại thuốc dễ hư mất).

Bây giờ hãy giải quyết ví dụ sau về sự cố trộn sản phẩm. Bạn có thể tìm thấy giải pháp cho sự cố này trong tệp, Prodmix.xlsx hiển thị trong Hình 27-1.

Giả sử chúng tôi làm việc cho một công ty thuốc sản xuất sáu sản phẩm khác nhau tại nhà máy của họ. Sản xuất của mỗi sản phẩm đòi hỏi lao động và nguyên liệu. Hàng 4 trong hình 27-1 cho thấy giờ lao động cần thiết để sản xuất một pound của mỗi sản phẩm, và hàng 5 cho thấy bảng Anh nguyên liệu cần thiết để sản xuất một pound của mỗi sản phẩm. Ví dụ, sản xuất một pound sản phẩm 1 đòi hỏi sáu giờ lao động và 3,2 pound nguyên liệu. Đối với mỗi loại thuốc, giá trên mỗi pound được đưa ra trong hàng 6, chi phí đơn vị trên mỗi pound được đưa ra trong hàng 7, và đóng góp lợi nhuận trên mỗi pound được đưa ra trong hàng 9. Ví dụ, Product 2 bán với giá $11,00 mỗi pound, chịu chi phí đơn vị là $5,70 mỗi pound và đóng góp lợi nhuận $5,30 cho mỗi pound. Nhu cầu của tháng cho mỗi loại thuốc được đưa ra trong hàng 8. Ví dụ, nhu cầu đối với sản phẩm 3 là 1041 bảng Anh. Tháng này, 4500 giờ lao động và 1600 pounds nguyên liệu có sẵn. Công ty này có thể tối đa hóa lợi nhuận hàng tháng như thế nào?

Nếu chúng tôi không biết gì về Excel Solver, chúng tôi sẽ tấn công vấn đề này bằng cách xây dựng một bảng tính để theo dõi lợi nhuận và sử dụng tài nguyên liên quan đến kết hợp sản phẩm. Sau đó, chúng tôi sẽ sử dụng thử nghiệm và lỗi để thay đổi hỗn hợp sản phẩm để tối ưu hóa lợi nhuận mà không sử dụng nhiều lao động hoặc nguyên liệu hơn là có sẵn, và không sản xuất bất kỳ loại thuốc vượt quá nhu cầu. Chúng tôi sử dụng Bộ giải trong quy trình này chỉ ở giai đoạn dùng thử và lỗi. Về cơ bản, Bộ giải là một công cụ tối ưu hóa thực hiện tìm kiếm phép thử và lỗi một cách hoàn hảo.

Chìa khóa để giải quyết vấn đề hỗn hợp sản phẩm là tính toán hiệu quả việc sử dụng tài nguyên và lợi nhuận liên quan đến bất kỳ kết hợp sản phẩm nhất định. Một công cụ quan trọng mà chúng ta có thể sử dụng để thực hiện tính toán này là chức SUMPRODUCT trọng. Hàm SUMPRODUCT nhân các giá trị tương ứng trong các phạm vi ô và trả về tổng của các giá trị đó. Mỗi phạm vi ô được sử dụng trong đánh giá SUMPRODUCT phải có cùng kích thước, điều này ngụ ý rằng bạn có thể sử dụng SUMPRODUCT với hai hàng hoặc hai cột nhưng không sử dụng với một cột và một hàng.

Ví dụ về cách chúng ta có thể sử dụng hàm SUMPRODUCT trong ví dụ kết hợp sản phẩm của mình, chúng ta hãy thử tính mức sử dụng tài nguyên của mình. Mức sử dụng lao động của chúng ta được tính bằng cách

(Lao động sử dụng cho mỗi pound của thuốc 1)*(Thuốc 1 bảng Anh sản xuất)+
(Lao động sử dụng cho mỗi pound của thuốc 2)*(Thuốc 2 pounds sản xuất) + ...
(Lao động sử dụng cho mỗi pound của thuốc 6)*(Thuốc 6 pounds sản xuất)

Chúng tôi có thể tính toán việc sử dụng lao động theo cách tẻ nhạt hơn là D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Tương tự, việc sử dụng nguyên liệu có thể được tính là D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Tuy nhiên, việc nhập các công thức này vào trang tính cho sáu sản phẩm sẽ tốn nhiều thời gian. Hãy tưởng tượng sẽ mất bao lâu nếu bạn đang làm việc với một công ty sản xuất, ví dụ: 50 sản phẩm tại nhà máy của họ. Một cách dễ dàng hơn nhiều để tính toán lao động và sử dụng nguyên liệu là sao chép từ D14 đến D15 công thức SUMPRODUCT($D$2:$I$2,D4:I4). Công thức này tính D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (là cách sử dụng lao động của chúng tôi) nhưng dễ nhập hơn nhiều! Lưu ý rằng tôi sử dụng dấu $ với phạm vi D2:I2 để khi tôi sao chép công thức, tôi vẫn chụp hỗn hợp sản phẩm từ hàng 2. Công thức trong ô D15 tính mức sử dụng nguyên liệu.

Tương tự, lợi nhuận của chúng ta được xác định bởi

(Lợi nhuận trên mỗi pound 1 ma túy)*(Sản xuất 1 pound thuốc) +
(Thuốc 2 lợi nhuận mỗi pound)*(Thuốc 2 pound sản xuất) + ...
(Ma túy 6 lợi nhuận trên mỗi pound)*(Thuốc 6 pounds sản xuất)

Lợi nhuận dễ dàng được tính toán trong ô D12 với công thức SUMPRODUCT(D9:I9,$D$2:$I$2).

Bây giờ chúng tôi có thể xác định ba thành phần của mô hình bộ giải hỗn hợp sản phẩm của chúng tôi.

• Ô đích. Mục tiêu của chúng tôi là tối đa hóa lợi nhuận (được tính toán trong ô D12).

• Thay đổi ô. Số pound sản xuất của mỗi sản phẩm (được liệt kê trong phạm vi ô D2:I2)

• Ràng buộc. Chúng tôi có các ràng buộc sau đây:

  • Không sử dụng nhiều lao động hoặc nguyên liệu hơn là có sẵn. Nghĩa là, các giá trị trong các ô D14:D15 (các tài nguyên được sử dụng) phải nhỏ hơn hoặc bằng các giá trị trong các ô F14:F15 (các tài nguyên sẵn có).

  • Không sản xuất nhiều thuốc hơn nhu cầu. Đó là, các giá trị trong các tế bào D2: I2 (pound sản xuất của mỗi loại thuốc) phải ít hơn hoặc bằng nhu cầu cho mỗi loại thuốc (được liệt kê trong các tế bào D8:I8).

  • Chúng tôi không thể sản xuất một số tiền âm của bất kỳ loại thuốc.

Tôi sẽ chỉ cho bạn cách nhập ô đích, thay đổi ô và ràng buộc vào Bộ giải. Sau đó, tất cả những gì bạn cần làm là nhấp vào nút Solve để tìm một kết hợp sản phẩm tối đa hóa lợi nhuận!

Để bắt đầu, hãy bấm tab Dữ liệu và trong nhóm Phân tích, bấm Bộ giải.

Hộp thoại Tham số Bộ giải sẽ xuất hiện, như minh họa trong Hình 27-2.

Bấm vào hộp Đặt Ô Đích rồi chọn ô lợi nhuận của chúng tôi (ô D12). Nhấp vào hộp Bằng cách thay đổi các tế bào và sau đó trỏ đến phạm vi D2:I2, trong đó có chứa bảng Anh sản xuất của mỗi loại thuốc. Hộp thoại lúc này sẽ có dạng Hình 27-3.

Chúng tôi hiện đã sẵn sàng thêm các ràng buộc vào mô hình. Bấm vào nút Thêm. Bạn sẽ thấy hộp thoại Thêm Ràng buộc, hiển thị trong Hình 27-4.

Để thêm ràng buộc sử dụng tài nguyên, hãy bấm vào hộp Tham chiếu Ô, rồi chọn phạm vi D14:D15. Chọn <= từ danh sách giữa. Bấm vào hộp Ràng buộc, rồi chọn phạm vi ô F14:F15. Hộp thoại Thêm Ràng buộc giờ sẽ trông giống như Hình 27-5.

Giờ đây, chúng tôi đã đảm bảo rằng khi Bộ giải thử các giá trị khác nhau cho các ô thay đổi, chỉ có các kết hợp đáp ứng cả D14<=F14 (lao động sử dụng ít hơn hoặc bằng lao động sẵn có) và D15<=F15 (nguyên liệu được sử dụng ít hơn hoặc bằng nguyên liệu sẵn có) sẽ được xem xét. Bấm Thêm để nhập ràng buộc nhu cầu. Điền vào hộp thoại Thêm Ràng buộc như minh họa trong Hình 27-6.

Việc thêm những ràng buộc này đảm bảo rằng khi Trình giải quyết thử các kết hợp khác nhau cho việc thay đổi giá trị ô, chỉ những tổ hợp đáp ứng các tham số sau đây sẽ được xem xét:

• D2<=D8 (lượng thuốc được tạo ra 1 nhỏ hơn hoặc bằng nhu cầu về Thuốc 1)

• E2<=E8 (lượng thuốc được sản xuất 2 nhỏ hơn hoặc bằng nhu cầu về Thuốc 2)

• F2<=F8 (lượng thuốc được tạo ra từ Thuốc 3 được tạo ra nhỏ hơn hoặc bằng nhu cầu về Thuốc 3)

• G2<=G8 (số lượng sản xuất của thuốc 4 thực hiện là ít hơn hoặc bằng nhu cầu cho thuốc 4)

• H2<=H8 (lượng thuốc được tạo ra bởi Thuốc 5 được làm nhỏ hơn hoặc bằng nhu cầu đối với Thuốc 5)

• I2<=I8 (số lượng sản xuất của Thuốc 6 thực hiện là ít hơn hoặc bằng nhu cầu cho Thuốc 6)

Bấm OK trong hộp thoại Thêm Ràng buộc. Cửa sổ Bộ giải sẽ trông giống như Hình 27-7.

Chúng tôi nhập ràng buộc rằng việc thay đổi ô phải không âm trong hộp thoại Tùy chọn Bộ giải. Bấm vào nút Tùy chọn trong hộp thoại Tham số Bộ giải. Chọn hộp Giả định Mô hình Tuyến tính và hộp Giả định Không Âm, như minh họa trong Hình 27-8 trên trang tiếp theo. Bấm OK.

Chọn hộp Giả định Không Âm đảm bảo rằng Trình giải quyết chỉ xem xét kết hợp các ô thay đổi trong đó mỗi ô thay đổi sẽ giả định một giá trị không âm. Chúng tôi đã kiểm tra hộp Giả định mô hình tuyến tính vì vấn đề kết hợp sản phẩm là một loại vấn đề đặc biệt của bộ giải được gọi là một mô hình tuyến tính. Về cơ bản, mô hình Bộ giải là tuyến tính theo các điều kiện sau đây:

• Ô đích được tính bằng cách cộng các thuật ngữ của biểu mẫu (thay đổi ô )*(hằng số).

• Mỗi ràng buộc thỏa mãn "yêu cầu mô hình tuyến tính". Điều này có nghĩa là mỗi ràng buộc được đánh giá bằng cách cộng các thuật ngữ của biểu mẫu (thay đổi ô )*(hằng số) với nhau và so sánh tổng với một hằng số.

Tại sao trình giải quyết vấn đề tuyến tính này? Ô mục tiêu (lợi nhuận) của chúng tôi được tính là

(Lợi nhuận trên mỗi pound 1 ma túy)*(Sản xuất 1 pound thuốc) +
(Thuốc 2 lợi nhuận mỗi pound)*(Thuốc 2 pound sản xuất) + ...
(Ma túy 6 lợi nhuận trên mỗi pound)*(Thuốc 6 pounds sản xuất)

Phép tính này tuân theo một mẫu hình trong đó giá trị của ô đích được lấy bằng cách cộng các thuật ngữ của biểu mẫu với nhau (ô thay đổi)*(hằng số).

Ràng buộc lao động của chúng tôi được đánh giá bằng cách so sánh giá trị xuất phát từ (Lao động sử dụng trên mỗi pound của thuốc 1)*(Thuốc 1 pound sản xuất) + (Lao động sử dụng trên mỗi pound của thuốc 2)*(Thuốc 2 pounds sản xuất)+ ... (Lao động chúng tôied cho mỗi pound của thuốc 6)*(Thuốc 6 pounds sản xuất) để lao động có sẵn.

Do đó, ràng buộc lao động được đánh giá bằng cách cộng các điều khoản của biểu mẫu cùng nhau (ô thay đổi )*(hằng số) và so sánh tổng với một hằng số. Cả ràng buộc lao động và ràng buộc nguyên liệu thỏa mãn yêu cầu mô hình tuyến tính.

Ràng buộc nhu cầu của chúng tôi có hình thức

(Drug 1 produced)<=(Drug 1 Demand)
(Drug 2 produced)<=(Drug 2 Demand)
§
(Drug 6 produced)<=(Drug 6 Demand)

Mỗi ràng buộc nhu cầu cũng thỏa mãn yêu cầu mô hình tuyến tính, bởi vì mỗi yêu cầu được đánh giá bằng cách cộng các điều khoản của biểu mẫu (ô thay đổi )*( hằng số) với nhau và so sánh tổng với một hằng số.

Đã chỉ ra rằng mô hình kết hợp sản phẩm của chúng tôi là một mô hình tuyến tính, tại sao chúng ta nên quan tâm?

• Nếu một mô hình Bộ giải là tuyến tính và chúng tôi chọn Giả sử Mô hình Tuyến tính, Bộ giải được đảm bảo để tìm giải pháp tối ưu cho mô hình Bộ giải. Nếu một mô hình Bộ giải không tuyến tính, Bộ giải có thể hoặc có thể không tìm thấy giải pháp tối ưu.

• Nếu một mô hình Bộ giải là tuyến tính và chúng tôi chọn Giả sử Mô hình Tuyến tính, Bộ giải sử dụng một thuật toán rất hiệu quả (phương pháp simplex) để tìm giải pháp tối ưu của mô hình. Nếu mô hình Bộ giải là tuyến tính và chúng tôi không chọn Giả sử Mô hình Tuyến tính, Bộ giải sử dụng thuật toán rất không hiệu quả (phương pháp GRG2) và có thể gặp khó khăn khi tìm giải pháp tối ưu của mô hình.

Sau khi bấm OK trong hộp thoại Tùy chọn Bộ giải, chúng ta trở về hộp thoại Bộ giải chính, hiển thị trước đó trong Hình 27-7. Khi chúng ta bấm giải quyết, Bộ giải tính toán một giải pháp tối ưu (nếu một trong những tồn tại) cho mô hình kết hợp sản phẩm của chúng tôi. Như tôi đã nói trong Chương 26, một giải pháp tối ưu để các mô hình kết hợp sản phẩm sẽ là một tập hợp các giá trị tế bào thay đổi (bảng Anh sản xuất của mỗi loại thuốc) mà tối đa hóa lợi nhuận trên tất cả các giải pháp khả thi. Một lần nữa, một giải pháp khả thi là một tập hợp các giá trị ô thay đổi đáp ứng tất cả các ràng buộc. Các giá trị ô thay đổi được hiển thị trong Hình 27-9 là một giải pháp khả thi vì tất cả các mức sản xuất đều không âm, mức sản xuất không vượt quá nhu cầu và mức sử dụng tài nguyên không vượt quá tài nguyên sẵn có.

Các giá trị ô thay đổi được hiển thị trong Hình 27-10 trên trang tiếp theo thể hiện một giải pháp không thể thực hiện vì những lý do sau đây:

• Chúng tôi sản xuất nhiều thuốc 5 hơn nhu cầu cho nó.

• Chúng tôi sử dụng nhiều lao động hơn những gì có sẵn.

• Chúng tôi sử dụng nhiều nguyên liệu hơn những gì có sẵn.

Sau khi bấm giải quyết, Bộ giải nhanh chóng tìm thấy giải pháp tối ưu được hiển thị trong Hình 27-11. Bạn cần chọn Giữ Giải pháp Bộ giải để bảo toàn các giá trị giải pháp tối ưu trong trang tính.

Công ty thuốc của chúng tôi có thể tối đa hóa lợi nhuận hàng tháng của nó ở mức $ 6,625,20 bằng cách sản xuất 596.67 pound của thuốc 4, 1084 pounds thuốc 5, và không có loại thuốc khác! Chúng tôi không thể xác định liệu mình có thể đạt được lợi nhuận tối đa là $6.625,20 hay không theo các cách khác. Tất cả chúng ta có thể chắc chắn là với nguồn lực và nhu cầu hạn chế của chúng tôi, không có cách nào để làm cho nhiều hơn $ 6,627,20 trong tháng này.