Description
This function can return a variety of financial values that are used in the construction of financial statements. This includes the ability to report balances as of a specific period or the activity for specific periods. Periods up to 119 months in the past and up to 6 months in the future can be specified. This supports Accrual-Basis as well as Cash-Basis accounting. Budget values can be specified using any of the ten possible budget types that can be defined in General Ledger. Prefixes can be reported individualy or consolidated in various ways.
Syntax
= TSGLAmount ( Prefixes , BaseAccounts , Year , Period , AmountType , BudgetType , Partner , DataFolders )Example
=TSGLAmount("10","4001",2001,5,"Balance")
Returns the balance for base account 4001 of prefix 10 for fiscal year 2001, fiscal period 5.
Arguments
| Argument | Description |
|---|
| Prefixes | If your account format does not use prefixes, leave this argument blank. This argument specifies the GL prefix to be reported or the set of prefixes to be consolidated. This may one of the following types of values:| Blank | This indicates that all prefixes should be consolidated. Example: "" | | Single Prefix | Includes accounts from the single prefix specified. Example: "1-10" | | Prefix Mask | A set of literal characters and/or wildcard characters that identify the set of prefixes to consolidate. A percent symbol (%) is a wildcard representing any number of characters. An underscore (_) is a wildcard representing a single character. Example: "1-1%" | | List of Prefixes | A comma-separated list of prefixes enclosed in parenthesis. The previxes in the list will be consolidated. Example: "('1-10','1-11','1-21','1-40')" | | Range of Cells | Produces the same result as List of Prefixes except that the list is built automatically based on the values in the cells that make up the range. Example: $G$3:$G$10 |
|
| BaseAccount | This argument specifies the GL base account to be reported or the set of base accounts to be consolidated. This may be one of the following types of values:| Single Account | A single base account. Example: "4001" | | Range of Base Accounts | Consolidates the values from a range of base accounts. See GLBaseRange function. Example: "[BaseAccount]>='4000' AND [BaseAccount]<='4999'" |
|
| Year | In general, this argument identifies the year from which the value should be reported. The specific use of this argument depends on Period argument.| Fiscal Period | The Year argument will be used to identify the fiscal year. | | Calendar Month | The Year argument will be used to identify the calendar year. | | Accounting Date | The Year argument will be ignored and the year will be based on the fiscal year of the fiscal period the date relates to. |
|
| Period | Identifies the period to be reported. Depending on your version of General Ledger, this may either go back as far as 23 periods or as far back as 119 periods (provided that master file totals have been stored that far back). The following values may be used:| 1 to 12 (or 13) | Indicates a fiscal period number. If not using 13-period accounting, the maximum value is 12. | | 101 to 112 | Indicates a calendar month where 101=January, 102=February, 103=March, etc.
NOTE - Calendar month reporting doesn't support 13-period accounting. | | Accounting Date | The fiscal period is determined by comparing the date to the calendar of dates in Fiscal Settings. When a date is provided in this argument, the Year argument is ignored because the year will be determined based on the fiscal period the date falls within. |
|
| AmountType | Optional - This argument indicates the type of value to be returned. If this is not specified, the balance will be returned. This may be one of the following values:- "Balance" (default if AmountType is not specified)
- "PeriodActivity"
- "YTDActivity"
- "DebitPeriodActivity"
- "DebitYTDActivity"
- "BalanceCash"
- "PeriodActivityCash"
- "YTDActivityCash"
- "DebitPeriodActivityCash"
- "DebitYTDActivityCash"
- "PeriodBudget"
- "YTDBudget"
- "PeriodBudgetCash"
- "YTDBudgetCash"
NOTE - Any AmountType referencing a debit requires custom configuration of your Sage Company settings. For more information, please see our KB Article Store Debit Activity. |
| BudgetType | Optional - This argument is only used if the AmountType argument specifies a budget value to be returned. This may be one of the 10 user-definable budget types in General Ledger. The name supplied for this argument must exactly match what was entered in General Ledger. If this is not specified, the first budget type will be used. |
| Partner | Optional - If Partners are used in General Ledger, this argument identifies the Partner to be reported. When reporting partner values, the partner's ownership percentage of the entity is multiplied by the amount that would otherwise be returned. If the partner has no ownership in an entity, zero is returned. If a value is supplied for this argument and the entity has no partners defined, zero is returned. |
| DataFolders | Optional - If blank or not specified, the current data folder will be used. Possible values include:| Blank | Current data folder (associated with the workbook) will be used. | | Data Folder Path | Specifies a specific data folder. | | Range of Cells | Where each cell in the range identifies a data folder path. In this case, the value from each data folder will be accumulated and returned as one result. |
|