Contoh penggunaan Solver Excel dalam usaha

Posted by

Contoh file Anda dapat men-download file sampel yang berhubungan dengan kutipan dari Microsoft Office System Inside Out dari Microsoft Office Online. Artikel ini menggunakan Solver.xls file.
Ketika masalah peramalan Anda mengandung lebih dari satu variabel, Anda perlu menggunakan Solver add-in utilitas untuk menganalisis skenario. Veteran dari sekolah bisnis dengan senang hati akan mengingat studi kasus multivariabel sebagai bagian dari keuangan mereka dan pelatihan manajemen operasi. Sementara penjelasan lengkap masalah multivariable pemecahan dan optimasi berada di luar cakupan buku ini, Anda tidak perlu latar belakang sekolah bisnis untuk menggunakan perintah Solver untuk membantu Anda memutuskan berapa banyak produk untuk memproduksi, atau bagaimana harga barang dan layanan. Kami akan menunjukkan dasar-dasar dalam bagian ini dengan menggambarkan bagaimana sebuah kedai kopi kecil menentukan jenis kopi itu harus menjual dan apa potensi pendapatan adalah.
Dalam contoh kita kita menjalankan sebuah kedai kopi yang saat ini menjual tiga minuman: reguler segar-diseduh kopi, caffe latte premium, dan premium caffe mocha. Saat ini kami menetapkan harga kopi biasa di $ 1,25, caffe latte di $ 2,00, dan caffe mocha di $ 2,25, tapi kami tidak yakin apa potensi pendapatan kita dan apa yang kita harus memberikan penekanan kepada setiap minuman. (Meskipun kopi premium membawa lebih banyak uang, bahan-bahan mereka lebih mahal dan mereka mengambil lebih banyak waktu untuk membuat daripada kopi biasa.) Kita dapat membuat beberapa perhitungan dasar dengan tangan, tapi kami ingin struktur penjualan kami data dalam lembar kerja sehingga kita berkala dapat menambah dan menganalisanya dengan menggunakan Solver.
Catatan Solver adalah sebuah add-in utilitas, sehingga Anda harus memastikan bahwa itu diinstal pada sistem Anda sebelum Anda memulai. Jika perintah Solver tidak pada menu Tools Anda, pilih Tools, Add-Ins, dan pilih Solver Add-In pilihan dalam kotak dialog Add-Ins. Jika Solver tidak ada dalam daftar, Anda harus menginstalnya dengan menjalankan program Setup Office lagi dan memilih dari daftar Excel add-ins.

Menyiapkan masalah

