Introduction

Every General Ledger Code or combination of General Ledger and BI Code can carry several years of Budget and Revised Budget data. This data can be period by period or Actual Values, and future or past. 

You can view budgets from two locations:

Option One: General Ledger Accounts grid

Go to General > General Ledger Accounts. If the Period Budget and To Date Budget columns are not present, click Add/Remove Columns and add them.

Option Two: Budget Review and Maintenance screen

In the blue banner go to General > Budget Review and Maintenance. 

The Budget Review & Maintenance lets you review your General Ledger dataset values in terms of yearly/monthly account comparisons.

You can use it to:

• Review and compare values for one or more Account Categories and/or Sub-Categories or Departments
• Compare Actual, Budget and Revised Budget values for consecutive or non-consecutive financial years.
• Review and compare figures for specific General Ledger Accounts
• Enter Budget and Revised Budget data directly on screen.
• Upload Budget Data from Excel using the Budget Upload function.
• View Graphs of the displayed data and choose how the graphs.
   

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:

How do I Setup and Maintain General Ledger Accounts?‍ 

GL Account Queries (11.5) - AIQ Academy

Managing Budgets (11.6) - AIQ Academy

Using Budget Templates

Use this function to upload budgets or revised budgets into the General Ledger. Budgets/revised budgets can be used for comparison purposes on a wide variety of GL reports.

Process Overview

Use the Excel template to record budget values, such as in Annual preparation or revision. 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.

BI and General Ledger Codes

If you used Extended Business Analysis for Department or Project Accounting (BI Codes), the set of BI and General Ledger Codes comes from the transactions in the System. 
 

If no transaction exists for a particular combination of BI Codes and General Ledger Code, then no budget line (Row) for that combination will appear on the Excel extract. If necessary, you can add budget lines into the downloaded Excel extract for subsequent re-import to create that budget combination.

Extracting data subsets

1. Go to General > General Ledger Accounts > Download Budget Template to open the Download Budget Template screen.
   
    
2. Under Include Accounts, extract the required data. For example, if you only budget against Profit and Loss GL accounts, you can choose to extract that set only.
   
   
   
   
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: Editing the downloaded Excel File

The downloaded file will look like the example below (does not include BI Codes):
 

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

Valid Data

• Specify the 'Financial Year' that you want to import the new budgets into. 
• 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. 
• Complete the monthly values against each GL Code & BI Code Combination. GL Code is mandatory but BI Code is optional. 
• All black text such as the GL Account Names, BI Code Names 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 text in blue is considered importable data and will go through validation. 
• When complete, check the annual totals (column D) 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. 
• Leave the name of the worksheet sheet as 'Budget' so that when uploading it the system reads the values. 

Numeric Formatting Rules

• Enter figures to two decimal places. 
• Enter all CR Values should 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.

Copying data

• 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.

• 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.

Avoiding Data Deletion and selectively updating data

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. 
 

If a GL/BI combination does not appear in the Excel sheet, any of the associated budget values will not be overwritten when the Excel sheet is imported. You can just update all BI Codes for a specific GL Code, or all GL Codes for a specific BI Code without affecting other budget values already uploaded. This also allows separate budgets for a location or Dept to be uploaded individually or Revenue/income budgets to be updated without impacting cost budgets.

Step Three: Uploading the edited data

When you have completed your budget file:

1. Click 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 download a validation file with the warnings and explanations in it. Correct the data as instructed and reupload the budget.
   
   

Using the Budget Review and Maintenance screen

As an alternative to the lengthier Excel process, you can use the Budget Review and Maintenance screen. It allows you to enter criteria for GL Codes, categories, sub-categories, Departments, and groups and show the actuals, budgets and variances for each period for selected years. You can also compare against budgets, revised budgets or previous year actuals.


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 in the System you can enter Budgets directly. 

Using the Budget Review and Maintenance screen

You can enquire about Actuals, Budgets, and Revised Budgets. This can be done period by period, over two years for any combination of General Ledger Category and General Ledger Sub-Category. 

You cannot update Budgets at this level since Budgets can only be maintained at the individual General Ledger Code or General Ledger/ BI Code level rather than using the higher-level Category and Sub-Category.

1. In the blue banner, go to General > Budget Review & Maintenance.
   
   
    
2. Select from each of the dropdowns. In this example, the company uses Departments so we can select an individual one, or ALL, in the Project Activity dropdown. 
3. Click View. 

To enquire about individual General Ledger Code/BI Code:

1. Click Clear Filter to restore the program to its initial state.
2. Select the required General Ledger Code from the Budget for dropdown. 
3. Select the desired Parameters.
4. Click View.
   
   

To enter budgets directly into the system:

If the General Ledger Code has no BI Code associated with it: 

1. Enter the Budgets directly in this screen.
2. Click Update.

If the General Ledger Code has a BI Code associated with it (as shown in the example above): 

1. Select the appropriate BI Code (or each one in turn for the chosen GL Code) from the BI Code dropdown list. 
2. Enter Budgets for each period.

Budget Review and Maintenance Actions

You can calculate new Budgets from old Budgets or Actuals. Conform to the entry rules described previously. 

Find these functions in the Actions dropdown on the lower left-hand side of the screen:

Adjust:

This allows you to adjust another Dataset, such as Budget, or Revised Budget, for the chosen years from a selected Dataset:

Here we can create a Budget for 2016 by applying an increase of 4.00% to the 2015 Budget. Click Apply and Update to complete the process. You can also use a negative % value here:

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:

 

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.

Budgets Review and Maintenance Charts

The Budget Review and Maintenance screen also provides Charting facilities. 

Access the Graph by clicking the Chart tab:
 

To modify the contents of the chart:

1. Modify the contents of the Chart by clicking on Graph Settings…
2. Select any combination of the six datasets and choose between Line and Bar charts.
3. Click OK.