Decision Methods for



Chapter 1

______________________________________________________________________________

Introduction

This volume is a companion to our textbook, Decision Methods for Forest Resource Management, containing detailed answers to the problems at the end of each chapter of that book. These problems were designed to provide students with practice in the formulation and use of models – both in algebraic and spreadsheet form.

Although the use of Microsoft Excel is assumed throughout, other spreadsheets can certainly be used to formulate and solve these models.

Spreadsheet models to solve relatively complex forest resource management problems using a relatively small set of spreadsheet functions. The models in Decision Methods for Forest Resource Management have all been written using the short list of functions shown in Table 1.1. Instructors may wish to consider using Table 1.1 as a handout, especially for students who have relatively little spreadsheet experience. Such students may also need some help with the concepts of absolute and relative cell references, and on how Excel adjusts relative cell references when copying a formula from one cell to another. Although Table 1.1 is based on the descriptions of functions in Microsoft’ Excel’s Help feature, it is no substitute for getting students to know how to use that feature.

The spreadsheets shown in this volume follow the same formatting conventions used in Decision Methods for Forest Resource Management, and following these conventions can help facilitate communication between students and instructors:

• Express input data in bold.

• Show the units for parameters and computed values, preferably in parentheses to distinguish them from other kinds of labeling text.

• Show formulas, the cells in which they were entered, and the ranges over which they have been copied at the bottom of a spreadsheet. To display a formula, copy the contents of the cell in which it appears and paste it into a cell between apostrophes and preceded by the “=” sign, e.g., =”=SUM(A4:B7)” will display as =SUM(A4:B7). The readability of this section is improved by filling the cells with “white” using the “paint bucket”.

• But a bold border around a cell that is to be maximized or minimized, and enter “max” or “min” in an adjacent cell.

• Enter parameters that you want to perform sensitivity analysis on in a cell, rather than embedding them in the equations of different cells that use them. Although we have made little use of named variables, students may find this way of referring to such parameters useful.

We welcome comments and suggestions. Please, send them by e-mail to: jbuongio@facstaff.wisc.edu, or gilless@nature.berkeley.edu. Thank you.

Joseph Buongiorno

J. Keith Gilless

Table 1.1. Excel worksheet and mathematical functions.

|Function |Syntax & use |

|Data tables |A data table is a range of cells that shows how changing certain values in your formulas affects the results of the |

| |formulas. Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and |

| |compare the results of all of the different variations together on your worksheet. |

| | |

| |You must design one-variable data tables so that input values are listed either down a column (column-oriented) or |

| |across a row (row-oriented). Formulas used in a one-variable data table must refer to an input cell. Two-variable |

| |data tables use only one formula with two lists of input values. The formula must refer to two different input |

| |cells. |

| | |

| |Application: One-variable data tables are used in Chap. 14 to show how net present value or landscape diversity of |

| |an uneven-aged forest change as the cutting cycle changes. Two-variable data tables are used in Chap. 14 to show how|

| |landscape diversity of an uneven-aged forest changes for different combinations of q ratios and desired numbers of |

| |trees. |

|IF |=IF(logical_test,value_if_true,value_if_false) |

| | |

| |The IF worksheet function checks a condition that must be either true or false. If the condition is true, the |

| |function returns one value; if the condition is false, the function returns another value. The function has three |

| |arguments: the condition you want to check, the value to return if the condition is true, and the value to return if|

| |the condition is false. |

| | |

| |Application: Used in Chap. 14 to determine if a harvest has occurred (the logical test is if harvest area is greater|

| |than zero), and to return the fixed cost of harvesting if this logical test is true, and zero if it is false. |

|INDEX |INDEX(array, column_num) |

| | |

| |Array is a range of cells. Column_num selects the column in array from which to return a value. |

| | |

| |Application: Used in Chap. 13 to identify the return associated with a decision which is stored in a given column in|

| |an array. |

|MATCH |MATCH(lookup_value,lookup_array,match_type) |

| | |

