{"id":1407,"date":"2021-03-16T16:49:10","date_gmt":"2021-03-16T08:49:10","guid":{"rendered":"https:\/\/indonesiangems.com\/blog\/?p=1407"},"modified":"2021-03-19T06:15:39","modified_gmt":"2021-03-18T22:15:39","slug":"accounts-payable-and-receivable-in-excel-report","status":"publish","type":"post","link":"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/","title":{"rendered":"Accounts Payable and Receivable in Excel report"},"content":{"rendered":"<p class=\"last-updated\">Last updated on March 19th, 2021 at 06:15 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\/membuat-laporan-hutang-piutang-dan-stok-di-excel\/\">switch language to bahasa<\/a><\/div>\n<\/div>\n\n\n\n<p>Case Example:<br>Mr. Adi has a small shop. Usually he writes down the buy and sell transaction of his shop in a book. As now he has more customers, suppliers and products, he plans to have his accounts payable and receivable in <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>Because he didn&#8217;t go to an Economic Schools, he will make the Excel program according to what he thinks is fit. So he starts gathering the data needed to make the accounts payable and receivable in <a href=\"https:\/\/docs.microsoft.com\/en-us\/office\/vba\/api\/overview\/excel\" target=\"_blank\" rel=\"noreferrer noopener\">Excel<\/a> report.<\/p>\n\n\n\n<p><strong>Preparation<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>A list of customer and supplier name<\/li><li>A list of product name<\/li><li>Opening balance of the accounts payable and receivable<\/li><li>Opening balance of the inventory<\/li><\/ul>\n\n\n\n<p>After all those points above collected, Mr. Adi starts to fill the Excel Worksheet with those data.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>1. Make a list of customer, supplier and product names.<\/strong><\/em><\/h4>\n\n\n\n<p>First, Mr. Adi name the Excel worksheet by double clicking the &#8220;Sheet1&#8221; tab, then change the name to &#8220;TABLE&#8221; by typing in that tab. He also double-click &#8220;Sheet2&#8221;, then change it to &#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=\"Naming the worksheet as the preparation to make the accounts payable and receivable in Excel.\" class=\"wp-image-1294\"\/><figcaption><em>Worksheet naming (please ignore the sheet name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p>Then on sheet TABLE, he type all of the names of the customer, supplier, and product which he gathered before in the preparation step. This list of names in the worksheet TABLE will be used when inputting the buy or sell transaction. Below is the example image:<\/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=\"Example of a list of customer, supplier and product names in Excel's worksheet.\" class=\"wp-image-1295\"\/><figcaption><em>A list of customer, supplier and product name example.<br>(please ignore the sheet name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>2. Make the Main Data<\/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=\"The headers for the Main Data table for the accounts payable and receivable transaction.\" class=\"wp-image-1296\"\/><figcaption><em>The headers for the Main Data tabl<\/em>e<br><em>(please ignore the header and the sheet name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p>Next, Mr. Adi create a transaction table in sheet DATA, with the headers from cell B1 to cell N1 as followed:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>DATE = the date of the transaction<\/li><li>INV = invoice number<\/li><li>NAME = customer \/ supplier name<\/li><li>BOOK = stock \/ cash<\/li><li>POST = CU for Customer, SU for Supplier<\/li><li>ITEM = product name<\/li><li>QT = item quantity<\/li><li>PRICE = price of the item<\/li><li>SUM = QT * PRICE (positive\/negative)<\/li><li>QTY = item quantity (positive\/negative)<\/li><li>D\/C = Debit \/ Credit<\/li><li>Y\/M = Year and Month of the invoice<\/li><li>PAID = will be filled when paid<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>3. Fill the Main Data with the Inventory opening balance<\/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>Inventory opening balance example<\/em><br><em>(please ignore the name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>4. Fill the Main Data with the accounts payable and receivable opening balance<\/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>Accounts Payable opening balance example<\/em><br><em>(please ignore the name)<\/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><em>Accounts Receivable opening balance example<\/em><\/em><br><em>(please ignore the name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p><span style=\"text-decoration: underline;\">NOTE<\/span>:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>In the INV column, the invoice number has a format as follow :<ul><li>The first two characters are CU or SU followed by a hyphen. CU- represents as a customer invoice and SU- represents as a supplier invoice.<\/li><li>The six digits after the hyphen is a date format : YYMMDD.<\/li><li>The last three digits is the number of the invoice in a day. The next day, the number will reset to 001 both for CU and SU.<\/li><\/ul><\/li><li>The value of SUM and QTY column will be a negative number for the accounts payable transaction (selling to the customer) and will be a positive number for the accounts receivable transaction (buying from the supplier)<\/li><li>D\/C column value is Debit for a positive value and Credit for a negative value.<\/li><li>In the Y\/M column, the date will always the first date.<\/li><\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><em><strong>5. Create a Dynamic Named Range<\/strong><\/em><\/h4>\n\n\n\n<p>In order the range of the customer, supplier, product name list and the Main Data dynamic, Mr. Adi create a Dynamic Named Range as follow:<\/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>Click the Name Manager button in Formulas tab menu<\/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>Click New&#8230;. in the Name Manager window<\/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>In the New Name window, leave the &#8220;Scope:&#8221; and &#8220;Comment:&#8221; as it is, while for the &#8220;Name:&#8221; and &#8220;Refers to:&#8221; copy-paste the following &#8230;<\/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>Click OK &#8211; Click New\n<br><br>Name : data<br>Refers to : <code>=OFFSET(DATA!$B$1,0,0,COUNTA(DATA!$B:$B),13)<\/code><br>Click OK &#8211; Click 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>Click OK &#8211; Click 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>Click OK &#8211; Click Close<\/p>\n\n\n\n<p>After making four Dynamic Named Range one by one, the result in the Name Manager is like in the image below:<\/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=\"Dynamic Named Range for the account payable, receivable and inventory report.\" class=\"wp-image-1300\"\/><figcaption><em>Example of a Dynamic Named Range in the Name Manager<\/em><br><em>(please ignore the sheet name)<\/em><\/figcaption><\/figure><\/div>\n\n\n\n<p>So that&#8217;s the first five beginning step which Mr. Adi did to make the accounts payable and receiable in Excel. <a href=\"https:\/\/indonesiangems.com\/blog\/accounts-payable-receivable-inventory-pivot-table\/\">In the next page, <\/a>Mr. Adi will make two Pivot Tables. One for the accounts payable\/receivable report, and one for the inventory report. <\/p>\n\n\n\n<p>If there is something is not clear regarding this topic, please don&#8217;t hesitate to ask in the Comment box. I&#8217;ll try my best to answer.<\/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-En\">Download sample file with opening balance data<\/a><\/button><\/div>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Last updated on March 19th, 2021 at 06:15 am<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9,12],"tags":[],"class_list":["post-1407","post","type-post","status-publish","format-standard","hentry","category-en","category-excel-application-en","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>Accounts Payable and Receivable in Excel report - part 1<\/title>\n<meta name=\"description\" content=\"Create a simple accounts payable and receivable in Excel report easily. Suitable for a beginner. Sample file is included.\" \/>\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\/accounts-payable-and-receivable-in-excel-report\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Accounts Payable and Receivable in Excel report - part 1\" \/>\n<meta property=\"og:description\" content=\"Create a simple accounts payable and receivable in Excel report easily. Suitable for a beginner. Sample file is included.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/\" \/>\n<meta property=\"og:site_name\" content=\"Indonesian Gems Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-03-16T08:49:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-18T22:15:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-00_naming-page-in-excel.gif\" \/>\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=\"6 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\/accounts-payable-and-receivable-in-excel-report\/#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/indonesiangems.com\/blog\/wp-content\/uploads\/2021\/03\/01-00_naming-page-in-excel.gif\",\"width\":491,\"height\":125},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/#webpage\",\"url\":\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/\",\"name\":\"Accounts Payable and Receivable in Excel report - part 1\",\"isPartOf\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/#primaryimage\"},\"datePublished\":\"2021-03-16T08:49:10+00:00\",\"dateModified\":\"2021-03-18T22:15:39+00:00\",\"author\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/#\/schema\/person\/96961e74ef7272e067370a6e6c4d0efd\"},\"description\":\"Create a simple accounts payable and receivable in Excel report easily. Suitable for a beginner. Sample file is included.\",\"breadcrumb\":{\"@id\":\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/#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\/accounts-payable-and-receivable-in-excel-report\/\",\"url\":\"https:\/\/indonesiangems.com\/blog\/accounts-payable-and-receivable-in-excel-report\/\",\"name\":\"Accounts Payable and Receivable in Excel report\"}}]},{\"@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\/1407","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=1407"}],"version-history":[{"count":21,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/posts\/1407\/revisions"}],"predecessor-version":[{"id":1604,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/posts\/1407\/revisions\/1604"}],"wp:attachment":[{"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/media?parent=1407"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/categories?post=1407"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/indonesiangems.com\/blog\/wp-json\/wp\/v2\/tags?post=1407"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}