Kali ini dzakie akan membuat sebuah tulisan tentang Tips microsoft Excel didasarkan berbagi tentang cara menggunakan microsoft excell yaitu membuat sebuah aplikasi yang sederhana untuk yang mempunyai toko atau
usaha yang melakukan Pencatatan atas setiap transaksi jual beli.
Yang akan kita buat nantinya adalah :
- Dashboard Penjualan
- Tabel Kode Barang
- Tabel Stok atau Persediaan Barang
- Tabel Pembelian Barang
- Tabel Penjualan Barang
Untuk mudah memahaminya silakan simak penjelasannya sebagai berikut :
Dashboard Penjualan, fungsinya untuk memantau perkembangan penjualan (omzet penjualan) dan keuntungan setiap bulannya yang disertai dengan grafik perkembangannya.
Tabel Kode Barang, berisi tentang kode-kode atas barang yang dibeli hal ini membantu dalam pengawasan barang di tabel persediaan barang.
Tabel Stok atau Persediaan Barang, berisi data persediaan awal barang ketika terjadi pembelian, data penjualan selama tahun berjalan dan data persediaan akhir dimana akan diketahui kondisi barang untuk terakhir kali setelah terjadinya transaksi penjualan.
Tabel Pembelian Barang, berisi tentang data-data pembelian barang.
Tabel Penjualan Barang, berisi tentang data-data penjualan sekaligus memonitor keuntungan yang didapatnya.
sudah paham kan, kalau sudah tak sabar sekarang silakan buka worksheet excelnya. Langkah pertama yang kita buat adalah dengan membuat Tabel Kode Barang.
Seperti yang sudah dijelaskan diatas, tujuan kita membuat Tabel Kode Barang (buat di sheet 1) adalah untuk lebih mudah memantau barang dengan diklasifikasikan dengan sebuah kode sehingga nantinya kode0kode tersebut akan berguna untuk tabel lainnya.
Perhatikan gambar dibawah ini :
Jika kita ingin Kode Barangnya muncul secara otomatis, maka rumus yang kita buat pertama kali di Cell B4 adalah sebagai berikut :
=IF(ISBLANK(C4);””;”A1010″)
Penjelasannya : Jika data yang ada di Cell C4 kosong maka kode barang juga kosong, jika terdapat data di Cell C4 maka Kode Barang yang muncul/dibuat adalah A1010.
Praktek : Buat rumus diatas di Cell B4 kemudian di Cell C4 masukkan data misalkan Kemeja Lengan Pendek
Selanjutnya di Cell B5 buatlah rumus sebagai berikut :
=IF(ISBLANK(C5);””;(LEFT(B4;1)&RIGHT(B4;4)+1))
Penjelasannya : Jika data sebelumnya di Cell B4 sudah muncul Kode Barang A1010 maka di cell bawah/selanjutnya adalah dengan menambahkan angka satu agar angka terakhir bisa berubah.
Praktek : Buat rumus diatas di Cell B5 kemudian di Cell C5 masukkan data misalkan Kemeja Lengan Panjang.
Untuk Cell selanjutnya cukup copy paste rumus di Cell B5 tersebut.
Catatan : jika rumus diatas dianggap ribet bisa kok dilakuin secara manual hehehe
Langkah selanjutnya adalah membaut Tabel Pembelian (buat di sheet 2), jadi dengan adanya tabel ini setiap pembelian barang nantinya dicatat ditabel ini sehingga akan memudahkan dalam pengawasan serta memudahkan untuk data yang ada ditabel lainnya.
Perhatikan gambar dibawah ini :
Untuk data pembelian sengaja saya urai berdasarkan ukuran, hal ini akan sangat membantu setiap penjual untuk memantau ukuran apa yang kondisinya harus segera dibeli.
Agar tidak sering terjadi penulisan Nama Barang maka sebaiknya kita membuat rumus Cell D5 sebagai berikut :
=IF(ISBLANK(C5);””;VLOOKUP(C5;TblKodeBarang!$B$4:$C$13;2;FALSE))
Penjelasannya : Jika data yang ada di Cell C5 kosong maka Nama Barang (Cell D5) juga kosong, jika terdapat data di Cell C5 maka Nama Barang akan muncul sesuai data yang ada di Tabel Kode Barang.
Praktek : Buat rumus diatas di Cell D5 kemudian di Cell C5 masukkan Kode Barang misalkan A1010.
Untuk penentuan HPP pastinya udah pada tahu semua sedangkan untuk Kolom Modal sengaja saya buat hanya sebagai pengawasan dan itu didapat dari hasil perkalian jumlah barang dengan HPP atau biasanya yang sering terjadi justru HPP itu dihasilkan dari jumlah modal yang sudah dikeluarkan dibagi jumlah barang.
Selanjutnya kita akan membuat Tabel Penjualan Barang (buat di sheet 3),
perhatikan gambar berikut ini :
Untuk Nama Barang (Cell D5) buatlah rumus sebagai berikut :
=IF(ISBLANK(C5);””;VLOOKUP(C5;TblKodeBarang!$B$4:$C$13;2;FALSE))
Untuk mendapatkan nilai Total Penjualan (Cell H5) didapatkan dari hasil perkalian Jumlah Ukuran Terjual dengan Harga Jual.
Untuk mendapatkan nilai HPP (Cell J5), maka rumusnya adalah :
=IF(ISBLANK(C5);0;(VLOOKUP(C5;TblPembelian!$C$5:$L$14;9;FALSE))*F5)
Untuk mendapatkan nilai Keuntungan (Cell K5), didapatkan dengan rumus :
=H5-I5-J5
Untuk mengetahui Bulan (Cell L5) terjadinya transaksi penjualan maka rumusnya adalah :
=MONTH(B5)
Jangan lupa rumus-rumus tersebut copy paste ke cell selanjutnya.
Sekarang kita berlanjut membuat Tabel Stok atau Tabel Persediaan Barang, seperti apa bentuknya silakan perhatikan gambar berikut ini :
Selanjutnya yang perlu kita lakukan adalah sebagai berikut :
Memunculkan Nama Barang (Cell C5) secara otomatis, maka rumusnya adalah :
=IF(ISBLANK(B5);””;VLOOKUP(B5;TblKodeBarang!$B$4:$C$13;2;FALSE))
Memunculkan Data Persediaan Awal (Cell D5) yang berasal dari Tabel Pembelian, maka rumusnya adalah :
=SUMIFS(TblPembelian!E$5:E$14;TblPembelian!$C$5:$C$14;TblStok!$B5)
Harap perhatikan tanda $.
Selanjutnya rumus tersebut di copy s.d Cell H14.
Untuk memunculkan Data Penjualan (Cell J5) yang berasal dari Tabel Penjualan Barang, maka rumusnya adalah :
=SUMIFS(‘Tbl Penjualan’!$F$5:$F$14;’Tbl Penjualan’!$E$5:$E$14;TblStok!J$4;’Tbl Penjualan’!$C$5:$C$14;TblStok!$B5)
Harap perhatikan tanda $.
Selanjutnya rumus tersebut di copy s.d Cell N14.
Untuk memunculkan Data Persediaan Akhir sangatlah mudah karena didapatkan dari data Pembelian dikurangi Data Penjualan.
Adapun kolom keterangan (Cell V5) sebagai bentuk Notifikasi jika persediaan dirasa sudah sangat sedikit maka secara otomatis akan ada pemberitahuan. Rumus yang kita buat adalah :
=IF(U5=0;””;IF(U5<5;”Stok Kurang“;IF(U5<10;”Stok Sedang“;IF(U5>9;”Stok Aman“))))
Langkah terakhir yang kita lakukan adalah dengan membuat Dashboard Penjualan, dashboard ini lebih bersifat memberikan informasi kondisi atau perkembangan omzet penjualan setiap bulannya. Perhatikan gambar berikut ini :
Untuk mendapatkan info/data omzet setiap bulannya maka rumus yang kita buat di Cell C6 adalah :
=SUMIFS(‘Tbl Penjualan’!$H$5:$H$14;’Tbl Penjualan’!$L$5:$L$14;$A6)
Untuk mendapatkan info/data keuntungan setiap bulannya maka rumus yang kita buat di Cell D6 adalah :
=SUMIFS(‘Tbl Penjualan’!$K$5:$K$14;’Tbl Penjualan’!$L$5:$L$14;Dashboard!$A6)
Untuk mengetahui persentase omzet penjualan perbulan terhadap omzet setahun (Cell E6) maka rumusnya adalah :
=IF(ISERROR(C6/$C$18);0;C6/$C$18)
Aplikasi ini sering digunakan oleh penulis yang kebetulan juga mempunyai
usaha Online, semoga saja dengan bisa menyimak, mempelajari dan memahaminya maka aplikasi ini sedikit bisa membantu para pengunjung webnya dzakie yang kebetulan punya usaha dan kebingungan mencari aplikasi sejenis yang terkadang dirasa mahal dan harus mengeluarkan biaya kalo kita membelinya
semoga artikel ini dapat membantu anda yang sedang membutuhkan
