Capture GL balances before and after a G/L reindex procedure

If the G/L needs a reindex due to a programming configuration change, use the following procedure to evaluate any G/L balance changes. Note that this process uses Advanced Report Writer, which may require additional training.

Import the IRW File

1. Download attached GL Monthly Balances. IRW file from the Advanced Report Library in DDI’s Customer Portal.
2. Go to Analytics > Advanced Report Writer, and then click New.
3. On the Definition tab, assign a name such as GL Balances Monthly.
4. On the Definition tab, clear the Generate Layout check box.
5. Click the Settings tab, and then import the GL Balances Monthly IRW file by clicking Import Layout.

   

6. To run the report, on the Definition tab, under Criteria, insert the current fiscal year. Click Save and then Run Report.
7. Export the ARW for the current fiscal year to show each period’s beginning balances.

Run the reindex

1. Ensure that all active users are off the system to avoid record locks and errors.
2. Go to Accounting > General Ledger > Re-Index General Ledger.

   Expect the process to take several minutes. You may choose to run it at the end of the day.

3. Export a second ARW to Excel as described above immediately upon completion of the reindex.
4. Allow system users to resume activity.
5. Compare the balance changes. It is normal to see “equal and opposite” changes in complementary G/L accounts like Sales and Sales Returns.
6. Accounts commonly affected by reindex include Inventory, Non-Inventory, Sales, Sales Returns, Cost of Goods Sold, and Inventory Adjustments.

   See also General ledger re-indexing

Export to Excel

Follow these instructions to work with the files in Excel and calculate any balance changes.

1. Create a workbook with tabs for Before, After, and Changes.

   

2. Copy the data from the raw files into the Before and After tabs.
3. Compare the number of accounts. If accounts were added or removed by the reindex, the files will not line up when compared.
4. Insert blank lines to ensure that both files are aligned.
5. Copy the account number and name columns into the Changes sheet.
6. Copy the Monthly Balance row into the Changes sheet.
7. Insert a formula to compare the balances from the after sheet to the before sheet. Note the formula in the image below.

   

8. Enter only one formula and use the drag and drop capability to copy it to the rest of the sheet.
9. Copy the results to a blank page.
10. Strip out any lines with no changes.
11. The result is a list of accounts with changes.

    It may be helpful to move the rows to show accounts that changed by an equal and opposite amount. Note in the example below similar changes have been lined up for easy interpretation.

    

Advanced Report Writer Specs

You can also build your own unformatted Excel export using the following specifications.