You can add a financial model stream
that contains neither cost or benefit values by using independent
variables.
A stream that uses the Variable type represents
a random variable that changes over time. This type of stream does
not have units, and you can apply yearly discount rates to it.
Procedure
To define an independent variable:
- On the Project Variables tab, click
the +... button.
- Specify a name for the new independent time series variable.
For example, numEmployees. In
the graph, a new stream is added.
- Populate the stream with data by using the graph paper
grid, a spreadsheet, or the calculator. When you refer
to independent variables from the Calculator or Project
Variables tab, type the independent variable stream name
in single quotation marks and specify the time period in square brackets;
for example, 'numEmployees'[PERIOD].
You
can use these variables to cite specific periods:
- PERIOD: The period that the calculator tape is saved to.
This value is displayed on the slider in the calculator.
- CURRENTPERIOD: The period that represents the current
date
- STARTPERIOD: The starting period in the model
- DELIVERYPERIOD: The selected delivery period
- ENDPERIOD: The last period in the model
- PERIODSPERYEAR: The number of periods in each year that
are configured for the current project
When you use independent variables, the period might also be
the result of an expression. For example, the following expression
refers to the number of employees in the previous period:
'numEmployees'[PERIOD-1].
- To create a sum over a range of periods, use the range
expression; for example, 'numEmployees'(STARTPERIOD..ENDPERIOD).
- By default, the discounted value from the variable stream
for the given period is displayed. To return a value that is not discounted,
use the undiscounted keyword: Undiscounted
‘Revenue'[PERIOD].
- To sum all costs or all benefits for a given period, you
can use the allCosts and allBenefits streams, which are predefined
collections.
- To sum all cost streams for a period, use the allCosts stream.
For example, allCosts[PERIOD]. To sum all costs or
benefits over a range of time, specify that range.
- To sum all benefit streams from the delivery period to the end
period, use allBenefits(DELIVERYPERIOD..ENDPERIOD).
- To sum all cost and benefit streams, use the npv function: npv(CURRENTPERIOD..ENDPERIOD).
- Where expressions are used, you can use and nest conditional
expressions. The conditional expression is the same as used in Java
and JavaScript:
( condition_expression ? true_expression : false_expression )
For
example, for a conditional expression that returns 0 before the CURRENTPERIOD
otherwise returns 1, use the following format: (PERIOD <
CURRENTPERIOD ? 0 : 1). You can use conditionals to test
boundary conditions and to avoid periods that are out of range, as
shown in this nested example:
newCustomers = 'numCustomers'[PERIOD] - (PERIOD-1 >= 0 ? 'numCustomers'[PERIOD-1] : 0)
Example
In this example, a calculator tape is used to see the current
number of employees. The project variable is salary, and the independent
variable is numEmployees.