Modeling Functions

Uses for functions

Financial Analyzer has many functions that you can use to make calculations and analyze data. You can use functions to perform the following tasks:

• Calculate totals and moving totals, averages, and moving averages

• Reference values from different time periods or derive data based on other line item values

• Compute logs, square roots, absolute values, and random numbers

• Calculate statistical and financial functions

To use a function, you type its name and, in parentheses, some additional information. Each piece of information needed by a function is called an argument.

Types of functions

Financial Analyzer includes the following types of numeric functions.

Type of Function	Description
Advanced Mathematical	Functions that perform advanced mathematical calculations
Time-Series	Functions that retrieve values from a previous or future time period and perform calculations on those values
Financial	Functions that perform calculations for financial analysis
Aggregation	Functions that return an aggregate value, generally consisting of a single value for many values of the input expression

Advanced mathematical functions

Advanced mathematical functions operate on each dimension in the expression. The dimensions of the result are the union of the dimensions of the inputs. The following table describes these functions.

Function	Description
ABS	Calculates an absolute value
INTPART	Calculates the integer part of a value
LOG	Calculates the natural logarithm
LOG10	Calculates the logarithm base 10
MAX	Calculates the maximum between 2 expressions
MIN	Calculates the minimum between 2 expressions
RANDOM	Calculates a random number
REM	Calculates the remainder after a division
SQRT	Calculates the square root

Most advanced mathematical functions operate on a single numeric variable, but the MAX and MIN functions make comparisons between the values of two variables. For dimensioned variables, the MAX and MIN functions compare each value of one variable with the corresponding value of the other variable.

Time-series functions

The time-series functions retrieve and perform calculations on values from a previous or future time period. The following table describes these functions.

Function	Description
CUMSUM	Calculates cumulative sums over time
LAG	Calculates the value for a previous time period
LAGABSPCT	Calculates the percent difference between current and previous values and indicates the direction of change
LAGDIF	Calculates the difference between current and previous values
LAGPCT	Calculates the percent difference between current and previous values
LEAD	Calculates the value for a subsequent time period
MOVINGAVERAGE	Calculates a series of averages over time
MOVINGMAX	Returns a series of maximum values over time
MOVINGMIN	Returns a series of minimum values over time
MOVINGTOTAL	Calculates a series of totals over time

The data you retrieve with a time-series function is usually dimensioned by a time dimension, such as MONTH, PERIOD, or YEAR. LAG and LEAD actually can retrieve data of any type, but the other time-series functions handle numeric data only.

The LAG, LAGDIF, LAGPCT, LAGABSPCT, and LEAD functions let you compare data for the time dimension values in the current status with data from a previous or future time period. You specify the data expression you want to analyze, the number of time periods the function should go back or forward for the comparison, and the time dimension. LAG and LEAD simply return the past or future value.

The LAGDIF and LAGPCT functions make the most commonly used types of comparisons, that is, the difference and the percent difference, between the current data and the comparison value.

The moving functions (such as MOVINGTOTAL or MOVINGMAX) return a value for each time period in the status of a time dimension. The value is based on the data for a range of time periods that precede, include, or follow the period for which the value is being calculated. The range is always relative to the current period, so the range is always moving forward as you process each time value in the current status.

Financial functions

The financial functions provide standard calculations needed for financial analysis. The following table describes these functions.

Function	Description
DEPRDECL	Calculates declining-balance depreciation
DEPRSL	Calculates straight-line depreciation
DEPRSOYD	Calculates sum-of-year's-digits depreciation
FINTSCHED	Calculates the interest on a series of fixed-rate installment loans
FPMTSCHED	Calculates payments for a series of fixed-rate installment loans
IRR	Calculates the internal rate of return for a stream of cash flows
NPV	Calculates the net present value of a stream of cash flows

Aggregation functions

Functions that return an aggregate value ordinarily return one value for many values of the input expression. The following table describes these functions.

Function	Description
ANY	Tests if any values match a criterion (returns Boolean result)
AVERAGE	Calculates an average value
COUNT	Counts values that match a criterion
EVERY	Tests if every value matches a criterion (returns Boolean result)
LARGEST	Calculates the largest value
NONE	Tests if no value matches a criterion (returns Boolean result)
SMALLEST	Calculates the smallest value
STDDEV	Calculates the standard deviation using N-1
TALLY	Counts the number of dimension values corresponding to related dimension values
TCONVERT	Aggregates or allocates data based on time periods
TOTAL	Calculates a total value

Related information

For complete descriptions of modeling functions, refer to the Oracle Express Language Reference Manual or the Oracle Express Language online help.