Menggunakan Solver untuk anggaran modal

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.

Bagaimana perusahaan menggunakan Solver untuk menentukan proyek harus melakukan?

Setiap tahun, perusahaan seperti Eli Lilly harus menentukan yang obat untuk mengembangkan; sebuah perusahaan seperti Microsoft, perangkat lunak mana program untuk mengembangkan; sebuah perusahaan seperti Proctor & Gamble, pelanggan produk yang baru untuk mengembangkan. Fitur Solver di Excel bisa membantu perusahaan yang membuat keputusan ini.

Sebagian besar perusahaan ingin melakukan proyek yang berkontribusi terbesar nilai bersih saat ini (NPV), tunduk pada sumber daya terbatas (biasanya modal dan kerja). Katakanlah bahwa perusahaan pengembangan perangkat lunak mencoba untuk menentukan 20 proyek perangkat lunak harus melakukan. NPV (dalam jutaan dolar) kontribusi setiap proyek serta huruf besar (di jutaan dolar) dan jumlah programmer diperlukan masing-masing tiga tahun yang diberikan pada Dasar Model lembar kerja dalam file Capbudget.xlsx, yang merupakan diperlihatkan dalam gambar 30-1 pada halaman berikutnya. Sebagai contoh, 2 proyek menghasilkan $908 juta. Memerlukan $151 juta selama tahun 1, $269 juta selama 2 tahun dan $248 juta selama tahun 3. Proyek 2 memerlukan 139 programmer selama tahun 1, 86 programer selama tahun 2, dan 83 programmer selama tahun 3. Sel E4:G4 memperlihatkan kapital (dalam jutaan dolar) tersedia selama masing-masing tiga tahun, dan sel H4:J4 menunjukkan berapa banyak programmer tersedia. Misalnya, selama tahun 1 hingga $2,5 miliar dalam huruf besar dan 900 programmer tersedia.

Perusahaan harus memutuskan apakah harus melakukan setiap proyek. Mari kita berasumsi bahwa kami tidak bisa melakukan pecahan proyek perangkat lunak; Jika kita mengalokasikan 0,5 sumber daya yang dibutuhkan, misalnya, kita akan memiliki program libur yang akan membawa kita pendapatan $0!

Caranya di pemodelan situasi di mana Anda lakukan atau tidak ada adalah dengan menggunakan biner sel berubah. Biner mengubah sel selalu sama dengan 0 atau 1. Saat biner mengubah sel yang terkait ke sebuah proyek sama dengan 1, kami lakukan proyek. Jika biner mengubah sel yang terkait ke sebuah proyek sama dengan 0, kami tidak melakukan proyek. Menyiapkan Solver untuk menggunakan rentang biner mengubah sel dengan menambahkan batasan — pilih sel perubahan yang ingin Anda gunakan dan lalu pilih sampah dari daftar dalam kotak dialog Tambahkan batasan.

Gambar buku
Data gambar-30-1 yang akan digunakan dengan Solver untuk menentukan proyek untuk melakukan

Dengan latar belakang ini, kami sudah siap untuk mengatasi masalah pilihan proyek perangkat lunak. Seperti biasa dengan Solver model, kita mulai dengan mengidentifikasi sel-sel target, sel berubah, dan batasan.

  • Sel target. Kami memaksimalkan NPV yang dihasilkan oleh proyek dipilih.

  • Mengubah sel. Kami Cari 0 atau 1 sel berubah biner untuk masing-masing proyek. Saya berada sel-sel dalam rentang A6:A25 (dan bernama rentang lakukan). Misalnya, 1 dalam sel A6 menunjukkan bahwa kami melakukan proyek 1; 0 dalam sel C6 mengindikasikan bahwa kami tidak melakukan proyek 1.

  • Batasan. Kita perlu memastikan bahwa untuk setiap tahun t (t = 1, 2, 3), tahun t modal digunakan kurang dari atau sama dengan tahun huruf besar t , dan tahun t kerja digunakan lebih kecil dari atau sama dengan tahun t tenaga tersedia.

