Formulas provide broad, flexible, powerful features that enable all users to automate calculations, build values from drivers, and create complex interrelations among accounts. Formulas can range from simple expressions such as sums or quotients for calculating metrics to complicated employee benefits or sales commission calculations. Adaptive's formula syntax is intended to be simple to read and maintain, to allow for straightforward editing as well as easy analysis of formula values and meaning. Even users who do basic data entry can use formulas to perform their data entry if they desire to do so, and can explore the meaning and origin of data being generated from formulas.
- 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
For additional examples, refer to Formula Examples: Overview.
Adaptive Insights gives you the tools to create formulas that represent the realities of your business in all its complexity. With extensive business logic and functions readily available, it’s easy to write, explain, and maintain complex formulas. Write them once and apply them anywhere in your model, wherever it makes sense, whether that's during the building of the model, the planning stage, reporting and analysis, or all of the above.
When used for planning, formulas often have significant tradeoffs to manage:
- Excel provides flexible and powerful formulas that are available for any user to use during data entry. However, the formula building blocks are based on cell letters and numbers, rather than descriptive terms. When you write formulas, it’s easy to make a mistake that can be hard to find, and easy for formulas to break when rows or columns are added or moved. Excel formulas are also often hard to follow, making it difficult to determine if your model is broken.
- In contrast, most planning applications generally provide better guidance than Excel for creating formulas (by allowing descriptive names for formula building blocks, or providing syntax guidance). However, these systems typically restrict which users can create formulas, forcing you to rely on formula administrators or programmers with a specific set of permissions to do the work.
At Adaptive Insights we believe:
- Formulas are too useful to be restricted only to administrator use. Many companies still use Excel for their planning, and one important reason is because you can put a formula anywhere. Even people who are just entering budget numbers often want to be able to enter simple formulas to represent the numbers they are entering.
- Formulas are too important in a model to be done without guidance. They require assistance to design and need to be easy to follow, maintain, and explore. Fragile Excel models that break when you make a small, seemingly minor change are a constant source of pain for Excel modelers.
As a consequence, formula support was designed from the beginning to combine the best features of these systems and eliminate the tradeoffs:
- Formula building blocks 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, and so on. In addition, budgeters, planners and other types of users can also easily create formulas for their own purposes, in reports or during data entry.