When it Comes to Budgeting, Measure Twice and Cut Once

On the plant floor, you'd do your due diligence before irrevocably cutting metal — this applies equally to designing your budget templates and budget model.

Table of Content

    It makes sense on the plant floor – do your due diligence before irrevocably cutting metal. It applies equally to a set of budget templates. Whether you’re using straight up Excel, an Excel based budget application, or a separate budget application, your budget model is likely to consist of a series of budget templates. And, depending on your business model, they can get quite complex. However, that complexity is a good thing.

    In designing a budget model, you should be striving for several goals:

    1. It should be flexible, handling numerous variables such as product mix, labor mix, etc.

    2. It should be repeatable. Unless your business fundamentally changes, you should be able to use the same template next year.

    3. Data entry by end users should be simple and straightforward.

    In order to achieve these goals, which at times can be conflicting, be prepared to invest your time at the beginning of the project. Your spreadsheets will likely be complex to in order to achieve the first two goals above, but you should be shooting for the end user to have to enter just two or three drivers and have everything else flow automatically.

    What does this mean? Let us take one universal type of expense as an example.

    Virtually every enterprise is subject to FICA tax on its employees’ pay. The tax is based on a rate, which theoretically could, but generally doesn’t change from one year to the next, and a maximum tax base, which almost always changes from year to year. The simplest way to handle this is to build a formula with the budget year maximum, but then next year you would have to remember to change it and find all the calculations dependent upon it.

    You would be much better off in the long run building a model where the maximum, and for that matter, the rate, are saved independently of the template and are included on the budget template by reference, eliminating the need to change the budget template from year to year. It would take more time in the initial design but save you time every year thereafter.

    There are myriad areas where that approach makes a lot of sense. Virtually any employee benefit, for example. Margin by product, days in the month, etc. Any variable whose value changes from year to year but whose use in calculations doesn’t change. Divorce the values from how those values are used. Yes, it’s more work when first creating your budget model, but it will save you time, and prevent errors, in future years.

    How Will this Budget Model Benefit My Business?

    If your business changes from making widgets to being a government contractor, fundamentally changing your budget model may be unavoidable. But your budget model should seamlessly handle a new line of widgets, and will if you invest the time up front.

    If you have questions about optimizing your budget templates or model, or need help creating one, send an email to our expert consultants.

    Analytics, Business Intelligence & Workflows