Functions Used in Formulas

Functions That Retrieve Values

These functions have no arguments:

Note:

The FreeForm forecast method will not execute for an account on a deal period.

@na

Definition

Not a number.

For use as a general fill value or for non-logical results.

Returns

N/A

Example

If a ratio is relevant only in forecasted periods due, use @na for other periods. For example:

@if(@ishist, @na, v1400/ v2890)

where Total Interest Expense is v1400 and Total Liabilities is v2890.

Note:

N/A* X= N/A and N/A+ X= X

@LOG

Definition

Computes the base-10 logarithm of the argument. This enables you to represent very high values using a smaller frame of reference. For example, LOG is used to assess seismic activity such as earthquakes.

Example

LOG(value, base))

For example, LOG(4, 100,000))= 0.12

For example, LOG10(86)=1.93449

@LN

Definition

Computes the natural logarithm of the argument

@nummonths

Definition

Number of months in a period

Returns

The number of months in a time period.
N/A for time structures based on weeks.

Example

In a quarter, this returns the number of months in the quarter.
In a half year, this returns 6.
In a year, this returns 12.

@numweeks

Definition

Number of weeks in a period

Returns

The number of weeks in a period.

Example

In a month, this returns the number of weeks in the month, or a value of 4 or 5.
In a quarter, this returns 12 or 13.
In a half year, this returns 26 or 27.
In a year, this returns 52 or 53.

@numweeks

Definition

Number of weeks in a period

Returns

The number of weeks in a period.

Example

In a month, this returns the number of weeks in the month, or a value of 4 or 5.
In a quarter, this returns 12 or 13.
In a half year, this returns 26 or 27.
In a year, this returns 52 or 53.

@LIKEPD

Definition

Use to specify a lead or lag time to a period-to-date of the same type. This function enables you to create formulas where you want the period-to-date periods to lag a full period instead of using the value from the previous period.

Example

Assume you define a formula using Oct09:YTD in which the value (V1000) from the previous year, 2008, is used V1000( -1 ). To use a value other than the 2008 period-to-date result, use V1000( @likepd(-1)).

@dimexact(vXXXX,dimension member)

Definition

References dimensional account instances from accounts that do not share the same dimensions as the account with the formula, enabling you to reference dimensions not currently assigned to the account with the function.

Returns

Returns a dimensional instance.

Example

If Sales has the dimension Region but not Product, and Cost of Goods sold has the dimension Product but not Region, the Cost of Goods Sold account can use this formula:

@dimexact(v1000,"North")*.1

to retrieve the instance for Sales/North and multiply it by 10%.

@scalar

Caution:

Do not use arguments to retrieve values from other accounts with @scalar. This functionality may be deprecated in a future release.

Definition

Returns a scalar value from the forecast method.

If you use @scalar in a Freeform Formula forecast method, the account accepts one scalar input value in all forecast periods.

Returns

Returns the value of the single forecast input for a variable when that forecast method is in use.

@calc( PROCESS, Vxxxx )

Definition

A general method of calling a complex calculation routine.

Returns

True—if the calculation succeeds.
False—if the calculation fails.

@ipvalue( PROCESS, Vxxxx )

An instruction to the calculation engine indicating there are intra-period values stored for this account, by some process, that should be retrieved and, in the case of Flows, summed to return the reporting value for the account for the period.

@debt( Vxxxx, TOKEN_NAME )

Returns values directly from the debt schedule for use in formulas and reporting. Other than trivial internal calculations, this performs no calculations.

@Debt keywords:

DAYS_IN_TERM: returns the total number of days in the term of the debt instrument according to the Day Count Convention used by the instrument. This measures the difference between the Issue Date and the Maturity Date.
FIRST_DAY: returns the day number for the Issue Date of the debt instrument.
LAST_DAY: returns the day number for the Maturity Date of the debt instrument.
INITIAL_BALANCE: returns the initial balance for the debt instrument.
ISSUE_COSTS: returns the amount of the issue costs (as a currency value) for the debt instrument.
PREM_DISC: returns the amount of the premium/discount (as a currency value) for the debt instrument.
ELAPSED_DAYS: returns the number of days that have passed since the Issue Date for the debt instrument; returns a zero when outside the term of the instrument.
TERM_REMAINING: returns the fraction of the term remaining for the debt instrument, adjusted for early repayment of debt if the Amortization Follows Principal option is selected. Returns 0 when outside the term of the instrument.
DAYS_IN_PERIOD: returns the number of days in the current period according to the Day Count Convention used by the instrument.
DCF: Day Count Fraction for the current period according to the Day Count Convention used by the instrument.

