General Ledger Drill Down
Short video (0:42)
This feature allows you to get the General Ledger detail of an account balance, from the customized financial statements built with ExcelFSM.
Thanks to the technology of the ROMAD patent (Reading Organizing and Manipulating Accounting Data - of any accounting software), ExcelFSM, with the General Ledger Drill Down option, will read and organize all the transactions from the beginning of the year of the specified as-of date.
Using the GL Drill Down
The first thing to do is to reproduce the last audited (or reviewed, or compiled) financial statements, using ExcelFSM.
If you are not already familiar with ExcelFSM, you can click the following: Get Started With ExcelFSM. For now, you can continue reading as you will be able to use the FSM GLDD 12-31-2014 workbook sample installed on your Windows Desktop, in the folder called 'ExcelFSM Demo Files'.
The next step is to get the trial balance produced by your accounting software into an Excel file, and, to get the general ledger produced by your accounting software into a second Excel file. This should be straightforward. Typically you would choose the Export, Save As, or Print To File functions within the accounting program.
(Note: ExcelFSM with Support QuickBooks Desktop retrieves the data directly from QuickBooks, without any need to export the data into Excel files.)
If your accounting software exports to Comma Separated Value files (.csv), choose this option.
See how
If your accounting software exports to Excel spreadsheets directly, you can choose this option, but the Comma Separated Value files might be more reliable since they do not have special characters and formatting that the 'export to excel' feature might have.
If your accounting program exports or prints to text (.txt) files, you can also choose this option.
See how
If the account numbers and the account titles are in the same column in the resultant trial balance excel file, you should split it in two columns.
See how
For now, you can continue reading as you will be able to use the excel files samples TB2015 and GL2015 installed on your Windows Desktop.
You now have all you need to prepare the FSM workbook that will be used to perform the audit/review/compilation of the financial statements.
With Excel running, open the FSM workbook with the last audited (or reviewed, or compiled) financial statements reproduced using ExcelFSM.
Select the FSM tab and click the Read Trial Balance Data button
Select ‘With GL transactions’ from the following dialog
Select the trial balance excel file
The Read Trial Balance Data dialog being pre-filled according to the previous financial period, update the as-of date to reflect the new financial period.
Select the general ledger excel file
ExcelFSM then shows the following dialog to allow you to specify additional information about this general ledger file from which ExcelFSM will read the transactions data
Once the reading and organizing process is completed, the number of new accounts added to the Chart of Accounts is displayed,
the new accounts are shown at the top of the Chart, to make is easier to group these new accounts into the financial statements,
and a ‘ReadTBData printout’ is generated.
Note: The only purpose for this ‘ReadTBData printout’ is to help you verify that the trial balance was read correctly. You may print it, delete it, etc.
Group the new accounts into the financial statements
Once the grouping of the new accounts into the financial statements is done, you can click the Chart of Accounts button to reorder the new accounts and then generate a printout sheet with account groupings
From the financial statement, select an item balance and click the ’Details’ button.
The accounts grouped in the Item are shown
From the item details, double click an account's balance.
The Account Unadjusted Balance Detail is displayed (all the transactions allocated to that account from the beginning of the year)
From the Account Unadjusted Balance Detail, double click a document.
The Transaction Detail is displayed
From the Transaction Detail (idem from the Account Unadjusted Balance Detail), double click an allocation account.
The Account Unadjusted Balance Detail of the selected account is displayed
Clicking the ‘Generate Printout Sheet’ button from the Account Unadjusted Balance Detail dialog
creates a new worksheet with the Account Unadjusted Balance Detail
You can manipulate the printout data. For example, you can sort the rows to get the largest credited amounts at the top:
- Select the rows
- Click the ‘Sort' button from the ‘Data’ tag
- Select the column Credit and the order Largest to Smallest
- Click OK
The rows are sorted with the largest credited amounts at the top
After selecting all disbursements with an amount higher or equal to 4,000, you can make a random selection among the other disbursements:
- In a new column (I), click the cell of the first row you want to select from
- Click Fx
- Select the ‘Math & Trig’ category
- Select ‘RAND’
- Click OK
- Grab the lower-right corner of the cell with the random number and drag it down to copy it to the other rows
- Copy & Paste Values in the new column (I)
- Select the rows, click the ‘Sort' button from the ‘Data’ tag, select the new column (I), click OK
Beginning with the first row, simply select the number of rows you want to include in your random sample
The ‘Generate Printout Sheet’ button from the Transaction Detail dialog
also creates a new worksheet, with the Transaction Detail
The GL button
creates a new worksheet with all the Transactions
and a new worksheet with the complete General Ledger
Here are the excel specifications and limits
So far, the Account Unadjusted Balance Detail dialog was accessed from an account balance (accessed from an item details), and from the Transaction Detail dialog (accessible from the Account Unadjusted Balance Detail).
Note: if adjusting entries are recorded in an account, clicking the account balance from an item details will display the Account Balance Detail
and clicking the Unadjusted Balance will display the Account Unadjusted Balance Detail dialog.
The Account Unadjusted Balance Detail dialog is also accessible from the General Ledger worksheet.
From the General Ledger worksheet, double click an account's Unadjusted Balance. The Account Unadjusted Balance Detail is displayed
The Account Unadjusted Balance Detail dialog is also accessible from the working papers Grouped Accounts Sheets and Trial Balance.
From a Grouped Accounts Sheet, double click the account's Unadjusted Balance. The Account Unadjusted Balance Detail is displayed
From a Trial Balance working paper, double click an account's Unadjusted Balance. The Account Unadjusted Balance Detail is displayed
Feel free to contact us thru the Contact page if you have any question.