Calculating a Budget Using a Formula

Budget amounts can be calculated using a formula instead of manually setting the value of each general ledger account. Open the budget list by selecting Financials > Budget from the mail EBMS menu.

It is easier to manage and test formulas within the general ledger budget dialog. Double click on any account budget record to view the budget settings for each general ledger account as shown below:

Change the Total Calculation setting from Manual to a formula by clicking on the template option button. Select one of the following Total Calculation formula options from the template list:

Formulas

The most common budget formula is the __% of budget for account(s):________ as highlighted above.

Enter a percentage and the source general ledger accounts into the formula template as shown below:

$image\ebx_-424800090.jpg$

In the example shown above, the budget for the purchase (cost of sale) account of 60000-010 will be calculated based on 60% of the projected sales within the 51000-010 revenue account. If the sales account budget is changed, the budget for the purchase account will be recalculated using the percentage calculation.

You can also add multiple accounts by placing a comma between the accounts as shown below.
$image\ebx_-1928753672.jpg$

The account setting can have a partial account code. The following settings will calculate 60% of the budget for all accounts starting with 51: $image\ebx_-1637763108.jpg$

Multiple partial account codes can be separated with a comma. The following settings would include all 51000 and 52000 accounts no matter what extension. $image\ebx_817281388.jpg$

A range of accounts can be used by using the to conjunction. The following setting would include all accounts in the range of 51000-010 through 51000-030 $image\ebx_-1827257014.jpg$

A combination of account settings can be used as shown below:
$image\ebx_1583960155.jpg$

Another common formula used to create expense budgets is basing the budget on the percent of sales, percent of all income (Sales & Server), or percent of the payroll expense budget. These formula options total general ledger accounts based on the classification of the GL account. For example the following formula will total the budget for all revenue general ledger accounts that are classified as Sales or Service but excludes accounts that are classified as Other Income, Sale of Assets, or other miscellaneous income. The Classification setting is found within the Advanced tab of the general ledger account. Review the Financials > Chart of Accounts > Account Classification section within the main documentation for more details on setting the classification of the account.

The % of sales & service income budget formula is often used to calculate overhead expense budgets by percent of sales. The following example sets the advertising budget to equal 3.5% of total income and service income. Miscellaneous income would not be included. $image\ebx_-681758790.jpg$

Calculate payroll overhead costs such as company payroll taxes using the following formula:
$image\ebx_-1440594773.jpg$

The following formula is similar to the previous formulas but limits the accounts to those within the department or profit center.

The __% of sales & service income budget for department __. Formula is useful of the purchase amount is a percentage of all revenue accounts for a specific department. This is a common way to calculate cost of sales within a department.

$image\ebx_-886234235.jpg$

Some other common formulas:

Or calculate payroll overhead costs for a specific department by using the following formula:

Notice that the general ledger accounts, department, or profit center settings are not validated. It is important that the user tests the Total Calculate entry before proceeding. Click on the Calculate button to update the budget values in the Year Total and monthly budget calculation.

A complete list of formula options including technical details are listed below:

Formula	Output
Manual.	Allows you to set each month manually.
Is $___.	Will set either the Yearly Total (calculated on a Yearly Basis) or each monthly value (Monthly Basis) to the value entered within the formula.
Variable formula.	Review the Using Variables section for more details on this formula option.
___% of last year.	Tries to find a record in glleddet.dbf for this account and the previous year. Copies each field such that BAL_i in gleddet.dbf becomes BUDGET_i in glbgt.dbf. Copies BAL_13 in gleddet.dbf to the yearend adjustment in glbgt.dbf. Multiplies all of them by the percentage.
___ % of last year account(s): __________.	Looks up each record in gleddet.dbf for the previous year and certain accounts that depend on what is passed in. Sums the values of BAL_i in gleddet.dbf to BUDGET_i in glbgt.dbf. Sums the values of BAL_13 in gleddet.dbf to the yearend adjustment in glbgt.dbf. See below for accounts syntax.
___% of this year.	Tries to find a record in glleddet.dbf for this account and the current year. Copies each field such that BAL_i in gleddet.dbf becomes BUDGET_i in glbgt.dbf. Copies BAL_13 in gleddet.dbf to the yearend adjustment in glbgt.dbf. Multiplies all of them by the percentage.
___ % of this year account(s): __________.	Looks up each record in gleddet.dbf for the current year and certain accounts that depend on what is passed in. Sums the values of BAL_i in gleddet.dbf to BUDGET_i in glbgt.dbf. Sums the values of BAL_13 in gleddet.dbf to the yearend adjustment in glbgt.dbf. See below for accounts syntax.
___% of budget for account(s): __________.	Looks up records in glbgt.dbf for this year and certain accounts that depend on what is passed in. There are three possible formats for the range of accounts. First, you can pass a single account. In this case, the months and yearend adjustment are copied from it. Second, you can pass a range of accounts in the form '00000-000to9999-000' with no spaces. In this case, the values are summed from each account within the range inclusively. Third, you can pass several accounts separated by commas in the form '01000-000,02000-000,03000-000' with no spaces. The end result is multiplied by the percentage.
___% of budget for accounts _____ to _____ with the same department.	Sums the values of the months for every budget within the range inclusively that has the same department as the account you are calculating for and the same year. Multiplies by the percentage.
___% of sales & service income budget.	Sums the values of the months for every budget in this year for an account who's classification begins with 'ARS'. Multiplies by the percentage.
___% of total payroll budget.	Sums the values of the months for every budget in this year for an account whose classification begins with 'PYL' or is 'PYBE'. Multiplies by the percentage.
___% of cost of sales budget.	Sums the values of the months for every budget in this year for an account whose classification begins with 'APP' or is 'APCS'. Multiplies by the percentage.
___% of sales && service income budget for department ___.	Sums the values of the months for every budget in this year for an account whose classification begins with 'ARS' and which is in the given department. Multiplies by the percentage.
___% of total payroll budget for department ___.	Sums the values of the months for every budget in this year for an account whose classification begins with 'PYL' or is 'PYBE' and which is in the given department. Multiplies by the percentage.
___% of cost of sales budget for department ___.	Sums the values of the months for every budget in this year for an account whose classification begins with 'APP' or is 'APCS' and which is in the given department. Multiplies by the percentage.
___% of sales && service income budget for cost center ___.	Sums the values of the months for every budget in this year for an account whose classification begins with 'ARS' and which has a glccdet record for the given cost center in the current year. Multiplies by the percentage.
___% of total payroll budget for cost center ___.	Sums the values of the months for every budget in this year for an account who's classification begins with 'PYL' or is 'PYBE' and which has a glccdet record for the given cost center in the current year. Multiplies by the percentage.
___% of cost of sales budget for cost center ___.	Sums the values of the months for every budget in this year for an account who's classification begins with 'APP' or is 'APCS' and which has a glccdet record for the given cost center in the current year. Multiplies by the percentage.
Excel sheet:______ column:___ row:____ file:__________	Opens the given file and looks up the value on the sheet with the given name in the given row and column numbers. Sets each month equal to that value. Sets the yearend adjustment to 0. Review the Connecting the Budget to a Spreadsheet section for more details.

The budget values are not updated without clicking the Calculate button on the budget dialog or clicking on the Recalculate button on the budget list.

Budgets values are not recalculated when a formula is changed from a calculation to the Manual option.

The budget for all the accounts can be updated by clicking on the Recalculate button within the Financials > Budget dialog as shown below:

Review the Using Variables section for more complicated formulas to calculate a budget.

Continue with the following section: Change a Range of Budget Accounts to set the same formula for a range of accounts.