@sub(Vxxxx.xx)

Similar to @dim in that, using no arguments, it references the same subaccount number as the variable being calculated. Useful when calculating subaccounted formulas, as with some Debt Scheduler variables.

Functions That Reference Specific Time Periods

These functions return time period information into the current time period from different periods. None of these functions have arguments.

@basepd

Definition

Base period

Returns

A relative time reference that returns the value for an account in the base period—see Settings the Time Periods.

Example

If 2007 is the base period, this equation: v1000(@basepd)

returns the value of Sales (v1000) for 2007.

@closing

Definition

Closing period

Returns

A relative time reference that returns the value for the closing period of an account.

Example

This formula: v1000(@closing)

returns the closing period value for Sales (v1000).

@deal

Definition

Deal period

Returns

A relative time reference that returns the value for the deal period of an account.

Example

This formula: v2005(@deal)

returns the deal period value for Cash Used in Transaction (v2005).

@firstfore

Definition

First forecasted period

A relative time reference that returns the first forecasted time period value for an account—see Settings the Time Periods.

Example

To return the first forecasted period output value for Sales, if the last historical value (for example,1999) of sales is 10 and a 10% growth rate in all following periods:

v1000(@firstfore)

The value returned is 11 (10* 1.1) or 2000's sales.

@firstpd

Definition

First period

Returns

A relative time reference that returns the first period in the model's value for an account.

Example

If 2007 is the first period in the model, the formula:

v1000(@firstpd)

return the value for 2007 Sales (v1000). If 2007 is in quarters, Strategic Modeling returns the first quarter for 2007 Sales (v1000).

@firsthist

Definition

This function specifies a time period reference to the first historical period (the first period after the Beginning Balance period) in the model.

Returns

This method returns a time period reference to the first historical period (the first period after the Beginning Balance period) in the model.

Example

v1000(@firsthist)

retrieves the value of account "v1000" in the first historical period.

@lastfore, vXXXX(@lastfore)

Definition

References the last forecasted value of an account.

Returns

The value of an account in the last forecasted period.
N/A for other periods.

Example

For this formula and values:

v1000(@lastfore)

V1000	1998H	1999H	2000F	2001F	2002F	2003F	2004F
Sales	10	15	16.5	18.15	19.965	21.962	24.158

The formula returns 24.158 in 2004 and N/A in other periods.

@lasthist, vXXXX(@lasthist)

Definition

References the value of an account that is in the last historical period.

Returns

The value in the last historical period.
N/A for all periods up to the last historical period.

Example

For this formula and values:

v1000(@lasthist)

V1000	1998H	1999H	2000F	2001F	2002F	2003F	2004F
Sales	10	15	16.5	18.15	19.965	21.962	24.158

The formula returns and N/A in 1998, a 15.000 in 1999 and remaining periods.

@opening

Definition

Opening period

Returns

A relative time reference that retrieves the value for an account in the opening period. The opening period is the aggregate of the closing and deal periods. For example, for a deal period in Mar99, Strategic Modeling creates the accounts Mar99:Closing and Mar99:Deal, which aggregate to Mar99. Mar99 is the opening period.

Example

The formula: v2000(@opening)

returns the opening period value of Cash (v2000).

@lastactual

Definition

Last actual value.

Returns

A reference to the last actual period.

@lastpd

Definition

Last time period.

Returns

A reference to the last time period.

Functions That Retrieve Time Period Information

These functions accept optional arguments to specify absolute or relative period references. Without arguments, they return the value for the current period.

@halfnum

Definition

Half period number

Used to trigger @if statements by making the condition equal to the first or second part of the year.

Returns

1—first half
2—second half

Example

If a company accumulates taxes for the first half of the year and pays taxes second half, this formula could be entered in a Taxes Payable account:

@if(@halfnum=1, @sum(v1690,@ytd), 0)

with Total Taxes (v1690).

@inputpd( [Time period reference])

Definition

Input period

This function is useful when using an optional time period reference, usually a lead or lag, although absolute period references are possible.

Returns

Returns the input period driving the value for the period being calculated. This may be the period that is being calculated, if it is an input period.

Example

For example: @inputpd( -1 )

You can reference the preceding input period, even if that period is a different type from the current period, which enables returning some types of lagged data.

This formula:

@Vxxxx(-1)

returns the value of Vxxxx from the previous period of the same type.

This formula:

@Vxxxx(@inputpd(-1))

returns the value of Vxxxx from the preceding input period.

@isagg

Definition

Is an aggregate period

A relative reference in @if statements. Used with memo accounts, ratio accounts, and covenant accounts in time period structures that yield aggregate periods.

