Tips Microsoft Excel : Membuat Aplikasi Pencatatan Usaha Dagang /Toko
1. Gambaran Umum Hhhmmm udah lama ga update maklum namanya juga manusia kadang semangat menulis kadang juga malas hehehe, tapi Alhamdulillah para sahabat tidak pernah malas untuk berkunjung ke Blognya Omiyan. Kali ini saya akan membuat sebuah tulisan tentang Tips Excel didasarkan atas banyaknya permintaan melalui email 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.
2. Membuat Tabel Kode Barang Nah 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
3. Membuat Tabel Pembelian Langkah selanjutnya adalah membuat 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 Nam a 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.
4. Membuat Tabel Penjualan Barang 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.
5. Membuat Tabel Persediaan Barang Sekarang kita berlanjut membuat Tabel Stok atau Tabel Persediaan Barang, seperti apa bentuknya silakan perhatikan gambar berikut ini :
! !"#!$!%& ' ) *
,
+
*
*
(! #(!%"
$
*
!$! #!$!%"
01
2 01 3 ! ! #! !%" 3 3 !(! #!(!%" - . 4!" 3
$ ) *
- .!
/ %"
01
, 01
(
.5*
%"
01
3 !$! #!$!%"
- .!
Notifikasi
6 8
7
-. 1
8
7%6
-.
8
9:
-.
6. Dashboard Penjualan ; )5 !
01
!
*< = -
"
> - ) -* ? 1
@
Cell C6 ,
2
01
3 !/ ! #!/ !%" 3
* < = - . 1 -1
01
) -* ? 1
3 ! ! #! !%" ! A @
Cell D6 , 01
2 01 3 ! ! #! !%" 3 3 ! ! #! !%" ; )5 ! A ? ) -)
> -?
01
# (BB B $A=!$!%C 6 $A=!$!%C
?
1
- 5
?
> -) - 51
$ --
1 *) #
$ %
$
& !