Langkah pertama dalam menggunakan perintah Solver adalah untuk membangun sebuah worksheet Solver-friendly. Ini melibatkan menciptakan sel target untuk menjadi tujuan masalah untuk rumus misalnya, yang menghitung total pendapatan-dan satu atau lebih sel menugaskan variabel yang Solver dapat berubah untuk mencapai tujuan Anda. Worksheet Anda juga dapat berisi nilai-nilai lain dan formula yang menggunakan sel target dan sel-sel variabel. Bahkan, untuk Solver untuk melakukan tugasnya, masing-masing sel variabel Anda harus preseden dari sel target. (Dengan kata lain, rumus di sel target harus referensi dan tergantung pada sel-sel variabel untuk bagian dari perhitungannya.) Jika Anda tidak mengaturnya dengan cara ini, ketika Anda menjalankan Solver, Anda akan mendapatkan pesan kesalahan, "Nilai-nilai Sasaran Set your tidak bertemu."
Gambar 1 menunjukkan lembar kerja sederhana yang dapat kita gunakan untuk memperkirakan pendapatan mingguan untuk toko kopi kami contoh dan untuk menentukan berapa banyak cangkir setiap jenis kopi kami akan perlu menjual. The worksheet dalam gambar muncul dalam modus Formula Auditing, yang diaktifkan oleh Alat memilih, Formula Auditing, Mode Formula Auditing. Your G4 adalah sel target yang menghitung total pendapatan yang ketiga menghasilkan minuman kopi. Tiga baris yang berkumpul di sel G4 ditarik dengan memilih bahwa sel dan Peralatan memilih, Formula Auditing, Preseden Trace. Panah menunjukkan bagaimana rumus dalam G4 sel tergantung pada tiga perhitungan lain untuk hasilnya. (Untuk menghapus panah, pilih Tools, Formula Auditing, Hapus Semua Arrows.)
Tiga variabel sel dalam worksheet adalah sel D5, D9, dan D13-ini adalah sel kosong yang nilai-nilainya kita ingin Solver untuk menentukan ketika menemukan cara untuk memaksimalkan pendapatan mingguan kami.
Catatan workbook yang digunakan dalam contoh ini, Solver.xls, termasuk dalam men-download file sampel .
Di sudut kanan bawah layar kami adalah daftar kendala kami berencana untuk digunakan dalam peramalan kami. Kendala adalah prinsip membatasi aturan atau membimbing yang mendikte bagaimana bisnis dijalankan. Misalnya, karena fasilitas penyimpanan dan kendala merchandise, kami saat ini hanya mampu menghasilkan 500 cangkir kopi (baik reguler dan premium) per minggu. Selain itu, pasokan cokelat membatasi produksi mochas caffe dengan 125 per minggu, dan pembatasan pendingin susu membatasi produksi minuman kopi premium menjadi 350 per minggu.
The commands on the Tools, Formula Auditing submenu help you visualize the relationship between cells. Here the target cell depends on three other cells, each of which contains a formula.
Gambar 1: Perintah pada Tools, Formula submenu Audit membantu Anda memvisualisasikan hubungan antara sel-sel. Di sini sel target tergantung pada tiga sel lainnya, masing-masing berisi formula.
Ini struktur kendala masalah, dan kami akan memasukkan mereka dalam kotak dialog khusus ketika kita menjalankan perintah Solver. Worksheet Anda harus mengandung sel-sel yang menghitung nilai-nilai yang digunakan sebagai kendala (dalam contoh ini, G8, G7, dan D13). Nilai-nilai yang membatasi untuk kendala yang tercantum dalam sel G11 melalui G13. Meskipun daftar kendala yang tidak perlu, itu membuat worksheet lebih mudah untuk mengikuti.
Tip: Nama sel kunci
Jika masalah Solver Anda berisi beberapa variabel dan kendala, Anda akan merasa lebih mudah untuk memasukkan data jika Anda nama sel kunci dan rentang dalam worksheet Anda dengan menggunakan Insert, Nama, Tentukan perintah. Menggunakan nama sel juga memudahkan untuk membaca kendala Solver Anda nanti.

Menjalankan Solver

Setelah Anda telah mendefinisikan masalah peramalan Anda dalam worksheet, Anda siap untuk menjalankan Solver add-in. Langkah-langkah berikut menunjukkan kepada Anda bagaimana menggunakan Solver untuk menentukan pendapatan mingguan maksimum untuk warung kopi Anda mengingat kendala berikut:
  • Tidak lebih dari 500 jumlah cangkir kopi (baik reguler dan premium)
  • Tidak lebih dari 350 cangkir kopi premium (baik caffe latte dan caffe mocha)
  • Tidak lebih dari 125 caffe mochas
Selain mengatakan pendapatan maksimum, Solver menghitung distribusi optimal kopi dalam tiga kelompok kopi. Untuk menggunakan Solver, ikuti langkah-langkah berikut:
  1. Klik target sel-satu yang mengandung formula yang didasarkan pada sel variabel Anda ingin Solver untuk menentukan. Dalam contoh kita, seperti yang ditunjukkan pada Gambar 1, sel target G4.
  2. Pilih Tools, Solver. The Solver Parameter kotak dialog akan terbuka, seperti yang ditunjukkan di sini:
