Excel Solver: Optimalkan Keputusan Kompleks dengan Banyak Variabel

Analisis what-if memungkinkan Anda menguji berbagai skenario untuk melihat bagaimana skenario tersebut memengaruhi hasil. Ini memberi Anda ruang aman untuk bereksperimen dengan variabel seperti biaya atau kapasitas guna menemukan solusi yang paling efektif. Pendekatan ini penting bagi siapa pun yang mengelola sumber daya, merencanakan jadwal, atau membuat keputusan bisnis strategis.

Uji-coba manual memakan waktu berjam-jam dan sering kali melewatkan jawaban optimal. Solver menjamin optimalitas matematis dalam hitungan detik. Itulah perbedaan antara menebak alokasi sumber daya dan mengetahui bahwa Anda membuat keputusan terbaik.

Tutorial ini membimbing Anda menggunakan Excel Solver, fitur lanjutan yang dibuat untuk masalah optimisasi ini. Saya akan menunjukkan cara menyiapkan spreadsheet, menjalankan analisis, dan menafsirkan temuan dengan contoh dunia nyata.

Memahami Excel Solver: Konsep Dasar

Sebelum saya menunjukkan cara menggunakan Solver, izinkan saya menjelaskan tiga komponen setiap masalah optimisasi. Ini bukan sekadar jargon, inilah yang membuat Solver bekerja.

Sel objektif, variabel keputusan, dan kendala

Anggap sel objektif sebagai sasaran Anda. Ini adalah rumus yang ingin Anda maksimalkan atau minimalkan, seperti total laba atau total biaya. Ini harus berupa rumus, bukan sekadar angka, karena Solver perlu melihat bagaimana hasil berubah saat Anda menyesuaikan berbagai hal.

Variabel keputusan adalah hal yang ingin Anda cari. Berapa banyak kursi yang harus kita buat? Berapa banyak yang harus kita belanjakan di tiap saluran pemasaran? Saya selalu memulai ini dari nol agar mudah melihat apa yang berubah setelah Solver dijalankan.

Kendala adalah batasan dan aturan Anda. Anda tidak bisa membelanjakan lebih dari anggaran. Anda tidak bisa memproduksi dalam jumlah negatif. Beberapa proyek membutuhkan tingkat investasi minimum. Kendala ini membuat masalah matematika Anda menjadi realistis dan mencerminkan keterbatasan bisnis yang sebenarnya.

Begini cara kerjanya: Solver menyesuaikan variabel keputusan untuk mengoptimalkan objektif Anda sambil memastikan semua kendala tetap terpenuhi. Jika Anda mempelajari riset operasi, ini akan terdengar familier. Jika tidak, anggap saja ini sebagai uji-coba cerdas dengan jaminan.

Perbandingan dengan Goal Seek

Ini sering ditanyakan. Goal Seek lebih sederhana. Fitur ini mengubah satu sel untuk mencapai target tertentu. Gunakan saat Anda tahu jawaban yang diinginkan dan hanya perlu mencari input yang mengantarkan ke sana.

Solver berbeda. Fitur ini mencari jawaban terbaik saat Anda memiliki banyak keputusan dan kendala yang harus dipatuhi. ISolver is used if Anda perlu memaksimalkan atau meminimalkan sesuatu sambil mengatur beberapa variabel sekaligus.

Patokan saya: jika Anda bisa menyebut angka persis yang diinginkan, gunakan Goal Seek. Jika Anda bertanya "apa cara terbaik untuk melakukan ini?" gunakan Solver.

Memuat dan Mengaktifkan Add-in Solver

Solver sudah ada di Excel, tetapi Anda perlu menyalakannya terlebih dahulu. Tenang, ini kurang dari satu menit.

Langkah instalasi untuk Windows dan Mac

Untuk Windows:

1. File > Options > Add-ins
2. Pilih Excel Add-ins dari menu Manage, klik Go
3. Centang Solver Add-in, klik OK
4. Masuk ke tab Data dan temukan Solver di grup Analyze

Untuk Mac:

1. Tools > Excel Add-ins
2. Centang Solver Add-in, klik OK

