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

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

Các công ty thường cần phải xác định số lượng mỗi sản phẩm để tạo ra trên cơ sở hàng tháng. Trong biểu mẫu đơn giản nhất của nó, sản phẩm kết hợp các vấn đề liên quan đến làm thế nào để xác định giá trị của mỗi sản phẩm sẽ được tạo trong một tháng để tối đa hóa lợi nhuận. Sản phẩm hợp thường phải tuân theo để giới hạn sau đây:

• Sản phẩm hợp không thể sử dụng tài nguyên khác hơn sẵn dùng.

• Không có một yêu cầu giới hạn cho từng sản phẩm. Chúng tôi không thể tạo ra nhiều sản phẩm trong một tháng hơn ra yêu cầu, vì sản xuất quá mức là lãng phí (ví dụ, một ma túy dễ hỏng).

Hãy giải quyết ngay ví dụ sau đây về vấn đề hợp sản phẩm. Bạn có thể tìm thấy giải pháp cho vấn đề 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 ma túy cho sáu sản phẩm khác nhau ở cây của họ. Sản xuất của từng sản phẩm này yêu cầu lao động và nguyên liệu. Chỉ cho hàng 4 trong hình 27-1 giờ lao động cần thiết để tạo ra một thăng từng sản phẩm và hàng 5 cân nguyên liệu cần thiết để tạo ra một thăng từng sản phẩm. Ví dụ: sản xuất một thăng sản phẩm 1 yêu cầu sáu giờ lao động và 3.2 £ của nguyên liệu. Đối với mỗi ma túy, giá trên mỗi thăng được cung cấp trong hàng 6, chi phí đơn vị mỗi thăng được cung cấp trong hàng 7 và đóng góp lợi nhuận cho mỗi thăng được cung cấp ở hàng 9. Ví dụ, sản phẩm 2 bán $11,00 cho mỗi thăng, làm phát sinh phí đơn vị của $5,70 cho mỗi thăng và đóng góp $5,30 lợi nhuận cho mỗi thăng. Yêu cầu của tháng cho mỗi ma túy được cung cấp trong hàng 8. Ví dụ, yêu cầu về sản phẩm 3 là 1041 £. Tháng này, 4500 giờ lao động và 1600 £ của nguyên liệu sẵn dùng. Làm thế nào có thể công ty này tối đa hóa lợi nhuận hàng tháng?

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

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

Làm ví dụ về cách chúng tôi có thể sử dụng hàm SUMPRODUCT trong sản phẩm của chúng tôi trộn ví dụ, hãy thử để tính sử dụng tài nguyên của chúng tôi. Cách dùng lao động của chúng tôi được tính toán bằng

(Lao động sử dụng cho mỗi thăng ma túy 1) *(Drug 1 pounds produced) +
(lao động sử dụng cho mỗi thăng ma túy 2) * (ma túy 2 cân sản xuất) +...
(Lao động sử dụng cho mỗi thăng ma túy 6) * (ma túy 6 cân sản xuất)

Chúng tôi có thể tính lao động sử dụng nhiều tedious luôn là D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Tương tự, sử dụng nguyên liệu có thể được tính cho chúng dưới dạng D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Tuy nhiên, nhập các công thức trong một trang tính dành cho sản phẩm sáu là tốn. Giả sử 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ụ, các sản phẩm 50 tại cây của họ. Một cách dễ dàng hơn nhiều để tính lao động và cách dùng nguyên liệu là sao chép từ D14 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 dùng lao động của chúng tôi) nhưng dễ dàng hơn để nhập! 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ợp sản phẩm từ hàng 2. Công thức trong ô D15 tính nguyên liệu sử dụng.

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

