How to create a custom income statement report in Dynamics 365 Business Central

Creating an income statement report is essential for businesses to show their profitability in a given period. This walkthrough will demonstrate how to create an Income Statement in Dynamics 365 Business Central, a powerful ERP created by Microsoft designed for small to mid-size companies.

Account Schedules in Business Central can be used to generate any type of financial report, including income statements, balance sheet, and cash flow. The Account Schedule functionality in Business Central does not require any development and can be configured by someone in accounting. Business Central includes some sample account schedules, including an income statement, but most organizations choose to create their own income statement. This post includes step-by-step instructions for creating an income statement in Business Central.

An Account Schedule is made up of a definition of the rows and columns. The rows are defined in the Account Schedule definition and can be heading rows, G/L accounts or group of accounts, and totals.

The columns are defined by Column Layouts which can be based on period, dimensions, budget, or subsidiary and have the ability to have formulas to create, for example, a variance column.

You can combine the row definition and the column definition to create unique reports. For example, if you want two versions of your income statement – one by department and one for the whole company with a comparison to last year, you would create a one-row definition for your income statement and two column layouts. When you go to run the income statement report you can choose which column layout to use.

In this example, we will be creating a new Account Schedule.

1. Search for Account Schedules
2. Select Account Schedules under Pages and Tasks
3. Click New to create a brand new Account Schedule. Alternatively, click Process >> Copy to start with an existing account schedule and modify it.
4. Enter Name and Description. Leave Default Column Layout and Analysis View Name blank for now.

Now that we have created the Account Schedule, we will edit the definition. Make sure Edit List is not selected and then double click on the Account Schedule to edit it.

To create the row definitions for your income statement, you can either manually type in the row definitions or pull in a list of accounts directly from the Chart of Accounts. If your income statement rows, match your Chart of Account closely, inserting from the Chart of Accounts save you time:

1. Go to Insert >> Insert Chart of Accounts
2. Scroll down to the Income Statement section of your Chart of Accounts
3. Click on the 3 dots next to the first account and select Select more
4. Select all of the accounts you wish to add
5. Click OK

This will insert the accounts into the Account Schedule definition.

The following fields can be edited as needed:

• Row No. – This is a unique identifier for the row which can be used elsewhere in the account schedule in a formula. If you insert from the Chart of Accounts, it will default to the G/L Account number. If you manually type in the definition, it is best practice to number the rows incrementally with a letter and number for example P0001 and then P0002. These row numbers are not visible when the income statement is printed.
• Description – This is the description that will appear on your income statement. If you insert from the Chart of Accounts, this will default to the G/L Account Name
• Totaling Type –  This field determines whether the row pulls data directly from a G/L Account or range of G/L Accounts, or if it is totaling or formula row. There are more options available such as creating an underline or double underline row.
• Totaling – This field works in conjunction with the Totaling Type field to define the data in the row:
  • Totaling Type of Posting – Defines which G/L Account(s) are included. One singular account can be listed, or a range of accounts can be included using an ellipse (40000..40001) or the pipe symbol can be used to string together individual accounts or ranges of accounts (40000..40001|40500..40990)
  • Totaling Type of Formula – References the Row No. and can use mathematical symbols, for example, P0001 + P0002.
  • Totaling Type of Totaling Account – References a totaling account from the Chart of Accounts, for example, if in your income statement in the Chart of Accounts, G/L Account 40990 was defined as Total Income you could enter 40990 in the Totaling field to pull in Total Income.
• Row Type – For an income statement, this will always be Net Change. The other options are Balance at Date and Beginning Balance which can be used to create a Balance Sheet.
• Show Opposite Sign – In the Chart of Accounts in Business Central, revenue accounts typically show a negative amount. In order to show revenue accounts as positive, you should check the Show Opposite Sign field on income accounts.
• Show – This column determines if you always want to show the row or not
  • Yes – The row will always display
  • No – The row will never display; this is often used if you create the row to be used as part of a formula but do not want it to show on the printed income statement
  • If Any Column Not Zero – If you want the row to only show if there is activity

There are also fields that can be used for formatting the account schedule:

• Bold
• Italic
• Underline
• Double Underline
• New Page

Finally, you can change the indentation on the schedule by going to Process >> Indent or Process >>Outdent.

