Creating and Inserting Trial Balance Links

The Insert TB Link option allows you to create links to the trial balance from a Microsoft^® Excel^® or Microsoft^® Word workpaper. This feature allows you to view information from the trial balance without having to open it. If the trial balance is updated, the TB link amounts will update when the workpaper is refreshed.

A TB link can include a single TB link formula or it can include several formulas. Once you have created a TB link, you can save it for future use in the same engagement, if needed.

Note: If needed, you can drill down to the accounts that make up the calculated amount in a TB link. See Drilling Down to Trial Balance Accounts in a TB Link for more information.

Create and Insert Simple TB Links

1. Open the Excel^® or Word workpaper you want to use.

1. Click the engagement on the Engagement List page.

   Note: See Using the Engagement List Page for information about using the pagination and search features to find an engagement in the list.

2. Locate the workpaper on the Engagement View page.

   Tip: You might need to expand the node or folder that holds the item you need.

2. Place the cursor where you want to insert the TB link.
3. Click Insert TB Link on the CCH iFirm Engagement ribbon to open the Insert TB Link window.

Important: If you have CCH iFirm Engagement installed on your computer, you may also see an Engagement ribbon. Be sure to click the button on the CCH iFirm Engagement ribbon.

Tip: If needed, the Insert TB Link window can be resized by dragging the corner of the window.

4. Build a formula using the listed parameters.

   Parameter	Description
   Trial balance	Populates automatically with the trial balance for the engagement you selected. Consolidated engagements will list the trial balances for the consolidated entity and the subsidiary entities.
   Account grouping	Select the account grouping you want to use to derive the dollar amount. You can select either Financial Statement or Tax. Note: Tax is not available with Financial consolidated engagements.
   Type	Select the type of grouping to use to reach the dollar amount. This selection determines your choices in the Account/Index # field. The selections include the following: Account Grouping. Sums the accounts in the account group and subgroup codes. Account Type. Sums the accounts that have the same type: Asset, Liability, Equity, Revenue, or Expenses. Account Class. Sums the accounts that have the same class. Only available if you selected Financial Statement for the account grouping. Account Detail. Allows you to select specific account numbers for the formula.
   Period	Select the current or prior period that contains the appropriate balances. Up to five prior periods are available.
   Balance type	Select the balance type to use in the formula. Journal entry balance types are available only when Current is selected for the period. Consolidated engagements will have different balance types available based on the trial balance selected (consolidated or subsidiary entity).
   Account/Index #	Select the account or index number to include in the formula. Only the account or index numbers for the type that you selected above are available in the list. To locate the item you need, click the down arrow or press F4 to display the list of accounts. You can also begin typing the account or index number, and then click the correct item when you see it.
   Fund level	This item will be available on a future release. Select the level of fund (Fund Type, Fund Subtype, or Fund) to be used when calculating the amount to be pasted. This option is only available if a fund trial balance is selected.
   Fund index	This item will be available on a future release. Based on the Fund Level selected above, this field will list the choices available for the index. For example, if you selected Fund Type as the Fund Level, the list includes all fund types for the selected fund trial balance.

5. Select the Round check box if you want to round amounts. Then, in the text box to the right, select the number of places after the decimal you want to round to.
6. Specify the sign option you want to use.
   • Actual. Amounts are as they appear in the trial balance.
   • Reverse sign. Amounts use the opposite sign from the trial balance.
   • Absolute value. Amounts appear as absolute values.
7. If you are working in a Word workpaper, select the negative style, numeric style, and zero style to use.
8. Click Insert. The formula displays in the TB link formula text box.

   Note: See Analyzing a TB Link Formula for detailed information about the components that make up the TB link.

9. Click Paste to insert the TB link into the workpaper.
10. Do any of the following if needed:
    •  Save this TB link formula for future use in the engagement.
      1. Click Save.
      2. Enter a name and description for the formula.
      3. Click OK.
    • Paste the same TB link to another cell in the workpaper. Click the cell on the workpaper and select Paste on the Insert TB Link window.
    • Paste a different TB link to another cell in the workpaper. Click the cell on the workpaper and click Clear on the Insert TB Link window. Then, repeat steps 4 through 10 above as needed to add a different TB link.
    • Create a complex TB link formula by adding another TB link to this TB link formula. See Advanced TB Link Features below for details.