For example, if a model is in quarterly detail a year end aggregate period is created. The same applies to months, half years, and subperiods. Used in @if statements, different calculations can be performed in these aggregate periods.

Returns

True—if the period is an aggregate period
False—if not

Example

If a model is in quarters and the year end is an aggregate period, this formula:

@if(@isagg,1,2)

returns 2 in each of the four quarters and 1 in the year end aggregate.

Note:

This function only works when one of the User Defined Accounts is a calculated (CALC) field and the None option is selected.

@isclosing

Definition

Is the closing period in the model

A relative reference in @if statements.

Returns

True—if the period is the closing period in the model
False—if not

Example

This formula:

@if(@isclosing, 1, 2)

returns 1 in the closing period, 2 for other periods.

@isdeal

Definition

Is the deal period

A relative reference in @if statements.

Returns

True—if the period is the closing period in the model
False—if not

Example

This formula:

@if(@isdeal, 1, 2)

returns 1 in the closing period, and 2 for other periods.

@iseoy

Definition

Is the end of the year

A relative reference in @if statements used for models in months, quarters, or half years. This function models year end payments of accrued liabilities. Causes no problems in yearly detail.

Returns

True—if the period is at the end of the fiscal year
False—if not

Example

If the model is in quarters and December is the fiscal year end, this formula:

@if(@iseoy,1,2)

returns 2 in the first three quarters and 1 in the fourth quarter.

@isfirstpd

Definition

Is first period

A relative reference in @if statements.

Returns

True—if the period is the first period in the model
False—if not

Example 1

If the first quarter of 1998 is the first period in the model, this formula:

@if(@isfirstpd, 1, 2)

returns 1 for 1Q98, and 2 for other periods.

Example 2

If you delete 1998, the formula in the previous example returns 1 in the first period of 1999, and 2 in subsequent periods.

@isfirsthist

Definition

Is the first historical period in the model.

A relative reference in @if statements, enabling you to change the historical/forecast boundary and maintain the integrity of the formula. Forecast periods are determined by the historical/forecast boundary set in Settings the Time Periods.

Returns

True—if the referenced time period is the first historical period (the first period after the Beginning Balance period)
False—if not

Example

@if(@isfirsthist,@na,@sum(v1000,-1))

returns a time period reference to the first historical period.

@isfirstfore

Definition

Is the first forecasted period in the model

A relative reference in @if statements, enabling you to change the historical/forecast boundary and maintain the integrity of the formula. Forecast periods are determined by the historical/forecast boundary set in Settings the Time Periods.

Returns

True—if the period is the first forecasted period in the model
False—if not

Example 1

If a model is in years and the historical/forecast boundary is 2007/2008, with 2008 as the forecast period, this formula:

@if(@isfirstfore,1,2)

returns 1 for 2000, and 2 for other periods.

Example 2

The same formula in the previous example, but with a model in quarters with the first quarter of 2008 as the first forecast period, returns 1 for the first quarter of 2008, and 2 for other periods.

@isfore

Definition

Is a forecasted period.

A relative reference in @if statements.

Forecast periods are determined by the historical/forecast boundary set in Settings the Time Periods.

Returns

True—if the period is a forecasted period
False—if not

Example

If a model contains 6 years starting in 2006, with the first year historical, then the remaining years, 2007–2011, are forecast. This formula:

@if(@isfore, 1, 2)

returns 1 for 2006, and 2 for the other years.

@isfirstforeyr

Definition

Is first forecast year.

Accepts a time period reference as an argument.

Returns

True—If this period is contained in the first forecast year, or the first year is, even partially, in the forecast period.
False—If not.

@ishalf

Definition

Is a half year

A relative reference in @if statements.

You can divide a year into quarters, enabling aggregate half year periods, or divide a year into halves, which would not make these aggregate periods. Time period detail is determined in Settings the Time Periods.

Returns

True—if the period is a half-year period whether or not it is an aggregate period
False—if not

Example

If a model has 2006 in years and 2007 in half years, this formula:

@if(@ishalf, 1, 2)

returns 2 in 2006, and 1 in 2007.

@ishist

Definition

Is a historical period

A relative reference in @if statements.

Historical periods are determined by the historical/forecast boundary set in Settings the Time Periods.

Returns

True—if the period is a historical
False—if not

Example

If a model contains 6 years starting in 2006 and the first year if historical, then 2007–2012 are forecast. This formula:

@if(@ishist, 1, 2)

returns 1 in the 2006, and 2 in the other years.

@isinput

Definition

Is an input period

A relative reference in @if statements.

