SA-4102 ANALISIS SISTEM DAN OPTIMASI SDA

SEMESTER I 2018/2019

TUGAS PROGRAM SOLVER PADA MICROSOFT EXCEL

Diajukan untuk memenuhi syarat kelulusan mata kuliah

SA-4102 Analisis Sistem dan Optimasi SDA

Dosen Mata Kuliah :

Dr. Ana Nurganah Chaidar ST.,MT,

Disusun Oleh :

Ahmad Hambali 15815015

PROGRAM STUDI TEKNIK PENGELOLAAN SUMBER DAYA AIR

FAKULTAS TEKNIK SIPIL DAN LINGKUNGAN

INSTITUT TEKNOLOGI BANDUNG

TAHUN AJARAN 2018/2019
1. Memanggil Program Solver di Excel
Seperti yang kita tahu seharusnya program solver terdapat pada menu Data di excel, tetapi
tampilan menu Data akan sebagai berikut, yaitu tidak terdapat program solver.

Sehingga yang perlu dilakukan adalah menambahkan add-ins Program Solver pada Excel dengan
cara sebagai berikut :

a. Pertama-tama membuka menu File pada Excel lalu klik pada options yang terdapat pada
bagian bawah, seperti berikut.
b. Setelah meng-klik Options maka akan muncul jendela seperti pada gambar berikut.

c. Selanjutnya klik menu add-ins sehingga muncul jendela sebagai berikut.

 d. Lalu centang seluruh add-ins yang tersedia, setelah itu klik OK. Sehinga program solver akan
muncul ketika mengklik menu Data, seperti pada gambar berikut.

2. Definisi Problem Solver

Secara definisi Problem adalah seluruh permasalahan yang dihadapi manusia baik yang dihadapi
di dunia nyata maupun yang dihadapi di perhitungan, sedangkan Solver adalah program
tambahan Microsoft Excel yang bisa digunakan untuk analisis bagaimana-jika dalam hal ini
Solver merupakan suatu alat. Sehingga problem solver adalah alat untuk memecahkan
permasalahan permasalahan yang dihadapi manusia. Solver bekerja dengan grup sel, yang
disebut variabel keputusan atau sel variabel sederhana yang digunakan dalam penghitungan
rumus di dalam sel tujuan dan batasan.

3. Apa fungsi/kegunaan dari solver

fungsi solver adalah Untuk menemukan nilai optimal (maksimum atau minimum) untuk rumus
di dalam satu sel (sel tujuan) dan tunduk pada batasan, atau batas. Solver menyesuaikan nilai di
dalam sel variabel keputusan untuk memenuhi batas pada sel batasan dan memberikan hasil
yang Anda inginkan untuk sel tujuan.

Kita dapat menggunakan Solver untuk menentukan nilai minimum atau maksimum dari satu sel
target dengan mengubah sel lainnya. Misalnya kita ingin melihat nilai maksimum dari
pemanfaatan debit yang ada dalam suatu sistim sungai, kita dapat menangani masalah yang
banyak melibatkan sel variable, Memungkinkan pengguna untuk mendefinisikan sendiri suatu
batasan/kendala yang harus dipenuhi agar masalah dianggap benar, serta Solver dapat
digunakan untuk memecahkan masalah yang memerlukan pembuatan model matematik dengan
sebuah proses yang dapat diprogram (algoritma).
4. Buat langkah cara penggunaan Solver secara jelas

Untuk menggunakan solver pertama tama kita harus memiliki suatu masalah yang
dituliskan dalam bentuk table di Microsoft Excel, kita memiliki target nilai yang dicari, serta
memiliki variable variable yang akan diubah dengan Batasan Batasan tertentu.

Setelah memiliki permasalahan yang disusun dalam bentuk table, langkah selanjutnya
adalah mengklik Tab Data lalu di grup analysis klik Solver.

Pada kotak Set Objective, masukkan sel referensi dari sel yang nilainya ingin kita ubah atau
cari. Sel Objective harus berisikan formula.

Lakukan hal hal berikut :

- Jika ingin nilai yang dicari menjadi sebesar mungkin, klik Max.
- JIka ingin nilai yang dicari sekecil mungkin, Klik Min.
- Jika ingin nilai yang dicari menjadi sama dengan dengan suatu nilai, klik Value of, Lalu ketik
berapa nilainya di kotak yang disediakan.

