About Budget Maintenance

Use the upload function to import budgets or revised budgets into the General Ledger. Budgets/revised budgets can be used for comparison purposes on a wide variety of GL reports. Budget Maintenance lets you view and manage your budgets as follows:

• Review and compare General Ledger Accounts by one or more Account Categories, Sub-Categories, or Departments.
• Compare Actual, Budget and Revised Budget values for consecutive or non-consecutive financial years.
• Review period, yearly, or Actual Values, either future or past.
• Enter Budget and Revised Budget data manually or upload it using templates.

Viewing Budgets in GL Listing Grid

Go to GL > General Ledger Accounts.

If the Period Budget and To Date Budget columns are not present, click Add/Remove Columns and add them. 

Prerequisites

Before you start setting up your Budgets, reduce your workload by reviewing the pre-supplied set of General Ledger Codes and deleting any unwanted ones. See Managing General Ledger Accounts for further details.

See:

Managing General Ledger Accounts‍ 

GL Account Queries (11.5) - AIQ Academy

Managing Budgets (11.6) - AIQ Academy

Process Overview

The process is as follows:

1. Download an Excel template list of GL accounts and Departments together with associated budget or historical actual period values.
2. Edit the data in Excel as required to produce the required set of Budgets.
3. Upload the Excel data into the system. You can also link the Excel template into existing financial models to allow these to be automatically uploaded to the GL.

1. Go to GL >  Download Budget Template to open the Download Budget Template screen.
2. Under Include Accounts, select the data to extract. For example, if you only budget against Profit and Loss GL accounts, you can choose to extract that set only. For Selected Categories, you can choose one or more GL Categories, and optional Sub-Categories. This ensures the Excel extract only contains the chosen GL and BI Codes for the General Ledger Category.
3. Under Download Options you can choose to:
   • Prepopulate Based on Used A/Cs in Fin. Year [Choose Year]: Tick and the system will download an import template prepopulated with data matching the selected year.
   • Include BI Code Analysis: Tick and the system will include BI Code level detail if the system already has a historical posting to a particular GL and BI Code combination.
   • Include Accounts with no movements: Tick and the system will also include GL accounts for which there have been no previous movements posted against them. This is useful to tick if you have recently added new GL accounts that you wish to hold budgets against in a future period for example.
   • Prepopulate with Existing Values: You can choose to pre-fill the import template with existing budget, revised budget or actual period values based on a selected financial year and set of GL Categories to assist with your subsequent budget maintenance in Excel.
4. Click Download and open and/or save the Excel file on your Desktop.

Step Two: Preparing the Budget Template

The downloaded file will look like the example below:

In the extracted Excel file, prepare your budgets, taking note of the following:

Data Completion Rules

• Leave the name of the worksheet sheet as 'Budget' so that when uploading it the system reads the values.
• All black text such as the GL Account Names, BI Code, and Row and Column Totals appear for informational purposes only. You do not need to populate these if you are adding new lines. These names will not go through validation on import.
• All blue text is considered importable data and will go through validation.
• If you have an existing Budget model you can embed this sheet in the model and map the value cells to the relevant values in your model.
• If you use BI Codes, the BI and GL Code combination comes from transactions. If no transaction exists for a particular BI and GL Code combination, then no budget line for that combination will appear on the Excel extract. If necessary, update the BI Codes for a specific GL Code, or the GL Codes for a specific BI Code. This will not impact other uploaded budget values. This allows budgets for a location/Department, or Revenue/income to be uploaded individually without impacting cost budgets.

Complete the following:

• Financial Year: Specify the 'Financial Year' that you want to import the new budgets into.
• Budget Dataset: Specify whether you want to import the values into the 'Budget' or 'Revised Budget' fields in the system by choosing the 'Budget Dataset' in the header of the file.
• Period Values: Complete the monthly values against each GL Code & BI Code Combination. GL Code is mandatory but BI Code is optional.
  • Enter figures to two decimal places.
  • Enter all CR values as minus figures. For example, for P&L Accounts all revenue/income figures are CRs and therefore minuses. The overall totals for each month will show profit as a minus/red and losses as plus/black.
  • To add a new row, select a row you want to base it on, for example with the desired GL, or BI Code. Copy the row, paste it where you want it, and change The GL, or BI Code as relevant. Be careful to change either the GL Code or BI code as duplicates will be rejected.
  • Do not leave any rows with zero values as importing these zero values will wipe out the existing budget values for that GL Code-BI Code. To avoid this happening, delete the rows that have zero values so that they are not imported.
  • When complete, check the annual Totals (column D or F) and Monthly Totals (row 2) to ensure the budget makes sense. Monthly Total should show the profit/loss for that period if all P&L GL Codes are included.

Step Three: Uploading the Budget template

When you have completed your budget file:

1. Go to Upload Budget Template.
2. Browse for your saved file.
3. The system will then validate the file. If there are any validation errors, the system will produce an error file with for you to download. Correct the data as instructed and reupload the budget.

The Budget Review and Maintenance screen lets you enter criteria for GL Codes, Categories, Sub-Categories, and Departments and show the actuals, budgets, revised budgets, and variances for each period for selected years. This is useful if you are using Departmental (BI Code) Analysis budgets, Project, or Job Accounting rather than, or in addition to, traditional Departmental Accounting. Each time you set up a new Project or Job, you can enter Budgets directly. 

Viewing Budgets

Data goes back two years. 

1. In the blue banner, go to GL > Budget Review & Maintenance.
2. Select an account from Budget for and optional Category and Sub-Category. Click View. If needed, click Clear Filter to restore the program to its initial state.  

Updating Budgets

• If the General Ledger Code has no BI Code associated with it, enter the Budgets directly in this screen. Click Update.
• If the General Ledger Code has a BI Code associated with it, select the appropriate BI Code (or each one in turn for the chosen GL Code) from the BI Code dropdown list. Enter Budgets for each period.

Actions

Calculate new Budgets from old Budgets or Actuals. Find these functions in the Actions dropdown:

• Adjust: This allows you to adjust another Dataset, such as Budget, or Revised Budget, for the chosen years from a selected Dataset:
• Spread: This allows you to spread an annual amount evenly across all periods. Click Apply and Update to complete the process:
• Fill: This allows you to fill every Period in the chosen Dataset with a value. Click Apply and Update to complete the process:
• Copy: This allows you to copy from one Dataset to another Dataset. Click Apply andUpdate to complete the process:

In addition:

• Display signs as reversed: You can also change the value signs (the internal signs used in the system).
• Export to: Export the Datasets to several formats.
• Budget Upload: Link to Upload Budget Template screen.