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.
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%)
- Apply the formula
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.
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:
- 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.
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. A Department as the Sum of Several Projects
Many of our customers use the 2 Tracking Categories in Xero for Departments and Projects. One department is usually the sum of a group of projects. For reporting purposes, it’s generally easiest to ignore the departments in Calxa except to just create a Business Unit Selection to consolidate the group of projects for reporting.
However, some of the reports, like the Business Unit Income and Expense Summary or the Business Unit Comparison reports, don’t have any way to showing multiple groups of projects (but watch this space!). The solution is to set the Department budget to be the sum of the relevant projects.
- Open the department budget and select all accounts (Ctrl-A is the shortcut).
- Change the data source to Project and then select the relevant projects (you can search to filter the list and then use Bulk Actions to select all).
- Set the left side of the calculation to Selected Accounts. Each account will then sum the same account from each project.
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.