Jika Anda menggunakan Excel Online, kabar buruknya: fitur ini tidak mendukung Solver. Anda membutuhkan versi desktop (2016 atau lebih baru).

Konfigurasi

Setelah aktif, akses Solver melalui tab Data > tombol Solver. Pengaturan default sudah memadai untuk sebagian besar masalah, jadi jangan terlalu dipikirkan.

Namun, dua pengaturan penting jika Anda mengalami masalah. Precision mengatur seberapa ketat Solver dalam memenuhi kendala. Saya memperketat ini untuk model keuangan yang memperhitungkan sen. Convergence memberi tahu Solver kapan harus berhenti mencoba memperbaiki solusi. Saya menurunkannya jika Solver berhenti terlalu cepat dengan jawaban yang belum sepenuhnya optimal.

Anda dapat menemukan keduanya di dialog Solver Parameters > tombol Options, tetapi jujur saja, saya jarang menyentuhnya.

Menyiapkan Masalah Anda: Formulasi Model di Excel

Spreadsheet yang rapi memudahkan konfigurasi Solver.

Mari saya tuntun melalui contoh: produsen furnitur yang membuat kursi dan meja. Kursi menghasilkan laba $60 (butuh 4 jam kerja, 2 unit kayu). Meja menghasilkan laba $75 (butuh 6 jam kerja, 2 unit kayu). Kita memiliki 240 jam kerja dan 100 unit kayu yang tersedia. Berapa banyak masing-masing yang harus dibuat?

Definisi masalah dan penyiapan spreadsheet

Saya menata setiap model Solver dengan cara yang sama. Selalu empat bagian:

1. Data input (info produk, batas sumber daya)
2. Variabel keputusan (jumlah yang diproduksi)
3. Fungsi objektif (total laba)
4. Perhitungan kendala (sumber daya terpakai vs. tersedia)

Model Solver lengkap dengan keempat bagian. Gambar oleh Penulis.

Tata letak ini membuat semuanya transparan. Siapa pun yang melihat model Anda dapat langsung melihat apa yang Anda optimalkan dan batasan apa yang Anda hadapi.

Sekarang, saya selalu membuat yang disebut named ranges untuk sel penting. Alih-alih menulis =B8*B3+B9*B4, saya bisa menulis =SUMPRODUCT(QuantityToProduce,ProfitPerUnit). Jauh lebih mudah dipahami enam bulan kemudian ketika Anda sudah lupa arti referensi sel itu.

Named ranges membuat rumus mudah dibaca. Gambar oleh Penulis.

Untuk membuatnya: pilih sel, klik Name Box, ketik nama, tekan Enter. Hanya butuh dua detik, menyelamatkan Anda dari pusing di kemudian hari.

Variabel keputusan dan fungsi objektif

Saya memulai variabel keputusan dari nol. Ini membantu perbandingan. Sebagian orang suka memulai dengan nilai estimasi, itu juga tidak masalah, tetapi nol cocok untuk saya.

Fungsi objektif harus berupa rumus. Untuk contoh furnitur kita, saya gunakan:

=SUMPRODUCT(B8:B9,B3:B4)

Ini menghitung: (Kursi × $60) + (Meja × $75)

Perkalian dan penjumlahan sederhana. Itu saja yang Anda butuhkan untuk sebagian besar masalah bisnis.

Kendala

Kendala adalah tempat Anda menerjemahkan batasan bisnis nyata ke dalam matematika. Pembuat furnitur kita memiliki dua kendala sumber daya:

• Tenaga kerja: =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Kayu: =SUMPRODUCT(B8:B9,D3:D4) <= 100

Dan ini pelajaran yang saya dapat dengan susah payah: selalu tambahkan kendala non-negativitas (B8:B9 >= 0). Tanpanya, Solver mungkin menyarankan membuat kursi negatif, yang jelas tidak masuk akal di dunia nyata.

Saya juga menambahkan kolom status dengan rumus =IF() seperti  =IF(B16<=C16,"OK","EXCEEDED"). Ini memberi umpan balik visual instan bahkan sebelum saya menjalankan Solver. Hijau artinya lanjut, merah artinya ada yang salah dengan penyiapan Anda.

