Knowledge Base | Abila



Document Type: INFORMATIONAL

Title: FAQ-Tracking Balances across Fiscal Years

Question: I have a balance in an account that crosses a fiscal year. I’m not sure if the balance is correct, how do I check it?

Product:

Product: Sage Fund Accounting

Module: General Ledger

Version/SP: All

KB #: 318352

Solution:

There are several reports that can be run when tracking down balances that cross fiscal years. How the report is run and how to interpret the results depends on two factors

1)    What type of GL Account is it that you are trying to track the balance on? Is it Revenue or Expense, a Balance Sheet or a Net Asset Equity type Account? If you are unsure of the answer the best way to check is to go to Maintain>Chart of Accounts. Select your GL segment and then select the account in question. Look at the Account Type field. This will tell you what type of account it is. [pic]

2)    Are you tracking the balance across a closed fiscal year or is the year open and you are simulating a year end close. If your account balances begin date is the first day of your first open fiscal year and your current reporting year begins the same time and your current period dates are also within that year then you are only reporting on that year then you are not simulating a year end close. If you’re current reporting year dates or your current period dates are after the year of your account balances begin dates you are simulating a close. You will see this mentioned in the bottom left hand corner of your report setup tab. [pic]

3)    Are you reporting on a Closed Fiscal Year? If your account balances begin dates are the first day of a closed fiscal year then you are reporting on a closed fiscal year. Your current period dates should be entirely within that closed fiscal year. YOU CANNOT DO CROSS FISCAL YEAR REPORTING ON CLOSED FISCAL YEARS AND GET ACCURATE NUMBERS. Such reports must be run one year at a time.

Setting up the report

Regardless of the type of account and period you are doing the basic report you will use remains the same. You should go to Report>GL Analysis>Expanded General Ledger. Create a new report. The dates on the setup tab will vary with your scenario but the content and filters will remain the same. On the Content Tab the report should look like this

[pic]

For shorter reports you may wish to omit document number.

On the filter tab you should filter on the GL code you are trying to analyze.

Additional Information

When running these reports it is going to be very important to know the first day of your first open fiscal year. The best way to find this out is to go to Activities>Close Fiscal Year and check the date that comes up. Your first open day is the first day of the year that ends on that screen.

[pic]

In this example since the year ends 12/31/07 the first day of the first open fiscal year would be 1/1/07. If the screen had said 6/30/08 then it would have been 7/1/07. You should write this down for reference.

Balance Sheet Type Accounts

This is the easiest account to track across fiscal years. If everything goes correct the ending balance of one year should be the beginning balance of the next.

Scenario 1 – Checking the balance from a closed fiscal year to an open fiscal year.

In this scenario your checking the ending balance of a fiscal year that has actually closed into another open fiscal year. You will first find out the ending balance of the closed fiscal year and then run the report a second time to find out the opening balance of the next fiscal year.

The Closed Fiscal Year End Balance

To get the ending balance of the closed fiscal year you will run the expanded general ledger report mentioned above.

For the Account Balances Begin date put the first day of the CLOSED fiscal year.

For the Current Reporting Year Begins date put the first day of the CLOSED fiscal year.

For the Current period dates from and through put the LAST day of the CLOSED fiscal year.

In our example the year ending 12/31/2007 has been closed.

We want to get the ending balance of that year so our report dates would look like this.

[pic]

We choose 12/31/07 as the from and through dates because it makes the report run faster. We are really only interested in the ending balances. If we go to the last page of the report we see the ending balance. Of $3,050,342.17

The Open Fiscal Year Beginning Balance

Keeping the report setup the same change the dates on the report. All dates should be the first day of the first open fiscal year. In this case 1/1/08

[pic]

When you print the report you should the Current Balance should tie to the Ending balance

[pic]

Things may look a little different if you happened to have activity on the dates you are reporting. When you have activity the “Current Balance” becomes “Opening Balance” and you see the activity listed after it. Aside from wording there is not difference between Current Balance and Opening Balance. The source and the numbers are identical.

