Bagaimana perusahaan dapat menggunakan Solver untuk menentukan proyek mana yang harus dilakukannya?
Setiap tahun, perusahaan seperti Eli Lilly perlu menentukan obat mana yang akan dikembangkan; perusahaan seperti Microsoft, program perangkat lunak mana yang akan dikembangkan; perusahaan seperti Proctor & Gamble, yang produk konsumen baru untuk dikembangkan. Fitur Solver di Excel dapat membantu perusahaan membuat keputusan ini.
Bagaimana perusahaan dapat menggunakan Solver untuk menentukan proyek mana yang harus dilakukannya?
Sebagian besar perusahaan ingin melakukan proyek yang menyumbang nilai bersih saat ini (NPV) terbesar, tunduk pada sumber daya yang terbatas (biasanya modal dan tenaga kerja). Katakanlah perusahaan pengembang perangkat lunak mencoba menentukan mana dari 20 proyek perangkat lunak yang harus dilakukannya. NPV (dalam jutaan dolar) yang disumbangkan oleh setiap proyek serta modal (dalam jutaan dolar) dan jumlah pemrogram yang dibutuhkan selama masing-masing dari tiga tahun ke depan diberikan pada lembar kerja Model Dasar di Capbudget.xlsx file, yang ditunjukkan pada Gambar 30-1 di halaman berikutnya. Misalnya, Proyek 2 menghasilkan $908 juta. Ini membutuhkan $151 juta selama Tahun 1, $269 juta selama Tahun 2, dan $248 juta selama Tahun 3. Proyek 2 membutuhkan 139 programmer selama Tahun 1, 86 programmer selama Tahun 2, dan 83 programmer selama Tahun 3. Sel E4:G4 memperlihatkan modal (dalam jutaan dolar) yang tersedia selama masing-masing dari tiga tahun, dan sel H4:J4 menunjukkan berapa banyak programmer yang tersedia. Misalnya, selama Tahun 1 modal hingga $2,5 miliar dan 900 programmer tersedia.
Perusahaan harus memutuskan apakah harus melakukan setiap proyek. Mari kita asumsikan bahwa kita tidak dapat melakukan sebagian kecil dari proyek perangkat lunak; Jika kita mengalokasikan 0,5 dari sumber daya yang dibutuhkan, misalnya, kita akan memiliki program yang tidak bekerja yang akan memberi kita pendapatan $0!
Trik dalam pemodelan situasi di mana Anda melakukan atau tidak melakukan sesuatu adalah dengan menggunakan sel pengubah biner. Sel biner yang berubah selalu sama dengan 0 atau 1. Ketika sel biner yang berubah yang sesuai dengan proyek sama dengan 1, kita melakukan proyek. Jika sel perubahan biner yang sesuai dengan proyek sama dengan 0, kita tidak melakukan proyek. Anda mengatur Solver untuk menggunakan rentang sel pengubah biner dengan menambahkan batasan—pilih sel perubahan yang ingin Anda gunakan, lalu pilih Bin dari daftar dalam kotak dialog Tambahkan Batasan.
Dengan latar belakang ini, kami siap memecahkan masalah pemilihan proyek perangkat lunak. Seperti biasa dengan model Solver, kami mulai dengan mengidentifikasi sel target kami, sel yang berubah, dan kendala.
- Sel target. Kami memaksimalkan NPV yang dihasilkan oleh proyek terpilih.
- Mengubah sel. Kami mencari sel pengubah biner 0 atau 1 untuk setiap proyek. Saya telah menemukan sel ini dalam rentang A6:A25 (dan menamai rentang doit). Misalnya, 1 di sel A6 menunjukkan bahwa kami melakukan Proyek 1; 0 di sel C6 menunjukkan bahwa kami tidak melakukan Proyek 1.
- Kendala. Kita perlu memastikan bahwa untuk setiap Tahun t (t = 1, 2, 3), Tahun t modal yang digunakan kurang dari atau sama dengan Tahun t modal yang tersedia, dan Tahun t tenaga kerja yang digunakan kurang dari atau sama dengan Tahun t tenaga kerja yang tersedia.
Seperti yang Anda lihat, lembar kerja kami harus menghitung untuk setiap pilihan proyek NPV, modal yang digunakan setiap tahun, dan pemrogram yang digunakan setiap tahun. Di sel B2, saya menggunakan rumus SUMPRODUCT(doit,NPV) untuk menghitung total NPV yang dihasilkan oleh proyek yang dipilih. (Nama rentang NPV mengacu pada rentang C6:C25.) Untuk setiap proyek dengan 1 di kolom A, rumus ini mengambil NPV proyek, dan untuk setiap proyek dengan 0 di kolom A, rumus ini tidak mengambil NPV proyek. Oleh karena itu, kami dapat menghitung NPV dari semua proyek, dan sel target kami linier karena dihitung dengan menjumlahkan istilah yang mengikuti bentuk (mengubah sel)*(konstanta). Dengan cara yang sama, saya menghitung modal yang digunakan setiap tahun dan tenaga kerja yang digunakan setiap tahun dengan menyalin dari E2 ke F2:J2 rumus SUMPRODUCT(doit,E6:E25).
Sekarang saya mengisi kotak dialog Parameter Solver seperti yang ditunjukkan pada Gambar 30-2.
Tujuan kami adalah untuk memaksimalkan NPV proyek yang dipilih (sel B2). Sel yang berubah (rentang bernama doit) adalah sel pengubah biner untuk setiap proyek. Kendala E2:J2<=E4:J4 memastikan bahwa setiap tahun modal dan tenaga kerja yang digunakan kurang dari atau sama dengan modal dan tenaga kerja yang tersedia. Untuk menambahkan batasan yang membuat sel yang berubah menjadi biner, saya mengklik Tambahkan dalam kotak dialog Parameter Penyelesai, lalu pilih Bin dari daftar di tengah kotak dialog. Kotak dialog Tambahkan Batasan akan muncul seperti yang ditunjukkan pada Gambar 30-3.
Model kami bersifat linear karena sel target dihitung sebagai jumlah istilah yang memiliki bentuk (sel berubah)*(konstanta) dan karena batasan penggunaan sumber daya dihitung dengan membandingkan jumlah ( mengubah sel)*(konstanta) dengan konstanta.
Setelah kotak dialog Parameter Solver terisi, klik Solve dan kami memiliki hasil yang ditunjukkan sebelumnya pada Gambar 30-1. Perusahaan dapat memperoleh NPV maksimum $9,293 juta ($9.293 miliar) dengan memilih Proyek 2, 3, 6–10, 14–16, 19, dan 20.
Menangani kendala lainnya
Terkadang model pemilihan proyek memiliki kendala lain. Misalnya, jika kita memilih Project 3, kita juga harus memilih Project 4. Karena solusi optimal kami saat ini memilih Proyek 3 tetapi bukan Proyek 4, kami tahu bahwa solusi kami saat ini tidak bisa tetap optimal. Untuk mengatasi masalah ini, cukup tambahkan batasan bahwa sel pengubah biner untuk Proyek 3 lebih kecil atau sama dengan sel pengubah biner untuk Proyek 4.
Anda dapat menemukan contoh ini pada lembar kerja Jika 3 lalu 4 di Capbudget.xlsx file, yang ditunjukkan pada Gambar 30-4. Sel L9 mengacu pada nilai biner yang terkait dengan Proyek 3, dan sel L12 ke nilai biner yang terkait dengan Proyek 4. Dengan menambahkan batasan L9<=L12, jika kita memilih Project 3, L9 sama dengan 1 dan batasan kita memaksa L12 (biner Project 4) sama dengan 1. Batasan kita juga harus membiarkan nilai biner dalam sel yang berubah dari Project 4 tidak dibatasi jika kita tidak memilih Project 3. Jika kita tidak memilih Project 3, L9 sama dengan 0 dan batasan kita memungkinkan biner Project 4 sama dengan 0 atau 1, itulah yang kita inginkan. Solusi optimal baru ditunjukkan pada Gambar 30-4.
Solusi optimal baru dihitung jika memilih Proyek 3 berarti kita juga harus memilih Proyek 4. Sekarang misalkan bahwa kita hanya dapat melakukan empat proyek dari antara Proyek 1 sampai 10. (Lihat lembar kerja Paling Lama 4 Dari P1–P10 , yang ditunjukkan pada Gambar 30-5.) Di sel L8, kami menghitung jumlah nilai biner yang terkait dengan Proyek 1 hingga 10 dengan rumus SUM(A6:A15). Kemudian kami menambahkan batasan L8<=L10, yang memastikan bahwa, paling banyak, 4 dari 10 proyek pertama dipilih. Solusi optimal baru ditunjukkan pada Gambar 30-5. NPV telah turun menjadi $ 9,014 miliar.
Memecahkan Masalah Pemrograman Biner dan Bilangan Bulat
Model Linear Solver di mana beberapa atau semua sel yang berubah diperlukan biner atau bilangan bulat biasanya lebih sulit dipecahkan daripada model linier di mana semua sel yang berubah dibiarkan menjadi pecahan. Untuk alasan ini, kita sering puas dengan solusi yang hampir optimal untuk masalah pemrograman biner atau bilangan bulat. Jika model Solver Anda berjalan dalam waktu yang lama, Anda mungkin ingin mempertimbangkan untuk menyesuaikan pengaturan Toleransi dalam kotak dialog Opsi Penyelesai. (Lihat Gambar 30-6.) Misalnya, pengaturan Toleransi 0,5% berarti bahwa Solver akan berhenti saat pertama kali menemukan solusi yang layak yang berada dalam 0,5 persen dari nilai sel target optimal teoretis (nilai sel target optimal teoretis adalah nilai target optimal yang ditemukan ketika batasan biner dan bilangan bulat dihilangkan). Seringkali kita dihadapkan pada pilihan antara menemukan jawaban dalam 10 persen optimal dalam 10 menit atau menemukan solusi optimal dalam dua minggu waktu komputer! Nilai Toleransi default adalah 0,05%, yang berarti bahwa Solver berhenti ketika menemukan nilai sel Target dalam 0,05 persen dari nilai sel target optimal teoretis.
Masalah
- Sebuah perusahaan memiliki sembilan proyek yang sedang dipertimbangkan. NPV yang ditambahkan oleh setiap proyek dan modal yang dibutuhkan oleh setiap proyek selama dua tahun ke depan ditunjukkan dalam tabel berikut. (Semua angka ada dalam jutaan.) Misalnya, Proyek 1 akan menambahkan $14 juta dalam NPV dan membutuhkan pengeluaran sebesar $12 juta selama Tahun 1 dan $3 juta selama Tahun 2. Selama Tahun 1, modal $50 juta tersedia untuk proyek, dan $20 juta tersedia selama Tahun 2.
| NPV | Pengeluaran Tahun 1 | Pengeluaran Tahun 2 | |
|---|---|---|---|
| Proyek 1 | 14 | 1.2 | 3 |
| Proyek 2 | 17 | 54 | 7 |
| Proyek 3 | 17 | 6 | 6 |
| Proyek 4 | 15 | 6 | 2 |
| Proyek 5 | 40 | 30 | 35 |
| Proyek 6 | 1.2 | 6 | 6 |
| Proyek 7 | 14 | 48 | 4 |
| Proyek 8 | 10 | 36 | 3 |
| Proyek 9 | 1.2 | 18 | 3 |
- Jika kita tidak dapat melakukan sebagian kecil dari sebuah proyek tetapi harus melakukan semua atau tidak sama sekali proyek, bagaimana kita bisa memaksimalkan NPV?
- Misalkan jika Proyek 4 dilakukan, Proyek 5 harus dilakukan. Bagaimana kita bisa memaksimalkan NPV?
Sebuah perusahaan penerbit mencoba menentukan mana dari 36 buku yang harus diterbitkan tahun ini. File Pressdata.xlsx memberikan informasi berikut tentang setiap buku:
- Proyeksi pendapatan dan biaya pengembangan (dalam ribuan dolar)
- Halaman di setiap buku
- Apakah buku ini ditujukan untuk audiens pengembang perangkat lunak (ditunjukkan dengan 1 di kolom E)
Sebuah perusahaan penerbit dapat menerbitkan buku dengan total hingga 8500 halaman tahun ini dan harus menerbitkan setidaknya empat buku yang ditujukan untuk pengembang perangkat lunak. Bagaimana perusahaan bisa memaksimalkan keuntungannya?
Tentang artikel
Artikel ini diadaptasi dari Microsoft Office Excel 2007 Data Analysis and Business Modeling oleh Wayne L. Winston.
Buku bergaya kelas ini dikembangkan dari serangkaian presentasi oleh Wayne Winston, seorang ahli statistik terkenal dan profesor bisnis yang mengkhususkan diri dalam aplikasi Excel yang kreatif dan praktis.