Optimasi dengan Excel Solver

Solver adalah program tambahan Microsoft Excel yang dapat Anda gunakan untuk pengoptimalan dalam analisis bagaimana-jika.

Menurut O'Brien dan Marakas, optimization analysisadalah perluasan yang lebih kompleks dari analisis pencarian tujuan. Alih-alih menetapkan nilai target tertentu untuk variabel, sasarannya adalah menemukan nilai optimal untuk satu atau lebih variabel target, dalam batasan tertentu. Kemudian, satu atau lebih variabel lain diubah berulang kali, tunduk pada batasan yang ditentukan, hingga Anda menemukan nilai terbaik untuk variabel target.

Di Excel, Anda bisa menggunakan Solver untuk menemukan optimal value (maksimum atau minimum, atau nilai tertentu) untuk rumus dalam satu sel yang disebut sel tujuan, tunduk pada batasan atau batasan tertentu, pada nilai sel rumus lain di lembar kerja.

Ini berarti Solver bekerja dengan sekelompok sel yang disebut variabel keputusan yang digunakan dalam menghitung rumus dalam sel tujuan dan batasan. Solver menyesuaikan nilai dalam sel variabel keputusan untuk memenuhi batas pada sel kendala dan menghasilkan hasil yang Anda inginkan untuk sel tujuan.

Anda dapat menggunakan Solver untuk menemukan solusi optimal untuk berbagai masalah seperti -

Menentukan bauran produk bulanan untuk unit pembuatan obat yang memaksimalkan profitabilitas.
Menjadwalkan tenaga kerja dalam sebuah organisasi.
Memecahkan masalah transportasi.
Perencanaan dan penganggaran keuangan.

Mengaktifkan Add-in Solver

Sebelum Anda melanjutkan mencari solusi untuk masalah dengan Solver, pastikan file Solver Add-in diaktifkan di Excel sebagai berikut -

Klik tab DATA di Pita. ItuSolver Perintah akan muncul di grup Analisis seperti yang ditunjukkan di bawah ini.

Jika Anda tidak menemukan perintah Solver, aktifkan sebagai berikut -

Klik tab FILE.
Klik Opsi di panel kiri. Kotak dialog Opsi Excel muncul.
Klik Add-In di panel kiri.
Pilih Add-in Excel di kotak Kelola dan klik Buka.

Kotak dialog Add-in muncul. MemeriksaSolver Add-indan klik Ok. Sekarang, Anda seharusnya dapat menemukan perintah Solver pada Ribbon di bawah tab DATA.

Metode Pemecahan yang digunakan oleh Solver

Anda dapat memilih salah satu dari tiga metode penyelesaian berikut yang didukung oleh Excel Solver, berdasarkan tipe masalahnya -

LP Simplex

Digunakan untuk masalah linier. SEBUAHSolver model linier dalam kondisi berikut -

Sel target dihitung dengan menjumlahkan istilah-istilah dari bentuk (sel yang berubah) * (konstan).
Setiap kendala memenuhi persyaratan model linier. Ini berarti bahwa setiap batasan dievaluasi dengan menjumlahkan suku-suku bentuk (sel yang berubah) * (konstanta) dan membandingkan jumlah tersebut dengan sebuah konstanta.

Generalized Reduced Gradient (GRG) Nonlinear

Digunakan untuk masalah nonlinier yang mulus. Jika sel target Anda, salah satu batasan Anda, atau keduanya berisi referensi untuk mengubah sel yang bukan bentuk (sel yang berubah) * (konstan), Anda memiliki model nonlinier.

Evolusioner

Memahami Evaluasi Solver

Solver membutuhkan parameter berikut -

Sel Variabel Keputusan
Sel Pembatas
Sel Tujuan
Metode Pemecahan

Evaluasi pemecah didasarkan pada:

Nilai dalam sel variabel keputusan dibatasi oleh nilai dalam sel batasan.
Perhitungan nilai dalam sel tujuan menyertakan nilai dalam sel variabel keputusan.
Solver menggunakan Metode Pemecahan yang dipilih untuk menghasilkan nilai optimal di sel tujuan.

Mendefinisikan Masalah

Misalkan Anda menganalisis keuntungan yang dihasilkan oleh perusahaan yang memproduksi dan menjual produk tertentu. Anda diminta untuk menemukan jumlah yang dapat dibelanjakan untuk iklan dalam dua kuartal berikutnya dengan batas maksimum 20.000. Tingkat iklan di setiap kuartal memengaruhi hal-hal berikut -

Banyaknya unit yang terjual, secara tidak langsung menentukan besarnya pendapatan penjualan.
Biaya terkait, dan
Keuntungannya.

Anda dapat melanjutkan untuk mendefinisikan masalah sebagai -

Temukan Biaya Satuan.
Temukan biaya iklan per Unit.
Temukan Harga Satuan.

Selanjutnya, atur sel untuk kalkulasi yang diperlukan seperti yang diberikan di bawah ini.

Seperti yang bisa Anda amati, perhitungan dilakukan untuk Triwulan1 dan Triwulan2 yang menjadi pertimbangan adalah -

Jumlah unit yang tersedia untuk dijual di Triwulan1 adalah 400 dan di Triwulan2 adalah 600 (sel - C7 dan D7).
Nilai awal untuk anggaran periklanan ditetapkan sebagai 10.000 per Kuartal (Sel - C8 dan D8).
Jumlah unit yang terjual tergantung pada biaya iklan per unit dan karenanya merupakan anggaran untuk kuartal / Adv. Biaya per unit. Perhatikan bahwa kami telah menggunakan fungsi Min untuk berhati-hati agar tidak ada. dari unit yang terjual di <= no. dari unit yang tersedia. (Sel - C9 dan D9).
Pendapatan dihitung sebagai Harga Satuan * Jumlah Unit yang terjual (Sel - C10 dan D10).
Biaya dihitung sebagai Biaya Unit * Jumlah Unit Tersedia + Uang Muka. Biaya untuk kuartal itu (Sel - C11 dan D12).
Laba adalah Pendapatan - Beban (Sel C12 dan D12).
Total Laba adalah Laba di Kuartal1 + Laba di Kuartal2 (Sel - D3).

