The importance of natural capital to the Scottish economy: research

Appendix 9: Economic Impact Analysis Model User Guide

An MS Excel-based economic impacts analysis model has been developed as part of the study. This model helps to analyse the economic impact of applying the three methods used to measure industry natural capital reliance in Scotland. The methodology section in the main report details the methodology utilised across Methods 1-3. The purpose of this User Guide is to enable users of the economic impacts analysis model to understand how the 17 worksheets can be used and interpreted. This User Guide should be read alongside the Excel-based model.

Model Information Sheets

1. Title

This Excel worksheet is a section divider and provides the model disclaimer. There is no requirement for the model user to input into this Excel worksheet.

2. Map

This Excel worksheet details the model map and shows the worksheets in the model and how the primary data flows between them. As shown in Appendix Figure 1, the map follows the model dividers by:

1) Inputs – referring to the direct primary data inputs;

2) Calculations – worksheets that are used for calculating from the input sheets;

3) Results – the main worksheet that summarises and presents results from Calculations worksheets; and

4) WSP Title – showing the shortcut to the “Map”, the “VersionCtrl” and the “Key” worksheets.

Black arrows between each set of worksheets (Inputs, Calculations and Results) identify where data flows between worksheets. Each cell is linked to the relevant sheet and can be used to navigate across the model. For example, the model user can click on “Natural Capital Reliance” in Cell F14 and this will automatically direct the user to the “Natural Capital Reliance” worksheet.

3. VersionCtrl

This worksheet details the log of the modifications made to the workbook for Quality Assurance (QA) purposes. For this project, modification and QA details are shown. Model users who make changes to the Excel workbook can log these on this worksheet, with relevant QA protocol followed and noted.

4. Key

This worksheet details the formatting used within the workbook:

• Cells with blue hashed formatting detail where user input is used/needed. Cells detailed in this format are the only cells a user should edit throughout the workbook.
• Cells with light grey italicised text detail cells that are used for a calculation and should not be edited by a user.
• Cells with black text within a box detail either a calculation or a result and should not be edited by a user.
• Cells highlighted in yellow provide an explanation of the method and should not be edited by a user.
• Cells highlighted in green are formula check cells and will show green for pass and red for failed. These cells should not be edited by a user. For reference, however, If the cell is highlighted in red, then the user should investigate the formula further and seek to correct it.
• Cells highlighted in orange detail source information for input data. These cells should not be edited by a user.

Appendix Figure 2 provides a snapshot of the content of the “Key” worksheet.

Results Sheets

5. Results>>

This worksheet is a section divider and presents the Results section.

6. Overall Summary Results

This worksheet presents the summary results for the model and is the only worksheet where the model user should make edits to the blue hashed cells. Throughout this worksheet:

• Method 1 results are presented in dark and light red;
• Method 2 results are presented in dark and light blue; and
• Method 3 results are presented in dark and light brown.

a. There are very few areas in this worksheet where user input is required. First, Column D “Change in Final Use (£)” currently shows the total Scottish output for each industry based on Scottish Supply Use Tables. This data is drawn from in the input tab “Natural Capital Reliance”. It can be changed by the user to model any injection of capital or change in a sector’s output or expenditure. To edit this, the user can enter numerical values in Columns D24 to D121 for the given industry.

b. Second, the model user can select the REP area in Cell D11 using the dropdown selections, and results and table headings will update accordingly to reflect the results for the REP selected.

c. Cells F12:S16 present the summary table where industry results are summarised. Appendix Figure 3 below shows the summary table. This table consists of the total change in final use numbers, the output effect and the employment effect for the whole of Scotland, as well as at the REP level, across the three methods.

d. Column G “Total Change in Final Use (£)” in the summary table summarises the inputs entered by the user in, for Methods 1 and 2. Method 3 is calculated differently (see main report section “Methodology” for methodological details).

e. Cells J12:N16 detail whole of Scotland results, and Cells O12:S16 detail the Regional Enterprise Partnership (REP) results.

f. Column I “Change in Final Use Reliant on Natural Capital” in the summary table summarises results for final use that is reliant on natural capital, across the three methods. This total amount is calculated below the summary table for each industry in Cells G24:G121 for Method 1, Cells J24:J121 for Method 2, and Cells O24:O121 for Method 3. This amount is calculated according to the natural capital reliance methodology for each method, as detailed in the main report under section “Methodology”. The percentage reliance by industry is drawn from the input sheet “Natural Capital Reliance” and should not be edited by the user.

g. Column J “Total Output Effect (Direct and Indirect)” in the summary table shows the total output effect across the three methods for the whole of Scotland. This is the sum of the industries below the summary table shown in Cells Q24:Q121 for Method 1, Cells V24:V121 for Method 2, and Cells AG24:AG121 for Method 3. The output effect is calculated in the calculations sheet “Regional Breakdown”. See main report section “Methodology” for further details.

h. Column L “Total Employment Effect (direct and indirect)“ in the summary table shows the total employment effect across the three methods for the whole of Scotland. This is a sum of the industries below the summary table shown in Cells R24:R121 for Method 1, Cells W24:W121 for Method 2, and Cells AH24:AH121 for Method 3. The employment effect is calculated in calculations sheet “Regional Breakdown”. See main report section “Methodology” for further details.