Langkah demi Langkah: Cara Menggunakan Solver di Excel

Model sudah dibuat. Sekarang saya tunjukkan cara menjalankan Solver. Bagian ini hanya perlu sekitar 30 detik begitu Anda paham alurnya.

Menggunakan dialog Solver Parameters

Buka Solver dengan mengeklik tab Data > tombol Solver.

Dialog Solver sebelum konfigurasi. Gambar oleh Penulis.

Berikut yang perlu Anda isi:

Set Objective: Klik bidang ini, lalu klik sel B12 (sel Total Profit kita).

To: Pilih Max karena kita ingin memaksimalkan laba.

By Changing Variable Cells: Pilih B8:B9 (jumlah yang kita putuskan).

Subject to the Constraints: Klik Add untuk tiap kendala:

• Tenaga kerja: $B$16 <= $C$16

• Kayu: $B$17 <= $C$17

• Non-negativitas: $B$8:$B$9 >= 0

Select a Solving Method: Pilih Simplex LP. Saya akan jelaskan metode lain nanti, tetapi untuk masalah linear seperti ini, Simplex LP yang Anda butuhkan.

Solver dikonfigurasi dan siap menyelesaikan. Gambar oleh Penulis.

Menyelesaikan model

Sebelum mengeklik Solve, lihat sekilas nilai saat ini. Semuanya harus nol.

Keadaan awal dengan nol di semua tempat. Gambar oleh Penulis.

Sekarang klik Solve. Untuk masalah sederhana seperti milik kita, ini memakan waktu beberapa detik.

Berhasil! Solver menemukan solusi optimal. Gambar oleh Penulis.

Saat dialog Hasil Solver muncul, seharusnya tertulis "Solver found a solution." Centang kotak Answer dan Sensitivity di bawah Reports. Ini memberi Anda analisis detail yang akan kita lihat nanti. Lalu klik OK.

Solusi optimal: 30 kursi dan 20 meja. Gambar oleh Penulis.

Selesai. Solusinya menunjukkan kita harus membuat 30 kursi dan 20 meja untuk laba $3.300. Perhatikan kedua kendala menunjukkan "OK", artinya kita menggunakan sumber daya secara efisien tanpa melebihi batas.

Ringkasan alur kerja

Ini proses yang saya ikuti setiap saat:

1. Bangun model (input, variabel, objektif, kendala)
2. Konfigurasi Solver (tentukan apa yang dioptimalkan dan apa yang disesuaikan)
3. Klik Solve dan tunggu
4. Periksa apakah solusi masuk akal secara bisnis
5. Tinjau laporan untuk wawasan lebih dalam

Langkah terakhir itu penting. Hanya karena Solver menemukan jawaban tidak berarti itu jawaban yang tepat untuk bisnis Anda. Selalu lakukan pengecekan kewajaran hasil.

Metode Solver, Parameter, dan Opsi Lanjutan

Solver menyediakan tiga algoritme berbeda. Memilih yang tepat sangat memengaruhi kecepatan dan akurasi.

Gambaran metode penyelesaian

Tiga metode penyelesaian untuk tipe masalah berbeda. Gambar oleh Penulis.

Simplex LP

Simplex LP yang saya gunakan 80% waktu. Ini untuk masalah linear di mana semuanya sekadar penjumlahan dan perkalian. Campuran produk? Alokasi anggaran? Perencanaan sumber daya? Simplex LP. Cepat dan menjamin Anda mendapatkan jawaban terbaik.

GRG Nonlinear

GRG Nonlinear digunakan saat ada kurva, kuadrat, atau eksponensial dalam rumus Anda. Pikirkan model penetapan harga di mana permintaan turun saat harga naik, tetapi tidak dalam garis lurus. Atau optimisasi portofolio dengan perhitungan varians. Ini menemukan optimum lokal, yang mungkin bukan yang benar-benar terbaik, tetapi biasanya cukup baik.

Evolutionary

