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.