Solver Parameters dialog box
  1. Jika Target Set your kotak teks sudah tidak mengandung referensi yang benar, pilih kotak teks dan kemudian klik G4 sel untuk memasukkan $ G $ 4 sebagai sel target.
  2. Pilih opsi Max mengikuti Equal Untuk label, karena Anda ingin mencari nilai maksimum untuk sel target.
  3. Klik tombol di ujung kanan Dengan Mengubah Sel kotak teks untuk menutup kotak dialog. Pilih masing-masing sel variabel. Jika sel-sel berdampingan satu sama lain, cukup pilih grup dengan menyeret seluruh sel. Jika sel-sel yang noncontiguous, seperti dalam contoh kita, tahan tombol Ctrl dan klik setiap sel (ini akan menempatkan koma antara entri sel dalam kotak teks).
Sebagai contoh kita, pilih sel D5, D9, dan D13 (tiga sel kosong disediakan untuk jumlah cangkir kopi yang harus dijual dalam kategori masing-masing), yang akan menempatkan nilai berikut dalam kotak Dengan Mengubah Sel teks: $ D $ 5, $ D $ 9, $ D $ 13, seperti yang ditunjukkan di sini:
Solver Parameters dialog box
Tip: Gunakan tombol Guess untuk melihat hasilnya
Jika Anda mengklik tombol Guess, Solver mencoba untuk menebak di sel variabel dalam masalah peramalan Anda. Solver menciptakan menebak dengan melihat sel-sel direferensikan dalam rumus sel target. Jangan bergantung pada perkiraan ini, meskipun-itu sering salah!
  1. Kendala tidak diperlukan dalam semua masalah Solver, tetapi masalah ini memiliki tiga. Klik tombol Add untuk menambahkan kendala pertama menggunakan kotak dialog Add Kendala.
Kendala pertama adalah bahwa Anda dapat menjual hanya 500 cangkir kopi dalam satu minggu. Untuk memasukkan kendala ini, klik sel G8 (sel yang berisi formula cangkir total), pilih <= dalam daftar drop-down operator, dan dengan titik penyisipan dalam kotak teks Kendala, klik G11 atau Max_cups jenis, dengan menggunakan karakter garis bawah untuk menghubungkan kata-kata. (. Max_cups adalah nama sel G11 di contoh kita) Kotak Kendala Tambah dialog akan terlihat seperti ini:
Add constraint dialog box
Setelah selesai, klik tombol Add di kotak dialog Tambah Kendala untuk memasuki kendala pertama.
Catatan Anda memiliki pilihan untuk mengetik nilai, mengklik sel, atau memasukkan referensi sel atau nama dalam kotak teks Kendala. Jika Anda mengklik sebuah sel yang memiliki nama yang ditetapkan, Excel akan menggunakan nama itu saat Anda menambahkan kendala.
  1. Menentukan kendala-Anda dapat menjual kedua hanya 350 kopi premium dalam satu minggu. Dengan titik penyisipan dalam kotak Reference your teks, klik sel G7 (sel yang berisi formula cangkir premium), pilih <= dalam daftar drop-down operator, dan dalam teks Kendala kotak, Max_premium jenis (nama sel G12 ) atau klik sel G12. Setelah selesai, klik tombol Add untuk memasukkan kendala kedua.
  2. Tentukan ketiga kendala-Anda dapat menjual hanya 125 mochas caffe dalam satu minggu. Klik cell D13 (sel variabel yang berisi jumlah cangkir mocha), pilih <= dalam daftar drop-down operator, dan dalam kotak teks Kendala, jenis Max_mocha (nama G13 cell) atau klik sel G13.
  3. Klik tombol OK di kotak dialog Tambah Kendala untuk menampilkan semua tiga kendala di kotak Solver dialog. Seharusnya terlihat seperti yang ditunjukkan di sini:
