Configuring a 3-Year Quarterly Trend Income Statement in D365 F&O
Introduction
Today, we're diving into the financial reports feature in Dynamics 365 Finance and Operations (D365 F&O). Our focus will be on creating an income statement that showcases a quarterly trend over a three-year period. This setup will help you visualize your organization’s profitability across the last three years, broken down by quarters.
Recap of Previous Session
Before we proceed, let’s quickly recap what we covered in our last session. We explored the report definition for a 12-month periodic trend, which was based on monthly data from January to December. Remember, we kept the row definition the same as our first video, which included various heads and ratios for the income statement. This time, we aim to see quarterly trends while keeping the row definition intact.
Understanding the Quarterly Trend Setup
In this tutorial, we will create a new column definition specifically for the quarterly income statement. The goal is to present the financial data in 12-month buckets, but this time, the amounts will be displayed quarterly. Let's get started by establishing our new column definition.
Creating the Column Definition
To begin with, we need to define our column type. This will be based on the row description, which retrieves values from our row definition. For our column type, we will use "FD." Once we select FD, the remaining values will auto-populate, but we need to make a few adjustments.
Setting Up Quarterly Columns
We will label our columns as Q1, Q2, Q3, and Q4. Each header will represent the respective quarters. For the first year, we will set the base to reflect the first four quarters. To make things easier, let’s copy the existing values and create four new columns. This will give us a total of 12 columns, starting from column B to column M, with the first column displaying the description from our row definition.
Fiscal Year Configuration
In the base section, we will set the fiscal year. For the first year, it will remain as "base." Next, we will insert the fiscal period name and fiscal year above the columns. This header will help in identifying the fiscal data more clearly.
Adjusting for Previous Years
Now, let’s move on to the second year. We need to adjust the base year to represent one year prior. This means we will set the base to "base -1" for the first four quarters of the second year. We will repeat this for the third year, setting it to "base -2." This adjustment ensures that our report reflects the correct periods for each fiscal year.
Defining Period Ranges
In our previous monthly trend setup, we inputted single values for each month. However, for the quarterly trend, we need to specify ranges. We will define the quarters as follows:
- Q1: Months 1-3
- Q2: Months 4-6
- Q3: Months 7-9
- Q4: Months 10-12
By using these ranges, we ensure that the correct months are picked up for each base year. It’s crucial to select the right ranges to maintain the accuracy of our report.
Finalizing the Column Definition
After setting up the ranges, it’s time to apply formatting. I prefer the Segoe UI font for the headers, making them bold for better visibility. Once everything is set, save the column definition with an appropriate name, such as "PKR I 3 Years Quarterly Trend."
Creating the Report Definition
Now that our column definition is ready, we need to create a new report definition, similar to what we did in the previous session. Let’s proceed with the following steps:
Steps to Create a New Report Definition
- Navigate to the report definition section.
- Select the company and set the detail level to "Finance sh account" and "transactional level." This allows for drilling down to the transactional level.
- Choose the previously created row definition for the income statement summary.
- Select the new three-year quarterly trend column definition.
- Name the report "Income Statement 3 Years Quarterly Trend."
- In the header and footer, include the company description and reporting date, ensuring the date is in a short format.
- Disable the currency symbol in the settings since we may need to display different currencies.
- Untick the option to display blank for zero amounts.
- Save the report definition as "PKR I 3 Years Quarterly Trend."
Generating the Report
With the report definition saved, we can now generate the report to see how it looks. Upon generation, we can review the data starting from the base year, which in our case is 2016, moving back to 2015 and 2014. You might notice that there’s no data for 2014, but the quarterly trends for 2016 are clearly displayed.
Homework Assignment
As a little challenge, I encourage you to add a fifth column to your report for the year-to-date figures for 2016, 2015, and 2014. Share in the comments how you achieved this by selecting the appropriate options in your column definition.
Looking Ahead
In our next session, we will explore consolidations across two companies, rather than just one. We’ll learn how to manage financial reports for multiple entities, focusing on USRT and USMF. Thank you for joining today’s session, and I hope you found this information useful. Enjoy your day!