**Spreadsheet EX3B.XLS (SIR example)**

*Clear_output*: This subroutine clears the temporary
storage that will be used when applying the SIR algorithm.

*RunSir*: This subroutine controls the application of the
SIR algorithm. It contains two main steps: (a) calling the subroutine that
copies the variables from the spreadsheet to those in the macro, and (b) calling
the subroutine that actually implements the SIR algorithm.

*Readpars*: This subroutine copies the variables from the
spreadsheet to those in the macro.

*PrintResults*: This subroutine copies the results of the
SIR algorithm from the macro variables back into the spreadsheet.

*Func*: This subroutine calculates the likelihood for the
current parameter vector.

*Sir*: This subroutine performs the SIR algorithm. Each
step involves generating a parameter vector from the prior, calculating the
likelihood for this parameter vector and storing the likelihood in a matrix
(indexed by the values for the two parameters). The macro also keeps track of
the depletion of the resource corresponding to each parameter vector and uses
this to construct the marginal posterior for depletion.

*Quicksel*: This subroutine allows the user to select
parameter vectors with replacement from the output of the SIR algorithm (see
Section 2.6.1 for details).

**Spreadsheet EX3C.XLS (MCMC example)**

*Clear_output*: This subroutine clears the temporary
storage that will be used when applying the MCMC algorithm.

*RunMCMC*: This subroutine controls the application of
the MCMC algorithm. It contains two main steps: (a) calling the subroutine that
copies the variables from the spreadsheet to those in the macro, and (b) calling
the subroutine that actually implements the MCMC algorithm.

*Readpars*: This subroutine copies the variables from the
spreadsheet to those in the macro.

*PrintResults*: This subroutine copies the results of the
MCMC algorithm from the macro variables back into the spreadsheet.

*Func*: This subroutine calculates the product of the
likelihood and the prior for the current parameter vector.

*Mcmc*: This subroutine performs the MCMC algorithm. Each
step involves first modifying each parameter in turn based on the jump function,
computing the product of the likelihood and the prior, and applying the rules to
see whether the modified or the original value for the parameter is kept (see
Section 2.3). Every few steps, the parameters of the jump function are modified
depending on whether too many or too few parameter vectors are being kept.
Finally, the samples from the posterior are copied back to the
spreadsheet.

**Spreadsheet EX4A.XLS (MCMC example - production
model)**

The macros and subroutines for this spreadsheet are the same
as those for spreadsheet EX3C.XLS, except that the subroutines *Readpars*
and *PrintResults* have been tailored to the example in Section
3.1.1.

**Spreadsheet EX4B.XLS (Production model decision
analysis)**

*Clear_output*: This subroutine clears the storage
space.

*Projections*: This subroutine controls the construction
of the decision tables. The two main tasks of this subroutine are to call the
subroutines *Readpars* and *Project*.

*Project*: This subroutine conducts projections for each
harvest rate for each of the parameter vectors. It does this by first generating
the random variables that determine the process, assessment and implementation
error, then by copying the values for *r*, *q* and *K* from the
sample from the posterior into cells B3, B4 and B5 of the sheet "Main", and
finally by pasting each of the harvest rates into cell G8 and recording the
values for the key model outputs. The final step of the subroutine is to copy
the results of the projections into the decision tables on the
spreadsheet.

*Readpars*: This subroutine copies the values for the
control parameters from the spreadsheet into the macro.

*Normal*: This subroutine generates a random deviate from
a normal distribution with pre-specified mean and standard deviation.

**Spreadsheet EX4C.XLS (SIR example - stock and recruitment
problem)**

The macros and subroutines for this spreadsheet are the same
as those for spreadsheet EX3B.XLS, except that the subroutines *Readpars*
and *PrintResults* have been tailored to the example in Section 3.2.1. The
subroutine *Sir* has been modified so that only parameter vectors that form
part of the posterior distribution are output (see Section 2.6.1 for
details).

**Spreadsheet EX4D.XLS (Stock-recruitment model decision
analysis)**

*ProjectSR*: This subroutine conducts the projections for
each combination of a parameter vector and a harvest rate and constructs the
resultant decision table. The subroutine first clears the cells in the sheet
"Main" where the output will be stored and then copies the values for the
control variables from the spreadsheet into the subroutine's local variables.
The next phase of the calculation involves conducting the projections for each
combination of a parameter vector and a harvest rate. To do this, the parameter
vector is first copied from columns C, D and E into the cells for a, b and
*s _{r}* (cells B2, B3 and B4) and the
random numbers for process and assessment error generated and stored in cells
G15 to H64 (the random numbers are the same for each harvest rate). The
projections for each harvest rate are then conducted and the values in the
decision table updated.