Bảng Cân Đối Thử

PreviousSố Dư Đầu Kỳ/Cuối Kỳ NextAccounts Receivable

Last updated 23 days ago

Bảng Cân Đối Thử

Bảng cân đối thử là một bảng tính kế toán trong đó số dư của tất cả các tài khoản được tổng hợp thành cột tổng tài khoản nợ và tài khoản có. Một công ty thường lập bảng cân đối thử định kỳ, thường vào cuối mỗi kỳ báo cáo. Mục đích chung của việc lập bảng cân đối thử là để đảm bảo rằng các mục trong hệ thống kế toán của công ty là chính xác về mặt toán học.
Bảng cân đối thử được gọi như vậy vì nó cung cấp một thử nghiệm về một khía cạnh cơ bản của một bộ sổ sách, nhưng nó không phải là một cuộc kiểm toán đầy đủ. Sổ đối chiếu tài khoản thường là bước đầu tiên trong quy trình kiểm toán, vì nó cho phép kiểm toán viên đảm bảo không có lỗi toán học trong hệ thống kế toán trước khi chuyển sang các phân tích phức tạp và chi tiết hơn.

Để chuẩn bị bảng cân đối thử, chúng ta sẽ thực hiện các bước sau:

Lọc danh sách các tài khoản riêng biệt từ các mục trong sổ nhật ký chung.
Lấy tổng số của bên nợ của mỗi tài khoản và đặt vào cột bên nợ của bảng cân đối thử.
Lấy tổng số của bên có của mỗi tài khoản và đặt vào cột bên có của bảng cân đối thử.
Lặp lại các bước trên cho số dư đầu kỳ và tính số dư cuối kỳ.

Chuẩn bị dữ liệu

Chúng ta sẽ sử dụng cùng bộ dữ liệu VAS_LA đã tạo trong bài Báo Cáo Thu Nhập cho báo cáo này.

Thu thập dữ liệu

Go to Report Setup > Data View and click on the + icon to add a new report.

Enter VAS_TB in the report code box. Then on the data source lookup box, select VAS_LA dataset and click save.

We want to create a trial balance report for a specific period of time. To do this, set up a filter to only include journal entries from that time period.

To add report variables, click the edit icon and drag the transactiondate from the left panel into the filter grid.

Now imagine we have all the journal entries saved in an Excel table. In order to make a trial balance, we will group these entries by both account code and debit/credit. Then, the sum amounts of these groups will be the items on the trial balance.

Let's do that:

Drag the amount into the output grid below and choose SUM as its aggregate function.
Drag the accountcode and d_c into the output grid. Additionally, drag the accountname under VAS_COA dataset to the output grid.

This will cause the SUM function to group all of the posting amounts that have the same combination of accountcode, d_c and accountname values and will add them together group by group.

When you run the report, the underlying dataset will look similarly to this, negative numbers just mean that it is a credit balance:

Similarly to the Income Statement report, so far inside the report template we have 2 datasets being created:

Filters: stores users's inputs when running the report.
Report: stores account information and their SUM amounts.

Presentation

Download the standard Excel template for a report.

Remove the REPORT_NAME sheet and replace it with this new, fancy trial balance form. Make sure that this form is the first sheet in the Excel workbook.

You can download it here for your convenience: Trial Balance - Presentation

Take a closer look at the trial balance. On the left, there is a list of different accounting accounts, each on its own row. Note that, depending on whether each account has both credit and debit amounts, there could be one or two rows of data for each account in the report..

Let's make a list of unique accounts from the posted accounts in the dataset. We will use a filter feature called DISTINCT to remove duplicate accounts. Go to the <#Config> sheet and make a new dataset called Account:

Cell A13: Account
Cell B13: Report
Cell C13: DISTINCT(accountcode)
Cell D13: accountcode

DISTINCT - Filter function that returns a unique list of field values from a dataset

Usage: In columns A, B, C and D of the <#Config> sheet, enter

The name of the filtered dataset in column A.
The name of the dataset it references in column B.
The filter function DISTINCT with the fields you wish to filter in column C. For example: DISTINCT(field1;field2;field3)
The sorting criteria for the fields of the result dataset ASC (increasing) DESC (decreasing) in column D

Refer to the Report Design Reference chapter for a full description.

When running this report, the data set Account would look like this:

Next, we need to show all the records from the newly created dataset Account in the Trial Balance (also known as presentation) sheet. To do this, we will use an Excel named range. Go to the Excel menu Formulas, click on Define Name, and create a named range called __Account__ using the whole row 6 from the Trial Balance, like this:

Now let's explain what our formulas do:

We defined a named range __Account__ which covers the whole sixth row of the Trial Balance sheet using this 'Trial Balance'!$6:$6
The band _Account__ will look in the column A in the configuration sheet for a dataset with the same name. That’s the Account dataset defined in cell A13 of the <#Config> sheet.
The dataset Account takes a distinct list of accountcode from the Report dataset defined in cell A10

