Walkthrough: Create custom financial reports

The examples in this article describe how to create the following reports:

  • Example 1 creates a Trial Balance report
  • Example 2 creates a Balance Sheet report
  • Example 3 creates an Income Statement report

Note

The examples are based on the demonstration data that Business Central provides for its CRONUS demonstration company.

Tip

Formulas are expressed as a row number reference, such as "R" or "CA" (current assets), which adds all accounts with row number "R" or "CA."

Example 1: Trial Balance

In this example, we have a new financial report called Trial Balance and a new row definition called TB.

[Shows the trial balance selection.]

  1. Open the new Trial Balance report.

  2. Open the TB row definition, and choose Insert.

  3. Choose the Insert G/L Accounts action to open the G/L Accounts page.

  4. Filter the Account Type column to Posting.

    Tip

    To quickly apply the filter, choose any row that has Posting in the Account Type column, and then right-click the Account Type column header and choose Filter to this value.

  5. On the first line, choose Show more options [Show more options icon.], and then choose Select more.

  6. Select all lines by choosing the radio button next to the first column header.

    ![Shows how to select all lines.](https://learn.microsoft.com/dynamics365/business-central/media/account type select all.png)

  7. Choose OK to assign all general ledger accounts with a totaling type of Posting to your row definition.

    Tip

    To sum the results of all lines, add a formula on the last line of the row definition. Because this is a trial balance report, the expected result is zero.

Your trial balance report is complete. To view the report, choose the report on the Financial Reporting page, and then choose View Financial Report. Choose the column definition you prefer for the report. Often used column definitions for a trial balance report are Balance Only, M-Balance, and Default.

Tip

If you add a new general ledger account to the chart of accounts, you must update your row definition to include it. Otherwise, your trial balance report won't balance to zero.

Example 2: Balance Sheet

In this example, we have a new financial report called Balance Sheet and a new row definition called BS.

![Shows a balance sheet selection in financial reporting.](https://learn.microsoft.com/dynamics365/business-central/media/balance sheet selection.png)

  1. Open the Balance Sheet report.
  2. Open the BS row definition.
  3. Map the accounts as shown in the following table. This example maps the balance sheet to the chart of accounts in the CRONUS demonstration company.
Row No.DescriptionTotaling TypeTotalingRow TypeBold
Current AssetsFormulaBalance at DateYes
CACashPosting Accounts18000..18999Balance at Date
CAAccounts ReceivablePosting Accounts15000..15999Balance at Date
CAOther ReceivablesPosting Accounts13000..13999Balance at Date
CAInventoryPosting Accounts14000..14999Balance at Date
CAPrepaid ExpensesPosting Accounts16000..16999Balance at Date
CAOther Current AssetsPosting Accounts100000..11999Balance at Date
F1Total Current AssetsFormulaBalance at Date
FormulaBalance at Date
Long Term AssetsBalance at DateYes
LTAFixed AssetsPosting Accounts10000..12899Balance at Date
LTAAccumulated DepreciationPosting Accounts12900..12999Balance at Date
LTAOther Long Term AssetsPosting Accounts17000..1799919000..19999Balance at Date
F2Total Long Term AssetsFormulaLTABalance at DateYes
FormulaBalance at Date
F3Total AssetsF1+F2Balance at Date

The following table describes the fields on the Row Definition page.

FieldDescription
Row No.Alphanumeric codes are used for each section. The codes let you group by row number when you use formulas. Groupings help make formulas shorter and more simple. Each formula line is labeled with an alphanumeric code for easy identification.
DescriptionUsed to hold the words to label each row of the report.
Totaling typeFormula is used in three ways:

* To add the results of rows together.

* When combined with a section header or total.

* When inserting a blank row into the report.

Posting accounts are used to indicate that the row pulls the result of posted transactions based on the accounts listed in the Totaling column.
TotalingContains either a range of accounts shown as 10000..11999, or multiple nonconsecutive ranges of accounts shown as 17000..17999|19000..19999.

Formulas are expressed as a row number reference, such as CA, which adds all accounts with Row No. of CA.

Following the general accounting convention of displaying financial report lines in the order of liquidity, general ledger accounts initially appear out of order. Notice that accounts show in the ranges created for each line. Totaling ranges let the report update dynamically when you add a new general ledger account to your chart of accounts.
Row typeBalance at Date is used for Balance Sheet reports.
BoldSection headers and totals are set to Bold so they stand out.

Tip

This example shows a summarized balance sheet, where each line is a group of related balance sheet accounts. Your report’s level of summarization is up to you. Good financial report design tries to provide enough detail to tell the story of financial performance without detailing every account. Avoid creating a balance sheet that is a categorized trial balance. It can be hard for people to digest such a large amount of information.

Continue to add balance sheet sections as shown in the following table. You can incorporate your own account groupings to create or replicate your company’s method of displaying accounts.

The last line of this report is a check figure that the report complies with the accounting equation where Assets=Liabilities+Equity. The result of this check figure should be zero. If it isn't zero, it's a good indication that there's a problem:

  • Some accounts aren't included in the report.
  • Accounts might be included more than once.
  • A formula in the report isn't calculating correctly.
Row No.DescriptionTotaling TypeTotalingRow TypeBold
Current LiabilitiesFormulaBalance at DateYes
CLAccounts PayablePosting Accounts22100..22399Balance at Date
CLAccrued PayrollPosting Accounts23500..2539926100..26399Balance at Date
CLAccrued TaxPosting Accounts23100..23499Balance at Date
CLAccrued OtherPosting Accounts26400..29999Balance at Date
CLOther Current LiabilitiesPosting Accounts22400..2309925400..26099Balance at Date
F4Total Current LiabilitiesFormulaCLBalance at DateYes
FormulaBalance at Date
Long Term LiabilitiesPosting AccountsBalance at DateYes
LTLNotes PayablePosting Accounts20000..21299Balance at Date
LTLOther Long Term LiabilitiesPosting Accounts21300..22099Balance at Date
F5Total Long Term LiabilitiesFormulaLTLBalance at DateYes
FormulaBalance at Date
F6Total LiabilitiesFormulaF4+F5Balance at DateYes
FormulaBalance at date
EquityFormulaBalance at Date
ECommon StockPosting Accounts30000..30299Balance at Date
ERetained EarningsPosting Accounts30300..39999Balance at Date
ECurrent Year EarningsPosting Accounts40000..99999Balance at Date
F7Total EquityFormulaEBalance at Date
FormulaBalance at Date
F8Total Liabilities and EquityFormulaF6+F7Balance at DateYes
FormulaBalance at Date
F9Check FigureFormulaF3+F8Balance at Date

Your balance sheet financial report is complete. To view the report, choose the report on the Financial Reporting page and select View Financial Report. Select any column definition to use with this report. The column definitions that are typically used for a balance sheet are Balance Only, M-Balance, and Default. You can also create a new column definition if you want.

The following image shows the structure of the report in this example.

![Shows the structure of the balance sheet report](https://learn.microsoft.com/dynamics365/business-central/media/balance sheet report example.png)

Example 3: Income Statement

In this example, we have a new financial report called Income Statement and a new row definition called IS.

![Shows the income statement selection.](https://learn.microsoft.com/dynamics365/business-central/media/income statement selection.png)

  1. Open the Income Statement report.
  2. Open the IS row definition.
  3. Map the accounts as shown in the following table. This example maps the balance sheet to the chart of accounts in the CRONUS demonstration company.
Row No.DescriptionTotaling TypeTotalingRow TypeBold
RevenueFormulaNet ChangeYes
RProduct RevenuePosting Accounts40000..40209Net Change
RJob RevenuePosting Accounts40410..40429Net Change
RServices RevenuePosting Accounts40210..4030940430..40909Net Change
ROther RevenuePosting Accounts40310..4040940920..40939Net Change
RDiscounts and ReturnsPosting Accounts40910..4091940940..49999Net Change
F1Total RevenueFormulaRNet ChangeYes
FormulaNet Change
Cost of GoodsFormulaNet Change
CMaterialsPosting Accounts50000..50209Net Change
CLaborPosting Accounts50210..59999Net Change
CManufacturing OverheadPosting Accounts60000..69999Net Change
F2Total Cost of GoodsFormulaCNet ChangeYes
FormulaNet Change
F3Gross Margin USDFormulaF1+F2Net ChangeYes
F4Gross Margin %FormulaF3/F1*100Net ChangeYes

The following table describes the fields on the Row Definition page.

FieldDescription
Row No.Alphanumeric codes are used for each section. The codes let you group by row number when you use formulas. Groupings help make formulas shorter and more simple. Each formula line is labeled with an alphanumeric code for easy identification.
DescriptionUsed to hold the words to label each row of the report.
Totaling typeFormula is used in three ways:

* To add the results of rows together.

* When combined with a section header or total.

* When inserting a blank row into the report.

Posting accounts are used to indicate that the row pulls the result of posted transactions based on the accounts listed in the Totaling column.
TotalingContains either a range of accounts shown as 40000..40209, or multiple nonconsecutive ranges of accounts shown as 40210..40309|40430..40909.

Formulas are expressed as a row number reference, such as CA, which adds all accounts with Row No. of CA.

Following the general accounting convention of displaying financial report lines in the order of liquidity, general ledger accounts initially appear out of order. Notice that accounts show in the ranges created for each line. Totaling ranges let the report update dynamically when you add a new general ledger account to your chart of accounts.
Row typeBalance at Date is used for Balance Sheet reports.
BoldSection headers and totals are set to Bold so they stand out.

Tip

This example shows a summarized income statement, where each line is a group of related incomes statement accounts. Your report’s level of summarization is up to you. Good financial report design attempts to provide enough detail to tell the story of financial performance without detailing every account. Avoid creating an income statement that's a categorized trial balance. It can be hard for people to digest such a large amount of information.

Continue to add income statement sections as shown in the following table. You can incorporate your own account groupings to create or replicate your company’s method of displaying accounts.

The last line of this report is a check figure that the report complies with the accounting equation where Assets = Liabilities + Equity. The result of this check figure should always be zero. If it isn't zero, it's a good indication that there's a problem:

  • Some accounts aren't included in the report.
  • Accounts might be included more than once.
  • A formula in the report isn't calculating correctly.
Row No.DescriptionTotaling TypeTotalingRow TypeBold
Operating ExpenseFormulaNet ChangeYes
OESalaries and WagesPosting Accounts70000..72109Net Change
OEEmployee BenefitsPosting Accounts72110..73299Net Change
OEEmployee InsurancePosting Accounts73300..74109Net Change
OEEmployee TaxPosting Accounts74110..79999Net Change
OEDepreciationPosting Accounts80000..89999Net Change
OEOther ExpensePosting Accounts90000..99999Net Change
F5Total Operating ExpenseFormulaOENet ChangeYes
FormulaNet Change
F6Net (Income) / LossFormulaF1+F2+F5Net Change
FormulaNet Change
F7Total of Income StatementPosting Accounts40000..99999Net Change
F8Check FigureFormulaF6-F7Net Change

Your income statement report is complete. To view the report, choose the report on the Financial Reporting page, and select View Financial Report. Select any column definition you like to use with this report. The often used column definitions for an income statement are M-NetChang and PTD + YTD. You can also create a new column definition.

The following image shows the structure of the report in this example.

![Shows the structure of the income statement report.](https://learn.microsoft.com/dynamics365/business-central/media/income statement report.png)

Primary capabilities of financial reporting
Walkthrough: Make cash flow forecasts using financial reports
Prepare financial reporting with financial data and account categories
Financial analytics overview