Analisa Data dengan Add-in SOLVER

Halo semua, semoga sehat selalu. 

Ada pertanyaan melalui message di Facebook tentang SOLVER. Hmm, makanan apa coba?

Nah, biar gak dikira sejenis makanan atau bahkan sembako, maka kali ini kita akan coba memahami apa itu SOLVER. Yang pasti, buat anda yang berprofesi di dunia bisnis, SOLVER akan sangat-sangat bermanfaat. Tapi sesungguhnya, SOLVER juga sangat bermanfaat untuk profesi lainnya.

Oke, kita mulai dengan definisi.

 

APA ITU SOLVER?

Solver adalah sebuah Add-in Excel yang dibuat Microsoft untuk menganalisa nilai Optimal.

Buat yang gak tahu apa itu Add-in, Add-in itu sebenernya sama dengan macro. Jadi didalamnya berisi program-program juga. Bedanya, macro ini sudah di pack dan bisa ditambahkan pada Excel kita.

Kembali ke SOLVER ya. Nah Solver ini akan menghitung nilai optimal dari suatu formula.

Masih bingung kan? Ya udah, mending pake contoh aja gimana?

 

CONTOH ANALISA MENGGUNAKAN SOLVER

 

Contoh 1 : Juragan Angkot

Pak Gimin adalah juragan angkot yang sangat berdedikasi. Beliau memiliki 150 angkot dan memegang 3 izin rute trayek. Trayek A memiliki jarak 20 km, Trayek B memiliki jarak 30 km dan Trayek C memiliki jarak 50 km.

Sebagai orang yang berdedikasi, beliau ingin memberikan pelayanan maksimal bagi para penumpang. Salah satunya adalah waktu tunggu. Beliau ingin waktu tunggu penumpang sependek-pendeknya dengan meng-optimalkan jumlah armada pada trayek yang dimilikinya. Sebagai bahan perhitungan, rata-rata kecepatan angkot adalah 20 km/jam.

 

Nah, Pak Gimin minta tolong ke kita untuk menghitung berapa jumlah angkot untuk masing-masing trayek agar layanan paling optimal?

 

Sebelum kita mulai menyelesaikan kasusnya Pak Gimin diatas, sebaiknya kita mengenal istilah Solver berikut:

 

Set Target Cell: Adalah cell dimana kita menuliskan formula yang mengindikasikan nilai optimal. Maksudnya gini.

Kapan kita bisa katakan suatu kasus itu optimal dan kapan belum optimal? Maka jawabannya biasanya adalah, jika NILAI X, pada kasus tersebut besar, maka kita sebut optimal dan bila kecil maka belum optimal (atau bisa sebaliknya).

Nah, NILAI X itulah pengukur apakah suatu kasus optimal atau belum. Biasanya, NILAI X berisi formula dari parameter-parameter pembentuknya. Dan, NILAI X itulah SET TARGET CELL.

 

Equal to: adalah opsi untuk menentukan definisi optimal. Apakah optimal itu jika nilai "paling besar" atau "paling kecil" atau "sesuai nilai tertentu"?

 

Changing Cells: Adalah cell-cell yang berisi parameter pembentuk fomula yang boleh diubah-ubah nilainya untuk mendapatkan nilai Optimal.

 

Constraints: Adalah batasan-batasan untuk mendapatkan nilai optimal. Misalkan begini, dikatakan optimal jika nilainya sebesar A tapi harus merupakan bilangan bulat dan parameter B tidak boleh lebih besar dari 100.

 

Oke, kalo masih bingung, yang penting dibaca aja. Nanti akan lebih jelas pada penjelasan contoh kasus. So? Siap menghitung jumlah angkot pak Gimin?

Hmm, belum.. Kita harus buat persiapan dulu. Yaitu, memunculkan Menu Solver!

 

Sekarang, coba pastikan anda telah memiliki menu Solver. Coba lihat di : Menu --> Data --> Analysis --> Solver. Jika anda tidak menemukanya, maka anda perlu memunculkannya lebih dahulu.

Caranya:

1. Klik Office Button (button berbentuk logo MS Office), trus pilih excel option.

 

 

