Calculating a Budget Using a Formula

How to Set Up a Formula | Common Budget Formulas | Calculating the Budget from a Formula | Full List of Budget Formulas

How to Set Up 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:

Common Budget Formulas

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

Enter a percentage and the source G/L 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 and service), or percent of the payroll expense budget. These formula options total general ledger accounts based on the classification of the G/L 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 Chart of Accounts Account Classification section within the main documentation for more details on setting the correct classification for each account.

The % of sales and 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 and service income budget for department __ formula is useful when 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:

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 Calculation entry before proceeding. Click on the Calculate button to update the budget values in the Year Total and monthly budget calculation.

There are several valid formats to enter account IDs for calculation formulas that call for account(s).

Enter a single account ID.
Enter 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.
Enter several account IDs separated by commas in this format: 01000-000,02000-000,03000-000. Note: make sure there are no spaces between IDs.
Enter the first two G/L digits to pull in all account IDs that start with those numbers. For example, account(s): 65 would pull in 65000-010, 65000-020, 65500-000, 65900-000, etc.

Calculating the Budget from a Formula

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.

Note: Budget 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.

Complete Budget Formula List

A complete list of formula options in the Total Calculation list, including summaries and technical details (in italics):

Manual

Selecting this option allows you to set each month's budget manually.

Is $___

This formula calculates a budget based on a set dollar amount. It sets either the year total (when 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 actual for previous year

This formula sets the budget to a set percentage of last year's budget. 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 year-end adjustment in glbgt.dbf. Multiplies all of them by the percentage.

__% of actual for previous year account(s): ____

This formula sets the budget based on a set percentage of last year's budget for a specified G/L account or selected range of accounts. 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 actual for this year

This formula sets the budget based on a set percentage from the current year's budget. 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 actual for this year account(s): ____

This formula sets a budget based on a set percentage from the current year's budget for a specified G/L account or selected range of accounts. 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): ____

One of the most frequently used budget formulas, which calculates a budget based on a set percentage of a specified G/L account or selected range of accounts. 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 year-end 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

This formula calculates budget based on a range of accounts in 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 budget for previous year

This formula calculates a budget based on a set percentage of last year's budget.

__% of sales and service income budget

This formula calculates budget based on a set percentage of the sales and service income budget. Sums the values of the months for every budget in this year for an account whose classification begins with 'ARS'. Multiplies by the percentage.

___% of total payroll budget

This formula calculates budget based on a set percentage of the 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

This formula calculates budget based on a set percentage of the 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 and service income budget for department ____

This formula calculates budget based on a set percentage of the sales and service income budget for a specified 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 ____

This formula calculates budget based on a set percentage of the payroll budget for a specified 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 ____

This formula calculates budget based on a set percentage of the sales budget for a specified 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 and service income budget for profit center ____

This formula calculates budget based on a set percentage of the sales and service income budget for a specified profit 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 profit center ____

This formula calculates budget based on a set percentage of the payroll budget for a specified profit center. 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 has a glccdet record for the given cost center in the current year. Multiplies by the percentage.

__% of cost of sales budget for profit center ____

This formula calculates budget based on a set percentage of the sales budget for a specified profit center. 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 has a glccdet record for the given cost center in the current year. Multiplies by the percentage.

Excel Sheet: ____ Cell: ____ File: ____

Many financial managers are accustomed to generating a budget using a spreadsheet. EBMS allows the user to import budget values into the EBMS budget directly from a MS Excel spreadsheet. Review this article on Connecting the Budget to a Spreadsheet for information on this option. Opens the given Excel file and looks up the value on the sheet with the given name in the given cell. Sets each month equal to that value. Sets the year-end adjustment to 0.