Solver Parameters dialog box
Ujung
Untuk memodifikasi salah satu kendala yang muncul dalam kotak Solver Parameters dialog, pilih kendala dan klik tombol Change. Untuk menyesuaikan iterasi dan parameter perhitungan dalam utilitas Solver, klik tombol Options dan membuat penyesuaian Anda.
  1. Masalah peramalan Anda siap untuk pergi, jadi klik tombol Memecahkan untuk menghitung hasilnya.
Setelah berhenti sebentar Solver akan menampilkan kotak dialog Solver Hasil menggambarkan bagaimana analisis optimasi pergi. Jika Solver berjalan ke masalah, Anda akan melihat pesan kesalahan, dan Anda dapat mengklik tombol Help untuk mempelajari lebih lanjut tentang kesulitan. Jika Solver menemukan solusi, Anda melihat kotak dialog berikut:
Solver Results dialog box
  1. Untuk menampilkan solusi baru dalam worksheet Anda, pilih Jauhkan Solver option Solusi dalam kotak Solver Hasil dialog dan kemudian klik tombol OK. Solver akan menempatkan nilai optimum dalam sel target dan akan mengisi sel variabel dengan solusi yang paling cocok dengan kendala yang Anda tentukan, seperti yang ditunjukkan pada Gambar 2.
Dalam contoh ini, Anda telah belajar bahwa jika Anda terbatas untuk menjual 500 cangkir kopi per minggu, Anda dapat mengharapkan maksimal $ 918,75 dalam pendapatan dan distribusi minuman optimal Anda adalah 150 cangkir kopi biasa, 225 cangkir caffe latte, dan 125 cangkir caffe mocha. Meskipun model ini keuangan tidak mempertimbangkan variabel beberapa bisnis yang realistis, seperti biaya yang berkaitan dengan menjalankan toko dan manfaat dari melakukan pembelian volume, itu tidak membantu Anda untuk meramalkan jauh lebih mudah dan cepat daripada Anda bisa menggunakan pensil dan kertas.
When the Solver finishes, the values that produce the optimum result in the target cell will appear in the variable cells.
Gambar 2: Ketika Solver selesai, nilai-nilai yang menghasilkan hasil yang optimal dalam sel target akan muncul di sel variabel.
Callout 1 Maksimum pendapatan
Callout 2 Tiga variabel yang mewakili campuran jumlah minuman untuk kembali maksimum, mengingat kendala

Mengedit perkiraan Solver Anda

Mungkin fitur terbaik dari perkiraan Solver adalah bahwa Anda dapat dengan mudah mengeditnya untuk mengevaluasi tujuan-tujuan baru dan kontinjensi. Misalnya, jika Anda memutuskan bahwa Anda ingin mendapatkan tepat $ 700 per minggu dari minuman kopi, Anda dapat menggunakan Solver untuk memberi tahu Anda apa kombinasi optimum dari minuman akan. Menetapkan nilai target dalam Solver adalah sedikit seperti menggunakan Goal Seek perintah untuk menentukan nilai variabel yang tidak diketahui, meskipun dengan Solver Anda dapat menggunakan lebih dari satu variabel.
Untuk mengedit perkiraan Solver Anda buat dalam latihan sebelumnya untuk menemukan variabel yang dibutuhkan untuk mencapai tujuan tertentu, ikuti langkah berikut:
  1. Aktifkan worksheet di mana Anda sebelumnya menggunakan Solver. Dalam sel-sel variabel, meninggalkan nilai-nilai yang dihasilkan oleh Solver dalam latihan sebelumnya (yaitu, 150 di D5, 225 di D9, dan 125 di D13).
  2. Pilih Tools, Solver. The Solver Parameter kotak dialog akan muncul, masih menampilkan target, variabel, dan kendala dari masalah Solver terakhir Anda. Anda akan menyesuaikan ini untuk menghitung tujuan peramalan baru.
  3. Pilih Nilai Of pilihan dan 700 jenis dalam kotak teks ke kanan. Nilai Dari pilihan set sel target untuk tujuan tertentu sehingga Anda dapat menentukan campuran variabel yang Anda butuhkan untuk mencapai tonggak Anda. (. Dalam contoh ini, sel-sel variabel mewakili jumlah cangkir dari berbagai jenis kopi) kotak dialog Anda akan terlihat seperti ini:
