Managing General Ledger and Analysis Budgets

Uploading Budgets from Excel and Subsequent Amendment.

Written By Grainne Reidy (Super Administrator)

Updated at November 2nd, 2023

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.

Option Two: Budget Review and Maintenance screen

In the blue banner go to GeneralBudget Review and Maintenance.
 


 

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

Delete

Updating your Budgets via Excel

Process Overview

Use Excel to prepare bulk sets of Budgets, such as in Annual preparation or revision. The process is as follows:

  1. Extract an existing set of Budgets and/or Actuals from AIQ to Excel on your Desktop. 
  2. Edit the data in Excel as required to produce the required set of Budgets.
  3. Upload the Excel data back into AIQ.

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

Using filters you can extract, to Excel, manageable subsets of the system. You can choose one or more GL Categories, and optional Sub-Categories, to work on. This ensures the Excel extract only contains the chosen General Ledger and BI Codes for the General Ledger Category. 

Step One: Extracting data

  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 Exiting Values: Decide whether to download Period Budget values for the year above, Period Revised budget values, or Period Actual values 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.

Delete

Using the Budget Review and Maintenance screen

As an alternative to the lengthier Excel process, you can use the Budget Review and Maintenance screen. This is useful if you are using Departmental (BI Code) Analysis for 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.
Delete

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.

Delete

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.

Delete