11 SPREADSHEETS WITH EXAMPLES AND EXERCISES

The Examples described in Chapter 10 and additional Exercises can be found on the Compact Disk that is provided with this manual. For further explanations please consult the file Software.doc.

The methods explained are for illustration only and should not be taken as an endorsement of any particular method or software. In some of the examples, data presented were extracted from ICES assessment working group reports. These data do not represent all information available for stock assessment, as they were selected to illustrate the methods only. The results differ from the ICES stock assessment results and should not be used for anything other than illustrating calculations.

It is hoped that the users will be given enough insight to evaluate methods for themselves and see whether any technique is applicable to their own data and fisheries. All software routines are written in Visual Basic for Applications and the exercises and examples are given in Microsoft Excel (Office 97 Service release 2, or later).

EXCEL SPREADSHEETS OF EXAMPLES AND EXERCISES

Example 10
Example 10 Bootstrap

These worksheets contain the example models, described in Chapter 10 of the manual, including Visual Basic for Applications routines.

Acoustic Survey

The ADAPT model uses an acoustic survey as the abundance index. The method is the same as that used for other indices, except that we can treat the survey as an absolute estimate, so the parameter adjusting the proportionality of the index, q, can be ignored.

ADAPT with 1 index

Make a spreadsheet to perform an ADAPT estimation of stock size and fishing mortalities for Baltic cod in Sub-Divisions 25 - 32 using Pope’s cohort analysis. Use only 1 CPUE observation series as the abundance index.

ADAPT with 2 indices

Perform an ADAPT on North Sea Cod data with external weighting on 2 surveys. Use backward extensioni.e. F(10) =average F(7-9). Use a given external weighting for the sum of squares of each series.

Estimate F_med

Make a spreadsheet to calculate F_med for Baltic cod in Sub-Divisions 25 - 32. Use the Thompson and Bell procedure for calculating the SSB per recruit.

Estimate F_msy F_crash

Estimate the reference point fishing mortalities for maximum yield (F_msy) and the point where the fishery becomes unsustainable (F_crash).

Fit Stock_Recruit Model

Fit Beverton and Holt, and Ricker models to the Baltic Cod Subdivisions 25-32 estimates of R(age2) and SSB. The data on Recruitment and SSB are the output of an ICA analysis of the data for Baltic Cod Subdivisions 25-32

ICA

Carry out an Integrated Catch-at-age Analysis (ICA) for Baltic cod in Sub-Divisions 25 - 32 with one abundance index. Note that ICA uses a separable VPA model.

ICA Missing Catch

Make a spreadsheet program to perform an ICA for Baltic cod in Sub-Divisions 25 - 32 treating 1991 as having no observed catch. This can be done by splitting the model into a separable VPA 1991 onwards and a normal tuned VPA before 1991. The separable VPA uses an estimated exploitation rate to provide an estimate of the catch.

PopeCohortAnalysis

Make a spreadsheet to perform a VPA for Baltic cod in Sub-Divisions 25 - 32 using Pope’s cohort analysis.

Retrospective

Make a spreadsheet to perform a retrospective analysis for Baltic cod in Sub-Divisions 25 - 32 using ADAPT framework.

Sensitivity

Make a spreadsheet to perform a VPA for Baltic cod in Sub-Divisions 25 - 32 using Pope’s cohort analysis and carry out a sensitivity analysis on the results.

SeparableVPA

Make a spreadsheet to perform a Separable VPA for Baltic cod in Sub-Divisions 25 - 32.

ShortTermProject

Set up a spreadsheet to project stock status variables into the short term for Baltic Cod including TAC. The model should be ordinary VPA.

StandardiseCPUE

Set up a Log-linear model to standardise CPUE indices (Cpue RV surveys data for Baltic cod in 25 - 32 subdivisions 1990 -1996). The regression will require dummy x-variables. Use the Analysis Tools regression add-in.

The spreadsheet models are complicated and not easy to follow in all cases. Flow diagrams have been given where appropriate. Another tool which is useful for tracing cell links is the Audit add-in provided with Excel. This will be worth using to see how cells are calculated.

Example 10 Example 10 Bootstrap	These worksheets contain the example models, described in Chapter 10 of the manual, including Visual Basic for Applications routines.
Acoustic Survey	The ADAPT model uses an acoustic survey as the abundance index. The method is the same as that used for other indices, except that we can treat the survey as an absolute estimate, so the parameter adjusting the proportionality of the index, q, can be ignored.
ADAPT with 1 index	Make a spreadsheet to perform an ADAPT estimation of stock size and fishing mortalities for Baltic cod in Sub-Divisions 25 - 32 using Pope’s cohort analysis. Use only 1 CPUE observation series as the abundance index.
ADAPT with 2 indices	Perform an ADAPT on North Sea Cod data with external weighting on 2 surveys. Use backward extensioni.e. F(10) =average F(7-9). Use a given external weighting for the sum of squares of each series.
Estimate F_med	Make a spreadsheet to calculate F_med for Baltic cod in Sub-Divisions 25 - 32. Use the Thompson and Bell procedure for calculating the SSB per recruit.
Estimate F_msy F_crash	Estimate the reference point fishing mortalities for maximum yield (F_msy) and the point where the fishery becomes unsustainable (F_crash).
Fit Stock_Recruit Model	Fit Beverton and Holt, and Ricker models to the Baltic Cod Subdivisions 25-32 estimates of R(age2) and SSB. The data on Recruitment and SSB are the output of an ICA analysis of the data for Baltic Cod Subdivisions 25-32
ICA	Carry out an Integrated Catch-at-age Analysis (ICA) for Baltic cod in Sub-Divisions 25 - 32 with one abundance index. Note that ICA uses a separable VPA model.
ICA Missing Catch	Make a spreadsheet program to perform an ICA for Baltic cod in Sub-Divisions 25 - 32 treating 1991 as having no observed catch. This can be done by splitting the model into a separable VPA 1991 onwards and a normal tuned VPA before 1991. The separable VPA uses an estimated exploitation rate to provide an estimate of the catch.
PopeCohortAnalysis	Make a spreadsheet to perform a VPA for Baltic cod in Sub-Divisions 25 - 32 using Pope’s cohort analysis.
Retrospective	Make a spreadsheet to perform a retrospective analysis for Baltic cod in Sub-Divisions 25 - 32 using ADAPT framework.
Sensitivity	Make a spreadsheet to perform a VPA for Baltic cod in Sub-Divisions 25 - 32 using Pope’s cohort analysis and carry out a sensitivity analysis on the results.
SeparableVPA	Make a spreadsheet to perform a Separable VPA for Baltic cod in Sub-Divisions 25 - 32.
ShortTermProject	Set up a spreadsheet to project stock status variables into the short term for Baltic Cod including TAC. The model should be ordinary VPA.
StandardiseCPUE	Set up a Log-linear model to standardise CPUE indices (Cpue RV surveys data for Baltic cod in 25 - 32 subdivisions 1990 -1996). The regression will require dummy x-variables. Use the Analysis Tools regression add-in.