Menggunakan Solver untuk menentukan campuran optimal produk

Catatan:  Kami ingin secepatnya menyediakan konten bantuan terbaru dalam bahasa Anda. Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda di bagian bawah halaman ini? Berikut artikel dalam bahasa Inggris untuk referensi.

Artikel ini membahas tentang menggunakan Solver, program add-in Microsoft Excel Anda bisa menggunakan untuk analisis bagaimana-jika, untuk menentukan campuran optimal produk.

Bagaimana cara menentukan campuran produk bulanan yang memaksimalkan profitabilitas?

Perusahaan sering perlu untuk menentukan jumlah setiap produk untuk menghasilkan setiap bulan. Dalam bentuk yang sederhana, masalah produk campur melibatkan cara menentukan jumlah setiap produk yang akan dihasilkan selama bulan untuk memaksimalkan laba. Produk campuran biasanya harus mematuhi batasan berikut ini:

  • Produk campuran tidak bisa menggunakan sumber daya lainnya dari tersedia.

  • Ada permintaan yang terbatas untuk setiap produk. Kami tidak bisa menghasilkan lebih banyak produk selama bulan dari perintah permintaan, karena berlebih produksi terbuang (misalnya, tahan lama obat).

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

Gambar buku
Gambar 27-1 produk campuran

Misalnya, kami bekerja untuk perusahaan obat yang menghasilkan enam produk yang berbeda di pabrik mereka. Produksi setiap produk ini memerlukan tenaga dan bahan. Memperlihatkan baris 4 di gambar 27-1 jam kerja yang diperlukan untuk menghasilkan pagar setiap produk, dan baris 5 memperlihatkan pon bahan yang diperlukan untuk menghasilkan pagar setiap produk. Sebagai contoh, menghasilkan pagar produk 1 memerlukan enam jam kerja dan 3.2 pon bahan. Untuk masing-masing obat, harga dari setiap 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 seharga $11,00 per pagar, menimbulkan biaya unit $5,70 per pagar dan berkontribusi $5,30 laba per pagar. Permintaan bulan ini untuk masing-masing obat diberikan dalam baris 8. Misalnya, permintaan untuk produk 3 adalah 1041 kilogram. Bulan ini, 4500 jam kerja dan 1600 pon bahan yang tersedia. Bagaimana perusahaan ini bisa memaksimalkan laba bulanan

Jika kita 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 produk campuran. Lalu kita akan menggunakan coba bervariasi campuran produk untuk mengoptimalkan laba tanpa menggunakan lebih banyak tenaga atau bahan dari tersedia, dan tanpa menghasilkan obat lebih dari permintaan. Kami menggunakan Solver dalam proses ini hanya pada tahap uji coba dan kesalahan. Pada dasarnya, Solver adalah optimisasi mesin yang sempurna melakukan pencarian uji coba dan kesalahan.

Tombol untuk memecahkan masalah campuran produk adalah secara efisien menghitung penggunaan sumber daya dan laba yang terkait dengan campuran produk tertentu apa pun. Alat penting yang bisa kami gunakan untuk melakukan perhitungan ini adalah fungsi SUMPRODUCT. Fungsi SUMPRODUCT mengalikan nilai terkait di dalam rentang sel dan mengembalikan jumlah nilai tersebut. Setiap rentang sel yang digunakan dalam evaluasi SUMPRODUCT harus dimensi yang sama, yang berarti bahwa Anda bisa menggunakan SUMPRODUCT dengan dua baris atau kolom kedua, tapi tidak dengan satu kolom dan satu baris.

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

*(Drug 1 pounds produced) (tenaga digunakan per pagar obat 1) +
(tenaga digunakan per pagar obat 2) * (obat 2 pon dihasilkan) + …
(Tenaga digunakan per pagar obat 6) * (obat 6 pound dihasilkan)

