The creation of budgets is a recurrent element of the budgetary function. The budgets can be constructed by disregarding the current situation or they can be based on previous budgets or real elements. This function is used to automatically calculate the elements constituting a budget code by means of formulas indicating the elements to be calculated and from where to start the calculation.

It is within this single function that you can set up all the calculation elements.

Example: the budget for company AAA is calculated each year on a global envelope basis (100,000 EUR). For each dimension, the proportion(%) of the actual N-1 with respect to the N-1 budget, determines the new percentage for the budget obtained for the year N. In this way, the budget N for "Department 1" is equal to the total amount for the Budget N* (Actual for "Department 1" / Total amount for N-1 budget). This case can be defined simply in the following way:

  • a calculation expression formula (for instance: V_BUDN*(F1/F2)
  • its constituting elements: F1 is the actual N-1; F2 is the budgeted N-1; V_BUDN is a constant that indicates the global amount to be distributed. By this simple setup, the calculation to be carried out could be defined.

Prerequisites

SEEREFERTTO Refer to documentation Implementation

Screen management

The setup of budget formulas is performed on two tabs Formulas and Variables and is used to determine the calculation rules of a budget. 

Header

Criteria

Formula (field BUDFOR)

Code on 10 characters, which identifies the budget calculation formula.
field DESTRA
Short description (field SHOTRA)

The short description replaces the standard description when display or print constraints require it.

By default the short title, the long title or the column header of a data are recorded (on creation/update) in the connection language of the user.
You can add your translation in another language using the Translation function:

  • Click Translation from the Actions icon in the corresponding field.
  • In the Translation window, add a new language code with the translation in this language.

A user who logs on with this language will view the short description, long description or column header in their connection language if a translation exists. Otherwise, these descriptions will be available in the folder language.

SEEINFO The connection language must be defined as a default language for thefolder.
Basis of transfer (field FLGDSP)

The transfer basis determines the budget calculation type.

  • In period totals The time allocation of the nature of the budget to be calculated is not applied to the calculation expression. The expression is calculated period by period and updates the budget amount period by period. The original distribution of the budgets that make up the expression is kept.

For instance: 

Original budget N-1: from 01/01/N-1 to 31/12/N-1

- Period 01:                           607000/SERV01/DIV01: 1000

- Period 02:                           607000/SERV01/DIV01: 2000

- Period 03 to 12:                   607000/SERV01/DIV01: 0

Calculated budget: from 01/01/N to 31/12/N

- Period 01:                           607000/SERV01/DIV01: 1000

- Period 02:                           607000/SERV01/DIV01: 2000

- Period 03 to 12:                   607000/SERV01/DIV01: 0

  • In annual amounts: The time distribution of the nature for the budget to be calculated, is applied to the global amount, calculated by the expression. Each budget, which composes the expression, is calculated for its period. The budget calculation expression is thus evaluated, then the allocation is applied, to find the budgeted amount period by period.

For instance:

Original budget N-1: from 01/01/N-1 to 31/12/N-1

- Period 01:                           607000/SERV01/DIV01: 1000

- Period 02:                           607000/SERV01/DIV01: 2000

- Period 03 to 12:                   607000/SERV01/DIV01: 0

- Time distribution key for 607000: 50 for January, 50 for February and 0 for the rest

Calculated budget: from 01/01/N to 31/12/N

- Period 01:                           607000/SERV01/DIV01: 1500

- Period 02:                           607000/SERV01/DIV01: 1500

- Period 03 to 12:                   607000/SERV01/DIV01: 0

If the decision is taken to only select a portion of the budgeted periods from the original budget (period 01 only), this has an impact on the amount to distribute (1,000 instead of 3,000) and the calculated budget will see an equivalent reduction. If the contrary is true and the decision is made to select all the budgeted periods (01 to 12) in the budget N-1, but to restrict the period for the calculated budget to the first period of the year, the selected amount will be identical to the instance, but the calculated budgeted amount will be distributed prorata with the time key only on this period (in this case only the 01 period would have been budgeted for 1,500).
Calculation expression (field CLCFOR)

The expression determines the calculation formula. The valuation of this calculation expression determines the budgeted amount of the budget entered on the first line of the grid.
The expression can contain the most common mathematical and logic operators making it possible to associate formulas (coded F1, F2, F3...) defined by the lines in the formula and variable grid (coded V_variable name) defined in the variable tab.
Each line in the grid corresponds to a budget formula. A budget formula indicates the budget amount to be taken into account. A formula is made up of F followed by the number of the line in the grid.

Block number 2

Generate budget MO (field OD)

If this box is checked, the program will generate the budget MOs in accordance with the budget setup.

Delete budget (field DEL)

Tab Formulas

The first line defines the budget to be calculated. The following lines define the formulas on which the calculation expression is based.

Type

  • Budget to be calculated: cannot be entered,
  • Amount column: specifies the type of amount to be distributed: Budgeted, Actual, Pre-committed, Committed.

Grid

Formula name (field FORNAM)

  • "Budget to be calculated" line: this is the budget data to be calculated.
    Indicate the analytical charges to be modified / calculated. It is also possible to assign a variable pre-fixed V_, which makes it possible to use the budgetary calculation for budget lines that need additional criteria at the moment of the calculation. It is not possible to assign wildcards ("?" or "*"), neither in the line nor in the variable.
  • Next lines: code of the budget to be distributed. Automatically assigned by Sage X3 (F1, F2, F3 etc...).
    It must be based on the same analytical ledger, on the same account or group of accounts and on the same analytical dimension or group of dimensions. It is possible to indicate fixed charges with or without wildcards ("?" or "*") and variable prefixed with V_.

For instance, it is possible to indicate for the budgetary line to be calculated: 607000 / SERV01 / DIV01 and for the budget line to be allocated: 607* / SERV01 / DIV01. In this scenario, all the budgetary line amounts 607*/SERV01/DIV01 (that is 607000 / SERV01 / DIV01 and 607100 / SERV01 / DIV01 and 607200 / SERV01 / DIV01), are copied to 607000/SERV01/DIV01.

Nevertheless if it is necessary to calculate and/or allocate the common charges to several budgetary formula lines, it is simpler to use variable.

Example: case of a "Company" budget calculation corresponding to the sum of budgets for multiple "Sites": the budget lines of sites are the same for each line of the formula. A variable can thus make it possible to avoid entering a budget line for each site.

Amount (field TYP)

It indicates the amount type to be taken into account:

  • Budgeted amount: Budget amount.
  • Actual amount: Actual amount of the balance.
  • Committed amount: Committed amount of the balance.
  • Pre-committed amount: Pre-committed amount of the balance.

The first line in the grid defines the budgeted amount to be calculated.
The following lines identify the amounts for the budgets that will be used as the budget calculation basis.
Budget (field BUD)

Field mentioning the budget code.
The first line in the grid identifies the budget data to be calculated.
The next lines identify the budgets to be taken into consideration to calculate this budget.
Company (field CPY)

This field can be assigned if the budget is defined at company and/or site level.

  • On the first line of the grid, the budgeted amount is calculated for the company and/or the site concerned.

  • On the next lines, mention the company and/or the site concerned by the budget to be distributed. It is not necessary that the budget to be calculated and the budget to be distributed are at the same definition level.
    For instance, it can be interesting to calculate a "Company" budget that represents the sum of the budgets for several "Sites".
Site (field FCY)

Field identifying the Site of the budget.
SEEINFO This field can be entered if the budget is defined at site level.

The budgeted amount is calculated for this site on the first line of the grid.
In the following lines, it is the budget amount for this site that is used as the calculation basis.
Ledger (field LEDTYP)

Specify the ledger impacted by the budget calculation.

SEEINFO The ledgers concerned by the budget calculation must necessarily have active "Analytical" and "Budget" flags (the budget can only be accessed if the "Analytical" flag is checked).
Version (field VER)

This field specifies the budget version impacted by the budget calculation.
Period start (field STRDAT)

Period start date.
The budgeted amount is calculated or modified for this period on the first line of the grid.
In the following lines, it is the budget amount for this period that is used as the calculation basis.
Period end (field ENDDAT)

Period end date.
The budgeted amount is calculated for this period on the first line of the grid.
In the following lines, it is the budget amount for this period that is used as the calculation basis.
field ACC

Budget account.
This field is mandatory on the first line of the grid. It is used to identify with which dimension the budgeted amount must be calculated.
On the following lines, the field is not mandatory and it accepts the * and ? wildcards. It defines the account(s) to be taken into account, to calculate the budget amount that will be used as the calculation basis.
field CCE1

Budget dimension.
This field is mandatory on the first line of the grid. It is used to identify with which dimension the budgeted amount must be calculated.
On the following lines, the field is not mandatory and it accepts the * and ? wildcards. It defines the dimension(s) to be taken into account, to calculate the budget amount that will be used as the calculation basis.

Tab Variables

The use of variables is optional since it is you can directly indicate in the formulas, the budget line to be calculated and/or the budget line to be allocated (e.g.: 607000/SERV01/DIV01). The variables make it possible to launch/relaunch the budgetary calculation while only modifying certain criteria. You can thus factorize the formulas sharing common variables.
 
Example : case of a budget calculation Company representing the total of budgets from multiple sites.
The site budgets have common formulas: by assigning the analytical allocations in the form of variables, it is possible to launch the calculation for all sites in common selections: 607000/SERV01/DIV01; then new execution of the calculation for 608000/SERV01/DIV01 and so on.

  • From the moment that a variable is entered, Sage X3 standardizes its code with the prefix "V_"
  • These variables can contain wildcards ("?" or "*") and account and/or dimension groups for the original budget. When an account/dimension is directly used in the variable, you can directly jump to the chart of Accounts/Dimensions.
  • Each calculation cancels and replaces the previous one.

Finally, let us take the first example, the table below displays the setup to be performed in the tabs Formulas and Variables.
The example was: the budget for company AAA is calculated each year on a global envelope basis (100,000 EUR). For each dimension, the proportion(%) of the actual N-1 with respect to the N-1 budget, determines the new percentage for the budget obtained for the year N. In this way, the budget N for "Department 1" is equal to the total amount for the Budget N* (Actual for "Department 1" / Total amount for N-1 budget). This case can be defined simply in the following way:

  • a calculation expression formula (for instance: V_BUDN*(F1/F2)
  • its constituting elements: F1 is the actual N-1; F2 is the budgeted N-1; V_BUDN is a constant that indicates the global amount to be distributed.
Setup of the Formulas tab:

Formula

Type

Budget

Company

Site

Start

End

Account

Department

Dimension type 2

Dimension type 3

Budget to be calc.

Budgeted

BUD04

001

 

01/01/2004

31/12/2004

601000

V_START

 

 

F1

Actual

BUD03

001

 

01.01.03

31.12.03

601000

V_START

 

 

F2 key

Budgeted

BUD03

001

 

01.01.03

31.12.03

601000

*

 

 
Setup of the Variables tab:
 

Variable

Title

Value

V_BUDN

Envelope to be allocated

100,000

V_START

Department

 

Result: at each calculation execution, the value of the variable V_START is required. It is thus possible to enter "Department 1" and launch the calculation and obtain the budget for BUD04 budget for the combination 601000/Department 1. Successively the variable V_START can take the values "Department2", "Department3" etc.

Grid

Variable (field VARCOD)

Code identifying the variable on 10 characters.
This code starts with V_. It can be used in the /expression for the calculation/BUCCLCFOR/ for the budget or to identify the natures or dimensions for the budget formulas.
Description (field DES)

This field is used to link a description to its record. This description can be printed on the records, displayed when the record code is entered in a screen, or used on selection.
The title, used by default in the reports and entry masks is replaced by the short title when it is required by display and print constraints.

By default the short title, the long title or the column header of a data are recorded (on creation/update) in the connection language of the user.
You can add your translation in another language using the Translation function:

  • Click Translation from the Actions icon in the corresponding field.
  • In the Translation window, add a new language code with the translation in this language.

A user who logs on with this language will view the short description, long description or column header in their connection language if a translation exists. Otherwise, these descriptions will be available in the folder language.

SEEINFO The connection language must be defined as a default language for thefolder.
Default value (field VARVAL)

Variable value.
Before running the calculation, the variable values are default values.
Upon execution of the calculation, these default values are proposed and can be modified.

Reports

Specific Buttons

Run

It runs the calculation

Error messages

In addition to the generic error messages, the following messages can appear during the entry :

Variable not defined XXX 

The assigned budgetary variable does not exist in the table. You need to use the function Selection to search for existing codes or jump to the budget variables setup directly using the associated field. The variables are defined in the variable tab, there is no tunnel or selection.

Incorrect expression, an opening/closing bracket is missing

A Sage X3 function is used in the formula but a bracket is missing.

Incorrect expression - Syntax error

A Sage X3 function is used but an entry error is preventing the correct interpretation of the formula.

ZZZ variable associated with the dimension type code XXX

The budget variables used at the level of the variable and the expression are incompatible because they are not associated with the same analytical dimension type code. It is necessary to replace the erroneous variable with an authorized variable.

Incorrect fiscal year

The fiscal year number entered does not exist in the fiscal year table. It is necessary to use the "selection" function to search for an existing number.

The expression does not contain a variable

To calculate a budget, it is necessary to use at least one variable in the expression.

Tables used

SEEREFERTTO Refer to documentation Implementation