UFDC Home  myUFDC Home  Help  RSS 
CITATION
PDF VIEWER


Full Citation  
STANDARD VIEW
MARC VIEW


Downloads  
This item is only available as the following downloads:  
Full Text  
PAGE 1 MODELING AND ANALYZING SMALL FARM LIVELIHOOD SYSTEMS WITH ETHNOGRAPHIC LINEAR PROGRAMMING AEB 5167 Economic Analysis of Small Farm Livelihood Systems EXERCISES Peter E. Hildebrand and Victor E. Cabrera Food and Resource Econom ics Department University of Florida Gainesville, FL 32611 0240 200 8 PAGE 2 Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 1 MODELING AND ANALYZING SMALL FARM LIVELIHOOD SYSTEMS WITH ETHNOGRAPHIC LINEAR PROGRAMMING PREFACE This set of learning exercises constitutes a manual designed for graduate students and others interested in agricultural development in countries where small scale, limited resource family farm livelihood systems are important to the national economy. It was developed for the course AEB 5167, Economic Analysis in Small Farm Livelihood S ystems, in the Food and Resource Economics Department at the University of Florida. It has been used in short courses for international agricultural development workers (research and extension) as well. Because of the highly varied background of those inte rested in development, the exercises do not require a high level of mathematical nor economic sophistication, but a minimum level of computer literacy is assumed. The course, AEB 5167, is based on the philosophy that the best way to analyze small scale li mited resource family farms is to intimately understand the relationships and interactions integral to them. Responses of these men and women farmers to new technologies, modified infrastructure, or price or policy incentives are molded by the constraints on these livelihood systems because the farms are a home, not just a business For this reason, gender considerations and household composition are critical components to be incorporated explicitly. It has also been found that seasonal cash flow and season al food availability are important drivers of these livelihood systems and must be incorporated on a multi period basis within a year. Relatively simple, single year models are very useful for many analyses. However, when livestock, fallow systems and per ennial crops are involved, as is the general rule, then multiple year, dynamic models are usually indispensable. The last set of exercises incorporate s these aspects of analysis. After an introduction to linear p rogramming and the use of the Solver in Exc el (an example of a spreadsheet capable of solving linear programming problems) the exercises begin with a simple situation that is easy to model. Each additional exercise introduces a new procedure to help make the model more realistic. It is also sugges ted that when users begin to construct their own models, they follow the same step by step procedure making sure that solutions are feasible and the matrix is behaving as anticipated prior to making it more complicated. Once a large matrix is constructed, it is much more difficult to trouble shoot. It is important also for users to interpret each solution in turn to help in improving the ultimate model. The suggested matrices are not the only way to set up the exercises. There could Beginning with exercise one, i t is strongly suggested that the user of PAGE 3 Ethnographic Linear Programmi ng in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 2 this manual attempt to construct his or her own linear programming matrix before looking at the matrix suggested in the manual The importa nt point is that there should be internal consistency and that the model reflects all the constraints and interrelationships that exist in the livelihood system being modeled. It is also necessary to determine and use appropriate objective functions (what the farm family is trying to achieve). By the time users of this manual have completed all the exercises, they should be able to begin construction of their own models using their own data for the number of purposes for which ethnographic linear programmi ng is useful. PAGE 4 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 3 CONTENTS PREFACE ................................ ................................ ................................ ................................ 1 CONTENTS ................................ ................................ ................................ ............................ 3 I. INTRODUCTION TO MATRIX CONSTRUCTION AND SOLUTION WITH EXCEL ................................ ................................ ................................ ..................... 5 II. INTRODUCTION TO EXERCISES ................................ ................................ .......... 10 M ODEL CREATION : Describing the livelihood system Exercise 1. Basic E LP matrix ................................ ................................ ...................... 11 Exercise 2. Interm ediate products and accounting rows ................................ ........ 13 Exercise 3. Family consumption constraints transfer activities and cash flow ................................ ................................ ................................ ...... 1 4 Exercise 4 a. Gender disaggregation and gender analysis ................................ ............. 1 6 b. Creating input and output tables ................................ ............................... 1 8 Exercise 5. Resou rce flow and integer solutions ................................ ...................... 20 MODEL CALIBRATION AND VALIDATION: is it ready to use? .................. 22 H YPOTHESIS TESTING : Assessing livelihood strategies Exercise 6. Assessing alternative technology (hypothesis testing) ........................ 24 PREDICTION Exercise 7. Policy ana lysis (prediction) ................................ ................................ .... 26 PRODUCTION LABOR STRESS Exercise 8a. Household composition, food security and production labor stress ................................ ................................ ................................ .......................... 28 AGGREGATING TO A HIGHER SCALE Exercise 8b. Aggregating to a higher scale ................................ ............................... 29 PAGE 5 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 4 DYNAMIC PROGRAMMING Exercise 9 a. A nimals ................................ ................................ ................................ .............. 3 2 b. Perennial crops ................................ ................................ ................................ 3 6 III. INTERACTIVE LINEAR PROGRAMMING WITH VISUAL BASIC ............ 3 9 PREFACE ................................ ................................ ................................ .......................... 3 9 A. Macros Exercise 1 Copy and Paste Macro ................................ ................................ ........... 40 Exercise 2 Locate and run the macro in other ways ................................ ............. 4 1 B. Visual Basic Objects and Buttons Exercise 3 Locate and familiarize yourself with VB objects ................................ 4 3 Exercise 4 Set up a Combo Box ................................ ................................ .............. 4 4 C. Filters (Inputting data) Exercise 5 Filter and Special Paste ................................ ................................ ......... 4 5 Exercise 6 Select Households from the Matrix Sheet ................................ ........... 4 7 D. Visual Basic Solve r Function ................................ ................................ ............. 4 8 E. Visual Basic Reference to Solver Exercise 7 Including the Solver Reference ................................ ............................ 4 8 F Direct Solver Call Exercise 8 Solver Button ................................ ................................ ......................... 4 9 G. Listing Results (Output Tables) Exercise 9 Output Tables ................................ ................................ ........................ 4 9 H. Solver with Commands Exercise 10 Advanced Solver ................................ ................................ .................. 50 List of Solver Commands ................................ ................................ .......................... 5 1 Other Proposed Exercises ................................ ................................ ........................ 5 4 PAGE 6 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 5 I. INTRODUCTION TO MATRIX CONSTRUCTION AND SOLUTION WITH MICROSOFT EXCEL Ethnographic L inear Programming ( E LP) is a useful, and with the wide availability of la ptop computers, easily available method for describing understanding and analyzing family farm livelihood systems. A livelihood system is the full range of activities available to the individuals in a particular set of small farm households. Farmers sel ect from among these options those that comprise their livelihood strategies those activities that best contribute to achieving the goals to survive and thrive. A well designed ethnographic linear program ( E LP) ref lects these choices by selecting a combination of farm and non farm activities that is feasible given a set of fixed farm constraints and that maximizes (or minimizes) a particular objective or family goal while achieving other go als such as food security. The E LP model requires the following in each farm household situation: 1) the farm and non farm activities and options available in the crop and livestock sub system s with their respective resource requirements, and any constraints on their magnitude; 2) the fixed resources and other maximum or minimum constraints that limit farm and family activities; 3) cash costs and returns of each relevant activity; and 4) a defined objective or objectives. An example is used to introduce matrix construction and solu tion using a n Excel spreadsheet format. A hypothetical farm model is created in a hypothetical country. Crops considered are sweet potatoes, sorghum, beans and peanuts, Table A. Table A. Basic linear programming matrix Land is limited to 2 ha (cell I8 ), male labor to 40 days, female labor to 25 days and beginning cash on hand to 30 dollars. Cash income (ending cash) for one hectare of each crop assuming the product is sold, is as follows: sweet potatoes, $80; sorghum $20; beans, $45; and peanuts, $55. These values are in row 12. Labor and beginning cash requirements for one hectare of each crop are also shown in Table A. For instance, production of one hectare of sweet potatoes requires 20 days of male labor (cell D9), 20 days of female labor and no be ginning cash. The Right Hand Side (RHS) represents the constraints on the PAGE 7 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 6 resources (cells I8 to I11). The RHS also indicates that the family needs at least $130 at the end of the year ($30 for the next year's crops and at least $100 for other necessary ex penses), cell I12. The variable row (row 6, cells D to G) indicates how many units (hectares in this case) of each crop comprise the solution that best satisfies the objective or goal. Table A represents the basic structure of a linear programming matrix f or this kind of farm situation. The desire (objective or goal) of this hypothetical farm family is to maximize the amount of cash available at the end of the year. This is the sum of the amount of ending cash for each crop (when sold), per ha, multiplied by the area (hectares) of each crop that ends up in the solution. Excel has a convenient formula that can do this for us. It is called =sumproduct The use of the formula is shown for cell J12 (the solution cell) in Table B. Notice that when you enter th e formula, a number (zero at this point) shows in the cell. Table B. Formula for objective function When the computer is solving the problem, it must also keep track of the amount of each of the resources or constraints being used at each step (iter ation) in the process. The =sumproduct formula is also used for this and the formula for land is shown in Table C. The $ sign before the 6 (C$6:F$6) is useful for copying the formula down to the other rows. The $ holds the row being multiplied to row 6 whe re the variables are. Otherwise, when you copy the formula down to the other rows, the spreadsheet would move that row down as well. Table C. Formula for resource use Table D shows all four formulas for the RHS as well as for the objective function. PAGE 8 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 7 Table D. All formulas NOTE: When you practice set ting up the matrix for solution, follow the rows and columns carefully as shown in the examples. To solve the ELP with Excel, use T ools/Sol v er/ (Figure 1): Figure 1 Set up of Solver dialog box Identify the solution cell be typed into the S e t Target Cell box or you can click on the red arrow and highlight cell J12, and then minimize the parameters window. Equal to: M ax select it by clicking). Identify the variable cells by clicking on the red arrow, then highlighting the variable cells ($D$6:$G$6) and then minimizing the parameters window. To incorporate the constraints first click A dd next to the S u bject To Constraint s box, then highlight the resource use cells (those with the formulas, or J8 to J11), then click <= (less than or equal to) and then click on the arrow for the constants (RHS) and highlight the constants (I8 to I11). This tells the computer that the solut ion cannot use any more of the resources than available (the use must be <= to the amount available). PAGE 9 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 8 To incorporate the minimum end cash click A dd again, then click on cell J12 (or type it in the space), then click >= (greater than or equal to), and then click on the arrow for the constants (RHS) and highlight the cell with the minimum amount of ending cash (I12). It is also necessary to tell the computer that the variables cannot be negative. So go to O ptions and chose Assume Non Ne g ative Still on O ptio ns Assume Linear M odel to make sure it uses only linear functions. Figure 2. Solver options Click OK on the Sover Options menu. To solve the model, click S olve on the Solver Parameters menu. When the solver results window appears, it will indi cate that solver has found a solution and that all constraints and optimality conditions are satisfied. Leave marked (by default) K eep Solver Solution and highlight on R eports (Answer, Sensitivity, and Limits) and these will be posted in three new sheets. The Detail Report is not usually needed. Figure 3. Solver results confirmation PAGE 10 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 9 If you entered the problem correctly, this should provide you with a solution as in Table E which shows that the strategies for this farm, as specified in the model, are t o produce 1.08 ha of sweet potatoes, 0.06 ha of sorghum no beans, and 0.86 ha of peanuts. All the land is used, as well as all the female labor and beginning cash, but some male labor is unused (about 6.2 days) End cash exceeds the minimum by nearly 5 d ollars. Table E. Solver solution of the simulated system If you have successfully completed this exercise, you should be ready to begin formulating and solving the following exercises. Remember, in each exercise try to set up the matrix based on the d iscussion of the situation before you look at the suggested matrix. PAGE 11 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 10 II. INTRODUCTION TO EXERCISES The following exercises are designed to introduce you to the concepts and mechanics of ethnographic linear programming ( E LP). The software used is Excel but other spreadsheets are also amenable to E LP. Each of the nine exercises introduces a new concept and new mechanics. After you have understood and mastered these exercises you will know most of what you need to develop your own ELP models. The values u sed in the se exercises are not necessarily realistic, but the y are set up in a realistic manner. Scenario A farming systems field team wants to pretest some technologies and be able to predict what responses the small scale farmers in their area of respo nsibility might make to these potential changes. The first task is to simulate the livelihood system available to the small farms in their area (a descriptive model) Once they have a model that adequately reflects the livelihood system, the team uses the model to assess potential changes in livelihood strategies of individual households and then assess potential adoption of a proposed new technology The model can also be used to provide information to the government about probable varying responses of div erse farmers to a proposed change in price policy or infrastructure (predictive models) Exercises The exercises present a situation and ask you to create a linear programming matrix that accomplishes what the team has in mind. Suggested matrices and the solutions are provided, but should not be used until after you have attempted to build your own matrix. Then you can c ompare the solutions with your own. PAGE 12 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 11 Exercise 1. Basic E LP matrix Situation You are a member of a team responsible for improving techn ology in an impoverished area of your country. The team has been working with farmers and has some ideas for improving the productivity of the farms in its area of responsibility and the cash income available to the families for discretionary spending. Th e team members want to pre evaluate some alternatives they think might interest the farmers. To do this, they want to use ELP First they must simulate the livelihood system in the area. That is, the first step is to create a descriptive model of the many activities available to all households for which the livelihood system is relevant. These activities must include those available to all members of the household, and whether conducted on or off the farm. As they begin to develop the model, they will wor k with a typical household and its set of resources. Note: The values used in this and the following examples are not necessarily realistic and are used for illustrative purposes When you create your own models, the values should be as realistic as possi ble. The household the team starts working with produces goats, maize and hay, and some of the farm is often in fallow. The household (man, wife, adolescent male, child) farm s 3 ha. For the year, they estimate 115 days of labor available for production ac tivities and $700 of cash available for production. The team calculates that a goat requires 0.1 ha of pasture (not including the fallow), 7 days of labor and $49 of the beginning cash reserve. The annual cash income per goat is $330. A ha of maize would r eturn $1800 cash income and a ha of hay, if sold, would return $300 cash. A ha of maize requires $200 of the cash reserve and 40 days of labor. A ha of hay requires $150 of the cash reserve and 30 days of labor. The farm family want s to maximize cash avail able for discretionary spending (the cash available after satisfying food and other household needs and necessary cash expenses). At the very least they need $1000 non discretionary cash at the end of the year. This provides for the $700 cash needed at the beginning of the next year as well as for other necessities not produced on the farm. The amount of cash available at the end of the year above $1000, then, is available for discretionary spending. Exercises I. Construct the linear programming matrix fo llowing the format used in the introductory model. Carefully label all variables, constraints, and units for rows and columns. The objective is to maximize cash available for discretionary spending at the end of the year. Remember; try to set up your mode l before looking at the suggested model on page 12 PAGE 13 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 12 II. Solve the problem to determine if there is a feasible solution. Note: An infeasible solution means all constraints cannot be met. An unbounded matrix means there is no effective limit to the objecti ve function. III. Discuss and interpret the solution if it is feasible. (See, for example, the discussion of this solution in the Situation statement for Exercise 2.) Table 1. Suggested matrix and the solution for exercise 1 PAGE 14 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 13 Exercise 2. Intermediat e products and accounting rows Situation In the solution to the first exercise, both maize and goats are produced, but no hay. However, the farmers say that goats must have hay to supplement the pasture and there is no real market for hay. This oversight in the elaboration of the first model must be corrected. The farmers estimate that a goat requires 0.6 tons of hay. One ha of land in hay produces 4 tons of hay. Exercises I. Modify the E LP matrix from the first exercise to include a hay accounting row Remember that if hay is fed to the goats and not sold directly, the value for hay in the objective function cannot reflect the value of the hay if it were sold (otherwise, you would be indicating you are selling it and feeding it as well). Note: Because hay is produced in one activity and used in another (it is an intermediate product ), it is necessary to have an accounting row in the matrix (hay accounting). The initial constraint (RHS) for this row would be <= zero if there is no hay in storage at the b eginning of the year. A positive number in the RHS of the accounting row, such as 2, would indicate that two tons of hay were in storage at the beginning of the year and could be used before it would be necessary to produce more. Because resource using c oefficients are positive, an activity that produces a product or resource must have a negative coefficient in the accounting row. Also note that if this accounting row is added inside the matrix (say between labor and cash), the formulas in the resource us e column will automatically adjust. You will only need to copy the resource use formula (the =sumproduct formula) from labor to hay accounting. II. Discuss and interpret the solution. VARIABLES >> MAIZE HAY GOATS AMOUNT >> 2.88 0.00 0.00 RESOURCE RES/CONST ha ha head RHS USE RES/CONST LAND ha 1 1 0.1 <= 3 2.88 LAND LABOR days 40 30 7 <= 115 115.00 LABOR HAY ACCTG tons 4 0.6 <= 0 0.00 HAY ACCTG CASH $ 200 150 49 <= 700 575.00 CASH CASH INCOME $ 1800 330 >= 1000 5175.00 CASH INCOME Table 2. Suggested matrix and the solution for exercise 2 PAGE 15 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 14 Exercise 3. Family consumption constraints, transfer activities and cash flow Situation In the second exercise, only maize entered the solution and it was all sold (compared with the first solution, this reflects the fact that goats now require more resources those needed to produce the hay they need and therefore, return le ss cash to the resource base). However, n o maize was stored for family con sumption. The farmers estimate that 35 cwt (hundred weight, quintal or 100 lbs.) of maize is required for family consumption (the consumption constraint one of the family goal s ), and this is usually produced on the farm rather than purchased. The dependab le yield (the yield that can be expected 9 years out of 10) of maize is about 30 cwt per ha. Surplus maize is often sold. Exercises I. Modify the E LP matrix from the second exercise to provide for at least 35 cwt of maize for family consumption. Also str ucture the matrix so that any surplus maize can be sold (at a price of $60/cwt). This requires a maize accounting row because there are alternative uses for the maize. The maize production activity produces maize (30 cwt/ha) into the maize accounting row ( a negative coefficient), and the maize selling activity takes maize ( uses it) out of this row (a positive coefficient). Maize must also be transferred out of the maize accounting row and into the maize consumption row with the use of a transfer activity Note: To simplify the manipulation of the matrix, it is useful to consider the consumption requirement as a "sink," as opposed to the stock of resources. Because the stocks are positive values, the sink must be a negative value. The values that contribut e to the "sink," that is the a ij values, must also be negative. The inequality is then <= as in the other rows. II. Change the concept of cash income to cash balance at the end of the year, CSH END YR. Because the ending year balance includes any cash not used from the beginning year balance, a cash transfer activity is also needed to transfer any unused funds from the beginning year balance to ending balance. Because in the CSH END YR row income is positive and expenses are negative, the transfer activity coefficient in the CSH END YR row will be positive (it increases cash balance). If maximum year end cash balance is the objective, all unused beginning year cash will be transferred to the year end cash when the sign of the CSH BEG YR is <= If something other than maximum year end cash balance is the objective the CSH BEG YR row should be an equality to assure that all cash not used at the beginning of the year is transferred to the end. III. Discuss and interpret the solution. PAGE 16 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 15 Table 3. Suggested matr ix and the solution for exercise 3. PAGE 17 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 16 Exercise 4a. Gender d isaggregating and gender analysis Situation In the solution to Exercise 3, no goats are produced, but it is from goats that women in this livelihood system receive income. The team is aware that s ome of the alternatives they may want to evaluate may be of more interest to one household member than others. Also, they are not completely satisfied with the solution so far achieved. For this reason they want to disaggregate beginning and ending cash ba lances an d labor for the men and the woma n The family and the team estimate that the men provide 70 of the 115 days of crop and animal labor and the wom a n 45. Labor requirements for the various farm tasks are: maize hay goats men 30 25 1 woman 10 5 6 The ma n control s $500 of the CSH BEG YR a nd the woma n $200. Cash requirements from each for the various farm tasks are: maize hay goats man 200 150 10 woman 0 0 39 The ma n control s the c as h from any maize sold and he split s the c ash from the goats with the woman. The ma n must have a minimum annual income, above the CSH BEG YR, of $100 to meet fami ly responsibilities and the woma n need s $200 income annually plus their $200 CSH BEG YR. Income distribution for the income producing activities is: Maize selling goats Man 60 130 Woman 0 200 Exercise I. Separate CSH BEG YR, CSH END YR and labor constraints into those of the me n (father and adolescent son) and those of the woman. Because both the man and the woma n require a certain minimum CSH END YR balance, set mi nimum constraints so that the ma n ha s >= $ 600 and the woma n >= $400 CSH END YR. It is convenient, also, to include a household or total CSH END YR row althoug h this does not need a minimum value (the initial constraint will be >= zero). II. Solve the program for objective functions that maximize 1) M CSH END YR, 2) F CSH END YR, and 3) TOT CSH END YR. III. Compare the solutions and interpret the results. PAGE 18 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 17 T able 4a. Suggested matrix and the solution for exercise 4a (TOT CSH END YR) PAGE 19 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 18 Exercise 4b. Creating input and output tables Small formulations of a linear program are relatively easy to understand from the solution and answer report. However, as the mat rix gets larger, and as analysis begins (where prices, inputs, yields, resource amounts, etc.) may be varied, it is very convenient to construct input and output tables that interact directly with the E LP matrix. It is probably most convenient to create t he tables on additional pages of the same spreadsheet. Table 4b, below, is the matrix, found on the page labeled LP. The input tables are found on the page labeled INPUT, and the output table is found on the page labeled OUTPUT. a) Input tables The funct ion of the input tables is to provide an easy way to see what input coefficients were used in the matrix and to change them, if desired, for different analyses. Page INPUT shows the land, labor and consumption constraints, the yields and prices of crops, t he labor required for the different activities, and the cash requirements. These are linked and interactive with the matrix (LP page) as shown in the table. For instance, the male labor requirement for m aize, cell D7 in the matrix, shows the formula =INPUT !C21 This formula means that the coefficient for D 7 in the LP matrix contains the value found in cell C21 on page INPUT (the number 30). Maize requires 30 units of male labor. Notice that in order for the formula to appear in the cell, there is a space j ust prior to the formula. Removing this space (or not putting in the space to begin with) allows the correct number to appear as shown in the second table. All formulas begin with the equal sign (=). Because all input table values are positive, using onl y the equal sign in the formulas will produce positive values in the matrix as well. When a negative value is needed in the matrix, the cell designation within the formula should be preceded by a minus sign ( ). For example, the values in the matrix for ma ize yield, cell D10 and maize consumption, cell L11 should be negative. The formulas used are = INPUT!C28 and = INPUT!C13 respectively. b) Output tables Output tables take results from the solution of the matrix and put them in the output table. The ou tput table for this matrix is found on page OUTPUT. Cell C4 on page OUTPUT has the formula =LP! D4 This means that the area in maize taken from the solution of the matrix on page LP, cell D4 will appear in cell C4 on page OUTPUT. PAGE 20 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 19 Table 4b. LP page l inked to INPUT and OUTPUT pages. The top matrix shows the links and the bottom matrix shows the values. Table 4c. INPUT and OUTPUT pages linked to the LP matrix PAGE 21 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 20 Exercise 5. Resource flow and integer solutions Situation The simul ation achieved in Exercise 4 by maximizing household income is more realistic than previous solutions or than solutions that maximize income to either the woma n or the m a n. However, the team and the family members note a few more discrepancies. By using al l 70 days of the available male labor and all 45 of the available female labor, they are able to produce more m aize than usual, allowing the ma n to sell 15 cwt. The y feel that labor is being borrowed from one period to another so they decide to consider la bor and cash resources by semester Note that smaller divisions such as quarter, month or week may be necessary in many cases. The men's labor is divided 50 units in the first semester and 20 in the second (when th ey spend time hunting). The woma n, who do es not hunt has 25 units of labor available in the first semeste r and 20 in the second. They ha ve also been over estimating the amount of cash used for production at the beginning of the year. They now estimate the amount to be $400 for the man and $50 fo r the woma n. Of course, any cash not used in the first semester is available in the second. Any cash earned in the first semester is available at the beginning of the second semester Maize can only be sold in the second semester, but goats can be sold in either. The woma n's income from the goats is the same either semester ($200), but from selling the go ats in the first semester the ma n receive s $8 0 and $130 in the second. The ma n require s a minimum end of year c ash of $500 and the woman $200. Any cash re ceived in the second semester is considered year end cash. Maize, produced in the first semester, uses 30 days of the men's labor and 10 of the woman's as well as $200 of the ma n's cash. The ma n sell s maize only in the second semester. Hay uses 5 days of the men's labor in the first semester and 20 days in the second. The hay also requires 5 days of the woma n's labor in the second. The goats require 0.5 days of the men's labor each se mester and 3 days of the woma n's labor each semes ter. Goats also use $5 o f the ma h each semester, $20 of the woma Exercises I. Separate labor and cash into two semesters for both the me n and the woman. Previously there was only one time period, with cash balance rows for the be ginning and the end of the year; now, with the year divided into two semesters, you will need cash balance rows for the beginning of each semester. Expenses are incurred at the beginn ing of the respective semester, and income is generated at the end of the semester, so is available at the beginning of the next semester or year end. Also, create separate year end cash balance rows for the man the woma n and for the household. Be sure that any cash left over in the beginning of semester one is transferred to the PAGE 22 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 21 beginning of semester two and that all cash that comes into the end of semester one is available at the beginning of semester two. Similarly, any cash left over in the beginning of semester two must be transferred to the end of the year. Year end cash will include any leftover cash transferred from the beginning of semester two as well as any cash that comes in at the end of semester two. Goat production can still be one activity, but selling goats must be divided into two semesters (be careful not to sell the same goats more than once!). II. Set up the new matrix, solve the problem, and interpret and discuss the solutions when end of y ear cash is maximized for 1) the man, 2) the woma n and 3) the household. III. In order not to have a fractional amoun t of goats in the solution, in the solver set an integer constraint on goat production and selling activities forcing them to be integers. With integers, also obtain solutions when labor is minimized (Chayanovian) or cash productivity of labor (end yr cash /tota l labor) is maximized. Discuss the implications of the different household goals. Note: In order to maximize the cash productivity of labor, total labor must be summed and included in the denominator of the formula. Prior to running the E LP, all re source use sumproducts, including labor, initially will be zero. In Excel, trying to divide any value by zero, or in this case by a formula that produces a value of zero (total labor), will produce an error term (#DIV/0), and the solver will not work. To a void that problem, add a very small value (i.e. 1E 20) to total labor. Additionally you will need to uncheck the linear model assumption in the options prior to solving it. Table 5. Suggested matrix and the solution for maximizing end cash when goats are set integers for exercise 5 PAGE 23 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 22 Model calibration and validation: Is it ready to use? Ethnographic l inear programming models should never be considered as finished tools. Rather, they are always a work in progress, always capable of being improved w ith new information. However, before they can be used to help predict responses to changes in technology, infrastructure, prices, resource availability or policies, they need to be validated to assure the users that they do, in fact, adequately reflect co nditions in the communities being modeled. Validation of models is not an exact science. In models like those in this set of exercises, one method of evaluation is simply the how well the model reflects the conditions being modeled. Care needs to be taken not to force the model into compliance artificially by setting limits on variables or constraints that do not really exist. For example, if farmers in the area normally have no more than one or two head of livestock, but the model consistently indicates many more, then the approach is not to set an artificial limit of two on the activity. Rather, the reasons no more than one or two head are found must be determined Perhaps availability of grazing was not taken into cons ide ration. It could be that yield estimates of the livestock feed produced on the farm were too high (because averages were used rather than dependable yields, for example) artificially allowing the model to select more livestock than really can be fed. Inputs in the livestock activity may be under estimated. This process of calibration and validation obviously helps improve the model and is valuable to the modeler. It also helps maintain flexibility in the model so it can respond to the kinds of stimu li that will be asked of it when it is being used for the purpose for which it was constructed. An artificially constrained model simply cannot respond to stimuli so it loses its value in use. But if the model consistently shows one head or three rather than the more normal two, is this a serious problem? Here the modeler must depend on subjective evaluation. It may be decided that the model, in its present form, is adequately valid that it can be put into use, while at the same time, the modeler search es for more definitive data. For example, it may be that differences in household composition account for the human resources needed for livestock husbandry. Perhaps the presence or absence of an adolescent male may make the difference. In some instance s, a more methodical procedure can be used, but still subjectivity will be involved. If a relatively large number of households in a community have been modeled from a single base model, then a statistical comparison of the real world data and the results of the model solutions c ould be compared. Areas in the main crops can be compared, for instance. The sampled area in maize on each farm can be compared with the area in maize from each of the model el of probability that these two series represent the same population. But here again, the modeler (or a PAGE 24 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 23 superior) must decide at what level of probability to accept the validity of the model. Is a 5% level required or is 10%, 15% or even 20% adequate? If a modeler (or a professional journal) insists on a 5% level prior to considering the model valid, the benefits of the model may never be realized. A flexible model that reflects a 20% level of confidence still will be bett er than an artificially constrained model that is forced to fit at a 1% or 5% level of confidence. PAGE 25 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 24 Exercise 6. Ass essing alternative technology (h ypothesis testing) Situation The integer solution to the fifth exercise, when maximizing the ho usehold end cash, simulates the current livelihood strategies of the household in this livelihood system quite well. Sufficient maize is produced to feed the household, and only a limited amount is sold. Two goats are produced in a small pasture (0.2 ha) a nd about a third of a ha of hay is produced for the goats. About one third of the farm is in fallow. Both the man and the woman have some time in the second semester (the man to hunt and the woman to make clothes). Reserve cash is not a constraint for the woman nor for the man. The woman has a limited amount of discretionary cash to spend ($372 $200=$172) and the man has $478. The team considers that the model is ready to use for hypothesis testing or pre evaluation of alternative technologies or activitie s. Note: In your own models, validation is a more thorough process. Alternative to consider The cash return to the household from the sale of goats is quite low ($280 in the first semester and $330 in the second). Because some land is in fallow, an alte rnative could be to produce more maize to feed the goats. It is anticipated by the team that maize fed goats could increase household income. Estimates are that they could produce more and higher quality goats. This would require 4 cwt of maize per goat. A bout 0.625 tons of hay should be sufficient for each goat fed maize. Labor would increase to about 10 days per goat (0.75 days each semester requirement would be the same ($5 each semes cash would increase to $26.25 each semester. The value of the maize fed goats would increase to $375 when sold in the first semester ($107 for the man and $268 for the woman) and $444 when sold in the second semester ($175 for the man and $269 for the woman). Exercises I. Assess a maize fed goat activity as an alternative to possibly be incorporated into the present system, or perhaps substituted for the present goat production system. To do this, the present matrix (and sys tem) is left as satisfactorily simulated in Exercise 5. The new activity, the maize goat activity, is added to the matrix in Exercise 5 so that the linear program solves for maximum household year end cash when the proposed new activity is in competition w ith existing farm activities. Note that because the price received for the different kinds of goats is different, it is necessary to have separate selling activities for each class of goat. The solution should incorporate the integer constraint on numbers of goats produced and sold. II. State a hypothesis to be tested by solving this linear program. PAGE 26 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 25 III. Solve, discuss and interpret the solution and use it to accept or reject the hypothesis. Table 6 Suggested matrix and the solution for exercise 6 PAGE 27 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 26 Exercise 7. Policy analysis (Prediction) Situation The country is forced to import maize for consumption in the urban areas because there is not enough sold by the small producers who produce about 80% has argued with the Ministry of Finance that the import duty on fertilizer needs to be dropped so that farmers can afford to purchase it. Their farming systems teams have told them that small farmers feel the cost of purchasing and transporting nitrogen t o the farm comes to an average of about $2.475 per kilogram of N, too high for most of them to use on maize. On farm trials with small producers, however, indicate that the use of 100 kg of N per ha should increase maize yield by 50%. 1 The President of the country is excited by the possibility of increasing production by 40% (50% increase in production by 80% of the maize) and even thinks of exporting maize. He wants to convince the Minister of Finance to eliminate the import duty on nitrogen but needs a mo re complete analysis of what would happen if small farmers could obtain the nitrogen at half the current cost The team you are with estimates that the application of nitrogen would require a bout 6 additional days of the me n's labor during the first semes ter and about 2 addit ional days of the woma n's. Exercises I. Modify the matrix from Exercise 5 to include the new activit y of fertilized maize (remember to keep the present maize activity so the program has to choose between ways to produce maize). Firs t use the current price in the fertilized maize activity. What does the solution tell you about this price of fertilizer? Then change the price of fertilizer to the subsidized price. What does the solution tell you about the price of fertilizer? What if there were a credit program for these small producers? II. Analyze and interpret the solution s Write a report to the President including not only what happens on the farms represented by your model of a typical household in this livelihood system, but also what might happen to maize production and amount marketed for the country as a whole, assuming the price of maize stayed the same. III. The farming systems team thinks that if farmers used nitrogen to produce maize it might make their maize goats al ternative more attractive. Take another look at the maize goats alternative ( Exercise 6 ) by adding the fertilized maize. Discuss the results. 1 The average small farm production function is estimated as Y = 30 + 0.2175 N 0.000675 N 2 where Y is kg ha 1 of maize and N is kg ha 1 of nitrogen. PAGE 28 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 27 Table 7a Suggested matrix and the solution for exercise 7 I Table 7b Suggested matrix for and the so lution for exercise 7 II I PAGE 29 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 28 Exercise 8a. Household composition, food security and production labor stress L inear programs can be used to model individual farms or can be representative of a type of farm within a livelihood system. When they are meant to be representative of a group of farms, they often are based on average values found from a survey. The problems with using averages are 1) averages represent very few of the real farms from which the survey was taken and 2) averages mask diversity and indiv idual household differences An average household will have some fraction of young children and some fraction of older children. Older children are considered to be available to help with adult tasks, either in or around the house ( reproduction tasks, mo st common for older female children) or the field ( production tasks, most common for older male children). Consumption requirements reflect the needs of a household average not the individuals Hence, solutions based on averages may be very misleading. I t is tempting to aggregate results of a linear program based on an individual, average farm and/or household to the level of a community or higher scale. Such an aggregation will be misleading for the reasons explained above. However, when using ELP a dive rse set of household compositions representative of a community can be utilized, and solutions obtained T hese diverse households can then be aggregated with much more realistic and valid results. Situation The team has noticed that the model in Exercise 5 does not reflect what many other households do. They have decided that household composition is one of the keys that needs to be explored. The household used in the previous models included a mother, father, one male adolescent, and one young child. Th e team looked further into the situation regarding household composition, labor availability, and food requirements. They then made the following estimates. Male labor I should be based on 30 units from an adult male and 20 from each adolescent male. Male labor II should be based on 10 units from each. Female labor I should be based on 30 units from an adult female and 20 from any adolescent female less 5 units of female field or producti on labor for each child in the family. Female labor II should include 25 units from an adult female and 20 from any adolescent female minus 5 units for each child. The females seldom help the males with their work. Annual maize consumption should be based on the following needs: adult male, 12 cwt; adult female, 10 cwt; adol escent male, 10 cwt; adolescent female, 8 cwt; and each child, 3 cwt. Exercises I. Construct three input tables to incorporate the values above. One table will be for family composition. Another will be maize consumption, and a third will be available la bor. Incorporate these tables into the matrix from Exercise 5 to make them interactive. PAGE 30 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 29 II. Solve for the following household compositions: HH No. Adult male Adult female Adoles. Male Adoles. Fem Children 1 1 1 0 0 0 2 1 1 0 0 1 3 1 1 0 0 2 4 1 1 1 0 1 (Exercise 5) 5 1 1 0 1 1 6 1 1 1 1 0 7 1 1 0 1 0 (male leaves 1 st ) 8 1 1 1 0 0 (female leaves 1 st ) In some cases there is no feasible solution because male labor is constraining at a level too low to produce sufficient maize for family consumptio n. Estimate how much additional male labor is needed (this is a measure of stress on the male members of the household) in order to satisfy stated household food needs. Using this level of male labor availability, obtain a feasible solution. Using the ori ginal male labor availability, also estimate how much less maize consumption would be required to get a feasible solution. This would reflect the consumption of a less than satisfactory diet by members of this household. III. Compare the maize consumpti on excess male labor requirements (male stress level), and end year cash for each of the solutions obtained above with Exercise 5 (1 1 1 0 1). Exercise 8b. Aggregating to a higher scale IV. Within the community where the team is working, the proportion (percentage) of households that falls into each of the above household composition groups is as follows: 15, 5, 12, 20, 20, 12, 8, and 8, respectively. Calculate the weighted average household composition for the popu lation of the community (1 1 0.4 0.4 0. 69 ). Solve your model for this household composition. How does this solution compare with the others? V. What are the implications for modeling potential responses to new technology or policy incentives? PAGE 31 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 30 Table 8a. Suggested matrix and solution for the average household composition for exercise 8 Note: This is for the unweighted average household. Table 1: Family Composition weighted household Adult male 1 Adult female 1 Adoles. male 0.4 Adoles. female 0.4 Children 0.69 Table 4: Maize Consumption Cwt per Total cwt Adult male 12 12 Adult female 10 10 Adolescent male 10 4 Adolescent female 8 3.2 Children 3 2.07 Total 31.27 Table 3: Labor Availability I II Adult male 30 10 Adult female 30 25 Adol escent male 20 10 Adolescent female 20 20 Children 5 5 Total male labor 38 14 Total female labor 34.55 29.55 PAGE 32 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 31 Table 8b. Suggested average composition input tables connected to the matrix Table 8c. Different solutions for different household compositions PAGE 33 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 32 Exercise 9. Dynamic programming Many activities on a farm are not completed during a year Most livestock and any perennial crops are examples. In order to analyze these kinds of enterprises adequately, it is necessary to extend a model ov er more than one year. Multiple year models are called dynamic because what happens in one year affects what happens in others. Trees planted in one year may not produce for three or four years. Two year old trees are different from three year old trees. A nimal inventories must be maintained to keep track of births, young stock prior to breeding age older animals, death losses, sales, and home consumption. Exercise 9a. Animals Beginning with Exercise 5 the farming systems field team has decided that it needs to be more realistic with respect to goats. Situation The team wants to build a three year model of the farm. The team found that 2 young goats (kids) are required for family consumption every year and that these are produced on the farm. The birth rate is usually two kids per female per year. Surplus kids are often sold on the market; this requires a kids accounting row Older goats as well as kids must be transferred from the first to the second year and so on. In the second year, kids are conside red yearlings. Consider a 20% annual death loss of the goats during the year. Start the 3 year period with a flexible size herd. That is, let the model decide ho w many animals it would include, but c onsider only female goats. Do not consider purchasing goa ts. Labor, land and pasture requirements for the yearlings are half that of the adult goats. Yearlings require no cash inputs. Prices for the kids sold are the same as that used for goats in Exercise 5. You will need to transfer the male and female cash se parately from one year to the next. Exercises I. Expand the matrix from Exercise 5 into a three year period. This will require about three times as many rows and three times as many columns as you used for Exercise 5. That is, you will have a set of columns (activities) for each year and a set of rows (constraints) for every year. Although the matrix is very large, it is not very dense because most c ells are blank. II. Solve the program for objective functions that maximize 1) the sum of year end cash balance ov er the three year period, and 2) number of adult goats (size of the goatherd) in the third year. Other objectives could also be maximized if you like. III. Try setting an integer constraint on the goat production and selling activities forcing them to be integ ers. IV. Discuss and interpret the results. PAGE 34 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 33 Table 9a1. Suggested structure of the three year matrix PAGE 35 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 34 Table 9a2. Suggested matrix and the solution (three year end cash) for exercise 9a (part I PAGE 36 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 35 Table 9a2. Suggested matrix and the solution (thre e year end cash) for exercise 9a (part II) PAGE 37 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 36 Exercise 9b. Perennial crops Situation The farm family decides it would like to consider banana production They estimate that the household could consume 100 kg of bananas per year and hope surpl us bananas woul d be sold. The team estimates me units in the first semester and 20 units in t he second. The woman would contribute 5 units of labor in the first semester and 10 in the second. After the first year, the man and the woman each woul d contribute 5 units of labor each semester. The first year the bananas would cash. After that no cash is required for the bananas. Banana yield is estimated at about 6000 kg/ha beginning the second year The price of ban anas for sale is $0.3 0 per kilogram. The man would control the cash from any bananas sold. Note: Remember that banana land has to be transferred from the first year to the second year and from the second to the third year and so on. But also remember that once the land is put in bananas it is not available for other uses in future years. Consequently, it is necessary to introduce a banana land constraint each year, and only non banana land is available in future years for other activities. For the purpose of this exercise consider planting bananas only the first year. Set the RHS constraint for banana land in the second year as equal to the area planted in banana the first year. The third year area in bananas RHS constraint should be equal to the area in bananas in the second year. Exercises I. Modify the LP matrix from the Exercise 9a to introduce bananas in the multiple year model. Solve the program for an objective function that maximizes the sum of the three year end cash balances over the three year period. You might also try maximizing the area in bananas subject to all the other constraints. V. Discuss and interpret the results. PAGE 38 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 37 Table 9b1. Suggested matrix and the solution (three year end cash) for exercise 9b (part I) PAGE 39 Ethnographic Linear Programming in Small Farm Livelihood Systems Hil debrand & Cabrera, 2006 38 Table 9b2. Suggested matrix and the solution (three year end cash) for exercise 9b (part II) PAGE 40 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 39 II I INTERACTIVE LINEAR PROGRAMMING WITH VISUAL BASIC PREFACE This set of learning exercises constitutes a manual designed for graduate students and others with linea r programming literacy and familiarity with Microsoft Excel It is intended to make the ELP analysis more efficient in representing diverse households and respecting the diversity of household s trategies The objective is to achieve individual household analyses with accuracy and speed. Recent computer development has made it possible for farming systems teams to use ELP on farm s while communicating with the farm families Visual Basic could become an essential tool for updating and testing different sit uations working directly with the farmers. Visual Basic makes the simulation process user friendly. This set of exercises is divided into eight sections and 10 exercises. The first two sections deal with the simplest ing Visual Basic Objects in a spreadsheet. The third and the seventh sections present common linear programming tasks such as input and output tables, respectively, linked to Visual Basic codes. The fourth, fifth, and sixth sections deal with all the tool s needed to control the present s and describe s A. Macros Macros are routine s and sub routines of tasks in E xcel. Usually we do many of these routine s and sub routines manually and frequently (e.g. copy and paste, cell and sheet selection, calculations, solve etc.). Many times these routines are repetitive, time consuming, and with a chance of error. The use of macros for handli ng large amounts of da ta, saves time reduc es the chance of errors and is very convenient. Macros can do any task that it is possible to do using the mouse and the keyboard in an Excel document. The principle of macros can better be understood if we think of it as analogous t o a tape recording process; we can record any routine and recall it at any time by simply using a shortcut PAGE 41 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 40 Exercise 1 Copy and Paste Macro 1. In an Excel spreadsheet. Go to Tools or VIEW /Macro/Record Macro A Record Macro window will appear. 2. In the Record Macro window. Leave the Macro name as as the Shortcut key, Store the macro in: This Workbook, and click OK. A Stop Rec window will appear. 3. Now the recording process starts. Select cell C5, go t o Edit/Copy Select cell C10, go to Edit/Paste Click in the blue square of the Stop Rec window. The macro is ready to use 4. Watch the macro work. Select cell C5 and write a number (e.g. 100), press Enter and press Ctrl + z. The number in the cell C5 will be duplicated and assigned to cell C10. Play with this. You will realize that this simple principle could be applied to many useful routines PAGE 42 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 41 Exercise 2 Locate and run the macro in other ways 1. You must be able to find where your macros are loca with the previous exercise. Go to Tools/Macro/Macros ... A Macro window will appear. 2. Make sure This Workbook is selected and Macro1 is highlighted. Then press the Run button. This is another way to call the macro. 3. rectly from the Visual Basic Editor. Go to Tools/Macro/Visual Basic Editor ( VBE ) Microsoft Visual Basic Editor will appear. You will see three frames in the Visual Basic Editor: in the upper left, the project directory; in the lower left the propertie s; and on the right a window for the CODES (it may contain visual basic codes or could just be empty). PAGE 43 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 42 4. Directory D ouble click in Modules folder and again double click in Module 1. Once you have it, you will see the codes of this simple macro in the codes window Any routine has a Sub Name( ) that is the starting point and an End Sub that is the end. Between these commands you will see comments (green and starting with an apostrop he) and the real codes. In this example the name of the routine is Macro1, the comments refer to the date and author of the macro and the keyboard short cut. T he codes are a set of select, copy, and paste. 5. Ret urn from VBE to the spreadsheet by click ing on the Excel icon in the bottom tool bar : and delete whatever is in cell C10. Return from the spreadshe et to VBE by click ing on the VBE icon in the bottom tool bar PAGE 44 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 43 Now, first make sure that the cursor is anywhere inside the routine look for the run icon in the middle top of the screen click on it and going back to Excel check to see if cell C5 is copied to C10 in the spreadsheet. Now you are able to create and run Macros in Excel. Save the workbook with the name of FirstMacro but keep in mind that at any time you ask to open this file, Excel may warn you of the existence of macros (which are similar to viruses) and you will need to enable them. B Visual Basic Objects and Buttons Microsoft Excel has a feature to in sert objects and buttons in any spreadsheet. These are useful in the linear programming work. Exercise 3 Locate and familiarize yourself with VB objects 1. Open the FirstMacro file, go to View/Toolbars and check Control Toolbox A Control Toolbox will ap pear 2. Box, Text Box, Command Button, Option Button, List Box, and Combo Box. (You can find these names by pointing and stopping the cursor on them). 3. Click on the Comm and Button. Go with the mouse to cell C 4 in the spreadsheet (you will note that instead of an arrow for the mouse cursor, now you have a cross). Locate the cursor on the lower left corner of C 4 and drag it to the top right corner of D3. A button will appea r 4. Double click in the middle of the new CommandButton1. VBE will open and the cursor will be right in the middle of a program routine ; write Macro1 and close VBE. PAGE 45 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 44 I n the spreadsheet you will see that the Design Mode (Ruler Triangle Penc il icon) is selected; deselect it by clicking on it. 5. Go to cell C5. Write a number (e.g. 1000), press enter. Now click on the CommandButton1. You will see that the number you put in C5 is copied into cell C10. You called Macro1 d irectly from the Comman dButton1 6. Play and insert other VB objects in the spreadsheet. Save your file as FirstButton Exercise 4 Set up a Combo Box 1. Open the FirstButton file and repeat step 1 of Exercise 3. In the Control Toolbox click on the Combo Box option. Insert a Combo Box in cell s H 2 I 3. 2. Write successive numbers 1 to 10 in cells J1 to J10. Make sure that the design mode is selected in the Control Toolbox and then double click on the Com bo Box The VBE window will appear. 3. In the VBE window, select the GotFocus option from the drop menu in the top right combo box (as seen in above figure) and then write the following code between Sub and End Sub of the CommandButton2 ComboBox1.List = Sheet1.Range("J1:J10").Value You will generate this routine : PAGE 46 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 45 Private Sub ComboBox1_GotFocus() ComboBox1.List = Sheet1.Range("J1:J10").Value End Sub 4. Return to the spreadsheet, make sure the design mode is deselected. Click on the drop menu option of the Com boBox you just created You will see that the nu mbers of cells J1 to J10 are now selection options in the ComboBox I magine that each number represents a specific household and links to its input data in your matrix and you could select any of them easily. Save your file as FirstList C Filters I nputting Data A critical function in individual household LP analysis is being able to without a chance of error. The Filter function of Excel, macros or routines, and Visual Basic o bjects will help in this task. Exercise 5 Filter and Special Paste 1. Open the file filter1.xls Available at: http://nersp.nerdc.ufl.edu/~vecy/vbe/ Save the file in your local hard drive. 2. Take a look at the file. You should be familiar with this, it is the solution to Exercise 5 of the ELP manual Note that there are two sheets: MATRIX and INPUT. Some RHS numbers in the MATRIX sheet (labor and maize consumption) are linked to the input sheet; th ey are highlighted with yellow. Whenever we change family composition in the INPUT table, the labor available and the consumption requirement change in the MATRIX. 3. To understand the FILTER function, go to the INPUT sheet, select cell H9 and write in Household then select cell H10 and write the number 5. Click Enter and look in the Excel main menu for: Data/Filter/Advanced Filter ... The Advanced Filter window will appear PAGE 47 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 46 In the Advanced Filter window, se lect Copy to another location. Make sure that in the List range, the household table: $A$9:$F$17 is selected and in the Criteria range, our criterion just written: $H$9:$H$10 is selected Finally, for the Copy to, make sure $A$19:$F$19 is selected as seen in the above Figure. Click OK. You will realize that this Filter function is very useful. It uses the Criteria range to find an exact match inside the List range (register number inside column heading). After finding it, it takes the whole matched r ow (f amily composition) and Copies it to the selected cells. Change the number of household in cell H10 and go to the Advanced Filter again P lay with this. 4. Now you are ready to make a macro called filt with a shortcut Ctrl+f. Save the macro in this workboo k and make sure it works properly. 5. The next step will be to put the household composition (selected in cells B20 to F20) into the input table (Cells B2 to B6). Manually, the easiest way is to just copy B20 to F20, and Transpose them in a Paste Special. Do it. PAGE 48 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 47 6 Create a new macro called hhcopy with shortcut Ctrl+h that makes this Paste Special for you. Make sure it works correctly. This is neat. Change the H10 number, do Ctrl+f the household selected changes, now do Ctrl+h the labor available an d consumption change not only in the above tables but also inside the matrix. Save this file as filter2 Exercise 6 Select Households from the Matrix Sheet 1. Start by opening the filter2.xls file. 2. Select the MATRIX sheet. Insert a ComboBox between F27 and G27. Write Household in F 26 3. Double click on the Com boBox and similarly to exercise 4, step 3, write: Private Sub ComboBox1_GotFocus() ComboBox1.List = Sheets("INPUT").Range("a10:a17").Value End Sub This code is assigning the values of the hou sehold numbers (1 to 8) to the Combobox. 4. Insert a CommandButton in the MATRIX sheet, between cel ls K26 and L 27. Double click on this second button and write: Private Sub CommandButton2_Click() Sheets("INPUT").Range("H10") = ComboBox1.Value Shee ts("INPUT").Select filt hhcopy Sheets("MATRIX").Select End Sub Note: you do not need to go to GotFocus for this routine. These codes are a little more complex. In the first line we are assigning the current ComboBox value to cell H10 in the INPUT sheet. In the second line we are indicating that we will work for a while in the INPUT sheet. The third and fourth lines are calling the macros filt and hhcopy respectively. The last line is only to return to our worksheet, MATRIX. 5. Deselect the Design Mode in the Control Toolbox, pick up some number in the ComboBox and click on the second CommandButton. Note that the numbers in the RHS of the matrix change automatically with each change in the household number, so you may solve the matrix for dif ferent households. Play with it by changing the numbers Save your file as filter3 PAGE 49 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 48 D Visual Basic Solver Function It is possible to call the solver function directly from the Visual Basic through codes or macros. If we call the function from macros we need to previously setup the solver parameters manually (Target Cell Max/Min/etc, Changing cells, Constraints, etc.). There are two important things to take into account before starting with the next exercises: 1. The solver can not be called if some cell in the sheet is being edited or if no specific cell is selected in a sheet. Taking this into account can avoid many frustrations. 2. The solver can only work from codes or macros in Visual Basic if a reference to them exists. Exercise 7 deals with th is topic. E Visual Basic Reference to Solver Exercise 7 Including the Solver Reference 1. Open your previous file filter3 .xls 2 Open the Visual Basic Editor (VBE). (See exercise s 2 & 3). In the top left window, highlight the VBAProject (filter3.xls) go to Tools/References The VBAProject window will appear. In this window check solver and click OK. The Project window must have References /Reference to SOLVER.XLA : Note that there is a new folder (References) inside which is the reference to the solver. You can put the reference in another path and it will still work, but it is always a good idea to insert it in the file you are working on to make sure it will work in other computers. 3 Close the VBE and save your file as filter4 PAGE 50 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 49 F Direct So lver Call Exercise 8 Solver Button 1. Open your file filter4.xls 2. Record a new macro called sol and solve the matrix. 3. Record another new macro called del deletes the v aria ble cells of the matrix 4. Now you can control all the actions in just one button. Select design mode (see exercise 3) double click in the existing command button, insert at the end of existing commands the new commands del and sol and you will get this: Private Sub CommandButton1 _Click() S heets("INPUT").Range("H10") = ComboBox1.Value Sheets("INPUT").Select filt hhcopy Sheets("MATRIX").Select del sol End Sub 5. Try it. You must choose one household in the ComboBox, th en click in the CommadButton2 and everything is done. 6. Play with this and make sure all the solutions are correct. Which households obtain non feasible solutions? 7. Save your file as solv1 G Listing Results Exercise 9 Output Tables 1. Open your file solv1 .xls 2. Go to Insert/Worksheet (right click on INPUT and click on insert, then worksheet) Right click on the new s heet1 and click on rename and rename it OUTPUT. In cell B5 in OUTPUT write household and in the next columns in row 5 copy the n ames of the variables of the matrix. After that, in the same row copy all the names of the resources and constraints (hint: PAGE 51 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 50 use Paste Special/Transpose). Then you will get column headings B5 to AC5. 3. In cells C4 to AC4 (yes, above not below) refer or li nk to the values of the matrix. 4. Refer or link cell B4 to the INPUT sheet (H10). 5 Create a new macro that insert s copied cells from the row 4 to row 6 using row 3 as a bridge. Call this macro output Right click on n Right click on Then click OK. Right click on Right click on 6 Go to the MATRIX sheet. Select design mode and double click on the Command button. In the VBE, write at the end of the commands (after Sol) Sheets("OUTPUT").Select output Sheets("MATRIX").Select Then close VBE. 7 Deselect design mode in Ex cel and t ry it to see if it works properly. Solve households 1 to 8 in order and see the differences at the end in the OUTPUT sheet. Save your file as solv 2 H Solver with Commands The solver in Microsoft Office can be called through commands in the VBE For example, we could just write SolverSolve and refer it to some command button in the spreadsheet and it would work exactly the same as recording a macro. But the main advantage of call ing the solver by commands is that we can control all the parameter s directly from buttons in the spreadsheet. With that in mind, here is an exercise and a list of all the commands that control the solver. These commands only work with Microsoft Excel 97 (or above) and the regular solver engine. For other versions, there are slight differences and you may need to find the exact commands in the help files or in the manua ls. Sometimes tutorial software handles these topics as well Exercise 10 Advanced Solver 1. Open your file solv2 .xls PAGE 52 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 51 2. Go to VBE. Find your codes th at call the solver (hint: double click in module2 ). You will see a series of codes. Some of them set up the parameters and the last code SolverSolve takes the action of the solver. Sub sol() ' sol Macro Macro recorded 8/12/2003 by VECabrera Keyboard Shortcut: Ctrl+s SolverOk SetCell:="$P$24", MaxMinVal:=1, ValueOf:="0", ByChange:="$C$7:$M$7" SolverSolve End Sub Sub sol() 3. The first two lines (bold) were set in the solver for exercise 5; Excel does not include the constraints in this macro, howeve r we could include them if desired by using the command SolverAdd (see following list) The constraints are not inside these parameters. Rather it is using the current constraints in the solver window. We can delete or change them to comment mode (no t acti ve) and control the parameters directly from the spreadsheet. Put an apostrophe before the first codes line: ByChange:="$C$7:$M$7" Letters will turn green indicating they are not active. Now, we are u sing the most important code only to call the solver function. Try it in the spreadsheet. Once it is working, you can go to the Tools/Solver and for CommandButton. How do the solutions in the different households c hange? Are there more or fewer non feasible solutions?. 4. Save your file as solv 3 List of Solver VBE Commands SolverAdd: Adds constraints to a Solver model. Takes three arguments: Reference to a cell or a range of ce lls that forms the left side of a constraint. 1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must contain integers.) Right side of the constraint. SolverChange: Changes an existing constraint. Takes three arguments: PAGE 53 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 52 Reference to a cell or a range of cells that forms the left side of a constraint. 1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must contain integers.) Right side of the constraint. SolverDelete: Delete s an existing constraint. Takes three arguments: 1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must contain integers.) Right side of the constraint. SolverFinish: After solving a problem, tells Excel to keep the re sults and to create a report. Takes two arguments: 1 keep final results; or 2 discard results and return to original values. 1 create an Answer report; 2 create a Sensitivity report; or 3 create a Limit report. Solver FinishDialog: Equivalent to SolverFinish; however, also displays the Solver Results dialog box after solving a problem. Takes two arguments: 1 keep final results; or 2 discard results and return to original values. 1 crea te an Answer report; 2 create a Sensitivity report; or 3 create a Limit report. SolverGet: Returns information about the Solver model. Takes two arguments: Takes values from 1 through 18 to return information about the Solver model. For d etails, use the Object Browser to view the SolverGet help topic in the XLM Function Reference section in Excel 5's online Help. Name of the sheet containing the Solver model. SolverLoad: Loads parameters for an existing Solver model that h ave been saved to the worksheet. Takes one argument: Range on the active sheet that contains the Solver model. SolverOk: Defines a Solver model. Takes four arguments: Target cell for the Solver model. 1 maximize targ et; 2 minimize target, 3 match target to a specific value. If the maxMinVal argument is set to 3, you must specify the value to which the target cell is to be PAGE 54 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 53 matched. Cell or range of cells that will be changed in setting the t arget cell. SolverOkDialog: Same as SolverOk but also displays the Solver dialog box. Takes four arguments: Target cell for the Solver model. 1 maximize target; 2 minimize target; 3 match target to a specific value. eOf If the maxMinVal argument is set to 3, you must specify the value to which the target cell is to be matched. Cell or range of cells that will be changed in setting the target cell. SolverOptions: Allows you to specify advanced options fo r your Solver model. Takes 10 arguments: Maximum time Excel will spend solving the problem. Maximum iterations Excel will use in solving the problem. Number between O and 1 that specifies the degree of precision to be used in solving the problem. If True, Solver assumes that the underlying model is linear. If True, Solver pauses at each trial solution. 1 for tangent estimates; 2 for quadratic estimates. 1 for forwa rd, 2 ffir central. 1 for Quasi Newton search; 2 for Conjugate Gradient search. Number between O and 1 that specifies tolerance. If Scaling is True and two or more constraints differ by several orders of magnitude, Solv er scales the constraints to similar orders of magnitude during computation. SolverReset: Resets Solver options (cell selections and constraints in the Solver Parameters dialog box and all settings in the Solver Options dialog box) to their default valu es. SolverSave: Saves a Solver model definition to a range of cells on the worksheet. Takes one argument: The range of cells to which the Solver model is to be saved. SolverSolve: Starts a Solver solution run. Takes two arguments: PAGE 55 Ethnographic Linear Programming in Small Farm Livelihood Systems Hildebrand & Cabrera, 2006 54 Finish If True, Solver returns results without displaying anything. If False, Solver returns results and displays the Solver Results dialog box. This argument is used only if True is passed for the step thru argument of the SolverOptions functi on. If so, you can pass the name of a subroutine (as a string) as the showRef argument this routine will be called whenever Solver returns an intermediate solution. Other proposed exercises You can try these exercises using your file solv3.xls 1. Ma disappears at the end of the solver. After clicking on the solver button only results must appear. 2 Control the cash constraints with buttons. With a button take out these constraints, with another button include th em before solving. 3. If you have some literacy in Visual Basic, you could try to which you could solve the eight households with only one click 4. statements that control constraints. 5. Your imagination is the limit. 