Rahasia Dahsyat Microsoft Excel Membuat Inventory Penjualan



Usaha atau bahasa kerennya adalah bisnis adalah dambaan setiap manusia untuk memilikinya apalagi hasil dari usaha itu pasti semua  menginginkan hasil yang didapatkan dari apa yang kita usahakan. Tetapi yang paling penting adalah ketika memulai sebuah usaha maka niatkan untuk ibadah  hanya untuk yang Maha Kaya yaitu Allah Subhanahuwata'ala.

Usaha kita ingin melesat, perlu strategi dalam meningkatkan omset penjualan kita, tetapi kali ini saya tidak akan bahas strategi bisnis. Namun aplikasi dari strategi bisnis atau bisa dibilang salah satu faktor strategi sebuah bisnis dalam rangka untuk menganalisa capaian bisnis yang kita lakukan.

Dalam dunia bisnis pasti kita akan selalu menghitung dan mencatat apa saja yang sudah terjual, berapa jumlah produk atau stok yang kita miliki, berapa modal untuk melakukan pembelian produk untuk kemudian kita jual kembali atau bahasa singkatnya MODAL yang akan kita keluarkan.

Nah semua itu dapat dilakukan oleh siapapun dengan syarat mengerti aplikasi Ms. Excel. Ya, Ms.Excel akan membantu anda dalam membuat report (laporan) keuangan sebagai dokumentasi catatan usaha yang kita jalani, dari catatan usaha ini kita dapat melihat sejauh mana usaha yang kita lakukan dalam menjual sebuah produk, sejauh mana keuntungan yang kita dapatkan dan lain-lain. Bermula dari sini kita dapat melakukan evaluasi terhadap usaha yang kita lakukan, apabila usaha yang kita lakukan merugi strategi apa agar menjadi meningkat, apabila untung bagaimana mempertahankannya atau bahkan meningkatkan lagi keuntungannya dan seterusnya.

Baik mari kita mulai bagaimana kita membuat laporan keuangan usaha kita dengan menggunakan excel:

Pertama kali yang harus anda lakukan adalah membuat tabel kode barang, pada tabel ini kita membuat tiga kolom diantaranya, No, Kode Barang dan Nama Barang.

Untuk memasukkan kode barang bisa dilakukan secara manual atau otomatis, tetapi saran saya buat secara otomatis, tentunya buat format kode barang sesuai dengan keinginan. Maka untuk otomatisasi gunakan rumus:

=IF(ISBLANK(C4);"";"A1010")

Pada rumus ini saya memulai kode barang dengan kode A1010 (buat sesuai selera ya). Kemudian pada cell berikutnya buat rumus seperti ini:

=IF(ISBLANK(C5);"";(LEFT(B4;1)&RIGHT(B4;4)+1))

Selanjutnya adalah membuat tabel pembelian, pada tabel pembelian terdapat:
1. No
2. Tanggal
3. Kode Barang
4. Nama Barang
5. Ukuran dengan sub satuan ukurannya dan jumlah
6. HPP atau harga satuan
7.  Modal

Darai 7 nama diatas saya akan bahas nama barang terlebih dahulu, karena 1-3 diisi secara manual saja. Pada nama barang kita akan memanggil nya dari tabel kode barang caranya adalah dengan menggunakan rumus sebagai berikut:

=IF(ISBLANK(C5);"";VLOOKUP(C5;TblKodeBarang!$B$4:$C$13;2;FALSE))

disini saya menggunakan VLOOKUP untuk membaca nama barang berdasarkan kode barang. Berikutnya ukuran diisi secara manual berapa jumalahnya, untuk jumlah (total dari ukuran 1 liter dan 5 liter misalnya) gunakanlah rumus: =SUM(E5:F5).

Untuk HPP adalah Harga Pokok Penjualan  diinput seca. ra manual, logika nya sebenarnya HPP didapat dari MODAL dibagi dengan jumlah pembelian barang.

Selanjutnya adalah membuat tabel penjualan:

