Defining independent variables

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:

  1. On the Project Variables tab, click the +... button.
  2. Specify a name for the new independent time series variable. For example, numEmployees. In the graph, a new stream is added.
  3. 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].
  4. To create a sum over a range of periods, use the range expression; for example, 'numEmployees'(STARTPERIOD..ENDPERIOD).
  5. 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].
  6. 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).
  7. 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.
Investment analysis displays of number of employees and salary costs

Feedback