Oracle financial Analyzer User's GuideRelease 11iPart No. A87522-01

Modeling Data, 5 of 6

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

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 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

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.