Formula Layers

Formulas allow you to create a layer that is a function of any Constant or Line Item in your model. This is distinct from a Value Layer which uses fixed data, either imported or created directly in your model.

Differences from Spreadsheets

**Table 1: Differences from Spreadsheets**
Category	Spreadsheet	Our Software
Architecture	Cell Based	Object Based
Data	A cell can contain data or a formula. A formula is specified by starting with an equal sign (=).	Data and formulas are separated, eliminating the possibility of inadvertently writing over a formula with data.
Range	Single cell	As specified by start date and end date.
Syntax	Proprietary	Expressions
Construction	Single formula	Multi-stage. allows variables to be defined and sub-calculations to be performed
Programming language access	VBA Macros	Java Functions

Creating a Formula Layer

Formulas can be added to FO Types ,FO Instances and Shared Layers.

Formula Layers can also be added in FO Designer.

Click on a line item parameter.
In the 3-bar menu for the parameter, click Add and select Formula Layer. A new Formula Layer panel will open below.

Specifying the Overlay and Priority Adjustment

The default overlay is the Universal Overlay The Universal Overlay is included in every model and cannot be deleted. It has a projection priority is 0.. However, you can specify that the layer be placed in any Overlay you choose. Overlays are created by Case Dimensions.

The default priority adjustment is zero. Unless a conflict occurs, it is not necessary to change this setting.

Handling conflicting overlays

If more than one layer is created for the same line item and is assigned to the same overlay (or one with the same priority), a potential conflict could occur. The software handles the potential conflict by using the time of creation for layer, with the layer created last taking priority.

Configuring for spans of time

This specifies the first period and last period an object applies to.

Example: A layer with a Start Period of Jan, 2011 and an End Period of Dec, 2011 will cover all of 2011.

Starting and Ending periods can be specified to be either relative or specific.

Example: For a Model with a Start Period of Jan and Start Year of 2011 (as defined in Model Parameters), a Specific date of Jan, 2011 and a Relative date of First plan period are synonymous.

Configuring a Relative Date

Relative dates use variables rather than a specific Period A Period is used in a number of ways depending on context. In an Annual model, a period is 1 year. In a monthly model, a period is one month. When used as a start of a projection, it refers to the beginning of the period and when used as the end of a projection it is the end of the period. and year. They can also be offset plus or minus any number of Periods A unit of measure that specifies how many periods (months, quarters, years depending on the model) are in a range.

Period

The software includes several predefined variables:

Period prior to start of plan (initial balance period)

First plan period

Last plan period

Default layer start (as specified in Model Parameters)

Additionally, if a constant is added to a FO Type, and its Units are defined as Period (which will convert the constant into date format), that parameter will be available as a variable. In this way a layer can be specified to start in the period specified as a parameter in the object.

Example: If an FO is created for an Employee, and one of its parameters ,Employee Start Date, is a constant defined as period, Employee Start Date will be available as a variable in defining the start of a layer. The salary layer for the employee can be specified to start on the Employee Start Date.

Further, any Period parameter included in a Case Dimension will also be available, in the format "<Case Dimension Title>:<Case Parameter Name>".

Offsets

The Start Period can be offset plus or minus any number of Periods A unit of measure that specifies how many periods (months, quarters, years depending on the model) are in a range.. To the right of the Period is a selection for +/- and an input box allowing you to specify how many periods to offset the period by. This value can be a constant (default), or can be converted to a variable.by clicking the + sign next to the constant box.

The software includes three predefined variables:

Actuals # periods

Plan periods

Periods per Year

Additionally, if a constant is added to a FO Type, and its Units are defined as Periods, that parameter will be available as a variable. In this way a layer start date can be offset by the number of periods specified in the variable.

Example: If an FO is created for an Employee, and has parameters for Employee Start Date, Vacation and Months until vacation is authorized, is a constant defined as periods, the layer for Vacation can be based on Employee Start Date, offset by Months Until Vacation is Authorized.

Switching between Relative and Specific dates

If the date is configured as a specific date but you want to use a relative date, click the + sign next to the date.
If the date is configured as a relative date but you want to use a specific date, from the drop down list, select Remove and save the change. The relative date will change to a specific date.

Writing Formulas

Formulas are made up of Let Statements and Steps.

Let Statements

Let Statements allow you to define variables to be used in your formula. To add a Let, click New Let; the new Let Statement will be added.

By default, the first variable name is 'x', the second one 'y' and so on. You can rename them with any words or letters. Note: variable names cannot begin with a number.

Next, select the Kind of variable you wish to create:

