General Ledger Budgeting Tool

General Ledger Budgeting Tool

General Ledger Budgeting Tool

The General Ledger Budgeting Tool is found in the Tools menu of the Chart Of Accounts.


 

This will present you with the GL Account Budgets application:



The first thing you will need to do is to select which Fiscal Year you would like to create/update budgets for:


 

The Fiscal Opening Dates listed are all fiscal years which are listing in the Year End Processing application. In order for a date to be available in the Budget Tool it must be listed in the Year End application as seen here.



Once you have selected a Fiscal Year, any existing Budget Worksheets for that year will be displayed.



Since budget variances from one year to the next can be different depending on the type of GL account or the Department it belongs to, Budget Worksheets work with sections of your Chart of Accounts. While this may require a number worksheets to manage your entire Chart of Accounts, generally it is easier to work with it in sections as opposed to the entire GL listing all at once.

Creating a Budget Worksheet

Select the “New Budget” icon to create a new budget worksheet. This will present you with a filter screen for you to select the section of the Chart of Accounts you’d like to work with and what you would like the Suggested Budget to be based on.



Using the drop downs beside each filter, select the criteria to be used for creating the Budget Worksheet.

First you will select which fiscal year you would like to create a budget for. You must select a date.


 

Next, you will select which type of GL account you’d like to work with. You must select an account type.

Next, you can select a department. Typically you would work with each department on its own as projected variances from one year to the next can differ depending on which Department you are working with.

By selecting the “Blank” value indicates that you want to work with ALL GL accounts for the selected Account Type. When working with balance sheet accounts this filter is not available.


 

Suggested Budgets can be calculated based on one of three different existing values: Previous Actual, Previous Budget or Prior Actual. “Previous” refers to one year before the Fiscal Opening Date of the worksheet and “Prior” refers to two years before the Fiscal Opening Date.


 

Lastly, you will provide a percentage variance to be used when calculating the Suggested Budget. This value will be used in the creation of the worksheet. Once created, you will be able to modify the percentage variance on individual GL accounts.

Selecting “OK” will create the Budget Worksheet.


 

The Budget Worksheet

When first displayed the worksheet is shown in summary format, one line for each GL account meeting the selection criteria.


 

Three values based on existing GL data are displayed for each account, Previous Year Actual, Previous Year Budget and Prior Year Actual.

Next the Suggested Variance which initially contains the same value for all GL accounts. You have the ability to change the variance percent on any line item and can enter either positive of negative values. Manual changes to the variance rate will recalculate the Suggested Budget for that line item only. The Suggested Budget is calculated based on the Suggested Variance Percent and GL data value as selected by “Base Suggested On”. The formula is ( Base_Value + ( Base_Value X Variance_Percent ). It is the “Accepted Budget” that will become your final budget values. Initially it is set to be the same as the Suggested Budget, afterwards there are a couple of different ways that it can be changed which will be discussed later in this document.

Budget values are actually generated on a monthly basis. To view the monthly data or to manually alter the Accepted Budget for a particular account, selecting the “+” plus sign to the left of a line item will expand and display the monthly data as seen here.



You can expand or collapse all line items by using the options in the View menu as seen here:


 

Modifying the Budget Worksheet

The desired end result of modifying the worksheet is to have Accepted Budget values for each GL account and for each month for those GL accounts.

Option #1 is to change the Suggested Variance rate on individual line items. This should be your first method of modifying the worksheet. These changes will cause the Suggested Budget to be recalculated for those accounts. To have the updated Suggested Budget values moved over to the Accepted Budget column, use the “Set Accepted…” option in the Edit menu as seen here…


 

Option #2 is to manually change the monthly Accepted Budget values for the line items. To change these values you first must expand the line item in order to gain access to the Monthly Accepted Budget fields.

If after creating the worksheet you change the Account Type or Department assignment on any GL accounts, you can reset the GL listing in the worksheet using the “Reload GL” option

This will reload all of the GL accounts and reload the historical values and suggested budget amounts. You are also provided with the option to reset the Accepted Budget to the newly calculated Suggest Budget amounts.


 

Exporting the Worksheet for manipulation externally

At anytime you can export the worksheet in Excel format. You can use either the Export option in the File menu or the Export icon.


 

In the Export to File screen a filename will be suggested for you which combines Fiscal Year/Account Type/Department selections from the worksheet.


 

You can then use any tools at your disposal to calculate new Accepted Budget values.


 

Importing a Previously Exported Worksheet

Once you have completed your changes to the exported worksheet, you then have the ability to import it back into the Budget Worksheet. From the main GL Account Budgets screen, select the “Import Budget Data” option from the File menu which will walk you through the Import Wizard.


 

The wizard will first want to know which existing “Open” worksheet to update and which file on your work station to import. It is important that you save your Excel file to “.csv” format before importing. You must also ensure that the column assignments in your file are the same as they were when exported. The wizard will expect the data to be in specific columns. Once the import has been completed, the Worksheet will be opened and displayed on the screen in summary format.

Closing a Budget Worksheet

Once you have completed all your changes to a worksheet and are satisfied with the “Accepted Budget” values, simply change the status of the worksheet to “Closed” and save it. This will cause the Budget data in the Summary tab of all GL accounts in the worksheet to be updated using the Accepted Budget values from the worksheet.


 

Re-Opening a Closed Budget Worksheet

Once a worksheet has been closed, you have the ability to Re-Open the worksheet for further modification. Open the worksheet and in the “File” menu simply select the “Reopen Budget” option. This will reset the status to “Open”. Make your required changes and set the status back to “Closed” when completed.

Special Handling of GL Accounts with Percentage Distributions to Child Accounts

When a GL Account has been set up with %Distributions to children accounts, only the Parent Account will be listed within a Budget Worksheet. For these accounts the monthly “Previous Year Actual”, “Previous Year Budget” and “Prior Year Actual” will be calculated as a sum total of the children accounts.

The system will calculate the “Suggested Budget” for the parent account only based on the “Suggested Variance”.

When the Worksheet is closed, the monthly “Accepted Budget” for these parent accounts will be split on the same percentage basis as defined on the GL Account. These split values will be setup under the children accounts in the Summary tab of the GL accounts. Any discrepancy between the Accepted Budget for the parent account and the posted split entries for the children accounts will be added to the last child account updated. Entries in the summary tab for the parent account will all be set to zero.

By using this process, any determination of budgets is done at the parent account level but when closed it is held at the child account level.

This process only applies to parent/children accounts where the %Distributions have been setup. All other child accounts without %Distributions at the parent level will be listed and treated the same as the parent account in the Budget Worksheet.

Redesign of the GL Account Summary Tab

Not only has the tab been given a fresh new look but it also has improved functionality. You can now easily select different fiscal years to display as the current year.