Financial analysis

Financial analysis uses Microsoft Power BI to bring together financial key performance indicators (KPIs), charts, and financial statements. Power BI is embedded in the application. The focus of Financial analysis is analytical reporting. Personas across an organization can view, research, understand, and act.

Financial analysis combines data from the general ledger and subledgers to give a more complete picture of the financial health of an organization.

The Financial analysis workspace is focused on letting you view and filter the data on existing reports. You are able to add new visuals to the Financial analysis workspace. The Financial analysis workspace is available for the current company as well as all companies to show data for all legal entities, regardless of the legal entities that the role has access to.

• Add or edit Power BI visualizations on your dashboard

Dynamics 365 Finance setup

General ledger

The main account type and the main account categories are used to fill in appropriate default main accounts on the Balance sheet financial statement and the various Income statement financial statements in Financial analysis.

On the Main accounts page, you must define your main account so that one of the following types is assigned to it:

• Revenue
• Expense
• Assets
• Liabilities
• Equity

Do not assign any other main account type, such as Balance sheet or Profit and Loss, to your main accounts. Reporting can't determine the type of main account when other main account types are assigned, because they aren't granular enough. The type of main account must be determined to show liabilities and revenue as positive amounts on financial reports.

To appear on the financial statements and to be included in various other visuals, such as KPIs, each main account must be assigned a main account category. The main account categories have been enhanced so that they include a display order. The display order is used specifically on financial statements in Financial analysis. After you edit or add a new main account category, you can change the Display order value to define the order that the main account categories should be shown in on a financial statement. If you must change the display order for many main account categories, you can use the Open in Excel feature to quickly edit and publish the changes back to the application.

Entity store

The data for Financial analysis is pulled from the Entity store (System administration > Setup > Entity store). If you open the CFO overview or Financial analysis workspace, and the following warning message appears in the visuals, you must update the entities.

You must update the following entities to see data in the Financial analysis workspace:

• Financial reporting transaction data version 3
• Credit and collections V2
• LedgerCovLiquidityMeasurement
• Purchase cube
• Sales cube

You can define a recurring batch to regularly update the data in the entities. Because each entity is completely rebuilt during an update, select the time and frequency of entity updates carefully. The primary entity that is used for financial statements is the FinancialReportingTransactionData entity. Therefore, you might decide to update that entity more often.

Security

Currently, the data on embedded Power BI reports can't be limited to the legal entities that the user has access to. Therefore, the embedded Power BI reports are controlled through duties in the security setup. The duties that are defined allow access to data for either all legal entities or only the active company. The following table shows the duties that exist and the roles that they are assigned to. The duties can be removed or assigned to different roles, based on your organization's requirements.

Financial reporting vs. Financial analysis

Although Financial analysis contains financial statements, it isn't a replacement for Financial reporting in the application. The default financial statements in Financial analysis are limited in scope and don't include all types of financial statements. Financial reporting is still the primary tool for designing, creating, and generating statutory financial statements.

The following comparison chart will help differentiate the two options:

The following financial statements are available:

• Trial balance
• Balance sheet
• Income statement by region
• Income statement actual vs. budget
• Income statement with variances
• 12-month trend income statement
• Expenses three-year trend
• Expenses by vendor
• Sales by customer

Edit visuals

In previous releases of Financial analysis, none of the visuals could be edited. In future releases, users who have the appropriate security will be able to create new visuals, copy existing visuals, and edit visuals. Although the .pbix files that contain the reports are available as resources, we don't recommend that you edit the default reports. Additional changes will be made to the data model, default reports, and custom financial statement visual that are used to create the financial statements. Therefore, to take advantage of new features and changes to the data model in the next release, you will have to redo any changes that you made to the default reports through Microsoft Power BI Desktop.

Filtering

Users can filter the report by using the Filter pane on the left. This pane is the same pane that is available through Power BI Desktop. There are various levels of filtering, some of which might not be available, depending on what you've selected on a page (tab) or whether you're using the drill-through capabilities:

