Using Budget Formulas open up many possibilities to save you time. Here’s a few examples that should inspire you. Some are relatively simple, some more complex. Calxa’s powerful Budget Formulas work across business units and can be built up in layers using Metrics.
Formulas work just the same whether you are calculating budgets or scenarios. As a result, consider when they can be useful to save you time with updates.
7 Ideas for Budget Formulas
Here are our suggestions for you to try out.
1. Cost of Goods Sold (COGS) as a Percentage of Sales
Our first example of a useful budget formula is a simple one but one that many people need. For some businesses, the cost of sales component is a set percentage of their goods sold. This is true for sectors like manufacturing where your production cost is mostly fixed. Here is how you set this up in your budgets.
- Click the account name for the account you want to set. This selects the whole date range you have displayed.
- Click Formula
- Select the relevant income account (or accounts if you have more than one). You can search for them and you can select a header account if your accounting system includes them.
- Select times as the operator
- Select number and then enter your percentage as a decimal fraction (0.4 for 40%). Now set the formula to a Live recalculation.
- Apply the formula
Now, for those of you with multiple business units, we can apply this formula to all the business units in one go:
1. Click on Layout and change it to Business Unit.
2. In the left side panel, select your COGS account.
3. You’ll see that account now for all your business units.
4. Select all the rows and click Formula.
5. Enter the formula as above (or just apply it if you’ve already entered it for one business unit – this will copy it).
6. Done!
2. Calculate Increasing Sales with Budget Formulas
Sometimes you want to budget for a sales line as a percentage increase on the previous year, sometimes on the previous month. This same principle works if you want a fixed amount increase. So here is the budget formula you can set up.
- Select the account(s) you want to change and then Formula
- On the left side of the formula choose Selected Accounts and it will select all the accounts you have chosen.
- Set times (or plus) as the operator
- On the right side choose Number and enter your increase (1.05 for a 5% increase)
- Change the Period to -12 for the same month a year earlier, -1 for the previous month.
To extend the logic of this formula, what if you want to base it on Actuals for past periods but budgets for the future (where you don’t have actuals)? Let’s do that step by step:
1. Run the Budget Factory setting the source as your current year actuals and the destination as a new budget version called Actuals + Forecast or something like that.
2. Modify the formula above and change the Data Source from Current Forecast to your Actuals + Forecast.
3. Each month, after you’ve finalised your accounting checks, run the Budget Factory again to update the Actuals + Forecast with the next month’s actuals.
3. Fortnightly Salaries
If your salaries are the same every week or fortnight, it makes sense to use a repeating budget formula to enter them. This will calculate how many pay periods are in each month so that the actuals will match your budget.
- Select your account and select Formula
- Choose Repeating
- Enter the gross pay for each period and the start date
- Then enter how often to repeat this.
- If you are anticipating pay rises mid-year, don’t select the full year. Select the first few months and set the formula for them, then repeat the process with the new rate for the second period.
4. Wages Based on Working Days
The previous example is probably too simple for most organisations. What if you have a team of 4 who each work 2, 3, 4 and 5 days a week and get paid different daily rates? For this budget formula example, we’ll use Metrics and nest them to get to our final result. Take it step by step and you will be able to follow.
- Switch the layout to Metrics/Month
- Add a Metric for each person’s working days and another for the daily rates and then one for each person’s monthly pay.
- Set the Formula for each of the working days to an amount of 1, repeat every 1 week and specify which days each of them work:
- For the daily rates, you can just enter the amounts, but you can also use a one-sided formula.
- Note that you can enter a calculation in the Number (for example, number of hours x hourly rate).
- If the rate is expected to change during the year, set the rate accordingly.
- Add a formula to the Monthly Pay metrics that calculates the working days times the relevant rate. Then set the formula to a Live recalculation:
- You should end up with a set of Metrics like this:
- Now, change the layout back to Accounts/Month and select your wages account
- Set the budget formula to just select each of the Monthly Pay metrics. The formula will sum the values selected. Again, set the formula to a Live recalculation.
If watching videos is your preferred learning method, see this video tip we did on payroll budgets.
5. Budget Formulas to add Australian Payroll Tax
Now, Payroll Tax is different in Australia to the US. It is a tax paid by employers based on the total payroll value. To complicate matters, the exact rules vary from state to state. but, they all have a threshold below which the tax isn’t payable. Where they differ is that some charge the tax only on the amount above the threshold, others on the whole amount. So here it goes:
- Because we have a multi-part calculation, switch the layout to Metrics to do the initial steps
- Create a Metric for Payroll Tax Threshold and, if you only pay tax on the amount over the threshold, one for Payroll Over Threshold.
- Set the Payroll Tax Threshold budget to the monthly amount of the threshold.
- Set the Payroll Over Threshold to Wages – Payroll Tax Threshold (note that Metrics can refer to budget amounts as well as vice versa).
- Best practice would be to use a Metric for the payroll tax rate as well. That will make it easier to adjust if that’s ever necessary.
- Now switch back to the Accounts layout, find your Payroll Tax account and set the formula as Payroll over Threshold times the Payroll Tax Rate.
6. Accruals and Prepayments
When entering accruals or prepayments, it’s important that both sides of the transaction are equal. A simple budget formula makes this easy for you.
If you have an asset account for prepaid rent, for example, in your accounts you would debit rent and credit the prepayment account each month. Replicate this in the budget by setting the prepayment account as the rent budget times -1.
For a discussion on how to manage accruals and prepayments for cashflow forecasts, see our help note on Income in Advance.
7. Copying Budget Formulas
One final tip on copying formulas as not everyone finds it obvious. The trick is to select a range that includes one month with the formula and then you can apply it across the whole row. So, for example, if you have formulas in your budgets for January to December 2024 and you want to copy them to January to December 2025, follow these steps:
- Select the date range for your budget as December 2024 to December 2025 (13 months).
- Select the row you want by clicking on the account name.
- Click Formula and it will display the formula you’ve previously entered in December 2021.
- Apply the formula and it will extend it across the whole row.
- Alternately, select the December 2024 cell in any row and on the 3 dots below, choose Copy to Future. You can use this to copy multiple rows if they are all together.
Budget Formulas Ready for You to Try
These 7 examples of clever things to do with budget formulas should give you a good idea of the possibilities. Let us know if you want to share your ideas. Even more so, if there’s something you want to do but can’t think of a way to do it. We will do our best to help you.
To get started with Budgeting in Calxa, have a look at our articles 5 Ways of Budgeting Made Easy, Building Budgets Back to Front and Budgeting Tips for Not-For-Profits.