Prepare financial reporting with financial data and account categories

  • [アーティクル]

The Financial Reports feature gives you insights into the financial data shown on your chart of accounts (COA). You can set up financial reports to analyze figures in general ledger (G/L) accounts, and compare general ledger entries with budget entries. The results display in charts and reports in your Role Center, such as the Cash Flow chart, and the Income Statement and Balance Sheet reports. You access these two reports, for example, with the Financial Statements action in the Business Manager and Accountant home pages.

Business Central provides sample financial reports you can use right away as templates. You can also set up your own reports to specify the figures to compare. For example, you can create financial reports to calculate profit margins using dimensions such as departments or customer groups. The number of financial reports you can create is unlimited and require no involvement of a developer.

Prerequisites for financial reporting

Setting up financial reports requires an understanding of the structure of your chart of accounts. There are three key concepts that you likely need to pay attention to before you design your financial reports:

  • Map G/L posting accounts to G/L account categories.
  • Design how you use dimensions.
  • Set up G/L budgets.

G/L account categories simplify your financial report definitions and make them more resilient to changes in the chart of accounts structure. Learn more at Use G/L account categories to change the layout of your financial statements.

Setting up dimensions allows you to slice and dice your financial data in ways that make sense for your organization. Learn more at Work with Dimensions.

If you want to view general ledger entries as percentages of budget entries, you must create G/L budgets. Learn more at Create G/L Budgets.

Financial reports

Financial reports arrange accounts from your chart of accounts in ways that make data easier to present. You can set up various layouts to define the information you want to extract from the chart of accounts. Financial reports also provide a place for calculations that can't be made directly in the chart of accounts. For example, you can create subtotals for groups of accounts and then include that total in other totals. Another example is to calculate profit margins on dimensions such as departments or customer groups. Additionally, you can filter general ledger entries and budget entries, for example, by net change or debit amount.

Note

Mathematically, think of a financial report as defined by two things:

  • A vector of row definitions that define what needs to be calculated.
  • A vector of column definitions that defines the data for the calculation.

The financial report is then the outer product of these two vectors, where each cell value is calculated according to the formula in the row applied to the data definition from the column.

Shows how a financial report is constructed from a row definition and a column definition.

The Financial Reports page shows how all financial reports follow a pattern that consists of the following attributes:

  • Name (code)
  • Description
  • Row definition
  • Column definition

Shows how all financial reports are constructed from a row definition and a column definition.

Note

The sample finance reports in Business Central aren't ready to use out of the box. Depending of the way you set up your G/L accounts, dimensions, G/L account categories, and budgets, you need to adjust the sample row and column definitions and the finance reports they're used in to match your setup.

You can also use formulas to compare two or more financial reports and column definitions. Comparisons let can do the following things:

  • Create customized financial reports.
  • Create as many financial reports as you need, each with a unique name.
  • Set up various report layouts and print the reports with the current figures.

To learn more about comparisons, go to When to use a comparison period formula and comparison date formula in a column definition.

Learning path: Create financial reports in Microsoft Dynamics 365 Business Central

Want to learn how to create budgets, and then use financial reports, dimensions, and row and column definitions to generate the financial reports that businesses typically need?

Start on the following learning path Create financial reports in Microsoft Dynamics 365 Business Central.

Create a new financial report

You use financial reports to analyze general ledger accounts or to compare general ledger entries with budget entries. For example, you can view the general ledger entries as percentages of the budget entries.

