Home > Report Designer > The Interface > Layout Designer Interface > Calculated Fields > Formula Builder
Formula Builder
If you need to edit, add or delete calculated fields, you need to open the formula builder.
- Clear All button – clears all fields from the My Formula area
- Standard Items – these are standard items that have been created and any new items you create will appear here.
- Function Items – includes your addition, subtraction, multiply, divide and other functions.
- Scroll bar – scrolls between all the saved standard items.
- Add Value button – allows you to add a value in the formula you create. For example calculating GP%. You would need to include a value to build this formula ( GP/Sales)*100
- Save – will save the formula you create. You will then need to name this formula? The formula will be saved and will appear as a button in the calculated field’s area of your Report Designer Layout Interface.
- The IS Percentage check box will convert to a % if required.
The IS Variance Check box will change signs of variances figures as per accounting practices. (see below for more detail)
- Cancel - will close the formula builder.
See example below: Actual Mar + Actual April + Actual May = Actual Quarter 1
How does the variance button work in the formula builder?
- The variance button caters for standard accounting calculations. The Variance calculation is based on the Account Type.
Variance Button Explained
- If you did $100 worth of Sales and your budget is $50 then the Variance is $50 and is a positive value.
- If your Cost of sales is $100 and your budget is $50 then the Variance is $50 and is calculated as positive, but should in fact be negative as you are $50 over budget.
- The IS Variance calculation button caters for this. The variance calculation is based on account type
Actual | Budget | Variance | |
Sales | 100 | 50 | 50 |
Cost of Sales | 100 | 50 | 50 |
When IS Variance is checked. The sign control is taken care of and delivers the correct result
Actual | Budget | Variance | |
Sales | 100 | 50 | 50 |
Cost of Sales | 100 | 50 | -50 |