2. Pilih bagian Add-in, dan pada opsi dropdown paling bawah, pilih Add-in Manager dan klik GO.

 

 

3. Akan muncul daftar Add-in yang tersedia. Check (centrang) pada add-in Solver dan klik OK.

 

 

4. Dan, coba lihat sekali lagi pada menu Solver. Seharusnya sudah muncul menu Solver.

 

 

Sip.. Persiapan selesai. Sekarang waktunya menyelesaikan kasusnya Pak Gimin. ANGKOT!!.

Sekarang, kita coba rangkum kasus di atas dalam sebuah sheet excel:

 

 

1. Karena kita belum tahu, maka jumlah angkot per trayek kita isi nilai sesuka kita saja. Saya isi 5 angkot tiap-tiap trayek.

2. Waktu tunggu per trayek menggunakan formula: =(B4/20)/B5. Atau: Jarak / Kecepatan angkot / jumlah anngkot.

3. Waktu tunggu total adalah rata-rata seluruh trayek, formula nya: =AVERAGE(B6:D6).

4. Dan, saya jumlahkan semua jumlah angkot per trayek pada cell: E5. Formulanya: =SUM(B5:D5).

 

Sip! Rangkuman selesai, selanjutnya, waktunya menggunakan SOLVER. Ikuti langkah-langkah berikut:

 

a. Select Cell B10 (Waktu Tunggu Total).

Cell ini adalah cell yang menentukan apakah usaha ANGKOT pak Gimin sudah Optimal atau belum. Semakin kecil nilai pada cell ini, maka semakin Optimal. Atau waktu tunggu semakin pendek.

Setelah di select, coba klik Menu Solver yang tadi sudah kita munculkan. Akan muncul window Solver dalam kondisi kosong.

Sekarang kita akan isi satu-persatu.

 

 

b. Isi Set Target Cell

Isi Set Target Cell dengan referensi Cell yang menunjukkan kasus Pak Gimin optimal atau belum. Yaitu cell: B10.

 

c. Pilih Equal to

Pada kasus Pak Gimin, nilai optimal adalah jika mendapatkan waktu sekecil-kecilnya. Jadi, kita pilih "Min".

 

d. Isi Changing Cells

Pada kasus Angkot Pak Gimin, yang ingin kita cari adalah jumlah angkot per Trayek. Jadi, cell-cell yang boleh berubah adalah cell-cell yang menunjukkan jumlah angkot per trayek yaitu cell: B5 s/d D5.

 

e. Isi Constraint

Pada kasus Angkot Pak Gimin, constraint (batasan) kita adalah:

- Jumlah keseluruhan angkot pak Gimin adalah 150. Jadi akumulasi jumlah angkot per trayek tidak boleh lebih dari 150. Yang menunjukkan akumulasi jumlah angkot per trayek adalah cell: E5. Jadi, kita tambahkan pada constraint : E5 <= 150.

- Jumlah Angkot per Trayek haruslan merupakan bilangan bulat! Kan gak mungkin punya angkot cuma 0.5 (setengah). Karena itu, kita tambahkan pada constraint: B5:D5 = Integer.

Cara menambahkan constraint, klik Add. Maka akan muncul window constraint:

 

 

Setelah diisi, pilih add jika masih ingin menambahkan contraint lainnya. Atau OK jika sudah selesai.

 

f. Klik SOLVE!

Bila semua sudah siap, saatnya klik SOLVE. Maka Solver akan mencari angka paling Optimal dan menentukan jumlah Angkot yang paling optimal untuk tiap-tiap trayek.

Jika muncul window ini:

 

 

Klik Ok. Dan coba kita lihat hasilnya:

 

 

Dan.. Akan muncul berapa jumlah angkot tiap trayek paling pas agar secara keseluruhan waktu tunggu adalah yang paling kecil.

Berapa angkanya? Untuk Trayek A, jumlah angkotnya adalah: 39, Trayek B: 48 dan Trayek C: 63.

Sip.. Contoh pertama selesai..

 

Contoh 2 : Pabrik Bola

Ini adalah contoh kasus yang dikirimkan melalui message Facebook.

