Data Model - Finance (Bevica Power BI)

Finance dataset reference for GL, budgets, bank ledgers, and KPIs in Bevica Power BI.

Data Model - Finance (Bevica Power BI)

Overview

The Finance data model reports on GL Entries primarily.

Tables

Table Name	Comments
Chart of Accounts	Includes hierarchy from Account Categories
Companies (if multi company setup)
GL Entries
GL Budgets
Bank Ledger Entries
Dates	Auto generated based on date setup
Time Intelligence	For calculating comparisons - see Time Intelligence
Dimension Set Entries	Filters entries by the posted Dimensions (Global/Shortcut Dimensions from GL Setup)

Features

• GL Budget Selector must be used to isolate to a single GL Budget
• Debit/Credit Switcher can be used to switch debits to credits

Measures

GL Entries

• G/L Amount = sum of G/L Amount
• G/L Debit Amount / Credit Amount = sum of debit or credit GL Amount as appropriate
• ACY = sum of G/L Amount additional reporting currency - ACY (if set up)
• Closing Year Entry should be used to exclude year end closing entries

G/L Budgets

• G/L Budget Amount = sum of budget amount
• G/L Budget Amount ACY = above but in ACY
• Avg. FX Rate ACY = shows the average exchange rate
• Note - you must select a single budget using the GL Budget Selector

G/L Actual vs Budget

• G/L Act. vs Budget = Actual GL amount less budget
• G/L Act. vs Budget = above variance as percent

Bank Ledger Entries

• Bank Ledger Amount = sum of bank ledger

  • LCY = company LCY
  • ACY = ACY
  • FX = in originating currency

KPIs

These are available from the Nov

The following KPIs are available. They use Account Categories as their basis so if you have customised the account categories or do not use them these may provide inaccurate results.

See: https://learn.microsoft.com/en-us/dynamics365/business-central/finance-general-ledger#account-categories

You should use Time Intelligence functions in most instances to set the calculations to Cumulative Totals

Quick Ratio

https://en.wikipedia.org/wiki/Quick_ratio

• Quick Ratio = (Cash + Accounts Receivables) / Current Liabilities
• Quick Ratio Cash = GL Amount where Account Category Level 1 or 2 are Cash
• Quick Ratio Accounts Receivables = GL Amount where Account Category Level 1 or 2 are Accounts Receivable
• Quick Ratio Current Liabilities = GL Amount where Account Category Level 1 or 2 are Current Liabilities

Gross Profit

• Gross Profit = Income - COGS
• Net Profit = Gross Profit - Expenses
• GP Income = GL Amount where Account Category Parent Income
• GP COGS = GL Amount where Account Category Parent Cost of Goods Sold
• GP Expense = GL Amount where Account Category Parent Expense