i. Column M “Total Output Effect from Substitutions” and Column N “Total Employment Effect from Substitutions” in the summary table show the total output effect and employment effect for the whole of Scotland, respectively, from substitutions alone. This is only relevant for Method 3. See main report section “Method 3 Measuring reliance on natural capital ecosystem services” for methodological details.

j. Column O “Total Output Effect” in the summary table shows the total output effect across the three methods for the chosen regional area, as selected by the user in Cell D11. This is a sum of the industries below the summary table shown in Cells S24:S121 for Method 1, Cells X24:X121 for Method 2, and Cells AK24:AK121 for Method 3. The output effect is calculated in calculations sheet “Regional Breakdown”. See main report section “Methodology” for further details.

k. Column Q “Total Employment Effect” in the summary table shows the total employment effect across the three methods for the chosen regional area, as selected by the user in Cell D11. This is a sum of the industries below the summary table shown in Cells S24:S121 for Method 1, Cells T24:T121 for Method 2, and Cells AK24:AK121 for Method 3. The employment effect is calculated in calculations sheet “Regional Breakdown”. See main report section “Methodology” for further details.

l. Column R “Total Output Effect from Substitutions” and Column S “Total Employment Effect from Substitutions” in the summary table show the total output effect and employment effect for the chosen regional area, respectively, from substitutions alone. This is only relevant for Method 3. See main report section “Method 3 Measuring reliance on natural capital ecosystem services” for methodological details.

m. Industry by industry results across the three methods, both for the whole of Scotland and/or for a chosen REP, are shown in Rows 24 to 121.

Calculation Sheets

7. Calculations>>

This worksheet is a section divider and presents the calculations section.

8. Regional Breakdown

This worksheet contains all output and employment effect results across all 98 industries, all three methods, and for the whole of Scotland as well as the eight REPs. The main results worksheet “Overall Summary Results” presents results taken from this “Regional Breakdown” worksheet. The “Regional Breakdown” worksheet calculates output and employment effects using user input in the “Overall Summary Results” worksheet and the input worksheets “Regional Split” and multipliers in input worksheet “Multipliers”. To generate Method 3 results, results are also generated using the “Substitution SIC Multipliers” worksheet. Check cells in the “Regional Breakdown” worksheet are included and should display green (i.e. no errors). Cells in this worksheet should not be edited by a user.

9. Substitution SIC Multipliers

This worksheet is used to calculate substitution output and employment effects under Method 3. The sheet contains both output and employment multipliers. The worksheet feeds into the calculation sheet “Regional Breakdown”. See main report section “Method 3 Measuring reliance on natural capital ecosystem services” for methodological details. Cells in the “Substitution SIC Multipliers” worksheet should not be edited by a user.

Input Sheets

10. Inputs>>

This worksheet is a section divider and presents the inputs section.

11. Natural Capital Reliance

This worksheet is the input sheet based on WSP and eftec’s methodology establishing industry natural capital reliance. The details underlying these numbers are discussed in the main report under “Methodology”. Established industry reliance estimates for Methods 1-3 are shown and used to apportion Scottish Domestic Expenditure and Scottish Output in 2019 for each industry. Scottish Domestic Expenditure and Scottish Output for 2019 was sourced from Scottish Government: Supply, Use and Input-Output Tables 1998-2020 (https://www.gov.scot/publications/input-output-latest/). Cells in this worksheet should not be edited by a user.

12. Multipliers

This worksheet is an input sheet consisting of output and employment effect multipliers sourced from Scottish Government: Supply, Use and Input-Output Tables 1998-2020 (https://www.gov.scot/publications/input-output-latest/). Multipliers are used to calculate output and employment effects. Cells in this worksheet should not be edited by a user.

13. Regional Split

This worksheet is an input sheet consisting of employment by industry for the whole of Scotland and the eight REPs, and is used to apportion results to regions. This is sourced from the Office for National Statistics (ONS) Business Register and Employment Survey, available on the Nomis platform (https://www.nomisweb.co.uk/query/construct/summary.asp?mode=construct&version=0&dataset=189). The Imputed Rent industry (Row 77) uses gross value added (GVA) balanced data sourced from ONS Regional Accounts (https://www.ons.gov.uk/economy/grossdomesticproductgdp/datasets/regionalgrossvalueaddedbalancedbyindustrylocalauthoritiesbyitl1region). Cells in this worksheet should not be edited by a user.

14. Lists

This worksheet is an input sheet and data feeds into the worksheets under “Results>>” and “Calculations>>”. Cells in this worksheet should not be edited by a user.

15. SIC Descriptions

This worksheet is an input sheet detailing Standard Industrial Classification (SIC) code hierarchies and is used in the worksheets under “Calculations>>” sheets. Cells in this worksheet should not be edited by a user.

16. Substitutions_All

This worksheet is an input sheet detailing all 98 industry ecosystem service substitutions, and the mapped SIC codes that are relevant. Column B outlines the industry, Column C the relevant process, Column D the relevant ecosystem service, and Column E the human capital and/or nature-based solution. Column F onwards shows all the relevant mapped 3 digit SIC codes. See main report section “Method 3 Measuring reliance on natural capital ecosystem services” for methodological details. Cells in this worksheet should not be edited by a user.

17. Substitutions SIC Consolidated

This worksheet is an input sheet consolidating all the mapped SIC codes by industry for substitutions. This sheet ensures there is no SIC code that is double counted for an industry and is used in worksheets under “Calculations>>”. Cells in this worksheet should not be edited by a user.