In our previous blog post, we briefly touched upon reporting with Financial Reports. Financial Reports are traditional financial statements like P&L, Balance sheet, etc. It’s part of Dynamics 365 Business Central and if you have a license, you can run them and create them, export them to Excel and share that with your team. Financial Reports can create financial statements when you are combining accounts, departments and have different groupings on lines like gross margin, balance sheet, cash flow statements. It is really flexible, depending on how you want to present it.
In this blog post, I’ll just provide a walk-through of the process and show you how to build a P&L using Financial Reports. This is a combination of a set of rows and set of columns. First, search for Financial Reports using the search icon in Dynamics 365 Business Central. There are a few Financial Reports that come built-in in Business Central.
To create a new one, click on + New and give it a Name and Description. For Default Column Layout, you can select the default column you want. To build the structure of this new Financial Report, click on Edit Row Definition.
Row No.: This is how you reference tables and row no. You can enter any number, but I usually start with 10 and so on. Create the 3 rows 10, 20 and 30.
Description: This is what shows up on your financial statements. For the 3 rows you created add Revenue, Cost of Sales and Gross Margin.
Totaling Type: You can select Posting Accounts (it is a sum of range of accounts) or Formula. Formula is basically the rows from the reports. Select Posting Account for Revenue and Cost of Sales, and Formula for Gross Margin.
Totaling: This is where you want to pick what accounts you want to choose. For the first row, click on the three dots and check the No. in your G/L Account list for Revenue. You want to do a range. For example- 60001..69999. This way if you add any more accounts to the revenue range it will pick it up and you don’t have to edit this. Enter the relevant information for Cost of Sales. And for Gross Margin, enter -10-20. It will switch the sign from positive to negative and subtract the cost of sales.
Department Code Totaling: You can put the range of your Dimensions code here.
Row Type: If you are doing a P&L, you will always want to select Net Change. For Balance sheet, you select Beginning Balance.
Amount Types: Most of the times it is the Net Amount.
Show Opposite Sign: This is useful especially for Row 1 where we don’t want revenue to show up as negative for credit and so check the box for show opposite sign.
Show: You can kind of suppress it if its Zero or if you are using a formula. For example, you want to create a complex formula and you create a row just for internal reference, you can hide it by selecting No. If you got a really detailed P&L showing every account or a lot of accounts, you can select If Any Column Not Zero and it will hide it if it’s just zero. So, its sort of a personal preference. You can just say Yes unless it is a really detailed report.
The next three are for formatting – Bold, Italic and Underline.
To run it, click on View Financial Report.
Over here, select the parameters that you want. You have your Financial Report which is your Row setup. You have your Columns and you can change that to be what you want. You can edit a column layout based on your reporting needs. Select View by and change the Date accordingly.
So, this is how you create a very simple report. You can click on the data on the report to drill down and see all the accounts that make this report. To view it in excel click on Export to Excel -> Create New Document.
Financial Report is a data source and you can run it in Microsoft Power BI too. If you would like to know more about other ways of reporting in Dynamics 365 Business Central, read the following blog posts:
