The Scenario

We have a client that has over 30 profit centers within a single entity. They create P&L's for each profit center on a tab within an Excel Workbook. On each profit center Excel tab, there is a detailed P&L that is filtered based on the Department Dimension Code and the Accounting Period for that Profit Center. In addition, they add special formatting, statistical measures, KPI's and budgets to their profit center P&L. On the final tab, they have a consolidating P&L that totals all of the departments into a single P&L. To add additional complexity, they have two Cost center departments that are allocated on the consolidated P&L. It is very complicated and beyond what the BC Financial Reports can accommodate!

‍

The Solution

Excel Layouts: https://learn.microsoft.com/en-us/dynamics365/business-central/ui-excel-report-layouts?tabs=any-report

To solve their reporting requirements, we created a custom report based upon the Analysis View Entries table. We decided to use the Analysis View Entries table to aggregate the financial data by month for each Account and Dimension combination. This decreased the amount of data in our Data tab on the output spreadsheet. The report simply exported BC data to a spreadsheet with a tab for the Data and a tab with the Budget data.

Once we had our spreadsheet with the data, we created a financial report using the excel function "SUMIFS".

The data comes from a tab called "Data". That is the output from the custom report that we created

Once the each tab of the P&L Spreadsheet was completed (Departmental and Consolidated) we uploaded the Excel Layout back into Business Central. Now, whenever they want to run the financial for a period, they execute the Custom report with the proper date filter. When the report completes, it presents the user with an excel file download. Once the user downloads the excel file, they have a completed financial report updated with the specified data! It is a very simple way to produce a very complicated financial report!