11. Click Close.

Advanced TB Link Features

• Create complex TB links. Complex TB links have multiple TB link formulas.

1. Create the initial TB link as explained above, but do not paste it into the workpaper.
2. Select appropriate parameters for the additional formula you want to include in the TB link.
3. Click Insert again to add the new TB link formula after the initial formula in the text box. A plus (+) sign appears between the two formulas. The results of each formula are summed to calculate the result for the TB link.
   • In Excel^®, the plus sign (+) at the end of a line in a TB link formula can be changed to a minus sign (-), a division sign (/), or a multiplication sign (*). Excel^® calculates the operation. For example, changing the plus sign (+) to /2 will divide the formula by 2.
   • In Word, the only operator is the plus sign (+). To subtract two values, you can use Word formulas in a Word table.
4. Repeat the steps above as needed until you've added all the parts of the complex formula.
5. Click Paste to add the TB link to the workpaper.

• Insert a saved TB link formula into a TB link.
  1. In the Insert TB Link window, click Open.
  2. In the Saved formulas section, select the saved formula you want to insert.
  3. Click OK.
  4. Complete the formula as needed and paste it into the workpaper.

Freezing Engagement Links

If you freeze Engagement links within an Excel® or Word workpaper, changes to the trial balance and/or name and date properties are not carried to the document. Links are not automatically refreshed before they are frozen, so use the Refresh function prior to freezing the links.

Note: Be sure to open the workpaper at least once before you freeze links. This ensures that the links are updated.

When you freeze Engagement links within a workpaper, you cannot insert new links or use the following functions:

• Name Functions
• Date Functions
• Refresh
• Insert TB Link

You cannot freeze engagement links in a locked workpaper. The workpaper must be unlocked before links can be frozen.

When a workpaper is rolled forward, frozen engagement links remain frozen. If you copy an engagement that includes workpapers with frozen links, the links remain frozen.

You must be the Active User of a workpaper to freeze links and you must be granted the permission to freeze links.

To freeze Engagement links from within the workpaper, do the following:

1. Navigate to the Engagement View page.
2. Click the name of the workpaper whose links you want to freeze.
3. Make sure the links have been updated to reflect the current trial balance and name/date information.
4. On the CCH iFirm Engagement ribbon, select Freeze TB Links.
5. For Excel workpapers, you will be notified that the workpaper will be automatically saved. Click Yes on the message to freeze the links.

Note: If the Excel workpaper has been signed off by one or more reviewers, the modified after reviewed status icon will display for the workpaper in the Engagement View. At least one reviewer must sign off the workpaper again to remove the status icon.

A frozen links icon displays in the Status column of the Engagement View so that all users are informed the links in the workpaper are frozen.

Unfreezing Engagement Links

You can unfreeze Engagement links that were previously frozen so they can be updated or changed. When you unfreeze links within an open workpaper, the links are automatically updated with any changes to the trial balance or the name and date functions.

You must be the Active User of a workpaper to unfreeze links and you must be granted the functional permission to unfreeze Engagement links.

To unfreeze Engagement links from within the workpaper do the following:

1. Navigate to the Engagement View page.
2. Click the name of the workpaper whose links you want to unfreeze.
3. On the CCH iFirm Engagement ribbon, select Unfreeze TB Links.
4. For Excel workpapers, you will be notified that the workpaper will be automatically saved. Click Yes on the message to unfreeze the links.

Note: If the Excel workpaper has been signed off by one or more reviewers, the modified after reviewed status icon will display for the workpaper in the Engagement View page. At least one reviewer must sign off the workpaper again to remove the status icon.

In the Engagement View page, the frozen links icon no longer displays in the Status column.

Back to top