Microsoft Dynamics Blog

Welcome to Armanino’s Microsoft Dynamics Blog, where you’ll find information on topics across Microsoft Dynamics 365 ERP and CRM, Dynamics GP, Power BI, PowerApps, Flow and more. Use these tips, tricks and insights to get the most out of your applications! Want these articles delivered straight to your inbox? Subscribe to our newsletter below.

Wednesday, August 21, 2019

Quarter-to-Date Financial Reporting in D365 F&O

Posted by Keith Higginson

Publicly traded companies (and some other companies) are required to report their financial operations quarterly. They often need to have a single column with quarter-to-date (QTD) results. This guide will walk you through managing your QTD financial reporting in D365.

In D365 F&O, and in previous versions of Dynamics AX and Dynamics GP, the solution for presenting quarter-to-date results isn’t obvious. But there is a solution, which applies to the financial reporting tool in Dynamics 365, as well as to the Management Reporter tool used in Dynamics AX and Dynamics GP.

Before outlining the preferred method, let’s start by acknowledging two less-satisfactory alternatives.

  1. It is possible to create a “rolling quarter” amount, which is the sum of the current period and the prior two periods, by entering BASE-2 in the Period row of a column layout. Most clients aren’t satisfied with this approach, however, because it will produce the correct result only when run for the last month of each quarter.
  2. Another common approach is to hard-code the Period row of each column with the number of each period (1 to 12), then use calculated columns to add the amounts for periods 1-3 as the first quarter, 4-6 as the second quarter, etc. The disadvantage to this approach is that usually all four quarters will print on the report when you typically want only one quarter at a time.

These two methods, while very straightforward, may not be flexible and precise enough for all applications.

A third method, however, can solve most any type of QTD financial reporting issue. This preferred method looks complex at first, as there are several advanced features at play in the column definition. However, once it’s been set up, it’s not difficult to modify or replicate for other reports.

The two keys to this approach are:

  1. Hard-coding each of the 12 fiscal periods to a separate column instead of using the default BASE value.
  2. Using conditional printing to print only the column that matches the base column for the report. The column selected for printing will have the specific formula needed to calculate a QTD number for that amount.

A sample column definition calculating the first quarter is shown below. Columns E, F and G would be repeated for the remaining three quarters.

QTD Financial Reporting example column calculation

Three key concepts for configuring this report for QTD financial reporting

  1. The Print Control line will be set to NP for columns B, C and D, and set to P=B for the next twelve columns.
    • Columns B, C and D are used to store only the value of calculations and therefore never prints on the actual report.
    • Each month of the year is either the first, second or third month of a quarter. Accordingly, column B calculates the QTD amount for the third month in a quarter, C calculates the QTD amount for the second month in a quarter and D calculates the QTD amount for the first month in a quarter. These stored amounts are then referenced by the appropriate column of the next 12 columns.
  2. The print control of P=B stands for Period = BASE. Since the BASE period is only a single period, only one of these columns will print. Consider the following examples:
    • If a report is run for January, the BASE period is 1 (assuming a Jan-Dec fiscal year). The Period value 1 is specified for column E. Therefore, only column E will print, and it will calculate the amount from column D which will return the amount for the base period (January) as the QTD amount.
    • If a report is run for February, the BASE period is 2. The Period 2 is specified for column F. Therefore, only column F will print, and it will calculate the amount from columns D+C which will return the amount for the base period (January) as the QTD amount.
    • This method looks complex because a total of 15 columns are used to print a single QTD column in a report. But it only takes a few minutes to create this column definition. Once created, it can be used for many reports.
    • Additional QTD columns can be created using this same method and changing the book code to budget for a budget-to-actual report, or changing the Fiscal Year or Period column
  3. This example applies to QTD amounts for the income statement, not the balance sheet.
    • A balance sheet, by definition, is always reporting YTD amounts. Therefore, balance sheet numbers are much more straightforward.

A company may need to compare their balance sheet this quarter to that last quarter, or against the same quarter last year. They can usually use BASE-1 in the Fiscal Year row for a comparison to the prior year or BASE-3 in the Period row for a comparison to the prior quarter.

Discover more tips, tricks and how-to tutorials like this how-to guide on QTD financial reporting throughout the Armanino Dynamics blog and learn about our experts.

« | »