Enter the tag <#Account.accountcode> in the A6 cell of the Trial Balance sheet.

When running a report, on the presentation sheet, the named range Account will look at each row in the Account 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.

So the <#Account.accountcode> will take the value in the field accountcode from the row’s current record, And so, row 6 will have the first account code of the Account dataset, which is 1111, row 7 will have 1121.01, so on and so forth, to the last row.

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

For your convenience, the intermediate report template can be downloaded from here: Trial Balance - I1

Next, we will see how to fill in debit and credit information for each account. We will use the <#Lookup> tag-function to get the total credit and debit amounts for each account. We will also create two new tag-functions named <#Debit(code)> and <#Credit(code)> to use for this purpose as follows:

Cell M16: Debit(code)
Cell N16: <#Lookup(Report;accountcode,d_c;<#Array(<#code>;D)>;amount)>

Let’s explain:

Cell M16 defines a tag-function <#Debit> which takes one input parameter, named code.
Cell N16 is the body of the tag-function: it searches the dataset Report for a row with the accountcode and d_c (accountcode,d_c) that matches both values given by the parameter “code” and the value D, and returns the value in the amount column. Just like a comprehensive version of the Excel VLOOKUP function that takes in 2 criteria to match.

<#Array> - The tag function that returns a combination of values from provided inputs..

Example: If the tag name <#code> has a value of 1111, then the tag function <#Array(<#code>;D) returns the value as of [1111, D]. When using the tag function <#Lookup()> with two criteria (accountcode and d_c), separated by a comma, the look up will match the accountcode with 1111 and the d_c with D to find the corresponding row.

Refer to the Reporting Reference for a full description.

And:

Cell M17: Credit(code)
Cell N17: <#Evaluate(-1 * <#Lookup(Report;accountcode,d_c;<#Array(<#code>;C)>;amount)>)>

Let’s explain:

Cell M17 defines a tag-function <#Credit> which takes one input parameter, named code.
Cell N17 is the body of the tag-function:
- First, it searches the dataset Report for a row with the accountcode and d_c (accountcode,d_c) that matches both values given by the parameter “code” and the value C, and returns the value in the amount column.
- Then, it uses the tag-function <#Evaluate> to calculate the final value by multiplying the result from the <#Lookup> tag-function with -1. This is because the credit amounts are negative numbers, so this makes them positive.

Now, it's time to fill up the report presentation.

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: <#Lookup(Report;accountcode;<#Account.accountcode>;accountname)>
Cell F6: <#Debit(<#Account.accountcode>)>
Cell G6: <#Credit(<#Account.accountcode>)>

Save your template, upload and run the report. The debit and credit amount is displayed.

Opening Balance

Opening balance of an account is its closing balance a day before a report date. To get the opening balances, we just need to repeat the same steps we did before but with the dataset from the very first day until the yesterday of the start date of the report. Note that, on the first day all the balances are zero, so the accumulated sum of debit and credit for that dataset by account must be their starting balances.

Let’s create this dataset.

Go to Report Setup > Data View, select our report, and then click on the Dataset Formulas button. This will copy the formula to the clipboard.

Go to the <#Config> sheet and make a new dataset called Beginning

Cell A16: Beginning
Cell B16: Paste the copied content from the clipboard.

We need to put a very old date, like “2000-01-01”, in the filterfrom field and use whatever date the user enters into the filter input as the filterto.

Let’s define tag names <#FromDate> and <#ToDate> to capture these dates. Enter in the following cells:

Cell M13: FromDate
Cell N13: <#Lookup(Filters;Name;transactiondate;ValueFrom)>
Cell M14: ToDate
Cell N14: <#Lookup(Filters;Name;transactiondate;ValueTo)>

Now go back to the cell B16 and change the value of filterto to <#FromDate>

Let’s copy the tag-functions <#Debit(code)> and <#Credit(code)> to create 2 new tag functions that use this Beginning dataset, as follows:

Cell M22: BeginningDebit(code)
Cell N22: <#Lookup(Beginning;accountcode,d_c;<#Array(<#code>;D)>;amount)>
Cell M23: BeginningCredit(code)
Cell N23: <#Evaluate(-1 * <#Lookup(Beginning;accountcode,d_c;<#Array(<#code>;C)>;amount)>)>
Cell M26: Balance(code)
Cell N26: <#Evaluate(<#BeginningDebit(<#code>)> - <#BeginningCredit(<#code>)>)>
Cell M28: DebitBalance(code)
Cell N28: <#If(<#Balance(<#code>)> > 0;<#Balance(<#code>)>;0)>
Cell M29: CreditBalance(code)
Cell N29: <#Evaluate(-1 * <#If(<#Balance(<#code>)> < 0; <#Balance(<#code>)>; 0)>)>