Closing Entry
Last updated
Last updated
Quoted from Investopedia
A closing entry is a journal entry made at the end of accounting periods that involves shifting data from temporary accounts on the income statement to permanent accounts on the balance sheet. Temporary accounts include revenue, expenses, and dividends, and these accounts must be closed at the end of the accounting year.
Closing the books is the process of verifying and adjusting month-end or year-end balances of specific accounts to prepare reports on the company’s performance over that period. Closing the books is an important part of the accounting cycle, and serves as a cutoff point for transactions. This cutoff point is created by zeroing out the balances in income statement accounts so those accounts can start fresh at zero for the next period
This is 4 steps in the closing process:
Close revenue accounts: First, all the revenue accounts are closed to Income Summary, which is a temporary account used during the closing process.
Close expense accounts. After expenses are closed to Income Summary, the balance in that account is net income for the period.
Close Income Summary: The Income Summary is then closed to Retained Earnings to track the company’s cumulative earnings.
Close dividends. Cash distributions or dividends paid out to owners during the period need to be closed out to Retained Earnings. So the balance reflects the earnings that are retained by the company.
That’s all for theory. Here is the closing report would looks like:
We are going to reuse the dataset VAS_GL from the previous lesions for this report. Go back to the previous chapter to create one if you haven’t. Let’s start..
Go to Report Setup > Data View and click on the plus sign to add a new report.
Enter VAS_CLS_PE in the report code box. Then on the data source lookup box, select VAS_GL dataset, then save it.
For Period End Closing reports, we want to have reports for a specific accounting period. So it should be a filter value. Besides that, in this specific sample data, we also want to exclude the KC journal type, which are period closing postings.
In our sample data, the revenue accounts are running from 511 to 599 and account 711 for the other income.
Similarly, the expense accounts are running from 611 to 699 and account 811 for the other loss.
The Income Summary account is 911.
So the accounts that need to be closed are going to run from 511 to less than 91199.
We may also just select the accounts of type P, meaning Profit and Loss accounts
Drag the period from the left panel into the filter grid above. The value inputs in the From Value and To Value cells would be passed into a report template when running as the report variables.
GIve the period any value, say, 2020011.
Drag the accountcode into the filter grid, enter 511 to the From Value and 91199 to the To Value boxes.
Then drag the following fields to the below output grid, then save it.
period select period from LA dataset.
accountcode select account code from LA dataset.
accountname select account name from COA dataset.
amount select posting amount from LA dataset. Select SUM aggregation that would group the posting amounts into groups with the same period, accountcode and accountname values, thencomputes a sum of the amounts of all transactions in each group.
When running this report template, the report data would look like this, negative means debit and positive means credit:
Click on the download icon to get the Excel template for this report. Go to the <#Config> sheet. Look at the cell A10 and B10.
IMPORTANT:
You must enter your report code in cell B10. So cell B10 MUST have the value CLS_PE. This is where the report template will feed all the data into its underlying dataset as you define in the output grid.
Cell A10 gives the underlying dataset a name which is Report in our example.
Remove the REPORT_NAME sheet and replace it with this new, fancy income statement form. Make sure that this form is the first sheet in the Excel workbook.
Open the report template and go to the configuration sheet
You can filter the data you are going to use directly in the template by writing filters in column C of the configuration sheet, which is applied to the data table defined in column B.
Let use row 13 to define a new data table, named Revenue, from data table CLS_PE in row 10. Also, this new table would be filtered to select only the revenue accounts.
Enter Report into cell B13, Revenue in cell A13, and (accountcode > '511' AND accountcode < '599') OR accountcode = '711' in cell C13 as follows:
Column D of the configuration sheet is for sorting data. In this example, we sort the data table Revenue by period and then accountcode.
Similarly, let use row 14 to define a new data table, named Expense, from data table CLS_PE in row 10. Also, this new table would be filtered to select only the expense accounts.
Enter Report into cell B14, Expense in cell A14, and (accountcode > '611' AND accountcode < '699' ) OR accountcode = '811' in cell C14
Now, let’s extract these above values.
Create 2 tag-names called <#RevenueTotal> and <#ExpenseTotal>, as follows:
Tag name <#RevenueTotal>:
Cell M19: RevenueTotal
Cell N19: <#Evaluate(-1 * <#Revenue.amount>)>
Tag name <#ExpenseTotal>:
Cell M20: ExpenseTotal
Cell N20: <#Expense.amount>
Let’s explain it:
Cell M19 defines a tag name RevenueTotal that would be used in the presentation sheet.
Cell N19 is the value of the tag RevenueTotal. It would take a value in column amount of a current record of the table Revenue, multiply by minus one. Note that the revenue amounts are credit so they are negative. This would make them positive. We are going to see how the current record is taken later in this chapter.
Cell M20 defines a tag name RevenueTotal.
Cell N20 is the value of the tag RevenueTotal. It would take a value in column amount of a current record of the table Expense,.
Next is to define how the data from table Revenue and Expense would be used. We do this with a named range. Go to excel menu Formulas, click on Name Manager, and define the following named ranges:
__Revenue__: ='Month End Closing'!$10:$11
__Expense__: ='Month End Closing'!$12:$13
The Month End Closing sheet is also called a presentation sheet. Let’s extract data from these data tables.
Go to rows 10, 11 and enter these tags:
Cell B10: KC-<#PeriodF1>
Cell C10: Close revenue accounts for period <#Revenue.period>
Cell D10: KC
Cell E10: <#PeriodF1>
Cell F10: <#TransDate>
Cell G10: <#Revenue.accountcode>
Cell H10: <#Revenue.accountname>
Cell I10: <#RevenueTotal>
Cell J10: =IF(I10>0,”D”,”C”)
Cell B11: KC-<#PeriodF1>
Cell C11: Close <#Revenue.accountcode> to Income Summary
Cell D11: KC
Cell E11: <#PeriodF1>
Cell F11: <#TransDate>
Cell G11: 911
Cell H11: Income Summary
Cell I11: = I10
Cell J11: =IF(J10=“D”,”C”,”D”)
Recall we defined the band __Revenue__ that takes 2 full rows 10 and 11 of the presentation sheet: 'Month End Closing'!$10:$11. So when running the report, these tags would get their values from a current record of the Report table.
Cell J11 is just the inverse of cell J10. So if J10 is debit then J11 would be credit and vice versa. Cell J10, in turn, is the inverse of total revenue amount on that account. Recall the revenue amount is credit if positive.
So when running a report, row 10 would zero out the revenue account and row 11 would post a compensated amount into the account 911, which is the Income Summary account.
Similarly, go to rows 12, 13 and enter these tags
Cell B12: KC-<#PeriodF1>
Cell C12: Close expense accounts for period <#Expense.period>
Cell D12: KC
Cell E12: <#PeriodF1>
Cell F12: <#TransDate>
Cell G12: <#Expense.accountcode>
Cell H12: <#Expense.accountname>
Cell I12: <#ExpenseTotal>
Cell J12: =IF(I12>0,”C”,”D”)
Cell B13: KC-<#PeriodF1>
Cell C13: Close <#Expense.accountcode> to Income Summary
Cell D13: KC
Cell E13: <#PeriodF1>
Cell F13: <#TransDate>
Cell G13: 911
Cell H13: Income Summary
Cell I13: = I12
Cell J13: =IF(J12=“D”,”C”,”D”)
Recall we defined the band __Expense__ that takes 2 full rows 12 and 13 of the presentation sheet: 'Month End Closing'!$12:$13. So it works just like the band __Revenue__
Now let's explain our formular:
We defined a named range __Revenue__ attached to the cell 'Month End Closing'!$10:$11
The band __Revenue__ is matched with the table Revenue that we defined in cell A13 in the configuration sheet.
The table Revenue derives its data from the table Report defined in cell A10, by filtering just the ones with the account code between 511 and 599 or account code 711 from all transactions of the CLS_PE report data.
When running this report template, the band __Revenue__ would iterate through the Revenue result set, and for each record, it inserts 2 full rows starting from row 10, 11 and feeds these rows with the data of the current record.
In our case, row 10 and 11 would be fed with this record
And row 12, 13 with this. So on and so forth…
Recall, this is what we defined In row 10 and 11 of the presentation sheet
Let’s look back at the definition of these tags, cell G10, H10 and I10 would take the values in the field accountcode, accountname, amount of the table Revenue. Cell G11 would be 911, H11 is Income Summary and I11 would be equal to I10.
The cell E10 and E11 would take a user’s input From Value, which is stored in the Filters table in the field period.
Cell F10 and F11 would take values from a tag function <#TransDate> with handy use of the excel function DATE to get the last date of the reporting period. Tip: Day zero of the next month would return the last day of the current month.
<#Evaluate(TEXT(DATE(LEFT(<#PeriodF1>,4),RIGHT(<#PeriodF1>,2)+1,0),"YYYY-MM-DD"))>
The whole process is repeated for row 12, 13… until it reaches the last record of the table Revenue.
Finally, go to rows 14, 15 of the presentation sheet and enter these tags
Cell B14: KC-<#PeriodF1>
Cell C14: Close Income Summary
Cell D14: KC
Cell E14: <#PeriodF1>
Cell F14: <#TransDate>
Cell G14: 911
Cell H14: Income Summary
Cell I14: =SUMIFS(I10:I13,G10:G13,911,J10:J13,"C")-SUMIFS(I10:I13,G10:G13,911,J10:J13,"D")
Explain: This is excel SUMIFS function that subtracts the total debit amount from total credit amount for the Income Summary account 911
Cell J14: =IF(I14>0,”D”,”C”)
Cell B15: KC-<#PeriodF1>
Cell C15: Close 911 to Retained Earnings
Cell D15: KC
Cell E15: <#PeriodF1>
Cell F15: <#TransDate>
Cell G15: 421
Cell H15: Retained Earnings
Cell I15: = I14
Cell J15: =IF(J14=“D”,”C”,”D”)
The rows 14 and 15 are not covered in any named ranges. So they are just simply pushed downward. The magic is excel would automatically expand the SUMIFS input ranges everytime a new row is inserted in between the input range.
Upload and run this report template. You should get this:
For your convenience, the complete report template can be downloaded from here: Month End Close
Till now, we have been going through very important concepts of KNOWINS reporting engine, consisting of filtering data, using band and named range. Next, we are going to learn how to collect report outputs and into entries in a ledger book..
And that brings us to the next concept: User Controls.
To add report variables click on edit icon, then expand the left panel:
