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

Key Differences

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

Use Case	Financial Data	Non-Financial Data	Functions Available
Modelling	Financial data limited by report area	Driver data available	Rolling function disabled
Report Formulas	Financial data limited by report area	Driver data available	Rolling function disabled
Custom KPIs	All data sources	Driver data available	All available

Fundamentals of Using the Formula Builder

When you open any formula builder you are met with the same basic structure. The options in terms of functions, data sources and settings vary depending on where you are building your formula, these differences are detailed in the sections below.

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

Name your formula.
To input data into your formula, select the origin under the Add Data right-hand panel.
Expand each section to view all the data lines, double-clicking to bring it into the formula.
Under the Forecast Data source, entire sections can be selected, also by double-clicking.
Click any of the mathematical functions to insert them into the formula.
Bring in numerical values by typing them into the Values box, or using the up and down arrows, then confirm the value by hitting Enter or clicking the green plus button.
To remove any component of the formula, click the cross in its corner.
Adjust your settings as required.
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 (Unavailable with Modelling and Report Formulas)	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

The following settings are available in the formula builder. Availability varies depending on which area of Castaway you are building your formula in.

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