[pic]

The presence of this activity is often what causes confusion. If you run a Trial Balance or Balance sheet as of 1/1/08 what you think is your beginning balance will be off by the amount of that activity. That is because the balance sheet is taking your opening balance AND the activity on 1/1/08 and netting them together. By running the Expanded GL instead we can see them separated.

Getting More Detail-

Sometimes you may want more detail as to what makes up your opening balance. On the Expanded General Ledger report you can get more detail by going to the Options Tab and checking the “Opening Entries in Detail” check box.

When you run the report with opening entries in detail you get a higher level of detail in place of your Opening/Current Balance.

[pic]

In this example you can see the actual session ID and document number that accounted for the closing entries. You can see the entry type that indicates it was an SO (system opening) entry. You can also see how much was closed to debits vs credits.

If we were to do some User Opening Entries (UO) we would also see it if we show opening entries in detail. As would any Adjust Opening Balances (AO).

[pic]

Using this report and these dates we should be able to proof the opening balances for our first open fiscal year.

If for some reason the ending balances do NOT equal the opening balances and your report is set up correctly (double check) the chances are that an entry has been made into the closed fiscal year. Because of this the balance in the prior year has changed after the year was closed but the opening balances for the current year were not updated. To correct this you will need to reverse the prior year entry and put it back in with an Adjusting Entry to bring the balance forward to the current year.

Scenario 2 – Checking the Opening Balances When a Simulated Close is Involved.

If you are running the report with a date range across multiple open fiscal years the procedure is a little bit different.

For example. Let’s say that the first open day of your first open fiscal year is 1/1/08. You have finished off 2008 and are now into 2009. You want to make sure that your ending balances as of 12/31/08 are equal to your beginning balances of 1/1/09. You would use the same report but set the dates up a bit differently.

For the Account Balances Begin date put the first day of the first OPEN fiscal year.

For the Current Reporting Year Begins date put the first day of the OPEN fiscal year.

For the Current period dates from and through put the LAST day of the PREVIOUS (but open) fiscal year.

[pic]

This gives us our ending balance as of 12/31/08

[pic]

To get the opening balance of 1/1/09 then change the dates.

For the Account Balances Begin date put the first day of the first OPEN fiscal year.

For the Current Reporting Year Begins date put the first day of the CURRENT fiscal year.

For the Current period dates from and through put the FIRST day of the CURRENT fiscal year.

[pic]

At the bottom of the report you will see that a year end close will be simulated.

[pic]

You should see that the opening balance is equal to the ending balance of the previous report.

[pic]

Revenue and Expense Type Accounts

When tracking balances for Revenue and Expense Type accounts across a fiscal year the procedure for running the reports remains the same but the results require a little bit more interpretation.

When dealing with balances across a closed fiscal year we set the dates like we did before with the balance sheet type accounts.

[pic]

This gives us our ending balances for the closed fiscal year

[pic]

Then we adjust our dates for the first day of the open fiscal year

[pic]

Notice our balance DOES NOT TIE

[pic]

Why is this?

The reason is that Revenue and Expense Accounts should NOT have an opening balance. When they cross a fiscal year they are supposed to be closed out into the Net Asset/Equity account. So we really shouldn’t expect to see an opening balance. So in this case all we see is the activity that was posted on 1/1, there are no opening balances and this is correct.

When a Year End Closed is simulated

One of the most confusing situations is when you are looking at a revenue or expense account on a report that simulates a year end close. If we change the dates on our report to simulate a year end close we can see the result.

Dates

[pic]

Result

[pic]

When we look at the result we see that there is now Current/Opening Balance. This seems to contradict what happened before where there was not balance rolling forward.

