Menggunakan Solver untuk menentukan campuran produk optimal

Bagaimana cara menentukan campuran produk bulanan yang memaksimalkan profitabilitas?

Perusahaan sering perlu untuk menentukan jumlah setiap produk untuk menghasilkan secara bulanan. Dalam bentuk paling Sederhananya, masalah produk campur melibatkan cara menentukan jumlah setiap produk yang akan dihasilkan selama satu bulan untuk memaksimalkan laba. Campuran produk biasanya harus mematuhi batasan berikut ini:

• Campuran produk tidak dapat menggunakan sumber daya lainnya dari tersedia.

• Ada permintaan yang terbatas untuk setiap produk. Kami tidak dapat menghasilkan selengkapnya produk selama satu bulan dari menentukan permintaan, karena berlebih produksi terbuang (misalnya, narkoba tahan lama).

Mari kita sekarang memecahkan contoh berikut masalah campuran produk. Anda bisa menemukan solusi untuk masalah ini di file Prodmix.xlsx, diperlihatkan dalam gambar 27-1.

Katakanlah kami bekerja untuk perusahaan narkoba yang menghasilkan enam produk yang berbeda di pabrik mereka. Produksi setiap produk memerlukan kerja dan bahan. Baris 4 di gambar 27-1 menunjukkan jam kerja yang diperlukan untuk menghasilkan pagar setiap produk, dan baris 5 memperlihatkan pound bahan diperlukan untuk menghasilkan pagar setiap produk. Misalnya, memproduksi pagar produk 1 memerlukan enam jam kerja dan 3.2 pon bahan. Untuk setiap narkoba, harga per pagar diberikan dalam baris 6, biaya unit per pagar diberikan dalam baris 7, dan kontribusi laba per pagar diberikan dalam baris 9. Misalnya, produk 2 menjual $11,00 per pagar, dikonversikan akan dikenakan biaya unit $5,70 per pagar dan berkontribusi $5,30 laba per tanda pagar. Bulan yang permintaan untuk setiap narkoba diberikan dalam baris 8. Misalnya, permintaan untuk produk 3 adalah 1041 pound. Bulan ini, 4500 jam kerja dan 1600 pon bahan tersedia. Bagaimana perusahaan ini memaksimalkan laba bulanan?

Jika kami tahu apa pun tentang Excel Solver, kami akan serangan masalah ini dengan membangun lembar kerja untuk melacak penggunaan laba dan sumber daya yang terkait dengan campuran produk. Lalu kami akan menggunakan uji coba dan kesalahan bervariasi campuran produk mengoptimalkan laba tanpa menggunakan lebih banyak tenaga atau bahan daripada tersedia, dan tanpa memproduksi apa pun narkoba melebihi permintaan. Kami menggunakan Solver dalam proses ini hanya di tahap uji coba dan kesalahan. Pada dasarnya, Solver adalah optimisasi mesin yang sempurna melakukan pencarian uji coba dan kesalahan.

Untuk memecahkan masalah campuran produk adalah secara efisien menghitung penggunaan sumber daya dan keuntungan yang terkait dengan campuran produk tertentu apa pun. Alat yang penting yang dapat kami gunakan untuk melakukan perhitungan ini adalah fungsi SUMPRODUCT. Fungsi SUMPRODUCT mengalikan nilai yang berhubungan dalam rentang sel dan mengembalikan jumlah nilai tersebut. Setiap rentang sel yang digunakan dalam evaluasi SUMPRODUCT harus memiliki dimensi sama, yang menyiratkan yang bisa Anda gunakan SUMPRODUCT dengan dua baris atau kolom dua, tetapi tidak dengan satu kolom dan satu baris.

Sebagai contoh bagaimana kami dapat menggunakan fungsi SUMPRODUCT dalam produk kami campur contoh, mari kita coba untuk menghitung penggunaan sumber daya kami. Penggunaan kerja kami dihitung dengan

(Tenaga digunakan per pagar narkoba 1) *(Drug 1 pounds produced) +
(tenaga digunakan per pagar narkoba 2) * (narkoba 2 pound diproduksi) + …
(Tenaga digunakan per pagar narkoba 6) * (narkoba 6 pound diproduksi)

Kita bisa menghitung kerja penggunaan secara padat dan lebih membosankan sebagai D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Demikian pula, bahan penggunaan bisa dihitung sebagai D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Namun, memasukkan rumus ini dalam lembar kerja untuk enam produk akan memakan waktu. Bayangkan itu akan lama jika Anda bekerja dengan perusahaan yang dihasilkan, misalnya, 50 produk di pabrik mereka. Cara yang jauh lebih mudah untuk menghitung kerja dan bahan penggunaan adalah untuk menyalin dari D14 ke D15 rumus SUMPRODUCT($D$2:$I$2,D4:I4). Rumus ini menghitung D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (yang merupakan penggunaan kerja kami), namun lebih mudah untuk memasukkan! Perhatikan bahwa saya menggunakan tanda $ dengan rentang D2:I2 sehingga ketika saya menyalin rumus saya masih ambil campuran produk dari baris 2. Rumus di sel D15 menghitung bahan penggunaan.

