Menggunakan Tools What-If Analysis

Met Pagi menjelang siang Bloggers...lom tidur nih, suasana kota Ambon rada-rada mendung gitu hari ini. Sambil ngisi waktu lowong saya ingin berbagi sedikit lagi tentang salah satu Tools Excel yang sangat bermanfaat, yaitu Tools What-If Analysis. Tools ini sangat membantu kita dalam membuat perkiraan nilai dengan berbagai skenario maupun asumsi, atau dengan kata lain merupakan suatu proses mengubah nilai-nilai dalam Cell untuk melihat bagaimana perubahan tersebut akan mempengaruhi hasil dari rumus pada worksheet. So...let's go guys...

Menggunakan Tools What-If Analysis

Tools What-If Analysis pada Excel dapat kita temui pada menu Data seperti pada gambar berikut :

Scenario Manager 
Nah, untuk lebih jelas kita misalkan: anda memiliki sebuah toko buku, dan memiliki 100 bh persediaan buku. anda kemudian menjual sejumlah persentase tertentu untuk harga tertinggi dengan keuntungan 50.000 dan persentase tertentu untuk harga terendah dengan keuntungan 20.000. Maka bila anda menjual sebanyak 60% pada harga tertinggi maka cell D10 akan menghitung total keuntungan 60*50.000+40*20.000 = 3.800.000

Tetapi bagaimana bila anda ingin menjual pada harga tertinggi sejumlah 70% atau 80% bahkan bila ingin 100%, masing-masing persentase dengan skenarionya masing-masing pula, anda dapat menggunakan Scenario manager untuk melakukannya.


Anda cukup mengetik persentase yang diinginkan ke dalam sel C4 untuk melihat hasil yang sesuai dari skenario di sel D10. Namun, What-If Analysis memungkinkan Anda untuk dengan mudah membandingkan hasil diantara scenario yang berbeda.

Langkah-langkahnya :

1. Pada menu Data pilih Tools What-If Analysis lalu pilih Scenario Manager.








2. Scenario Manager dialog box akan muncul. Tambahkan skenario dengan meng-klik Add



3. Berikan Nama Skenarionya : Tertinggi 60% (terserah mau dinamain apa)





4. Masukkan nilai persentasenya, misalnya 60% diisi 0.6
   
   
   


5. Berikutnya masukkan lagi beberapa skenario yang dibutuhkan (mis 70%, 80%, 90% dan 100%), dan nantinya kotak dialog Scenario Manager akan tampak seperti di bawah ini:



untuk melihat hasil dari skenario, pilih skenario dan klik pada tombol Show. Excel akan mengubah nilai sel C4 sesuai bagi Anda untuk melihat hasil yang sesuai pada worksheet.


6. Scenario Summary
Untuk dengan mudah membandingkan hasil skenario ini, jalankan langkah-langkah berikut.
- Pilih dan klik tombol Summary...
- Select sel D11 sebagai sel hasil lalu klik OK




- Hasilnya seperti berikut :







7. Kesimpulan: jika Anda menjual 70% untuk harga tertinggi, Anda mendapatkan keuntungan total 4.100.000, jika Anda menjual 80% untuk harga tertinggi, Anda mendapatkan keuntungan total 4.400.000, dan seterusnya. Itulah cara mudah Tools What-If Analysis 

Goal Seek

Bagaimana jika Anda ingin tahu berapa banyak buku yang Anda perlu jual pada harga tertinggi, untuk mendapatkan keuntungan total persis 4.700.000? 
Dalam hal ini anda dapat menggunakan Goal Seek

1. Pada menu Data pilih Tools What-If Analysis lalu pilih Goal Seek.




Akan muncul dialog box Goal Seek


2. Pada Set cell isikan dengan D11
3. Pada To value isikan dengan 4700000
4. Pada By changing cell isikan dengan C4
5. Klik OK
6. Klik OK lagi

Hasilnya akan seperti gambar dibawah, Mudahkaannnn....

OKeh temans, berhubung sudah larut malam besok baru saya edit lanjutannya..hehe.

Memaksa Pengguna Mengaktifkan Macro Excel

Hi...hello...hahaha lama yah, lama banged baru nongol lagi, sepertinya setaon sekali baru nongol ngeblog, tapi yah sudahlah, setidaknya masih ada dan masih bisa nulis-nulis aja, daripada pikiran kosong gak tentu, yah mungkin bisa membantulah dikit-dikit. Oke kali ini saya akan membahas tentang Macro lagi...yaa benar, macro Excel, gak panjang sih, cuman tentang bagaimana suatu file atau workbook yang menggunakan macro bisa memaksa penggunanya untuk mengaktifkan macro, selain itu juga menghalangi file tersebut dapat di preview di jendela explorer. Mari kita mulai...