To understand what is happening you have to look more closely at the report. If you notice the GL code listed is not 50001 that we filtered on but 30001. This is the Net Asset/Equity account that 50001 closes into. What the system is doing is reporting the amount in the expense account that would have closed to the Net Asset/Equity account IF there was an actual close. This number is being included in the report so that you see how much was coded to your account in the prior year even though it’s not a real opening balance.

If we had activity on that day it would show up as normal and our current balance would convert to an opening balance like before.

[pic]

Net Asset Equity Accounts

Proofing the Net Asset Equity Accounts (NAE) across fiscal years is significantly more complex. The beginning balance of the NAE account is equal to the ending balance of the NAE account in the prior year plus the net of the revenues and expenses assigned to close into the NAE account.

When the NAE account has been in use for several years your beginning balances is equal to

NAE Balance = Opening Balance + Activity + Revenues – Expenses

The Opening Balance + Activity is easy enough to find. You can run the expanded GL report as if it were a balance sheet type account. Where it gets complicated is figuring out the Revenues – Expenses.

If you have only one NAE type account it makes it easier to track Revenues – Expenses. They all close into the same account.

Where it gets complicated is when you have multiple NAE accounts. With multiple NAE accounts it is possible have accounts closing into multiple NAE accounts. This means that when you look at an individual NAE account you may not be getting a complete picture. On top of that it is possible for the same GL account to close into different NAE accounts depending on what Fund code was used in the transaction. So you can have any combination of Fund/GL’s closing into any account.

The best way to determine what is closing is to go to Reports>Lists>Closing Account Assignments. This will give you a list of all of your closing account assignments.

Assuming you only have one NAE account (or you understand what is closing where) how do you check the balance across a fiscal year?

There are three reports that you can use for this. A Statement of Revenues and Expenses, A GL Report or a Trial Balance. The simplest report to use is a Trial Balance. The only problem with this is that you have to know for sure which of your accounts are revenues or expenses. Normally all of your Revenues or expenses will be continuously numbered after a certain value. But this is not always the case. Also someone may have made a mistake when setting up a GL code and set it up as something other than a Revenue or expense account even though its number indicates it should be one.

Below is a sample report setup for a statement of Revenues and expenses that is set up to just show your Revenues, Expenses and the net of the two.

Report Setup

[pic]

Financial Statement Format Setup

[pic]

Both the Revenue and expense sections have all the appropriate accounts in them and are set to print in detail. The bottom section is a totaling section of the two.

With the Statement of Revenues and Expenses we would not need to filter on anything.

If you were to use a Trail Balance it might look something like this

[pic]

In the filter tab we might want to filter on GL Code > 40000 which in the sample database is the beginning of the Revenue and Expense Accounts.

Either way once the report is set up you need to select the proper dates

Tracking Balances from a closed fiscal year to an open fiscal year

For the Account Balances Begin date put the first day of the CLOSED fiscal year.

For the Current Reporting Year Begins date put the first day of the CLOSED fiscal year.

For the Current period dates from the FIRST to the LAST day of the CLOSED fiscal year.

In our example the year ending 12/31/2007 has been closed.

We want to get the ending balance of that year so our report dates would look like this.

[pic]

If we run the report we see that our Revenues are greater than our expenses by $9,60,584.59 This will be our change in fund balance.

[pic]

Now we have to find out what our ending balance is in our NAE account. This is done by running a GL report just like it was a balance sheet account.

[pic]

The only difference is that we want to filter for our NAE account.

[pic]

This gives us our ending balance. The ending balance plus the change in fund balance should equal our new beginning balance. To prove this we run another GL report for the first of the year.

Dates

[pic]

Result

[pic]

If we take our Ending Balance of $6,061,971.30 and add our change in fund balance of $9,60,584.59 we get our Beginning/Current Balance of $702.555.89.

Like before you may consider running the report with opening entries in detail. This shows the actual detail information of the entry that makes up the opening balance.

[pic]

As you can see it is the summary of the ending balance plus the change.

Is there any way to see what GL Accounts that came from?