Solver Parameters dialog box
  1. Klik tombol Memecahkan untuk menemukan solusi untuk masalah peramalan Anda. Ketika Solver telah selesai, klik tombol OK di kotak dialog Solver Hasil.
Gambar 3 menunjukkan solusi Solver menghasilkan jika, sebelum Anda berlari Solver, sel-sel variabel yang mengandung nilai-nilai yang dihasilkan dalam latihan sebelumnya (yaitu, 150, 225, dan 125). Hasil Solver baru menunjukkan bahwa Anda dapat membuat $ 700 dengan menjual 100 mochas, 175 latte, dan 100 kopi biasa.
When you specify a target goal, the Solver computes an optimum product mix that meets your constraints.
Gambar 3: Bila Anda menetapkan tujuan target, Solver menghitung suatu bauran produk optimal yang memenuhi kendala Anda.

Bagaimana jika ada lebih dari satu solusi untuk masalah ini?

Pada contoh sebelumnya, Solver menetapkan bahwa Anda bisa menjual 100 mochas, 175 latte, dan 100 kopi secara teratur untuk mencapai tujuan penjualan Anda sebesar $ 700. Tapi Anda juga bisa mencapai angka $ 700 menggunakan campuran produk yang berbeda, misalnya, Anda bisa menjual 94 kopi reguler, 151 latte, dan 125 mochas mencapai $ 700. (Menggunakan campuran ini, pendapatan Anda benar-benar akan menjadi $ 700,75.) Jadi, bagaimana Solver memutuskan apa bauran produk optimal akan? Solver itu hanya dimulai dengan angka saat ini di sel variabel dan disesuaikan mereka sampai menemukan solusi yang dapat diterima (tunduk pada batasan yang dijelaskan dalam contoh sebelumnya). Inilah sebabnya, jika Anda menggunakan nilai awal yang berbeda dalam sel variabel sebelum Anda menjalankan Solver, Anda bisa mendapatkan hasil yang berbeda dari masalah dengan beberapa solusi.

Bagaimana jika Solver mencapai batas iterasi tanpa mencari solusi?

Nilai-nilai yang dimulai pada sel-sel variabel dapat mempengaruhi solusi: Solver mungkin gagal untuk menemukan solusi atau mungkin mengatur waktu keluar sebelum mencapai solusi. Masukkan nilai dalam sel variabel yang jatuh dekat dengan apa yang Anda percaya nilai-nilai akhir akan. Jika Solver masih mencapai batas iterasi tanpa tiba di solusi, Anda dapat menyesuaikan nilai awal dan restart atau klik Lanjutkan untuk menggunakan waktu solusi maksimal. Anda dapat mengatur kedua iterasi maksimum dan waktu maksimum dengan menggunakan tombol Options di kotak dialog Solver Parameters.
Jika Anda ingin menggunakan campuran produk tertentu, Anda dapat mengambil keuntungan dari cara Solver mencapai hasilnya. Masukkan nilai-nilai yang Anda pikir mungkin dapat diterima dalam sel variabel sebelum Anda menjalankan Solver, dan Excel akan menggunakan mereka sebagai nilai-nilai awal ketika menghitung s solusi.


DR.CB
MS. Office TutorialUpdated at : April 04, 2013

4 comments:

  1. Blog bagus...memberi info yang sangat bermanfaat...Terimakasih Bro

    ReplyDelete
  2. Terima kasih , tulisan dan backgroundnya kurang padu , yang baca jadi agak bingung terima kasih

    serilkom.blogspot.com

    ReplyDelete
  3. keren! tapi di tutorialnya gambar excelnya kok ga muncul vroh? jadi agak bingung? bisa dimunculin ga vroh? thx

    ReplyDelete
  4. belum ngerti karna tanpa gambar

    ReplyDelete