{"id":5,"date":"2021-02-20T08:54:00","date_gmt":"2021-02-20T00:54:00","guid":{"rendered":"https:\/\/indonesiangems.com\/blog\/?p=5"},"modified":"2021-03-19T06:51:49","modified_gmt":"2021-03-18T22:51:49","slug":"membuat-laporan-hutang-piutang-dan-stok-di-excel","status":"publish","type":"post","link":"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/","title":{"rendered":"Membuat laporan hutang piutang dan stok di Excel"},"content":{"rendered":"<p class=\"last-updated\">Last updated on March 19th, 2021 at 06:51 am<\/p>\n<!--more-->\n\n\n\n<div class=\"wp-block-buttons is-layout-flex wp-block-buttons-is-layout-flex\">\n<div class=\"wp-block-button\"><a class=\"wp-block-button__link\" href=\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/\">ganti ke bahasa inggris<\/a><\/div>\n<\/div>\n\n\n\n<p>Contoh kasus:<br>Pak Adi mempunyai toko kecil, UD. MAJU JAYA. Selama ini dia mencatat di buku mengenai hutang-piutang dan stok tokonya tsb. Seiring dengan usahanya yang makin berkembang, ada tiga hal yang menyebabkan pak Adi ingin membuat laporan hutang piutang dan stok di <a href=\"https:\/\/docs.microsoft.com\/en-us\/office\/vba\/api\/overview\/excel\" target=\"_blank\" rel=\"noreferrer noopener\">Excel<\/a>.<\/p>\n\n\n\n<p>Yang pertama, dia tidak usah lagi menghitung secara manual pakai kalkulator. Kedua, dia bisa melihat laporan hutang piutang dan stok toko di aplikasi Excel secara cepat. Dan yang ketiga, dia bisa mencetak nota\/invoice saat terjadi penjualan\/pembelian.<\/p>\n\n\n\n<p>Akhirnya pak Adi membeli sebuah komputer, dan pak Adi menyiapkan segala data data yang dia perlukan dalam membuat laporan hutang piutang dan stok di <a href=\"https:\/\/en.wikipedia.org\/wiki\/Microsoft_Excel\" target=\"_blank\" rel=\"noreferrer noopener\">aplikasi Microsoft Excel<\/a>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Menyiapkan data:<\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\"><li>customer\/supplier (PD\/HD)<\/li><li>barang \/ item yang diperjual-beli-kan<\/li><li>jumlah barang barang yang dijual (stok awal)<\/li><li>PD\/HD yang masih ada hutang piutang-nya (PDHD awal)<\/li><\/ul>\n\n\n\n<p>Setelah point point diatas sudah disiapkan, Pak Adi mulai melakukan persiapan di file Excel.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>1. Membuat daftar Customer, Supplier dan Item.<\/strong><\/em><\/h4>\n\n\n\n<p>Menamakan halaman : double-click halaman &#8220;Sheet1&#8221;, lalu ganti namanya menjadi &#8220;TABEL&#8221;. Double-click halaman &#8220;Sheet2&#8221;, lalu ganti namanya menjadi &#8220;DATA&#8221;.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"491\" height=\"125\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-00_naming-page-in-excel.gif\" alt=\"menamakan halaman untuk persiapan membuat laporan hutang piutang di excel\" class=\"wp-image-1294\"\/><figcaption><em>Menamakan halaman di Excel<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p>Dengan membuat halaman TABEL lalu membuat daftar nama nama Customer, Supplier dan Item, ini akan memudahkan peng-inputan data karena daftar nama nama nya sudah tersedia.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"738\" height=\"433\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-01_customer-supplier-product-name-list.png\" alt=\"Daftar nama customer, supplier dan produk.\" class=\"wp-image-1295\"\/><figcaption><em>Contoh daftar nama nama piutang-hutang dagang dan item<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>2. Membuat tabel transaksi<\/strong><\/em><\/h4>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"999\" height=\"152\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-02_table-header-for-buy-and-sell-data.png\" alt=\"header tabel data untuk laporan hutang piutang di Excel\" class=\"wp-image-1296\"\/><figcaption><em>Header tabel transaksi penjualan pembelian dan stok <\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p>Selanjutnya pak Adi membuat table transaksi di halaman &#8220;DATA&#8221;. Adapun header tabel yang pak Adi buat adalah sbb:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>TGL = tanggal terjadi-nya transaksi jual, beli dan bayar<\/li><li>NOTA = nomor invoice (nota\/kwitansi)<\/li><li>NAMA = nama PiutangDagang \/ nama HutangDagang<\/li><li>BUKU = stok \/ pembayaran<\/li><li>POS = PD \/ HD \/ Stok Awal<\/li><li>ITEM = nama item<\/li><li>QT = kwantitas item<\/li><li>HARGA = harga item<\/li><li>SUM = perkalian QT * HARGA (plus\/minus)<\/li><li>QTY = kwantitas item (plus\/minus)<\/li><li>D\/K = Debet \/ Kredit<\/li><li>BlnThn = kategori tahun dan bulan apa<\/li><li>LUNAS = di isi apabila nota sudah lunas<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>3. Mengisi halaman DATA dengan stok awal <\/strong><\/em><\/h4>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1011\" height=\"316\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-03_product-opening-balance-example.png\" alt=\"saldo awal item untuk laporan stok\" class=\"wp-image-1297\"\/><figcaption><em>Contoh stok awal<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>4. Mengisi halaman DATA dengan saldo awal hutang-piutang dagang<\/strong><\/em><\/h4>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1008\" height=\"313\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-04_customer-opening-balance-example.png\" alt=\"Contoh saldo awal untuk laporan hutang piutang di Excel\" class=\"wp-image-1298\"\/><figcaption><em>Contoh saldo awal piutang dagang<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1008\" height=\"313\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-05_supplier-opening-balance-example.png\" alt=\"contoh saldo awal hutang dagang non lunas\" class=\"wp-image-1299\"\/><figcaption><em>Contoh saldo awal hutang dagang<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p>KETERANGAN:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Kolom NOTA, format nomor nota-nya berupa :<ul><li>Karakter pertama adalah huruf P ataupun H. Ini berguna sebagai  tanda bahwa nomor terkait adalah nota Piutang Dagang (PD) ataukah nota Hutang Dagang (HD).<\/li><li>Karakter kedua bebas bebas saja mau di isi huruf apa, tetapi harus selalu tetap sama.<\/li><li>Enam digit pertama setelah tanda &#8220;-&#8221; adalah format waktu berupa YYMMDD (Tahun 2 digit, Bulan 2 digit, Tanggal 2 digit).<\/li><li>Tiga digit terakhir adalah nomor nota PD yang keluar \/ dicetak pada tanggal terkait.<\/li><\/ul><\/li><li>Untuk kolom SUM dan QTY nilainya minus untuk transaksi penjualan, nilainya plus untuk transaksi pembelian.<\/li><li>Sedangkan kolom D\/K berisi text &#8220;kredit&#8221; ataupun &#8220;debet&#8221;<\/li><li>Dan terakhir, kolom BlnThn selalu tanggal 1, namun bulan dan tahun-nya seturut yang terdapat di kolom TGL.<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>5. Membuat Dynamic Named Range<\/strong><\/em><\/h4>\n\n\n\n<p>Agar cakupan baris di daftarnya bertambah\/berkurang secara otomatis\/dinamis, berikut cara-nya: pada tab menu <code>Formulas<\/code> &#8212;&gt; klik <code>Name Manager<\/code>.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"803\" height=\"248\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-06c.png\" alt=\"\" class=\"wp-image-1008\"\/><figcaption><em>Pada tab menu Formulas, klik Name Manager<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"383\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-06a.png\" alt=\"\" class=\"wp-image-1004\"\/><figcaption><em>Di window Name Manager, klik New<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"377\" height=\"294\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-06b.png\" alt=\"\" class=\"wp-image-1005\"\/><\/figure><\/div>\n\n\n\n<p>Untuk <code>Scope<\/code> dan <code>Comment<\/code> biarkan begitu saja, sedangkan untuk  <code>Name:<\/code> dan <code>Refers to:<\/code> nya isi (copy &#8211; paste) seperti dibawah ini :<\/p>\n\n\n\n<p style=\"font-size:14px;\">Name : Customer<br>Refers to : <code>=OFFSET(TABEL!$B$2,0,0,COUNTA(TABEL!$B$2:$B$10000),1)<\/code><br>Klik OK &#8211; klik New\n<br><br>Name : data<br>Refers to : <code>=OFFSET(DATA!$B$1,0,0,COUNTA(DATA!$B:$B),13)<\/code><br>Klik OK &#8211; klik New\n<br><br>Name : Item<br>Refers to : <code>=OFFSET(TABEL!$F$2,0,0,COUNTA(TABEL!$F$2:$F$10000),1)<\/code><br>Klik OK &#8211; klik New\n<br><br>Name : Supplier<br>Refers to : <code>=OFFSET(TABEL!$D$2,0,0,COUNTA(TABEL!$D$2:$D$10000),1)<\/code><br>Klik OK &#8211; klik Close<\/p>\n\n\n\n<p>Buat Named Range-nya satu per satu. Setelah membuat empat Named Range, hasilnya seperti terlihat pada gambar dibawah ini.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"861\" height=\"597\" src=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-06_dynamic-named-range-name-manager.png\" alt=\"named range customer supplier item di Name Manager untuk persiapan dalam membuat laporan hutang piutang di Excel\" class=\"wp-image-1300\"\/><figcaption><em>Contoh dynamic named range di Name Manager Excel<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p>Demikianlah 5 langkah awal pak Adi membuat laporan hutang piutang dan stok di Excel. <a href=\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-pada-excel\/\" target=\"_blank\" rel=\"noreferrer noopener\">Berikutnya<\/a> pak Adi akan membuat salah satu fitur aplikasi Excel, yakni Pivot Table. Pivot Table ini akan dia buat pada halaman yang dia namakan &#8220;PDHD&#8221;. Gunanya adalah untuk melihat daftar hutang piutang usaha-nya.<\/p>\n\n\n\n<p>Apabila ada pembaca yang tidak jelas ataupun ada yang ingin ditanyakan terkait topik ini, silahkan tulis di kolom Comment. Saya akan berusaha untuk bisa menjawabnya.<\/p>\n\n\n\n<div style=\"max-width:800px;text-align:right;\"><button style=\"width:auto\"><a style=\"color:white;\" href=\"https:\/\/www.indonesiangems.com\/blog\/download\/count.php?nm=Sample01\">Download Sample File Data Awal<\/a><\/button><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Testing halaman 1<\/p>\n","protected":false},"author":1,"featured_media":970,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,10],"tags":[],"class_list":["post-5","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-aplikasi-excel-id","category-id","tw-post-has-image-20-9","tw-meta-no-icon"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v15.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Membuat laporan hutang piutang dan stok di Excel - part 1<\/title>\n<meta name=\"description\" content=\"Membuat tabel data dengan mudah dan simpel untuk laporan hutang piutang dan stok di aplikasi Excel dari Microsoft Office. Cocok untuk pemula.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Membuat laporan hutang piutang dan stok di Excel - part 1\" \/>\n<meta property=\"og:description\" content=\"Membuat tabel data dengan mudah dan simpel untuk laporan hutang piutang dan stok di aplikasi Excel dari Microsoft Office. Cocok untuk pemula.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Indonesian Gems Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-02-20T00:54:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-18T22:51:49+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01feature.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"200\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\">\n\t<meta name=\"twitter:data1\" content=\"5 minutes\">\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/#website\",\"url\":\"https:\/\/indonesiangems.com\/blog\/\",\"name\":\"Indonesian Gems Blog\",\"description\":\"just want to share what I&#039;ve done so far\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/indonesiangems.com\/blog\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01feature.jpg\",\"width\":800,\"height\":200},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/#webpage\",\"url\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/\",\"name\":\"Membuat laporan hutang piutang dan stok di Excel - part 1\",\"isPartOf\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/#primaryimage\"},\"datePublished\":\"2021-02-20T00:54:00+00:00\",\"dateModified\":\"2021-03-18T22:51:49+00:00\",\"author\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/#\/schema\/person\/96961e74ef7272e067370a6e6c4d0efd\"},\"description\":\"Membuat tabel data dengan mudah dan simpel untuk laporan hutang piutang dan stok di aplikasi Excel dari Microsoft Office. Cocok untuk pemula.\",\"breadcrumb\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/\",\"url\":\"https:\/\/indonesiangems.com\/blog\/\",\"name\":\"Home\"}},{\"@type\":\"ListItem\",\"position\":2,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/\",\"url\":\"https:\/\/indonesiangems.com\/blog\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/\",\"name\":\"Membuat laporan hutang piutang dan stok di Excel\"}}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/#\/schema\/person\/96961e74ef7272e067370a6e6c4d0efd\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/73e0be4b74509a0250ea0115a8fb8b5a132592a52df5ccc0013ac50eb087f578?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"sameAs\":[\"https:\/\/indonesiangems.com\/blog\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","_links":{"self":[{"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/posts\/5","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/comments?post=5"}],"version-history":[{"count":106,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/posts\/5\/revisions"}],"predecessor-version":[{"id":1602,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/posts\/5\/revisions\/1602"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/media\/970"}],"wp:attachment":[{"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/media?parent=5"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/categories?post=5"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/tags?post=5"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}