Selanjutnya, Anda dapat mengatur parameter untuk Solver seperti yang diberikan di bawah ini -

Seperti yang dapat Anda amati, parameter untuk Solver adalah -

Sel tujuan adalah D3 yang berisi Laba Total, yang ingin Anda maksimalkan.
Sel Variabel Keputusan adalah C8 dan D8 yang berisi anggaran untuk dua kuartal - Quarter1 dan Quarter2.
Ada tiga sel Kendala - C14, C15 dan C16.
- Sel C14 yang berisi anggaran total adalah mengatur batasan 20000 (sel D14).
- Sel C15 yang berisi no. dari unit yang terjual di Triwulan1 adalah dengan menetapkan batasan <= tidak. dari unit yang tersedia di Quarter1 (sel D15).
- Sel C16 yang berisi no. dari unit yang terjual di Triwulan2 adalah dengan menetapkan batasan <= tidak. dari unit yang tersedia di Quarter2 (sel D16).

Memecahkan Masalah

Langkah selanjutnya adalah menggunakan Solver untuk menemukan solusi sebagai berikut -

Step 1- Buka DATA> Analisis> Pemecah di Pita. Kotak dialog Parameter Solver muncul.

Step 2 - Di kotak Set Objective, pilih sel D3.

Step 3 - Pilih Max.

Step 4 - Pilih rentang C8: D8 di By Changing Variable Cells kotak.

Step 5 - Selanjutnya, klik tombol Tambah untuk menambahkan tiga kendala yang telah Anda identifikasi.

Step 6- Kotak dialog Add Constraint muncul. Tetapkan batasan untuk total anggaran seperti yang diberikan di bawah ini dan klik Tambahkan.

Step 7- Tetapkan batasan untuk total no. dari unit yang terjual di Triwulan1 seperti yang diberikan di bawah ini dan klik Tambahkan.

Step 8- Tetapkan batasan untuk total no. dari unit yang terjual di Quarter2 seperti yang diberikan di bawah ini dan klik OK.

Kotak dialog Parameter Solver muncul dengan tiga batasan ditambahkan dalam kotak –Subjek ke Kendala.

Step 9 - Di Select a Solving Method kotak, pilih LP Simplex.

Step 10- Klik tombol Pecahkan. Kotak dialog Hasil Solver muncul. PilihKeep Solver Solution dan klik OK.

Hasilnya akan muncul di lembar kerja Anda.

Seperti yang dapat Anda amati, solusi optimal yang menghasilkan keuntungan total maksimum, tergantung pada batasan yang diberikan, adalah sebagai berikut -

Total Keuntungan - 30000.
Adv. Anggaran untuk Quarter1 - 8000.
Adv. Anggaran untuk Quarter2 - 12000.

Melangkah melalui Solver Trial Solutions

Anda dapat melangkah melalui solusi uji coba Solver, dengan melihat hasil iterasi.

Step 1 - Klik tombol Opsi di kotak dialog Parameter Solver.

Itu Options kotak dialog muncul.

Step 2 - Pilih kotak Show Iteration Results dan klik OK.

Step 3 - Itu Solver Parameterskotak dialog muncul. KlikSolve.

Step 4 - Itu Show Trial Solution kotak dialog muncul, menampilkan pesan - Solver paused, current solution values displayed on worksheet.

Seperti yang Anda amati, nilai iterasi saat ini ditampilkan di sel kerja Anda. Anda dapat menghentikan Pemecah menerima hasil saat ini atau melanjutkan Pemecah dari mencari solusi di langkah selanjutnya.

Step 5 - Klik Lanjutkan.

Itu Show Trial Solutionkotak dialog muncul di setiap langkah dan akhirnya setelah solusi optimal ditemukan, kotak dialog Hasil Solver muncul. Lembar kerja Anda diperbarui di setiap langkah, akhirnya menampilkan nilai hasil.

Menyimpan Pilihan Solver

Anda memiliki opsi penyimpanan berikut untuk masalah yang Anda selesaikan dengan Solver -

Anda bisa menyimpan pilihan terakhir dalam kotak dialog Parameter Solver dengan lembar kerja dengan menyimpan buku kerja.
Setiap lembar kerja dalam buku kerja bisa memiliki pilihan Solver sendiri, dan semuanya akan disimpan saat Anda menyimpan buku kerja.
Anda juga dapat menentukan lebih dari satu masalah dalam lembar kerja, masing-masing dengan pilihan Pemecahnya sendiri. Dalam kasus seperti itu, Anda dapat memuat dan menyimpan masalah satu per satu dengan Muat / Simpan di kotak dialog Parameter Solver.
- Klik Load/Savetombol. Kotak dialog Muat / Simpan muncul.
- Untuk menyimpan model masalah, masukkan referensi untuk sel pertama dari rentang vertikal sel kosong tempat Anda ingin meletakkan model masalah. Klik Simpan.

Model masalah (kumpulan Parameter Solver) muncul mulai dari sel yang telah Anda berikan sebagai referensi.

Untuk memuat model masalah, masukkan referensi untuk seluruh rentang sel yang berisi model masalah. Kemudian, klik tombol Load.