Kita bisa menghitung tenaga penggunaan dalam mode 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 berapa lama akan jika Anda bekerja dengan perusahaan yang dihasilkan, misalnya, 50 produk pabrik mereka. Cara yang jauh lebih mudah untuk menghitung tenaga 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 tenaga kami) namun jauh lebih mudah untuk memasukkan! Perhatikan bahwa saya gunakan tanda $ dengan rentang D2:I2 sehingga ketika saya menyalin rumus saya masih ambil campuran produk dari baris 2. Rumus dalam sel D15 menghitung bahan penggunaan.

Dengan cara yang sama, keuntungan kami ditentukan oleh

(Obat 1 laba per pon) * (diproduksi pound obat 1) +
(2 obat laba per pon) * (obat 2 pon dihasilkan) + …
(6 obat laba per pon) * (obat 6 pound dihasilkan)

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

Sekarang kita bisa mengidentifikasi komponen campuran produk kami Solver model.

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

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

  • Batasan. Kami memiliki batasan berikut ini:

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

    • Menghasilkan selengkapnya obat dari dalam permintaan. Yaitu, nilai dalam sel D2:I2 (pon dihasilkan masing-masing obat) harus kurang dari atau sama dengan permintaan untuk masing-masing obat (tercantum di sel D8:I8).

    • Kami tidak bisa menghasilkan jumlah negatif obat.

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

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

Catatan: Seperti yang dijelaskan di Bab 26, "pengenalan ke optimisasi dengan Excel Solver," Solver diinstal dengan mengklik tombol Microsoft Office, lalu opsi Excel, diikuti dengan Add-in. Di daftar Kelola, klik Add-in Excel, centang kotak Solver Add-in, dan lalu klik OK.

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

Gambar buku
Kotak dialog parameter Solver gambar 27-2

Klik kotak setel sel Target dan lalu pilih sel-sel laba (sel D12). Klik kotak dengan mengubah sel dan lalu arahkan ke rentang D2:I2, yang berisi pon dihasilkan masing-masing obat. Kotak dialog akan terlihat gambar 27-3.

Gambar buku
Kotak dialog parameter Solver gambar 27-3 dengan sel target dan mengubah sel yang ditentukan

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

Gambar buku
Kotak dialog Tambahkan batasan 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.

Gambar buku
Kotak dialog Tambahkan batasan gambar 27-5 dengan batasan penggunaan sumber daya yang dimasukkan

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

Gambar buku
Kotak dialog Tambahkan batasan gambar 27-6 dengan batasan permintaan yang dimasukkan

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

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

  • E2 < = E8 (jumlah dihasilkan obat 2 kurang dari atau sama dengan permintaan obat 2)

  • F2 < = F8 (jumlah yang dihasilkan dari 3 obat dibuat lebih kecil dari atau sama dengan permintaan obat 3)

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

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

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

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

Gambar buku
Gambar 27-7 dalam kotak dialog parameter Solver akhir untuk masalah campuran produk

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 pada halaman berikutnya. Klik OK.

Gambar buku
Pengaturan opsi gambar 27-8 Solver

Mencentang kotak mengasumsikan Non-negatif memastikan bahwa Solver menganggap kombinasi hanya mengubah sel di mana setiap sel yang berubah mengasumsikan nilai non-negatif. Kami memeriksa kotak mengasumsikan Linear Model karena produk campur masalah adalah tipe khusus pemecah 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 pas untuk memenuhi persyaratan"linear model." Ini berarti bahwa setiap batasan dievaluasi dengan menambahkan bersama-sama ketentuan formulir (mengubah cell)*(constant) dan membandingkan jumlah untuk konstanta.

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

(Obat 1 laba per pon) * (diproduksi pound obat 1) +
(2 obat laba per pon) * (obat 2 pon dihasilkan) + …
(6 obat laba per pon) * (obat 6 pound dihasilkan)

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

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

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

Batasan permintaan kami mengambil bentuk

(Obat 1 dihasilkan) < = (obat 1 permintaan)
(obat 2 dihasilkan) < = (obat 2 permintaan)
§
(6 obat dihasilkan) < = (obat 6 permintaan)