| |Returns the relative position of an item in an array that matches a specified value in a specified order. You use |

| |MATCH instead of LOOKUP (defined below) when you need the position of an item in a range instead of the item itself.|

| | |

| |Lookup_value is the value you use to find the value you want in a table. Lookup_value can be a value (number, text, |

| |or logical value) or a cell reference to a number, text, or logical value. Lookup_array is a contiguous range of |

| |cells containing possible lookup values. Match_type specifies how Microsoft Excel matches lookup_value with values |

| |in lookup_array. Match_type is the number -1, 0, or 1. If match_type is 0, MATCH finds the first value that is |

| |exactly equal to lookup_value. Lookup_array can be in any order. |

| | |

| |Application: Used in Chap. 13 to find out which cell in an array of numbers holds the largest number in that array, |

| |so that the column label of that cell (which identifies the decision that would return that largest value) can be |

| |recovered. |

|MAX |MAX(number1,number2,...) |

| | |

| |Returns the largest value in a set of values or a range of cells. |

| | |

| |Application: Used in Chap. 9 to determine how many trees to cut in a given size class – zero, or the difference |

| |between the desired number and actual number (which might be a negative number). |

|MIN |MIN(number1,number2, ...) |

| | |

| |Returns the smallest value in a set of values. |

| | |

| |Application: Used in Chap. 13 to identify the smallest probability of failure out of a set of probabilities |

| |associated with different possible decisions. |

|MMULT |MMULT(array1,array2) |

| |Returns the matrix product of two arrays. The number of columns in array1 must be the same as the number of rows in |

| |array2, and both arrays must contain only numbers. The result is an array with the same number of rows as array1 and|

| |the same number of columns as array2. |

| |Application: Used in Chaps. 16 and 17 to multiply a state array of the number of trees in each size class in a |

| |forest under uneven-aged management by a matrix of transition probabilities to predict the state in the next period.|

| |Note: Excel displays this formula in braces that the user doesn’t type. |

|RAND |RAND( ) |

| |Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is |

| |returned every time the worksheet is calculated. To generate a random real number between a and b, use: |

| |RAND()*(b-a)+a |

| | |

| |Applications: Used in Chaps. 15 and 17 to characterize the variation in stochastic variables like prices, growth, or|

| |the timing of discrete events like storms. |

|STDEV |STDEV(number1,number2,...) |

| | |

| |Estimates standard deviation based on a sample contained in a range of cells. The standard deviation is a measure of|

| |how widely values are dispersed from the average value (the mean). |

| | |

| |Applications: Used in Chaps. 15 and 17 to characterize the variation in key variables observed in simulations with |

| |stochastic prices, growth, or discrete events like storms. |

|SUM |SUM(array1) |

| |Adds all the numbers in a range of cells. |

| |Applications: Used throughout the book to calculate the total number of hectares of land to be managed in a given |

| |manner, the total value derived from actions taken at different points in time or at different locations, etc. |

|SUMPRODUCT |SUMPRODUCT(array1,array2) |

| | |

| |Array1 & array2 are 2 arrays whose components you want to multiply and then add. |

| | |

| |Applications: Used throughout the book to calculate the total volume, value, or cost implied by assigning a set of |

| |values to an array of decision variables, given an array of per unit volumes, values, or costs. |

|VLOOKUP |VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) |

| | |

| |Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you |

| |specify in the table. Lookup_value is the value to be found in the first column of the array. Lookup_value can be a |

| |value, a reference, or a text string. Uppercase and lowercase text are equivalent. Table_array is the table of |

| |information in which data is looked up. Use a reference to a range or a range name, such as Database or List. |

| | |

| |If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, |

| |-1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, |

| |table_array does not need to be sorted. |

| | |

| |Applications: Used in Chap. 12 to find the earliest finishing time for an activity in a project that must take place|

| |before some other activity can begin. Used in Chap. 13 to find the highest possible return for all decisions made |

| |subsequent to the current decision in a dynamic programming problem. |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download