Use in time period structures containing aggregate or year-end periods: months, quarters, halves. Use this function with accounts convertible into calculated accounts: memo accounts, ratio accounts, and covenant accounts.

Returns

True—if the period is an input period
False—if not

Example

If 2006 is in quarters with an aggregate year end, this formula:

@if(@isinput, 1, 2)

returns 1 for each quarter of 2006, and 2 in the year end 2006.

@islastfore

Definition

Is the last forecast period

A relative reference in @if statements.

Returns

True—if the period is the last forecasted period in the model
False—if not

Example

If a model contains 6 years ending in 2011, and 2007–2011 are forecast, this formula:

@if(@islastfore, 1, 2)

returns 1 in 2011 (the last forecasted period), and a 2 in the other years.

@islasthist

Definition

Is the last historical period

A relative reference in @if statements.

Forecast periods are determined by the historical/forecast boundary set in Settings the Time Periods.

Returns

True—if the period is the last historical period in the model
False—if not

Example

If a model contains 6 years starting in 2005, with 2006 as the last historical period, then the 2007–2010 are forecast. This formula:

@if(@islasthist, 1, 2)

return 1 in 2006, and 2 in all other years.

@ismonth

Definition

Is a monthly period

A relative reference in @if statements.

Time period detail is set in Settings the Time Periods.

Returns

True—if the period is in months
False—if not

Example

If 2006 is in years and 2007 is in months, this formula:

@if(@ismonth, 1, 2)

returns 2 for 2006, and 1 in monthly periods for 2007.

@isopening

Definition

Is the opening period in the model

A relative reference in @if statements.

The opening period is the aggregate of the closing and deal periods.

For example, if you create a deal period in Mar08, Strategic Modeling creates the accounts Mar08:Closing and Mar08:Deal, which aggregate to Mar08. Mar08 is the opening period.

Returns

True—if the period is the opening period in the model
False—if not

@isqtr

Definition

Is a quarter

A relative reference in @if statements.

Returns

True—if the period is in quarters
False—if not

Example

If 2007 is in years and 2008 is in quarters, this formula:

@if(@isqtr, 1, 2)

returns 2 in 2007, and a 1 for each quarter in 2008.

@issub

Definition

Is a subperiod

A relative time reference in @if statements.

The opening period is the aggregate of the closing and deal periods.

Returns

True—if the period is subperiod with zero days
False—if not

Example

If 2007 is in years and 2008 is comprised of two subperiods, one of 365 days and another of zero days, this formula:

@if(@issub, 1, 2)

returns 2 for 2007, and, for 2008, 1 for the 365 day and 1 in the restatement or zero-day period.

@isweek

Definition

Is a week

A relative time reference in @if statements.

Time period detail is set in Settings the Time Periods.

Returns

True— if the period is in weekly detail
False—if not

Example

If 2007 is in months and 2008 is in weeks, this formula:

@if(@isweek, 1, 2)

returns 2 in the months of 2007, and 1 in the weeks of 2008. Aggregate periods of 2008 returns 2.

@isyear

Definition

Is a year

A relative time reference in @if statements.

Returns

True—if the period is a year or yearend
False—if not

Example

If 2007 is in years and 2008 is in quarters, this formula:

@if(@isyear, 1, 2)

returns 1 in 2007, and 2 for each quarter in 2008.

@monthnum

Definition

The month fiscal number

Use @monthnum in @if statements to model one-time-a-year occurrences. Strategic Modeling numbers months from 1 to 12 beginning with the first month of the fiscal year. For example, the function is used if a liability were paid out on a month.

Returns

The number of the month: between 1 and 12.

Example

If income Taxes Payable is v2530, Total Taxes is v1690, and September is month number 9, this formula:

@if(@monthnum= 9, v1690, v2530(-1m)+v1690)

aggregates all taxes in v2530 until September, pays taxes by decreasing the balance to 0, and starts accumulating current-period taxes.

@period

Definition

Period number, starting from the first forecasted time period

This function counts the aggregates and input columns. The last historical time column is number 0, and each column number following increases by 1.

Returns

True—if the period number equals a number input in @if
False—if not

Example

This formula:

@if(@period= 2, 1, 2)

executes (result: 1) after the formula reaches the second forecasted time column.

@pdexists

Definition

Period exists

Executes commands if a period exists.

Returns

True—if the period exists
False—if not

Example

For the Sales (v1000) account:

@if(@pdexists(-3),V1000(-3),@na)

Embedded in a larger formula, this formula ensures that Sales from the 3 previous periods enters the equation. If there are no Sales figures for those periods, it returns N/A.

@pdlen

Definition

Period length

Time period detail is set in Settings the Time Periods.

