Get Balances
From VT Transaction buttonSupport topics - VT Final Accounts
The linked entry trial balance/inserting a new financial row
The way that Excel is linked to VT Transaction is often the subject of support calls. The topic that appears in both the user guide and the help topics is therefore reproduced below:
To create a workbook linked to VT Transaction, you should choose the Obtained automatically from VT Transaction option in the New Accounts Workbook dialog. You cannot change this option retrospectively because there are separate templates for the manual and linked entry methods.
When you click on the Get Balances From VT Transaction button on the VT toolbar for a linked workbook, a snapshot of the trial balance in the currently open company in VT Transaction is automatically stored with your workbook.
Get Balances
From VT Transaction button
VT Transaction supplies a trial balance in a special format designed to match the headings in the workbook you are currently working on. You cannot directly see the copied trial balance. Instead cells are linked to it using special custom functions.
If you double click on a cell containing a custom function, a list of the accounts it is linked to is displayed. If you want a summary only, hold down the Alt key when double clicking.
An account is what you post entries to in VT Transaction. A heading relates to a reporting line in Excel. At first it may seem complicated to distinguish between the two, but the distinction has significant advantages:
· Several accounts can be included under one heading in Excel. For instance, other creditors in Excel may comprise of several accounts (you can see a list by double clicking on the other creditors cell)
· If you or a client sets up new accounts in VT Transaction, you are forced to assign the accounts to headings in Excel. This helps ensure that your accounts balance
· If your accounts do not balance, you can automatically list the headings not incorporated into the workbook by choosing the Check All Headings Included command (Tools menu, VT Auditing sub-menu)
· A company in VT Transaction can supply different lists of headings. For instance, the same company can supply the headings required by both the small and large companies templates in Excel
The Trial Balance Analyser dialog determines which accounts are included under which heading in Excel when you click on the Get Balances From VT Transaction button. Ledgers and accounts are shown on the left of the dialog and the Excel headings to which they are 'assigned' are shown on the right.
If you have created new accounts in your company in VT Transaction, the Trial Balance Analyser will be automatically displayed when you click on the Get Balances From VT Transaction button.
You can display the dialog at any other time by choosing the Trial Balance Analyser command from the Set Up menu in VT Transaction, or by clicking on the Trial Balance Analyser button on the VT toolbar in Excel.
Trial Balance
Analyser button on the VT toolbar in Excel
If you are not sure how to use the Trial Balance Analyser dialog, click on the Help button in the dialog.
If you want to display amounts in thousands, choose the Get Balances From VT Transaction command from the VT sub-menu of the Tools menu. An options dialog is displayed if you choose the menu command instead of clicking on the toolbar button.
A financial row is a row in the accounts linked to the trial balance. New financial rows are typically added to the Notes or DetailPL2 sheets.
Inserting a new financial row in a linked workbook is not as easy as in a manual workbook. This is because VT Transaction is a separate application to VT Final Accounts and because of the distinction between accounts and headings explained earlier. VT Software wishes it were possible to create a new account in VT Transaction and for this to appear magically in the correct place in a note or on the DetailPL2 sheet in Excel. It is not. Similarly, you cannot change an account name in VT Transaction and have the change automatically reflected in Excel.
It is not practical to rename unused existing headings in linked workbooks. You should always create new ones instead. You do not need to worry about unused existing headings because AutoHide automatically hides them.
Before inserting a new financial row in a linked workbook, you should understand the distinction between accounts and headings and how the Trial Balance Analyser works. If you do not, you may find these instructions hard to follow.
Inserting a new financial row is potentially a three stage process:
1 An account must be created in VT Transaction (unless you are just changing where an existing account appears). To create a new account in VT Transaction, click on the Set Up Account button in any of the transaction entry dialogs, or choose the Account command from the Set Up menu
2 A new heading must be created in the Assign Account dialog (displayed when you click on the Assign Account button in the Trial Balance Analyser in Excel)
3 The heading must be inserted into the appropriate place in Excel using the Insert Financial Row button (for instance into the Notes or DetailPL2 sheets)
Assuming that the necessary account already exists in VT Transaction, the step by step instructions for inserting a new financial row are as follows:
1 Click on the Trial Balance Analyser button on the VT toolbar in Excel
Trial Balance
Analyser button
2 In the Trial Balance Analyser dialog select the relevant account such as Director’s loan and click on the Assign Account button
3 In the Assign Account dialog, click on the New Heading button and enter the name of the new heading. Often this will be the same as the account name, but it does not need to be
4 Click the OK button in the Assign Account dialog
5 Close the Trial Balance Analyser
6 You will be asked if you want to refresh the balances in the workbook. Choose Yes
7 Select the cell above which the new heading is to appear, for instance the cell containing the Other creditors line in the creditors note on the Notes sheet, or a cell on the DetailPL2 sheet
8 Click on the Insert Financial Row button on the VT toolbar
Insert
Financial Row button
9 In the Insert Financial Row dialog, select the new heading from the list and click on the OK button. A new row will be inserted in the accounts with formulas linking it to the trial balance
10 If you have a cash flow statement and the new heading is not part of operating profit, debtors or stocks you will need to insert the new row in an appropriate place on the CFWorkings sheet. You can use the Insert Financial Row button to do this as well. (There is more detail in the on-screen help system on this task. Choose the VT Final Accounts Help Topics command from the Help menu and select The Linked Entry Trial Balance topic.)
11 If you have inserted the new row into an existing list on the Notes or DetailPL2 sheets, the new row will be automatically included in the lead profit and loss account or balance sheet. If you have inserted it elsewhere, you may need to modify these sheets to pick up the numbers in the new row. If you modify the lead PL sheet in a company, you will also need to make a similar modification to the DetailPL1 sheet and vice versa
Tip If you double click on one of the formulas, a list of the accounts included is displayed. If you hold down the Alt key and double click, the name of the heading is displayed.
You cannot use the Insert Financial Row button to insert a new row in a column style note such as fixed assets. If you need to modify these notes, you should use standard Excel commands and the Insert TB function button on the VT Utility toolbar.
Insert TB
Function button on the VT Utility toolbar
A heading can be deleted by choosing the Special Trial Balance sub-menu from the Set Up menu of VT Transaction. The change does not take effect in Excel until you have clicked on the Get Balances From VT Transaction button again.
If a row in Excel already contains formulas linking it to the heading, the row should also be deleted by selecting the row and choosing the Delete command from the Edit menu in Excel.
In a linked workbook, the trial balance is always automatically rounded. You can alter where the rounding difference (if any) is included in the accounts by clicking on the Rounding button in the Trial Balance Analyser.