Calculating a Budget Using a Formula

Full List of Budget Formulas

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:

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.

The account setting can have a partial account code. The following settings will calculate 60% of the budget for all accounts starting with 51:

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

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:

A combination of account settings can be used as shown below:

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.

Calculate payroll overhead costs such as company payroll taxes using the following formula:

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.

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):