Batasan demand masing-masing juga pas untuk memenuhi persyaratan linear model, karena masing-masing dievaluasi dengan menambahkan bersama-sama ketentuan formulir (mengubah cell)*(constant) dan membandingkan jumlah untuk konstanta.

Mengalami menunjukkan bahwa model campuran produk kami linear model, mengapa kita harus peduli?

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

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

Setelah mengklik OK dalam kotak dialog Opsi Solver, kami kembali ke kotak dialog Solver utama, diperlihatkan sebelumnya di gambar 27-7. Saat kami mengklik selesaikan, Solver menghitung solusi yang optimal (jika ada) untuk model campuran produk kami. Sebagaimana saya bab 26, solusi yang optimal ke produk campuran model akan serangkaian mengubah nilai sel (pon dihasilkan masing-masing obat) yang memaksimalkan laba serangkaian semua solusi yang memungkinkan. Sekali lagi, solusi memungkinkan adalah sekumpulan mengubah nilai sel memenuhi 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.

Gambar buku
Gambar 27-9 A memungkinkan solusi produk campur masalah cocok dalam batasan.

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

  • Kami menghasilkan lebih banyak obat 5 dari permintaan ini.

  • Kami menggunakan lebih banyak tenaga dari apa tersedia.

  • Kami menggunakan bahan baku dari apa tersedia.

Gambar buku
Gambar 27-10 solusi tidak mudah untuk masalah campuran produk tidak sesuai dengan dalam batasan yang ditentukan.

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

Gambar buku
Gambar 27-11 optimal solusi untuk masalah campuran produk

Perusahaan obat kami bisa memaksimalkan laba bulanan di tingkat $6,625.20 dengan menghasilkan 596.67 pon obat 4, 1084 pon obat 5 dan tidak ada obat lain! Kami tidak bisa menentukan jika kami bisa mendapatkan keuntungan maksimum $6,625.20 dengan cara lain. Semua kami pasti adalah bahwa dengan sumber daya terbatas dan permintaan, tidak ada cara untuk membuat lebih dari $6,627.20 bulan ini.

Anggap bahwa permintaan untuk setiap produk harus terpenuhi. (Lihat Tidak memungkinkan solusi lembar kerja dalam file Prodmix.xlsx.) Kita lalu harus mengubah kami batasan permintaan dari D2:I2 < = D8:I8 untuk D2:I2 > = D8:I8. Untuk melakukan ini, buka Solver, pilih D2:I2 < = D8:I8 batasan, dan lalu klik Ubah. Dalam kotak dialog Ubah batasan, diperlihatkan dalam gambar 27-12, yang muncul.

Gambar buku
Kotak dialog Ubah batasan gambar 27-12

Pilih > =, dan lalu klik OK. Kami sekarang memastikan bahwa Solver akan mempertimbangkan perubahan hanya nilai sel yang memenuhi semua kebutuhan. Saat Anda mengklik selesaikan, Anda akan melihat pesan "Solver tidak dapat menemukan solusi yang memungkinkan." Pesan ini berarti kami melakukan kesalahan dalam model, tetapi yang dengan sumber daya kami terbatas, kami tidak bisa memenuhi permintaan untuk semua produk. Solver cukup menjelaskan bahwa jika kita ingin memenuhi permintaan untuk setiap produk, kami perlu menambahkan lebih banyak tenaga, bahan lebih banyak atau lebih keduanya.

