Spry Simulations Documentation
Simulation model in Spry is build in the usual spreadheets way - by values and formulas in cells. To create a structure of the model, you can use all Excel functionalities, formulas and functions. Besides these, Spry introduces a number of functions which implement simulation elements like syQueue(), syStock() and syState().
Model's minimal requiremens
Simulation model should be developed on one worksheet. A worksheet is regarded as Spry simulation model if it has syClock() element. The clock element defines the simulation time, time interval between timesteps, and the number of realizations. There should be only one syClock() element on the worksheet. Almost all Spry simulation elements need to reference the clock by their TimeNow argument, so that the clock can support their functionality by singaling them current simulation time.
Simulation time and recalculation
By default, Spry issues the recalculaton of Excel formulas on the model worksheet once per each timestep. The number of timesteps depends from start and end of simulation time. By default, the time interval between two timesteps is one assumed time unit, but it can be set to lower or higher value. The clock element also defines the number of realizations. One realization is sequential recalculation of Excel formulas of the model during which the clock element returns values from TimeStart to TimeEnd, by TimeInterval steps. More than one realization is necessary when we have stochastic elements as a part of simulation model, to achieve representative sample (Monte Carlo simulations).
At the beginning of simulation time (simulation zero time) the first model recalculation is executed. This is the first simulation time step, denoted by ID 0. Next time step happens one time interval after zero time, and bears the ID of 1. Next timesteps follow this pattern with ID = 2, 3 ...until the last time step at TimeEnd. Spry recalculates the model at each timestep and notes the state of all the elements.
syClock's return value is current simulation time in assumed time units. If syClock's TimeStart and TimeInterval arguments are not set, then the current simulation time corresponds to timestep ID. However, if TimeStart is set to some other value than 0, then the syClock() still returns simulation time, which is different than timesteps IDs. TimeInterval default value is 1; if you change it, the behaviour is simular - syClock() will return exact simulation time, not the timesteps IDs. In these cases, you can get the timestep ID by syCurrentStep() function if needed.
Simulation elements' return value
Simulation elements like syQueue(), syStock() and syState() are spreadsheet functions that return 0 in design time, when you are building the model. If they return an error #VALUE!, check if the arguments are set according to function's expectations. During the execution of the model, these elements return values according to their purpose, in each timestep. Values are propagated to other elements and formulas. You can list their return values, statistics and charts by syOutcome() elements.
Formulas that implement simulation elements can be nested - one element can be an argument to another element in the same formula, if the parent element expects its value. Generally, range or array arguments cannot be used in this way. Nesting is discouraged because it obscures readability of the model.
During simulation run, Spry executes all the realizatons of the model that is constructed on the active worksheet. It temporarily freezes all other worksheets and open workbooks except for the active worksheet. During development, testing and execution of simulation model the worksheet with the model can be recalculated by Spry even if the recalculaton mode in Excel is set to manual.
Button groups on the ribbon depict the tasks and types of simulation elements. The Experiment button group resembles a typical design and experiment workflow. It allows to
- declare the worksheet to become a simulation model with adding the syClock() element,
- edit the existing simulation elements,
- provide syOutcome() elements with model results,
- run the model,
- clear the results.
Other button groups provide simulation elements. In essence, they present the function wizard which guides the setting of element's parameters. The wizard automatically enters the reference to syClock() and provides arguments' descriptions. If the element already exists in a cell, it can be edited by selecting the cell and clicking the Edit button, or the button of that element. If the button doesn't correspond to already present element, or there is some other data in the cell, Spry displays a warning.
Elements can be edited also "by hand" without using the function wizard. In this case, a context sensitive help gives explanations of the function and its arguments.