Evolutionary adalah opsi brute force. Gunakan saat Anda memiliki kendala bilangan bulat (hanya bisa membuat unit utuh), keputusan biner (ya atau tidak), atau pernyataan =IF() dalam rumus. Ini lebih lambat karena pada dasarnya mencoba banyak solusi acak dan mempertahankan yang bagus. Namun tetap bekerja saat metode lain gagal.

Aturan keputusan saya sederhana: mulai dengan Simplex LP. Jika rumus Anda memiliki eksponen atau hasil kali antar variabel, beralih ke GRG Nonlinear. Jika Anda membutuhkan bilangan bulat atau variabel biner, gunakan Evolutionary.

Opsi dan pengaturan Solver

Klik Options di dialog Solver untuk pengaturan lanjutan. Saya jarang mengubahnya, tetapi ini yang penting:

Batasi lama waktu Solver berjalan dengan Max Time. Default-nya 100 detik, biasanya cukup untuk sebagian besar masalah. Saya meningkatkannya untuk model yang sangat besar yang butuh waktu lebih lama.

Batasi berapa banyak percobaan yang dilakukan Solver dengan Iterations. Jika mencapai batas ini tanpa solusi, naikkan nilainya.

Kendalikan seberapa ketat Solver terhadap kendala dengan Precision. Saya memperketatnya untuk model keuangan di mana setiap sen berarti, selebihnya biarkan saja.

Pembuatan Laporan dan Analisis Sensitivitas

Ingat saat saya meminta Anda mencentang kotak laporan itu? Inilah yang Anda dapatkan.

Answer Report mendokumentasikan solusi. Gambar oleh Penulis.

Answer Report adalah jejak audit Anda. Ini menunjukkan apa yang berubah: variabel keputusan dari 0 menjadi 30 kursi dan 20 meja, laba dari $0 menjadi $3.300. Laporan ini juga memberi tahu kendala mana yang mengikat (terpakai penuh) vs. tidak mengikat (masih ada kelonggaran). Dalam kasus kita, baik tenaga kerja maupun kayu mengikat, artinya kita menggunakan seluruh kapasitas yang tersedia.

Seberapa jauh input bisa berubah sebelum solusinya berubah. Gambar oleh Penulis.

Sensitivity Report adalah bagian yang menarik. Kolom Allowable Increase/Decrease menunjukkan seberapa jauh input Anda bisa berubah sebelum Anda membutuhkan solusi berbeda. Rentang lebar berarti jawaban Anda tangguh. Rentang sempit berarti perubahan kecil bisa membalikkan semuanya.

Shadow price menunjukkan nilai sumber daya. Gambar oleh Penulis.

Namun emas sesungguhnya adalah shadow price. Ini memberi tahu berapa nilai satu unit tambahan sumber daya. Jika tenaga kerja memiliki shadow price $7,5, mendapatkan satu jam kerja tambahan menambah $7,5 pada laba. Jadi jika Anda bisa menyewa pekerja lepas $5/jam, lakukan. Jika $10/jam, lewatkan.

Pentingnya analisis sensitivitas

Saya menggunakan laporan ini untuk menjawab tiga pertanyaan. Sumber daya mana yang harus saya investasikan? Apakah solusi saya rapuh? Bagaimana jika perkiraan saya keliru? Laporan ini memberi jawaban jelas untuk ketiganya.

Pertama, saya melihat shadow price untuk memutuskan sumber daya mana yang harus saya investasikan. Shadow price tinggi berarti nilai tinggi.

Berikutnya, saya periksa rentang yang diizinkan untuk melihat apakah solusi saya rapuh. Jika laba hanya bisa berubah $1 sebelum solusi berubah, Anda berada di posisi rawan. Jika bisa berubah $50, Anda aman.

Terakhir, saya bertanya bagaimana jika perkiraan saya keliru? Rentang yang diizinkan memberi tahu seberapa jauh Anda bisa meleset sebelum perlu menghitung ulang.

Saran saya: selalu periksa shadow price sebelum menyetujui anggaran. Jika suatu sumber daya menunjukkan shadow price nol, artinya Anda memiliki kapasitas berlebih. Menambah investasi di sana adalah pemborosan. Tempatkan dana Anda pada sumber daya dengan shadow price tertinggi.