1. No
2. Tanggal
3. Kode Barang
4. Nama Barang
5. Ukuran
6. Jumalah barang terjual
7. Harga Jual
8. Total Penjualan
9. Potongan Harga
10. HPP
11. Keuntungan
12. Bulan Penjualan

pada tabel penjualan ini yang paling penting adalah pada nama barang kita gunakan rumus seperti yang diatas:

=IF(ISBLANK(C5);"";VLOOKUP(C5;TblKodeBarang!$B$4:$C$13;2;FALSE))

Kemudian total penjualan adalah hasil dari jumlah barang terjual * harga jual

Untuk mendapatkan nilai HPP di tabel penjualan kita gunakan rumus VLOOKUP:

=IF(ISBLANK(C5);0;(VLOOKUP(C5;TblPembelian!$C$5:$I$14;6;FALSE))*F5)

pada rumus diatas kita membaca tabel pembelian, pada tabel ini dapat dipahami bahwa pada index row 6 tabel pembelian adalah HPP.

HPP pada tabel penjualan didapat dari Jumlah barang terjual * HPP pada tabel pembelian.

Keuntungan didapat dari rumus pengurangan:

=H5-I5-J5

Bulan pejualan gunakan rumus:

=MONTH (B5), membaca cell tanggal.

Selanjutnya adalah membuat Tabel Stok atau tabel persediaan barang, pada tabel ini kita membuat item-item yang dibutuhkan diantranya adalah No, Kode Barang, Persediaan Awal, Penjualan, Persediaan akhir, Keterangan.

Pada item persediaan awal kita membaca stok dari tabel pembelian dengan rumus:

=SUMIFS(TblPembelian!E$5:E$14;TblPembelian!$C$5:$C$14;TblStok!$B5)

Pada item penjualan kita akan membaca dari tabel penjualan dan tabel stok dengan rumus:

=SUMIFS('Tbl Penjualan'!$F$5:$F$14;'Tbl Penjualan'!$E$5:$E$14;TblStok!G$4;'Tbl Penjualan'!$C$5:$C$14;TblStok!$B5)

dan untuk keterangan kita menggunakan rumus:

=IF(L5=0;"";IF(L5<5;"Stok Kurang";IF(L5<10;"Stok Sedang";IF(L5>9;"Stok Aman"))))

pada rumus diatas kita membuat parameter stok barang, jika jumlah barang kurang dari 5 maka akan muncul secara otomatis "Stok Kurang". Jika kurang dari 10 maka "Stok Sedang". Jika lebih dari 9 maka "Stok Aman"

Berikutnya kita buat Tabel Dashboard yang fungsinya untuk monitoring omset dari penjualan.pada omset ini kita membaca tabel penjualan dengan rumus:

Item Penjualan

=SUMIFS('Tbl Penjualan'!$H$5:$H$14;'Tbl Penjualan'!$L$5:$L$14;$A6)

Item Keuntungan

=SUMIFS('Tbl Penjualan'!$K$5:$K$14;'Tbl Penjualan'!$L$5:$L$14;Dashboard!$A6)

Item Persentase

=IF(ISERROR(C6/$C$18);0;C6/$C$18)


Selesai, sampai disini pasti pada bingung, karena saya tidak mencantumkan gambar. Iya hari ini saya tidak bisa mengupload gambar dikarenakan agak teknis. Sebagai gantinya, bagi yang berminat contoh excelnya bisa saya kirim via email GRATIS, silakan bagi yang berminat, komen dibawah cantumkan emailnya.

Demikian pembahasan  kali ini, thx sudah berkunjung dan membaca tulisan ini, mohon masukannya jika ada yang kurang dan mohon gunakan informasi ini untuk kebaikan, silakan share seluas-luasnya.

Salam Ngeblog...!!

2 komentar

mau nanya kalo yg =IF(ISBLANK(C4);””;”A1010″) tanda "" gabisa digunakan lalu sebagai gantinya bagaimana? saya pengguna macbook. thankyou.

O...maksudnya itu 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.


EmoticonEmoticon