Table of contents
- Contents Page
- Information page
- Trustees’/Personal Representatives' Approval and Accountants’ Report
- [ Trust Accounts ] Balance Sheet
- [ Trust Accounts ] Beneficiaries’ Income Account (max 5 beneficiaries)
- [ Estate Accounts ] Estate Capital Account (continued)
- [ Estate Accounts ] Distribution Accounts
- [ Estate Accounts ] Balance Sheet
- Investment Schedule – Headings Centred over Two Columns
A number of manual changes are required to the accounts pages after conversion from VAP. These changes have already been made in the CCH masters, but will need to be made to any local pages.
Suppressing Unused Pages
The VAP Contents Page listed all possible pages and then suppressed those that did not apply. A typical formula to suppress the page references was:
=If (Pagestart,9)=f0 Then rs::`zero` Else `nonzero` Endif
This tests on the starting page number of the ninth page in the index. If it is zero then the page reference is not shown on the Contents page.
This approach does not work in AP; page numbering commands such as Pagestart,9 (or Pagestart,page9bi) cannot be used as part of an If-condition. The only method that currently works in AP is to repeat the page condition. This is entered in a cell on a hidden row. The row is hidden using an =AP(“rs”) command in column A. Then the line, or lines, that show the page reference can be suppressed based on the TRUE or FALSE value of the page condition. This is easier to understand by looking at the CCH Contents page.
Multiple Versions of a Single Page
Where the user has multiple versions of an accounts page, it may be necessary to change the Contents page to use a standard Format Code that can be used for all versions, as recommended when discussing the Alternative Formats Collection above.
The user can take advantage of the DirRange command to list beneficiaries, settlors, trustees or personal representatives in a single command. The CCH masters use the following formulae:
AP("DirRange,#dr13 #dr11 #dr12 #dr105,IsBeneficiaryNowAndThen")
AP("DirRange,#dr13 #dr11 #dr12 #dr105,IsSettlorNowAndThen")
AP("DirRange,#dr13 #dr11 #dr12 #dr105,IsTrusteeNowAndThen")
|[ Estate Accounts ] Personal Representatives|| |
AP("DirRange,#dr13 #dr11 #dr12 #dr105,IsPersonalRepresentativeNowAndThen")
The DirRange command is documented here.
Estate Information Page
Two changes are required to the Specific and Pecuniary Legacies:
- The LegacyRange commands that show the values of the legacies are converted with style Body Text A which makes them left justified. The style needs to be changed to Curr Yr 2dp No. This is a fault.
- In VAP, the figures have a pound sign. There is no style for this in AP; so a pound sign should be added as header above each set of values.
Trustees’/Personal Representatives' Approval and Accountants’ Report
The user can take advantage of the #bsig1 command to list the first signatory, or #bsig2 for the second etc. The CCH masters do this.
[ Trust Accounts ] Balance Sheet
The styles that are automatically applied by the conversion are correct in most cases. However the precision of numbers shown in the accounts comes from the rounding method selected in Trust Accounts. This is fine in most cases, but the Market Value figure shown in the Balance Sheet is usually shown with no decimal places even though the other figures on the page are shown to 2 decimal places, e.g.
Market Value : £2,458,870
After conversion, the style used for the Market Value cell will be 2 decimal places as well.
To fix this, the style needs to be changed manually on the Market Value cell(s). Use style Body Text N.
[ Trust Accounts ] Beneficiaries’ Income Account (max 5 beneficiaries)
This Beneficiaries’ Income Account has columns for up to 5 beneficiaries. If the trust has fewer beneficiaries, unused columns are automatically suppressed and the account description column is made correspondingly wider.
The VAP commands that do this use the #total command a lot, but the conversion into AP converts them automatically into SUM() commands. However a change also needs to be made to another formula and this requires a manual change. At the top of each beneficiary column is a formula to determine whether the column should print or not:
This is converted to:
However while the VAP formula returns 0 or 1, the AP formula returns FALSE or TRUE. A subsequent command, that tries to add up how many printing columns there are, returns 0 as a result. To fix this the user needs to manually change formulae such as :
[ Estate Accounts ] Estate Capital Account (continued)
Several heading are underlined. The conversion does not currently convert underlining. So the style needs to be changed from Body Text A to Body Text Underline. The same applies on some of the Notes pages. This is a fault.
[ Estate Accounts ] Distribution Accounts
There are two pages used for the Distribution Account. Page9di1 is used if there are 5 or fewer residuary legatees; Page9di2 is used if there are 6 or more. On both pages the formula, LegacyRange,#lg3,R,TranX, is used to display the legatee name (where X is the residual legatee number). These formulae are converted with style Current Year No.
- On Page9di1, the headings containing the legatee names should be changed to Col Head C to centre them; and the pound signs should be changed to Col Head A.
- On Page9di2, the column headings should be changed to Col Head C and the legatee names should be changed to Body Text A.
[ Estate Accounts ] Balance Sheet
Because there are two versions of the Distribution Account, the reference to the page number of the Distribution Account needs to be changed to:
=AP("If (#NumLegs,R)<f6 Then PageRange,page9di1,0 Else PageRange,page9di2,0 Endif")
In other words, if the number of residual beneficiaries is less than six then show the page numbers of page9di1; otherwise show the page numbers of page9di2.
Investment Schedule – Headings Centred over Two Columns
Headings over 2 cells
The CCH investment schedule shows two columns for Additions, Quantity and Cost. Above these two column headings is the word Additions as shown below.
However in the CCH page, the word Additions is entered in the cell over Quantity and has been moved to the right by adding leading spaces. This method does not work in AP. So after conversion:
- The two cells above Quantity and Cost should be merged (select both cells, right-click, Merge)
- The heading Addition should be added without leading spaces
- In VAP, dates could be picked up as =#cd12 for last year and =#cd10 for this year, but in AP this means that dates are formatted with a leading zero, e.g. 05 April 2016. To avoid this, dates need explicit formatting of 'd mmmm yyyy', e.g. =AP("#cd12'd mmmm yyyy'").