Returns

Returns the number days in a period.

Example

This formula calculates interest expense:

@pdlen/@yearlen * v2520 * 9%

Where Notes Payable (v2520) has a 9% annual rate. This takes the number of days in the period, divides by the number of days in the year, multiplies the debt balance, and multiplies by 9% to get to a periodic interest expense.

@pdnum

Definition

Period number, starting from the first time column

Totals the aggregates and inputs. The first time column is number 0.

Returns

The number of time columns in the model since the first period.

True—if the period number equals the number enter in @if
False—if not

Example

@if(@pdnum= 2, 1, 2)

This executes (result: 1) after the formula reaches the third time column in the model.

@qtrnum

Definition

Quarter number

Returns

A relative time reference in @if statements.

Example

@if(@qtrnum=3,v1080(-1Q),v1000*@input)

In the third quarter, this formula takes the value of second quarter Selling, General & Administration Expense (v1080). In the first, second, and fourth quarters, the formula takes an input percentage (@input) times Sales (v1000).

@since( period reference ), @after

Definition

Adds durations to functions such as @sum.

@since includes starting periods. Accepts absolute period references such as @firstfore or May03, but rejects relative period references such as -1Y.

@after does not include starting periods.

Example

@sum( Vyyyy, @since( @issuepd( Vxxxx )))

Sums all flows occurring after the debt was issued.

@weeknum

Definition

The week fiscal number

Used in @if statements to model one-time-a-year occurrences.

Strategic Modeling numbers the weeks from 1 to 52 (or 53) beginning with the first week of the fiscal year. For example, use this function if a dividend were paid out in a week.

Returns

The fiscal week number, between 1 and 53.

Example

For Common Dividend (v1880) and Weighted Average Common Shares Outstanding (v3410):

@if(@weeknum=37,v3410*.65,0)

returns a Common Dividend corresponding to 65 cents per common share in the 37th week of each year and 0 in all other weeks.

@yearlen

Definition

Year Length

Returns

The number of days in the year, as defined in Settings the Time Periods.

Example

This formula returns the value of Sales (v1000) in annual periods:

@if(@isyear,v1000,v1000/@pdlen*@yearlen)

In non-year periods, it annualizes Sales by dividing by the period length and multiplying by the number of days in the year.

@yearnum

Definition

Year number

A relative time reference in @if statements.

Returns

The year number.

Example

@if(@yearnum=2001,v1080(-1y),v1000*@input)

In 2001, the formula returns the value of 2000 Selling, General & Administration Expense (v1080). In other forecast years, the formula return an input percentage (@input) times Sales (v1000).

@firstday

Definition

The first day of the time period

Returns

The day number for the first day of the period.

@lastday

Definition

The last day of the time period

Returns

The day number for the last day of the period. For calendar time, this is the number of days since December 30, 1899. For non-calendar time periods, the value is adjusted for the length of the year (360 or 364 days).

@iscalc

Definition

Is a calculated number

Returns

True—if the time period contains calculated values
False—if not

@isleaf

Definition

Is a model with no child models

Returns

True—if the time period has no child periods
False—if not

Example

In a year containing only months, the year returns false and months return true.

@isptd

Definition

Is period-to-date

Returns

True—if the time period is a period-to-date
False—if not

@istrailing

Definition

Is a trailing period

Returns

True—if the time period is a trailing period
False—if not

@islastactual

Definition

Is the last actual value

Returns

True—if the time period is the last actual period
False—if not

@islastperiod

Definition

Is the last time period

Returns

True—if the time period is the last period
False—if not

@blocknum

Definition

A block of time periods defined by month or week numbers

Returns

For models in months, the month number in the year, 1 to 12.
For models in weeks, the week number in the year, 1 to 53.

@issuepd( Vxxxx, [optional period reference])

Definition

Issue period

Returns

If Vxxxx contains a debt schedule, returns the period in which the debt was issued.
Otherwise, returns an invalid period reference.

@isissuepd( Vxxxx, [optional period reference])

Definition

Is issue period

Returns

True—if Vxxxx contains a debt schedule and the current period, or optional reference, is when the debt was issued
False—if not

Use the optional period reference to change the test period. For example:

@isissuepd( Vxxxx, -1 )

test if the prior period was the issue period.

@isinterm( Vxxxx, [optional period reference])

Definition

Is in the term—Debt Scheduler only

Use the optional period reference to check another period if the first is within the term. A period is within the term of the debt if the debt is issued, exists, or is extinguished during that period.

Returns

True—If Vxxxx has a debt schedule and the current period is within the term.
False—If Vxxxx has no debt schedule, or the current period is not within the term.

