Excel and Budgeting
I recently wrote an article for the Minnesota Society of CPAs about a technique that helps you create budgets faster. Even if you don’t prepare budgets, the underlying features and functions used can be applied elsewhere. In summary, we use a data validation drop-down to provide several Budget Methods. Once you select a budget method for a specific account, we use CHOOSE and MATCH to compute the corresponding amounts.
Check out the full article here:
For a sample file:
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.
Hi Jeff
1. I notice that the sample workbook doesn’t include Manual as one of the method options, which IMHO cannot be done with what you’ve proposed as the user would either have to overwrite the Choose/Match formulas in the Budget Year cells, or you need to add a separate input table for manually entered values (which are very often needed for overheads such as marketing, legal, etc. that are very chunky and irregular) to which the Choose/Match formulas for Manual would reference.
2. Nevertheless, I’m glad you posted this article because I’ve wanted to use Choose for text string options on various projects recently but never figured out (now to my shame as I ought to have been able to!) how to convert the string to a integer for Choose’s Index_num argument.
PS: As the Captcha window is not displayed until later, you need to add a Checkbox next to the “I agree to terms” hyperlink to make it explicit that users MUST click it before their comment will be submitted.
Hi Col,
Regarding the Manual budget method, this would be a separate manual input worksheet. Depending on the complexity of it, we could use a direct cell reference or a function such as VLOOKUP, XLOOKUP or SUMIFS to return the manually entered budget values.
Thanks
Jeff
Thanks for this, I just struggling to see where I can change the calculations when you select one of the drop down options.
Hi Daniel,
To change the calculations, update the arguments in the CHOOSE function. For example, if you want to change the way the first budget method Spread PY computes values, perhaps changing it from quarterly to monthly, change the first value argument from $H8/4 to $H8/12. Hope this helps!
Thanks
Jeff
Thanks Jeff, perfect answer
🙂
This is great, thanks Jeff. Reminds me of a budget model I developed years ago, and never got to the point of CHOOSE / MATCH for Budget Method as you have efficiently demonstrated here. Maybe mine even predated those functions. At any rate, it’s so much better when you can fill the same formula from top to bottom, and let your Budget Method handle the formula switch!