Using TB Links with Fund Information

In a Fund engagement, you can insert formulas in Microsoft® Excel® and Microsoft® Word workpapers to display trial balance amounts grouped by fund types, fund sub-types and funds.

See Linking Engagement Information in Workpapers for details on how to add trial balance information, name and date functions in workpapers for all types of engagements.

To add a TB link in a fund engagement,

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

  2. Place the cursor where you want to insert the TB link.

  3. Click Insert TB Link on the Axcess Engagement ribbon to open the Insert TB Link window.

    Important: If you have CCH ProSystem fx Engagement installed on your computer, you may also see an Engagement ribbon. Be sure to click the button on the Axcess 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

Select the fund level to use to reach the dollar amount. The selections include the following:

  • None. Sums the account balance regardless of fund mapping

  • Fund Type. Sums the balance for account(s) mapped to a specific Fund Type. The Fund Type is selected in the following (Fund Index) field

  • Fund Sub-Type. Sums the balance for account(s) mapped to a specific Fund Sub-Type. The Fund Sub-Type is selected in the following (Fund Index) field

  • Fund. Sums the balance for account(s) mapped to a specific Fund. The Fund is selected in the following (Fund Index) field
Fund index

Select the fund index number to include in the formula. Only the index numbers for the fund level 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.

  1. 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.

  2. Specify the sign option you want to use.

  3. If you are working in a Word workpaper, select the negative style, numeric style, and zero style to use.

  4. Click Insert. The formula displays in the TB link formula text box.

  5. Click Paste to insert the TB link into the workpaper.

  6. Click Close.

Any updates made to the linked information in the engagement are applied to the workpaper links when the workpaper is refreshed. Workpapers refresh automatically when they are opened, or you can click Refresh on the Axcess Engagement ribbon if you have the workpaper open.

Analyzing a Fund TB Link Formula

To view a TB link formula in a workpaper, do one of the following:

  • In Microsoft® Excel®, click the cell containing the TB link. The formula displays in the formula bar. You can also click Drill Down TB Link on the Axcess Engagement ribbon.

  • In Microsoft®Word, select the linked formula in the document, and then click Toggle Field Codes or View Field Codes. You can also press Alt + F9. A Word formula has several formatting parameters, but within the formula you will see the same formula that would be used in Excel®.

In both Excel® and Word, you can also view a TB link by selecting Drill Down TB Link on the Axcess Engagement ribbon. In Excel®, you must first click the cell containing the TB link, and in Word you must highlight the linked formula.

The Fund TB link formula is based on the following:

XTBLink("TRIAL BALANCE.FT[116] ","ADJ[5]","1000","FS","1").

  • TRIAL BALANCE displays for fund engagements.

    • The suffix .FT, .FS or .FD stands for Fund Type, Fund Sub-Type or Fund as selected in the Fund Level field. If None was selected, there is no suffix to TRIAL BALANCE.

    • The parameter [116] is the Index number of the specific fund type, fund sub-type or fund selected in the Fund Index field.

  • ADJ[5] represents the balance type. ADJ is a short description for Adjusting Journal Entry and [5] is the balance type period ID assigned by Engagement.

  • 1000 is the Account/Index # based on the Type selected in the formula. The value will be the group index, group|subgroup index, account type abbreviation, account class abbreviation, or the account number.

  • FS represents the Financial Statement account grouping ID. If the Tax account group is used for the formula, Tax is the account grouping ID.

  • 1 represents the type of trial balance link that is being created. It indicates the level of detail needed.

Options include:

  • 3 = Account Detail

  • 2 = Account Group|SubGroup

  • 1 = Account Group

  • -1 = Account Class

  • -2 = Account Type