Process for Custom Grouping & Subtotaling of Management Pack Templates
Process for Custom Grouping & Subtotaling of Management Pack Templates
- 1 Symptoms
- 2 Resolution/Workaround
- 3 Customising the Grouping and Subtotalling
- 4 Example:
- 5 DO NOT USE THE NUMBERS FROM 8 TO 38
- 6 References
- 7 Applies to
The following are examples of error messages that may occur:
- All Management Pack Reports (version S and D) automatically group and subtotal the accounts on the layout generated. In order to produce a final income statement or balance sheet, the accounts are grouped by the Financial Category Description in a predefined order. For the purpose of this document a final income statement or balance sheet will be referred to as “a complete layout”.
There is however an exception to this in that Management Packs for certain applications will not produce complete layouts. The following applications are exceptions:
- Sage Line 100
- Syspro v 6.0
What is the exception?
On generating a layout the accounts are still grouped and subtotaled by financial category but the layout is incomplete. You would need to do the following in order to complete the layouts:
1. For Income statements you would need to insert your own calculations to create a Gross profit and Net Profit/Loss lines.
For Balance Sheets you would need to rearrange the order of the groups if necessary and insert calculations to form the balancing totals of a balance sheet
Customising the Grouping and Subtotalling
The application code written to automate the grouping and subtotaling within the Management Pack Template is designed around a specific layout built into each template. This specified layout enables a complete Income Statement or Balance Sheet to be generated for that specific application. From a users point of view it is not custom built, but there is however a way around customising it should you wish to do so in which case you do at your own risk.
Where does one customise the grouping?
On the Lookup Sheet
When does one customise the grouping?
Before or after you generate your first layout, this is dependant on the grouping and subtotaling you wish to automate.
Once you change the original formula on the lookup sheet, you will not be able to generate a complete layout automatically unless you set the formulae back to its original state.
What and how must one customise the lookup sheet?
You need to customise the formulas or column numbers for the following columns:
The order in which the accounts are displayed on the layout generated, is determined by the GL_Cat_Code
The grouping and subtotaling of the accounts is determined by the GL_Cat_Description
i.e. Ascending order by GL_Cat_Code then GL_Cat_Description
The formulas would need to be changed in row 6, (should be the row beneath the row containing column numbers) which is normally set with a very small row height, appearing almost hidden.
Adjust the height of all these rows between row 5 and row 9 so you can determine the correct row. There should also be a row beneath this containing the word RETINC in column A, this is separate to the formulas used to extend alongside all accounts on this lookup sheet. DO NOT overwrite this row with the formulas/values you change.
Refer to the example.
This example refers to a Management Pack designed for Pastel
You need to generate an Income Statement per Ledger Sub-Account No. The sub-account no already exists on sheet1 in the third column.
1. Display the Lookup Sheet 2. Cell F5 should contain the no 7, change this to 3 (3 representing the column no of the sub-account no’s on sheet1) 3. The GL_Cat_Code column should now contain the sub-account no’s 4. Browse through these no’s and confirm that you do not have any numbers that are in the range 8 to 38 (these numbers are not allowed in customising) 5. If you don’t then proceed with step 11, if you do then you need to change your numbers in order for a meaningful layout to be produced. 6. On the lookup sheet, unhide the hidden rows between rows 5 and 9 7. Cell F6 should contain the Index formula to return the values from column no (no 3) specified in F5. Since you have invalid no’s, you would need to change them. The easiest way is to change the formula so that it increases all your no’s by 100. 8. Change the formula in cell F6 to read : =INDEX(Sheet1!RawDataCols,$C6,F$5)+100 9. Then copy this formula and paste it to all the used cells in this column from row 9 onwards. 10. Hide rows 6 to 9 again. That’s the only change required in this case 11. Since you still want a standard income statement there is no need for any further change. The description used for the grouping will still work on the financial category description 12. Go the Menu sheet 13. Generate the layout you require.
14. You should have an income statement layout for each set of sub-account no’s on the same sheet, but listed one below the other.
15. Since you have changed the GL_Cat_Code numbering from its original numbers the various income statement groups may not appear in the order you wish. You may need to just move these sets of rows around to correct the order.
Insert your Gross profit and Net profit calculations.
GL_Cat_Code Relates to a financial category code present on sheet1 (this is not necessarily the financial code listed within the accounting application. It may be a forced number to change the sort order of the accounts so they appear in the correct order for a complete income statement or balance sheet.
Set this to pick up a value (or enter a value manually) you wish to sort by so the accounts appear in a particular order. If the existing formula is using an index function then it is currently picking up the value from sheet 1, from the column number stated in row 5 above it. You can change only the column number in row 5 should you wish to pick up a value from another column on sheet 1. In order to prevent incorrect sorting and grouping, there are exceptions to the values that you can use to sort on for some applications. An example of this is the number 10 that may be used to group all revenue accounts, so should you also have a number 10 in the values you place here then that set of accounts could end up being grouped as Revenue.
DO NOT USE THE NUMBERS FROM 8 TO 38
Relates to the description applied to the GL_Cat_Code , also looked up from sheet1 – the accounts are grouped and totaled by the value in this column and not by the GL_Cat_Code
Set this to pick up the value you wish to group on or enter a value manually for each account.
Here again if the existing formula is using an index function you can pick up a different value from sheet 1 just by changing the column number in row 5. This value becomes the name assigned to the total line of the group.
In most instances a formula based on the GL_Cat_Code is used to determine whether or not the value displayed should be displayed as positive or negative.
1 in this column means do not change the sign of the value
-1 in this column means change the sign.
If you customise the Gl_Cat_Code and GL_Cat_Description fields then the sign control mayl result an incorrect values. You would then need to reassign a value next to each account either manually of by means of a formula should you wish to apply sign control.
If Sales values are entered as negative and you would like them displayed as positive when the layout is generated, then ensure that there is a –1 in this column for each sales type account.
(The sign control value is applied to the value of the account in the amount columns on the lookup sheet such as actual, budget, prior where the formula in these columns is multiplied by the sign control value)
This is similar to the Sign Control value but is only applied to the layouts when generated and only if they include a variance column. It controls the variance formula in terms of leaving the result as a positive value or forcing it to a negative.
For expense accounts, if the actual value is greater than the budget value then the variance should be negative.
For income accounts if the actual value is greater than the budget then the variance should be positive.
You would then need to reassign a value next to each account either manually of by means of a formula should you wish to automatically correct the variance formula.
What to do after customising the various columns on the lookup sheet:
Generate the layout you need.
At the end of the layout you may find the words “gross profit….”, “ Net profit etc…” on Income Statement layouts or other descriptions on Balance sheets - Delete these lines as they will be incorrect and create your own where necessary.
TIP: Should you wish to generate a complete layout for a Balance Sheet but then a custom layout for an Income Statement, generate the Balance Sheets first, change the lookup sheet to customise it in the way you need it, then generate the Income Statement layouts.
NOTE: There is no upgrade process to upgrade an existing FADD to a Management Pack.
- Management Pack Templates