# Accounts Receivable With Aging

Quoted from [Investopedia](https://www.investopedia.com/terms/a/accounts-receivable-aging.asp):

> Accounts receivable aging is a periodic report that categorizes a company's [accounts receivable](https://www.investopedia.com/terms/a/accountsreceivable.asp) according to the length of time an invoice has been outstanding. It is used as a gauge to determine the financial health and reliability of a company's customers.
>
> If the accounts receivable aging shows a company's receivables are being collected much more slowly than normal, this is a warning sign that business may be slowing down or that the company is taking on greater credit risk in its sales practices.

Accounts receivable aging, as a management tool, can indicate that certain customers are becoming credit risks. It can be used to help determine whether the company should keep doing business with customers who are chronically late payers.&#x20;

Accounts receivable aging has columns that are typically broken into date ranges of 30 days each and shows the total receivables that are currently due, as well as those that are past due for each 30-day time period.

Có nhiều cách để tạo báo cáo các khoản phải thu, tùy thuộc vào phần mềm kế toán mà bạn đang sử dụng. Ví dụ, bạn có thể tập hợp các bút toán liên quan đến các tài khoản phải thu nhóm theo đối tượng khách hàng, và lấy tổng số tiền ghi Nợ và tổng số tiền ghi Có của từng đối tượng đó. Tổng nợ là khoản tiền phải thu, tổng có là khoản tiền đã trả. Phần chênh lệch là số tiền còn lại phải thu.

Trong ứng dụng với gói Dedicated Server này, chúng ta sẽ dùng một phương pháp khác, rất hiệu quả và mạnh mẽ, để làm báo cáo khoản phải thu: phương pháp đối trừ.

## Đối Trừ Khoản Phải Thu

Giả sử bạn đã hạch toán bút toán bán hàng và sau đó thu tiền vào sổ nhật ký chung trước đó. Bạn có thể đấi trừ hai bút toán này cho nhau bằng cách đánh dấu hai bút toán này trong sổ nhật ký chung như sau:

* &#x20;Ghi ký tự chữ A vào cột _allocation_ của hai dòng bút toán này trong sổ cái bảng laatransaction.
* Tạo ra một số duy nhất và ghi vào cột _allocatoinref_ của hai bút toán này trong sổ cái. Số này là mã đối trừ của bút toán đối trừ.

Khi đó, để trích xuất ra các khoản còn phải thu, bạn chỉ cần lọc lấy ra các bút toán liên quan đến các tài khoản phải thu chưa được đối trừ.

Làm thế nào để đánh dấu hai bút toán là đối trừ?

### Đối Trừ Thủ Công

Đi vào menu <mark style="color:yellow;">Truy vấn</mark> ==> <mark style="color:yellow;">Nhật ký</mark> bạn sẽ thấy tất cả các dòng bút toán xuất hiện như sau:

<figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2FYxUwv8di6RaMOtFNLTdb%2Fimage.png?alt=media&#x26;token=e1de1a59-2961-4206-b6eb-b682aeca2b4f" alt=""><figcaption><p>Truy vấn Nhật Ký Chung</p></figcaption></figure>

Bạn cần tìm cặp bút toán thu tiền/phải thu và đối trừ chúng, có các cách như sau:

1.  Cách 1: tìm theo số tiền. Bạn nhập chính xác số tiền của bút toán cần đối trừ vào dòng tìm kiếm, sau đó đánh dấu chọn cặp bút toán đối trừ, bấm nút đối trừ.  Cột A của các dòng đã đối trừ sẽ xuất hiện chữ "A".

    <figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2FB6Y4TY04LynIfyCH9KUR%2FScreen%20Shot%202024-01-31%20at%201.16.16%20PM.png?alt=media&#x26;token=1b01248b-ca28-4025-b25b-ee81331f26ba" alt=""><figcaption><p>Đối trừ tìm kiếm theo số tiền</p></figcaption></figure>
2.  Cách 2:  tìm theo số bút toán. Bạn nhập các số bút toán cần đối trừ và cách nhau dấu phảy (,) ví dụ: cần đối trừ số bút toán 207 và 2, nhập vào ô tìm kiếm <mark style="color:yellow;">`207,2`</mark>. Sau đó đánh dấu  chọn cặp bút toán đối trừ và bấm nút đối trừ

    <figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2FR5xE59l1LqQ4RLeChIiC%2FScreen%20Shot%202024-01-31%20at%201.28.50%20PM.png?alt=media&#x26;token=240ca672-89ce-4a5a-9fd8-88947dd08b84" alt=""><figcaption><p>Đối trừ tìm kiếm theo số bút toán</p></figcaption></figure>

Cuộn sang phải để tìm cột _allocationref_, bạn sẽ thấy mã đối trừ đã được tạo cho hai dòng bút toán đã được đối trừ với nhau này.

### Đối Trừ Tự Động

Giả sử bạn đã hạch toán bút toán bán hàng vào sổ nhật ký chung trước đó, nhưng chưa hạch toán bút toán thu tiền. Bạn có thể tạo bút toán thu tiền tự động đối trừ với bút toán trước như sau:

1.  Định nghĩa các tài khoản cần theo dõi khoản phải thu bằng cách đi vào menu <mark style="color:yellow;">Kế Toán Sổ Cái</mark> ==> <mark style="color:yellow;">Tài Khoản</mark>, chọn các tài khoản phải thu và đánh dấu loại tài khoản là Phải Thu (D - viết tắt của từ Debtor).\


    <figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2FrEMoY5HRp0AwmFgA55xS%2FScreen%20Shot%202024-02-01%20at%209.30.56%20AM.png?alt=media&#x26;token=6cc28837-56c8-428f-b152-55c308afdc66" alt=""><figcaption><p>Khai báo loại tài khoản Phải Thu</p></figcaption></figure>
2.  Đi vào menu <mark style="color:yellow;">Truy vấn</mark> ==> <mark style="color:yellow;">Phải Thu</mark> bạn sẽ thấy tất cả các dòng bút toán của các tài khoản phải thu. Lọc chọn lấy giao dịch bán hàng trước đó :\


    <figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2FKNixtKRCQfid85oECAnx%2FScreen%20Shot%202024-02-01%20at%209.45.13%20AM.png?alt=media&#x26;token=8d480e67-71e8-40ac-a520-087c25fad18c" alt=""><figcaption><p>Chọn giao dịch phải thu và tạo bút toán đối trừ</p></figcaption></figure>
3.  Nhấn vào biểu tương $ để tạo  bút toán thu tiền:\


    <figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2F8liHddpnAw1gQYHotn1X%2FScreen%20Shot%202024-02-01%20at%2010.24.16%20AM.png?alt=media&#x26;token=83cfd88f-d928-4597-b88d-11367ec05db5" alt=""><figcaption><p>Chọn nghiệp vụ dùng để đối trừ</p></figcaption></figure>
4. Nhấn vào biểu tượng Run để chạy bút toán đối trừ. Kiểm tra, bổ xung các thông tin khác nếu cần, và nhấn biểu tượng Save để lưu bút toán vào sổ cái&#x20;

Quy lại menu <mark style="color:blue;">Truy vấn</mark> ==> <mark style="color:blue;">Nhật ký</mark> bạn sẽ thấy một bút toán mới được tạo ra, đối trừ với bút toán bán hàng. Trong giao diện truy vấn các khoản phải thu, sẽ không còn xuất hiện bút toán bán hàng này nữa.

## Prepare Dataset

We will use the VAS\_LA dataset from the Income Statement created in the previous chapter. If you don't already have one, go back to the last chapter and make one. Then, we will make a report template that uses this dataset.

Take a closer look at the VAS\_LA dataset. It has the customer's codes in the _addresscode_ field, but we need more information than just the codes - we need to get the customer's full names and addresses to make the report easier to understand.

Such details are available in the _csaddress_ table. Let's add them to the VAS\_LA dataset.

1.  Create VAS\_NAD dataset:\
    Go to **Report Setup > Data Set** and click the + icon to create a new dataset.\


    Name the new dataset as **VAS\_NAD**, select the _default_ connection, then click on the button <mark style="color:yellow;">Add Table</mark> and choose the _csaddress_ table.\


    <figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2FromCFbnGy4Ot84ylLfJn%2FScreen%20Shot%202024-02-01%20at%2011.01.05%20AM.png?alt=media&#x26;token=c9b492f8-be2c-4f29-b126-d1d057be453d" alt=""><figcaption><p>Tạo nhu liệu VAS_NAD</p></figcaption></figure>
2.  Embed VAS\_NAD dataset into VAS\_LA dataset: theo hướng dẫn trong phần Dataset/Nhu Liệu để nối nhu liệu VAS\_NAD vào VAS\_LA. Dùng cột _addresscode_  trong hai bộ nhu liệu làm cột giá trị chung.\


    <figure><img src="https://1789172917-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FIVrwm5UGBB368BFg123u%2Fuploads%2F2ofKKvBBa2d3XY2Ecxnb%2FScreen%20Shot%202024-02-01%20at%2011.33.39%20AM.png?alt=media&#x26;token=b74f24f0-ebf2-4dbc-99df-a47d1d73a64c" alt=""><figcaption><p>Nối nhu liệu VAS_NAD vào VAS_LA</p></figcaption></figure>

That's it for the dataset preparation. Imagine the new VAS\_LA dataset as a table with all the information of the journal entries. Each row in the table will have additional columns with information about the address code (addresscode), which could be for both customers or suppliers, such as their name and address, vat name, tax code…

## Collect Data

Go to **Report Setup > Data View** and click on the plus sign to add a new report.

Enter <mark style="color:yellow;">VAS\_AR</mark> in the report code box. Then on the data source lookup box, select <mark style="color:yellow;">VAS\_LA</mark> dataset, then save it.

We need a report of accounts receivable that only shows the unpaid sales. The sales are journal entries that post to accounts 1311 to 1319 in our sample data. It might be different for your data.

Finally, the field allocation in the LA dataset will be used to make sure that the sales are unpaid - if the field allocation has a value “A” then the sale is paid, whereas if it is empty the sale is unpaid.

To add report variables, click the edit icon:

* Drag the accountcode from the left panel into the filter grid above. The From Value and To Value cells will be used as report variables when the report is run.
* Drag the journaltype into the filter grid and set it to be HDBR.
* Drag the allocation into the filter grid and set it to be not equal A.

Now, let's drag the necessary fields for Account Receivable report on left into the output grid:

* transactiondate for transaction date.
* transactionref for transaction reference.
* addresscode for address code.
* invoiceno for invoice number.
* invoicedate for invoice date.
* duedate for due date.
* amount for amount
* address1 under VAS\_NAD for customer name.
* address2 under VAS\_NAD for customer address

When you run the report, the underlying dataset will look similarly to this:

<figure><img src="https://lh7-us.googleusercontent.com/qd4psPHMhGoq-Nt8umdlW_pcxSuenWBnzff_z_vZUn3cNbhXspD6elrBq01o50E2OxzQ2fLgwYV0yqt5nT01n1MyE6YGaDUO4Xc-H8IUL0znF6vy0aTBgM4v2fIMdwFWRAu9gThlv9bIKajUCaRKnNk" alt=""><figcaption></figcaption></figure>

The next step is to collect the data needed for the Accounts Receivable Aging report.

## Presentation

Remove the REPORT\_NAME sheet and replace it with this new, fancy accounts receivable form. Make sure that this form is the first sheet in the Excel workbook: [AR - Presentation](https://docs.google.com/spreadsheets/d/1aPkUtiQK9zjXAU2c2snsC7O6_YJ3C1mirRkn-lB19bU/edit?usp=sharing)

<figure><img src="https://lh7-us.googleusercontent.com/nY5ZyyxukNDU5eEikopWfMKpdLBJbsxFaaZJdX_tgeycFmtGIL4UpMY_ENvfGo23sa49wQbnJ7YTP5TzEmhWxjBdWSBlO1kOt2eCeqJglaLc3aYG_LcGRAC9YaUwFSptIpjCocFOWk0iIHl9o7LzVaQ" alt=""><figcaption></figcaption></figure>

Look at this presentation, we need to extract a list of all the customer codes and their names. We will use the <#Lookup> tag-function to get the debtor name for each addresscode. We will also create a tag-name <#Customer> in the configuration sheet:

* Cell M16: Customer
* Cell N16: <#Report.addresscode>

and a tag-function <#Name(code)>:

* Cell M17: Name(code)
*   Cell N17: <#Lookup(Report;addresscode;<#code>;address1)>\


    <figure><img src="https://lh7-us.googleusercontent.com/yzpwNzSDtfwCgC2ZlxSQrv0uaM-iNlHtOi_BWd21UPCiHIQDodl5tOyHoHhTWOsqyTvgg-KzCgg-UEi-EwYTFxbgOJ4l6HKXL0436L3EAZ9GZ18fGmuBlEWJIPe3lzD_AlgHgwNipPC2NCgq6ZpqwXk" alt=""><figcaption><p>bcdef</p></figcaption></figure>

    Let’s explain:

    * Cell M16 defines a tag-name <#Customer> and it takes the value in the field addresscode of the current record in the Report dataset.
    * Cell M17 defines a tag-function <#Name> which takes one input parameter, named code.
    * Cell N17 is the body of the tag-function: it searches the dataset Report for a row  with the addresscode that matches a value given by the parameter “code”, and returns the value in the address1 column. Just like how the Excel VLOOKUP function works.

Next, we need to show all the records from the dataset Report in the Accounts Receivable Aging sheet (also known as the presentation sheet). To do this, we will use the band and Excel named range. You should now be familiar with these concepts. Go to the Excel menu Formulas, click on Name Manager, and create a named range called \_\_Report\_\_ using the entire row 6 from the presentation sheet, like this:



Enter the tag-name <#Customer> in the D6 cell and the tag-function <#Name(<#Customer>)> in the cell E6 of the presentation sheet.&#x20;

<figure><img src="https://lh7-us.googleusercontent.com/-q96nUkQvHGvxNVlub2GTZp-mA6WhUWkXZT7HZSKFOaaSPuimBkm4kyjc-piEz83ofLucDQ96uhEIjQC_ZZs-ikuIqNvhHDSyQgMOawvFqKknQ2HLgjWw6rfBzXHl3nHnVgNX3jpWHMwF_1kU0x_fNw" alt=""><figcaption></figcaption></figure>

Now let's explain our formulas:

* We defined a named range called  \_\_Report\_\_, and attached it to the entire sixth row with  'Accounts Receivable Aging'!$6:$6
*   The band \_\_Report\_\_ will look in the configuration sheet for a dataset with the same name to use as its dataset. That’s the Report dataset defined in cell A10 of the <#Config> sheet.\


    <figure><img src="https://lh7-us.googleusercontent.com/FsqIy_d1D7X7uy_fT8oB7FH4hdK58Tu0Yw1vAwehDDQyP5vjAozmKYHQ_LN58Az7OcHnyg0w_DE5kGPAkIbnOeoLCKFHYPf1gR-KyIxPUrFJDDD-uOKXWlC8qGSRqzaLLYhrrfSlmWjQ2pLGzFpCXyg" alt=""><figcaption></figcaption></figure>
* On the presentation sheet, the band \_\_Report\_\_ will look at each row in the Report dataset, inserting a new row in the presentation sheet for each record, and provide the reference to the current record for formulas in that row.
*   Remember, how we defined the tag-name <#Customer> in the configuration sheet and its usage in the presentation sheet:\


    <figure><img src="https://lh7-us.googleusercontent.com/_CeXrCBOcPebE9xzJ_-6aFZDi9PSicYvG4_fKmyc-D7hTtNiusUDSyuH11UpvjANgQfBU8klIJrSQnMnm308dxXDVSgzqnWwyBrpsctRk3LOniq83k4_8hjH6IOHY0qb51wsSqTUyrQKn_R0md3Fo5s" alt=""><figcaption></figcaption></figure>
*   So the tag name <#Customer> will take the value in the field addresscode from the row’s current record of the Report dataset, And so, row 6 will have the first address code, which is 57636, row 7 will have SUNRISE, so on and so forth, to the last row.\


    <figure><img src="https://lh7-us.googleusercontent.com/e0EmNFVx4vxcspRn2GS3s1Qsu9m2dByYhsZhNFT3XmrZkzFauIv8bL-549vOVhiJYWS7Xj5dGlg8rBQ_inwEDDUdRZD1_UyIMPXnChueMhUiwROWnFqcoCAtss8IiNf88PprFn1Krmlk6MfnE3s8LHA" alt=""><figcaption></figcaption></figure>

Upload this report template, save and run it. You should get this:\


<figure><img src="https://lh7-us.googleusercontent.com/Ln5S7Uz-b85eEFSAPim9gO2OIM366PyvslEA_1KPUPac8HJT-vk969_u0K6Miif-6niJ34o-EULy4c16KGITELo_-ACVgokn5aZb33aqvq4SfgbrwQzTOnRBUIH3qvKja6sep_KQ582taqh95203who" alt=""><figcaption></figcaption></figure>

For your convenience, this report template can be downloaded from here: [AR - I1](https://docs.google.com/spreadsheets/d/1VTIlOIdFbdnyIPUbakFuYDcIazUXYU5Dqz9aQpXK0G8/edit?usp=sharing)

Finally, we will complete all of the other details.

Aging computation\
NOTE: We recommend using Microsoft Excel, if possible, as Google Sheets may not always work correctly with Excel files.
-----------------------------------------------------------------------------------------------------------------------

Go to the following cells and enter these tags:

* Cell B6: <#Report.invoicedate>
* Cell C6: <#Report.invoiceno>
* Cell G6: <#Report.amount>
* Cell H6: <#Report.duedate>

\


We want to work out how old the outstanding amounts are by subtracting the due date from the current date. Cells J6 to M6 show the current outstanding amount, and how many days overdue they are if they are more than 1, 30, 60 and 90 days old.

\


Go to the following cells and enter these tags:

* Cell I3 =TODAY()
* Cell I6: =IF(H6<$I$3,G6,0)
* Cell J6: =IF(AND($I$3-$H6>=1,$I$3-$H6<=30),$I6,"")
* Cell K6: =IF(AND($I$3-$H6>=31,$I$3-$H6<=60),$I6,"")
* Cell L6: =IF(AND($I$3-$H6>=61,$I$3-$H6<=90),$I6,"")
* Cell M6: =IF(AND($H6>=0,$I$3-$H6>=91),$I6,"")

The other cells are just normal excel formulas.

For your convenience, you can download a complete report template from here: [Accounts Receivable Report - 2](https://docs.google.com/spreadsheets/d/1zUJI_iG8TPFFkh1Qqy1ZMmpNHi8Dq4ZJySms_wGKmVg/edit?usp=sharing)

That’s all for data preparation. Upload this report template, and save it.\


## Running and Testing

Run the report you have worked hard on and you should see the results of your work:

<figure><img src="https://lh7-us.googleusercontent.com/kqUL0sk-n1lInSkX3rmFWZNAkmQW-gXZVYdhOaobaiu4_BfpNz4ipLh3GfyLDq_FeTntQfyAKqnFzEK5e_WQQOhDwytFITykfaygh9ik6niEw3pXjRw8byJZjBn8qMJzsm_pVVAyE8PWqHm2-a4jKfY" alt=""><figcaption></figcaption></figure>

We are going to check the accuracy of this report. Let's take a look at the second record, which shows an amount of 706,332,000:

Go to Ledger Accounting > Inquiry > Journal Inquiry and type 706332000 into the search box. If needed, click on the refresh icon. Your view should look like this:

<figure><img src="https://lh7-us.googleusercontent.com/_jg5F-Oukv-iCP_6DWrEO48mp805ArmQfoH34y_5IAEMkvFcbRjWgmhDMFz7CjdRYux2vdfQ1xQT8uQDsuzPp0RrwDMuNkno2dBPfWLifKUykPiyG553Yd4y0WpeU_eIGGRhlEBmMuMngW0nS0A-S18" alt=""><figcaption></figcaption></figure>

On February 28th, 2020, the sale was recorded in journal 98. On 2020/09/22, the payment for the sale was settled in journal 473. To link the payment to the sale, select the checkboxes for the two journal lines with the account code 1311 and click the match amount icon. This will link the two journal lines together and give them the same allocation reference number.

<figure><img src="https://lh7-us.googleusercontent.com/geUFkxw6qZuptwwpOmfDnkNvMLroXlV-QXiaR8sZ2zN_qud2WOyxYBlkdxU8s4EssIscRaExBx-48UZ7ctuN0fbM0QrqTSj4YlXqq3VZBEsu-HjfcGsonX-CNqwocnUhgWmq4UooE3J2BmeeJ5aE3ho" alt=""><figcaption></figcaption></figure>

Now, re-run the report. The invoice number 0000015 should no longer show up in the report.

<figure><img src="https://lh7-us.googleusercontent.com/PtSOMGa3pUQHvty8MhqLKqZGM2EzFqpM8T2RqcpbFgjNjo1cZkGy7qAo7COD8iDd4iNl10qBpotWSoD-Wb7IN5e9NvZ3AhqJfN6BWQ2UC28KBs63I4wwsQO6ibWEfuOUkwlRINGS1gdOyLCW38aj0yc" alt=""><figcaption></figcaption></figure>

Congratulations! You have worked hard and now you are one of the pioneers to be part of the KNOWINS power user community.