Pada kotak By Changing Variable Cells, masukkan sel referensi yang merupakan variable variable
yang nilainya akan diubah sesuai dengan nilai dari Objectives yang kita inginkan. Kita bisa
menambahkan sampai sebanyak 200 sel variabel.

Pada kotak Subject to the Constraints, masukkan batasan-batasan atau parameter dari keadaan
permasalahan yang sebelumnya telah dituliskan dalam tabel dengan melakukan hal hal berikut:

- Pada kotak dialog dari parameter, klik add, untuk menambahkan suatu Batasan. Lalu muncul
kotak dialog untuk memasukan Batasan.
- Pada kotak Cell Reference, masukkan nama sel atau referensi sel yang ingin kita jadikan
sebagai Batasan atau yang nilainya ingin kita batasi, dll.
- Klik hubungan antara sel yang nilainya ingin kita batasi dengan nilai Batasan atau constraint
(yaitu simbol <=, =, >=, int, bin, atau dif ) lalu pilih symbol sesuai yang kita inginkan. Jika
mengklik int, integer akan muncul di kotak Constrain yang akan membatasi sel sel tersebut
hanya berisikan nilai bilangan bulat saja. Jika mengklik bin, binary akan muncul di kotak
Constraint sehingga sel referensi akan dibatasi hanya bernilai 0 atau 1 saja. Jika mengklik dif,
alldifferent akan muncul di kotak Constraint.
- Jika memilih simbol <=, =, atau >= untuk tanda hubung maka di kotak Constrain, tuliskan
angka, nama sel atau referensi sel, atau sebuah formula.
 Lakukan hal hal berikut ini:

Untuk menyelesaikan batasan yang satu lalu ingin menambahkan batasan lain, klik Add.

Untuk menyelesaikan Batasan yang satu lalu kembali ke kotak dialog Solver Parameters, klik OK.

Untuk catatan bahwa kita bisa menerapkan bin, int, dan dif hanya untuk sel sel yang terdapat
pada kotak By Changing Variable Cell

Kita bisa mengubah atau mengapus batasan dengan melakukan hal hal berikut ini:

- Pada kotak dialog Solver Parameters, klik Batasan atau parameter yang ingin kita hapus atau
ubah.
- klik Change lalu ketikkan perubahan yang diinginkan, atau klik Delete untuk menghapus.

Setelah semuanya berhasil diatur klik Solve dan lakukan salah satu dari hal hal berikut:

- Untuk membiarkan hasil yang telah disediakan oleh solver pada lembar kerja kita, pada
kotak dialog Solver Results, klik Keep Solver Solution.
- Untuk mengembalikan data data sel kita seperti semula, klik Restore Original Values.
- Kita bisa menghentikan proses solving oleh solver dengan mengklik tombol Esc.

Untuk membuat report berdasarkan pada solusi permasalahan kita setelah solver bekerja, kita
bisa mengklik report type pada kotak Reports lalu klik OK. Report atau laporan akan dibuat pada
lembar kerja baru atau new worksheet di file Ms. Excel kita. Jika solver tidak dapat menemukan
solusinya maka hanya beberapa laporan atau reports yang tersedia bahkan tidak ada reports
yang bisa dilaporkan.

Untuk menyimpan nilai nilai sel variable yang telah diputuskan sebelumnya untuk kemudian
ditampilkan sebagai scenario pengerjaan, klik Save Scenario pada kotak Solver Results, dan ketik
nama untuk skenario di kotak scenario name.

5. Buat Contoh permasalahan beserta pengerjaan dari sebuah kasus menggunakan solver

Misalkan Perusahaan Bujangka memiliki dua pabrik (pabrik 1 dan pabrik 2), memiliki lima daerah
pemasaran (Daerah Jakarta, Bogor, Depok, Tangerang, dan Bekasi). Masing-masing pabrik
memiliki kapasitas produksi yang berbeda, dan masing-masing daerah memiliki batasan
permintaan yang juga berbeda. Kapasitas produksi pabrik 1 sebanyak 75.000 unit, pabrik 2
sebanyak 65.000 unit. Permintaan di daerah Jakarta sebanyak 25.000 unit, daerah Bogor
sebanyak 24.000 unit daerah Depok sebanyak 25.000 unit, daerah Tangerang sebanyak 35.000
unit dan daerah Bekasi sebanyak 16.000 unit.

Biaya transport untuk pengiriman dari pabrik ke masing-masing daerah adalah sebagai berikut.
 Tujuan