Pemecahan Masalah dan Resolusi Error

Pesan error Solver terdengar menakutkan tetapi biasanya mudah diperbaiki. Berikut yang paling sering saya temui.

Error dan pesan umum

Solver could not find a feasible solution

Artinya kendala Anda saling bertentangan.

 Tidak ada solusi yang memenuhi semua kendala. Gambar oleh Penulis.

Ini biasanya terjadi saat Anda meminta sesuatu yang mustahil, seperti mencoba mencapai target laba yang tidak mungkin didukung oleh sumber daya. Solusinya adalah menghapus kendala satu per satu hingga Solver berfungsi; proses ini mengidentifikasi kendala mana yang menyebabkan konflik.

Set Cell values do not converge

Artinya objektif Anda dapat tumbuh tanpa batas. Anda kehilangan batas atas di suatu tempat. Bisnis nyata selalu memiliki batas, tambahkan batas tersebut.

Solver cannot improve the current solution

Ini biasanya terjadi pada masalah non-linear. Coba nilai awal berbeda atau beralih ke metode Evolutionary. Terkadang optimum lokal sudah cukup baik.

Kesalahan formulasi model

Circular references

Ini terjadi saat Sel A bergantung pada Sel B, yang bergantung pada Sel A. Excel akan memperingatkan Anda. Gunakan tab Formulas > Trace Precedents untuk menemukan dan memutus lingkarannya.

Kendala bilangan bulat

Menambahkan kendala bilangan bulat untuk angka bulat. Gambar oleh Penulis.

Batasan Solver dan Alternatif untuk Masalah Skala Besar

Excel Solver sangat berguna, tetapi memiliki batas yang perlu Anda ketahui.

Memahami batasan Solver

Solver standar dibatasi hingga 200 variabel keputusan dan 100 kendala. Kedengarannya banyak sampai Anda mencoba menjadwalkan 200 pekerja di 50 slot waktu. Itu 10.000 variabel, jauh melampaui kemampuan Solver.

Saya menggunakan Solver untuk rencana produksi bulanan, anggaran triwulanan, dan pemilihan proyek. Ini sempurna untuk keputusan taktis. Namun tidak dibuat untuk masalah tingkat perusahaan seperti jaringan rantai pasok dengan ribuan lokasi.

Alternatif untuk masalah besar

OpenSolver

OpenSolver gratis dan menghapus batas tersebut. Ini menangani sebanyak variabel yang memungkinkan oleh memori komputer Anda, menggunakan antarmuka yang sama dengan Excel Solver, dan Anda bisa mengunduhnya dari OpenSolver.org. Saya merekomendasikannya saat Anda melampaui batas Solver standar.

Frontline Solver Premium

Frontline Solver berbayar ($300-$1.500/tahun) tetapi mendukung ribuan variabel dengan algoritme yang lebih baik. Ini yang akan saya gunakan jika saya melakukannya secara profesional penuh waktu.

Perangkat lunak mandiri

Alat seperti CPLEX atau Gurobi menangani jutaan variabel tetapi memerlukan pengetahuan pemrograman dan investasi nyata. Itu ranah tingkat perusahaan.

Menurut saya, bagi sebagian besar analis bisnis, Excel Solver mencakup 95% kebutuhan Anda. Jika Anda mencapai batasnya, itu pertanda baik—pekerjaan Anda cukup canggih untuk membenarkan alat yang lebih baik.

Kesimpulan

Kita telah membahas banyak hal, dari penyiapan dasar hingga menafsirkan shadow price. 

Sekarang, alih-alih berdebat opini di ruang rapat, Anda bisa menampilkan angka di layar. "Inilah rencana yang optimal secara matematis." Itu mengubah percakapan. Anda berhenti menebak dan mulai membuktikan.

Silakan coba pada masalah berantakan berikutnya—konflik penjadwalan atau tekanan anggaran yang Anda hindari. Bisa jadi jawaban sempurna selama ini tersembunyi di spreadsheet Anda.

Dan jika Anda ingin mempelajari lebih banyak teknik untuk mengesankan atasan, ikuti kursus Data Analysis in Excel kami untuk terus membangun kemampuan.