Statement Tracker/Recon Process (Copy)
Amber Schmitz
|
81 steps
|
5 minutes
1
In File Explorer, click "Statements", currently found G:\Finance\PROCURE TO PAY\Statements
2
Click "03-Statement Log"
3
Click "Coordinators"
4
Click "Statement Tracker_ APC Name_2023"
5
Click "‎Statements"
6
From Tracker, Maximize if needed/desired
7
Click "Formula Bar" Enable Content
8
Click here:
9
From File Explorer, Click "Name"
10
From Tracker, Click "Line down" to scroll down to Supplier Site to be worked next.
11
From File Explorer, Click the next statement needed to add to statement tracker.
12
Make any necessary formatting adjustments to your spreadsheet as per your preference.
13
Switch back to the statement tracker and begin filling in fields for the relevant supplier.
14
Once a statement has been received, it will not need to be requested a 2nd or 3rd time. Fill in these fields with N/A.
15
Enter the received date here.
16
Use the drop down to select a statement status (or the autofill feature).
17
Check to see if buckets are included on statement. In this case, they are not.
18
From the Tracker, scroll to the left for Aging Bucket Templates. Select either one needed based on the information being provided on the statement being worked.
19
In this case, use "Template-With Due Date Provided"
20
From the supplier statement, copy and paste the info needed to fill out the template selected on the tracker.
21
Verify that the statement received date is updated in field B2.
22
Make sure the formulas in columns D & E are filled in for all invoices.
23
Scroll back up and select the pivot table.
24
Click "PivotTable Analyze"
25
Click "Refresh"
26
Verify the pivot table data is correct.
27
From the Tracker, scroll back over to the current months tab.
28
Fill in the bucket information on the tracker using the data from the template.
29
If there is no amount for a specific bucket, enter 0. All buckets should have a number and the statement total formula should match the supplier statement total.
30
If there are balances in any bucket over 31+ days, note the reasons for these balances using the drop down or autofill feature.
31
Once Tracker data is filled in, start the reconciliation process. Log in to Oracle to access the recon tool.
32
From the Oracle Home page, select Tools.
33
Select Reports & Analytics.
34
Select Browse Catalog on upper right hand side of page.
35
Under Invoice Details_Statement Recons, click Open
36
Under Supplier, click the drop down.
37
Click Search.
38
Type in the supplier name.
39
Click "Search"
40
Double-click here to select the supplier needed.
41
Once supplier name is selected, click OK.
42
Enter the date range as needed for the statement being worked.
43
Click OK
44
Scroll down to the bottom and click Export.
45
Export Formatted Data to Excel.
46
Once exported, click OK.
47
Open the exported Excel file.
48
Click Sort & Filter to add Filters
49
Select Column A
50
Click "Data"
51
Click "Text to Columns..."
52
Click here:
53
Filter Column O
54
Click "(Select All)" to deselect everything
55
Click 0 and OK
56
Select filter for Payment Status Name
57
Deselect Fully paid or deselect "Select All" and filter only for Not Paid.
58
Click OK
59
Select filter under Validation Status.
60
Remove "Canceled"
61
Click "OK"
62
Select all of the 0's that remain.
63
Control F and Click "Replace"
64
Under Find what, put in 0
65
Under Replace with, Type "Processed, pending pymt" or whatever you want to use to indicate the invoice has been processed but not yet paid.
66
Click "Replace All"
67
Click "OK"
68
Click here:
69
Clear all the filters
70
From the supplier statement, begin your VLookup using the invoice number.
71
From the Recon spreadsheet, select all for your vlookup table data.
72
From the supplier statement, finish the vlookup formula using column 15,false.
73
Select the formula and drag down for all invoices.
74
Click "Page up"
75
Adjust your column to fit if desired.
76
While all data is still selected, do Ctrl+C.
77
Then right click and Paste Special
78
Click "Values"
79
Click OK
80
If there are payment numbers you can do another Vlookup in the next column to pull in the payment date info (column 16)
81
Any #N/A's will need to be researched and have the fields updated with information found. Ex. Invoice missing/requested copy, DM#/copy provided to supplier, ect.
