10 Excel Formulas We Use in Budget Creation

At AG Capital CFO Services, we create practically all financial models in Microsoft Excel, because it provides great flexibility in work and is understandable to every client. MS Excel is the most popular spreadsheet program in the world; it is used both by beginners to perform simple operations or to keep track of data for their purposes as well as by advanced everyday work with large data flows. In this article, we’ve summarized the most important and most commonly used Excel formulas for creating budget models.

1. IF function.

You have probably already heard about the IF function – it is possible to create various logical conditions with it (what will happen if we do action X?). For example, = IF (A2 = “AG Capital”; 1; 2) means: if “AG Capital” is entered in cell A2, then Excel will return 1; otherwise 2.

In our budget models, we use the IF function, for example, to count account receivables and payables on a monthly basis, as they depend on the pre-and post-payment days. In addition, the IF function is useful for formatting the model, e.g. removing unnecessary data.

2. COUNTIF function.

The COUNTIF function counts cells in a range that meets a user-selected criterion. COUNTIF can be used to find out, for example, how many times people who have sold more than 30 products a month appear on your employee list. Microsoft Excel provides a simple example of how to understand a formula: COUNTIF (Where do you want to look?, What do you want to look for?).

In budget modeling, we use the COUNTIF function, for example, to count how many stores have opened or how many products have been sold in the current month instead of altogether.

3. IFERROR function.

The IFERROR function is mainly used to format models. The function looks for errors and, if found, returns the value specified by the user. The IFERROR syntax looks like this: IFERROR (value, value_if_error), and the function checks for the following errors: # N / A, #VALUE !, #REF !, # DIV / 0 !, #NUM !, #NAME? or #NULL !.

The most common use of the IFERROR function in our budget models is the control of blank fields – oftentimes data value in a cell is 0 (e.g. new shops in October) and if you would have to divide by such a number, Excel would return an error # DIV / 0! (divided by 0). IFERROR formula eliminates this error and the model is easier to read.

Nested functions.

Nested functions are most easily understood by thinking of them as “function in a function”. They allow several criteria to be tested and the number of possible outcomes to increase. For example, to assign a letter to certain numbers, you can use the function = IF (A3> 89, “A”, IF (A3> 79, “B”, IF (A3> 69, “C”, IF (A3> 59, ” D ”,“ F ”)))) – if the number in cell A3 is greater than 89, it will be assigned the letter A, if greater than 69 – the letter C, if greater than 59 – the letter D, and in all other cases it will be assessed with F.

In our budget models, nested IF functions are found, for example, in forecasting volume-dependent revenues. If the volume is X, then the price is A; if the volume increases and is Y, then the price is B – we record it all with the help of the Nested IF function.

4. SUM function.

This function is the simplest, most useful, and also the most widely used. It works just like a basic calculator and instantly adds values.

The SUM function is quite often used in our budget models to sum up the data of individual structural units so that our client can see the main thing – an overview. It is used to calculate, for example, total income, expenses, inventories, purchases and other items.

5. SUMPRODUCT function.

SUMPRODUCT can be used to multiply two columns or rows and then sum up this result. For example, to calculate total sales, you must first multiply the price by sales and only then add them up. The SUMPRODUCT function allows you to do this in one step with the following formula = SUMPRODUCT (array1, [array2], [array3],…). In its simplest form, an array means a range of cells, such as C6:C14.

We use the SUMPRODUCT function to calculate the total turnover depending on several products or services.

6. SUMIF function.

The SUMIF function is used to quickly and easily count only the values that meet a specified criterion. For example, if you want to count only values greater than 100 in a column, use the following formula: SUMIF (D3: D30, “> 100”).

In our case, the SUMIF function is used, for example, to immediately calculate the turnover/costs of certain structural units from the accounting data in the budget file (e.g. count the turnover for all months of the year only for the store located in the shopping center “Alfa”.

7. VLOOKUP function

VLOOKUP (/ HLOOKUP) is used to quickly and easily find data in a table, such as to quickly find an employee’s sales volume based on their ID number. The syntax of the function is as follows: = VLOOKUP (what to look for, where to look, answer column number from the selected range).

However, this function is easier to understand with a clear example:

Microsoft recommends that you use the XLOOKUP feature, which is a new and improved version of VLOOKUP because it works in any direction and is easier to use because there is no need to specify a column number:

8. INDEX/MATCH functions

When using the VLOOKUP function, you may encounter some limitations – the value can be looked up only from left to right, or the first reference column must be specific for the function to work. That’s why the INDEX / MATCH function has been developed, which works in a very similar way:

We use VLOOKUP (HLOOKUP), XLOOKUP, and INDEX / MATCH functions in budget models to accurately select data when a specific number/calculation needs to be provided, or as an additional activity before performing other operations.

9. MIN/MAX/AVERAGE functions

The MIN function in Excel will return the smallest number from a user-defined range, while the MAX function will return the largest number from a user-defined range. The AVERAGE function returns the arithmetic mean of a specified range. For example, if the range B43: B60 in an Excel sheet contains numbers, the formula = AVERAGE (B43: B60) will count all the numbers in that range and divide their sum by the number of values.

In our budget models, the MIN, MAX, and AVERAGE functions are used to display average, minimum, and maximum costs, revenue, number of stores, number of employees, and more. These figures give a quick overview of the most important positions before going into detail. The AVERAGE function is used to calculate the average gross salaries in the company, while the MIN and MAX functions are also widely used in the motivation systems when the employee receives a bonus if a certain amount of sales can be achieved.

10. ADDRESS & INDIRECT functions

The ADDRESS function is used to obtain the address of a cell in Excel if its row and column numbers are known. It can also find the address of cells from other sheets. With INDIRECT, this address can be converted to content by displaying a number or text or anything else in the addressed cell. The INDIRECT formula serves as a reference to other cells or worksheets. This is useful if it is not possible or desirable to change the formula, but the reference to the cell must be changed for any reason.

In our budget models, these functions are often combined with other formulas to make it easier to use the budget file when different scenarios and data are in different worksheets.

Of course, the list is not exhaustive and we make use of many other Excel functions to create the budget models, such as referencing one cell in another page or combining many formulas – sometimes, even 3-5 formulas can be used in a single cell. In the end, budget models consist of about 16 to 24 pages filled with both simple and complex formulas.

If you want to know more about each of the described formulas, click here: