Accounts Receivable Details
Last updated
Last updated
Quoted from Investopedia:
Accounts receivable (AR) are the balance of money due to a firm for goods or services delivered or used but not yet paid for by customers. Accounts receivable are listed on the balance sheet as a current asset. Any amount of money owed by customers for purchases made on credit is AR.
The purpose of the accounts receivable process is to make sure that sales invoices are correctly recorded and that customers pay on time. This includes keeping track of the money owed from sales and making sure it is paid back. Managing a receivables account is all about matching sales with payments.
We are going to use the same dataset VAS_LA we created in the Income Statement report session for this report.
Go to Report Setup > Data View and click on the plus sign to add a new report.
Enter VAS_AR in the report code box. Then on the data source lookup box, select VAS_GL dataset, then save it.
This report needs to show all of the sales (whether they were paid or unpaid) for each customer. The sales and payments are recorded in the accounts 1311 to 1319 in our sample data, but this might be different for your data.
The addresscode field in the LA dataset will help us track the payment details for each customer.
To add report variables, click the edit icon:
Drag the accountcode from the left side into the filter grid. Set the From Value and To Value cells to the desired values. They will be used as report variables when it is run.
Drag the transactiondate into the filter grid. Set it to the desired values, for example, from 2020-01-01 to 2020-12-31.
Then drag the following fields into the output grid:
transactiondate for transaction date
transactionref for transaction reference
addresscode for address code
invoiceno for invoice number
allocation for allocation
allocationref for allocation reference
description for description
amount for amount
Then, drag type, address1 and address2 under NADRecord to the output grid.
type type C for customer and S for Supplier.
address1 customer name.
address2 customer address: street name and city…
When you run the report, its underlying dataset will look similarly to this:
Download the standard Excel template for a report.
Remove the REPORT_NAME sheet and replace it with this receivable details form. Make sure that this form is the first sheet in the Excel workbook: RD - Presentation
We will take the address codes from the LA dataset, make a new list of unique customers, and remove any duplicates using the DISTINCT filter. Go to the <#Config> sheet and make a new data set called Client:
Cell A13: Client
Cell B13: Report
Cell C13: DISTINCT(addresscode)
Cell D13: addresscode
When running this report, the data set Client would look like this:
Look at this presentation, we need to extract a list of all the customer codes and their names. To do this, we will use a tag-function called <#Lookup> to get a name that corresponds to each customer code. We will also create a tag-name <#Customer> in the configuration sheet:
Cell M16: Customer
Cell N16: <#Client.addresscode>
and a tag-function <#Name(code)>:
Cell M17: Name(code)
Cell N17: <#Lookup(Report;addresscode;<#code>;address1)>
Let’s explain:
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.
Enter the tag-name <#Customer> in the B6 cell and the tag-function <#Name(<#Customer>)> in the cell C6 of the presentation sheet.
Now let's explain our formulas:
We defined a named range __Client__ attached to the entire row 6 by giving it the value ’Receivable Details’!$6:$6
The band _Client__ will look in the configuration sheet for a dataset with the same name to use as its dataset. That’s the Client dataset defined in cell A13 of the <#Config> sheet.
The Client dataset will take all the different addresscode from the Report dataset listed in cell A10.
On the presentation sheet, the __Client__ band will look at every row in the Client dataset, add a new row to the presentation sheet for each record, and provide the reference to the current record for formulas in that row..
The tag name <#Customer> will take the value in the field addresscode from the current record of the Client dataset, And so, row 6 will have the first address code, which is 57636, row 7 will have NTN, so on and so forth, to the last row.
Upload this report template, save and run it. You should get this:
For your convenience, the this report template can be downloaded from here: RD - I1
For each customer we need more information about details of their original debt and settlement. This leads us to the next concept, called the master-detail band.
Named ranges can be placed inside each other, and a master-detail relationship will be automatically created. For example, if you define a range __Customer__ and inside it a range __Orders__ and then create a relationship between Customer and Order tables, it will automatically group your orders by customer.
This range takes its data from the Report dataset that is defined in the cell A10 in the configuration sheet table.
To make the __Client__ band the master of the __Report__ band, we need to change its range from 'Receivable Details'!$6:$6 to 'Receivable Details'!$6:$9 so that it covers the entire range of the __Report__ band.
Now, let’s define a relationship between these two bands. Go to the <#Config> sheet and enter the following:
Cell A14: Relationship ⇨ define a relationship
Cell B14: Client->Report ⇨ define master - detail
Cell C14: addresscode->addresscode ⇨ define linked field
Let’s explain this: cell A14 defines a master-detail relationship between 2 bands. The master band, which is on the left side of the operator "->" in cell B14, is the Client dataset. The detail band, which is on the right side, is Report dataset. The common field that links the two datasets together is addresscode, which is located in cell C14. It is ok to have more than one linked field.
Next, we will use the <#If> tag-function to find out if a journal line is a debit or a credit. We will create two new tags called <#Debit> and <#Credit> to accomplish this:
Cell M19: Debit
Cell N19: <#If(<#Report.amount> > 0; <#Report.amount>; 0)>
Let’s explain: Cell M19 just defines a tag name <#Debit>. The value of this tag name is in cell N19. We are using the tag-function <#If> to compare the amount column of the current record in the Report dataset. If the amount is greater than zero, the tag name <#Debit> will be set to that value. If the amount is not greater than zero, the tag name <#Debit> will be set to zero. Positive number represents the debit amount and the negative number represents the credit amount. The tag-function <#If> works like the Excel IF function, except that it also accepts tag names as parameters.
Cell M20: Credit
Cell N20: <#If(<#Report.amount> < 0; <#Report.amount>; 0)>
So far so good? Let’s add the tag names to catch user’s inputs in the filter grid. Remember, the name of the filter dataset is called Filters.
Cell M12: FromDate
Cell N12: <#Lookup(Filters;Name;transactiondate;ValueFrom)>
Cell M13: ToDate
Cell N13: <#Lookup(Filters;Name;transactiondate;ValueTo)>
Cell M14: FromAccount
Cell N14: <#Lookup(Filters;Name;accountcode;ValueFrom)>
Cell M15: ToAccount
Cell N15: <#Lookup(Filters;Name;accountcode;ValueTo)>
Finally, let’s create our fancy receivable details report.
On the presentation sheet, go to the following cells and enter these tags:
Cell B7: <#Report.transactiondate>
Cell C7: <#Report.description>
Cell D7: <#Report.invoiceno>
Cell E7: <#Report.transactionref>
Cell F7: <#Report.allocation>
We will use the <#Debit> and <#Credit> defined above to fill in the transaction debit and credit. However, we want Excel to format these values as numbers, not text. So we will use the tag-function <#Evaluate> to convert them to numbers and make sure they are positive by multiplying them by either 1 or -1.
Cell G7: <#Evaluate(1 * <#Debit>)>
Cell H7: <#Evaluate(-1 * <#Credit>)>
When running the report, these tags will get their values from the current record of the Report dataset when each record is added in the __Report__ band of the presentation sheet.
The other cells are just normal excel formulas to determine if the ending balance is credit amount or debit. Go to the following cells and enter these tags:
Cell I7: =IF(I6-J6+G7-H7>0,I6-J6+G7-H7,0)
Cell J7: =IF(I6-J6+G7-H7>0,I6-J6+G7-H7,0)
Cell G8: =SUM(G7:G8)
Cell H8: =SUM(G7:G8)
Cell I8: =IF(I6-J6+G9-H9>0,I6-J6+G9-H9,0)
Cell J8: =IF(I6-J6+G9-H9<0,-1 *(I6-J6+G9-H9),0)
Upload the report template and run it. Here is what you should get:
For your convenience, you can download a this report template from here: RD - I2
To figure out the beginning balance, we need to add up the credit and debit amounts of all the journal entries posted to the accounts from 1311 to 1319 from the very first day up until the day before the start date of the report for each address code. On the first day all the balances are zero, so the adjusted debits or credits for that set must be the starting balances.
Let’s create this dataset.
Go to Report Setup > Data View, click the plus icon to add an ARS report as follows:
Save the report, and then click on the Dataset Formulas button. This will copy the formula to the clipboard.
Now go to the configuration sheet of our Receivable Details report and define this new dataset called Beginning
Cell A26: Beginning
Cell B26: Paste the copied content from the clipboard.
We will create a tag called <#BeginDate> which will calculate the date before the start date of our report. Excel stores dates as a number, with 1/1/1900 being the first date in Excel's date system, and equal to the number 1. The date 1/2/1900 is 2, and so on. Additionally, Excel has a function called DATEVALUE which can convert a date in text format, such as ”2020-12-31”, to its internal format, which is 44196. This means that the date prior to this is 44195.
Excel also has the functions YEAR, MONTH, and DATE to get the year, month, and date from a date in its internal format. For example, typing in YEAR(44196) will give you the year 2020.
We can use this formula to figure out what date it was before the reporting date and make sure it is in the correct format. We must use the tag function <#Evaluate> because we want the Excel functions YEAR, MONTH and DAY to accept the tag <#ExcelDate> as their parameter:
Cell M22: ExcelDate
Cell N22: <#Evaluate(DATEVALUE(<#FromDate>) - 1)>
Cell M23: BeginDate
Cell N23: <#Evaluate(YEAR(<#ExcelDate>))>-<#Evaluate(MONTH(<#ExcelDate>))>-<#Evaluate(DAY(<#ExcelDate>))>
We need to enter a very old date, says "2000-01-01", in the filterfrom field and use the tag <#BeginDate> for the filterto field. Go back to the cell B16 and change the value of the filterto to <#BeginDate>.
Run your report and enjoy your hard work. You should see the followings:
Well done! You have learned everything you need to know to make reports on the KNOWINS platform. Now, it's time to practice by creating the Accounts Receivable Summary report.
We need to show all the records from the Client dataset in the Receivable Details sheet (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 Client using the entire row 6 from the presentation sheet, like this:
In the following example, the yellow cells are the range __Customer__ and the blue ones are the range __Orders__. After running the report, you will get a result similar to the one on the right side:
Let's use what we have learned to make our report better. Go to the data menu and create a new range called __Report__ that takes the entire row 7 of the presentation sheet (‘Receivable Details'!$7:$7).
