2019
SOP for Sales Reconciliation
RECONCILIATION BETWEEN ZSD25, ZSD09 AND GL BALANCES FOR SALES
Table of Contents
1.
Download VFX 03
2
2.
Download GL Dump for the Month
3
3.
Download ZSD9
6
4.
Download ZSD25
8
5.
Sales Reconciliation File
9
Addendum to the SOP (steps to be followed to create a Query):
12
A. ZSD9
12
B. ZSD25
19
C. GL
22
1
1. Download VFX 03
1.1 Download VFX3 from SAP to identify the billing documents stuck for accounting process. Refer below
screen shot for the report run.
1.2 The report will look like following:
Filter on ‘Billing Date’ column to find out the billing documents stuck in current Financial Year (Starting
April 2019). All the documents must be cleared before closing of the sales figure for the month.
2
1.3 If for any reason the same cannot be processed in current month, reasons for the same shall be noted. Call
must be taken as to whether these transactions are to be included in the current month sales.
1.4 If yes, then manual adjustment entry will be required to be passed in Accounting module for the same
which must be reversed next month, and proper sales entry be booked via billing document be booked in
next month.
1.5 While passing the above manual entry it must be insured that ‘PGI’ for such transaction is completed in
SAP to have the inventory and COGS reflecting the appropriate number reflecting the billing document.
1.6 In case call is taken not to include the sales transaction in current month, then COGS and Inventory must
be adjusted appropriately in Accounting Module in case ‘PGI’ for the transaction is completedǤ
1.7 The transactions reflecting in VFX3 will appear as completed sales in ZSD09 but not in GL or ZSD25. These
reports should be adjusted for VFX3 transaction based on call taken in 1.4 to 1.6 above for them to reflect
the balance in sync with each other.
2. Download GL Dump for the Month
2.1
Use TCODE ‘FBL3N’ to get Sales related GL’s for the monthǤ Attached excel lists the GL’s to be used for GL
data dump.
Sales TB
grouping.xlsx
Above list is based on “Grouping” sheet in Financial statements excel file used for monthly financials
preparation.
2.2
Use T code ‘FBL3N’ to download the GL dumps from SAPǤ Refer below screen shots for the same.
3
Fill in the fields highlighted in ‘Yellow’Ǥ For ‘G/L Account’ field click on the arrow Icon indicated in Green. It will
give pop up as below.
Select ‘Single Values’ tabǤ Copy the list of GLs’ (only the Codes and not descriptions) and paste it using the icon
highlighted in ‘Red’Ǥ Do not do (Ctrl + V) since it will paste only an incomplete list of GLs’. Click on icon indicated
in ‘Blue’ after GLs are pasted in placeǤ
2.3
Use layout “/Practus_3GL” for downloading the GL report every month to keep the format consistent.
2.4
Once report is run download it to .txt (text file) once its populated in SAP. Refer following screen shot for
the same
4
2.5
Create a new folder ‘GL’. Save the .txt file in this folder in the ‘Save As Pop Up’Ǥ
2.6
Going forward, every monthly export of ZSD25 needs to be saved in this folder.
2.7
Open the excel file (GL Pivot) in which we have created the GL Query (refer Addendum at the end of this
SOP to know how to create the query/if you are creating ‘GL Pivot’ excel for the first time)
2.8
Go to the Data Tab --> Queries and Connections
2.9
You will see the GL Query under Other Queries
2.10
Right click on GL and click Refresh
2.11
You will see the number of rows (mentioned below GL) have increased as per the GL txt dump we have
added to the folder
2.12
Also, the Pivot Table gets refreshed automatically incorporating the new month dump added to the GL
Folder (if the pivot does not refresh automatically, right click on the table and select refresh)
2.13
Now we get the PivotTable of GL which would act as an input to the Sales Reconciliation File
2.14
Copy these numbers from the Pivot Table and paste it as per the Sales Reconciliation format, in the
respective columns
2.15
Follow the above steps for every month (Export the dump to txt from SAP --> Save it in the GL Folder -->
Open the GL Pivot Excel --> Refresh the Query and the Pivot table)
2.16
Always ensure that the balance of each GL in the GL dump so downloaded is matching with the latest TB
balance for Control Check. If there are errors in download or any GL is missed same shall be included back
in the GL dump for the month and steps 2.6 to 2.8 be followed again for the same.
2.17
Check for all values in column ‘DocǤ Type’ for values other than ‘RV’Ǥ These are manual Journal entries
passed in system which will not reflect in ZSD9 and ZSD25 reports.
2.18
These manual entries shall be checked for ‘user’ (who passed the entry) and the purpose. Appropriate
impact of the same shall be given in ZSD9 and ZSD25. (i.e. Add details for Customer Name, Product details
like SKU, Article code, Brand, Customer type, Qty, Value, salesperson, profit center, Invoice reference be
added to ZSD09 and ZSD25 reports manually).
2.19
As a rule, there shall not be any manual entries in GL’s pertaining to Sales except in cases mentioned in
point 1.4 to 1.6 above where a call is taken to manually adjust billing documents stuck in VFX3. Hence any
other manual entries must be avoided to the extent possible.
5
3. Download ZSD9
3.1 Refer below screen shot for download of ZSD9
Fill in the values as shown in the fields highlighted above.
3.2
For Profit Center, click on the arrow button highlighted above and include profit center from the GL dump
taken earlier in ‘Single Value’ tab similar to shown in 2Ǥ2 aboveǤ
3.3
The PC List will generally have codes: 2520, 2525, 2545, 2555, 2557 and RFDC.
3.4
Create a new folder ‘ZSD09’ on your computer
3.5
Export the report to a Text file (.txt) as shown in 2.4 above and save the file in the folder ‘ZSD09’.
3.6
Going forward, every monthly export of ZSD9 needs to be saved in this folder.
3.7
Open the excel file (ZSD09 Pivot) in which we have created the ZSD09 Query (refer Addendum at the end of
this SOP to know how to create a query)
3.8
Go to the Data Tab --> Queries and Connections
3.9
You will see the ZSD9 Query under Other Queries
3.10
Right click on ZSD9 and click Refresh (refer screenshot below)
6
3.11
You will see the number of rows (mentioned below ZSD9) have increased as per the ZSD9 txt dump we have
added to the folder
3.12
Also, the Pivot Table gets refreshed automatically incorporating the new month dump added to the ZSD09
Folder (if the pivot does not refresh automatically, right click on the table and select refresh)
3.13
Now we get the PivotTable of ZSD9 which would act as an input to the Sales Reconciliation File
3.14
Copy these numbers from the Pivot Table and paste it in as per the Sales Reconciliation format, in the
respective columns
3.15
Follow the above steps for every month (Export the dump to txt from SAP --> Save it in the ZSD09 Folder -->
Open the ZSD09 Pivot Excel --> Refresh the Query and the Pivot table)
7
4. Download ZSD25
4.1
Use TCODE ZSD25 in SAP to download the report. Refer below screenshot.
ZSD25 is populated at a month consolidated level and does not give the line item details as available in ZSD9 and
GL dumps. Select ‘Online’ radio button under ‘View Data’ and Layout as ‘/PRACTUSZS25’
4.2
Create a new folder ‘ZSD25’
4.3
Once the report on SAP is populated, download the same to a Text file (.txt) in similar manner as 3.4
above and save the file in the folder ‘ZSD25’ as created above
4.4
Going forward, every monthly export of ZSD25 needs to be saved in this folder.
4.5
Open the excel file (ZSD25 Pivot) in which we have created the ZSD25 Query (refer Addendum at the end
of this SOP to know how to create the query)
4.6
Go to the Data Tab --> Queries and Connections
4.7
You will see the ZSD25 Query under Other Queries
4.8
Right click on ZSD25 and click Refresh
4.9
You will see the number of rows (mentioned below ZSD25) have increased as per the ZSD25 txt dump we
have added to the folder
4.10
Also, the Pivot Table gets refreshed automatically incorporating the new month dump added to the ZSD25
Folder (if the pivot does not refresh automatically, right click on the table and select refresh)
4.11
Now we get the PivotTable of ZSD25 which would act as an input to the Sales Reconciliation File
8
4.12
Copy these numbers from the Pivot Table and paste it as per the Sales Reconciliation format, in the
respective columns
4.13
Follow the above steps for every month (Export the dump to txt from SAP --> Save it in the ZSD25 Folder
--> Open the ZSD25 Pivot Excel --> Refresh the Query and the Pivot table)
5. Sales Reconciliation File
With the Pivot tables of ZSD9, ZSD 25 and GL file ready as per the above steps, the respective pivots shall look as
following:
GL
Profit Center Apr
May
Jun
Jul
Aug
Grand Total
Gross
2520
7,687,556
6,419,882
4,670,046
6,966,081
2,453,685
28,197,249
2525
7,813,520
9,016,665
11,239,608
13,867,135
10,286,618
52,223,546
2545
678,860
574,536
496,736
1,337,334
2,675,901
5,763,367
2555
3,833,658
3,450,307
3,482,753
3,234,328
2,221,201
16,222,246
2557
66,742
430,017
257,522
360,848
267,100
1,382,228
RFDC
3,133
26,339
68,698
91,164
-
189,335
Blank
-
-
249,881
-
-
249,881
Gross Total
20,083,470
19,917,746
20,465,242
25,856,890
17,904,504
104,227,851
Rev/Ret
2520
-
-
-
(159,258)
-
(159,258)
2525
(14,477)
(37,354)
(3,275)
(72,503)
(64,263)
(191,872)
2545
(10,504)
(117)
(2,396)
(230,606)
(233,028)
(476,652)
2555
-
(1,222)
(341)
(6,472)
(16,447)
(24,483)
2557
-
(3,389)
-
(29,663)
(27,488)
(60,540)
Blank
(167,335)
-
-
(249,881)
-
(417,216)
Rev/Ret Total
(192,317)
(42,082)
(6,012)
(748,383)
(341,227)
(1,330,020)
Grand Total
19,891,153.04
19,875,663.41
20,459,229.92
25,108,506.82
17,563,277.19
102,897,830.38
ZSD 25
Profit Ctr.
Apr
May
Jun
Jul
Aug
Grand Total
Gross
2520
7,687,556
6,419,882
4,670,046
6,966,081
2,453,685
28,197,249
2525
7,813,520
9,016,665
11,239,608
13,867,135
10,286,618
52,223,546
2545
678,860
574,536
496,736
1,107,215
2,675,901
5,533,248
2555
3,833,658
3,450,307
3,482,753
3,234,328
2,221,201
16,222,246
2557
66,742
427,286
254,399
360,848
267,100
1,376,374
RFDC
3,133
26,339
68,698
91,164
189,335
(blank)
-
-
Gross Total
20,083,470
19,915,015
20,212,238
25,626,771
17,904,504
103,741,998
Return/Rev
2520
-
-
-
(159,258)
-
(159,258)
2525
(14,477)
(37,354)
(3,275)
(72,503)
(64,263)
(191,872)
2545
(10,504)
(117)
(2,396)
(487)
(233,028)
(246,533)
2555
-
(1,222)
(341)
(6,472)
(16,447)
(24,483)
2557
-
(13,523)
(4,308)
(29,663)
(27,488)
(74,983)
(blank)
-
-
Return/Rev Total
(24,982)
(52,217)
(10,321)
(268,383)
(341,227)
(697,129)
Grand Total
20,058,488
19,862,798
20,201,918
25,358,388
17,563,277
103,044,869
9
ZSD 09
Profit Center Apr
May
Jun
Jul
Aug
Grand Total
01 Gross
2520
7,846,814
6,423,257
4,670,046
6,966,081
2,453,685
28,359,882
2525
7,813,520
9,028,498
11,239,608
13,867,135
10,286,618
52,235,378
2545
678,888
574,536
496,736
1,107,215
2,675,901
5,533,276
2555
3,833,658
3,450,307
3,482,753
3,234,313
2,221,201
16,222,231
2557
66,742
413,763
250,090
331,184
239,612
1,301,391
RFDC
3,133
26,339
68,698
91,164
189,335
Blank
01 Gross Total
20,242,756
19,916,699
20,207,930
25,597,092
17,877,016
103,841,493
02 Rev/Ret
2520
(159,258)
(159,258)
2525
(15,077)
(37,354)
(3,275)
(72,503)
(64,263)
(192,471)
2545
(10,504)
(117)
(2,396)
(487)
(233,028)
(246,533)
2555
-
(1,345)
(3,076)
(6,472)
(16,447)
(27,340)
2557
Blank
-
-
-
02 Rev/Ret Total
(25,581)
(38,816)
(8,747)
(238,720)
(313,739)
(625,602)
Grand Total
20,217,175
19,877,883
20,199,183
25,358,372
17,563,277
103,215,891
5.1
Put these in a single file and compare for the differences at Profit Center level.
5.2
Any differences within reports will be for one of the following two reasons:
a.
Transactions stuck in VFX 03 appearing only in ZSD09 and not in GL and ZSD 25.
b.
Manual entries passed in GL not appearing in ZSD09 and ZSD 25.
5.3
If the steps from 1.3 to 1.7 pertaining to VFX 3 have been followed and there are no unreconciled billing
documents, difference in 5.2.a shall not be there.
5.4
Similarly, if steps from 2.10 to 2.12 pertaining to manual entries in GL dump have been followed there
shall not be any differences.
5.5
If despite these steps there is a difference between these reports, it can be drilled down to the lowest level
of granularity with the help of the Pivots prepared as per steps in pint 2, 3 and 4 to identify
a.
Customer in whose case there is a difference (Open the respective Pivot Table Files, and in the
Field settings, add ‘Customer/Bill-to-Name’ as a row in the Pivot Table (refer screenshot
below)
10
b.
Document references including the Invoice Number on account of which there is a difference
c.
Product, SKU, Article Code, Salesperson and even the user who has passed the relevant entry
can be tracked and targeted questions can be asked to the concerned personnel for a quick
resolution of problem.
11
Addendum to the SOP (steps to be followed to create a Query):
A. ZSD9
A.1
Create a new excel file (outside the ZSD9 folder) and name it ‘ZSD9 Pivot’Ǥ Open the excel file as createdǤ
In a new worksheet, go to Data --> Get Data --> From File--> From Folder
A.2
Go to ‘Browse’ and select the ZSD09 Folder as created in step 3Ǥ4 aboveǤ
A.3
You will see a screen as below, select ‘Transform Data’
A.4
Once you select Transform Data, the PowerQuery window will open. Right click on the Extension Column
--> Go to Transform Data --> Select lowercase (refer screenshot below)
12
A.5
Select all the columns (from column ‘Name’ to column ‘Folder Path’) except the ‘Content’ column and
press delete
A.6
Press the ‘Combine File’ button next to the ‘Content’ column (refer screenshot below)
A.7
After the above step, you will see a window as shown in the screenshot belowǤ Select ‘Based on entire data
sheet’ in the Data Type Detection tabǤ Then select OKǤ
13
A.8
Now you should be able to see a preview of the ZSD09 report. Also, please note that all the changes made
to this report can be seen in the Query Settings panel on the right.
A.9
We now need to make suitable changes to this report in order to make it fit for a Pivot Table. Please
follow the instructions as below:
A.9.1 Delete Column1
A.9.2 Go to the Home tab --> Remove Rows --> Remove blank rows (refer screenshot below)
A.9.3 Then select ‘Use First Row as Headers’ in the Home tabǤ
14
A.9.4 Right click on the Profit Centre column, select change type, and select Text. Then click on
‘Replace current’ (refer screenshot below)
A.9.5 Like what we did in the previous step, right click on the Invoice Date column, select
Change Type and then Select ‘Using Locale’
A.9.6 Now select the data type as ‘Date’ and select the Locale as ‘English (India)’ and then press
OK. (refer screenshot below) You will see that the Invoice date column has now been
changed to a mm/dd/yyy date format.
15
A.9.7 Now, go to the Add column tab and select Custom Column.
A.9.8 Name the new column as ‘Classification’ and in the Custom Column Formula, paste the
below formula: =if [Gross Value]>=0 then "Gross" else "Return/Reversal" and then click
on OK
16
A.9.9 Click on the dropdown next to the ‘Profit Centre’ column, and click on ‘Load more” (refer
screenshot below)
A.9.10 This might take some time to loadǤ But once it is done, uncheck on ‘Profit Centre’ and click
OK (refer screenshot below)
`
A.9.11 Now, go to the Home tab, select on the dropdown next to ‘Close & Load’ and select ‘Close &
Load to’ (refer screenshot below) (please make sure that you click on the dropdown only)
17
A.9.12 Now, in the Import data window, select Pivot Table report and check on the box next to
‘Add this data to the Data Model’ (refer screenshot below) and then click on OK
A.9.13 This will take some time to load, but once it is done, you will see the number of rows that
are loaded in the PowerQuery. Also, you will see a PivotTable Fields Panel on the right.
18
A.9.14 From here onward, it is the same as a normal Pivot Table. Select the necessary fields that
we need in the table (refer screenshot below)
A.9.15 Right click on the Pivot table, go to PivotTable Options, go to Display and check on ‘Classic
PivotTable layout’ and click OK
A.9.16 Right click on the Classification column and select ‘Subtotal Classification’
B.
ZSD25
B.1
Create a new excel file (outside the ZSD25 folder) and name it ‘ZSD25 Pivot’Ǥ Open the file, and in a new
worksheet go to Data --> Get Data --> From File --> From Folder
B.2
Go to Browse and select the ZSD25 folder as created in step 4.2 above and then select Transform Data
B.3
Like step 1.3 above, once you select Transform Data, you will see the PowerQuery window on your
screen. Right click on the Extension Column --> Go to Transform Data --> Select lowercase.
B.4
Select all the columns (from column ‘Name’ to column ‘Folder Path’) except the ‘Content’ column and
press delete
B.5
Click on the ‘Combine File’ button next to the ‘Content’ column
B.6
Select ‘Based on entire data sheet’ in the Data Type Detection tabǤ Then select OKǤ
B.7
Now you should be able to see a preview of the ZSD25 report. Also, please note that all the changes made
to this report can be seen in the Query Settings panel on the right.
B.8
We now need to make suitable changes to this report in order to make it fit for a Pivot Table. Please
follow the instructions as below:
B.8.1 Delete Column1 and Column2
B.8.2 Go to the Home tab --> Remove Rows --> Remove blank rows
B.8.3 Then select ‘Use First Row as Headers’ in the Home tab
B.8.4 Right click on the Profit Centre column, select change type, and select Text. Then click on
‘Replace current’
B.8.5 Right click on the Month column, select Change Type and then Select ‘Date’
19
B.8.6 Click on the dropdown next to the ‘Profit Centre’ column, and click on ‘Load more’ and
uncheck on ‘Profit Centre’ and click OK (refer step 1.9.10 above)
B.8.7 Select all the columns starting from ‘Billed Qty’ until the last column ‘BUom Qty’ (press
shift and right arrow from Billed Qty column), right click on the header --> select change
type --> decimal number (refer screenshot below)
B.8.8 Now, select only the ‘Gross ValǤ’ and ‘Sale RetǤ’ Column, go to the Transform tab, click on
the dropdown next to Unpivot Columns and select ‘Unpivot only Selected columns’ (refer
screenshot below)
20
B.8.9 Now you should see two new columns in the end - ‘Attribute’ and ‘Value’
B.8.10 Go to the Add column tab and select ‘Conditional’ Column
B.8.11 Name the new column as ‘Classification’, add the conditions as per the below screenshot
and then click on OK (if Attribute contains Gross Val. then Gross else Return/Reversal)
(refer screenshot below)
21
B.8.12 Now, go to the Home tab, select on the dropdown next to ‘Close & Load’ and select ‘Close &
Load to’ (please make sure that you click on the dropdown)
B.8.13 In the Import data window, select Pivot Table report and check on the box next to ‘Add
this data to the Data Model’ and then click on OK
B.8.14 This will take some time to load, but once it is done, you will see the number of rows that
are loaded in the PowerQuery. Also, you will see a PivotTable Fields Panel on the right.
B.8.15 From here onward, it is the same as a normal Pivot Table. Select the necessary fields that
we need in the table (refer screenshot below)
B.8.16 Right click on the Pivot table, go to PivotTable Options, go to Display and check on ‘Classic
PivotTable layout’ and click OK
B.8.17 Right click on the Classification column and select ‘Subtotal Classification’
C. GL
C.1
Create a new excel file (outside the GL folder) and name it ‘GL Pivot’Ǥ Open the file, and in a new
worksheet go to Data --> Get Data --> From File --> From Workbook
C.2
Select the Sales TB Grouping File (as attached in step 2.1 above)
C.3
You will see a screen as shown below. Select Table1 on the left and then click on Transform Data
22
C.4
You should now be able to see a preview of the Sales TB grouping file
C.5
Click on the dropdown next to Close & Load and select Close & Load to
C.6
Select ‘Only create connection’ and ‘Add this to the data model’ and click on OK (refer below screenshot)
23
C.7
Now, again go to the Data tab --> Get Data --> From File --> From Folder
C.8
Go to Browse and select the GL folder as created in step 2.5 above and then select Transform Data
C.9
Like step 1.3 above, once you select Transform Data, you will see the PowerQuery window on your
screen. Right click on the Extension Column --> Go to Transform Data --> Select lowercase.
C.10
Select all the columns (from column ‘Name’ to column ‘Folder Path’) except the ‘Content’ column and
press delete
C.11
Click on the ‘Combine File’ button next to the ‘Content’ column
C.12
Select ‘Based on entire data sheet’ in the Data Type Detection tab. Then select OK.
C.13
Now you should be able to see a preview of the GL report. Also, please note that all the changes made to
this report can be seen in the Query Settings panel on the right.
C.14
We now need to make suitable changes to this report in order to make it fit for a Pivot Table. Please
follow the instructions as below:
C.14.1 Delete Column1, Column2 and Column5
C.14.2 Go to the Home tab --> Remove Rows --> Remove blank rows
C.14.3 Then select ‘Use First Row as Headers’ in the Home tab
C.14.4 Right click on the Profit Centre column, select change type, and select Text. Then click on
‘Replace current’
C.14.5 Right click on the Posting Date column, select Change Type and then Select ‘Using Locale’
C.14.6 In the Data type, select ‘Date’ and in the Locale, select ‘English (India)’
C.14.7 You should now see the Posting Date column format being change to mm/dd/yyyy format
C.14.8 Click on the dropdown next to the ‘Profit Centre’ column, and click on ‘Load more’ and
uncheck on ‘Profit Centre’ and ‘blank’ and click OK (refer screenshot below)
24
C.14.9 Now, select the ‘OffstǤacct’ Column and press delete
C.14.10 In the Home tab, click on the dropdown next to Merge Queries and select ‘Merge Queries’
(refer screenshot below)
25
C.14.11 Now you should see a window as shown below. Select the GL column in the first table.
Select ‘Table1’ in the dropdown and select GL column in the second tableǤ You should be
able to see the number of rows matching. All the rows should match. Then click on OK
(this is like a vlookup, where we are looking for the GL account in the Sales TB grouping
file, and getting the revised grouping column against each account) (refer screenshot
below)
C.14.12 You should now see a new column added to the existing GL dump. Click on the button
next to the column name ‘Table1’ and only select ‘revised grouping’ and click OK (refer
screenshot below)
26
C.14.13 Go to the ‘Add Column’ tab, and select ‘Custom Column’
C.14.14 Name the new column as ‘Classification’ and paste this formula as it is: = if
[Table1.Revised Grouping]="Returns" then "Returns/Reversal" else if [#"Amount in local
cur."]>=0 then "Returns/Reversal" else "Gross" and click OK (refer screenshot below)
27
C.14.15 Now, go to the Home tab, select on the dropdown next to ‘Close & Load’ and select ‘Close
& Load to’ (please make sure that you click on the dropdown)
C.14.16 In the Import data window, select Pivot Table report and check on the box next to ‘Add
this data to the Data Model’ and then click on OK
C.14.17 This will take some time to load, but once it is done, you will see the number of rows that
are loaded in the PowerQuery. Also, you will see a PivotTable Fields Panel on the right.
C.14.18 From here onward, it is the same as a normal Pivot Table. Select the necessary fields that
we need in the table (refer screenshot below)
28
C.14.19 Right click on the Pivot table, go to PivotTable Options, go to Display and check on ‘Classic
PivotTable layout’ and click OK
C.14.20 Right click on the Classification column and select ‘Subtotal Classification’
29