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:

Using Excel to budget faster, easier

For a sample file:

 

 

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

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?

Our training programs start at $29 and will help you learn Excel quickly.

7 Comments

  1. Col Delane on May 19, 2020 at 11:20 pm

    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.

    • Jeff Lenning on May 20, 2020 at 8:17 am

      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

  2. Daniel Spencer on May 20, 2020 at 3:39 am

    Thanks for this, I just struggling to see where I can change the calculations when you select one of the drop down options.

    • Jeff Lenning on May 20, 2020 at 8:34 am

      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

      • Daniel Spencer on May 20, 2020 at 9:17 am

        Thanks Jeff, perfect answer

        • Jeff Lenning on May 20, 2020 at 9:18 am

          🙂

  3. Jerry Cooper CMA on May 21, 2020 at 10:10 pm

    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!

Leave a Comment