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 sr (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.
