Concept: Using Adaptive Planning Formulas

Formulas enable you to:

Save time by automating calculations in your model.

Build values from drivers.

Create complex interrelations among accounts.

Formula Assistant is available in all the places that you can enter formulas. We recommend that use Formula Assistant to avoid syntax errors.

Different Places to Use Formulas

We offer different ways to use formulas in your model:

Ad hoc in sheets: Enter formulas in the cells of sheets to create ad hoc calculations similar to Excel. See Concept: Typing Basic Formulas in Sheet Cells.

Calculated accounts: Add formulas to accounts to calculate across levels. As an option, you can create different formulas per version. See Concept: Calculated Accounts.

Shared formulas: Shared formulas work as calculated suggestions for accounts. You can create a different formula for each version and level. See Create Shared Formulas.

Report formulas: You can add formulas to reports to calculate the report values. Example: You can calculate totals from accounts that aren’t in the same hierarchy. See Concept: Matrix Report Calculations.

You might prefer to use links instead of formulas as a way of moving and combining data. To compare and contrast calculated accounts, shared formulas, and links, see Reference: Linked Accounts Compared to Shared Formulas and Calculated Accounts.

Common Usage Examples

Goal	Formula Example	Formula Description
Calculate inventory turnover.	Div (ACCT.5500, ACCT.1150) 5500: Account code for Cost of Goods Sold. 1150: Account code for Inventory.	Divides the cost of goods sold by inventory.
Calculate raises on the first month of the fiscal year for each employee on the Personnel sheet.	Iff (this.Year.PositionOf(this.Month) = 1, ROW.ExpectedRaise, 0)	If it's the 1st month of the year, apply the value of the Expected Raise account to this row. Otherwise, return a 0.
Calculate the percent of sales of service contracts to sales vehicles.	Divf (ACCT.Sales[ProductType = ServiceContract], ACCT.Sales[ProductType = Vehicle]) * 100 Product Type is a custom dimension.	Divide the data tagged with Service Contract by the data tagged with Vehicle. Then, multiply by 100.