Table of contents
Objective and Scope
Objective of this operation
Scope
Task description
Lease Reconciliation Account update
- Lease Reconciliation Account Analysis
1 - Objective and Scope
Objective of these operations
The purpose of these tasks is to ensure that all activities are performed in order to guarantee the monthly analysis and clearing of the IFRS 16 rents, and also to monitor if the contracts are setted up correctly.
Scope
This procedure is applied to all WW companies under IFRS 16.
2 - Task description
Lease Reconciliation Account Reconciliation
The Lease Reconciliation Account is the account used from FSL and AP, where both rents and invoices are posted.
Lease Reconciliation account WP1: 56030120
Lease Reconciliation account PF1: 5112100040
The information in these accounts are reflected on the Lease Reconciliation Control file which is updated every Monday mornings. This file is used both from FSL and AP at WW level.
Go to transaction /FBL3N/ and insert the account and the necessary company code, then select "all items" to see all the documents posted, or "open items" to see the open documents that are not yet cleared.
I can also choose to check only one or more specific contracts ate the same time, by going in dynamic selection and insert the RE-FX number preceded by a * in the field "Assignment"
Open items:
This shows that for this specific company we have only one open item, which is the rent (S4) of April. This has not been reconciled probably because we didn't receive April's invoice yet. It will be AP to look for the invoice or request it to the supplier.
All items:
In this case you can see that the first 4 lines are the rents (S4) of contract 3470/400033 respectively of January, February, March and April 2021 (see Text). 3 out of 4 has been reconciled with the invoices already. For the rent of April 2021 the status is red, this means that it is still open and needs to be cleared (no clearing doc. Number exists for this line).
The last 2 lines are the invoices (RP – Invoicing Plan in this specific case, but you can find other codes for the invoices like AB: clearing, RP/RO: self-billing invoices, etc.). The 5th line has been created for a higher amount, but if you click on the clearing document you can check in details:
Here you can also see the invoice in the Attachment list
In this specific case the invoice has been created for 2 months, this explains the higher amount.
In the Document header, you can see in details what type of document has been posted, who/when posted it, etc.
LRA Update
LRA file Update:
Download file BW_QRY_CPFICX02_0001 open items and through ANALYSIS update the dates to current day in PROMPTS. When updated select column AB and in HOME General click on More Number Formats number and make sure there is no tick on Use 1000 Separators (.)
Duplicate sheet Current Situation on LRA and name it with the date of the previous week, when the data has been extracted (normally previous Monday). Block the sheet by clicking on the right on the sheet and using Protect Sheet (custom giving access to Nicole and Paola).
Copy text from line 3 of BW_QRY_CPFICX02_0001 open items and paste it on LRA Current situation from line 2. Scrolling down after the copied part (blue one), there are left the old items in white. Select them and click on delete rows.
To check if everything has been copied correctly, check column AB on both files (Amount KEUR on LRA and Amount EUR at Key date on BW) to see if the amounts matches (can be rounding).
BW_QRY_CPFICX02_0001 open items can be closed and in the meanwhile, we can start update the other BW, BW_QRY_CPFICX02_0001 documents posted YtD 2021 . Here in Prompts put the current date and in Posting Date, the date until the current day. This file serves to update the IFRS 16 - KPI (new) file on Google Sheet.
Then on LRA sheet Template change the date in the yellow part with the date of the old sheet from previous week. Then change the dates in the formulas in columns AE, AG, AI
Copy first line from AC2 to AK2 and paste from AC3 to AK7 to update the formulas.
Copy again the first line from AC2 to AK2 and copy in sheet Current Situation from column AC2 (concatener) to AK2 (priority). Copy again the first line from AC2 to AK2 and copy from second line until the end still in the range AC to AK. Still in Current sheet copy again from column AC to AK from line 2 and then paste as value to eliminate the formulas. The only column with the formula must be AL (Unique key). Using a filter, check if in column AI (Responsible for next step) appears the voice CLEARED. If yes, substitute it with Purchasing Operation (this means that the items were not cleared) and remove the filter.
Make a copy of the file and save it in LRA Backup inserting in the name the date of the previous week.
Now you can delete the old sheet of 2 weeks ago and remove the color in the blocked copied sheet of the previous week
In file IFRS 16 - KPI (new) in sheet Progress View copy from column C3 (date) to G3 (check) until the end and paste as value starting from line 4, in order to have the historical and add the new line for the current week. In line 2 change the date to current day, eliminate the value on D2 and update the number of open item in F2. To confirm the number of open items, still in file IFRS 16 - KPI (new) check sheet Current week - open items and it has to be the same number on the LRA in Current Situation.
Insert the number of Open Items on F2 in file IFRS 16 - KPI (new).
Update the number of Document Posted D2 inserting the total amount found in column I from BW file BW_QRY_CPFICX02_0001 documents posted YtD 2021 in AA17 in yellow on file IFRS 16 - KPI (new).
The number to insert in D2 is Residual GAP AA24. The number of Document cleared on E3 will be updated automatically through the formula.
Still on file IFRS 16 - KPI (new) update Questionnaire part columns in green from R to W.
Copy the number from R2 to V2 to line 3 R3 to V3.
To update the file on GDrive IFRS16 Rec account – Auto, open BW file BW_QRY_CPFICX02_0001 cleared items YtD 2021. In sheet cleared items, cancel title Asset Class of column T.
Update the Prompt by using current day and the previous day on Clearing date field:
Once it's updated, put again title Asset Class on column T and do the refresh on sheet Pivot Table.
In file IFRS16 Rec account – Auto sheet Pivot per week add a comment asking Ricardo Nunes to update the figures of the week or missing previous weeks if necessary. To update on IFRS16 Rec account – Auto the sheet Clearing Doc, copy from BW file BW_QRY_CPFICX02_0001 cleared items YtD 2021 all the lines starting 4 from A to E (Asset class) until the end, without the Grand total.
Paste the copied lines on IFRS16 Rec account – Auto sheet Clearing Doc starting from column B2 in yellow.
After pasting, if present, eliminate the lines “in excess” from the previous week.
On file IFRS 16 - KPI (new) update the document cleared part. Substitute the number on AA8 yellow with the grand total on Pivot on file BW_QRY_CPFICX02_0001 cleared items YtD 2021
