Apply one off calculations or add live formulas to build budgets based on other accounts or driver-based budgets using Metrics.
Apply Formula
Browse to the Budgets & Actuals menu and select the range of cells that you would like to apply a formula to.
Click FORMULA or select it from the 3 dots menu on the range of selected cells.
Set the required properties and click APPLY FORMULA.
Tip: 💡to select a row or column click on the account name or column heading respectively.
Note: see Formula or Repeating Amounts headings below for details on the required properties.
Formula Indicator Icons
These icons indicate what budget cells contain a formula. The icon on the top row indicates a one-off calculation while the icon on the bottom row indicates a live budget formula.
Formula
Formulas allow you to do account based calculations such as "Sales x 40%" to calculate your Cost of Goods Sold budget or Metrics based calculations such as "Widgets Sold x Widget Price" to calculate your Sales budget.
Select Formula and choose:
ONE-OFF: calculated when you apply the formula but does not update when related values change.
or LIVE: automatically updates as the data source changes. You cannot use LIVE when using Actuals as the Data Source.
Data Source: the data source defines what data will be used in the formula calculation.
Actuals or Budget version: defaults to the current budget which you will use for most calculations, however you may wish to use Actuals or another Budget. For example starting with last years actuals or copying from one budget to another.
Business Unit: defaults to the current business unit which will be used for most calculations, however you may wish to select another business unit or multiple business units. For example to copy from another business unit or to consolidate multiple into different category.
Period: represents the period you want to use in the calculation which is relative to the current period. This defaults to the current period for most calculations however if you wanted to reference Last Years data you would enter the period as -12. You could also reference last month with -1 or 3 months ago with -3.
Formula Definition: the formula calculation consists of 2 sides with an optional operator in the middle.
Left & Right Side: you can add either Accounts, Metrics or Numbers, but only one type on each side. The right side is disabled when the operator is None.
Operation: single maths operation with options including None, plus (+), minus (-), times(x), divide(÷).Search for Accounts or Metrics and select the checkboxes to add them to the left or right side. Selecting multiple will sum them on each side before applying the Operation.
When adding a Number to the left or right side you can add numbers and simple maths operations like (133 + 99 -87) * 26 / 12. The result of this will be calculated first before applying the Operation.
Repeating Amounts
The repeating formula option allows you to set budgets as repeating fixed amounts. For example Wages paid fortnightly or Rent paid weekly where each month may include a different number of payments.
Select Repeating then enter an Amount or calculation: the amount is the amount that will be repeated according to the following repeating properties. The amount can also be entered as a simple calculation like 50,000/26 which calculates a total annual amount of 50,000 divided into fortnightly amounts.
Enter Start date: choose the first date the repeating amount will be entered.
Set the Repeat every properties:
x number of Days: the amount will repeat every x days starting on the Start Date.
or
x number of Weeks on days of the week: the amount will repeat every x weeks on the specified days of the week starting on the Start Date.
Note: the above example will repeat 1923.08 every 2 weeks on Fridays starting the 3rd of July 2020.