Contact Us
+1 732 991 0534 | info@musewerx.com
Most of the times Companies end up their yearly financial reports into the new fiscal year, even if they follow a January to December financial calendar. Therefore, the financial closing may happen in February or March of the subsequent fiscal year. This timing difference affects the precision of Year to Date (YTD) account balances presented in financial statements like the Balance Sheet or Trial Balance.
The issue arises from incorrect YTD figures being carried forward from the previous economic year to the current one. Users unaware of this discrepancy might overlook previous year's balances, leading to an analysis based on incomplete data. In this blog, we will counter this problem and solution.
Illustration of YTD account balances issue
To illustrate YTD account balances issue, let us take an example of the sample Dynamics AX company, USMF. Open the most recent unclosed year, in this case 2017, and pull the trial balance for the month of December 2017 (General Ledger > Inquiries and Reports > Trial Balance).
See the screenshot of this trial balance. For example we will consider the account 140100 Raw Materials Inventory.
This is shown in Figure 1, the closing value of account 140100 is USD 5,302,452.95. This is enclosed in Red.
Next, run the trial balance for the first period of the next financial year, i.e. 2018. Notice that in Dynamics 365’s Trial Balance inquiry the closing balance for the year 2017 does not carry forward to the first period of the financial year 2018. Figure 2 shows the snapshot of the trial balance for the month of January 2018.
Notice that there are only 2 accounts showing up the January 2018 trial balance inquiry. This is because there are no transactions in this period other than for the above 2 accounts. Furthermore, none of the accounts have had their balances (for balance sheet accounts) carried forward to 2018 because the year 2017 was not financially closed.
Now let’s see how the financial reporter Trial Balance report handles this information. We will create a simple report that contains a row building block having main account numbers only. This is shown below in figure 3. This is a simple listing of all the GL accounts, going from top to bottom.
However, the main point is in the column building block. In order to show how the financial YTD account balances reporter behaves for the YTD numbers. First we will use a building block that uses the accounting currency only. Here is a simple building block that will do this for us.
This building block is pulling the YTD (Year to Date) values for the Base Year and the Base Period. Also, in the currency display attribute we have selected the Accounting currency (highlighted in red). This will ensure that the accounting currency is used in this column.
Here we can leave this column for Currency Display blank and it will amount to the same thing.
When we run this report for the base period 1 (January) and base year 2018 what we get is 0.00 for all the accounts. The account 140100 that we picked up while generating the system Trial Balance inquiry is also showing 0.00 amount as YTD account balances.
To summarize, our Financial reporter returns the same results as the system Trial Balance inquiry for the period of January 2018.
Remember, when we ran the 12/31/2017 Trial balance from within Dynamics 365 we had seen a value of 5,302,452.95 (shown in figure 1). So far it seems both the system Trial balance inquiry and the financial report have failed the YTD test.
Fix incorrect YTD
Let’s now make a small change to the column design. In the currency display select a currency for which we don’t have a translation rate (this way when the system doesn’t find a translation rate it will assume 1 as the conversion factor).
In the column design shown above, we have selected a currency CHF. If you have access to sample company data for USMF you will see that there is no exchange rate defined for USD to CHF conversion.
When we run the same report again this is what we get.
Notice that we now have YTD account balances numbers showing up for GL account 140100 – and for other accounts too.
The reason why the numbers have started showing up in the financial reporter output is because when there is a non-local currency in the currency display column the financial reporter does its own calculation for the Year to Date (YTD) numbers which considers the transactions from unclosed years also.
This results in the correct YTD value showing up on the financial report. So you will perhaps agree that with a very small tweak you can get your YTD numbers to start showing properly even for open years in financial reporter.