GL Report Models

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  

  1. 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)
  2. 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
  3. 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
  4. 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.
  5. 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.

    T​he "%_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 .

Below the Variance %  Column D does calculate the increase and decrease but if I use formatting
rules it show an increase in cost as GREEN arrow but it should show as a bad thing ie  RED thing - for example in  3A the cost is 229,452 which is more that Budget 215,006 .
The  Variance % shows 6.72% which is correct but the formatting rule shows it green even though an increase in costs is bad.
It's not possible to make formatting rules understand good and bad based upon income or expense
Screenshot 2023-11-25 at 12.09.58 pm.png
So we have created a % Var to Budget  in column E and it understand the desired sign of Income and Expense and signs the % Var to Budget  calculations accordingly. 
The visual formatting rules are very nice for P&L however the arrows symbols could be misunderstood and may need to just show colour.
Lastly there is a new % of Actual Income capability.
Was this article helpful?
0 out of 0 found this helpful

Articles in this section

Ask the BI4 Community
Need help using this report or feature?