Halaman 1 dari 13
Nurjati Widodo, S.AP, M.AP
Lesson 4. Presenting Data Visually
Excel punya kemampuan untuk membuat dokumen dengan tampilan profesional yang siap untuk
dipublikasi dan didistribusi. Namun terbatas dalam hal desain dan layout yang dapat dicapai. Maka
anda harus sangat kreatif untuk dapat menyalin data atau tabel dari excel dan menempatkannya di
word. Word merupakan software khusus dari Microsoft yang didesain untuk mengkreasi dokumen.
Ada dua pertimbangan ketika anda bekerja menggunakan Excel. Pertama, apakah anda akan
mencetak dokumen ini atau menggunakannya sebagai versi lunak/elektronik. Kedua, digunakan
untuk tujuan pelaporan ataukah diskusi?
Jika data akan diprint dan didistribusikan, anda harus mempertimbangkan bahwa halaman fisik
mempunyai beberapa pembatasan seperti ukuran dan area cetak. Ukuran fon/huruf dan warna
juga penting. Sebagai versi cetak tidak bisa dilakukan perubahan ukuran ataupun zion in
(diperbesar). Jika data masih merupakan suatu file excel, anda masih punya fleksibilitas dan tidak
terbebani dengan keterbatasan di atas. Jika data digunakan untuk tujuan pelaporan, maka aliran
dan tata letak sangat penting. Hal ini memastikan bahwa pengguna bisa mengikuti arah laporan.
Normalnya, diskusi hanya memerlukan data untuk pegangan.
Untuk memulai, buat dokumen sederhana dengan nama Monthly Region Performance. Dokumen
ini akan dibagi menjadi 2 area. Yang pertama akan melihat keseluruhan penjualan per bulan dan
yang kedua akan fokus pada individu wilayah.
1. Buat sebuah worksheet dengan nama “Regional Performances”. Tekan Shift + F11 secara
bersamaan untuk membuat sebuah worksheet baru secara cepat.
2. Pertama kita perlu tahu area kerja kita. Pada tab View, pilih Page Layout. Ini akan
menyajikan worksheet berada dalam bentuk physical page (halaman fisik). Biasanya saya
sarankan pengguna untuk membuat konsep
sederhana terlebih dahulu, kemudian kembali
dan poles dokumen sebelum mencetak. Dengan
cara ini anda akan lebih fleksibel.
3. Di A1, ketik ”Monthly Regional Performance”.
Di sesi pertama ini kita perlu melaporkan
keseluruhan penjualan per bulan. Karena kita
tidak membuat data ini sebelumnya, maka kita
perlu membuatnya sekarang. Di A4, ketik
“Month” dan di B4 ketik “Sales”. Sekarang isi
nama bulan mulai dari Januari hingga Desember
di kolom di bawah Month.
Halaman 2 dari 13
Nurjati Widodo, S.AP, M.AP
4. Data utama berada di worksheet Sales. Untuk menghindari keharusan menduplikasi semua
data, kita hanya perlu membuat rumus untuk mereferensi worksheet tersebut. Di B5 ketik
rumus =SUMIF(Sales!$B$4:$B$510;1;Sales!$I$4:$I$510). Jika anda perhatikan, kita tidak
mereferensi satu sel pun di worksheet baru kita. Karena di data kita, bulan seperti Januari
ditampilkan secara numerik sebagai 1. Karena laporan ini adalah laporan resmi, kita perlu
menggunakan January. Sehingga untuk menunjuk kriteria, kita bisa ketik 1 untuk mengetes
kondisi penjumlahan.
5. Copy dan Paste rumus ke sel lain Month. Jangan lupa untuk mengubah kriteria tes (1 sampai
dengan 12) untuk tiap bulan yang sesuai.
6. Setelah data sudah ada, kita langsung membuat diagram untuk membuat visualisasi. Diagram
yang paling cocok untuk data ini adalah grafik garis. Karena juga akan menyoroti tren dari
bulan ke bulan. Pilih A4:B16, pada tab Insert pilih Line. Kemudian klik pada Line Chart.
Pindah diagram tepat ke kanan data dan ubah ukurannya sehingga fit dengan baik pada
halaman.
7. Diagram secara otomatis akan membuat judulnya sendiri. Karena data utama dari tabel ini
merupakan field Sales, maka judul field Sales yang dijadikan Judul. Untuk memastikan agar
tidak ada kebingungan, kita perlu merubah judul. Pilih area ilustrasi grafik, kemudian klik pada
bagian judul. Ketikkan “2010 Monthly Sales of Office 2010”.
8. Untuk mengubah sifat (properties) lain dari diagram kita perlu menggunakan fungsi-fungsi
yang ada di tab Layout. Karena data kita hanya satu, kita bisa terus lanjut dan menghapus
legenda (keterangan) grafik. Pada tab Layout pilih Legend dan klik None. Atau anda bisa klik
pada Legend dan tekan tombol delete.
Halaman 3 dari 13
Nurjati Widodo, S.AP, M.AP
9. Angka-angka yang ada di sumbu (axis) Y cukup
sulit untuk dibaca, oleh karena itu kita perlu
membuat beberapa perubahan. Pada tab Layout
pilih Axis. Kemudian pilih Primary Vertical Axis
dan klik More Primary Vertical Axis Options.
Akan terbuka jendela Format Axis. Pilih bagian
Number. Rubah kategori menjadi Currency
dengan menekannya. Kemudian rubah desimal
menjadi 0 karena kita tidak memerlukan poin
desimal.
10. Untuk mendapatkan angka yang lebih pas, kita
dapat mengurangi ukuran dengan mengubahnya ke
satuan ribuan. Di jendela Format Axis pilih Axis
Option. Kemudian di kotak drop down Display Unit
pilih Thousands. Dan tandai kotak Show display
units label on chart.
11. Untuk membuat diagram benar-benar menonjol,
kita akan memodifikasi style atau tampilannya.
Pada tab Design, klik pada kotak drop down Chart
Style untuk membuka galeri. Kemudian pilih style
44. Harap diingat bahwa jika anda mencetak dalam
keadaan hitam putih, beberapa kombinasi warna
tidak akan muncul bagus. Anda bisa memodifikasi
warna menjadi biru, jika suka. Klik dua kali tombol
kiri mouse ke diagram. Akan tebuka jendela Format
Chart Area. Pilih Fill dan kemudian pilih Solid Fill.
Pada bagian bawah, pilihan warna akan muncul.
Pilih merah. Anda juga bisa menekan bagian dalam
diagram untuk mengubah warna latar belakang.
Halaman 4 dari 13
Nurjati Widodo, S.AP, M.AP
Untuk mengkonversi diagram ini menjadi sesuatu yang berarti, kita masukkan satu garis yang
merepresentasikan target penjualan per bulan di grafik garis yang sudah ada.
1. Pilih grafik. Pada tab Design pilih Select Data.
Akan terbuka jendela Select Data Source.
Fungsi ini memungkinkan anda memasukkan
atau menghilangkan data di diagram, kapan
saja.
2. Sekarang masukkan secara manual rangkaian data
baru ke dalam slide. Klik tombol Add. Akan terbuka
jendela Edit Series.
3. Ketik pada kotak Series Name “Target”, kemudian
pada kotak Series Values ketikkan
={175000;175000;175000;175000;175000;175000;
175000;175000;175000;175000;175000;175000}
(175000 diketikkan 12X). Klik OK.
4. Pindahkan posisi Target di atas rangkaian (series)
Sales. Pilih Target, kemudian klik tombol panah
ke atas. Klik OK. Akan membuat garis target
berada di belakang garis sales.
5. Untuk membuatnya tidak nampak terlalu mendominasi dan menonjol, kita akan coba
memodifikasi. Pilih diagram kemudian klik pada garisnya. Pada tab Format pilih Shape
Outline. Pertama-tama ubah garis dengan warna kuning. Kemudian ubah strip (dashes)
menjadi titik-titik (dots). Dan terakhir buat Weight 2½pt.
6. Untuk memastikan bahwa orang-orang membaca dokumen ini memahami garis tersebut, kita
bisa memasukkan keterangan di text box (kotak teks). Tempatkan text box di atas garis
samping kanan. Ketikkan “Target”. Pertebal tulisan dan ubah menjadi warna kuning. Buat text
box transparan dan hilangkan outline.
Halaman 5 dari 13
Nurjati Widodo, S.AP, M.AP
Selanjutnya kita format data berada di sisi kiri diagram. Cara tercepat dan paling mudah untuk
memformat data tersebut adalah dengan menggunakan fungsi Format as Table. Di tab Home, pilih
Format as Table. Kemudian pilih desain yang diinginkan. Cara yang lazim adalah dengan
memanipulasi properties data dan sel.
1. Pilih semua data numerik, B5:B16. Kemudian pada tab Home, ubah format Number menjadi
Currency. Hilangkan penempatan desimal dengan menggunakan tombol remove decimal.
2. Sebagaimana anda lihat, beberapa nama bulan tidak bisa menyesuaikan dengan lebar sel
standar. Maka kita perlu membuat kolom A lebih lebar. Cukup klik borrder antara A dan B
(pada bagian atas) dan geser ke kanan. Maka kolom akan bertambah lebar.
3. Judul Sales tidak berbaris dengan angka di bawahnya. Klik sel B4 dan pada tab Home klik
tombol Centre Alignment.
4. Sekarang kita siap memasukkan beberapa warna dan menyesuaikan huruf. Karena diagram
berwarna merah, maka kita juga akan menggunakan warna merah sebagai warna tema di sini.
Pilih sel A4:B4, klik panah drop down Fill Colour. Pilih Red Accent 2. Kemudian pertebal teks
dan ubah hurufnya menjadi putih.
5. Ulangi proses ini dan ubah warna di sel tersisa. alternatif warna perlu kita berikan untuk
memberi kesan perbedaan antar baris. Sebagai contoh, light dark light.
6. Pilih A4:B16. Sekarang kita berikan border (garis). Pada tab Home, pilih panah drop down All
Borders. Klik More Borders. Jendela Format Cells akan terbuka. Di sini anda dapat
memanipulasi warna, ketebalan (Weights) dan model baris di border. Rubah warna menjadi
merah dan buat garisnya lebih tebal. Kemudian klik outline dan insiden untuk memberlakukan
perubahan.
Halaman 6 dari 13
Nurjati Widodo, S.AP, M.AP
Pada paruh kedua laporan ini, kita perlu menampilkan 3 grafik lainnya. Dan menyelesaikan desain
kita sebagai laporan. Perlu diingat mempertimbangkan desain anda ketika mencetak.
Pada bagian sebelumnya, anda menggunakan rumus SUMIFS untuk mendapatkan total per bulan
per daerah pemasaran. Dengan data ini (tertampil di bawah) kita akan membuat kolom grafik 3D.
1. Pilih hanya kolom yang berisi angka total pada tiap daerah pemasaran (seperti terlihat di
atas). Untuk menyorot kolom-kolom tertentu, tekan tombol ctrl sambil menyeleksi kolom
tertentu menggunakan mouse. Kemudian, pada tab insert pilih Column. Kemudian pilih 3D
Clustered Column Graph.
Halaman 7 dari 13
Nurjati Widodo, S.AP, M.AP
2. Kemudian klik kanan diagram, dan pilih Move Chart. Pilih kotak Object In dan pilih sheet
Regional Performance. Cara ini akan memindah diagram ke lembar laporan kita.
Halaman 8 dari 13
Nurjati Widodo, S.AP, M.AP
Lesson 5. Skenario Analisis
Sebagai seorang profesional di banyak kesempatan, kita perlu memperhitungkan berbagai macam
skenario untuk mengidentifikasi peluang dan resiko. Perhitungan ini cukup sulit dan cukup
menghabiskan waktu. Dengan Excel 2010, kita bisa mempercepat proses dengan menggunakan
tool yang akan kita pelajar berikut ini untuk membantu kita.
1. Fungsi Goal Seeking (mencari tujuan)
Ini merupakan fungsi sederhana yang bisa membantu anda mencari jawaban melalui
perhitungan kemungkinan. Sebagai contoh, a+1=10 kemudian kita bisa menyimpulkan bahwa
“a” adalah 9. Pada dasarnya, goal seeker mencari jawaban dengan tes multiple possibilities
(beberapa kemungkinan). Fungsi yang sangat bagus dan terbatas hanya untuk 1 variabel.
Kita gunakan informasi dari bab sebelumnya untuk membuat
pernyataan sederhana tentang untung dan rugi dan bekerja
dengan beberapa kemungkinan.
1) Buka file Raw.xlsx. Kemudian klik ke dalam worksheet
Scenario.
2) Buat copy dari sheet tersebut, dan namai BEP.
BEP (Break Event Point/titik impas) adalah suatu keadaan di mana suatu organisasi tidak
mendapat untung maupun rugi/impas. Titik ini merupakan titik minimal yang mana penjualan
perusahaan harus dicapai. Pertanyaannya adalah berapa banyak penjualan yang kita
butuhkan untuk mencapai BEP?
3) Pada tab Data pilih What If Analysis. Kemudian pilih Goal Seek. Akan terbuka jendela
Goal Seek.
4) Ada tiga variabel di jendela ini. Set Cell adalah sel di mana kita ingin mencapai hasil dari
To Value. Jika kita ingin Profit (Set Cell) kita menjadi 0 (to value), kita masukkan data
yang sesuai. Sel yang ingin kita ubah adalah nilai Sales USD. Sehingga kita tahu berapa
penjualan minimal yang kita perlukan untuk mencapai BEP.
5) Atur kotak By Changing Cell ke sel Sales USD (D18). Klik OK. Sistem akan mulai
menghitung dan memberi anda sebuah jawaban.
Halaman 9 dari 13
Nurjati Widodo, S.AP, M.AP
6) Jika anda klik OK, perubahan akan menjadi permanen. Jika anda klik cancel, maka akan
kembali ke hasil semula.
Dengan menggunakan fungsi ini, kita coba menjawab pertanyaan yang lebih rumit. Jika kita
punya investasi Rp5.000.000.000 ke perusahaan ini. Berapa tahun kita bisa mendapat balik
modal? Jika batas peningkatan untung bersih sebesar 10%, berapa lama modal kita balik?
Jika kita ingin mencapai 3 tahun balik modal, berapa banyak margin yang kita perlukan untuk
mencapainya?
Untuk memulai kita perlu memasukkan garis tambahan dan rumus ke Profit & Lost.
1) Ketik Margin, Capital dan ROI di bawah Profit. ROI adalah Return Of Investment.
2) Rumus untuk margin cukup sederhana, Margin=(Profit/Sales)%. Maka ketik rumus
=D18/D5. Kemudian rubah format sel menjadi persen dengan menekan tombol % pada
tab Home.
3) Kemudian ketik ke bagian nilai Capital, 5.000.000.000
4) Rumus untuk ROI adalah ROI=Capital/Profit. Ketik rumus =D20/D18.
Dari hasil di atas kita dapat menjawab pertanyaan bagian pertama. Dengan kondisi saat ini,
pengembalian modal selama 5,91 tahun. Kita gunakan tool yang sama untuk menghitung jika
keuntungan bersih sebesar 10%, berapa lama waktu yang diperlukan untuk kembali modal.
Untuk menjawabnya kita perlu mengubah variabel COGS. Karena hal ini berkaitan dengan
harga jual kepada pelanggan.
Halaman 10 dari 13
Nurjati Widodo, S.AP, M.AP
1) Pada tab data, pilih What if Analysis. Kemudian pilih Goal Seek, dan akan membuka
jendela Goal Seek.
2) Set Cell kita isi dengan Margin (D19) dan To Value diisi 10%. Kemudian isi By Changing
Cells dengan COGS Percentage (B9). Klik OK. Sistem akan mulai menghitung dan
memberi sebuah jawaban.
Untuk mencapai Margin 10%, persentase COGS harus sebesar 75%. Dari hasil ini, bisa kita
lihat bahwa perkiraan pengembalian modal adalah 2,96 tahun.
2. Skenario Manajer
Skenario Manajer pada dasarnya memungkinkan kita membuat perubahan terhadap sebuah
variabel dalam pekerjaan kita untuk melihat berapa besar outcome (hasil) yang diperoleh.
Untuk menampilkannya kita jawab pertanyaan ini. Target penjualan perusahaan sebesar
USD3.000.000. Tunjukkan pengaruh laba dalam pencapaian target dalam interval 20% mulai
dari 40%.
1) Pada tab Data pilih What if Analysis. Kemudian pilih Scenario Manager. Akan terbuka
jendela Scenario Manager.
2) Klik Add. Akan terbuka jendela Add Scenario. Beri nama Scenario 100%. Kita ingin
mengubah Sales USD yang ada di D4. Klik OK. Akan membuka jendela Scenario Value.
Masukkan 100% dari target, yakni 3.000.000.
3) Ulangi langkah di atas untuk 80%, 60%, dan terakhir 40%. Yang mana nilai-nilai yang
berkaitan, berubah secara berurutan.
Halaman 11 dari 13
Nurjati Widodo, S.AP, M.AP
Halaman 12 dari 13
Nurjati Widodo, S.AP, M.AP
4) Sekali menyelesaikan perhitungan ini, anda bisa menekan tombol Summary. Akan terbuka
jendela Scenario Summary. Pilih sel yang ingin anda display di bagian summary,
contohnya, Profit (D18), Margin (D19), dan ROI (D21), jangan lupa pisahkan
menggunakan titik koma (;). Kemudian klik OK. Langkah ini akan menghasilkan sebuah
worksheet baru yang bernama Scenario Summary.
5) Ubah nama sel $D$4 menjadi Sales, $D$18 menjadi Profit, $D$19 menjadi Margin, dan
$D$21 menjadi ROI. Hasilnya akan anda lihat pada ilustrasi gambar terakhir.
Halaman 13 dari 13
Nurjati Widodo, S.AP, M.AP
Jika kita menganalisa data di atas, kita bisa melihat bahwa kita perlu mencapai di atas 40%
untuk memperoleh keuntungan. Jika kita mencapai 40% dari target, kita masih rugi.