How can I use Excel for Accounting?

If the G/L needs a re-index due to a programming fix or setting change use the following analytics to determine any G/L balance changes. Users re-opening an older year are advised to follow the procedures if there is a significant amount of data in the current year.

1. Copy the attached GL Monthly Balances.IRW file into your windows environment.
2. Go to Analytics > Advanced Report Writer and click New to begin a report.
3. In the Definition tab, assign a name such as GL Balances Monthly.
4. In the Definition tab, uncheck “Generate Layout.”
5. In the Settings tab, import the GL Balances Monthly IRW file.

   

6. Export the ARW for the current fiscal year to show each period’s *beginning* balances.
7. Ensure all active users are off the system to avoid record locks and errors.
8. Run the re-index at Accounting > General Ledger > Re-Index General Ledger. Expect the process to take several minutes.
9. Export a second ARW to Excel immediately upon completion of the re-index.
10. Allow system users to resume activity.
11. Compare the balance changes. It is normal to see “equal and opposite” changes in complementary G/L accounts like Sales and Sales Returns.

    Accounts commonly affected by re-index include Inventory, Non-Inventory, Sales, Sales Returns, Cost of Goods Sold and Inventory Adjustments.

If an accounting period is closed late

Suppose accounting period 1217 is left open for a few extra days. This will result in additional data in December’s accounting period that actually belongs to January.

1. Run a G/L Report at Accounting > General Ledger > G/L Inquiry. Choose the highest level of detail.

   

2. Convert the debit and credit to a single column.
3. Strip the additional columns out of the sheet to appear like this:

   

4. Sort the sheet by Column E (date).
5. Strip out all of the rows that have the correct period and date.
6. The results will show all January-dated items in December’s period.
7. Sort the sheet by G/L Account.
8. Insert subtotals by G/L account number.
9. The subtotaled sheet will provide the summarized G/L effect of the late close as follows:

   

10. The summarized data is the revers* of what must be entered to December’s accounting period.
11. Go to Accounting > General Ledger > Journal Entry and choose type “accrual." This entry will post to the selected period followed by a reversal in the next period.
12. Make the December entry as the *reverse* of the Excel results.

    Both periods will be corrected. Note that Sales Journal driven analytics will continue to report the transactions in the wrong period. The financial reports will be correct.

When cash receipts don’t easily reconcile, use the “sort, filter and export” features to convert a current reconciliation to Excel.

1. Open the current reconciliation at Accounting > Bank Reconciliation > Bank Reconciliation.
2. Filter the content for CR transactions. This includes deposits and reversals from cash, check, credit card or ACH.
3. Right-click anywhere in the active screen to export to Excel.
4. Transactions are hierarchical in the Excel sheet, with the “parent” in bold and the “children” nested below. An additional column indicates TRUE for temporarily cleared items and FALSE for open items.

Use this Excel process to determine the cause of a difference in the account used for POS transaction daily clearing.

1. Run the subledger at Sales > Order > P.O.S. Open Deposits Report. This report is available by branch or for the company as a whole.
2. Compare the balance in the report with the POS Deposit account. Multi branch companies may need to add several accounts. Note that a “deposit available” on the report is represented by a credit balance in the G/L account.
3. If there is a difference, export the POS Deposits account detail to Excel.
4. Strip out duplicate columns until these remain: G/L Account, Account Description, Month, Source, Date, Reference, Reference Description, Reference Debit and Reference Credit.
5. Convert the Reference Debit and Reference Credit columns to a single value.
6. Sort the sheet by Reference Description, then Period, then Date.