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 Bi4Cloud WorkBooks.
- 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 WorkBooks 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 so we call the dimension Profit & Loss Sub-totals and this essentially controls the group totals which give the Classic Income, Cost of Sales, Gross Margin, Expenses
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 divisional reports. The standard feature of this
- 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
- EBITDA - Earnings Before Interest, Tax, Depreciation, and Amortisation. This produces a Profit and Loss report which separates out Interest and Depreciation. The accounts that participate in this placement is controlled by user feature where accounts are assigned to particular report codes.
Several workbooks have been created and the calculation of the dollar and numberic amounts is correct in each of the above.
Balance Sheet and Trial Balance reports also utilise GL Models. These include
1. BalSheet format which group totals by
- Asset,
- Liabilities,
- Equity,
- Liabilities and Owners Equity with Retained Earning and Current Earnings in Equity su-totals.
2. TrialBalance format which groups
- Assets,
- Liabilities,
- Equity then
- P&L
- Income,
- Cost Of Goods,
- Expense,
- Other Income,
- Other Expense and
- Profit.
3. Equity Change format which groups
- Opening Equity,
- Current Earnings and
- Closing Equity
3. Equity Change Detail format which groups with expanded detail
- Opening Equity summary
- Current Earnings
- Income
- COGS
- Expense
- Closing Equity
When this format is filtered a date range and by Division ( Job, Classification, Tracking Category ) then this forms the basis of a mini-period Statement.
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 .