Unfortunately the SO entry only records the values that closed into fund balance as of the first day of the fiscal year. There is no single report that definitively says $350.00 from GL 51000, Fund 6, and Program 12 closed into GL 30001 on 1/1. If you want the detail of what made up the closing entry you should look at the expanded GL report and the Statement of Revenues and Expenses. If the ending balances matches the opening balance for your NAE account then all the detail information of what made up your opening balance is contained in those two reports.

What if it doesn’t match?

If the ending balance of the GL + R&E reports do not match the opening balance on the GL report then you have an issue. Due to the complexity of these reports the most common cause of a mismatch is a reporting error. Start by making sure your reports are set up correctly. Then go through and check all of your revenue and expense accounts. It is common to miss an account assignment on the financial statement format. Check to make sure all of your revenue and expense accounts are in fact revenue and expense accounts. Also check to make sure you don’t have any other accounts that you thought were balance sheet type accounts that are really R&E type accounts. You can go to Reports>Lists>Chart of Accounts for a complete account listing.

When checking all of your account types make sure that you don’t have any extra NAE accounts that you were unaware of. If you do check your closing account assignments, you may have values closing to one of those accounts and not know it.

If you are confident all of your accounts are properly set up and assigned then the problem usually lies in a prior year posting. Something has been posted into a closed fiscal year and has not rolled forward into the current fiscal year.

To try and find transactions that have posted into a close fiscal year see KB 306245.

Tracking Balances across Open Fiscal Years

The reports to track the balances across open fiscal years are as follows.

To get the ending change in fund balance run a Statement of Revenues and expenses with the following dates

For the Account Balances Begin date put the first day of the first OPEN fiscal year.

For the Current Reporting Year Begins date put the first day of the first OPEN fiscal year.

For the Current period dates from the FIRST to the LAST day of the first OPEN

fiscal year.

[pic]

The Result will give you your Change in Fund Balance

[pic]

Now to get the Ending Balance of your NAE Account run the expanded general ledger report.

For the Account Balances Begin date put the first day of the first OPEN fiscal year.

For the Current Reporting Year Begins date put the first day of the first OPEN fiscal year.

For the Current period dates put the LAST day of the first OPEN

fiscal year.

[pic]

The result will give you your ending balance

[pic]

After you have these two numbers you will need to manually add them together. When MIP simulates a year end close it does not put the net of the revenues and expenses into the NAE account on a GL. As we saw in one of the previous examples it carries the balance on the report along with the revenue or expense account it was associated with. Therefore you must manually add the numbers together. If you run your GL report where the current transactions dates are the first day of the current year you will get your opening balance equal to your ending balance. The net of the revenues and expenses are not included.

[pic]

Question-

Can I use these techniques for tracking a balance across multiple closed fiscal years?

Answer –

Yes. The process for tracking a balance when both fiscal years is closed is the same as tracking the balance across a closed fiscal year. The thing to remember is that you can only run the reports for one year at a time. So if you want to track the balance across multiple closed fiscal years you will need to run the reports several times, once for each year.

For example you are trying to track a balance sheet balance from the end of 2004 to the beginning of 2006 and all those years are closed. You would use the instructions for tracking a balance sheet type balance. You would run the reports with the following dates

For the 2004 Ending Balance

Account Balances Begin 1/1/04

Current Reporting Year Begins 1/1/04

Current Reporting Dates 12/31/04 to 12/31/04

Then the 2005 Beginning Balance

Account Balances Begin 1/1/05

Current Reporting Year Begins 1/1/05

Current Reporting Dates 1/5/05 to 1/1/05

Then the 2005 Ending Balance

Account Balances Begin 1/1/05

Current Reporting Year Begins 1/1/05

Current Reporting Dates 12/31/05 to 12/31/05

Then the 2006 Beginning Balance

Account Balances Begin 1/1/06

Current Reporting Year Begins 1/1/06

Current Reporting Dates 1/1/06 to 1/1/06

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download