Using Excel to budget faster, easier
By Jeff Lenning, CPA
May 13, 2020
Microsoft Excel is always moving forward, which means you need to, as well. In this article, I’ll discuss an Excel technique that can be used to make budgeting faster and easier — a prize for anyone willing to learn it.
The overall idea is to create several budget methods and allow the user to pick one for each account. When a user selects one of the budget methods, our snazzy Excel formulas compute the corresponding Budget Year amounts based on Prior Year Actuals.
For example, consider Figure 1.
Figure 1
The left side contains Prior Year Actuals for each account. The Method column allows the user to pick a budget method for each account. Based on the selected budget method, formulas compute the Budget Year amounts.
Because you can create budget methods as needed for your organization, this approach is quite flexible. Here are a few budget method ideas to get you started:
- Spread PY — spread prior year actuals evenly for budget year.
- Match PY — match prior year actuals.
- Headcount — based on budgeted headcount.
- Manual — manually budget this account.
- Decrease — apply the defined decrease percentage.
- Zero — set budget year amounts zero.
Let’s walk through an illustration to demonstrate one way to implement this system in Excel.
Illustration
Let’s say our company has many departments. We’ll create one budget workbook for each department. Our goal is to make it easy for department managers to prepare their selling, general and administrative (SG&A) expense budgets.
We start by listing each account and the prior year actual amounts. (This is represented by the Prior Year Actuals columns in Figure 1.)
Next, we need to make it easy to select a budget method for each account. This is illustrated by the Method column shown in Figure 1. I’ll provide several Budget Method examples, but feel free to tailor them to your own requirements.
Budget methods
We create a list of the budget methods we would like to offer our users by entering them into a worksheet, like this:
Figure 2
To create the budget method dropdown, we select the cells in the Method column (shown in Figure 1) and use the Data Validation feature. Select Data > Data Validation. We want to Allow a List, and the Source is the list of budget methods. This creates a dropdown for each cell in the selected Method column.
Now that the user can easily select a budget method from a dropdown, we need to create the formulas that compute the budget year values (represented by the Budget Year columns in Figure 1). Let’s start by discussing the CHOOSE function.
CHOOSE
The CHOOSE function performs a calculation depending on its first argument. Officially, it looks like this:
=CHOOSE(index_num, value1, value2, …)
If you set the index_num argument to 1, then the value1 argument is returned. If index_num is 2, then value2 is returned, and so on. But here is the key: the value arguments can be expressions. An expression is any type of calculation. For example, you could use value arguments such as G10/4, G10*0.9, or just 0.
Here is how we apply the CHOOSE function. We use it to perform the calculation that corresponds to the selected budget method.
For example, if the budget method is SpreadPY, the calculation will return the prior year total divided by 4 (or 12 if monthly). In Figure 1, this is illustrated by the first account, Office Supplies.
If the budget method is MatchPY, the calculation will return the prior quarter actual (or prior month actual). This is illustrated by the Trade Shows account in Figure 1.
But there is an issue we need to address. The budget methods are text values, such as SpreadPY, MatchPY and so on. However, the CHOOSE function expects the index_num argument to be a whole number, such as 1, 2 or 3. This brings us to the next function we’ll use, MATCH. It will help us convert the budget method text into a whole number.
MATCH
The MATCH function returns the relative position of an item in a list. Officially, it looks like this:
=MATCH(lookup_value, lookup_array, [match_type])
If you set the lookup_value to the selected budget method, for example SpreadPY, and you set the lookup_array to the list of budget methods (Figure 2), then using a match_type of 0 would return 1. This is because SpreadPY is the first item in the list. MatchPY would return 2 because it is the second method in the list, and so on.
We use MATCH as the index_num argument of CHOOSE, like this:
=CHOOSE(MATCH(…), value1, value2, …)
MATCH basically converts the budget method label, SpreadPY, into a whole number that is used as the first argument of CHOOSE. The CHOOSE function then returns the corresponding expression to compute the selected budget method value.
Going forward
This approach is flexible because you can create any budget methods needed. You then use the CHOOSE function to return the corresponding math.
We can apply Excel features, functions and techniques to streamline the mechanics of our budget process. Preparing the budget is now easier than ever. The only hard part is getting the department managers to stick to it!
Jeff Lenning, CPA is a Microsoft Office Certified Excel Expert and conducts live CPE training sessions across the country.
Additional resources:
