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.
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.
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.
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.
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.
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.
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.
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:
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.
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."
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:
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.
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.
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!