Seperti yang Anda lihat, kami lembar kerja harus menghitung untuk setiap pilihan proyek NPV, kapital digunakan tahunan dan programmer digunakan setiap tahun. Dalam sel B2, saya menggunakan rumus SUMPRODUCT(doit,NPV) untuk menghitung NPV total yang dihasilkan oleh proyek dipilih. (Nama rentang NPV merujuk ke 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, kita dapat menghitung NPV semua proyek, dan sel-sel target linear karena itu dihitung dengan menjumlahkan istilah yang mengikuti formulir (mengubah cell)*(constant). Dengan cara yang sama, cara menghitung modal digunakan setiap tahun dan tenaga digunakan setiap tahun dengan menyalin dari E2 untuk F2:J2 rumus SUMPRODUCT(doit,E6:E25).

Saya sekarang isi dalam kotak dialog parameter Solver seperti yang diperlihatkan dalam gambar 30-2.

Gambar buku
Kotak dialog parameter Solver gambar-30-2 kumpulan untuk model pilihan proyek

Tujuan kami adalah untuk memaksimalkan NPV proyek dipilih (sel B2). Sel-sel yang berubah kita (rentang bernama lakukan) adalah biner mengubah sel untuk masing-masing proyek. Batasan E2:J2 < = E4:J4 memastikan bahwa selama tahun setiap huruf besar dan tenaga digunakan lebih kecil atau sama dengan huruf besar dan tenaga tersedia. Untuk menambahkan batasan yang membuat sel berubah biner, saya klik Tambahkan dalam kotak dialog parameter Solver dan lalu pilih sampah dari daftar di tengah kotak dialog. Kotak dialog Tambahkan batasan akan muncul seperti yang diperlihatkan dalam gambar 30-3.

Gambar buku
Menggunakan gambar-30-3 Bin opsi dalam kotak dialog Tambahkan batasan untuk menyiapkan biner mengubah sel-sel yang akan menampilkan 0 atau 1.

Model kami linear karena sel target dihitung sebagai jumlah istilah mempunyai formulir (mengubah cell)*(constant) dan karena batasan penggunaan sumber daya dihitung dengan membandingkan jumlah (mengubah cells)*(constants) untuk konstanta.

Kotak dialog parameter Solver terisi dalam, klik selesaikan dan kami memiliki hasil yang ditampilkan sebelumnya di gambar 30-1. Perusahaan bisa mendapatkan NPV maksimum $9,293 juta ($9.293 miliar) dengan memilih proyek 2, 3, 19 6-10, 14-16, dan 20.

Kadang-kadang proyek-pilihan model memiliki batasan lainnya. Misalnya, anggap bahwa jika kita memilih 3 proyek, kami juga harus memilih 4 proyek. Karena solusi optimal saat ini kami memilih proyek 3 tapi tidak proyek 4, kami mengetahui bahwa solusi saat ini kami tidak bisa tetap optimal. Untuk mengatasi masalah ini, cukup tambahkan batasan yang sel berubah biner untuk proyek 3 kurang dari atau sama dengan sel berubah biner untuk proyek 4.

Anda bisa menemukan contoh ini di lembar kerja jika 3 lalu 4 di dalam file Capbudget.xlsx, yang diperlihatkan dalam gambar 30-4. Sel L9 merujuk ke nilai biner yang terkait dengan proyek 3, dan sel L12 ke biner nilai yang terkait dengan 4 proyek. Dengan menambahkan batasan L9 < = L12, jika kita memilih proyek 3, L9 sama dengan 1 dan batasan kami memberlakukan L12 (4 proyek biner) untuk sama dengan 1. Batasan kami juga harus meninggalkan biner nilai dalam sel berubah proyek 4 tak dibatasi jika kita tidak memilih 3 proyek. Jika kita tidak memilih proyek 3, L9 sama dengan 0 dan batasan kami memungkinkan 4 proyek biner sama dengan 0 atau 1, yang merupakan kami sesuai keinginan. Solusi yang optimal baru diperlihatkan dalam gambar 30-4.

Gambar buku
Gambar-30-4 baru solusi yang optimal untuk jika tidak proyek 3 lalu 4 proyek

Solusi optimal baru dihitung jika memilih proyek 3 berarti kami juga harus memilih 4 proyek. Sekarang anggap bahwa kita dapat melakukan hanya empat proyek antara proyek 1 hingga 10. (Lihat Di sebagian besar 4 P1 – P10 lembar kerja, diperlihatkan dalam gambar 30-5). Dalam sel L8, kita menghitung jumlah nilai biner yang terkait dengan proyek 1 hingga 10 dengan rumus SUM(A6:A15). Lalu kita tambahkan batasan L8 < = L10, yang memastikan bahwa, paling, 4 terlebih dahulu 10 proyek yang dipilih. Solusi yang optimal baru diperlihatkan dalam gambar 30-5. NPV memiliki turun untuk $9.014 juta.

Gambar buku
Solusi yang Optimal gambar-30-5 ketika kita bisa memilih hanya 4 dari 10 proyek

Model Solver linear dalam sel mana yang beberapa atau semua perubahan diperlukan untuk menjadi biner atau bilangan bulat yang biasanya lebih sulit untuk mengatasi dari model linear semua perubahan sel yang diperbolehkan menjadi pecahan. Alasan ini, kami sering puas dengan dekat optimal solusi untuk masalah pemrograman biner atau bilangan bulat. Jika Anda model Solver berjalan untuk waktu yang lama, Anda mungkin ingin mempertimbangkan menyesuaikan pengaturan toleransi di dalam kotak dialog Opsi Solver. (Lihat gambar 30-6). Sebagai contoh, pengaturan toleransi 0,5% berarti bahwa Solver akan berhenti pertama kalinya menemukan solusi yang memungkinkan yang ada di dalam 0,5 persen dari nilai sel target optimal teoritis (nilai sel target optimal teoritis adalah nilai optimal target ditemukan ketika batasan biner dan bilangan bulat dihilangkan). Sering kita menghadapi pilihan antara menemukan jawaban dalam 10 persen yang optimal dalam 10 menit atau menemukan solusi yang optimal di 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 teoritis.

Gambar buku
Gambar-30-6 menyesuaikan toleransi

  1. 1. perusahaan memiliki sembilan proyek dipertimbangkan. NPV ditambahkan oleh masing-masing proyek dan modal yang diperlukan oleh masing-masing proyek selama dua tahun berikutnya diperlihatkan dalam tabel berikut. (Semua angka yang ada di jutaan.) Misalnya, 1 proyek akan menambahkan $14 juta di NPV dan mengharuskan pengeluaran $12 juta selama tahun 1 dan $3 juta selama tahun 2. Selama tahun 1, $50 juta dalam huruf besar tersedia untuk proyek, dan $20 juta tersedia selama tahun 2.

NPV

Pengeluaran tahun 1

Pengeluaran tahun 2

Proyek 1

14

12

3

Proyek 2

17

54

7

Proyek 3

17

6

6

Proyek 4

15

6

2

Proyek 5

40

30

35

Proyek 6

12

6

6

Proyek 7

14

48

4

Proyek 8

10

36

3

Proyek 9

12

18

3

  • Jika kami tidak bisa melakukan pecahan proyek tapi harus melakukan semua atau tidak ada proyek, bagaimana kami memaksimalkan NPV?

  • Anggap bahwa dilakukan proyek 4, 5 proyek harus dilakukan. Bagaimana kami bisa memaksimalkan NPV?

  • Sebuah perusahaan penerbitan mencoba untuk menentukan buku 36 harus menerbitkan tahun ini. File Pressdata.xlsx memberi informasi berikut tentang setiap buku:

    • Biaya diproyeksikan pendapatan dan pengembangan (dalam ribuan dolar)

    • Halaman di setiap buku

    • Apakah buku yang ditujukan untuk audiens pengembang perangkat lunak (ditunjukkan dengan 1 di kolom E)

      Sebuah perusahaan penerbitan bisa menerbitkan buku dengan tahun ini hingga 8500 halaman dan harus menerbitkan setidaknya empat buku diarahkan pengembang perangkat lunak. Bagaimana perusahaan memaksimalkan laba?

Artikel ini adalah Microsoft Office Excel 2007 Analisis Data dan pemodelan bisnis dengan Wayne L. Winston.

Buku ruang ini dikembangkan dari serangkaian presentasi oleh Wayne Winston, statistik dikenal dan Profesor bisnis yang khusus kreatif, praktis aplikasi Excel.

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.

×