PT. BOLABOLI (bukan nama sebenarnya yah) adalah perusahaan yang memproduksi Bola Kaki dan Bola Basket. Bahan yang dibutuhkan untuk membuat bola adalah Karet dan Kulit. 

Berikut adalah data-data produksi PT. BOLABOLI:

- Untuk memproduksi Bola Basket dibutuhkan 3 ons Karet dan 4 m Kulit. Dan keuntungan per unit nya adalah Rp. 130.000.

- Untuk memproduksi Bola Kaki dibutuhkan 2 ons Karet dan 5 m Kulit. Sedangkan keuntungan per unit adalah Rp. 160.000.

- Di gudang, PT. BOLABOLI memiliki stok Karet 500 ons dan Kulit 800 m.

Permasalahannya, PT. BOLABOLI ingin meghitung pengaturan produksi yang dapat memberikan keuntungan paling maksimal. 

 

Nah, mari kita coba selesaikan dengan SOLVER, namun sebelumnya coba kita rangkung ke dalam Excel Sheet.

 

 

1. Yang ditanyakan adalah pengaturan produksi yang paling bisa mendapatkan profit perusahaan paling besar, maka kita harus buat formula yang menunjukkan profit perusahaan.

Profit perusahaan adalah profit per unit dikalikan jumlah unit produk A ditambah profit per unit dikalikan jumlah unit Produk B. MS. Excel memiliki formula SUMPRODUCT untuk mempermudahkan kita.

Karenanya, pada cell: B9, saya buat formula: =SUMPRODUCT(B6:C6,B7:C7).

2. Untuk permulaan Jumlah Unit (Jumlah Produksi) saya buat masing-masing 1 unit dulu. Nanti akan diubah oleh Solver.

3. Untuk jumlah bahan, saya isi formula: Jumlah Unit (Jumlah Produksi) * Jumlah bahan per unit. Misal untuk Karet Bola Basket: B7 * 3 dan untuk Kulit Bola Basket B7 * 4. Demikian pula untuk Bola Kaki.

Jadi, jumlah bahan yang dibutuhkan, akan bertambah sesuai dengan jumlah unit yang di produksi.

4. Karena bahan untuk Bola Kaki dan Bola Basket bercampur, maka perlu ditambahkan Total bahan. Ingat, total bahan Karet yang dimiliki PT. BOLABOLI adalah 500 ons, sedangkan kulit 800 ons.

 

Oke, setelah kita rangkung, saatnya kita selesaikan kasus ini dengan SOLVER! Ikuti langkah ini:

 

a. Klik menu Solver!

 

 

b. Isi Set Target Cell dengan cell Total Profit.

Karena disinilah kita menentukan apakah nilai sudah optimal atau belum. Dalam hal ini, nilai optimal adalah nilai profit terbesar. Maka isi: B9

 

c. Pilih Equal to "Max"

Karena, yang kita cari adalah pengaturan produksi pada nilai profit terbesar.

 

d. Isi Cell Changing.

Cell yang akan kita atur adalah penggunaan bahan untuk masing-masing produk. Maka pilih: B7:C7

 

e. Constraint

Aturan mainnya adalah:

- Jumlah bahan adalah integer (bilangan bulat). Karena agar jadi sebuah bola basket, dibutuhkan tepat 3 ons karet dan 4 m Kulit. Tidak lebih dan tidak kurang. Maka perlu ditambahkan: B7:C7 = Integer.

- Gudang menyimpan bahan karet 500 ons, baik untuk Bola Basket maupun Bola Kaki. Maka, total bahan karet harus maksimum 500. Jadi formulanya: D4 <= 500.

- Gudang menyimpan bahan kulit 800 m, baik untuk Bola Basket maupun Bola Kaki. Maka, total bahan kulit harus maksimum 800. Jadi formulanya: D5 <= 800.

 

f. Selanjutnya, waktunya Klik SOLVE!

Dan hasilnya adalah..

 

 

Akan sangat menguntungkan jika PT. BOLABOLI memproduksi 128 Bola Basket dan 57 Bola Kaki.

Dan ternyata, masih ada sisa bahan Karet: 2 ons dan Kulit: 3 m.

 

Horeeee.. Selesai!

Sampai jumpa lagi yah..