Functions That Modify the Retrieval of an Account Value

For these functions, the first argument is an account reference, unless otherwise indicated. Arguments in [] are optional.

@abs(vXXXX)

Definition

Absolute value

Returns

The absolute value of a specified account.

Example

@abs(v1750)

Where Net Income equals v1750 and is -10, this returns 10.

@annualize(vXXXX)

Definition

Annualize a value

Use when working in months, quarters, or half years. The calculation uses the number of days in the year and the number of days in that period to calculate an annualized value.

Returns

Returns the annualized value of the account.

Example

@annualize(v1150)

Where Operating Profit equals v1150 and is 15 in the third quarter of 2000. This calculates as:

15*(# of days in year)/(# of days in period)

or

15* 366/ 92= 59.674.

@avg(vXXXX, -t)

Definition

Averages a value over t periods

The t variable can be a month, quarter, or half year. Strategic Modeling calculates values for periods with insufficient detail.

Returns

Returns a rolling average of an account for the precious t periods.

Example

@avg(v1040, -3q)

Where Cost of Goods Sold (v1040) is:

v1040	1998	1999	1Q00	2Q00	3Q00	4Q00
Cost of Goods Sold	15	20	4	5	6	7

The calculation in 2Q00:

[5+ 4+ (20*(# of days in 4Q99)/(# of days in 1999)]/3

The result: 5.68

The calculation in 3Q00:

(4+ 5+ 6)/3 or 5.

@ceil(vXXXX)

Definition

Rounds a value up to the next integer number

Returns

Returns the next highest integer number (for example, 2, 10, 65, 149...) based on the result of the parenthesized variable or equation.

Example

Where Inventory (v2040) is 233, this formula:

@ceil(v2040/100)

calculates the number of trucks necessary to haul the inventory, assuming each truck carries 100 worth of inventory in a period. This example equates to 2.33 and the function rounds result up to 3.

@chg(vXXXX,-t)

Definition

Calculates the change

Returns

Returns the change in the variable over t periods.

Example

@chg(v1040, -1q)

Where Cost of Goods Sold (v1040) is:

v1040	1998	1999	1Q00	2Q00	3Q00	4Q00
Cost of Goods Sold	15	20	4	5	6	7

In the second quarter of 2000 the equation returns:

1, (5- 4)

@floor(vXXXX)

Definition

Rounds a value down to the next integer number

Returns

Returns the next lower integer number (for example: 2, 10, 65, 149...) based on the result of the parenthesized variable or equation.

Example

If Sales are 20.23, 34.45:

@floor(v1000)

calculates the values 20 and 34.

@histavg or @histavg(vXXXX)

Definition

Historical forecasting average

If a Freeform Formula forecast method contains @histavg, no input is required in the forecast period. On the Accounts view, when the cursor is on the referenced account, the Hist. Avg. text box displays the historical average.

The years in the calculation of the historical average are defined in Settings the Time Periods.

Returns

The historical average based on the forecasting method used in the account.

Example 1

The basic formula:

@histavg

Example 2

If Sales (v1000) in 2006 and 2007 are 10 and 12, and the forecasting method of sales is as an actual dollar value, this formula:

@histavg(v1000)

returns 11.

Example 3

If example 2 uses a growth rate forecasting method, Strategic Modeling calculates the historical average at 20%—the growth rate needed to get from 10 to 12.

@input or @input(vXXXX)

Caution:

Do not use arguments to retrieve values from other accounts, as this option is deprecated.

Definition

Uses an input in the formula

Enables users to input a number and reference that input in a formula.

Returns

The input function works in conjunction with the Input as and Units text boxes.

Example 1

When calculating dividends, Net Income from the first forecasted time period can be used as a base. Net Income is multiplied by a percentage input in each period.

For example:

@input* v1750(@firstfore)

By default, the Input as is Currency. Change it to Percentage for this formula.

Example 2

For Cost of Goods Sold (v1040):

@input(v1040)

returns the input of v1040 regardless of the account containing the formula. If Cost of Goods Sold is 75% of Sales, Strategic Modeling returns 75% instead of the Cost of Goods Sold output.

@irr(vXXXX(t),vXXXX(t), [%])

Definition

Internal Rate of Return (IRR)

The first variable is the account in which the initial cash outlay exists at in a period.
The second variable is the cash stream beginning in a period.
The percentage is an optional guess at the IRR.

Returns

The internal rate of return of a stream of cash flows.

Example

A project with an initial invest in 2000 and a cash flow beginning in 2000, uses this formula:

@irr(v300(1999), v4100(2000))

if the initial investment exists in v300 of 1999. The cash stream comes from the cash flow of Operations (v4100) beginning in 2000.

@normalize

Definition

Normalize a value

Calculates a normalized value from the number of days in the current period and a prior period. For comparing consecutive time periods of various lengths.

Returns

The normalized value of an account across consecutive time periods.

Example

@normalize(v1150(-1))

If Operating Profit (v1150) is $8,515 in 2006,

2007 calculates as:

(v1150(-1)*# of days in current period)/(# of days in prior period) or (8515*366)/365 = 8538.

@prior(vXXXX)

Definition

Prior period account

Returns

References the prior period's account balance.

Example

@prior(v1040)

references the prior period balance for Cost of Goods Sold (v1040).

@sum(vXXXX, -t)

Definition

Summation

Returns

This is a rolling summation function. You must enter an account number and the number of periods to sum.

Example

@sum(V1040,-3M)

sums Cost of Goods Sold (v1040) over the last three months.

@ytd

Definition

Year to Date

References year-to-date time periods.

Returns

A summation of values from the beginning of a year to the present.

Example

If a three-year model, 2004-2006, is in quarters:

@avg(v1040(-2Q), @ytd)

averages the year-to-date Cost of Goods Sold value from the prior two quarters. In the first quarter of 2006, the sums 1Q2000, 2Q2000, and 3Q2000, and divides by three.

Functions That Operate on Expressions

These functions can take any expression as an argument:

@depr(vXXXX,"schedule") or @depr(vXXXX,schedule, period retirement is in, % of initial investment retired)

Definition

Depreciation

Returns

Calculates depreciation or removes retirements from depreciation expense.

Enables forecasting depreciation against a capital expenditures stream. The basic formula:

@depr (CAPX* stream, * schedule)

where CAPX stream is the depreciating capital expenditure account and schedule is the schedule name defined in the following section Using Debt Scheduler. Enter this formula in v2190.1 or a memo account and the function output is the depreciation for that period's asset.

When depreciating CAPX streams, @depr lags backward. The depreciation in 2003 for a five-year asset is:

2003's CAPX times the first year rate
plus 2002's CAPX times the second year rate
plus 2001's CAPX times the third year rate, etc.

If a year has no CAPX or the year does not exist, Strategic Modeling multiplies zero by a rate.

Because Strategic Modeling applies the same rate to all a variable's CAPXs, each asset class should have a CAPX stream. For example, use separate subaccounts of v2170.1 for five-year and ten-year properties.

When depreciating assets for financial and tax reporting purposes, you can use the same CAPX stream, but different schedules and output accounts.

Interim Periods

If you enter assets per quarter, month, or half-year, @depr calculates the annual depreciation for the asset, displaying the amount in the quarter. To calculate each interim period likewise, create schedules.

In years after the year placed in service, Strategic Modeling calculates annual depreciation for that asset and allocates to interim periods based on days.

Retirements

To record a retirement, this function records depreciation:

@DEPR(v2170.03, "schedule", 3, 50)

v2170.3 (Gross Retirements) is the retirements on assets
Schedule is the schedule containing the depreciation rate (that is, 5 year SL)
(3) is the current year in the schedule (in a half-year convention, five-year properties depreciate in six years). Must be a positive integer.
(50) is the depreciation percentage not recognized in the retirement year. Must be between (0) and (100) and applies only in the retirement year. In years after the retirement year, Strategic Modeling calculates depreciation no longer recognized. If a method recognizes no depreciation in the retirement year, enter 100.

Note:

If modeling the asset sales, model accumulated depreciation on the retired assets.

Total depreciation is calculated by adding all depreciation accounts and subtracting retirements.

Purpose 1: Depreciation

@depr can depreciate capital investments streams using depreciation schedules. The formula should use the account forecasting capital investments (v2170.1.xxx). Enter the exact name of the schedule in quotations.

Example 1:

@depr(v2170.1.010, "Tax: 5 year")

With the 5 year MACRS depreciation schedule: 20%, 32%, 19.2%, 11.52%, 11.52%, 5.76%. There are six depreciation percentages due to the half-year convention.

V2170.1.010	1998H	1999H	2000F	2001F	2002F	2003F
CAPEX	16	20	30	35	40	45

In the first forecast year, 2000, Strategic Modeling takes 19.2% of 1998's investment (from the third period of the schedule), 32% of 1999's 20, 20% of 2000's 30 investment. The answer is 15.28.

Purpose 2: Removing Retirements

When removing assets from service before completely depreciating, remove the asset's portion of the depreciation expense, because Strategic Modeling depreciates initial investments. Strategic Modeling does not know retirements occur in the future and can not adjust accordingly.

@depr can remove the retired portion from the depreciation amount. In @depr, reference the account holding asset retirements (v2170.3.xxx).

Reference the schedule depreciating the assets, enter the number of periods back the retired asset started, and reference the % of the initial investment being removed from the depreciation expense.

Example 2:

@depr(v2170.3.010, "Tax: 5 year", 3, .75)

@if(T/F Test Condition, Execute if True, Execute if False)

Definition

Conditional logic statement

Syntax:

@if(test condition, execute if "true", execute if "false")

Executes one of two commands based on a condition. The condition can be a formula, string, list, or date, and must return true or false. You can nest @if statements.

Returns

Results of executed command.

Example 1

For Net Income (v1750), if the common dividend payout percentage is based on the company's net income growth over the previous year, and:

If Net Income increases by at least 25%, the dividend is 20%
If Net Income is below 25%, the dividend is 15%

The formula for Common Dividends is:

@if((v1750-v1750(-1Y))/v1750(-1Y)>=25%, v1750*20%,v1750*15%)

Example 2

If the model in example 1 is in quarters, but dividends are paid once a year at the fiscal year beginning, based on the prior year's net income, use a nested @if to calculate payment in the first quarter only:

@if(@qtrnum=1,@if((v1750(-1q)-v1750(-5q))/v1750(-5q)>=25%, v1750(-1q)*20%,v1750(-1q)*15%),0)

Note:

In the analyze trail, Strategic Modeling returns 1 if the answer is true, and a 0 if the answer is false.

Using strings

Enter strings in quotes, for example: Underwriting. Strings are not case sensitive, and are used as function arguments only to test formulas—they cannot be used as results.

This is valid:

@if(v1.0.600="Underwriting", v300*v305, @na)

because you can use strings in tests.

This is invalid:

@if(v155=v160, 300, "Revenue")

because you cannot use string as results.

Using lists

Lists behave like strings. Lists can be time series or scalar.

For example, if an account "Processed?" (v150.00.0000) has these possible values: Yes, No, Not Available—this is valid:

@if(v150="Yes", 100, 200)

In enumerated number lists, numbers are handled as strings, and entered in quotes. For example:

@if(v176="8", 300, 400)

Using Dates

To enter dates, use the MM/DD/YYYY format within quotes, for example:

@if(v174="06/30/2003", 100, 300)

Dates are handled as strings.

@isna(expression)

Definition

Is not available

Can be used in @if statements.

Returns

True — if the expression is undefined or not a number
False — if not

Example 1

@isna(1/0)

The answer to the expression is undefined, so Strategic Modeling returns true.

Example 2

@if(@isna(v2040/v1040), v2040/v1040, 0)

With Inventory (v2040) and Cost of Goods (v1040), this formula returns the inventory turnover when Cost of Goods Sold does not equal zero and 0 when Cost of Goods Sold does equal 0 or is N/A.

@max(val.,val.)

Definition

Maximum

Returns

The greatest number in a range of values.

Example

@max(0, 1, 2)

Returns 2.

@min(val.,val.)

Definition

Minimum

Returns the minimum of a value range. Formulas can be performed within function.

Example

@min(0, 1, 2)

Strategic Modeling returns 0.

@not(condition or formula)

Definition

Not something

Used in @if statements to execute a command if a condition is not true.

Example 1

@if(@not(v1750>100000), 1, 2)

If v1750 (Net Income) is less than or equal 1 million, a 1 is returned, otherwise a 2 is returned.

Example 2

@if(@not(@isyear), 1, 2)

Strategic Modeling returns 1 if the period is not a year (for example, a month, quarter...), and a 2 upon other time structures.

Special Functions

These functions are for specific use. Do not use them unless absolutely necessary.

@debtex

Used by Debt Scheduler for internal calculations.

@pfdindebt

Used by Funding Options for internal calculations.

@xspfdtodebt

Used by Funding Options for internal calculations.

Functions That Reference Infinity Values

@ inf

Definition

Loads infinity value

Returns

Infinity

Example

@isinf(expression)

Definition

Method to check whether the value of an expression is infinite.

Returns

True — if passed a positive or negative Infinity value
False — if not

Example1

@isinf (1/0) The answer to 1/0 is "Infinity", so Strategic Modeling returns true.

Example 2

@if(@isinf (v2040/v1040), v2040/v1040, 0) With Inventory (v2040) and Cost of Goods(v1040), This formula for inventory turnover checks to see if the calculation produces an infinite value (which it will when Cost of Goods Sold is zero or Inventory is Infinite) and, if so, sets the computed value to 0.