• Report-level filters – These filters are applied to all visuals on all pages (tabs).
• Page-level filters – These filters are applied to all visuals on the active tab. These filters are applied on top of the report-level filters.
• Visual-level filters – These filters are applied only to the selected visual. These filters are applied on top of the page level filters.
• Drill-through filter – This filter filters from a "source" visual that is applied to the current visual when you drill through from the source visual to the current visual.

To remove a specific filter value, select the eraser symbol next to it. Don't remove a filter by selecting the X. If you select the X, the field that you're filtering on is removed as a filter option. If you accidentally remove a field from the filter, close the workspace, and then reopen it. The default filter settings will be reapplied.

By default, when you first open workspaces, the active legal entity is used as the report-level filter. Depending on their security, users might be able to add other legal entities or change the default legal entity that is selected in the filter.

The Fiscal calendar filter is required so that the correct calendar is used for the visual. By default, the report-level filter is set to the active legal entity's fiscal calendar. If you change the filter to a fiscal calendar that has a different start or end date, the beginning balances won't be included. Therefore, your Balance sheet financial statements won't show the correct balances. If you select an additional fiscal calendar in the filter, you will have an additional set of columns. Each additional set of columns shows the amounts for a different fiscal calendar.

The Posting layer filter is also required. By default, the filter is set to Current. You can select additional posting layers in the filter to show the aggregated amounts.

Filters are also available for the Date and Fiscal year fields. Typically, these filters are applied at the page level. By default, the Date filter uses a relational date that you can change. You can also remove the relational date filter and use the Fiscal year filter instead.

Currency

All visuals that report on general ledger data show amounts in the accounting currency. Therefore, when you filter on the legal entity, you must be careful to include only legal entities that have the same accounting currency. Otherwise, you will aggregate data in different currencies.

All visuals that report on subledger data, such as the Cash flow forecast and Top 10 visuals, show amounts in the system currency. The system currency and system exchange rate type are defined on the System parameters page.

The Balance by bank account visual uses amounts in the bank accounts' currency.

Dimensions

The default financial statements don't include any financial dimensions but are focused only on the main account. Support for financial dimensions will be available in future releases, when the reports become editable. Organizations will then be able to filter on financial dimension values.

Some financial statements contain dimensions that are based on subledger transactions. The goal of the new financial statements is to enable filtering on dimensions that aren't set up as financial dimensions. For example, the default Expenses by vendor report lets you expand down beyond the main account, so that you can see the balances broken down by vendor. The vendor isn't set up as a financial dimension. Instead, the system returns to the originating subledger transaction to find the vendor.

The following dimensions are used on the default reports. None of these dimensions are financial dimensions.

• Vendor
• Vendor group
• Customer
• Customer group
• Country/region
• State/province
• City

Drill on data

Various levels of drilling are available through Power BI. Each level has a different name and different functionality. You can also drill on rows and columns. This section discusses the various options by using the Trial balance financial statement as an example and showing how you can drill on the rows. The same functionality exists for columns. You just have to change the Drill on setting.

In the following illustration, the Trial balance statement is collapsed to the highest level of the row hierarchy, the main account type.

To view the next level of the hierarchy, the main account categories, you can set the Drill on field to Rows and then select the Expand button (the third button after the Drill on field). You now see all the main account categories expanded. Currently, Power BI doesn't let you expand only one row or column but still see all the other rows or columns.

To expand to the main accounts for all rows, you can again use the Expand button. However, to drill down to the main accounts for only one row, first select the Drill down button (the single downward-pointing arrow on the right side of the window), and then select the row to drill down on. The following illustration shows the result when the Sales row is selected after the Drill down button is selected.

After you drill down on a single row, multiple clicks are required to return to the full trial balance. The Drill up button (the first button after the Drill on field) drills up only in the context of the Sales category, as shown in the following illustration.

You can continue to use the Drill up button to return to the highest level of summarization for the rows.

Power BI also has a button that lets you go to the next level in the hierarchy (the second button after the Drill on field). The effect of this button differs from the effect of the Expand button (the third button after the Drill on field), which is used to expand the hierarchy. When you expand the hierarchy, the hierarchy is maintained on the report. For example, as was shown earlier, if you expand on the main account type, you still see the main account type on the report. However, when you go to the next level in the hierarchy, the report no longer shows the parent in the hierarchy, as shown in the following illustration.