Pabrik
Jakarta Bogor Depok Tangerang Bekasi
Pabrik 1 Rp 2,000 Rp 2,000 Rp 2,000 Rp 1,500 Rp 2,500
Pabrik 2 Rp 1,500 Rp 2,500 Rp 2,000 Rp 1,500 Rp 2,000

Bagaimana Perusahaan Bujangka mendistribusikan produknya untuk memenuhi permintaan

masing-masing daerah dengan batasan produksi masing-masing pabrik, agar biaya minimum
pengiriman tercapai.

Dalam linear programming, masalah kita tersebut dapat diformulasikan dalam model matematik
yang meliputi tiga tahap :

A. Variabel Keputusan: Menentukan variabel yang tak diketahui (variabel keputusan) dan
menyatakan dalam simbol matematik

Variabel keputusan dalam masalah ini adalah jumlah barang yang dikirimkan dari masing-
masing pabrik ke masing-masing daerah.

Jumlah ini dapat dilambangkan sebagai :

a. P1A = jumlah barang yang dikirimkan dari pabrik 1 ke daerah A

b. P1B = jumlah barang yang dikirimkan dari pabrik 1 ke daerah B
c. P1C = jumlah barang yang dikirimkan dari pabrik 1 ke daerah C
d. P1D = jumlah barang yang dikirimkan dari pabrik 1 ke daerah D
e. P1E = jumlah barang yang dikirimkan dari pabrik 1 ke daerah E
f. P2A = jumlah barang yang dikirimkan dari pabrik 2 ke daerah A
g. P2B = jumlah barang yang dikirimkan dari pabrik 2 ke daerah B
h. P2C = jumlah barang yang dikirimkan dari pabrik 2 ke daerah C
i. P2D = jumlah barang yang dikirimkan dari pabrik 2 ke daerah D
j. P2E = jumlah barang yang dikirimkan dari pabrik 2 ke daerah E

B. Fungsi tujuan: Membentuk fungsi tujuan yang ditunjukkan sebagai suatu hubungan linier
(bukan perkalian) dari variabel keputusan

Tujuan masalah kita adalah meminimumkan biaya trasportasi total. Jelas bahwa biaya
transport adalah jumlah biaya dari masing-masing pabrik ke masing-masing daerah. Biaya
dari pabrik 1 ke daerah A adalah perkalian antara jumlah produk yang dikirimkan dari pabrik
1 ke daerah A dengan biaya tranport per unit. Dengan cara serupa juga dapat dihitung untuk
pabrik dan daerah lainnya. Sehingga total biaya transport Z, dapat ditulis :

Z = 2000P1A + 2000P1B + 2000P1C + 1500P1D + 2500P1E + 1500P2A + 2500P2B + 2000P2C +

1500P2D + 2000P2E
C. Fungsi kendala: Menentukan semua kendala masalah tersebut dan mengekspresikan dalam
persamaan dan pertidaksamaan yang juga merupakan hubungan linier dari variabel
keputusan yang mencerminkan keterbatasan sumberdaya masalah itu

Dalam masalah ini ada dua kendalanya yaitu kendala permintaan dan kendala produksi.
Total barang yang diterima di masing-masing daerah harus lebih besar atau sama dengan
permintaan daerah tersebut, serta total barang yang dikirimkan dari masing-masingpabrik
harus lebih kecil atau sama dengan kapasitas produksi pabrik tersebut.

1. Kendala permintaan:
a. Daerah A: P1A + P2A ≥ 25.000
b. Daerah B: P1B+ P2B ≥ 24.000
c. Daerah C: P1C + P2C ≥ 25.000
d. Daerah D: P1D + P2D ≥ 35.000
e. Daerah E: P1E + P2E ≥ 16.000

2. Kendala produksi:
a. Pabrik 1: P1A + P1B + P1C + P1D + P1E ≤ 75.000
b. Pabrik 2: P2A + P2B + P2C + P2D + P2E ≤ 65.000

3. Kita juga membatasi masing-masing variabel hanya pada nilai positif, karena tidak
mungkin unit barang yang dikirimkan negatif. Kendala-kendala ini dikenal dengan non
negativity constraints dan secara matematis dapat ditulis :

P1A, P1B, P1C, P1D, P1E, P2A,P2B, P2C, P2D, P2E ≥ 0

Dari tiga tahapan tersebut, formulasi LP secara lengkap dapat ditulis :

Meminimumkan Z = 2000P1A + 2000P1B + 2000P1C + 1500P1D + 2500P1E + 1500P2A +

