Pada kesempatan kali ini saya akan memberikan sebuah tutorial sederhana tentang cara membuat aplikasi penjualan menggunakan Microsoft Excel. Aplikasi penjulan ini juga saya gunakan pada usaha yang sedang saya geluti yakni penjualan ATK dan Accessories komputer.
Tujuan saya membuat Aplikasi Penjualan dari Microsoft Excel ini adalah untuk mempermudah memonitoring keluar masuknya stock secara real time sehingga tidak perlu lagi repot-repot merekap stok setiap hari karena aplikasi penjualan ini sudah bisa mengurangi stock secara otomatis.
Baca Juga :
Perlu anda ketahui bahwa untuk membuat aplikasi penjualan menggunakan Excel ini, saya hanya memanfaatkan rumus Excel sederhana seperti VLOOKUP, SUMIF, SUM dan rumus sederhana lainnya. Dan saya yakin jika dipelajari baik-baik tutorial ini, maka anda juga bisa membuat aplikasi penjualan ini dengan mudah.
Karena sifatnya sedehana, maka aplikasi penjualan ini hanya terdiri tiga tabel saja yakni tabel PEMBELIAN, STOCK dan PENJUALAN. Dan masing-masing tabel berada pada sheet yang berbeda, jadi kita juga memerlukan tiga buah sheet.
1. Membuat Tabel Pembelian
Bentuk tabelnya bisa anda lihat pada gambar di bawah. Pada tabel pembelian ini anda akan menginputkan data berupa Tanggal Pembelian, Kode Barang, Nama Barang, Jumlah, Satuan, Harga dan Supplier.
Tabel ini diisi setiap kali melakukan pembelian barang dan secara otomatis jumlah barang yang anda inputkan akan bertambah ke Tabel Stock.
2. Membuat Tabel Stock Barang
Model Tabelnya bisa dilihat pada gambar di bawah. Data yang perlu diisi pada tabel ini berupa Kode Barang dan Harga Pokok saja, sebenarnya bisa saja dibuat Harga Pokok muncul secara otomatis ketika diketikkan Kode Barang. Namun untuk mengantisipasi terjadinya perubahan harga barang maka Harga Pokok sebaiknya diketik secara manual mengikuti harga pemebelian terbaru pada Tabel Pembelian.
3. Membuat Tabel Penjualan
Buat seperti gambar di bawah. Data yang perlu anda isi pada tabel Penjualan adalah Tanggal, Kode Barang, Jml dan Harga Jual. Sedangkan data yang lainnya akan terisi sendiri sesuai dengan rumus yang telah diberikan. Pada Tabel Penjualan ini anda bisa melihat keutungan per item barang pada kolom Laba, selain itu juga anda bisa memfilter Total Penjualan dan Laba berdasarkan tanggal yang diinginkan.
Untuk mempermudah proses penyortiran data, buatkan tombol filter pada Nama Field masing-masing tabel. Caranya : blok Header Tabel lalu pilih Data - Filter, maka masing-masing kolom akan memiliki tombol berupa segi tiga menghadap ke bawah yang berfungsi untuk menyortir data.
1. Tabel Pembelian
Blok seluruh isi tabel mulai dari kolom Kode Barang sampai dengan kolom Supplier kemudian klik pada Name Box dan berikan nama menjadi DATA_PEMBELIAN. Ingat yang diblok hanya isinya saja bukan termasuk Headernya
Masih pada Tabel Pembelian, blok isi pada kolom Kode Barang kemudian berikan nama Rangenya menjadi BARANG
Berikutnya blok pada kolom Jml lalu berikan nama menjadi JUMLAH
Jika sudah selesai, kini kita lanjut ke Tabel Stock ...
2. Tabel Stock
Blok seluruh isi tabel mulai dari kolom Kode Barang sampai dengan kolom Stock Akhir kemudian klik pada Name Box dan berikan nama menjadi STOCK
3. Tabel Penjualan
Blok kolom kode barang kemudian berikan nama pada Name Box menjadi BARANG2
Blok kolom JML kemudian berikan nama pada Name Box menjadi JUMLAH2
Ok, proses pemberian nama Range sudah selesai, kini tinggal memasukkan rumus pada kolom tertentu di masing-masing tabel.
1. Tabel Pembelian
Kita mulai dari Tabel Pembelian, di sini yang perlu diberikan rumus hanya pada kolom Total saja, rumusnya adalah =E4*G4 (Jml x Harga Pokok).
Setelah rumusnya Selesai, masukkan data-data barang yang telah anda beli pada Tabel Pembelian
2. Tabel Stock
Pada kolom Nama Barang ketikkan rumus berikut =IF(B4="";"";VLOOKUP(B4;DATA_PEMBELIAN;2;FALSE)) fungsinya untuk memanggil nama barang dari Tabel Pembelian sesuai dengan Kode Barang.
Pada kolom Tambah Stock isikan rumus berikut =SUMIF(BARANG;B4;JUMLAH) ini berfungsi untuk memasukkan data barang yang baru masuk pada Tabel Pembelian.
Sedangkan pada kolom Stock Keluar rumusnya seperti ini =SUMIF(BARANG2;B4;JUMLAH2) dan fungsinya adalah untuk memasukkan data barang yang baru saja keluar pada Tabel Penjualan.
Dan pada kolom Stock Akhir berikan rumus seperti ini =(E4+F4)-G4
3. Tabel Penjualan
Pada tabel penjualan, ada empat kolom yang akan diberikan rumus yakni Nama Barang, Harga Pokok, Total dan Laba. Berikut adalah rumus untuk masing-masing kolom tersebut.
Nama Barang : =IF(B4="";"";VLOOKUP(B4;STOCK;2;FALSE))
Harga Pokok : =IF(B4="";"";VLOOKUP(B4;STOCK;3;FALSE))
Total : =G4*D4
Laba : =H4-(F4*D4)
Setelah semua rumus berhasil diisi pada kolom yang sesuai, berati anda telah berhasil membuat aplikasi penjualan pada Excel. Sekarang tinggal mengisikan data pada tabel Pembelian dan Stock.
Ketika Anda menginputkan barang pada Tabel Pembelian maka secara otomatis Stock barang akan bertambah. Demikian juga ketika melakukan penjualan, maka Stock barang akan berkurang secara otomatis.
Itu saja dulu cara membuat Aplikasi Penjualan menggunakan Microsoft Excel, selamat mencoba dan semoga ada manfaatnya bagi kita semua. Amin ....
Tujuan saya membuat Aplikasi Penjualan dari Microsoft Excel ini adalah untuk mempermudah memonitoring keluar masuknya stock secara real time sehingga tidak perlu lagi repot-repot merekap stok setiap hari karena aplikasi penjualan ini sudah bisa mengurangi stock secara otomatis.
Baca Juga :
- Membuat Dropdown List pada Excel
- Cara membuat Header Tampil berulang pada tiap halaman Excel
- Menggunakan Rumus Left, Right dan MID
Perlu anda ketahui bahwa untuk membuat aplikasi penjualan menggunakan Excel ini, saya hanya memanfaatkan rumus Excel sederhana seperti VLOOKUP, SUMIF, SUM dan rumus sederhana lainnya. Dan saya yakin jika dipelajari baik-baik tutorial ini, maka anda juga bisa membuat aplikasi penjualan ini dengan mudah.
Karena sifatnya sedehana, maka aplikasi penjualan ini hanya terdiri tiga tabel saja yakni tabel PEMBELIAN, STOCK dan PENJUALAN. Dan masing-masing tabel berada pada sheet yang berbeda, jadi kita juga memerlukan tiga buah sheet.
LANGKAH - LANGKAH MEMBUAT APLIKASI PENJUALAN MENGGUNAKAN EXCEL
1. Membuat Tabel Pembelian
Bentuk tabelnya bisa anda lihat pada gambar di bawah. Pada tabel pembelian ini anda akan menginputkan data berupa Tanggal Pembelian, Kode Barang, Nama Barang, Jumlah, Satuan, Harga dan Supplier.
Tabel ini diisi setiap kali melakukan pembelian barang dan secara otomatis jumlah barang yang anda inputkan akan bertambah ke Tabel Stock.
2. Membuat Tabel Stock Barang
Model Tabelnya bisa dilihat pada gambar di bawah. Data yang perlu diisi pada tabel ini berupa Kode Barang dan Harga Pokok saja, sebenarnya bisa saja dibuat Harga Pokok muncul secara otomatis ketika diketikkan Kode Barang. Namun untuk mengantisipasi terjadinya perubahan harga barang maka Harga Pokok sebaiknya diketik secara manual mengikuti harga pemebelian terbaru pada Tabel Pembelian.
3. Membuat Tabel Penjualan
Buat seperti gambar di bawah. Data yang perlu anda isi pada tabel Penjualan adalah Tanggal, Kode Barang, Jml dan Harga Jual. Sedangkan data yang lainnya akan terisi sendiri sesuai dengan rumus yang telah diberikan. Pada Tabel Penjualan ini anda bisa melihat keutungan per item barang pada kolom Laba, selain itu juga anda bisa memfilter Total Penjualan dan Laba berdasarkan tanggal yang diinginkan.
Untuk mempermudah proses penyortiran data, buatkan tombol filter pada Nama Field masing-masing tabel. Caranya : blok Header Tabel lalu pilih Data - Filter, maka masing-masing kolom akan memiliki tombol berupa segi tiga menghadap ke bawah yang berfungsi untuk menyortir data.
MEMBERIKAN NAMA RANGE PADA TABEL
Untuk mempermudah penulisan rumus, sebaiknya berikan nama pada range tertentu yang menjadi target penulisan rumus. Berikut adalah beberapa range yang harus diberikan nama.1. Tabel Pembelian
Blok seluruh isi tabel mulai dari kolom Kode Barang sampai dengan kolom Supplier kemudian klik pada Name Box dan berikan nama menjadi DATA_PEMBELIAN. Ingat yang diblok hanya isinya saja bukan termasuk Headernya
Masih pada Tabel Pembelian, blok isi pada kolom Kode Barang kemudian berikan nama Rangenya menjadi BARANG
Berikutnya blok pada kolom Jml lalu berikan nama menjadi JUMLAH
Jika sudah selesai, kini kita lanjut ke Tabel Stock ...
2. Tabel Stock
Blok seluruh isi tabel mulai dari kolom Kode Barang sampai dengan kolom Stock Akhir kemudian klik pada Name Box dan berikan nama menjadi STOCK
3. Tabel Penjualan
Blok kolom kode barang kemudian berikan nama pada Name Box menjadi BARANG2
Blok kolom JML kemudian berikan nama pada Name Box menjadi JUMLAH2
Ok, proses pemberian nama Range sudah selesai, kini tinggal memasukkan rumus pada kolom tertentu di masing-masing tabel.
MEMASUKKAN RUMUS PADA APLIKASI PENJUALAN EXCEL
1. Tabel Pembelian
Kita mulai dari Tabel Pembelian, di sini yang perlu diberikan rumus hanya pada kolom Total saja, rumusnya adalah =E4*G4 (Jml x Harga Pokok).
Setelah rumusnya Selesai, masukkan data-data barang yang telah anda beli pada Tabel Pembelian
2. Tabel Stock
Pada kolom Nama Barang ketikkan rumus berikut =IF(B4="";"";VLOOKUP(B4;DATA_PEMBELIAN;2;FALSE)) fungsinya untuk memanggil nama barang dari Tabel Pembelian sesuai dengan Kode Barang.
Pada kolom Tambah Stock isikan rumus berikut =SUMIF(BARANG;B4;JUMLAH) ini berfungsi untuk memasukkan data barang yang baru masuk pada Tabel Pembelian.
Sedangkan pada kolom Stock Keluar rumusnya seperti ini =SUMIF(BARANG2;B4;JUMLAH2) dan fungsinya adalah untuk memasukkan data barang yang baru saja keluar pada Tabel Penjualan.
Dan pada kolom Stock Akhir berikan rumus seperti ini =(E4+F4)-G4
3. Tabel Penjualan
Pada tabel penjualan, ada empat kolom yang akan diberikan rumus yakni Nama Barang, Harga Pokok, Total dan Laba. Berikut adalah rumus untuk masing-masing kolom tersebut.
Nama Barang : =IF(B4="";"";VLOOKUP(B4;STOCK;2;FALSE))
Harga Pokok : =IF(B4="";"";VLOOKUP(B4;STOCK;3;FALSE))
Total : =G4*D4
Laba : =H4-(F4*D4)
Setelah semua rumus berhasil diisi pada kolom yang sesuai, berati anda telah berhasil membuat aplikasi penjualan pada Excel. Sekarang tinggal mengisikan data pada tabel Pembelian dan Stock.
Ketika Anda menginputkan barang pada Tabel Pembelian maka secara otomatis Stock barang akan bertambah. Demikian juga ketika melakukan penjualan, maka Stock barang akan berkurang secara otomatis.
Itu saja dulu cara membuat Aplikasi Penjualan menggunakan Microsoft Excel, selamat mencoba dan semoga ada manfaatnya bagi kita semua. Amin ....