The Formula Builder: Modelling, KPIs & Reports

The formula builder is a powerful means of adding dynamics into your model and getting custom insights into your reporting. There are three areas of Castaway where formulas can be used:

• Modelling projections in elements
• Creating Custom KPIs
• Custom Report Formulas

There are a few key difference between these three areas of Castaway and how the Formula builder can be used in each, in summary:

Feature	Modelling	Report Formula	Custom KPI
Financial Data	Financial data limited by report area	Financial data limited by report area	All data sources
Non-Financial Data	Driver data available	Driver data available	Driver data available
Functions available	Rolling function disabled	Rolling function disabled	All available

Fundamentals of Using the Formula Builder

When you open any formula builder you are met with the same basic structure. The option in terms of function, data sources and settings vary depending on where you are building your report formula. These differences are stipulated later in this article.

The Formula Builder view for a P&L Custom Report Formula

1. Name your Formula
2. To input data into your formula, select the origin under the right-hand Add Data panel
3. Expand each section to view all the data lines, double clicking to bring it into the formula
4. Under the Forecast Data source, entire sections can be selected, also by double clicking
5. Click any of the mathematical functions to insert them into the formula
6. Bring in numerical values typing them into the Values box, or using the up and down arrows, and then confirming the value by hitting enter or clicking the green plus button
7. Remove any component by clicking the cross in its corner
8. Adjust your settings as required
9. Castaway will tell you if your Formula is mathematically cogent and Valid, click Save

Available Functions

Symbol	Function	Mechanics
	Plus, minus, multiply, divide	Place between data sources or number values
	Rolling function	Combine as shown below, this example will sum your Direct Costs from the prior 6 months
	To the power of	Combine as shown below, a simple squaring
	Parentheses	Enclose numbers and expressions, dictating that the content inside must be calculated first according to the order of operations
	Add a number	Type or use the arrows, click plus to insert

Available Settings

Symbol	Options	Mechanics
	Always, Only when positive, Only when negative	Returns a value if criterion is met
	Negate Result	Reverse sign (*-1)
	Sum, First Period, Last Period, Min. Period, Max. Period, Average, Calculate, Zero	Define how monthly values are combined in quarter and year views
	Percent, Negated	Define value as a percentage Reverse sign (*-1)
	Always Visible, Negative, Positive, Non-Zero, Never	Returns a value if criterion is met
	0-2	Determine decimals to display
	Standard, Rolling, Cumulative (Ongoing)	Determine if and how the resultant values sum month to month
	3-12	Period for Calculation Method: Rolling