STEM brings clarity and structure to the telecoms business-planning process, but
while the STEM Model Editor program offers powerful drag-and-drop editing for the
expert, a more familiar interface is a realistic necessity for the peripheral user,
from a colleague providing detailed tariff and cost data to a customer entering
key assumptions.
Recognising this, STEM is designed to accept external inputs from Excel and also
from any database which supports the Open Database Connectivity (ODBC) standard,
such as Access, SQL Server or Oracle. STEM also accepts the inputs or results of
other STEM models. This article, written as one in a series of how-to articles intended
to raise awareness and understanding of STEM, focuses on the principles of how to
link scalar values, time series or even default values for an entire element, and
highlight the new STEM 6.2 format for linking data for Resource Requirements from
Excel. The process of creating such links, using the External Links dialog, is explained
in detail in Section 5 of the STEM User Guide.
With the addition of the add-in interface for running models and retrieving model
results, this close integration with Excel keeps the inputs and outputs of a STEM
model readily accessible to the non STEM expert.
Linking scalar values and time series
The syntax for linking single numbers from Excel is directly comparable with the
syntax in Excel for references to cells in other workbooks. However, the majority
of STEM model inputs are time-series and so the format for linking an Interpolated
Series is of particular importance.
The form of an external reference to Excel is ‘<filename>’!<reference>.
The <filename> is enclosed in single quotes to prevent special characters
being misinterpreted as another part of a formula, while the <reference> may
be a cell reference (e.g. R1C2 or $A$2) or a name which is defined in the workbook.
In order to link the Capital Cost of a Resource to cell $A$2 in the spreadsheet
C:\DATA\COSTS.XLS, then you would enter the formula:
‘C:\DATA\COSTS.XLS’!$A$2
More generally, if a workbook contains several sheets, then the name of the sheet
must also be specified, using the same syntax as is used in Excel for an inter-workbook
link:
‘C:\DATA[COSTS.XLS]Sheet3’!$A$2
However, this is not necessary for references to global named ranges; and these
are also generally preferable, as a named range will generally remain valid when
rows are inserted in, or deleted from, a worksheet.
The syntax for scalar external references can also be used to link, for example,
the individual parameters for an Exponential Growth, but a richer format is required
for an Interpolated Series, where a single link is used to retrieve the entire series.
The essential point to understand is that, when a formula is intended to define
the entire series (rather then just an individual cell in the series), then the
formula should be entered on the time-series button, and not within the Interpolated
Series dialog (where the individual periods and values are displayed). This is most
generally achieved with reference to a named range which identifies a 2x2 block
of cells as follows:
The left-hand column tells STEM, first that the data is an Interpolated Series,
and then that the years are in the first row and corresponding values in the second
row. The range should only include this label column and the first column of values;
STEM keeps reading to the right until a blank cell is encountered.
Typically you may link several time series with the same set of years. If you first
associate the name ‘Years’ with the actual row of years, then you can omit the years
row from individual links.
STEM can calculate results by quarter or month from standard annual inputs, but
if you need to specify inputs for specific shorter time periods, then just replace
‘years’ with ‘periods’ in the examples above.
Although these techniques for scalar values and time series are fairly straightforward,
it is well worth the investment of working through these simple examples. Perhaps
the syntax is so like Excel that it is then easy to be caught out by the material
differences? Certainly these topics consistently represent the most frequent requests
to our STEM Support staff!
Linking multiple inputs as new default values for an element
Although this partly comes down to personal style and preference, there is an argument
that an excess of inter-file links makes a model hard to maintain. So an alternative
mechanism is provided which allows several inputs for a given element to be defined
through a single link.
As you might expect, a formula defining values for an entire element is entered
via its icon. For example, you can select Defaults… from a Resource icon menu to
access a dialog with a single button for each Resource in a model. Here you can
enter a formula referring to a 2×n block of cells as follows:
Just like the format for an Interpolated Series, the left-hand column identifies
the individual data, while the right-hand column defines the relevant values, with
Interpolated Series data again being read to the right until a blank cell is encountered.
The same principle works for every type of element, and the full set of labels is
provided in an appendix to the STEM User Guide, as well as in an example workbook,
MODEL.XLS, which is supplied with STEM, and linked directly from, the Start menu
for STEM 6.2. The following example shows the new format for linking data for Resource
Requirements as part of a selection of defaults for a Service:
Notice how the names of individual Resources are used to identify individual Requirements,
and thus form part of the actual labels. In order to facilitate use of this format,
the relevant labels are constructed by formula in Excel, linking the Resource names
from the immediately adjacent column.
The qualification of where the individual data belongs is thus provided within the
spreadsheet itself, significantly reducing the number of inter-file links. The order
of items is completely flexible, and only those items of interest need be included,
which means that you are not constrained by a fixed template format. The layout
in the spreadsheet may be reasonably compact, with the attendant benefits of being
easier to verify and maintain.