Understanding how to report on the Retained earnings account and return the right balances in various reports is crucial for those Velixo users who either design new financial reports or start from one of Velixo's pre-built templates, such as Balance Sheet summary or Trial balance which include this account. Even more importantly, although it can assumed that most users already understand this concept, it is crucial to also understand how Sage Intacct calculates and store balances for Retained earnings.

TABLE OF CONTENTS

• Understanding Retained Earnings from Sage Intacct report
  • Accessing and running the Sage Intacct built-in Balance Sheet report 
  • Understanding the Sage Intacct Balance Sheet report
  • How can Velixo replicate the same behavior of the report?
  • How to use Velixo to calculate Retained Earnings on a YTD basis

Understanding Retained Earnings from Sage Intacct report

Accessing and running the Sage Intacct built-in Balance Sheet report 

• To access the built-in Sage Intacct report, go to Applications > General Ledger > Financial Reports > Balance Sheet - Summary

• Clicking the built-in report will prompt you to select filters:
  • Reporting Method
  • As of date
  • Department
  • Location

           The selected filters will drive the balances that will be extracted from the ERP

Understanding the Sage Intacct Balance Sheet report

• As default, Sage Intacct will return three periods for the YTD balances.

  But for this sample, let’s focus on the first YTD column which is the one specified on the As of date filter:

• As shown above, the Retained Earnings line is located under the Stockholders Equity section of the balance sheet. This balance is the rollover balance from the previous period. Please note that this is always booked on the first day of the reporting period and calculated using the accumulated Retained Earnings + Net Income from the previous period.

• Using the report that we ran from Sage Intacct as a sample - the Dec YTD 2019 Retained Earnings is calculated by adding the Nov YTD 2019 RE (at the beginning of the period) and Net Income (representing the movements within the current period, i.e. since the first day post RE recording). The total of Nov YTD 2019 RE and Net Income is being closed to the first day of the next reporting period. For this case, it is December 1, 2019.

• This can be confirmed by clicking the Retained Earnings balance to open another screen to show the Retained Earnings balance and the date it was rolled over to.

As illustrated above, the balance forward date is as of 12/01/2019, and the balance forwarded is the total of the previous period YTD Retained Earnings and the previous period MTD Net income (Nov 2019).

How can Velixo replicate the same behavior of the report?

First and foremost, we need to make sure that the Velixo filter is the same as what was used on the Sage Intacct report. Once set, let’s re-create the retained earnings from the built-in Sage Intacct report using Velixo function SI.CLOSINGBALANCE.

Note: As mentioned, Retained Earnings are always booked on the first day of the reporting period. Please keep this in mind as the Velixo function SI.CLOSINGBALANCE needed two dates argument to work:

1. Start Date
2. End Date

It is important to specify the correct dates on these two arguments if we want to get the same Retained Earnings as the Sage Intacct report for a specific period.

• Connect to Velixo login portal and input your credentials.

• On your Excel file, input the same parameters used for the Sage Intacct report.

  • Reporting Method: Accrual
  • Date: 12/31/2019
  • Department: All Departments
  • Location: 100 – USA 1

We can omit Department dimension for this case as we are not retrieving balances for a specific one.

• Specify the Retained Earnings category or the default account code assigned to RE. We can add the Net Income category as well so that we can visualize the calculations more properly.

• For the dates, we have two methods to get the retained earnings:
  • Using a month-to-date parameter: this will be the easier method and less confusing as we are going to use the same period as your YTD. Except for the Start Date which is the first day of the selected period. For example, if the YTD report date is as of 12/31/2019, the start date should be 12/01/2019 instead of 01/01/2019.
  • Using the previous period’s end of month + 1: this approach will work as well but might create confusion especially if the user is not familiar with the Velixo SI.CLOSINGBALANCE function. For example, if the YTD report date is as of 12/31/2019, the dates that we need to use for both Start Date and End Date is 11/30/2019 + 1.

Nonetheless, both methods will give you the 1^st day of the reporting period but for this exercise, we are going to use method #1. This method is a lot easier and will make much more sense once applied to the template.

As shown on the below illustration, the Retained Earnings for the period 12/01/2019 – 12/31/2019 is the total of the Retained Earnings + Net Income from the previous period 11/1/2019 – 11/30/2019. This is also tied with the Sage Intacct built-in report for both Retained Earnings and Net Income accounts.

How to use Velixo to calculate Retained Earnings on a YTD basis

Basically, that’s how to calculate the Retained Earnings to match with the Sage Intacct report. However, we can still create our own Retained Earnings calculation using YTD dates.

As a sample, let’s create a new column and use the YTD date parameter 01/1/2019 – 12/31/2019.

See how the Retained Earnings and Net Income on Column C did not match with the Sage Intacct report?

Please see explanation below:

• The Retained Earnings calculated on Column C using the Velixo function SI.CLOSINGBALANCE does not contain any Current Year Earnings (or Net Income). This is purely the rollover balances from previous year (for this case, 2018). This is totally different with the Sage Intacct built-in report where the Retained Earnings is already including the Current Year Earnings (Net Income) from previous periods.
• Same goes for Net Income as the Sage Intacct built-in report only gets the monthly movement for the Net Income amount and not the YTD balance.

Regardless, the totals are exactly the same for both the Velixo driven report and the Sage Intacct built-in report.