Dalam mode serupa, laba kami ditentukan oleh

(Narkoba 1 laba per pon) * (diproduksi pound narkoba 1) +
(2 narkoba laba per pon) * (narkoba 2 pound diproduksi) + …
(6 narkoba laba per pon) * (narkoba 6 pound diproduksi)

Laba dengan mudah dihitung dalam sel D12 dengan rumus SUMPRODUCT(D9:I9,$D$2:$I$2).

Kami kini dapat mengidentifikasi komponen tiga campuran produk kami Solver model.

• Sel target.Tujuan kami adalah untuk memaksimalkan laba (dihitung dalam sel D12).

• Mengubah sel.Jumlah pound diproduksi setiap produk (tercantum dalam rentang sel D2:I2)

• Batasan. Kami memiliki batasan berikut ini:

  • Jangan gunakan lebih banyak tenaga atau bahan dari yang tersedia. Yaitu, nilai di sel D14:D15 (sumber daya digunakan) harus lebih kecil atau sama dengan nilai di sel F14:F15 (sumber daya yang tersedia).

  • Tidak menghasilkan selengkapnya narkoba dari dalam permintaan. Yaitu, nilai di sel D2:I2 (pon yang dihasilkan dari setiap narkoba) harus lebih kecil atau sama dengan permintaan untuk setiap narkoba (tercantum dalam sel D8:I8).

  • Kami tidak dapat menghasilkan jumlah negatif narkoba apa pun.

Saya akan memperlihatkan Anda cara memasukkan sel target, mengubah sel, dan batasan ke Solver. Lalu perlu Anda lakukan adalah klik tombol Solve untuk menemukan campuran produk memaksimalkan laba!

Untuk memulai, klik Data tab, dan di grup analisis, klik Solver.

Kotak dialog parameter Solver akan muncul, seperti yang diperlihatkan dalam gambar 27-2.

Klik kotak mengatur Target sel dan lalu pilih kami laba sel (sel D12). Klik kotak dengan mengubah sel dan lalu arahkan ke rentang D2:I2, yang berisi pon diproduksi narkoba setiap. Kotak dialog akan terlihat gambar 27-3.

Sekarang kami siap untuk menambahkan batasan ke model. Klik tombol Tambahkan. Anda akan melihat kotak dialog Tambahkan batasan, diperlihatkan dalam gambar 27-4.

Untuk menambahkan batasan penggunaan sumber daya, klik kotak referensi sel, dan lalu pilih rentang D14:D15. Pilih < = dari daftar tengah. Klik dalam kotak batasan, dan lalu pilih rentang sel F14:F15. Kotak dialog Tambahkan batasan akan terlihat seperti gambar 27-5.

Kami telah sekarang memastikan bahwa ketika Solver mencoba nilai yang berbeda untuk mengubah sel, hanya kombinasi yang memenuhi keduanya D14< = F14 (tenaga digunakan kurang dari atau sama dengan kerja tersedia) dan D15< = F15 (bahan digunakan kurang dari atau sama dengan bahan tersedia) akan dianggap. Klik Tambahkan untuk memasukkan batasan permintaan. Isi kotak dialog Tambahkan batasan seperti yang diperlihatkan dalam gambar 27-6.

Menambahkan batasan ini memastikan bahwa saat Solver mencoba kombinasi yang berbeda untuk nilai sel berubah, hanya kombinasi yang memenuhi parameter berikut akan dianggap:

• D2< = D8 (jumlah yang dihasilkan narkoba 1 kurang dari atau sama dengan permintaan narkoba 1)

• E2< = E8 (jumlah diproduksi narkoba 2 kurang dari atau sama dengan permintaan narkoba 2)

• F2< = F8 (jumlah yang dihasilkan dari 3 narkoba dibuat kurang dari atau sama dengan permintaan narkoba 3)

• G2< = G8 (jumlah yang dihasilkan dari 4 narkoba dibuat kurang dari atau sama dengan permintaan narkoba 4)

• H2< = H8 (jumlah yang dihasilkan dari 5 narkoba dibuat kurang dari atau sama dengan permintaan narkoba 5)

• I2< = I8 (jumlah yang dihasilkan narkoba 6 dibuat kurang dari atau sama dengan permintaan narkoba 6)

Klik OK dalam kotak dialog Tambahkan batasan. Jendela Solver akan terlihat seperti gambar 27-7.

Kami masukkan batasan yang mengubah sel harus non-negatif dalam kotak dialog Opsi Solver. Klik tombol opsi dalam kotak dialog parameter Solver. Centang kotak mengasumsikan Linear Model dan kotak mengasumsikan Non-negatif, seperti yang diperlihatkan dalam gambar 27-8 di halaman berikutnya. Klik OK.

Mencentang kotak mengasumsikan Non-negatif memastikan bahwa Solver menganggap hanya kombinasi mengubah sel di mana setiap sel berubah menganggap nilai non-negatif. Kami dicentang kotak mengasumsikan Linear Model karena produk campur masalah adalah tipe khusus Solver masalah disebut linear model. Pada dasarnya, Solver model linear dalam kondisi berikut ini:

• Sel target dihitung dengan menambahkan bersama-sama ketentuan formulir (mengubah cell)*(constant).

• Batasan setiap Just "persyaratan linear model." Ini berarti bahwa batasan setiap dievaluasi dengan menambahkan bersama-sama ketentuan formulir (mengubah cell)*(constant) dan membandingkan penjumlahan ke konstanta.

Mengapa adalah masalah Solver ini linear? Kami sel target (keuntungan) dihitung sebagai

(Narkoba 1 laba per pon) * (diproduksi pound narkoba 1) +
(2 narkoba laba per pon) * (narkoba 2 pound diproduksi) + …
(6 narkoba laba per pon) * (narkoba 6 pound diproduksi)

Perhitungan ini mengikuti sebuah pola di mana nilai sel target berasal dengan menambahkan bersama-sama ketentuan formulir (mengubah cell)*(constant).

Batasan kerja kami dievaluasi dengan membandingkan nilai yang Diperoleh dari (tenaga digunakan per pagar narkoba 1) * (diproduksi pound narkoba 1) + (tenaga digunakan per pagar narkoba 2) *(Drug 2 pounds produced) +... (Kerja kamied per pagar narkoba 6) * (narkoba 6 pound diproduksi) untuk kerja tersedia.

Oleh karena itu, batasan kerja dievaluasi dengan menambahkan bersama-sama ketentuan formulir (mengubah cell)*(constant) dan membandingkan penjumlahan ke konstanta. Batasan kerja dan batasan bahan memenuhi persyaratan linear model.

Batasan permintaan kami mengambil formulir

(Narkoba 1 diproduksi) < = (narkoba 1 permintaan)
< (narkoba 2 diproduksi) = (narkoba 2 permintaan)
§
< (narkoba 6 diproduksi) = (narkoba 6 permintaan)

Setiap permintaan batasan juga memenuhi persyaratan linear model, karena setiap dievaluasi dengan menambahkan bersama-sama ketentuan formulir (mengubah cell)*(constant) dan membandingkan penjumlahan ke konstanta.

Mengalami diperlihatkan model campuran produk kami adalah linear model, mengapa harus kami peduli?

• Jika Solver model linear dan kami memilih mengasumsikan Linear Model, Solver dijamin untuk menemukan solusi yang optimal ke Solver model. Jika Solver model tidak linear, Solver mungkin atau mungkin tidak menemukan solusi yang optimal.

• Jika Solver model linear dan kami memilih mengasumsikan Linear Model, Solver menggunakan algoritma yang sangat efisien (metode simplex) untuk menemukan solusi yang optimal dalam model. Jika Solver model linear dan kami tidak memilih mengasumsikan Linear Model, Solver menggunakan algoritma yang sangat efisien (metode GRG2) dan mungkin memiliki kesulitan dalam menemukan solusi yang optimal dalam model.

Setelah mengklik OK dalam kotak dialog Opsi Solver, kami kembali ke kotak dialog Solver utama, diperlihatkan sebelumnya di gambar 27-7. Saat kami klik selesaikan, Solver menghitung solusi yang optimal (jika ada) untuk model campuran produk kami. Saya menyatakan di Bab 26, solusi yang optimal ke model campuran produk akan sekumpulan mengubah nilai sel (pon yang dihasilkan dari setiap narkoba) yang memaksimalkan laba serangkaian semua solusi yang memungkinkan. Sekali lagi, solusi yang memungkinkan adalah sekumpulan mengubah nilai sel memuaskan semua batasan. Nilai sel berubah yang diperlihatkan dalam gambar 27-9 adalah solusi memungkinkan karena semua tingkat produksi non-negatif, produksi tingkat tidak melebihi permintaan, dan penggunaan sumber daya tidak melebihi sumber daya yang tersedia.

Nilai sel berubah yang diperlihatkan dalam gambar 27-10 di halaman berikutnya mewakili solusi tidak mudah untuk alasan berikut:

• Kami menghasilkan lebih dari 5 narkoba dari permintaan ini.

• Kami menggunakan lebih banyak tenaga dari apa tersedia.

• Kami menggunakan selengkapnya bahan dari apa tersedia.

Setelah mengklik selesaikan, Solver dengan cepat menemukan solusi yang optimal diperlihatkan dalam gambar 27-11. Anda perlu memilih Pertahankan solusi Solver untuk mempertahankan solusi optimal nilai dalam lembar kerja.

Perusahaan narkoba kami dapat memaksimalkan laba bulanan tingkat $6,625.20 oleh memproduksi 596.67 pound narkoba 4, 1084 pound 5 narkoba dan tidak ada narkoba lainnya! Kami tidak dapat menentukan jika kami bisa mendapatkan keuntungan maksimum $6,625.20 dengan cara lain. Kami dapat memastikan hanyalah dengan sumber daya terbatas dan permintaan, ada yang tidak ada cara untuk membuat lebih dari $6,627.20 bulan ini.