A result definition is a formula which the Results program uses to calculate the corresponding result for each period of the model run. Such a result is called a
derived result, because the values are derived from the input data and primitive (or other derived) results.
The syntax for derived-result definitions is similar to that for formulae in the Editor – see 4.12 Creating formulae and internal references. In particular, any reference to another result name which contains spaces or other punctuation must be quoted. However, results are defined for types of elements, rather than individual elements, so references are made to types of input data, rather than specific elements.
For example, the standard result, Services.Increase in Creditors
is defined as:
Creditors – prev (Creditors, "Service.InitCreditors")
where Creditors
is the name of another result for Services, which doesn’t require quotes. The
prev()
function returns the value of this result in the previous period.
Service.InitCreditors
is a reference to the Initial Creditors input for Services in the Editor, which has to be quoted because of the dot. This input provides the year zero value for the
prev()
function. When this result is calculated for a particular Service, the Results program draws on the values of the
Creditors
result for this Service, together with the corresponding Initial Creditors input.
Note: At present you have to enter references to other results and inputs by hand, as there is no automatic mechanism to generate the appropriate labels. The precise reference labels for the input data are listed in 11.1 External Reference Labels for Excel. Alternatively, you can use the Export Model Data command on the Editor’s File menu to generate a ‘print-out’ in Excel of selected model element inputs, complete with the appropriate labels, and then copy the desired labels from the resulting spreadsheet. See 6.2 Exporting model data to Excel for details.
Note: STEM checks for circularities in the definitions of derived results. You can choose whether you want the Results program to treat any circularities as an error or to use iteration to resolve them. See 4.12.4 Using iteration to resolve circular references. (These options are governed separately in the Editor and Results programs.)
Global data and network results
Results of a given type can be defined in terms of other results of the same type, and the corresponding type of input data. In addition, you can refer to Network results and global inputs in the definition of any type of result.
For example, you could define a result,
Resources.Discounted Capital Expenditure, with the following definition:
"Capital Expenditure" / (1 + "Discount Rate") ^ ("Relative Year" + 1)
where the Results program would interpret both Discount Rate and
Relative Year
as Network results because there are no Resource results with those names. Similarly, you could define a result,
Service.Revenue in Dollars, defined as:
Revenue * "Model.Globals.User1"
where Model.Globals.User1
is a reference to the global input User 1, which, in this example, would be used to store a currency conversion to dollars.
Summing over related elements
Certain results are defined as a sum over a related set of elements. For example, Network cost results are summed over all the Functions in a model. Similarly, Function cost results are summed over all the Resources in a given Function. In order to support this mechanism, the
sum()
function has two different interpretations, depending on whether it is given one or more arguments.
For a Network result, the definition, sum (x, y, z), is interpreted as the straightforward sum of three other Network results,
x, y and z, whereas the definition,
sum ("Functions.Depreciation"), is interpreted as the sum of the result, Depreciation, over all Functions in the model. For the sake of consistency, the Results program does not support a trivial sum of one Network result, e.g.,
sum (x), since this is just equivalent to x.
You can sum over results for Services, Transformations, Functions and Cost Indices in the definition of a Network result, and over results for Resources in the definition of a Function Result.
The same dual interpretation applies to several other functions which can take a variable number of arguments, namely
mean(), min(), max(),
and() and
or().
Special functions
A number of functions specific to the Results program are provided to support the calculation of IRR and Break Even results. These are in addition to the general operators and functions listed in 4.12.1 Operators and functions.
| breakEven(x) |
The elapsed time to the end of the most recent period when x
goes non-negative if it has previously been negative.
If you want to look for the point at which x
goes non-positive after previously being positive, e.g., when long-term borrowing goes to zero, you can use breakEven (–
x). |
| csum(x) |
Cumulative sum of x to date |
| yearC(x) |
Current value of x within csum() |
Special functions for derived results
csum() and yearC() exist for a very specific purpose, in the calculation of IRR:
solve: csum (“Free Cashflow”) / ((1 + yearC (IRR))t = 0
t = “Period End”