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:

 

 

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

7 comments:

  1. Col Delane
    Reply

    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.

    1. Jeff Lenning Post author
      Reply

      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
    Reply

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

    1. Jeff Lenning Post author
      Reply

      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

      1. Daniel Spencer
        Reply

        Thanks Jeff, perfect answer

        1. Jeff Lenning Post author
          Reply

          🙂

  3. Jerry Cooper CMA
    Reply

    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 Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.