Below is an example of account schedule rows created using the fields listed above.

Now that we have defined the rows of the income statement, the next step is to define the columns. We will be walking through creating a layout that compares the current period versus the previous period.

1. From the Account Schedule, go to Process >> Edit Column Layout Setup
2. Click the ellipses (3 dots) next to Name and select New
3. Enter the Name and Description and click OK

The following fields can be used to define the columns of our income statement:

• Column No. – This is not printed on the income statement report and can be used in a formula
• Column Header – This will be the name of the column that is displayed on your income statement
• Column Type – Used to define the type of column
  • Net Change – This is the most common type for an income statement report
  • Formula – Used when making comparisons or needing to do other mathematical calculations
  • Subsidiary – Used when comparing multiple subsidiaries
• Ledger Entry Type – This will always be Entries unless you want to compare to budget, in which case select Budget Entries
• Amount Type – This will typically always be Net Amount unless you want to include only Credit Amounts or only Debit Amounts
• Budget Name – Used to specify the budget to compare to if Budget Entries is selected as the Ledger Entry Type
• Formula – References the Column No. and can use mathematical symbols, for example, C001 – C002.
• Show Opposite Sign – This can be used to show the opposite sign of the column. Note: Do not check Show Opposite Sign on both the Account Schedule and Column Layout
• Comparison Date Formula – Used to indicate the comparison date period, for example, prior month, prior year, etc.
• Show – Defaults to Always but can be changed to Never if the column is used as a formula and you do not want it displayed on the income statement. Other options are to display when positive or when negative.
• Rounding Factor – Defaults to None but can be changed to round to the nearest whole dollar, nearest thousand, or nearest million

In this example, we are going to create a comparison between the current and previous periods with the following three columns:

• Current Period
• Previous Period
• Variance

In Business Central, you have several options on how to view the income statement report. You can view it on screen and have drill down ability, print it to PDF, or export it to Excel.

To view the income statement on screen, from the Account Schedule click Related >> Overview. Select the Account Schedule, Column Layout, and Date Filter desired and the rows and columns will automatically change to match your Account Schedule and Column Layout definition.

If you want to find more information on the transactions that make up that amount, you can click on the number to drill down to more details.

If you wish to send this information to Excel, from the Acc. Schedule Overview, click Actions >> Excel >> Export to Excel >> Create New Document.

Alternatively, if you wish to print the report, from the Account Schedule, click Reports >> Print. Select the appropriate Acc. Schedule Name and Column Layout Name and a Starting Date and Ending Date. From here you can print, preview, or send to PDF, Word, or Excel. You can also schedule the report to be run on a recurring basis.

Once the report runs it looks like the report below.

The Account Schedule functionality in Business Central has a lot of functionality and flexibility in terms of defining rows and columns to build the exact income statements needed by your organization. One limitation, however, is the formatting of the report. Account Schedule functionality has the ability to add bold, italic, underlines, and double underlines but natively it does not have the ability to change fonts and perform more advanced formatting. There are several approaches to address this issue:

1. Export Account Schedules to Excel and perform necessary formatting in Excel
2. Have a Microsoft Certified Partner customize the native reports to include the necessary formatting
3. Use a third-party reporting tool such as Jet Reports by insightsoftware to build the financial statements

Jet Reports is a third-party reporting tool that can be used to build reports, including financial statements, directly in Excel. If your financial statements are complicated or require specific formatting, Jet Reports may be a good alternative to using Account Schedules.

Below is an example of an income statement report created in Jet. There are several parts of the report shown below that cannot be accomplished by Account Schedules in Business Central without a customization:

• The ability to have headers such as Trend Results that spans multiple columns
• The ability to have column headers that have specific month names. Typically, the column header name would be Current Period or Previous Period and the date range for the entire report would be at the top of the report.
• The use of colors and cell borders

Rand Group is a Microsoft Certified Partner as well as an insightsoftware partner. Our team of CPAs, data scientists, and software developers can help with any of your reporting needs, whether it is training on how to build Account Schedules in Business Central, customization of Business Central reports by a developer, or implementation and training on a third party reporting tool such as Jet Reports. Additionally, we can help you evaluate what is the best approach to your reporting needs. Contact us today to set up a consultation.