To see the transaction details behind the summarized balances, you can select some amounts to drill back into Financial and Operations.

The drill-back from the financial statements takes you to the Accounting source explorer (ASE), not to the voucher transactions. The ASE doesn't show just the accounting entries in the general ledger. Instead, it shows the details of the subledger transaction. Therefore, you get much more detail about the originating transaction and can use it for analysis. For example, you can see who the vendor or customer was, what the customer bought or the vendor sold, and even what project was on the transaction.

The following filters from the financial statements are sent to the ASE, so that the ASE shows the transactions that are aggregated:

Required fields for filtering:

• Legal entity
• Fiscal calendar
• Year
• Main account ID

Optional fields for filtering:

• Quarter
• Month
• Period

If you don't expand down far enough on a row, the drill-down doesn't work. For example, if you expand down only to the main account category, you can't drill down into the ASE on the balance, because the main account is a required field for filtering in the ASE.

If you expand down too far on a row, the additional filters on the financial statements aren't sent to the ASE. Therefore, you might see a difference in your numbers. For example, if you expand down to the country or region on the rows of the Income statement by region financial statement, the country or region isn't included as a filter in the ASE.

Hierarchies

The default financial statements use two hierarchies to drill and expand on the data. One hierarchy is for the rows, and the other hierarchy is for the columns. Both hierarchies are predefined in the design of the financial statement. For most financial statements, the row hierarchy is Main account type > Main account categories > Main account. However, some reports have additional fields, such as Country and Region. The additional nodes of the hierarchy are based on subledger data for each transaction.

For the columns, the hierarchy is focused on the legal entities and the fiscal periods. For most financial statements, the column hierarchy is Legal entity > Fiscal calendar > Fiscal year > Quarter > Period.

Currently, the financial statements don't support the organizational hierarchies, which let you aggregate data.

Data limitations

The financial statement visuals have a limit on the number of rows that can be shown. Currently, the limit is set to 30,000. If you exceed this limit, the visual will have a warning symbol to notify you about this situation.

If the maximum is exceeded, the totals that appear on the financial statement will be incorrect, because not all the rows were loaded into the visual.

Empty rows

Power BI doesn't provide an option to hide and show empty rows. If a row doesn't have any data, the row won't appear in the visual.

Additional resources for Power BI

The information in the following resources isn't required in order to enable the embedded reports for the Financial analysis workspace in a production environment. Instead, they are helpful for dev boxes and if you want to embed your own Power BI reports.

• Add analytics to workspaces by using Power BI Embedded

Breakdown of account categories by page and visual

Account categories used by page and visual

Measures used in the previous table

'Transaction detail'[Current assets]

Current assets = 
CALCULATE (
    'Transaction detail'[Actuals credits positive], (changed in 10.0.26)
    'Account category'[Account category name]
        IN {
            "CASH",
            "CASHEQUIV",
            "SHORTTERMINVEST",
            "AR",
            "INV",
            "NOTESREC",
            "WIP",
            "PREPAIDEXP",
            "CURASS", (added in 10.0.26)
            "OTHERCA" }
)

'Transaction detail'[Current liabilities]

Current liabilities = 
CALCULATE(
    'Transaction detail'[Actuals credits positive], (changed in 10.0.26)
    'Account category'[Account category name] 
        IN { 
            "AP",
            "NOTESPAY",
            "CURRENTMATLTD",
            "TAXPAY",
            "INTPAY",
            "DIVPAY",
            "LEASEPAY",
            "SINKPAY",
            "CURLIAB", (added in 10.0.26)
            "OTHERCURLIA",
            "LONGTERMDEBT" }
)

'Transaction detail'[Cost of goods sold]

Cost of goods sold = 
CALCULATE
(
    'Transaction detail'[Actual operating],
    'Account category'[Account category name] = "COGS"
)

'Transaction detail'[Total assets]

// After version 10.0.37:
Total assets =
CALCULATE (
    'Transaction detail'[Actual operating],
    'Account'[Account type] = "Asset"
)