(Ma túy lợi nhuận 1 cho mỗi thăng) * (sản xuất bảng ma túy 1) +
(ma túy 2 lợi nhuận cho mỗi thăng) * (ma túy 2 cân sản xuất) +...
(Ma túy 6 lợi nhuận cho mỗi thăng) * (ma túy 6 cân 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 cấu phần của tập hợp sản phẩm của chúng tôi tìm hiểu mô hình.

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

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

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

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

  • Không tạo ra nhiều trong một ma túy hơn trong yêu cầu. Có nghĩa là, các giá trị trong ô D2:I2 (bảng sản xuất của mỗi ma túy) phải nhỏ hơn hoặc bằng yêu cầu cho mỗi ma túy (được liệt kê trong ô D8:I8).

  • Chúng tôi không thể tạo ra một số âm ma túy bất kỳ.

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

Để bắt đầu, bấm vào tab dữ liệu, 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 và sau đó chọn ô lợi nhuận của chúng tôi (ô D12). Bấm vào hộp bằng cách thay đổi ô và sau đó trỏ đến phạm vi D2:I2, chứa £ sản xuất của mỗi ma túy. Hộp thoại bây giờ sẽ hiển thị hình 27-3.

Chúng tôi đã sẵn sàng thêm 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 ô, sau đó chọn phạm vi D14:D15. Chọn < = từ danh sách tên đệm. Bấm vào hộp ràng buộc, sau đó chọn phạm vi ô F14:F15. Hộp thoại thêm ràng buộc bây giờ sẽ trông giống như hình 27-5.

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

Thêm ràng buộc những đảm bảo rằng khi bộ giải tìm cách kết hợp khác nhau cho các giá trị ô thay đổi, chỉ những tổ hợp thỏa mãn các tham số sau đây sẽ được coi là:

• D2< = D8 (giá trị sản xuất của ma túy 1 là nhỏ hơn hoặc bằng nhu cầu về ma túy 1)

• E2< = E8 (lượng sản xuất của ma túy 2 là nhỏ hơn hoặc bằng nhu cầu về ma túy 2)

• F2< = F8 (giá trị sản xuất của ma túy 3 đã thực hiện là nhỏ hơn hoặc bằng nhu cầu về ma túy 3)

• G2< = G8 (giá trị sản xuất ma túy 4 đã thực hiện là nhỏ hơn hoặc bằng nhu cầu về ma túy 4)

• H2< = H8 (giá trị sản xuất của ma túy 5 thực hiện là nhỏ hơn hoặc bằng nhu cầu về ma túy 5)

• I2< = I8 (giá trị sản xuất của ma túy 6 đã thực hiện là nhỏ hơn hoặc bằng nhu cầu về ma túy 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 thay đổi ô phải là không âm trong hộp thoại tùy chọn bộ giải. Bấm nút tùy chọn trong hộp thoại tham số bộ giải. Kiểm tra hộp giả định tuyến tính mô hì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.

Kiểm tra xem như không phải âm hộp đảm bảo bộ giải xem xét chỉ tổ hợp các thay đổi ô trong đó mỗi ô thay đổi giả định một giá trị không âm. Chúng tôi kiểm tra hộp giả định tuyến tính mô vì sản phẩm trộn vấn đề là một kiểu đặc biệt của vấn đề bộ giải được gọi là một mô hình tuyến tính. Cơ bản, mô hình bộ giải được tuyến tính bên dưới các điều kiện sau đây:

• Ô đích được tính cho chúng bằng cách thêm các số hạng của biểu mẫu với nhau (thay đổi cell)*(constant).

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

Tại sao được giải quyết vấn đề này tuyến tính? Chúng tôi ô đích (lợi nhuận) được tính cho chúng dưới dạng

(Ma túy lợi nhuận 1 cho mỗi thăng) * (sản xuất bảng ma túy 1) +
(ma túy 2 lợi nhuận cho mỗi thăng) * (ma túy 2 cân sản xuất) +...
(Ma túy 6 lợi nhuận cho mỗi thăng) * (ma túy 6 cân sản xuất)

Tính toán này theo một mẫu hình trong đó giá trị của ô đích được lấy bằng cách thêm thuật ngữ biểu mẫu với nhau (thay đổi cell)*(constant).

Ràng buộc lao động của chúng tôi được đánh giá bằng cách so sánh giá trị phái sinh từ (lao động sử dụng cho mỗi thăng ma túy 1) * (sản xuất bảng ma túy 1) + (lao động sử dụng cho mỗi thăng ma túy 2) *(Drug 2 pounds produced) +... (Lao động chúng tôied cho mỗi thăng ma túy 6) * (ma túy 6 cân sản xuất) để lao động sẵn dùng.

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

Giới hạn của chúng tôi yêu cầu thực hiện biểu mẫu

(Ma túy 1 sản xuất) < = (ma túy 1 yêu cầu)
(ma túy 2 sản xuất) < = (ma túy 2 yêu cầu)
•
(ma túy 6 sản xuất) < = (ma túy 6 yêu cầu)

Ràng buộc yêu cầu mỗi cũng đáp ứng yêu cầu tuyến tính mô hình, bởi vì mỗi được đánh giá bằng cách thêm các số hạng của biểu mẫu với nhau (thay đổi cell)*(constant) và so sánh tính tổng cho một hằng số.

Có hiển thị 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 nên chúng tôi xử lý?

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

• Nếu một mô hình bộ giải được tuyến tính và chúng tôi chọn giả định tuyến tính mô hình, bộ giải sử dụng một thuật toán rất có hiệu quả (phương pháp simplex) để tìm giải pháp tối ưu vào mô hình. Nếu một mô hình bộ giải được tuyến tính và chúng tôi không chọn giả định tuyến tính mô hình, bộ giải sử dụng một thuật toán không hiệu quả rất (phương pháp GRG2) và có thể khó tìm giải pháp tối ưu vào mô hình.

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

Các giá trị ô thay đổi hiển thị trong hình 27-10 trên trang tiếp theo đại diện cho giải infeasible giải pháp vì các lý do sau đây:

• Chúng tôi đã tạo ra nhiều ma túy 5 hơn yêu 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, nhanh chóng bộ giải tìm giải pháp tối ưu Hiển thị trong hình 27-11. Bạn cần phải chọn giữ giải pháp bộ giải để giữ nguyên các giải pháp tối ưu việc theo giá trị trong trang tính.

Công ty ma túy của chúng tôi có thể tối đa hóa lợi nhuận hàng tháng ở cấp độ $6,625.20 bằng sản xuất 596.67 £ của ma túy 4, 1084 £ của ma túy 5 và không có thuốc khác! Chúng tôi không thể xác định nếu chúng tôi có thể đạt được tối đa lợi nhuận của $6,625.20 theo nhiều cách khác. Tất cả chúng tôi có thể chắc chắn về là rằng với các giới hạn tài nguyên và yêu cầu, không có cách nào để thực hiện nhiều hơn $6,627.20 tháng này.