The financial reports in the standard version of Business Central might not suit your business needs. To quickly create your own financial reports, start by copying an existing one, as described in step 3 below.

  1. Choose the Lightbulb that opens the Tell Me feature 1. icon, enter Financial Reports, then choose the related link.
  2. On the Financial Reports page, choose the New action to create a new financial report name. Alternatively, to reuse settings from an existing financial report, choose the Copy Financial Report action.
  3. Fill in the report short name (you can't change the name later) and description.
  4. Choose a row definition and a column definition.
  5. Optionally, choose analysis views for the row and column definitions.
  6. Choose the Edit Financial Report action to access more properties on the financial report.
  7. On the Options FastTab, you can edit the report description, change the row and column definitions, and define how to show dates. Dates can be a Day/Week/Month/Quarter/Year hierarchy, or use accounting periods. To learn more, go to Comparing accounting periods using period formulas.
  8. On the Dimensions FastTab, you can define dimension filters for the report.
  9. You can preview the report in the area below the Dimensions FastTab.

Tip

After you create a financial report, you can use the Financial Report page to preview and validate it. To open the page, choose the View Financial Report action.

Note

When you open a financial report in View or Edit mode, the Filter pane is available. Don't use the Filter pane to set filters for the data in your report. Such filters can cause errors or might not actually filter the data. Instead, use the fields on the Options and Dimensions FastTabs to set up filters for the report.

Create or edit a row definition

Row definitions in financial reports provide a place for calculations that can't be made directly in the chart of accounts. For example, you can create subtotals for groups of accounts and then include that total in other totals. You can also calculate intermediate steps that aren't shown in the final report.

Row definitions also provide settings for formatting your report. Most of the options provide visual definition to reports when people view or preview them on their monitors, or print them.

Note

Some of the formatting options don't carry over when you export them to Excel. If you often export reports to Excel, you might skip the formatting here and format the report in Excel instead.

To learn more, go to Row definitions in financial reporting.

Create or edit a column definition

Use column definitions to specify the columns to include in the report. For example, you can design a report layout to compare net change and balance for the same period this year and last year. You can have up to 15 columns in a column definition. For example, multiple columns are useful for displaying budgets for 12 months with a column that shows the total.

To learn more, go to Column definitions in financial reporting.

Using dimensions in financial reports

In financial analysis, a dimension is data you add to an entry as a kind of marker. This data is used to group entries with similar characteristics, such as customers, regions, products, and salesperson, and easily retrieve these groups for analysis. You can use dimensions on entries in journals, documents, and budgets.

Each dimension describes the focus of analysis. So, a two-dimensional analysis, for example, would be sales per area. By using more than two dimensions when you create an entry, you can carry out a more complex analysis. An example of a complex analysis is exploring sales per sales campaign per customer group per area. That gives you greater insight into your business, such as how well your business is operating, where it is or isn't thriving, and where you should allocate more resources. That insight helps you make more informed business decisions. To learn more, go to Work with Dimensions.

Set up financial reports with overviews

You can use a financial report to create a statement that compares general ledger figures with budget figures.

  1. Choose the Lightbulb that opens the Tell Me feature 3. icon, enter Financial Reports, then choose the related link.

  2. On the Financial Reports page, select a financial report.

  3. Choose the Edit Row Definition action

  4. On the Row Definition page, in the Name field, select the default financial report name.

  5. Choose the Insert G/L Accounts action.

  6. Select the accounts you want to include in your statement, then choose OK.

    The accounts are inserted in your financial report. If you want, you can also change the column definition.

  7. Choose the Edit Financial Report action.

  8. On the Financial Report page, on the Dimensions FastTab, set the budget filter to the filter name you want to use.

  9. Choose OK.

Now you can copy and paste your budget statement into a spreadsheet.

Integrate financial reports with Excel

You can integrate a financial report with an Excel workbook template, adjust the layout to suit your needs, and then update the Excel template with data from Business Central. For example, this integration makes it easier to generate your monthly and yearly financial statements in a format that works for you.

Set up Excel integration for a financial report (create an Excel template)

To set up Excel integration for a financial report, follow these steps to create an Excel template.

  1. Choose the Lightbulb that opens the Tell Me feature 4. icon, enter Financial Reports, and then choose the related link.
  2. On the Financial Reports page, select the financial report to enable with Excel, and then choose the Export to Excel action.
  3. Choose the Create New Document action. This action downloads a template Excel workbook with a single worksheet named after the report name.
  4. Copy the worksheet, and rename it to Data.
  5. Rename the report worksheet to your liking.
  6. In the report worksheet, mark all cells that show data from the financial report (including column and row headers). On the Home ribbon, find the Number menu and choose General as the format.
  7. Choose the left most cell of the area with data from the financial report, and set a reference to the equivalent cell in the Data worksheet. Drag the formula to the right to extend it to all cells in the first row, and then drag the row down to cover all rows in the financial report.
  8. Hide the Data worksheet.
  9. Format the report worksheet to suit your needs.
  10. Save the workbook in OneDrive, or a similar place where the file is backed up and versioned.
  11. Close the workbook.

Note

If you make changes to the row or column definitions of the report you created a template for, you must update the template too. Each row and column in the Excel template must be in the same position as the generated report.

Example: Use and update an Excel template to create a monthly report

You can create an Excel template that contains the reports that you include in your reporting package. Each report in the workbook must have a data tab and a tab that has the formatted report. When you update your monthly package, use the Update Copy of Existing Document for each report. Be sure to save after each update.

Follow these steps to produce your monthly financial report.

  1. Choose the financial report you used to create the Excel template.
  2. Change the date filter to the next month.
  3. Choose the Export to Excel and Update Copy of Existing Document actions.
  4. Select the template you saved. Make sure that the Excel file is closed. Otherwise, an error message will state that the file is in use.
  5. The next screen shows the tabs that are available in the Excel workbook. Choose the Data tab, and then choose OK. If you choose the tab labeled IS, your formatting will be overwritten and you'll need to start over.
  6. The Excel workbook is created. Be sure to enable editing to show your changes.
  7. Save the workbook with a different name than the original template.

Run a financial report with an Excel template

To run a financial report with an Excel template, follow these steps:

  1. Choose the Lightbulb that opens the Tell Me feature 4. icon, enter Financial Reports, and then choose the related link.
  2. On the Financial Reports page, select the financial report to enable with Excel, and then choose the Export to Excel action.
  3. Choose the Update Copy of Existing Document action.
  4. Upload your Excel template (close the Excel workbook before uploading it).
  5. On the Name/Value Lookup page, choose the Data worksheet.
  6. Business Central runs the financial report and merges the resulting data with your Excel template.

You can print financial reports using your device's printing services. Business Central also offers options to save reports as Excel workbooks, Word documents, PDF, and XML files.

  1. Choose the Lightbulb that opens the Tell Me feature 4. icon, enter Financial Reports, then choose the related link.
  2. On the Financial Reports page, select the report to print, then choose the Print action.
  3. Fill in the fields as necessary. Hover over a field to read a short description.
  4. In the Printer field, select the device to which the report is sent.
    1. The (Handled by the browser) option indicates there's no designated printer for the report. In this case, the browser handles the printout and display the standard printing steps, where you can choose a local printer connected to your device. (Handled by the browser) isn't available in the Business Central mobile app or app for Teams.
  5. Choose the Print action.

Schedule a financial report or save as a PDF, Word, or Excel document

You can save a financial report in file formats such as PDF, XML, Word, or Excel. Business Central can also generate recurring financial reports.

  1. Choose the Lightbulb that opens the Tell Me feature 4. icon, enter Financial Reports, then choose the related link.
  2. On the Financial Reports page, choose the Print action.
  3. Set the report accordingly, then choose the Send to action.
  4. Select the file format or the Schedule action, and choose OK.
  5. To generate a scheduled or recurring financial report, fill in the fields. Hover over a field to read a short description..

    For recurring financial reports, set the Earliest Start Date/Time and Expiration Date/Time fields with the first and last date, respectively, to generate the financial report. Also, select on which days the report is generated by setting the Next Run Date Formula field following the format explained in the Use Date Formulas section.

Best practices for working with financial report definitions

Financial report definitions aren't versioned. When you change a report definition, the old version is replaced when your change saves to the database. The following list contains some best practices for working with financial report definitions:

  • If you add report definitions, choose a good code and fill in the description field with a meaningful text while you still know what you use the report for. This information helps your coworkers (and your future self) to work with the report and perhaps changing the report definition.
  • Before you change a report definition, consider taking a copy of it as a backup, just in case your change doesn't work as expected. You can either just copy the definition (give it a good name), or export it. To learn more, go to Import or export financial report definitions.
  • If you need a fresh copy of a definition that Business Central provides, an easy way to get one is to create a new company that only contains setup data. Then, export the definition and import it in the company where the definition needs a refresh.

Import or export financial report definitions

You can import and export financial report definitions as RapidStart configuration packages. For example, configuration packages are useful for sharing information with other companies. The package is created in a .rapidstart file, which compresses the contents.

Note

When you import financial report definitions, existing records with the same names as those you are importing are replaced with the new definitions. The configuration package for a report definition won't overwrite any existing row or column definitions that are used in the report definition.

To import or export financial report definitions, follow these steps:

  1. Choose the Lightbulb that opens the Tell Me feature 4. icon, enter Financial Reports, then choose the related link.
  2. Choose the financial report, then choose the Import Financial Report or Export Financial Report action, depending on what you want to do.

To learn more about how to import or export financial report row or column definitions, go to the following articles:

See also

Row definitions in financial reporting
Column definitions in financial reporting
Run and Print Reports
Financial Business Intelligence
Finance
Setting Up Finance
The General Ledger and the Chart of Accounts
Work with Business Central

Find free e-learning modules for Business Central here