Formulas provide broad, flexible, powerful capabilities that let you:
- Automate calculations.
- Build values from drivers.
- Create complex interrelations among accounts.
This formula calculates an inventory turnover ratio by dividing the cost of goods sold by the average inventory.
This more complex formula applies an employee's planned raise, but only in the first month of the fiscal year.
This formula calculates the sales of service contracts as a percentage of sales of vehicles.
DIV(ACCT.Sales[ProductType=ServiceContract], ACCT.Sales[ProductType=Vehicle]) * 100
See Formula Examples: Overview for more.
You create formulas that represent the realities of your business in all its complexity.
Formulas come with extensive business logic and functions. They are easy to write, explain, and maintain. 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.
When used for planning, formulas often have significant tradeoffs to manage:
- Excel provides formulas that are available for anyone to use during data entry. Formula in Excel reference cell letters and numbers, rather than descriptive terms. Writing formulas can break when adding rows or columns. Errors can be hard to find. Excel formulas are often hard to follow, making it difficult to determine if your model is broken.
- Most planning solutions provide better guidance than Excel when creating formulas. They reference descriptive names for formula building blocks, or provide syntax guidance. These solutions restrict who can create formulas. You must rely on formula administrators or programmers with a specific set of permissions to do the work.
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 creation is not restricted to a small subset of users. 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.