Exercise 22: Running a STEM model from Excel

In some contexts it may be preferable, or simply more convenient, to update the results of your STEM model without leaving Excel, especially if you want to see the impact of changing a linked input cell on the corresponding linked results. The add-in toolbar provides this functionality.

Change external link AND change the model name in the control sheet

1. Repeat the process of saving the workbook with the new name and updating the external link within the STEM model.
2. Close the STEM Editor and Result programs. This is not essential, but gives a better impression of how a self-contained Excel interface for STEM will work. You will be prompted to save the changed external link, so that the model will reference the latest workbook when you run it from Excel.
3. Navigate to the toolbar definitions sheet in your workbook and change the model name to WiMAX-DSL22.
4. Navigate to the sheet where the inputs for the DSL access service are defined and change the penetration Value B from 0.4 to 0.8.

Run working model

5. Navigate to the results sheet and click the button (Run Working Model) on the add-in toolbar. The working model runs and the linked results update automatically.

Note that the first time you run a model from Excel, if the main STEM programs are not running, the STEM about box is displayed. On subsequent model runs, only the run status windows appear.

6. Change some of the formulae in the results sheet to reference one of the scenarios, e.g., Dual/Base, rather than the working model.

You can use the button (Paste Scenarios) on the toolbar to access the scenario names if they are too long or cumbersome to type by hand.

Run all or listed scenarios

The add-in should report Scenario not found, because we have only run the working model from Excel, and not the scenarios.

7. Change the cell labelled Include Working on the toolbar definitions sheet to TRUE if you want to run the working model with either of the scenario run buttons. (The sample formulae for the run period labels refer to the working model.)
8. Click the yellow button (Run All) to run all the scenarios defined in the model. You should see the results update now.
9. If you want to avoid running unnecessary scenarios you can use the Run Scenarios button. Populate the Scenarios to run in the Toolbar definitions sheet first. You must use a semi-colon (‘;’) to separate multiple scenario names. Click the black button (Run Scenarios) to run the scenario or scenarios listed in the Scenarios to Run cell in the control sheet. Edit your results sheet to compare NPV (Zero Terminal Value) for the three scenarios, DSL/Base, WiMAX/Base and Dual/Base, and edit the control sheet to run just these three scenarios rather than all twelve!

The optional consolidation period parameter for the StemGetResult() function is defined as a named range, StemConsolidationPeriod, which refers to the cell labelled Consolidation Period on the Toolbar Definitions sheet, for instance Year, Quarter or Month.

You can use a blank string instead of Network as the element name for network results if you prefer.

Further customisation

If your spreadsheet will be used by third parties who are not familiar with the STEM add-in, it is possible to embed Excel macro buttons in the workbook, and use these to call the relevant functions to run a STEM model or its scenarios. This customisation should only be attempted by advanced users, and involves copying the macro code out of the STEM add-in.

However, this requires skills in Visual Basic for Excel and is beyond the scope of this training course. If you have these skills, you can probably figure this out for yourself as an exercise!