Menggunakan Solver untuk menentukan campuran produk optimal

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 produk yang 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.

Gambar buku

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.

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

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.

Gambar buku

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

Gambar buku

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

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.

Gambar buku

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.

Gambar buku

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.

Gambar buku

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.

Gambar buku

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.

Gambar buku

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.

Gambar buku

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.

Anggap bahwa permintaan untuk setiap produk harus terpenuhi. (Lihat Solusi memungkinkan tanpa lembar kerja dalam file Prodmix.xlsx.) Kami lalu harus mengubah batasan permintaan kami 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. Kotak dialog Ubah batasan, diperlihatkan dalam gambar 27-12, muncul.

Gambar buku

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

Mari kita lihat apa yang terjadi jika kami mengizinkan permintaan tak terbatas untuk setiap produk dan kami mengizinkan negatif kuantitas yang dihasilkan dari narkoba setiap. (Anda bisa melihat masalah Solver ini pada lembar kerja Mengatur nilai tidak berkumpul di file Prodmix.xlsx.) Untuk menemukan solusi yang optimal situasi ini, buka Solver, klik tombol opsi, lalu 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 "Nilai sel kumpulan tidak berkumpul." Pesan ini berarti bahwa jika sel target untuk dimaksimalkan (seperti dalam contoh kami), solusi yang memungkinkan dengan nilai sel target sewenang besar. (Jika sel target untuk diminimalkan, pesan "Mengatur sel nilai tidak berkumpul" berarti ada memungkinkan solusi dengan nilai sel target kecil.) Dalam situasi kami, oleh memungkinkan negatif produksi narkoba, kami berlaku "Buat" sumber daya yang bisa digunakan untuk menghasilkan jumlah sewenang besar narkoba lain. Diberikan permintaan tak terbatas kami, ini memungkinkan kami untuk membuat keuntungan tak terbatas. Dalam situasi riil, kami tidak dapat membuat tak terbatas sejumlah uang. Singkatnya, jika Anda melihat "Mengatur nilai tidak berkumpul", model Anda memiliki kesalahan.

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

  2. Pada chip pabrik, empat teknisi (A, B, C, dan D) menghasilkan tiga produk (produk 1, 2, dan 3). Bulan ini, produsen chip dapat menjual 80 unit produk 1, 50 unit produk 2, dan maksimal 50 unit produk 3. Teknisi A dapat membuat hanya produk 1 dan 3. Teknisi B dapat membuat hanya produk 1 dan 2. Teknisi C dapat membuat hanya produk 3. Teknisi D dapat membuat hanya produk 2. Untuk setiap unit diproduksi, produk berkontribusi laba berikut ini: 1 produk, $6; Produk 2, $7; dan produk 3, $10. Satu (dalam jam) teknisi setiap perlu 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. Teknisi setiap bisa bekerja hingga 120 jam per bulan. Bagaimana produsen chip memaksimalkan laba bulanan? Anggap bilangan pecahan unit dapat diproduksi.

  4. Komputer pabrik menghasilkan tikus, keyboard, dan permainan video joystick. Laba per unit, per unit kerja penggunaan, permintaan bulanan, dan per unit mesin-waktu penggunaan diberikan di tabel berikut ini:

    Tikus

    Keyboard

    Joystick

    Laba satuan

    $8

    $11

    $9

    Penggunaan/satuan 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, total 13.000 jam kerja dan 3000 jam mesin waktu tersedia. Bagaimana pabrikan memaksimalkan laba kontribusi webnya bulanan dari tanaman?

  6. Mengatasi kami narkoba contoh dengan asumsi bahwa permintaan minimum 200 unit untuk setiap narkoba harus terpenuhi.

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

    Produk

    Unit laba

    Jam kerja per unit

    Ons berlian per unit

    Gelang

    $300

    .35

    1.2

    Kalung

    $200

    .15

    .75

    Anting-anting

    $100

    .05

    .5

Perlu bantuan lainnya?

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.

×