**Table 2: Kinds of Variables**
Kind		Description
Constant		A constant value
Result from Previous Layer		If the Parameter includes another Layer Tthe word "Layer" is commonly used as a general category that includes both Value and Formula layers in an Overlay with a lower priority, this formula can be based on its result.
Line Item *		Allows you to select any line item, or group of line items in the model. Drill down until you find the item(s) you want to define as a variable.
System Parameter
	Period prior to start of plan	The initial balances period
	First plan period	The first period following the initial balances period
	Last plan period	The last period in the plan/model
	Plan periods	The number of periods in the model, not including the initial balance period
	Default layer start	As defined in Model Parameters
	Periods per year	Returns a different value based on the model type: Annual=1, Monthly=12, 13 Periods=13, Weekly=52
	Current period	Returns the value of current period. The first period of the plan is 1, the second period 2 and so on.
	Current fiscal period	Returns a value, starting with 1, for each period in the fiscal year, restarting at 1 in the next fiscal year.
	Current calendar period	Returns a value, starting with 1, for each period in the calendar year, restarting at 1 in the next calendar year.
	Previous period	Returns the value of the Current Period, -1.
	Days in period	Returns the number of days in a period based on a calendar. February returns 29 in a leap year.


Kind		Description
<title of case dimension> Case Dimension Parameter		If a Case Dimension has been added to your model, selecting it will reveal all Constant Parameters contained in the dimension.
Local Parameter*		Allows you to select a FO parameter in the FO Type or FO Instance An FO Instance inherits all parameters and any logic(formulas) contained in the FO. Any change in the FO is immediately updated in all instances. you are working on. If you select the parameter for which this layer is being created, be sure to offset the Line Item Starting parameter to a previous period.
FO Attribute		Returns the numerical value of the FO Attribute. If not numerical, then "Not a Number" is returned.
Sum of a Line Item Set		Returns the sum of all elements of the referenced Line Item Set
Average of a Line Item Set		Returns the average of all elements of the referenced Line Item Set
Count of a Line Item Set		Returns a numeric value equal to the number of line items in the line item set.
Sum of Expressions Line Item Set		Returns the sum of a line item set that has been multiplied by one or more variables Example: Let's say you want to allocate a group of expenses to different departments. The expenses to be allocated could be defined as the line item set.
Expression (default)		Allows you to write an expression, similar to a spreadsheet formula, based on the Expression Operators and Functions, if used in a Let Statement only use variables that are listed above it. Expressions can also use custom Java Functions.

*Line items and Local Parameter references include the ability to refer to the line item, plus or minus any number of periods, using the Line Item Starting parameter. See Configuring a Relative date above.

Note: Let Statements, by themselves, do not complete a formula layer. The variables defined must be used in a "Step".

Steps

Steps are where your formula goes into action. It is here that any variables defined in Let Statements (if any) are put to use.

Simple formulas can be written using Steps alone. Step 1 is included by default and can only be deleted if there is more than one step.

Each subsequent step acts on the step above it based on its Operation parameter.

**Table 3: Operations**
Operation	Description
Add	Adds to the previous step.
Subtract	Subtracts from the previous step.
Multiply by	Multiplies the result of the previous step by this step's value
Divide by	Divides the result of the previous step by this step's value. Will return a #DIV/0! error if zero in any period.
Safe Divide	Divides the result of the previous step by this step's value. Will return zero if a #DIV/0! error occurs in any period.
Minimum value	Imposes a minimum value on the result of the previous step.
Maximum value	Imposes a maximum value on the result of the previous step.
Round to nearest	Rounds the result of the previous step to this step's value
Round down to nearest	Rounds down the result of the previous step to this step's value
Round up to nearest	Rounds up the result of the previous step to this step's value

Just as in Let Statements, each step has the ability to select a Kind (see Table 2: Kinds of Variables above).

Line Item Adjustments

When referring to a line item, including Local Parameter references, it is possible to adjust how the line item is calculated for use in the formula. The choices, and calculations, are different depending on whether the line item is a Duration Period In a rollup, sums all included period values or Instant Period In a rollup, returns the value of the last period item.