Membuat Pengguna Mengaktifkan Macro

Hal ini jadi perhatian saya ketika sedang mencoba membuat sebuah katalog harga sebagai database, namun untuk membatasi akses pada file tersebut kemudian saya membuat menu login memanfaatkan macro, dan kok di jendela preview isi file atau workbook dapat diakses,

lalu file terbuka seperti biasa dan tidak masuk ke menu login, setelah cek dan ricek...ternyata ada warning seperti gambar berikut:

Macro-nya telah didisable oleh Excel demi keamanan. Intinya adalah meski kita sudah membuat Macro SUPER CANGGIH, ribet bulet-bulet (hahaha...), tetapi macro tersebut tidak akan pernah berjalan kecuali penggunanya "MENGAKTIFKAN MACRO". Pertanyaannya: "Dapatkah kita Memaksa Pengguna Mengaktifkan Macro?".

Berikut solusi saya, sebenarnya simple saja...yang pertama adalah kita membuat satu worksheet dengan nama "MULAI" atau apapun itu terserah anda, nah worksheet ini sebenarnya hanya berisikan peringatan bagi pengguna untuk mengaktifkan Macro, seperti gambar di bawah:

Langkah kedua adalah kita menyembunyikan worksheet lainnya kecuali worksheet "MULAI". Terakhir...kita membuat Macro yang akan menampilkan worksheet yang hilang tadi sesuai loginnya.

Sampai di sini ceritanya tuh begini, ketika pengguna membuka file atau workbook Excel, maka pengguna akan diminta untuk mengaktifkan macro (bila Macro Excelnya belum aktif), dan sedikit memaksa karena pengguna tidak akan dapat melihat worksheet lain kecuali worksheet "MULAI".

Nah, untuk langkah terakhir di atas, maka kita memerlukan dua buah macro: pertama adalah macro yang menyembunyikan semua worksheet kecuali worksheet "MULAI" saat file menutup dan kedua adalah macro yang memunculkan semua worksheet kecuali worksheet "MULAI" saat file terbuka.

Macro saat file menutup

1. Aktifkan Visual Basic Editor dengan menekan ALT+F11
2. Pada Jendela Project cari project dengan nama file anda
3. Double Klik ThisWorkbook
4. Pilih Event Before Close pada menu dropdown
5. Masukkan kode berikut

______________________________________________________________________

   Private Sub Workbook_BeforeClose(Cancel As Boolean)

     

      'Step 1: Mendklarasikan variables, ini akan membuat sebuah wadah memori untuk setiap sheet yang kita loop 

    Dim ws As Worksheet

     

    'Step 2: Memastikan sheet MULAI tampil

    Sheets("MULAI").Visible = xlSheetVisible

     

    'Step 3: Melakukan looping untuk mengecek semua worksheet

    For Each ws In ThisWorkbook.Worksheets

     

    'Step 4: Memastikan aksi berikut untuk sheet yang bukan sheet MULAI

    If ws.Name <> "MULAI" Then

     

    'Step 5: Menyembunyikan sheet selain sheet MULAI

    ws.Visible = xlVeryHidden

    End If

     

    'Step 6: Loop ke sheet berikut

    Next ws

     

    'Step 7: Setelah mengecek macro menyimpan dan berakhir

    ActiveWorkbook.Save

    

    End Sub

______________________________________________________________________

Macro saat file terbuka

1. Aktifkan Visual Basic Editor dengan menekan ALT+F11
2. Pada Jendela Project cari project dengan nama file anda
3. Double Klik ThisWorkbook
4. Pilih Event Open pada menu dropdown
5. Masukkan kode berikut

______________________________________________________________________

Private Sub Workbook_Open()
    'Step 1: Mendklarasikan variables, ini akan membuat sebuah wadah memori untuk setiap sheet yang kita loop 
    Dim ws As Worksheet
     
    'Step 2: Start looping semua worksheets
    For Each ws In ThisWorkbook.Worksheets
     
    'Step 3: Menampilkan semua Worksheets
    ws.Visible = xlSheetVisible
    
    'Step 4: Loop ke worksheet berikut
    Next ws
     
    'Step 5: Sembunyikan sheet MULAI
    Sheets("MULAI").Visible = xlVeryHidden

    'Step 6: Sembunyikan Sheet selain Antarmuka:
    VisibleFalse

End Sub
_____________________________________________________________________


Setelah Macro di atas selesai dimasukkan, maka kita akan mempunyai File atau Workbook yang hanya bekerja bila Macro diaktifkan.

Demikian semoga bisa membantu teman-teman yang sedang belajar macro.