Using Formulae within a Paragraph to link to dates, figures, words, pages and notes
Introduction
Paragraphs that feed through to the statutory database may contain formulae, some are very simple and other quite complex. Below are common formulae used within paragraphs.
Bringing dates from the statutory database into a paragraph
Dates within the statutory database, General > General Information the current year or prior periods, which exist in CCH Accounts Production, may be brought into a paragraph.
Linking the current year (period) dates
| Statutory database description | Statutory database reference | Formula to be used within the paragraph for the current year | Output, where the year runs from 1 January - 31 December 2022 |
| Period end | #cd61 | {#cd61'd mmmm yyyy'} | 31 December |
| Year | #cd62 | {#cd62'd mmmm yyyy'} | 2022 |
| Year end | #cd10 | {#cd10'd mmmm yyyy'} | 31 December 2022 |
| Year start | #cd12 | {#cd12'd mmmm yyyy'} | 1 January 2022 |
Linking the prior year (period) dates
Use the formula as above with the addition of b, before the # to return the output of the prior period/year date, and then c, for the prior-prior, and so on, for example {b,#cd62'd mmmm yyyy'} will give the output 2021 using the dates above and {c,#cd62'd mmmm yyyy'} will give the output 2020.
Bringing figures from the name ranges or nominal codes into a paragraph
Any name range or nominal code range or singular code can be brought into a paragraph.
Linking using a Name Range
Copy from the format, remove the shell of the formula and replace with {}
For example, copy the total revenue figure on a format shown as =AP("[a,*TOTREV]"). Paste into the paragraph in the statutory database and edit so that it reads {[a,*TOTREV]}.
The figure returned within the paragraph will be displayed with no thousand comma separator and will show 4 decimal places, however, this will not be the case on the format where the style will show with the comma and no decimal places (so long as the style is correct for that display).
A further example is given below bringing through lease payments included within the other creditors figures for this year and last year:
The paragraph created may read ... Included within other creditors is the figure of {#cd280}{[a,*STLEASE]} ({b,#cd62}: ({#cd280}{[b,*STLEASE]})
#cd280 is the actual currency field from the statutory database
[a,*STLEASE] is the name range for short term leases for this year as preceded by a, for last year will be preceded by b,
b,#cd62 is last year
Calculating figures in a paragraph
Examples:
Name ranges:
This year's revenue less last year's revenue
{[a,*TOTREV]-[b,*TOTREV]}
This year's admin expenses plus this year's distribution costs
{[a,*AE]+[a,*DIS]}
A range of nominal codes
This year's revenue less last year's revenue
{[a,0001..049Z]-[b,0001..049Z]}
A single nominal code:
The balance on the sundry accounts in the administrative expenses range for this year, less the same for last year
{[a,3890]-[b,3890]}
To automate wording e.g. profit or loss
This formula has been copied from the P&L account format and edited so that it is looking at the name range profit for the current year to see whether the word profit or loss should be shown within the paragraph when referring to the profit for the year.
{If [a,*PROFIT]<=f0 Then `profit` Else `loss` Endif}
Profit / loss may be changed to e.g. surplus / deficit, the name range will remain the same
To bring through statutory database references
Above we saw the dates being linked. You can do this for other statutory database references, for example, to link the currency symbol {#cd280} or a figure entered into an analysis table e.g. the first row of the analysis of turnover by class table* {#pn999510}, put them together to within a paragraph {#cd280}{#pn999510} will return for example £100,000, being the figure entered into the statutory database. *within the Limited (FRS102) entity.
Linking to pages or notes in the accounts from within a paragraph
The page or note number will not be displayed until a Preview of the Collection is run from the Financial Statements screen.
When linking to a particular page or note within the accounts enter the "code" of that page/note within the formula. The code will be displayed on the financial statements screen if the code column is shown, if it is not, to display right click within the Collection grid, click on Field Chooser and bring the Code column into the grid, drag and drop or double click. Alternatively double click into the format and the code is shown on the bottom tab and within the page setup "format code".
The formulae given below will return the page or note number of the page /note code entered when the accounts are previewed in full.
Linking to a specfic page
{Pagerange,page6is,0} - page6is in this example is the P&L account in the Limited FRS102 entity.
Linking to a specific note
{note,note26} - note26 in this example is the tangible asset note in the Limited FRS102 entity.
Showing a range of pages/notes:
e.g. To identify all the notes pages:
{FirstNotePage} to {LastNotePage}
e.g. To identify specified pages, from the P&L account to the last note page
{Pagestart,page6is} to {LastNotePage}