Compiler Report - Ministry of Forests, Lands & Natural Resource
Contents
1. 4L 65536 0 PRODUC T SUMIF AH 2001 AH 65536 gt 5 4L 2001 AL 65536 SUMPRODUCT AH 2001 AH 65536 gt 5 AL 2001 AL 65536 lt gt This function determines if the sum of all pine ages where the pine is greater than or equal to 50 is greater than 0 and then sums up these ages and divides by the number of samples to get an average age To get the total number of ages SUMPRODUCT AH 2001 4H 65536 gt 5 AL 2001 AL 65536 lt gt is used This forces all pines percentages greater than or equal to 50 to become 1 and all ages to also become 1 All other values become 0 These columns are then multiplied and the resulting column is summed producing the number of ages for pine gt 5 e Age is determined in this manner for each species but only the age of the leading species is used Part 12 Inventory Height coloured tan e Inventory height is determined using the same function as for age part 11 but with different cell references Part 13 Site Index coloured turquoise e Site index is also determined using the same function as for age but the cell references are for the SI inventory data Part 14 Crown Closure coloured tan e This calculation is a simple average of all crown closure values entered Part 15 Total Trees per Hectare coloured turquoise e Confidence interval statistics at the 95 level are run on the total tree column producing a mean and a lower and upper confidence limit P2. spruce spruce values are used if it is a mixed stratum the SI values for pine spruce and balsam are used Note to use pine and spruce but not balsam only one formula needs to be changed e IfSlIis outside of the range 15 25 the cell will appear red in the results section Part 2 MSQw and MSOf Statistics coloured tan e During data collection in the field surveyors were required to assess whether there was an acceptable tree that stocked the quadrant that was free growing MSQf If there was a tree but it was not free growing it was considered to be MSQw e In this section of the worksheet means for the stratum for these two values are calculated Report on Compiler Function and Derivation Predicted merchantable volume PMV is then calculated different section of the sheet using both MSQw and MSOf The upper and lower confidence intervals are also calculated for both MSQw and MSOQf using a 95 confidence interval Part 3 Tree Height Statistics coloured light turquoise The mean upper and lower confidence intervals are calculated for the height of the leading species in each stratum Tree heights come from sample tree heights and not the inventory heights The heights are copied into columns on the right of the data table on the results sheet in order to place them under the appropriate species rather than sample tree 1 2 and 3 If the stand is either pine or spruce only the stats for those species are pas
3. three steps 1 Enter the plot data from the field into the Data worksheet click the data tab or create a separate spreadsheet like the one provided in the compiler ensuring that the column headings and arrangement are the same at least up to the columns on brush conditions and then paste it into the data worksheet Note that the stocking gaps column is used to determine OAF1 values at the MoF website noted above 2 Click on the compiler tab to get to the compiler worksheet and enter a name for the compilation typically the stratum number select a stratum number from the drop down menu select plot type from the drop down menu v for variable or leave it blank to use all data select method for determining site index from the drop down menu growth intercept or SIBEC and enter the decimal value typically 0 15 for OAF1 3 Click on the Compile button Clicking on the compile button will create a new worksheet a separate tab summarizing the results for that stratum To get the results for other strata repeat steps two and three Report on Compiler Function and Derivation above To view the results for the entire population all strata combined click on the volumes tab the volume summary worksheet Note that strata worksheets can be deleted but the corresponding information in the volume table in the volume worksheet will not be deleted unless this is done manually The results worksheet where all the underlying calcu
4. Report on Compiler Function and Derivation OVERVIEW In 2005 the Morice Lakes IFPA members led by Canfor proposed developing a multi block survey system and landscape level stocking standards to measure reforestation performance within the IFPA The essence of the system is assessing regeneration performance 10 to 15 years after harvest to determine if across a series of cutblocks the predicted future volume from these stands exceeds a target future volume possible with an aggressive silviculture regime Regeneration would be considered successful if for the entire population of blocks predicted future volume exceeded target future volume regardless of whether there were some sections with low or no stocking within the population Perceived benefits included e Flexibility to invest first in those treatments sites which yield greatest gains at least cost e Opportunity to better allocate area or emphasis to biodiversity objectives A better link between reforestation effort and timber supply analysis e A potential opportunity to link AAC allocations to regeneration performance e Government review and tracking at the multi block level e Reduced survey costs Funding was provided in 2005 through the Forest Investment Account to undertake this work A fundamental aspect of the project was to develop a compiler to calculate target future volume and predicted future volume and confidence intervals by strata from early stand conditio
5. Slope and spacing are lookup values in the table at the top of the sheet e Currently OAF is not accounted for anywhere In the 604 field plots there was only instance of a stocking gap OAF1 OAF value can be easily added as either a column in the data or as part of the lookup table at the top of the page Part 20 BEC Zone coloured tan e This function simply counts all the BEC zones listed within the plots and then makes the one with the highest count the leading BEC Zone Part 21 Site Series coloured turquoise e This function is very similar to BEC Zone except it also calculates the second and third leading site series A drawback is the lack of percentages for each stratum although this could potentially be added in the future e If the count of site series present is the same within 2 site series it will name the one higher on the list as the 2nd site series and the one lower on the list as the 3rd the 2nd and 3rd are just examples could be Ist and 2nd For example if the count of site series for 01 there is 20 02 is 5 and 06 is also 5 the results will be 1 01 292 and 3 05 This could be changed if deemed necessary so that site series which occur more often e g 05 and 06 move to the top of the list Part 22 Volume Summary e Information on predicted merchantable volume PMV for well spaced and free growing MSQ values and target merchantable volume TMV for well spaced and free growing MSQs is copied to the volume s
6. acro sheet simply close the window In the future it is expected that a simpler user interface will be developed This will take the form of an import function that allows the user to take existing data from a multi block stocking survey or other silviculture survey and manipulate it through the compiler to produce a data worksheet that can be used by the compiler DESCRIPTION OF RESULTS WORKSHEEPT FUNCTIONS This version of the compiler requires the user to have an basic understanding of stocking survey information statistics operational adjustments factors used in TASS spreadsheet use and the protocol for the multi block stocking survey It does not require the user to understand the details of the compiler calculations However for those wanting to check the validity of the functions or who might want to modify the compiler for their own application the following explanations for the calculations are provided There are 25 basic parts 1 Stratum Information Mean Stocked Quadrant information Tree Height statistics Effective Age EA calculations Mean Predicted Merchantable Volume PMV calculations Lower Confidence Limit for PMV calculation ON Report on Compiler Function and Derivation 7 Upper Confidence Limit for PMV calculation 8 Error calculations 9 Target Merchantable Volume TMV calculations 10 Inventory Label determination 11 Inventory Age determination 12 Inventory Height determi
7. art 16 Total Conifers per Hectare coloured tan e This statistic is used for the second type of compilation in which future yield is predicted based on conventional stocking information generated from silviculture surveys not MSQ Because of the range of stocking possible in a particular stratum some measure of confidence is required Confidence interval statistics at the 95 level are therefore run on the total conifer column producing a mean and lower and upper confidence limit This information will be used in TIPSY to determine PMV and TMV Part 17 Total Well Spaced Trees per Hectare coloured turquoise e Information on well spaced trees is found in the data worksheet where the number of well spaced trees for each plot is recorded including those over the M value To account for M values the well spaced values are copied to a column to the right of the data on the results sheet Here if the well spaced number is greater than the TSS 200 the value becomes TSS 200 otherwise the actual well spaced number is entered Report on Compiler Function and Derivation e Confidence interval statistics at the 95 level are run on the well spaced column producing a mean and a lower and upper confidence limit Part 18 Total Free Growing Trees per Hectare coloured tan e Calculations for free growing stems are the same as for well spaced part 16 but using the FG column Part 19 Slope Spacing and OAF1 coloured turquoise e
8. ecause the equations are the same for both the following explanation is provided for PMVw only Site Index this step is used to facilitate interpolation between SI values see bullets under stand type below since the look up tables for PMV coefficients only work on a site index of 15 20 and 25 When a mean site index value falls between these numbers the upper and lower limit of the range is identified For example if SI is 19 the upper SI is 20 and the lower SI is 15 If the SI is less than 15 or greater than 25 a PMV value can not be calculated Actual EA Like site index this step is used to facilitate interpolation between SI values see bullets under stand type below since the look up tables for PMV Report on Compiler Function and Derivation coefficients only work for effective ages of 5 7 10 and 13 If the SI is less than 5 or greater than 13 a PMV value cannot be calculated e Stand Type and MSQ values these are just copied from cells calculated in Parts 1 and 2 above e Because there are two possible values for SI and two for EA for each stratum four equations are potentially needed to get PMV e Equation 1 in the worksheet uses the higher SI and the higher EA e Equation 2 uses the higher SI and the lower EA e Equation 3 uses the lower SI and the higher EA e Equation 4 uses the lower SI and the lower EA e The equation used to calculate PMV has the form PMV a b MSQ c MSQ where a b and c are coeffic
9. formation gaps recommendations regarding implementation al es
10. ients The equation and coefficients were based on a regression analysis of simulations of stand yield produced by JS Thrower and Associates using the Ministry of Forests Tree and Stand Simulator TASS 212 3 ha TASS simulations were run with varying site indices 15 20 25 species compositions 0 to 100 of Fdi Pl or Sx planting density 400 to 1400 natural ingress density 400 to 8 000 spatial distribution random vs clumped and ingress periods They produced stem maps for ages 5 7 10 and 13 as well as a yield table for each stand listing volume for the first 15 years and every five years after that to age 120 They then simulated 30 different surveys on each of the 2 212 stands for each stem map 4 resulting in 265 440 surveys In each survey they simulated 48 plots noting species and height of tree in each stocked quadrant to get MSQ for various stand conditions A range of site index harvest age and species combinations were assessed using regression analysis to fit curves to this data 216 curves were produced 18 sets of 12 anamorphic curves one for each species and site index combination e The equations for various combinations of effective age and site index described in the bullets prior to the last bullet are used together with stand type to lookup the values for the coefficients a b and c The MSQ value for the stratum determined above and these coefficients are then used to calculate PMV e In excel the PMV equa
11. in this mode other than for testing formulae as the resulting file size will be quite large and this will slow the compiler down Excel add ins are required e Before running compiler it is necessary to ensure that three add ins are installed To install the add ins select Tools Add Ins and select Analysis Toolpak Analysis Toolpak VBA and Lookup Wizard and follow the instructions e The next time you open excel it will ask if you want to run a macro before you open a file Click Enable Macros e The user will also have to enable macros when opening the compiler Without macros it will not work Output is only as good as the input I2 Report on Compiler Function and Derivation e Like any model the results will only be useful if field data has been carefully collected and entered without errors There are few quality control algorithms built into the model This will need to be done during data entry and collection Results also need to be reviewed to see if they make sense NEXT STEPS e This report was provided to accompany the compiler to help users understand how to use it It is not a project report The full project report is also needed to address O O O O O rationale for the project a summary of the survey procedure a more succinct summary of the modelling process an analysis of results from the two standards and two survey methodologies including an analysis of cost a summary of issues and in
12. lations are performed will not normally be visible to the user There are however two ways to view these functions The first way is to go to format sheet unhide and click results when in any of the worksheets This will automatically take the user to the top of a new worksheet called Results where all the underlying functions and lookup tables can be seen Lookup tables are grey and sections of the worksheet with functions are highlighted with tan or blue A second way to reveal the functions and algorithms used in the compiler is to use test mode see also Model Complexity below This feature of the compiler was created to allow advanced users to check how the equations are treating the data To enter into test mode go to the compiler worksheet and scroll down to cell C1001 and select or type in test Scroll back up to cell B3 and enter the name of the compilation stratum number plot type site index method and OAF value Click on the compile button This will automatically take the user to the stratum worksheet where the results are shown at cell A1000 Scrolling up to the top of the worksheet the user will find the lookup tables and functions described above A user can also check the macro used to manage functions such as copying information to the results page running the statistics managing naming conventions To do this simply go to the compiler worksheet and click tools macro macros compiler macro edit To get out of the m
13. n 1 and 2 the formula takes the biggest PMV value and subtracts the smallest PMV divides this by the difference in the lower and upper SI this is always 5 and then multiplies the value by the difference between the actual SI and the lower SI i e if SI is 19 the difference between 19 and 15 is 4 The formula then adds the PMV value for the lower SI The formula is PMVupper SI PMV lower SI Actual SI Lower SI Lower PMV Upper SI Lower SI o For Option C both the lower and upper EA s and SI s are different This means all 4 equations will have different values The linear interpolation process to arrive at PMV is similar to option B except it is repeated twice as follows IF B 168 C B 155 B 153 B 155 ROUND PRODUCT B 166 B 167 B 158 B 159 B 157 B 159 MIN B 166 B 167 4 4 N A the yellow part of the equation calculates the PMV using the upper SI and the actual EA like in the option B equation and the blue and grey parts both calculate the PMV value for lower SI and the actual EA similar to option A The whole equation together is the same as the last equation on page 26 of the JS Thrower July 5 2004 report 1 PMVupper EA PMV lower EA Actual EA Lower EA Lower PMV Upper EA Lower EA 2 PMVupper EA PMV Lower EA Actual EA Lower EA Lower PMV Upper EA Lower EA 1 2 Actual SI Lower SI Lower SI PMV 2 Upper SI Lower SI Rep
14. nation 13 Site Index determination 14 Crown Closure determination 15 Total Stems per Hectare calculation 16 Total Conifers per Hectare calculation 17 Well Spaced Trees per Hectare calculation 18 Free Growing Trees per Hectare calculation 19 Slope Spacing and OAF1 determination 20 BEC Zone determination 21 Site Series determination 22 Volume Summary 23 Summary of Results 24 Data Organization and 25 the Compiler Macro Each part is contained within a Results worksheet that remains hidden unless one of the two methods described above is used to reveal them Part 1 Stratum Information coloured light turquoise e The majority of this information in this area comes from a lookup table This lookup table is just a copy of the table entered into the stratum description sheet e The stratum number plot type and site index SI method is based on the information that is entered into the compiler sheet e If SIBEC is entered on the compiler sheet the SI value is selected from a table note SIBEC lookup starts at row 453 The SI value is based on BEC zone site series and leading species for the stratum e If Growth Intercept GI is entered on the compiler sheet the SI value is calculated as an average of the site index values from the sample trees in the stratum not the inventory SI If the stratum is a pine leading stratum only pine GI values are used if it is
15. ns The compiler was produced using Microsoft Excel in the winter of 2005 2006 This report is a form of user s manual developed to explain how the compiler works It does not replace the project report which will be produced once feedback on the compiler and its utility has been received The compiler has been developed to produce information for two basic types of compilation stand yield from mean stocked quadrant MSQ data and stand yield from conventional silviculture survey data stems ha The first type of compilation represents an adaptation of a system developed by JS Thrower and Associates for Riverside Forest Products in Kelowna Stand Survey and Growth Modelling for the TFL 49 Results Based Pilot Project Year Three Final Report July 2004 The second type of compilation is a different approach using conventional stocking survey information that is meant to more closely parallel timber supply analysis The rationale for these two approaches is provided in the technical report for this project Before the compiler is run there are five worksheets Once a compilation has been run a number of new sheets are automatically created depending how many strata are involved The initial sheets include A Data Worksheet e Where plot information is entered A Stratum Description Worksheet e Where specifications for each stratum are provided Before a stratum can be compiled this section must be completed including OAF1 values that Rep
16. ort on Compiler Function and Derivation The final PMV is selected from option A B or C depending on whether all PMV values are the same option A there are two different values option B or all 4 values are different option C Part 6 Lower CL PMV coloured tan e There are three sources of error contributing to the variance of PMV sampling error for the estimate of MSQ sampling error for the estimate of effective age and prediction error from the equations produced by Thrower from the regression analysis Parts 6 7 and 8 of this compiler address these sources of error In this part lower confidence interval for PMV is calculated using the same set of equations used in calculating Mean PMV part 5 but the lower confidence limit for EA and MSQ are used instead of the mean Part 7 Upper CL PMV coloured light turquoise e In part seven the upper confidence interval for PMV is calculated using the same set of equations used in calculating Mean PMV part 5 but the upper confidence limit for EA and MSQ are used instead of the mean Part 8 Error Calculations coloured tan e In part eight steps 7 12 on page 20 of the JS Thrower report Thrower July 5 2004 are built into the spreadsheet to determine the 95 confidence interval for predicted merchantable volume for each stratum as follows o Step 7 sampling variance POWER PRODUCT B 220 B 197 4 2 o Step 8 prediction variance Linear interpolation was
17. ort on Compiler Function and Derivation are computed for each stratum using the calculator found at the MoF website http www for gov bc ca hfp silviculture OAF1 default htm A Compiler Worksheet e This sheet provides the compiler with instructions on how to run including i the name for the compilation this will be the name for the new worksheet created ii the stratum to compile iii the plot type uniform or variable and site index method used if growth intercepts have been used to obtain site index it will use the site index recorded for the plot otherwise it will use lookup tables for the provincial SIBEC correlations to obtain site index from the site series noted in the plot iv aspot to enter an OAF 1 value for the target merchantable volume calculation Volumes Worksheet e Where the final result of the compilation are summarized volume summaries from all strata compiled Results Worksheet e Where all calculations are performed Once the calculations have been completed a copy of the worksheet is made and the new sheet is renamed according to the value that was entered on the Compiler page e This sheet remains hidden at all times e Colour coding is used on the results worksheet to help in interpretation as follows areas with calculations will alternate between light turquoise and tan and lookup tables will be grey BASIC USE OF THE COMPILER The compiler can be operated with little input from the user There are
18. ted However if the stand is a mix then stats for all conifers is pasted and then either pine value or spruce value is chosen depending on which one is leading species Part 4 Effective Age coloured tan Effective Age EA is a function of top height actual age site index and species It is determined from juvenile height growth models developed by the Ministry of Forests Nigh and Love 1999 and is used to compensate for differences in early stand development A 10 year old stand that is 0 5 m shorter than what would normally be expected at that age might have an effective age of only 8 years for example The mean upper and lower effective ages are calculated using the mean upper and lower tree height calculations from part 3 above The lookup table used is the same table as on page 64 and 65 of the July 5 2004 JS Thrower report The effective age tables are only for pine and spruce so if the leading species is a different species then the effective age will return an INVALID SPECIES and the compilation will not return correct data If effective range is outside of 5 13 the cell will appear red in the results section Part 5 Mean Predicted Merchantable Volume coloured turquoise This part of the sheet calculates predicted merchantable volume PMV using MSQw or MSQf Input variables include SI effective age stand type and MSQ values Functions for PMVw and PMVf are the same except PMV w uses MSQw and PMVf uses MSQf B
19. tion was as follows o ROUND SUM E 157 PRODUCT E 158 E 160 PRODUCT SE 159 PRODUCT SE 160 E 160 4 o which is o A B MSQ MSQ C e This was done for all 4 equations to produce 4 different MSQ values e The next step had three options as documented on page 25 of the JS Thrower July 5 2004 report o Option A is used when both the upper and lower SI are equal i e SI equals 15 20 or 25 and both upper and lower EA are equal i e EA equals 5 7 10 or 13 If this case all 4 equations above produce the same value so the value from equation is used as PMV any of the four could have been used but the first one was chosen o Option B is when only one set of the upper and lower numbers match for example SI is 20 but EA is 8 In this case linear interpolation between Report on Compiler Function and Derivation the 2 different values from the different EA is required To do this in excel the following formula was used IF B 168 B IF B 154 lt gt B 155 ROUND PRODUCT M AX B 164 B 167 MIN B 164 B 167 B 154 B 155 B 153 B 155 MIN B 164 B 167 4 IF B 158 lt gt B 159 ROU ND PRODUCT MAX B 164 B 167 MIN B 164 B 167 B 158 B 159 B 157 B 159 MIN B 164 B 167 4 N A N A The 4 equations should produce 2 different values in this case so the algorithm looks to see where the different values are i e between equation and 2 or equation 3 and 4 If it is betwee
20. ummary worksheet The only calculation in this section is that volumes are multiplied by area to get total volumes Part 23 Summary and Results e This section of the compiler starts at row 1000 of the results worksheet and is simply where all the results get reported out and subsequently copied to the stratum worksheet Part 24 Data e Input data is copied from the data worksheet and pasted into the results section starting at row 2001 Part 25 The Compiler Macro e Each step of the macro is explained within the macro itself see tools macro macros compiler macro edit The macros main function is to 10 Report on Compiler Function and Derivation identify which stratum is being compiled copy plot information to the results page run the statistics described above copy the volumes to the volume summary page rename the worksheet as the compilation name and save the worksheet e The macro also ensures that the Compiler Stratum Description Volumes and Results sections remain protected This allows only cells that should be altered to be altered AW awry i S Report on Compiler Function and Derivation LIMITATIONS OF THIS VERSION OF THE COMPILER Species the compiler can deal with include PI Bl Sx and At only e Itis possible with a few revisions to adapt the model to accept other species but this was viewed as low priority for the current version SIBEC site index functions were produced onl
21. used to extract the range of values provided in Table 9 from the Thrower report This was used to produce a lookup table for variance based on species and SI Step 9 not required because it is covered in step 8 Step 10 combined variance SUM B 238 POWER B 237 2 Step 11 calculate PMV see parts 4 5 and 6 Step 12 95 confidence interval This is the mean PMV 2 SE This step yields the upper and lower confidence interval for PMV These numbers are displayed on the final results sheet O00 0 Part 9 TMV calculation coloured turquoise e Target Merchantable Volume is calculated using the same set of equations used in calculating Mean PMV except EA is fixed at 10 and MSQ is fixed at four There is also an OAFI adjustment It is possible to change the OAF adjustment factor on the compiler page Part 10 Inventory Label coloured tan e In this section the means for all the percentages entered for each species in the inventory component of the data entry page are calculated These percentages are then corrected to make sure they add up to 100 This is done by adjusting the leading Species up or down as required The final percentages are then used to rank the species and produce the species composition for the label Report on Compiler Function and Derivation Part 11 Inventory Age coloured turquoise e Inventory age is calculated using the following function e IF SUMIF AH 2001 AH 65536 gt 5 4L 2001
22. y for SBSdk SBSmc2 and ESSFmc because this is where the majority of timber harvesting has occurred e Additional BEC zones can be added relatively easily the main barrier being getting the numbers in a format that works The model assumes that values from the field for site index were based on the growth intercept method e Although it was assumed that SI values from the field were based on growth intercept measurements some may have actually been based on SIBEC correlations The ranges for Site Index used to lookup PMV equation coefficients and for Effective Age are limited to those produced by Thrower 2004 e These ranges are 15 to 25 and 7 to 13 respectively for SI and EA Data collected during the trial for this project indicate that there will be instances where LCLs for strata averages will fall outside these ranges In this version of the compiler the lowest or highest limits are used but in future versions it will be necessary to develop a wider range Model complexity e The model is quite complex and adaptation will require a user to be very familiar with logic statements and macros Caution should be used One of the design features in the model is a test mode which allows users to check all equations In this mode the compiler will only use a minimum number of required cells without changing the formulae To enter into test mode enter test into cell C1001 on the compiler worksheet The compiler should not be run
Download Pdf Manuals
Related Search