Example: For a duration line item, an average of x periods is calculated as the sum of the three periods divided by x. For an Instant line item, an average of x periods is the sum of the average for each period (each period's beginning and ending balance divided by 2) divided by x. If inventory (an instant line item) has an initial balance of 0 (e.g. December) and $100,000 in the first month (e.g. January), its average value for the first month is $50,000.

**Table 4: Line Item Adjustment Options - Sum**
Option			Line Item Type	Description
Sum
	Time span of
		all periods	Duration	Returns a running total of all previous periods including the current period
		days (specify)	Duration	Returns the sum of the calculated daily value for the number of calendar days specified, including the current period.
		periods (specify)	Duration	Returns the sum of the period values for the number of periods specified, including the current period.
		years (specify)	Duration	Returns the sum of the period values for the number of years specified, including the current period.
		fiscal year	Duration	Returns a running total of all periods in the fiscal year, including the current period
		calendar year	Duration	Returns a running total of all periods in the calendar year, including the current period
	Previous period time span of
		all periods	Duration	Returns a running total of all previous periods excluding the current period
		days (specify)	Duration	Returns the sum of the calculated daily value for the number of calendar days specified excluding the current period
		periods (specify)	Duration	Returns the sum of the period values for the number of periods specified, excluding the current period.
		years (specify)	Duration	Returns the sum of the period values for the number of years specified, excluding the current period.
		fiscal year	Duration	Returns a running total of all periods in the fiscal year, excluding the current period
		calendar year	Duration	Returns a running total of all periods in the calendar year, excluding the current period
	Entire current fiscal year		Duration	Returns the sum of all periods in the fiscal year
	Entire current calendar year		Duration	Returns the sum of all periods in the calendar year

**Table 5: Line Item Adjustment Options - Daily, Period, or Annual Average**
Option			Line Item Type	Description
Daily, Period, or Annual Average
	Time span of
		all periods	Duration	Returns an average of the calculated daily averages of all previous periods including the current period
		days (specify)	Duration	Returns the average of the calculated daily value for the number of calendar days specified, including the current period.
		periods (specify)	Duration	Returns the average of the calculated daily values for the number of periods specified, including the current period.
		years (specify)	Duration	Returns the average of the calculated daily values for each period for the number of years specified, including the current period.
		fiscal year	Duration	Returns a running average of the calculated daily values for all periods in the fiscal year, including the current period
		calendar year	Duration	Returns a running average of the calculated daily values of all periods in the calendar year, including the current period
	Previous period time span of
		all periods	Duration	Returns an average of the calculated daily averages of all previous periods excluding the current period
		days (specify)	Duration	Returns the average of the calculated daily value for the number of calendar days specified, excluding the current period.
		periods (specify)	Duration	Returns the average of the calculated daily values for the number of periods specified, excluding the current period.
		years (specify)	Duration	Returns the average of the calculated daily values for each period for the number of years specified, excluding the current period.
		fiscal year	Duration	Returns a running average of the calculated daily values for all periods in the fiscal year, excluding the current period
		calendar year	Duration	Returns a running average of the calculated daily values of all periods in the calendar year, excluding the current period

**Table 6: Line Item Adjustment Options - Average of an Instant Line Item**
Option			Line Item Type	Description
Average of an Instant Line Item
	Time span of
		all periods	Instant	Returns the running average of the calculated daily averages for each period, including the current period.
		days (specify)	Instant	Returns the average of the calculated daily average for the number of calendar days specified, including the current period.
		periods (specify)	Instant	Returns the average of the calculated daily averages for each period specified, including the current period.
		years (specify)	Instant	Returns the average of the calculated daily averages for the number of years specified, including the current period.
		fiscal year	Instant	Returns a running average of the calculated daily averages for all periods in the fiscal year, including the current period
		calendar year	Instant	Returns a running average of the calculated daily averages for all periods in the calendar year, including the current period
	Previous period time span of
		all periods	Instant	Returns the running average of the calculated daily averages for each period, excluding the current period.
		days (specify)	Instant	Returns the average of the calculated daily average for the number of calendar days specified, excluding the current period.
		periods (specify)	Instant	Returns the average of the calculated daily averages for each period specified, excluding the current period.
		years (specify)	Instant	Returns the average of the calculated daily averages for the number of years specified, excluding the current period.
		fiscal year	Instant	Returns a running average of the calculated daily averages for all periods in the fiscal year, excluding the current period
		calendar year	Instant	Returns a running average of the calculated daily averages for all periods in the calendar year, excluding the current period
Increase (decrease) in value from previous period			Both	Returns the period difference of the referenced line item
Decrease (increase) in value from previous period			Both	Returns the period difference of the referenced line item multiplied by minus one
Increases in value over previous period			Both	Returns the monthly difference of the referenced line item, but returns zero for decreases.
Decreases in value over previous period			Both	Returns the period difference of the referenced line item, but returns zero for increases.
What if			Both	Returns the value of the referenced line item if the What if line item had the stated value, for the range of time specified in What if Start Period and What if End Period. Example: Lets' say you want to create a Basic Short Term Debt FO Instance An FO Instance inherits all parameters and any logic(formulas) contained in the FO. Any change in the FO is immediately updated in all instances. and have its Balance equal the value of Available Cash Available Cash is a system generated value that returns the amount in the default cash account (Misc Cash), if its value is greater than zero. (thereby making the short term debt instance the default balance. Create a formula layer for the Balance. In Step 1, refer to the line item Financial Statements/Available Cash, set the Line Item Adjustment to What if, select Local Parameter as the What if line item, and the What if value to 0, then set the What if Start Period and What if End Period to be the range of time for which the calculation is to apply. Then add a new step and Multiply by minus one. What this formula says is that you want the Balance to be equal to Available Cash if the Available Cash was 0.