General Ledger Reporting in Workbooks
Classic IR was designed for Sales report where totals and sub-total were simple sums. This is the same for IRNG.
- To create GL reports in IR Classic a feature was added in the groups definition for Account_Type to indicate how these groups would add or subtract.
- In IRNG create GL reports dimension called a GL Model is used. This references a table were the data rows are duplicated and signed and so the sub-total are still simple sums of the rows. The side affect of this is there is no GL Model row defined in the the output we be a non-sensical sum of rubbish.
The term GL Model will mean nothing to a use the dimension is named Profit & Loss Sub-totals
The GL model has directives on how to sum sub-totals. It does this by having multiple and dupicating rows of data that are indexed by the name of the sub-total eg Income, Expense, Gross Profit.
The directives have a Sign control ( FSSign) which multiplies the value and so the displayed value have a natural sign. Using this is how reports are able to show COGs and Expenses as +ve.
The GL Models defined are
- Classic - Produces the Classic Profit & Loss
- Income with Total
- COGS with Total
- Gross Profit
- Expenses with Total
- Operating Profit /(Loss)
- Other Income
- Other Expenses
- Net Profit / (Loss)
- Divisional - Produces a Profit and Loss that does not display COGs but does display Gross Margin and also lists individual row dimension Profit. This is intended to be used for divsional reports
- Income with Total
- Gross Margin
- Expenses with Total
- Net Profit and Loss for each Dimension
- Net Profit / (Loss) Total
- Division Σ Profit - Produces a Profit and Loss that does not display COGs but does display Gross Margin and shows asumamry Net Profit. This is intended to be used for divisional reports
- Income with Total
- Gross Margin
- Expenses with Total
- Net Profit / (Loss) Total
- Simple - Produces a list of the dimension being analysed with a total and the amount sign is +ve for Income and -ve for COGS and Expense and there is only one entry per dimension. This is intended for use in Charts.
-
When using GL Model Simple it only has one Total - All so there is no use for it being in a Group By. When you then Group By other things like flexi-levels the values of COGS and Expense are show as negative and the sums of these will be negative.
The "%_Var_to_Budget" calculates correctly but as a negative value for Expense and COGS.
Because if this the sign the colour and arrow direction of formatting rules is missleading and should avoided
Several workbooks have been created and the calculation of the dollar and numberic amounts is correct in each of the above.
Calculation of Variance %
The following discussion relates to the GL Model Classic . Even though other models have the following Metrics they have not been modifed to use them.
The calculation of Variance % is typically variance / budget as a percentage. When these calculate they accurately depict the increase or decrease in Actuals Vs Budget.
WorkBooks has a neat feature called Formatting Rules that can provide widgets that give graphical coloured images indicate increase or decrease and these can be graduated in colour and symbol .