Mari kita lihat apa yang terjadi jika kita membiarkan permintaan tidak terbatas untuk setiap produk dan kami memperbolehkan negatif kuantitas yang dihasilkan dari masing-masing obat. (Anda bisa melihat masalah Solver ini pada lembar kerja Mengatur nilai tidak berkumpul dalam file Prodmix.xlsx.) Untuk menemukan solusi yang optimal untuk situasi ini, buka Solver, klik tombol opsi, dan Kosongkan kotak mengasumsikan Non-negatif. Dalam kotak dialog parameter Solver, pilih permintaan batasan D2:I2 < = D8:I8 dan lalu klik Hapus untuk menghapus batasan. Saat Anda mengklik selesaikan, Solver mengembalikan pesan "Mengatur nilai sel tidak bertemu." Pesan ini berarti bahwa jika sel target dimaksimalkan (seperti dalam contoh kami), tidak ada solusi yang memungkinkan dengan nilai sel target sewenang besar. (Jika sel target diminimalkan, pesan "Mengatur sel nilai tidak bertemu" berarti ada solusi yang memungkinkan dengan nilai sel target kecil.) Dalam situasi kita, dengan memungkinkan negatif produksi obat, kami berlaku "Buat" sumber daya yang bisa digunakan untuk menghasilkan jumlah sewenang besar obat lain. Diberi permintaan kami tidak terbatas, ini memungkinkan kami untuk membuat keuntungan tidak terbatas. Dalam situasi riil, kami tidak dapat membuat jumlah uang tak terbatas. Singkatnya, jika Anda melihat "Mengatur nilai tidak bertemu", model Anda memiliki kesalahan.

  1. Anggap perusahaan obat kami bisa membeli hingga 500 jam kerja $ 1 lebih per jam dari biaya kerja saat ini. Bagaimana kami bisa memaksimalkan laba?

  2. Pada chip pabrik empat teknisi (A, B, C, dan D) menghasilkan tiga produk (1, 2 dan 3). Bulan ini, produsen chip dapat menjual 80 unit produk 1, 50 unit produk 2, dan paling 50 unit produk 3. Teknisi A bisa membuat hanya produk 1 dan 3. Teknisi B dapat hanya produk 1 dan 2. Teknisi C dapat membuat hanya produk 3. Teknisi D dapat membuat hanya produk 2. Untuk setiap unit yang dihasilkan, produk kontribusi laba berikut ini: produk 1, $6; Produk 2, $7; dan produk 3, $10. Satu (dalam jam) teknisi setiap harus pembuatan produk adalah sebagai berikut:

    Produk

    Teknisi A

    Teknisi B

    Teknisi C

    Teknisi D

    1

    2

    2,5

    Tidak bisa Anda lakukan

    Tidak bisa Anda lakukan

    2

    Tidak bisa Anda lakukan

    3

    Tidak bisa Anda lakukan

    3,5

    3

    3

    Tidak bisa Anda lakukan

    4

    Tidak bisa Anda lakukan

  3. Setiap teknisi bisa bekerja hingga 120 jam per bulan. Bagaimana produsen chip memaksimalkan laba bulanan? Mengasumsikan bilangan pecahan unit bisa dihasilkan.

  4. Komputer pabrik menghasilkan mouse, keyboard, dan joystick permainan video. Laba per unit, penggunaan per unit tenaga, permintaan bulanan, dan penggunaan per unit mesin waktu diberikan dalam tabel berikut ini:

    Mouse

    Keyboard

    Joystick

    Laba unit

    $8

    $11

    $9

    Penggunaan/unit kerja

    UK.2 jam

    .3 jam

    .24 jam

    Unit mesin waktu

    .04 jam

    .055 jam

    .04 jam

    Permintaan bulanan

    15.000

    27.000

    11.000

  5. Setiap bulan, dengan total 13.000 jam kerja dan 3000 jam mesin waktu tersedia. Bagaimana pabrikan memaksimalkan laba dengan bulanan kontribusi dari tanaman?

  6. Mengatasi kami obat contoh dengan asumsi bahwa permintaan minimum 200 unit untuk masing-masing obat harus terpenuhi.

  7. Jason membuat gelang berlian, kalung dan earrings. Ia ingin bekerja maksimal 160 jam per bulan. Dia memiliki 800 ons berlian. Laba, waktu kerja, dan ons berlian yang diperlukan untuk setiap produk diberikan di bawah ini. Jika permintaan untuk setiap produk tidak terbatas, bagaimana dapat Jason memaksimalkan keuntungan?

    Produk

    Unit laba

    Jam kerja per unit

    Ons berlian per unit

    Gelang

    $300

    .35

    1.2

    Kalung

    $200

    .15

    .75

    Earrings

    $100

    .05

    .5

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×