General ledger cube (LedgerCube) for Microsoft Dynamics AX 2012 R2 and R3
Important
This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2
The General ledger cube for Microsoft Dynamics AX is used to report on ledger accounts and bank accounts. This article provides details about the cube.
Note
To view current information in this cube, you must update the financial dimension balances. You can either click the Update balances button in the Financial dimension sets form to update the balances immediately, or you can schedule a batch to update balances before the cube processing is scheduled.
.png "Reference")
Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security |
.png "Resources")
Analytics in Microsoft Dynamics AX Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3 Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack |
Deployment
The General ledger cube is included in the Dynamics AX project. For information about how deploy the Dynamics AX project—and the cubes that it contains—see Deploy the default cubes.
Configuration keys
The following configuration keys are required to use all features of the General ledger cube:
Bank (Bank)
Check (BankCheque)
Reporting currency (CurrencySecondaryCurrency)
General ledger (LedgerBasic)
Tables and views
The General ledger cube uses data from the following tables and views:
BankAccountTable table
BankAccountTrans table
BankTransType table
BudgetModel table
CustTransOpen table
BankChequePaymTransCube view
BudgetTransactionCube view
CustCollectionLetterJourCube view
CustInterestTransCube view
DimensionFocusBalanceCube view
FreeTextInvoiceCube view
GeneralJournalCube view
LedgerDerivedFinHierarchyCategory view
LedgerDerivedFinHierarchyResults view
LedgerTrvExpTransCube view
PurchaseOrderCube view
SalesOrderCube view
VendTransOpenCube view
Measures
The General ledger cube includes the following measure groups.
Open customer transactions
This measure group is based on the CustTransOpen table and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Accounts receivable open amount – accounting currency |
CustTransOpen.AmountMST |
Sum |
The Accounts receivable open amount, in accounting currency. |
Company Customer Collection letter fee Date (last interest date) Date (due date) Date (cash discount date) Date (transaction date) Date (exchange rate date) Fiscal period date (transaction date – fiscal calendar) Fiscal period date (due date – fiscal calendar) Fiscal period date (cash discount date – fiscal date) Fiscal period date (last interest date – fiscal calendar) |
Bank transactions
This measure group is based on the BankAccountTrans table and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Bank amount – bank currency |
BankAccountTrans.BankTransAmountCur |
Sum |
The total bank transaction amount, in bank currency. |
Currency Company Bank account description Bank transaction type Ledger derived financial attribute value combinations Bank transaction Date (due date) Currency (currency – registration currency) Date (transaction date) Date (bank statement date) Date (acknowledgement date) Date (exchange rate date) Fiscal period date (transaction date – fiscal calendar) Fiscal period date (bank statement date – fiscal calendar) Fiscal period date (acknowledgement date - fiscal calendar) Fiscal period date (due date – fiscal calendar) |
| Bank amount – transaction currency |
BankAccountTrans.AmountCur |
Sum |
The total bank transaction amount, in transaction currency. |
|
| Bank amount – accounting currency |
BankAccountTrans.AmountMST |
Sum |
The total bank transaction amount, in accounting currency. |
Bank payment transactions
This measure group is based on the BankChequePaymTransCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Bank payment amount - transaction currency |
BankChequePaymTransCube.PaymentAmountCur |
Sum |
The bank payment amount, in transaction currency. |
Currency Company Bank account description Date (transaction date) Date (invoice date) Date (invoice transaction date) Fiscal period date (transaction date – fiscal calendar) Fiscal period date (invoice date – fiscal calendar) Fiscal period date (invoice transaction date – fiscal calendar) |
Purchase orders
This measure group is based on the PurchaseOrderCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
General ledger purchase line amount – accounting currency |
PurchaseOrderCube.LineAmountMST |
Sum |
The General ledger purchase line amount, in accounting currency. |
Company Purchase order Vendor Date (due date) Date (exchange rate date) Fiscal period date (due date – fiscal calendar) |
Expenses
This measure group is based on the LedgerTrvExpTransCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
General ledger expense amount – accounting currency |
LedgerTrvExpTransCube.AmountMST |
Sum |
The General ledger expense amount, in accounting currency. |
Company Expense Date (due date) Date (exchange rate date) Fiscal period date (due date – fiscal calendar) |
Collection letter fees
This measure group is based on the CustCollectionLetterJourCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
General ledger collection letter fee amount – accounting currency |
CustCollectionLetterJourCube.FeeMST |
Sum |
The General ledger collection letter fee amount, in accounting currency. |
Company Customer Collection letter fee Date (due date) Date (exchange rate date) Fiscal period date (due date – fiscal calendar) |
Interest notes
This measure group is based on the CustInterestTransCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
General ledger interest note amount – accounting currency |
CustInterestTransCube.InterestAmountMST |
Sum |
The General ledger interest note amount, in accounting currency. |
Company Interest note Date (due date) Date (exchange rate date) Fiscal period date (due date – fiscal calendar) |
Free text invoices
This measure group is based on the FreeTextInvoiceCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
General ledger free text amount – accounting currency |
FreeTextInvoiceCube.AmountMST |
Sum |
The General ledger free text amount, in accounting currency. |
Company Customer Free text invoice Date (due date) Date (exchange rate date) Fiscal period date (due date – fiscal calendar) |
Open vendor transactions
This measure group is based on the VendTransOpenCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Accounts payable open amount – accounting currency |
VendTransOpenCube.AmountMST |
Sum |
The Accounts payable open amount, in accounting currency. |
Company Vendor Date (due date) Date (cash discount date) Date (transaction date) Date (exchange rate date) Fiscal period date (transaction date – fiscal calendar) Fiscal period date (due date –fiscal calendar) Fiscal period date (cash discount date – fiscal date) |
Sales orders
This measure group is based on the SalesOrderCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
General ledger sales line amount – accounting currency |
SalesOrderCube.LineAmountMST |
Sum |
The General ledger sales line amount, in accounting currency. |
Company Customer Ledger sales order (sales order) Date (due date) Date (exchange rate date) Fiscal period date (due date – fiscal calendar) |
Ledger derived financial hierarchy results
This measure group is based on the LedgerDerivedFinHierarchyResults view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Ledger derived financial hierarchy results count |
Not applicable |
Count |
The number of ledger hierarchy results. |
Company Derived financial category hierarchy Ledger derived financial hierarchy results Ledger derived financial attribute value combinations |
Ledger transactions
This measure group is based on the GeneralJournalCube view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| General ledger amount - accounting currency |
GeneralJournalCube.AccountingCurrencyAmount |
Sum |
The General ledger amount, in accounting currency. |
Currency Company Derived financial category hierarchy Chart of accounts Ledger derived financial attribute value combinations Ledger transaction Date (transaction date) Date (acknowledgement date) Date (exchange rate date) Fiscal period date (transaction date – fiscal calendar) Fiscal period date (acknowledgement date – fiscal calendar) Date (document date) Fiscal period date (document date – fiscal calendar) |
| General ledger amount - transaction currency |
GeneralJournalCube.TransactionCurrencyAmount |
Sum |
The General ledger amount, in transaction currency. |
|
| General ledger amount - reporting currency |
GeneralJournalCube.ReportingCurrencyAmount |
Sum |
The General ledger amount, in reporting currency. |
Ledger balances
This measure group is based on the DimensionFocusBalanceCube view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Main account credit amount - reporting currency |
DimensionFocusBalanceCube.CreditReportingCurrencyAmount |
Sum |
The main account credit amount, in reporting currency. |
Company Ledger balance Chart of accounts Ledger derived financial attribute value combinations Date (transaction date) Date (exchange rate date) Fiscal period date (transaction date – fiscal calendar) |
| Main account debit amount - accounting currency |
DimensionFocusBalanceCube.DebitAccountingCurrencyAmount |
Sum |
The main account debit amount, in accounting currency. |
|
| Main account debit amount - reporting currency |
DimensionFocusBalanceCube.DebitReportingCurrencyAmount |
Sum |
The main account debit amount, in reporting currency. |
|
| Main account credit amount - accounting currency |
DimensionFocusBalanceCube.CreditAccountingCurrencyAmount |
Sum |
The main account credit amount, in accounting currency. |
Ledger budgets
This measure group is based on the BudgetTransactionCube view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| General ledger budget amount - transaction currency |
BudgetTransactionCube.TransactionCurrencyAmount |
Sum |
The General ledger budget amount, in transaction currency. |
Currency Company Ledger budget model Chart of accounts Ledger derived financial attribute value combinations Ledger budget Date (transaction date) Date (exchange rate date) Fiscal period date (transaction date – fiscal calendar) |
| General ledger budget amount - accounting currency |
BudgetTransactionCube.AccountingCurrencyAmount |
Sum |
The General ledger budget amount, in accounting currency. |
Exchange rates by day
This measure group is based on the BIExchangeRateView view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Exchange rate |
BIExchangeRateView.CrossRate |
Max |
The exchange rate. |
Currency Date (exchange rate date) Analysis currency |
Calculated measures
The General ledger cube contains the following calculated measures.
Calculated measure |
Aggregation |
Associated measure group |
Description |
|---|---|---|---|
General ledger sales - accounting currency |
Sum |
Ledger transactions |
The sales for the organization. |
General ledger sales returns and discounts - accounting currency |
Sum |
Ledger transactions |
The sales, returns, and discounts for the organization. |
General ledger actuals total - accounting currency |
Sum |
Ledger transactions |
The total actuals for your organization. |
General ledger sales total - accounting currency |
Sum |
Ledger transactions |
The net sales for the organization, after deducting sales returns and discounts. |
General ledger budget total - accounting currency |
Sum |
Ledger budgets |
The total budget amounts for the organization. |
General ledger cost of goods sold - accounting currency |
Sum |
Ledger transactions |
The cost of goods sold for the organization. This calculated measure is calculated from the dimension set balances for main accounts where the Main account category = COGS (Reference ID 35). |
General ledger gross profit - accounting currency |
Sum |
Ledger transactions |
The gross profit for the organization. |
Main account net amount - accounting currency |
Sum |
Ledger balances |
The net balance, in accounting currency, of a selected main account of the organization. |
Main account net amount - reporting currency |
Sum |
Ledger balances |
The net balance, in reporting currency, of a selected main account of the organization. |
General ledger accounts receivable - accounting currency |
Sum |
Ledger transactions |
The accounts receivable balance for the organization. |
General ledger accounts payable - accounting currency |
Sum |
Ledger transactions |
The accounts payable balance for the organization. |
General ledger current assets total - accounting currency |
Sum |
Ledger transactions |
The organization's available cash and other assets that could be converted to cash within a year. |
General ledger current liabilities total - accounting currency |
Sum |
Ledger transactions |
The organization's liabilities that are to be settled within the fiscal year or operating cycle, whichever period is longer. |
General ledger working capital total - accounting currency |
Sum |
Ledger transactions |
The amount of the organization's current assets that remain after current liabilities are deducted. |
General ledger assets total - accounting currency |
Sum |
Ledger transactions |
The sum of the organization's current and long-term assets. |
General ledger liabilities total - accounting currency |
Sum |
Ledger transactions |
The sum of the organization's current and long-term liabilities. |
General ledger debt total - accounting currency |
Sum |
Ledger transactions |
The sum of the organization's current and long-term obligations or borrowings. |
General ledger equity total - accounting currency |
Sum |
Ledger transactions |
Represents ownership interest in the organization. |
General ledger operating expense total - accounting currency |
Sum |
Ledger transactions |
The organization’s expenses incurred in transacting normal business operations. This includes administrative and selling expenses, but excludes interest, taxes, and cost of goods sold. |
General ledger operating income total - accounting currency |
Sum |
Ledger transactions |
The organization's income before interest and taxes. |
General ledger net income - accounting currency |
Sum |
Ledger transactions |
The amount of the organization’s revenues that remain after subtracting all costs, such as operating expenses, depreciation, interest, and taxes. |
General ledger ebitda total - accounting currency |
Sum |
Ledger transactions |
The organization's profit for a particular period before taking into account interest payments on debt, taxes, and amounts for depreciation and amortization of assets. |
Spendable balance - accounting currency |
Sum |
Ledger balances |
This measurement defines the current balance minus any commitments. |
General ledger cash position accounts receivable amount - accounting currency |
Sum |
Open customer transactions |
The cash position accounts receivable amount in accounting currency for the organization. |
General ledger cash position accounts payable amount - accounting currency |
Sum |
Open vendor transactions |
The cash position accounts payable amount in accounting currency for the organization. |
General ledger cash position amount - accounting currency |
Sum |
Open customer transactions |
The cash position amount for the organization. |
General ledger sales revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of sales for the organization. |
General ledger sales returns and discounts revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of sales, returns, and discounts for the organization. |
General ledger revised budget total - accounting currency |
Sum |
Ledger budgets |
The revised budget total for the organization. |
General ledger sales revised budget total - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of net sales for the organization, after deducting sales returns and discounts. |
General ledger cost of goods sold revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of cost of goods sold for the organization. |
General ledger gross profit revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of gross profit for the organization. |
General ledger operating expense total revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of operating expenses incurred in transacting normal business operations. |
General ledger operating income total revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of the organization's income before interest and taxes. |
General ledger net income revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of the organization’s revenues that remain after subtracting all costs, such as budgeted operating expenses, depreciation, and interest. |
General ledger ebitda total revised budget amount - accounting currency |
Sum |
Ledger budgets |
The revised budget amount of the organization's profit for a particular period before taking into account budgeted interest payments on debt, taxes, and amounts for depreciation and amortization of assets. |
Key performance indicators
The following sections describe the key performance indicators (KPIs) in the General ledger cube.
KPI calculations
The KPIs in the General ledger cube depend on account categories. For information about account categories, see Main account categories and analysis cubes.
An asterisk (*) indicates that the KPI calculations use accumulated amounts. Accumulated amounts, such as some period-to-date amounts, are not stored in the Microsoft Dynamics AX database. For example, to calculate a period-to-date amount, all the transactions from the beginning of the fiscal calendar to the end of the previous period are totaled. Then all the amounts from the beginning of the fiscal calendar to the current date are totaled. The difference between these two amounts is the period-to-date amount.
You can use the information in the following table to help verify the information in your KPIs. Export your chart of accounts to Office Excel and verify that the accounts that should be included in the KPIs are assigned to the correct ledger account category.
KPI |
Associated measure group |
Calculation |
|---|---|---|
Accounts payable turnover* |
Ledger transactions |
[Cost of Goods Sold (Reference ID = 35) + (Ending Inventory (Reference ID = 5) for period - Beginning Inventory (Reference ID = 5) for period)] / [Beginning Accounts Payable (Reference ID = 15) + Ending Accounts Payable (Reference ID = 15) / 2] Note * Only the Inventory and Accounts Payable parts of this KPI are accumulated. |
Accounts receivable turnover* |
Ledger transactions |
[Sales (Reference ID = 33) *-1] - Sales Returns and Discounts (Reference ID = 34) / [Beginning Accounts Receivable (Reference ID = 4) + Ending Accounts Receivable (Reference ID = 4) / 2] Note * Only the Accounts Receivable part of this KPI is accumulated. |
Average days to pay* |
Ledger transactions |
Ending Accounts Payable (Reference ID = 15) balance for the period / [Cost of Goods Sold (Reference ID = 35) + (Ending Inventory (Reference ID = 5) for the period - Beginning Inventory (Reference ID=5) for the period)] * Number of days in the period Note * Only the Accounts Payable and Inventory parts of this KPI are accumulated. |
Average collection period |
Ledger transactions |
(Accounts Receivable (Ref ID 4) / ((Sales (ref id = 33) + Sales returns and discounts (ref id 34)*-1) * number of days in fiscal year to date] |
Cash position* |
Ledger transactions |
Cash (Reference ID = 1) + Cash Equivalents (Reference ID = 2) Note * Accumulation applies to all calculations. |
Cash ratio* |
Ledger transactions |
Cash Equivalents (Reference ID = 2) + Cash (Reference ID = 1) / (Accounts Payable (Reference ID = 15) + Notes Payable (Reference ID = 16) + Current Maturities on Long Term Debt (Reference ID = 17) + Taxes Payable (Reference ID = 18) + Interest Payable (Reference ID = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current) (Reference ID = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Other Current Liabilities (Reference ID = 23)) Note * Accumulation applies to all calculations. |
Cost of goods sold |
Ledger transactions |
Cost of Goods Sold (Reference ID = 35) |
Current ratio* |
Ledger transactions |
[Cash (Reference ID = 1) + Cash Equivalents (Reference ID = 2) + Short Term Investments (Reference ID = 3) + Accounts Receivable (Reference ID = 4) + Inventory (Reference ID = 5) + Notes Receivables (Reference ID = 6) + Work in Process (Reference ID = 7) + Prepaid Expenses (Reference ID = 8) + Other Current Assets (Reference ID = 9) + Inventory (Reference ID = 5)] / [Accounts Payable (Reference ID = 15) + Notes Payable (Reference ID = 16) + Current Maturities on Long Term Debt (Reference ID = 17) + Taxes Payable (Reference ID = 18) + Interest Payable (Reference ID = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current) (Reference ID = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Other Current Liabilities (Reference ID = 23)] Note * Accumulation applies to all calculations. |
Debt to equity* |
Ledger transactions |
Accounts Payable (Reference ID = 15) + Notes Payable (Reference ID = 16) + Current Maturities on Long Term Debt (Reference ID=17) + Taxes Payable (Reference ID = 18) + Interest Payable (Reference ID = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current) (Reference ID = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Other Current Liabilities (Reference ID = 23) + Long Term Debt (Reference ID = 24) / Common Stock (Reference ID = 25) + Preferred Stock (Reference ID = 26) + Additional Paid in Capital: Common (Reference ID= 27) + Additional Paid in Capital: Preferred (Reference ID = 28) + Retained Earnings (Reference ID = 29) + Treasury Stock (Reference ID = 30) + Common Dividends (Reference ID=31) + Preferred Dividends (Reference ID=32) + Encumbrance (Reference ID = 54) + Pre-encumbrance (Reference ID=55) Note * Accumulation applies to all calculations. |
Debt to total assets* |
Ledger transactions |
[Accounts Payable (Reference ID = 15) + Notes Payable (Reference ID = 16) + Current Maturities on Long Term Debt (Reference ID = 17) + Taxes Payable (Reference ID = 18) + Interest Payable (Reference ID = 19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current) (Reference ID = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Other Current Liabilities (Reference ID = 23) + Long Term Debt (Reference ID = 24)] / [Cash (Reference ID = 1) + Cash Equivalents (Reference ID = 2) + Short Term Investments (Reference ID = 3) + Accounts Receivable (Reference ID = 4) + Inventory (Reference ID = 5) + Notes Receivables (Reference ID = 6) + Work in Process (Reference ID = 7) + Prepaid Expenses (Reference ID = 8) + Other Current Assets (Reference ID = 9) + Long Term Investments (Reference ID = 10) + Property Plant and Equipment (Reference ID = 11) + Accumulated Depreciation (Reference ID = 12) + Intangible Assets (Reference ID = 13) + Other Assets (Reference ID = 14)] Note * Accumulation applies to all calculations. |
Gross profit |
Ledger transactions |
[Sales (Reference ID = 33)*-1] - Sales Returns and Discounts Reference ID = 34) - Cost of Goods Sold |
Gross profit margin |
Ledger transactions |
[(Sales (Reference ID = 33) *-1] - Sales Returns and Discounts (Reference ID = 34) - Cost of Goods Sold |
Inventory turnover* |
Ledger transactions |
Cost of Goods Sold (Reference ID = 35) / [Beginning Inventory (Reference ID = 5) + Ending Inventory (Reference = 5) / 2] Note * Only the Inventory part of this KPI is accumulated. |
Net income |
Ledger transactions |
Result A - Result B Result A = [Sales (Reference ID = 33) + Other Income (Reference ID = 48) + Revenues not producing working capital (Reference ID = 50)+ Sales Returns and Discounts (Reference ID = 34)] *-1 Result B = Cost of Goods Sold (Reference ID = 35) + Selling Expense (Reference ID = 36) + Administrative Expense (Reference ID = 37) + Manufacturing Expense (Reference ID = 38) + Travel and Entertainment Expenses (Reference ID = 39) + Project Operation Expenses (Reference ID = 40) + Salaries Expense (Reference ID = 41 ) + Other Employee Expenses (Reference ID = 42) + Interest Expense (Reference ID = 43) + Tax Expense (Reference ID = 44) + Depreciation Expense (Reference ID = 45) + Income Tax Expense (Reference ID = 46) + Other Expenses (Reference ID = 47) + Charges not using working capital (Reference ID = 49) +Gain or Loss on Asset Disposal (Reference ID = 51) + Amortization of Intangible Assets (Reference ID = 52) |
Quick ratio* |
Ledger transactions |
[Cash (Reference ID =1) + Cash Equivalents (Reference ID = 2) + Short Term Investments (Reference ID = 3) + Accounts Receivable (Reference ID = 4) + Notes Receivables (Reference ID = 6)] / (Accounts Payable (Reference ID = 15) + Notes Payable (Reference ID = 16) + Current Maturities on Long Term Debt (Reference ID = 17) + Taxes Payable (Reference ID = 18) + Interest Payable (Reference ID=19) + Dividends Payable (Reference ID = 20) + Leases Payable (Current) (Reference ID=21) + Sinking Fund Payable (Current) (Reference ID = 22) + Other Current Liabilities (Reference ID = 23) Note * Accumulation applies to all calculations. |
Return on total assets* |
Ledger transactions |
(Result A/Result B) X 100 Result A: Net income + Interest expense (Reference ID =43) + Tax expense (Reference ID = 44) + Income tax expense (Reference ID = 46) Result B: Cash (Reference ID = 1) + Cash Equivalents (Reference ID = 2) + Short Term Investments (Reference ID = 3) + Accounts Receivable (Reference ID = 4) + Inventory (Reference ID = 5) + Notes Receivables (Reference ID = 6) + Work in Process (Reference ID = 7) + Prepaid Expenses (Reference ID = 8) + Other Current Assets (Reference ID = 9) + Long Term Investments (Reference ID = 10) + Property Plant and Equipment (Reference ID = 11) + Accumulated Depreciation (Reference ID = 12) + Intangible Assets (Reference ID = 13) + Other Assets (Reference ID = 14) |
Revenue budget variance |
Ledger budgets |
Sum of budget amounts for the period selected for the accounts / Sum of actual balances for revenue accounts for the same time period = Variance. The result is displayed as a percentage. The following ledger account categories are included in the sum of the budget amounts and in the sum of the actual amounts: Sales (Reference ID = 33) + Other Income (Reference ID = 48) + Revenues not producing working capital (Reference ID =50) + Sales Returns and discounts (Reference ID = 34) + Gain/Loss on Asset Disposal (Reference ID = 51) Note If necessary, the amounts for each account category are summed by dimension combination to compare the budget versus actual amounts by department, cost center, and so on. Reference ID 51 is included only if the balance of the accounts in that account category is negative. Otherwise it is excluded. |
Times interest earned |
Ledger transactions |
Result A/ Interest Expense (Reference ID = 43) Result A: Net income + Interest expense (Reference ID =43) + Tax expense (Reference ID = 44) + Income tax expense (Reference ID = 46) |
Total expenses |
Ledger transactions |
Cost of Goods Sold (Reference ID = 35) + Selling Expense (Reference ID = 36) + Administrative Expense (Reference ID = 37) + Manufacturing Expense (Reference ID = 38) + Travel and Entertainment Expense (Reference ID = 39) + Project Operation Expenses (Reference ID = 40) + Salaries Expense (Reference ID = 41 ) + Other Employee Expense (Reference ID = 42) + Interest Expense (Reference ID = 43) + Tax Expense (Reference ID = 44) + Depreciation Expense (Reference ID = 45) + Income Tax Expense (Reference ID = 46) + Other Expenses (Reference ID = 47) + Charges not using working capital (Reference ID =49) + Amortization of Intangible Assets (Reference ID = 52) + Gain/Loss on Asset Disposal (Reference ID = 51) Note Reference ID 51 is included only if the balance of the accounts in that account category is positive. Otherwise it is excluded. |
Total revenue |
Ledger transactions |
[Sales (Reference ID = 33) + Other Income (Reference ID = 48) + Revenues not producing working capital (Reference ID =50) +Sales Returns and Discounts (Reference ID = 34) + Gain/Loss on Asset Disposal (Reference ID = 51)] *-1 Note Reference ID 51 is included only if the balance of the accounts in that account category is negative. Otherwise it is excluded. |
Earnings before income tax |
Ledger transactions |
( Sales (ref_id = 33) + Other Income (ref id = 48) + Sales Returns and Discounts (ref id = 34)) *-1 ) - ( COGS (ref id =35) + Selling Expense (ref id = 36) + Admin Expense (ref id = 37) + Manuf Expense (ref id = 38) + T& E Expense (ref id = 39) + Project and Operation Expense (ref id = 40) + Saleries Expense (ref id = 41) + Other Employee Expenses (ref id = 42) + Interst Expense (ref id = 43) + Tax Expense (ref id = 44) + Depreciation Expense (ref id = 45) + Other Expenses (ref id = 47) + Charges not using working capital (ref ID = 49) ) + Revenues not producing working capital (Reference ID=50) + Amortization of intangible assets (ref id = 52) + Gain/loss on asset disposal (refid = 51) |
Expense budget variance |
Ledger budgets |
Sum budget amounts for the: Cost of Goods Sold (refid = 35)+Selling expense (ref id = 36) + Admin exp (refid = 37) + Manu exp (ref id = 38) + Travel and Enter exp (ref id = 39) + Project expenses (ref id = 40) + Salaries exp (ref id = 41 ) + Other emp exp (ref id = 42) + Interest exp (ref id = 43) + Tax exp (ref id = 44) + Depr exp (ref id = 45) + Income tax exp (ref id = 46) + Other expenses (ref id = 47) + Charges not using working capital (Reference ID = 49) + Amortization of intangible assets (ref id = 52) + Gain/loss on asset disposal (refid = 51) / Sum of actual balances for expense accounts for the same time period : Cost of Goods Sold (refid = 35)+Selling expense (ref id = 36) + Admin exp (refid = 37) + Manu exp (ref id = 38) + Travel and Enter exp (ref id = 39) + Project expenses (ref id = 40) + Salaries exp (ref id = 41 ) + Other emp exp (ref id = 42) + Interest exp (ref id = 43) + Tax exp (ref id = 44) + Depr exp (ref id = 45) + Income tax exp (ref id = 46) + Other expenses (ref id = 47) + Charges not using working capital (Reference ID = 49) +Amortization of intangible assets (ref id = 52) + Gain/loss on asset disposal (refid = 51) Note: If necessary, the amounts for each account category are summed by dimension combinationto compare the budget versus actual amounts by department, cost center, and so on. Reference ID51 is included only if the balance of the accounts in that account category is positive. Otherwise it isexcluded. Note Reference ID 51 is included only if the balance of the accounts in that account category is positive. Otherwise it is excluded. |
Accounts payable |
Ledger transactions |
Accounts Payable (Reference ID = 15) |
Notes payable |
Ledger transactions |
Notes Payable (Reference ID = 16) |
Interest payable |
Ledger transactions |
Interest Payable (Reference ID = 19) |
Other liabilities |
Ledger transactions |
Current Maturities on Long Term Debt (Reference ID = 17) + Taxes Payable (Reference ID = 18) + Leases Payable (Current) (Reference ID = 21) + Sinking Fund Payable (Current) (Reference ID = 22) + Other Current Liabilities (Reference ID = 23) + Dividends Payable (Reference ID = 20) |
Long-term debt |
Ledger transactions |
Long Term Debt (Reference ID = 24) |
Cash and cash equivalents |
Ledger transactions |
Cash (Reference ID = 1) + Cash Equivalents (Reference ID = 2) |
Short-term investment |
Ledger transactions |
Short Term Investments (Reference ID = 3) |
Accounts receivable |
Ledger transactions |
Accounts Receivable (Reference ID = 4) |
Notes receivable |
Ledger transactions |
Notes Receivable (Reference ID = 6) |
Long-term investment |
Ledger transactions |
Long Term Investments (Reference ID = 10) |
Inventory and WIP |
Ledger transactions |
Inventory (Reference ID = 5) + Work in progress (Reference ID = 7) |
Other org assets |
Ledger transactions |
Prepaid Expenses (Reference ID = 8) + Other Current Assets (Reference ID = 9) + Property Plant and Equipment (Reference ID = 11) + Accumulated Depreciation (Reference ID = 12) + Intangible Assets (Reference ID = 13) + Other Assets (Reference ID = 14) |
Days cash on hand |
Ledger transactions |
Cash (Reference ID =1) + Cash Equivalents (Reference ID = 2)/[Operating expense - Depreciation expense (Reference ID = 45)/Number of days in period] Note The KPI needs to calculate from the start of the fiscal year through the last completed fiscal time span. |
Days working capital |
Ledger transactions |
[Working capital X number of days in period]/[(Sales (Reference ID = 33) * -1) - Sales Returns and Discounts (Reference ID = 34) Note The KPI needs to calculate from the start of the fiscal year through the last completed fiscal time span. |
Days inventory on hand |
Ledger transactions |
Number of days in period/Inventory turnover Note The KPI needs to calculate from the start of the fiscal year through the last completed fiscal time span. Note Inventory turnover is defined as: Cost of Goods Sold (Reference ID = 35) / [Beginning Inventory (Reference ID = 5) + Ending Inventory (Reference = 5) / 2]. Inventory turnover needs to likewise calculate from the start of FY thru last completed fiscal time span. |
Fixed asset turnover |
Ledger transactions |
[Sales (Reference ID = 33) *-1) - Sales Returns and Discounts (Reference ID = 34)/Property Plant and Equipment (Reference ID = 11) +Accumulated Depreciation (Reference ID = 12) Note The KPI needs to calculate from the start of the fiscal year through the last completed fiscal time span. |
EBITDA margin |
Ledger transactions |
(EBITDA/[(Sales (Reference ID = 33) *-1) - Sales Returns and Discounts (Reference ID = 34))*100 Note EBITDA is defined as Net income - Interest Expense(Reference ID = 43) - Tax Expense (Reference ID = 44) -Depreciation Expense (Reference ID = 45) - Income TaxExpense (Reference ID = 46) -Amortization of Intangible Assets (Reference ID = 52) |
Role Centers
The following table lists the Role Centers and web parts that display the KPIs associated with the General ledger cube.
Role Center |
Web parts and KPIs |
|---|---|
Credit and collections manager |
Operational efficiency (displayed by default):
|
Controller |
Profitability analysis (displayed by default):
Investment analysis:
Short-term solvency:
Operational efficiency:
Revised long term solvency:
|
CFO |
Profitability analysis (displayed by default):
Investment analysis:
Short-term solvency:
Operational efficiency:
Long-term solvency:
|
Account manager |
Profitability analysis (displayed by default):
Investment analysis:
Short-term solvency:
Operational efficiency:
Long-term solvency:
|
Accountant |
Operational efficiency (displayed by default):
Profitability analysis:
Investment analysis:
Revised short-term solvency:
Long-term solvency:
|
Treasurer |
Treasurer asset analysis (displayed by default):
Profitability analysis:
Investment analysis:
Short-term solvency:
Investment analysis:
Operational efficiency
Long-term solvency:
Treasurer liability analysis:
|
Security
The General ledger cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.
Accountant
Accounting manager
Accounting supervisor
Accounts payable centralized payments clerk
Accounts payable clerk
Accounts payable manager
Accounts payable payments clerk
Accounts receivable centralized payments clerk
Accounts receivable clerk
Accounts receivable manager
Accounts receivable payments clerk
Budget clerk
Budget manager
Chief executive officer
Chief financial officer
Collections agent
Collections manager
Compliance manager
Financial controller
Treasurer