// Before version 10.0.37:
Total assets = 
CALCULATE
(
    'Transaction detail'[Actual operating],
    'Account category'[Account category name] 
        IN { 
            "CASH", 
            "CASHEQUIV", 
            "SHORTTERMINVEST", 
            "AR", 
            "INV", 
            "NOTESREC",
            "WIP",
            "PREPAIDEXP",
            "OTHERCA",
            "LONGTERMINVEST",
            "PPE",
            "ACCUDEP",
            "INTANASSET",
            "OTHERASSET" }
)

'Transaction detail'[Total operating expenses]

Total operating expenses = 
CALCULATE
(
    'Transaction detail'[Actual operating],
    'Account category'[Account category name] 
        IN { 
            "COGS",
            "SELLEXP",
            "ADMINEXP",
            "MANUEXP",
            "TANDEEXP",
            "PROJEXP",
            "SALARYEXP",
            "OTHEREMPEXP",
            "INTEXP",
            "TAXEXP",
            "DEPREXP",
            "OTHEREXP",
            "CHRNOTUSEWORKCAP" }
)

'Transaction detail'[Total expenses]

// After version 10.0.37:
Total expenses =
CALCULATE (
    'Transaction detail'[Actual operating],
    'Account'[Account type] = "Expense"
)

// Before version 10.0.37:
Total expenses = 
'Transaction detail'[Total operating expenses] +
CALCULATE
(   
    'Transaction detail'[Actual operating],
    'Account category'[Account category name] IN 
    { 
        "INCTAXEXP",
        "GAINLOSSASSET",
        "AMORTINTAGASSET"
    }
)

'Transaction detail'[Total revenue]

// After version 10.0.37:
Total Revenue =
CALCULATE (
    'Transaction detail'[Actual operating],
    'Account'[Account type] = "Revenue"
)

// Before version 10.0.37:
Total revenue = 
CALCULATE(
    'Transaction detail'[Actual operating],
    'Account category'[Account category name] 
        IN {
            "SALES", 
            "OTHERINC", 
            "SALESRETDIS" }
)

'Transaction detail'[Additional earnings]

Additional earnings = 
CALCULATE (
'Transaction detail'[Actual operating],
'Account category'[Account category name]
IN { 
            "REVNOTPRODWORKCAP", 
            "GAINLOSSASSET", 
            "AMORTINTAGASSET" } 
)

'Transaction detail'[Actuals]

Actuals = CALCULATE(-sum('General ledger attributes'[PRIMARYAMOUNT]), 'General ledger attributes'[ISCREDIT] = "Yes") + CALCULATE(sum('General ledger attributes'[PRIMARYAMOUNT]), 'General ledger attributes'[ISCREDIT] = "No")

'Transaction detail'[Subledger Total revenue]

Subledger Total revenue =
CALCULATE (
    'Transaction detail'[Subledger Actual operating],
    'Account'[Account type] = "Revenue"
)

'Transaction detail'[Subledger Total expenses]

Subledger Total expenses =
CALCULATE (
    'Transaction detail'[Subledger Actual operating],
    'Account'[Account type] = "Expense"
)

'Transaction detail'[Subledger Actual operating]

Subledger Actual operating =
CALCULATE (
    'Transaction detail'[Subledger Actuals credits positive],
    'General ledger attributes'[Transaction Type] = "RegularOperating"
)

'Transaction detail'[Subledger Actuals credits positive]

Subledger Actuals credits positive =
CALCULATE ('Transaction detail'[Subledger Actuals], Account[Account typical balance] = "Debit" )
        - CALCULATE ( 'Transaction detail'[Subledger Actuals], Account[Account typical balance] = "Credit")

'Transaction detail'[Subledger Actuals]

Subledger Actuals = SUM('Transaction detail'[ACCOUNTINGCURRENCYAMOUNT])

Account category description by name table

Use the following table to find the long form name of an account category.

Custom account categories

The CFO Overview workspace doesn't currently support custom account categories. As an alternative, you can create a Power BI report and publish it to a workspace. For more information, see Connect to the services used with Power BI.