Use insert accounts for a fast start on building a trial balance

You’re diving into using account schedules – you’ve figured out where they are, created a brand new one, given it an easy short name and a longer descriptive name and now you’re ready to get started. Now what? You need to begin with the row setup; the foundation of all account schedules.

The row setup defines what you’ll see on the lines of your report. Generally you’ll want to define general ledger accounts, or groups of general ledger accounts, in the row setup when you’re just getting started. Later, you’ll want to add some column layouts but let’s focus on getting that foundation built first.

The easiest way to get going with a new row setup is to put together a trial balance. Your trial balance should be a listing of all your general ledger accounts, one line each, which will show the balance of each account on a particular date, generally a month or period end. If you add them all up, all the accounts should balance to zero, which is one way to prove that your books are in balance.

If you’re using the role-tailored client (RTC), choose Edit Account Schedule from the action pane.

You’ll get a new screen with many blank rows ready to be used. From this screen, choose Actions=>Functions=>Insert Accounts.

This opens up your G/L Account List. Use your mouse to select all the rows you want to add to your row setup.

Once you’ve highlighted them all (they should turn light blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.

At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Choose the Overview button (shown in the above screenshot).  Select a column layout. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only. Select Show Matrix from the action pane.

This will show all the accounts you selected in your row setup, now in your account schedule.

To prove out the balance, dump the account schedule into Excel. Do this by choosing Actions=>Functions=>Export to Excel. On the next window choose the option to Create a New Workbook. Hit OK, and your schedule will export to Excel.  Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.

Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report:  Departments=>Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. Select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.

For those who use the classic client, let’s run through the same steps.  Starting from Financial Management=>General Ledger=>Analysis & Reporting=>Account Schedules, choose your new account schedule from the list.

Hit OK, and you’ll get a new screen with many blank rows ready to be used. From the Functions button on the bottom right, choose Insert Accounts.

This opens up your G/L Account List. Simply click on the blank cell immediately to the left of the No. field to select all accounts quickly.

Once you’ve highlighted them all (they should turn dark blue), hit OK, and all the accounts you’ve selected will be added to your row setup, one row per account.

At this point, don’t make any changes to the row setup. Let’s see if it matches the canned trial balance that you can print directly out of NAV. Hit OK, and then choose Overview from the Account Schedules button found on the bottom right.

Select a column layout from the Column Layout Name field. If you don’t have one yet, hang in there, we’ll cover that in an upcoming post. For now, I’m going to choose a simple one that shows balance only.

This will show all the accounts you selected in your row setup, now in your account schedule.

To prove out the balance, dump the account schedule into Excel. Do this by choosing Export to Excel from the Functions button on the bottom right.   

On the next window choose the option to Create a New Workbook. Hit OK and your schedule will export to Excel.  Watch out: If you’ve used Begin or End-Total lines in your chart of accounts, you’ll need to remove these lines. These are calculated fields, and should not be a part of your trial balance.

Let’s run the canned trial balance from NAV as a comparison to see if we’ve done this right. Follow this trail to get to the report:  Financial Management=>General Ledger=>Reports=>Financial Statement=>Trial Balance. On the Options tab, select Actual Balances as your report column and make sure the date filter is set to the same date you used when running the account schedule. Compare the report to the account schedule. Each line should match between the two reports and the total should be zero on both, proving your trial balance is indeed in balance.