Concept: Formulas
Formulas let you:
- Automate calculations.
- Build values from drivers.
- Create complex interrelations among accounts.
In formula expressions, use commas to separate the terms of the expression. If your browser uses commas as decimal points, use semicolons to separate terms.
Usage Examples
Example Formula | Desciption |
|---|---|
DIV(ACCT.CostOfGoodsSold, ACCT.Inventory)
With commas as decimal points: DIV(ACCT.CostOf GoodsSold; ACCT.Invetory) | Calculates inventory turnover ratio by dividing the cost of goods sold by the average inventory. |
IF(this.Year.PositionOf(this.Month)=1,ROW.ExpectedRaise,0)
With commas as decimal points: IF(this.Year.PositionOf(this.Month)=1;ROW.ExpectedRaise,0) | Applies planned raises, but only in the first month of the fiscal year. |
DIV(ACCT.Sales[ProductType=ServiceContract], ACCT.Sales[ProductType=Vehicle]) * 100
With commas as decimal points: DIV(ACCT.Sales[ProductType=ServiceContract]; ACCT.Sales[ProductType=Vehicle]) * 100 | Calculates the sales of service contracts as a percentage of sales of vehicles. |
Design Philosophy
You create formulas that represent the realities of your business in all its complexity.
You can write formulas once and apply them anywhere in your model wherever and whenever it makes sense whether during the building of the model, the planning stage, reporting and analysis.
In the planning model:
- Formula building block are descriptive. By leveraging a standard, consistent set of building blocks (account terms, mathematical functions, logical constructions, time references, etc.) you can create flexible, robust combinations that are easy to read and understand.
- Formula Assistant is available to help you pick the correct building blocks and combine them using the correct formula syntax.
- Cell Explorer makes it easy to track where the data is coming from, explore formulas in your model, and troubleshoot issues if they occur.
- Administrators can define global formulas that work across versions, apply to multiple accounts. Anyone who enters budget numbers can create simple formulas for their own use to represent the numbers they are entering in sheets or use formulas to calculate data in reports.