2500P2B + 2000P2C + 1500P2D + 2000P2E

Dengan kendala:

a. P1A + P2A ≥ 25.000

b. P1B+ P2B ≥ 24.000
c. P1C + P2C ≥ 25.000
d. P1D + P2D ≥ 35.000
e. P1E + P2E ≥ 16.000
f. P1A + P1B + P1C + P1D + P1E ≤ 75.000
g. P2A + P2B + P2C + P2D + P2E ≤ 65.000
h. P1A, P1B, P1C, P1D, P1E, P2A,P2B, P2C, P2D, P2E ≥ 0

Setelah merumuskan model linear programming tersebut, sekarang kita akan menggunakan
Solver pada Excel untuk memecahkan (mencari optimisasinya).
Berikut tampilan lembar kerja Ms. Excel berdasarkan pada permasalahkan yang disebutkan
sebelumnya.

Biaya transport per unit barang

Tujuan
Pabrik
Jakarta Bogor Depok Tangerang Bekasi
Pabrik 1 Rp 2,000 Rp 2,000 Rp 2,000 Rp 1,500 Rp 2,500
Pabrik 2 Rp 1,500 Rp 2,500 Rp 2,000 Rp 1,500 Rp 2,000

Jumlah yang dikirimkan

Tujuan
Pabrik Total Kapasitas
Jakarta Bogor Depok Tangerang Bekasi
Pabrik 1 0 0 0 0 0 0 75000
Pabrik 2 0 0 0 0 0 0 65000
Total 0 0 0 0 0
Permintaan 25000 24000 25000 35000 16000
Total Biaya pengiriman Rp -

Setelah mempersiapkan semua data tersebut, kemudian klik Tab Data lalu pada bagian
Analysis klik Solver sehingga akan muncul tampilan jendela seperti berikut ini.
Pada kotak set objectives dimasukkan sel yang ingin kita cari nilai yang paling minimumnya
yaitu sel biaya pengiriman seperti berikut.
Karena dalam permasalahan ini adalah ingin dicari solusi nilai minimumnya maka pada
pilihan (To :) dipilih Min. seperti pada gambar berikut ini.

Karena yang ingin kita ubah nilainya adalah seberapa banyak unit barang yang akan
dikirimkan pabrik ke masing masing daerah maka pada kotak By Changing Variable Cells:
diisikan sel jumlah barang yang dikirim ke masing masing daerah dari pabrik yaitu sel B13
sampai F14 seperti pada gambar berikut ini.

Lalu pada kotak Subjent to the constraint diisikan masing masing Batasan yang perlu kita
masukkan yaitu diantaranya adalah :

i. P1A + P2A ≥ 25.000

j. P1B+ P2B ≥ 24.000
k. P1C + P2C ≥ 25.000
l. P1D + P2D ≥ 35.000
m. P1E + P2E ≥ 16.000
n. P1A + P1B + P1C + P1D + P1E ≤ 75.000
o. P2A + P2B + P2C + P2D + P2E ≤ 65.000
p. P1A, P1B, P1C, P1D, P1E, P2A,P2B, P2C, P2D, P2E ≥ 0
Untuk memasukkan kendala maka kita harus mengklik add terlebih dahulu, lalu akan keluar
tampilan jendela yang kemudian kita isikan sesuai kendala kendala atau Batasan-batasan
yang kita hadapi seperti berikut ini.

Klik add untuk menambahkan kendala selanjutnya, lalu masukkan kendala selanjutnya.
Setelah seluruh kendala atau batasan dimasukkan ke kotak sesuai dengan lembar kerja Excel
kita, klik ok, maka tampilan akan sebagai berikut.

Ketuk solve, tunggu beberapa saat, maka akan muncul tampilan sebagai berikut dengan
nilai variabel pada lembar kerja kita sudah berubah menjadi nilai yang dioptimasi paling
minimal.
Klik Ok dengan keep solver solution dalam keadaan tercentang dan klik Answer, Sensitivity,
dan Limits pada kotak reports untuk menampilkan masing masing dari Reports tersebut.
Sehingga lembar kerja dan buku kerja Excel kita sudah berubah menjadi seperti berikut ini.
Berikut tampilan Answer Reports.
Berikut tampilan Sensitivity Report

Dan berikut tampilan Limit Report

Sehingga dapat disimpulkan Bahwa nilai biaya pengiriman paling minimal sebesar

Rp. 220,000,000