KNOWLEDGE CENTRE

How can I easily identify and reconcile differences for multiple accounts within my FC hierarchy for the month of October, 2020 after the S4HANA Migration?

This example will demonstrate how to reconcile any variances using a custom Excel reconciliation template, and the following 3 reports:

  1. The Month-End Statement of Accounts (ZFTR106) report is a Funds Management (FM) report which provides a static, month end view of the balances and activity of your FM account(s) (i.e. Funds Center (FC) or FC/Fund (FUND).
  2. Total Funding Activity (ZFTR008) report will provide summary balances for multiple accounts and will reflect the same balances as those reported on the ZFM1 and ZFTR111 reports.

The difference between the 2 reports is, essentially, the “as at” view of the balances and activity details of the information contained in the reports.

  1. The All Postings Line Item Report (ZFIR079A) will provide detailed line-by-line postings, for both Commitments and Actuals, and will help identify the transactions that have caused any variances.

STEP 1: Open the RECONCILE Month End Reports to MSA Excel spreadsheet

Click here to download a copy of the spreadsheet 

The excel template comprises a 5 tabs/worksheets, with built-in formulas to facilitate reconciliation. The tabs are:

  • STEPS – provides instructions on what reports to download, including specific layout variants to use.
  • ME VIEW OF ZFTR008 – used to summarize all of the report balances and compare totals for each account to locate variances.
  • ZFTR106 – worksheet used to summarize the Month End Statement of Accounts for October. The FIS report download will be pasted here.
  • FM_ZFTR008 – worksheet used to summarize the Total Funding Activity Report for October. The FIS report download will be pasted here.
  • ZFIR079A – worksheet used to summarize the All Postings Line Item Report for October. The FIS report download will be pasted here.

 

STEP 2 – Generate and Download the ZFIR079A – All Postings Line Item report and Paste into Excel

This step will identify the FM accounts that have had activity after October month end, (i.e. not included in the ZFTR106 month end report).

  1. Generate the All Postings Report for the group of accounts within your hierarchy. Ensure that you use the report layout = to locate the postings not included in the ZFTR106 report.
  2. Download the report output to Excel.
  3. Go to the worksheet in the spreadsheet.
    1. Paste output into cells indicated below.
    2. Use the CONCATENATE formula to combine the Funds Center or FC/Fund in column “K”. If necessary, drag the concatenated cell down for all FM accounts.
    3. Copy and paste the FM accounts into the cells indicated below. The worksheet will calculate the summarized totals for each account.

4. Paste a screenshot of the system output below the data.

 

 

STEP 3 – Generate and download the ZFTR008 – Total Funding Activity report and paste into Excel

The ZFTR008 report lists the FM accounts and their current (i.e. real-time) balances.

  1. Generate the Total Funding Activity report for the same accounts used in Step 2.
  2. Download the report output to Excel.
  3. Go to the worksheet in the Excel reconciliation file.
    1. Paste report output into cells indicated below.
    2. Use the CONCATENATE formula to combine the Funds Center or FC/Fund in column “T”. If necessary, drag the concatenated cell down for all FM accounts.
    3. Copy and paste the FM accounts into the cells indicated below.

The worksheet will calculate the summarized totals for each account.

4. Paste a screenshot of the system output below the data.

 

 

STEP 4 – Generate the ZFTR106 – Month End Statement of Accounts report and paste screenshots of the report (for each FM Account) into Excel

This report provides the output for the Total Revenues, Total Expenses and Net Totals (i.e., Control Balance) for each FM Account that has a variance.

  1. Generate the Month-End Statement of Accounts report for the same accounts used in Step 2 and the relevant period (i.e. period 6).
  2. Capture a screenshot of the Summary Report section of the Month-End Statement of Accounts report for each Funds Center and Funds Center/Fund combination.
  3. Paste the screenshots into the worksheet as indicated below.
  4. Type the Net Revenues, Net Expenses and Net Totals into the designated cells.

 

STEP 5 – Compare the ZFTR106 report results for October to the adjusted ZFTR008 balances

  1. Copy and paste all of the FC/Fund values from the FM_ZFTR008 / ZFIR079A worksheet.
  2. Create a “unique” list of the FC/Fund values that have a variance. This list will be used to “search and sum” totals from the worksheets = FM_ZFTR008 and ZFIR079A.
  3. Use the “SUMIF” formula to “search and sum”  totals from the worksheets = FM_ZFTR008 and ZFIR079A; (3a) = “FUNDS AVAILABLE” from FM_ZFTR008 and (3b) = “Payment budget” from ZFIR079A
  4. Add columns (3a) and column (3b) to get “Month-end (ME)” view of ZFTR008 balances which should agree to the balances reported on the month end MONTHLY STATEMENT OF ACCOUNT (ZFTR106); if differences exist, compare BUDGET totals
  5. Update ZFTR106 balances for comparison to “ME view” of ZFTR008 report; it is expected that rather than manually update this worksheet with the numbers (as ZFTR106 is not downloadable), the end user will visually compare the balances and note and analyze any differences.
  6. Identify the reason for any differences; these should relate to changes in the budget totals made in the “adjusting period” (i.e. Period=5 in the example).

After comparing all of the account balances (column 6), we note that 2 of the Funds Available balances do not match. At this point, departments should identify the budget transactions that will account for the variance ($70 and $100) shown below.

 

 

STEP 6 (If Required)

To locate the budget transactions, run the FMEDDW – Display Budget Entry Document report for the relevant FM accounts (e.g., FC=100123 and Funds=400135 & 470405) using variant=BUD BY PER.

After updating our comparison chart, the Adjusted Difference (column 8) for all accounts is now $0, so the reconciliation for these accounts is now complete.

If you have any questions or concerns, please contact your FAST Team representative or email .

Learn More:

Last Updated:  August 5, 2021