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
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
If you need to use Infinity in a Strategic Modeling formula, the easiest way is using the @inf
function. Positive and negative Infinity values are also generated by mathematical operations such as dividing by zero.
@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
.