SP 03-3

STAFF PAPER SERIES

MODELING AND ANALYZING SMALL FARM LIVELIHOOD

SYSTEMS WITH LINEAR PROGRAMMING

by

Peter E. Hildebrand and Victor E. Cabrera

August 2003

Staff Paper SP 03-3

UNIVERSITY OF

SFLORIDA

Institute of Food and Agricultural Sciences

Food and Resource Economics Department

Gainesville, Florida 32611

MODELING AND ANALYZING SMALL FARM

LIVELIHOOD SYSTEMS

WITH LINEAR PROGRAMMING

AEB 5167

Economic Analysis of Small Farm Livelihood Systems

EXERCISES

Peter E. Hildebrand and Victor E. Cabrera

Food and Resource Economics Department

University of Florida

Gainesville, FL 32611-0240

2003

Modeling and Analyzing Small Farm Livelihood Systems

with Linear Programming

Peter E. Hildebrand

Professor Food and Resource Economics Department

University of Florida

Victor E. Cabrera

School of Natural Resources and Environment

University of Florida

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 Systems, 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 interested 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 limited-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 analysis and household composition are

critical components to be incorporated explicitly. It has also been found that

seasonal cashflow and seasonal 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 perennial crops are involved, as is

the general rule, then multiple-year, dynamic models are usually indispensable.

The last set of exercises incorporates these aspects of analysis. After an

introduction to linear programming and the use of Excel (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 suggested 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.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

MODELING AND ANALYZING SMALL FARM LIVELIHOOD

SYSTEMS WITH 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 Systems, 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 interested 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 limited-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 analysis and

household composition are critical components to be incorporated explicitly. It

has also been found that seasonal cashflow and seasonalfood 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 perennial crops are involved, as is the general

rule, then multiple-year, dynamic models are usually indispensable. The last set

of exercises incorporates these aspects of analysis.

After an introduction to linear programming and the use of Excel (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

suggested 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

be a number of "correct" ways to structure any particular situation.

Beginning with exercise one, it is strongly suggested that the user of

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

this manual attempt to construct his or her own linear

programming matrix before looking at the suggested matrix. The

important 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 ofpurposes for which linear programming is useful.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

CONTENTS

PR E FA CE ........................................................................................................................... 1

CO N TEN TS ........................................................................................................................ 3

I. INTRODUCTION TO MATRIX CONSTRUCTION AND SOLUTION

WITH EXCEL ......................................................................................................... 5

II. INTRODUCTION TO EXERCISES ........................................................... .... 10

MODEL CREATION: Describing the livelihood system

Exercise 1. Basic LP matrix..............................................................................11

Exercise 2. Intermediate products and accounting rows..................................... 13

Exercise 3. Family consumption constraints and transfer activities................... 14

Exercise 4.

a. Gender analysis................................................................................16

b. Creating input and output tables ..................................... ............... 18

Exercise 5. Resource flow and integer solutions............................................. 20

MODEL CALIBRATION AND VALIDATION: is it ready to use? .................22

HYPOTHESIS TESTING: Assessing livelihood strategies

Exercise 6. Assessing alternative technology....................................................24

PREDICTION

Exercise 7. Policy analysis .............................................................................. 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

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

DYNAMIC PROGRAMMING

Exercise 9.

a. Anim als.......................................................... ......................................... 31

b. Perennial crops........................................................................................ 35

III. INTERACTIVE LINEAR PROGRAMMING WITH VISUAL BASIC........... 38

PR EFA CE ..................................................................................................................... 38

A. Macros

Exercise 1 Copy and Paste Macro ....................................................................39

Exercise 2 Locate and run the macro in other ways........................................ 40

B. Visual Basic Objects and Buttons

Exercise 3 Locate and familiarize yourself with VB objects................... ........ 42

Exercise 4 Set up a Combo Box .................................... .............................. 43

C. Filters (Inputting data)

Exercise 5 Filter and Special Paste ................................................................44

Exercise 6 Select Households from the Matrix Sheet .......................................46

D. Visual Basic Solver Function.................................................................47

E. Visual Basic Reference to Solver

Exercise 7 Including the Solver Reference ................................................... 47

F. Direct Solver Call

Exercise 8 Solver Button................................................................... 48

G. Listing Results (Output Tables)

Exercise 9 Output Tables ........................................ .... ......... 48

H. Solver with Commands

Exercise lo Advanced Solver .................................................................... ........49

List of Solver Commands............................................... ......................... 50

Other Proposed Exercises.................................................................... 54

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

I. INTRODUCTION TO MATRIX CONSTRUCTION AND SOLUTION

WITH MICROSOFT EXCEL

Linear Programming (LP) is a useful, and with the wide availability of laptop

computers, easily available method for describing 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 select from

among these options those that comprise their livelihood strategies those

activities that best contribute to achieving the household's production and

reproduction goals to survive and thrive. A well-designed linear program (LP)

reflects 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 goals such

as food security. The 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-systems 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 solution using an Excel

spreadsheet format. A hypothetical farm model is created in a hypothetical

country. Crops considered are sweet potatoes, sorghum, beans and peanuts,

Table A.

A a C D E F G _H I J K

2

4

5 Activities> Sw. pot. Sorghum Beans Peanuts

6 i Variables> 0 0 0 0 RHS Resource

7 Constraints v Unit v> ha ha ha ha Use Constraints v

8 Land ha 1 1 1 1 <= 2 Land

9 M. Labor days 20 30 40 12 <= 40 M. Labor

101 F. Labor days 20 0 10 4 <= 25 F. Labor

11 Beg. Cash dollars 0 0 40 35 <= 30 Beq. Cash

12 i End. Cash dollars 80 20 45 55 >= 130 End. Cash

Table A. Basic linear programming matrix

Land is limited to 2 ha (cell 18), 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

beginning cash. The Right Hand Side (RHS) represents the constraints on the

resources (cells 18 to Ill). The RHS also indicates that the family needs at least

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

$130 at the end of the year ($30 for the next year's crops and at least $1oo for

other necessary expenses), cell 112. 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 for 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 the formula, a

number (zero at this point) shows in the cell.

A B C D E F 0 HI 1 J K_

i -- ____ ___ -- -

5 Activities> Sw. pot. Sorghum Beans Peanuts

6 Variables> 0 0 0 0 RHS Resource

7 Constraints v Unit v> ha ha ha ha Use Constraints v

8 Land ha 1 1 1 1<-- 2 0 Land

T M. Labor days 20 30 40 12 <= 40 _0 M. Labor

101 F. Labor days 20 0 10 4 _<= 25 0 F. Labor

11i Beo. Cash dollars 0 0 40 35 <= 30 0 Beg. Cash

12 End. Cash dollars 1 801 20 46 55 >= 130 =SUMPRODUCT(D12:G12;D56:G6! End. Cash

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 (iteration) 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 where the variables are. Otherwise, when you copy the formula down to the

other rows, the spreadsheet would move that row down as well.

A A B i C D E F G HI I J K

4 -- I ____ ___ ___ __ ___ -- -~-- ------------

5 Activities> Sw. pot. Sorohum Beans Peanuts

6 i Variables> 0 0 0 0 RHS Resource

7 Constraints v Unit v > ha ha ha ha Use Constraints v

8 j Land ha 1 1 1 = 2 =SUMPRODUCT(D8:G8.DS6:GS6 Land

9 M Labor days 20 30 40 12 = 40 0 M. Labor

10 F. Labor days 20 0 10 4 < 25 0 F. Labor

11 Beg. Cash dollars 0 0 40 35 = 30 0 Beg.Cash

12 End. Cash dollars 80 20 45 55 > 130 0 End. Cash

131

Table C. Formula for resource use

Table D shows all four formulas for the RHS as well as for the objective function.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

7Aj B C D E F I G H I J K I

2i

3i

41__

5 Ai activities> Sw. pot. Sorahum Beans Peanuts

6 Variables> 0 0 0 0 R__ S Resource

7 I Constraints v Unit v> ha ha ha ha Use Constraints v

8 Land ha 1 1 1 1 1 <= 2 =SUMPRODUCT(D6:G8.D$6:GS6) Land

9 M Labor days 20 30 40 12 <= 40 =SUMPRODUCT(D9:G9.D6:G6) M. Labor

101 F. Labor days 20 0 10 4 <= 25 =SUMPRODUCT(D10G10.D56:GS6 F. Labor

11 Beg Cash dollars 0 0 40 35 < 30 =SUMPRODUCT(D11:G11,DS6:GS6 Beg Cash

12U End. Cash dollars 80 20 45 55 >= 130 =SUMPRODUCT(D12:Gi2.D$6:GS6} End. Cash

Table D. All formulas

NOTE: When you set up the matrix for solution, follow the rows and

columns carefully as shown in the examples.

To solve the linear program with Excel, use Tools/Solyer/, (Figure i):

Set Target Cell: )$3$12

Equal To: ( Max Min C Value of: 0

Options

Reset All

Hdp

V Identify the solution cell (in this example) as $J$12. "$J$12" can simply

be typed into the Set Target Cell box or you can click on the red arrow

and highlight cell J12, and then minimize the parameters window.

" Make sure the problem is to "Equal to: Max" (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.

V To incorporate the constraints, first click Add next to the "Subject To

Constraints" box, then highlight the resource use cells (those with the

formulas, or J8 to Jn1), then click <= (less than or equal to) and then click

on the arrow for the constants (RHS) and highlight the constants (18 to

111). This tells the computer that the solution cannot use any more of the

resources than available (the use must be <= to the amount available).

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

V To incorporate the minimum end cash, click Add 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 (112).

V It is also necessary to tell the computer that the variables cannot be

negative. So go to Options and chose Assume Non-Negative.

" Still on Options, choose "Assume Linear Model" to make sure it uses

only linear functions.

Figure 2. Solver options

Max Jme: seconds OK

Iterations: F1OO Cancel

Eredsion: 10.000001 Lad Modd...

Tolerance: 5 % Save Model...

Convergence: 0.001 Help

rW Assume Linear Model r Use Automatic Scaling

r Assume Non-Negative r Show Iteration Results

timates -Derivatives--- each

ro Tangent r* Eorward ro tiewton

r Quadratic C central C Conjugate

v Click OK on the Sover Options menu.

V To solve the model, click Solve on the Solver Parameters menu.

V When the solver results window appears, it will indicate that solver has

found a solution and that all constraints and optimality conditions are

satisfied. Leave marked (by default) Keep Solver Solution and

highlight on Reports (Answer, Sensitivity, and Limits) and these will be

posted in three new sheets. The Detail Report is not usually needed.

Fi gure 3. Solver results confirmation

Solver found a solution. All constraints and optimality

conditions are satisfied. Reports

K Cancel Save Scenario... elp I

I t Keep Solver Solution

f Restore Original Values

A

Senitji~y

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 to produce 1.08 ha of sweet potatoes, o.o6 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 dollars.

A B C D E F G H I J K

1

2

3

4

5 Activities> Sw. pot. Sorghum Beans Peanuts

6 Variables> 1.08 0.06 0.00 0.86 RHS Resource

7 Constraints v Unit v> ha ha ha ha Use Constraints v

8 Land ha 1 1 1 1 <= 2 2.00 Land

9 M. Labor days 20 30 40 12 <= 40 33.79 M. Labor

10 F. Labor days 20 0 10 4 <= 25 25.00 F. Labor

11i Beg Cash dollars 0 0 40 35 <= 30 30.00 Beg. Cash

112 lEnd. Cash Idollars 1 801 201 45

Table E. Solver solution of the simulated system

551>=

134.71 End. Cash

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 discussion of the situation before you look at

the suggested matrix.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

II. INTRODUCTION TO EXERCISES

The following exercises are designed to introduce you to the concepts and

mechanics of linear programming (LP). The software used is Excel but other

spreadsheets are also amenable to 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 linear

programming models. The values used in the problems are not necessarily

realistic, but the exercises 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 responsibility

might make to these potential changes. The first task is to simulate the livelihood

system available to the smallfarms 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 diverse farmers to a proposed change in price

policy (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. Compare the solutions with your own.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 20o3

Exercise i. Basic LP matrix

Situation

You are a member of a team responsible for improving technology 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.

The team members want to pre-evaluate some alternatives they think might

interest the farmers. To do this, they want to use linear programming. 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 work with a typical household and its set of

resources.

Note: The values used in this and the following examples are not

necessarily realistic. When you create your own models, they should

be as realistic as possible.

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) farms 3 ha. For the year, they estimate 115 days of labor available for

production activities and $700 of cash available for production. The team

calculates that a goat requires o.I 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 return $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 wants to maximize cash available for discretionary spending (the

cash available after satisfying food and other household needs and necessary cash

expenses). At the very least they need $1ooo 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 $iooo, then, is available for discretionary

spending.

Exercises

I. Construct the linear programming matrix following 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

model before looking at the suggested model on the next page.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 20o3

Exercise i. Basic LP matrix

Situation

You are a member of a team responsible for improving technology 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.

The team members want to pre-evaluate some alternatives they think might

interest the farmers. To do this, they want to use linear programming. 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 work with a typical household and its set of

resources.

Note: The values used in this and the following examples are not

necessarily realistic. When you create your own models, they should

be as realistic as possible.

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) farms 3 ha. For the year, they estimate 115 days of labor available for

production activities and $700 of cash available for production. The team

calculates that a goat requires o.I 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 return $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 wants to maximize cash available for discretionary spending (the

cash available after satisfying food and other household needs and necessary cash

expenses). At the very least they need $1ooo 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 $iooo, then, is available for discretionary

spending.

Exercises

I. Construct the linear programming matrix following 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

model before looking at the suggested model on the next page.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 objective 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.)

A1 B CiD El F IG HI 1I I J

2 VARIABLES >> MAIZE HAY GOATS

3 :AMOUNT > 1.31 0.00 8.93i RESOURCE

4 RES/CONST ha ha) head RHS1 USE RES/CONST

5 ":LAND ha 1 1 0.1 < 3 2.21 LAND

6 LABOR days 40- 30 7 <= 115 115.00 LABOR

7 CASH $ 200 150; 49 <= 700 700.00 CASH

8 CASH INCOME $ 1800 300 330 >= 1000 5308.93 CASH INCOME

Table i. Suggested matrix and the solution for exercise 1

12

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Exercise 2. Intermediate 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 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 beginning 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 coefficients 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 use 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.

A B C D E F Gi H I J

i i

2 VARIABLES >i__ MAIZE HAY! GOATS1 -

3 :'AMOUNT >> 2.88 0.00 0.00 IRESOURCE -

4 iRES/CONST ha ha; head RHS USE :RES/CONST

.5 _LAND ha 1 1 0.1 <= 3 2.88LAND

6 ,LABOR days' 40| 30p 7 < -15 115.00 LABOR

7 HAY ACCTG tn 4; 0.6: <= 0.00 HAY ACCTG

8 CASH $ 200 150i 49 <= 700 575. CASH

S CASH INCOME _;$ 1800 300 330>=1000 175.0CASH I-NCOME

Table 2. Suggested matrix and the solution for exercise 2

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 less cash to the resource base). However, no maize was stored for family

consumption. The farmers estimate that 35 cwt of maize is required for family

consumption (the consumption constraint, one of the family goals), and this

is usually produced on the farm rather than purchased. The dependable yield of

maize is about 30 cwt per ha. Surplus maize is often sold.

Exercises

I. Modify the LP matrix from the second exercise to provide for at least 35 cwt of

maize for family consumption. Also structure the matrix so that any surplus

maize can be sold (at a price of $6o/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 contribute to the "sink," that is the aij 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.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

SA B C D I EI F I G H I JI KI L M I

1 SELL TRN

2 VARIABLES >> MAZE MAIZE MAIZE HAY CASH GOATS

3 AMOUNT >> 2.88 51.25 35.00 0.00 125.00 0.00 RESOURCE,

3.--- -_ o.;.~.. ... --- ------ ...... .....- .......... ..-

4 RES/CONST hae cwt cwt ha $ head RMS USEh RES/CONST

5 LAND ; ha 1 1 0.1 <= 3 2.88 LAND

6 LABOR days 40 ___T30 <- 115- 115.00 LABOR

7 HAY ACCTG tons -4 60.6 <= 0- 0.00 HAY ACCTG

8 MZE ACCTG cwt -30 1 1 <= 0 0.00 MZE ACCTG

9 _MZE CONS'N cwt -1 <-: -35< -35.00 MZE CONSN

10 __CSHBEG YR 20 150 1 __ 1 49 <= 700 700.00 CSH BEG YR

11e 3_CSHENDYRsd mS 60atrix and the so0utn fr e0 SHx e

Table 3. Suggested matrix and the solution for exercise 3.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Exercise 4a. Gender disaggregating 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 some 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 balances and labor for the men and the woman.

The family and the team estimate that the men provide 70 of the 115 days of crop

and animal labor and the woman 45. Labor requirements for the various farm

tasks are:

maize hay goats

men 30 25 1

woman 1o 5 6

The man controls $500 of the CSH BEG YR and the woman $200. Cash

requirements from each for the various farm tasks are:

maize hay goats

man 200 150 10

woman o 0 39

The man controls the cash from any maize sold and he splits the cash from the

goats with the woman. The man must have a minimum annual income, above the

CSH BEG YR, of $1oo to meet family responsibilities and the woman needs $200

income annually plus their $200 CSH BEG YR. Income distribution for the

income-producing activities is:

Maize selling goats

Man 60 130

Woman o 200

Exercise

I. Separate CSH BEG YR, CSH END YR and labor constraints into those of the

men (father and adolescent son) and those of the woman. Because both the

man and the woman require a certain minimum CSH END YR balance, set

minimum constraints so that the man has >= $600 and the woman >= $400

CSH END YR. It is convenient, also, to include a household or total CSH END

YR row although this does not need a minimum value (the initial constraint

will be >= zero).

II. Solve the program for objective functions that maximize i) M CSH END YR,

2) F CSH END YR, and 3) TOT CSH END YR.

III. Compare the solutions and interpret the results.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

I A B C I D E F I G H I J KI L M

N I

12_ ., -- -- ---- **- --l 'N I : ;

2 SELL TRN'

3 VARIABLES >> MZE MAIZE AE MAIZE HAY TRN M S TRNI F I GOATS

4 AMOUNT >> 1.67 15.08 35.00 0.63 29.84' 36.45 4.19 RESOURCE

5 REPsiCOrST ha cwt cwt ha 5 5 head RHS USE RESICOIST

6 LAND ha 1 1 0.1 <= 3 2.72 LAND

7 M LABOR days 30 25 1 < 70 70.00 M LABOR

S FLABOR days 10 5 6 <= 45: 45.00 F LABOR

^ ._. ^^ ^^ --..-.^ -- ---^ ------..- .- ^ -- -. -- .--- --- -- _. .- ^ ^ --

10, MZE ACCTG cwl -30 1 1 0 0 0.00 MZE ACCTG

11 MZE CONSN cwt -1 <= -3 .35.00 MZE CONS'N

12 M CSH BEG YR 200 150 1 10 = 500 500.00 M CSH BEG YR

SFCSHBEGYR 1 39 ;-200 200.0F CS BEGYR

4 M CSH END YR 5 60 1 130 >= 600 1479.84 M CSH ENID YR

15 F CSH END YR 1 200 >= 400 875.16 F CSH END YR

jl __ot CSH gENDYR 6i an 1 ___ __ 1 330>s 0 5.00 _TSOT CSH END YR

Table 4a. Suggested matrix and the solution for exercise 4a (TOT CSH END YR).

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 matrix 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 LP matrix.

It is probably most convenient to create the 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 function 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, the 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 maize, cell Dll

in the matrix, shows the formula =INPUT!C21. This formula means that the

coefficient for D11 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

orderfor the formula to appear in the cell, there is a spacejust 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 only 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 maize yield, cell D14, and maize

consumption, cell L15, 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 output table for this matrix is found on page OUTPUT. Cell C4

on page OUTPUT has the formula =LP!D8. This means that the area in maize

taken from the solution of the matrix on page LP, cell D8, will appear in cell C4

on page OUTPUT.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

2 SELL IRN

31 VARIABLES>> MAIZE MAIZE MAIZE HAY TRNMS TRNFS GOATS

4 AMOUNT>> RESOURCE

T1 PESrCONST ha cwt Ct ha I S nead RHS USE CES'CONST

6 LAID ta 1 1 01 <- =iIJFUTIC6 000LAND

7 M LABOR days =IrPUTIC21 =IIJPUTID21 =IIjPUTlE21 <= =rPUTIC7 0 0 MP LABOR

8 LABOR days =IliPUTIC22 =IINFUTD22 -IlPUTIE22 <= IIPUTICB 0.00 LABOR

9 HAY ACCTG tons -INIPUT1C29 06 <- 0 000 HAY ACCTG

10 MZE ACCTG CM =-IJPUTLmC,8 1 1 0 0.00 MZE ACCTG

1 MZE CO1S cml -1 <= =-INPUTIC13 0.00 ME CONSN

2 MCSH BEG YR S =INPUTIC35 =IrjUTID3S 1 =llFUTE35 =INPUTIC14 0.00 M CSH BEG Y

1 F CSH BEG YR S 1 =ijPUTE36 = =INPUTIC15 0.00 F CSH BEG YR

.41 M CSH END YR S INPUTID28 1 130 n 600 0.00 M CSH END YI

15i F CSH ENDYR S. 1 200 > 400 0 00 F CSH ETID Y

16T TOT CSH END YR S =IIFPUTID28 1 1 330 > 0 0 0. ToOT CSM END

19i

SELL TRN

21 VARIABLES MAIZE MAIZE MAIZE HAY TRN MS TRN F S GOATS

22 AMOUNT > RESOURCE

31 ES'CONST ha cMt cw ha S head RHS USE FES'COtlST

241 LAND ha 1 1 0.1 < 3 0.00 3LANID

25 MLABOR days 30 -26 < 70 0.00 M LABOR

-26 F LABOR days 10 5 6 45 0.00 FLABOR

217 AY ACCTG tons .4 0.6 < 0.00 MAY ACCTG

28.1 MZE ACCTG cw -30 1 1 0.00 MZE ACCTG

.' MZE CONSSN cwi -1 .35 0.00 MZE CONS

30 M CSH BEG YR S 200 150 1 10 = 600 0.00 M CSH BEG Y

31 F CSH BEG YR 5 1 39 200 0.00 F CSH BEG Y

32 MCSH END YR S 60 1 130 >- 600 0.00 M CSH ErD Y

33 F CSH END YR 1 200 > 400 0.00 F CSH END Y

STOT CSH ENDYR 5 60 1 1 330 >= 0.00TOTCSH END

4i \LPI piNP JT/ OUTPUT 1__

Table 4b. LP page linked to INPUT and OUTPUT pages. The top matrix shows

the links and the bottom matrix shows the values.

SA B C D E

1;

2 INPUT TABLES

4 Land and latbo avadarLe

6 Lind i" 3

'

7 Male bor 70

8 Female labor 45

91

10

11 Cor.nurpl.on and tg.rn..ng year Cash

131 Mize cons inmative) 35

14 IA cash be 500

15 F cash beg 200

16T

S Labor requirements for crops and lvestock

19 -A -C D- E -

20 .. Maze Hay Goats 1

21 |Malelabor 30 25 1 2 OUTPUT TABLE

22 Female labor 10 51 6 3

-23- 4 W--- M fernal ~ LP'DB

2. Yields and prices 6 Hay (al -LFP G8

26 6 Coat81 ead L PUS

27 Yield Price 7 Il.sA:e Solda Iel) =LPrE6

28. |Ma 30 60 8s Male end-year cash ) =LPM18

29j IHay I 9 Female end-year cash () =LP!M19

30 10 Total end-year cash (S) =LP!M20

31

32 Cash requirements for crop production

33j 12

34. 'I Maize Hay 1Goats 13

35-; Male beginning cash 200 1501 10 14

360 Femalebeg cash 0 0 39 1'

37 li to t *1'.

-a --l 4c INiPig l o LPUoTr ... J_ l Jf---- P

Table 4c. INPUT and OUTPUT pages linked to the LP matrix

R

R

YR

R

R

R

YR

>

jnj I --" -- -----

L M I I1

l a i n e n i F P

(i n1 I i J I

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Exercise 5. Resource flow and integer solutions

Situation

The simulation achieved in Exercise 4 by maximizing household income is more

realistic than previous solutions or than solutions that maximize income to the

woman or the man. However, the team and the family members note a few more

discrepancies. By using all 70 days of the available male labor and all 45 of the

available female labor, they are able to produce more maize than usual, allowing

the man to sell 15 cwt. They feel that labor is being borrowed from one period to

another so they decide to consider labor and cash resources by semester.

Note that smaller divisions such as 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 they spend time hunting). The woman, who does not hunt, has 25 units of

labor available in the first semester and 20 in the second. They have 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 for the

woman. 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 woman's income from the goats is the same either

semester ($200), but from selling the goats in the first semester the man receives

$80 and $130 in the second. The man requires a minimum end of year cash of

$500 and the woman $200. Any cash received in the second semester is

considered year end cash.

Maize, produced in the first semester, uses 30 days of the men's labor and to of

the woman's as well as $200 of the man's cash. The man sells 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 woman's labor in the

second. The goats require 0.5 days of the men's labor each semester and 3 days of

the woman's labor each semester. Goats also use $5 of the man's cash each

semester, $20 of the woman's the first semester and $19 the second.

Exercises

I. Separate labor and cash into two semesters for both the men and the woman.

Previously there was only one time period, with cash balance rows for the

beginning 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 beginning 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 woman, and for the household. Be sure

that any cash left over in the beginning of semester one is transferred to the

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 year cash is maximized for i) the man, 2) the woman

and 3) the household.

III.In order not to have a fractional amount 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/total

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

LP, all resource 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/o), and the solver

will not work. To avoid 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

_ C i D E F ___ I __ I J K JL M __ N_ 0_ P 0 __! R_1 S

2. SELL. TR TRUS TERMS TRFS TRFS 5 ELlL SEL

3 V4FiLES MAIzP E MAIZE M6IAE MAY Ei-EID PBEY B1-B61l B6-EYR GOATS GOAT I GOAT I1

4 AMOUIT .. 1.58 12.50 35.00 0.30 2.33 98.33 1000 172.00 2.00 1.00 1.00 RESOURCE

S RES/CONST he cWa cWt hai $ S S head head head RHS USE ES.'COtNST

6 LAND ha 1 1 0.1 = 3 208LAND

7 M LABOR I da 30 6 0.5 < 50 50.00 M LABOR I

B M LABOR II aap 20, 0.6 < 20 1.00 M LABOR h

9 F LABOR I aa,s 10 3 < 25 21.83 F LABOR I

10 F LABOR II as 5. 3 20 7.50 F LABOR I

11 mAY ACCTG o.s -4 06 <= 0.00 HAY ACCTG

12 MZE ACCTG cWl .30 1 1 0.00 MZE ACCTG

13 MZE CONST1 rCl .1 .= 35 35 00 M2E COIIST

14 GOAT ACCTG ead -.1 1 1 <= 0.00 GOAT ACCTG

15 IMCSl BEO I 5 200 150 1 6 400 400.00 M CSM BEG I

16 F CSM BEG I 1 20 50 0.00 F CSH BEG I

17 M CSH BEG g -1 1 5 -80 = 0.00 M CSh BEG

18 F CSM BEG I S -1 1 19 -200 = 0.00 F CSrlBEGI

19 M CnI EID YR S 60 1 130 > 500 978.33 M CSH EFID YR

~0 FCSM EIND I, 1 200 >= 200 372 00 F CSM ED YR

21 TOTCSHENDYR $ 60 1 1 330 >= 1350.33 TOT CSM EJD Y

22,

23 T LABOR 6.33

2 CSWLAB 15.64

Table 5. Suggested matrix and the solution for maximizing cash productivity of

labor when goats are set integers for exercise 5

'R

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Model calibration and validation: Is it ready to use?

Linear programming models should never be considered as finished tools.

Rather, they are always a work in progress, always capable of being improved

with 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 conditions 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 modeler's sense of how well the

model reflects the conditions being modeled. Care needs to be taken not toforce

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 consideration. It

could be that yields of the livestock feed produced on the farm were too high,

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

validation obviously helps improve the model and is valuable to the modeler. It

also helps maintainflexibility in the model so it can respond to the kinds of

stimuli 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

searches 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 instances, 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 could 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

solutions. A "t" test can be used to ascertain the level of probability that these

two series represent the same population. But here again, the modeler (or a

superior) must decide at what level of probability to accept the validity of the

model. Is a 5% level required or is lo%, 15% or even 20% adequate? If a modeler

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Model calibration and validation: Is it ready to use?

Linear programming models should never be considered as finished tools.

Rather, they are always a work in progress, always capable of being improved

with 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 conditions 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 modeler's sense of how well the

model reflects the conditions being modeled. Care needs to be taken not toforce

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 consideration. It

could be that yields of the livestock feed produced on the farm were too high,

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

validation obviously helps improve the model and is valuable to the modeler. It

also helps maintainflexibility in the model so it can respond to the kinds of

stimuli 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

searches 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 instances, 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 could 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

solutions. A "t" test can be used to ascertain the level of probability that these

two series represent the same population. But here again, the modeler (or a

superior) must decide at what level of probability to accept the validity of the

model. Is a 5% level required or is lo%, 15% or even 20% adequate? If a modeler

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

or the modeler's organization 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 better than an artificially

constrained model that is forced to fit at a 1% or 5% level of confidence.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Exercise 6. Assessing alternative technology (hypothesis testing)

Situation

The integer solution to the fifth exercise, when maximizing the household 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) and 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 activities.

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

alternative 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. About 0.625 tons of hay should be sufficient for each goat fed

maize. Labor would increase to about to days per goat (o.75 days each semester

for the man and 4.25 days each semester for the woman). The man's cash

requirement would be the same ($5 each semester per goat) but the woman's

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 system) 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 with existingfarm 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.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

III. Solve, discuss and interpret the solution and use it to accept or reject the

hypothesis.

I e IC o I E F i G i H i I J I K L I UM N ; I P i 0 IT I U i V

1 SELL' SELL SELL

2 SELL TR TRUST TRMS TRFISTRF MAIZE SELL SELL SELL SELL.

3 VARiABLES A.M IIAIZE Ma ME HAY Bl-B E BIE) Peteh BQH-EYR GOATS MGOAT I MGOAT I GOATS GOAT I C

I4 AJAMO~ 1.58 12.50 33.00 0.30 28.33 9.33 1000 172.00 0.00 000 000 2.00 100

5 PESCOFh hoa A t ha $ S head head head head head

6 LAD ha 1 1 0.1

7_ M LBOR I alf 30 6 0.75 0.5

8 LABORI dlaf 20 0.75 065

9 F LABOR I days 10 42 3

10 F LABCOR R as,% 6 425 3

11 rAY ACCTG Iol -4 0621 0.6

12..CE ACC1G cM .30 1 1 4

131 'E CC0I6S1 CIA .1

I4 GOAT ACCTG heao -* 1

I_ M GOAT ACCTG head .1 1 1_

1 MCSCHLOEGI 5 200 150 1 6 6

II F CSH BEG S I 2 25 20

'18 CSiBEGPI .1 1 6 -107 6 -40

19 F CSH BEG I .1 1 26.2 -.268 19 -200

O :M CH END YR S 60 1 17

21 FCSih EIIYR 1 ?I

22 1I1 CSEtID YR $ 60 1 1 444

Table 6. Suggested matrix and the solution for exercise 6

OAT n

1.00

head

RESOURCE

RnS USE rESCONST

3 2 08 LAND

S 50 0 00 M.LABOR I

< 20 7 00 M LABOR I

S25 21 83 F LABOR I

< 20 7 50 F LABOR II

S 0 00 HAY ACCTG

0.00 M2E ACCTG

< -3S J3 00 MZE COFSll

S. 0 00 o OAT ACCTI

0 00 M CCAT ACCIC

400 4- 00 0MCEBEGGI

50 50.00 F CSH BEG I

0 00 M Cn BEG H

S 000 F CE BEG I

I 500 I1833 M CSH END YR

1 200 3n100FCCIEIDYR

I > 13033 10 CTSr EIOJDYR

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 8o%

of the country's maize. The Ministry of Agriculture 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 to the farm comes

to an average of about $2.475 per kilogram of N, too high 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 more

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

about 6 additional days of the men's labor during the first semester and about 2

additional days of the woman's.

Exercises

I. Modify the matrix from Exercise ; to include the new activity of fertilized

maize (remember to keep the present maize activity so the program has to

choose between ways to produce maize).

II. Analyze and interpret the solution. 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 alternative more attractive. Take

another look at the maize goats alternative (Exercise 6) by adding the

fertilized maize. Discuss the results.

1The average small farm production function is estimated as Y = 30 + 0.2175 N 0.000675 N2, where Y is

kg ha-1 of maize and N is kg ha-1 of nitrogen.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 8o%

of the country's maize. The Ministry of Agriculture 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 to the farm comes

to an average of about $2.475 per kilogram of N, too high 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 more

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

about 6 additional days of the men's labor during the first semester and about 2

additional days of the woman's.

Exercises

I. Modify the matrix from Exercise ; to include the new activity of fertilized

maize (remember to keep the present maize activity so the program has to

choose between ways to produce maize).

II. Analyze and interpret the solution. 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 alternative more attractive. Take

another look at the maize goats alternative (Exercise 6) by adding the

fertilized maize. Discuss the results.

1The average small farm production function is estimated as Y = 30 + 0.2175 N 0.000675 N2, where Y is

kg ha-1 of maize and N is kg ha-1 of nitrogen.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

S_ B C 0 E F I G H _1_ J K I L MI 1_0

2 FERI SELL T- TRMI TRFiM TF RFI 0s EEU SEJ

3 -ARIIABLES a MAIZE MAZE L.A'2E MAI4E hAY St1-8 BuEYR B~-61 BI-EYR GOATS COATI COA

4 AAMOUn '>s 1.18 03383 15.54 35.00 0.30 0.00 0.00 10.00 172.00 2.00 1.00 1

6 FCPeorT T h h a w Ctn W ha 5$ $ head head hei

6 LASSD ha 1 1 1 0.1

7 M LABORI says 30 36 0.6

8 M LAOR II days 20 0 .5

9 F LABOR I das 10 12 3.

1 F LABOR n aiys 5 3

11.MAYACCTG Ions .4 0.6

12 MAZE CCTG cAm -30 -45 1 1

13 MIE CONSlI cM -1

14 GOATACCTG hea *11 1

15 M CSM BEG I S 200 32375 150 1

10 F C S BEGI 5 1 20

11 M CH BEG I I 1 1 6 -80

18 F CHBEC1 -1 1 10 -200

19,MCSHEIIDYR 5 0 -1 -

20 F CSH EODYR 1

21 IOCT Cr EID YR 60 1 1

Table 7a. Suggested matrix and the solution for exercise 7-I

i P I S JI T

.LL

,1 h

.00

KI

1a

130

I =

<=

<=

1 <=

130 > ,

200 -=

330 -,

RESOURCE

RHS USE rESCOlJST

3 202 LAND

0S 50 00 LABOR I

20 7.00 M LABOR n

25 21 83 F LABOR

20 .S0 F LABOR H

0.00 KAY ACCTG

0 00 MZE ACCTG

3E .35.00 lE COINST1

0 00 GOAT ACCTG

400 00 0 MCS BEG I

50 5O00 F CSrBEG

0.00 M CSH BEG I

0.00 F CEMB EC I

500 1132.69 MCSFH EN YR

200 312.00 F CSH END YR

1504.69 TOT CSH END YR

S B C l DI E i F GIH I J i K I L I M N 0 P Oi R IS T iU V L W_

1 "

2 i :FE" R -FET SEIL T TIMS TRMi5TFS TRF MAIZE SE. SELL SELL SELL

3 L~aca.LES a MAt'E I.'tE UACEMIOCE HAY En EBi E-E'IR BI-BI EliEYR GOATS 00TI7 MGOATP GOATS GOATI 0G0 41

4 tAIOUvle 118 0 31 15.543500 030 0.00 0 10.00 1 17ZO 0. 00 000 000 200 1.00 1.00

S-E~'.:C, S1 he h ewIt et ha S S S head head head hed had head

6 LAlID ha 1 1 1 0 1

7 iMLABORI days 30 36 5 071 05

b M LAEORI days 20 075 06 cm

SFr.LASCO I days 10 12 A 25 3 a

10 F LeAOR U days 5 25 3 a

11 HAVACCTG IonS 4 0621 06 a

1V MCE ACCIG cm -30 -15 1 1 4
1 L CE CCO.'rSa ev -1 .

A GOATACCTG head -1 1 1 <

15 M GOAT ACCIG head -1 1 1

16 MCe" BEG I 200 32308 150 1 6 56

17 FCSH BEGI 1 2625 20

18 MCSE.GI S -1 1 5 -107 6 -80

1S FCSMBEGI -1 1 26-25 .268 19 200

Z0 MCSr Elr YR S 60 1 175 130 >"

21 F CS" ElD V. V 1 269 200 -

.2 TC.TCSHEID VR $ 60 1 1 444 330 >=

Table 7b. Suggested matrix for and the solution for exercise 7-III

ESCURCE

RBS USE CESCCAOST

3 2 02 LAMD

5 50 0 00 M LABOR I

20 1 00 M LABOR I

25 21 3FLABOR I

20 7.0 F LABOR

00 AY ACCTO

0.00 MLE ACCTG

.3 .3500 LME COliSP

000 GOAT ACCTG

0 00 M GOAT ACCTG

400 0 00 MC M BEG I

60 5000FCSHBEGI

00 M C!n BEG I

0.00 F CSH BEG I

500 1132.69 MCSHENDYR

200 372.00 FCSH END YR

04.M9 TOT CSH END YR

+

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Exercise 8a. Household composition, food security and production labor stress

Linear 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 realfarms from which the survey was taken, and 2) averages mask

diversity and individual 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, most 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.

It 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 a range of household compositions representative of a

community is utilized, and solutions obtained, these can 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.

The 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 to 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 production 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, to cwt; adolescent male, o1

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 labor. Incorporate these tables into the matrix from Exercise 5 to

make them interactive.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Exercise 8a. Household composition, food security and production labor stress

Linear 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 realfarms from which the survey was taken, and 2) averages mask

diversity and individual 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, most 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.

It 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 a range of household compositions representative of a

community is utilized, and solutions obtained, these can 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.

The 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 to 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 production 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, to cwt; adolescent male, o1

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 labor. Incorporate these tables into the matrix from Exercise 5 to

make them interactive.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

II. Solve for the following household compositions:

HH No. Adult male Adult female Adoles. Male Adoles. Fer Children

1 1 1 0 0 0

2 1 1 0 0 1

3 1 1 o o 2

4 1 1 1 0 1 (Exercise5)

5 1 1 0 1 1

6 1 1 1 1 0

7 1 1 O 1 O (male leaves it)

8 1 1 1 O O (female leaves lt)

In some cases there is no feasible solution because male labor is constraining

at a level too low to produce sufficient maize for family consumption.

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 original 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 consumption, excess male labor requirements (male

stress level), and end year cash for each of the solutions obtained above with

Exercise 5 (i-1-1-o-i).

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 population of

the community (1-1-o.375-o.375-o.625). 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?

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

II. Solve for the following household compositions:

HH No. Adult male Adult female Adoles. Male Adoles. Fer Children

1 1 1 0 0 0

2 1 1 0 0 1

3 1 1 o o 2

4 1 1 1 0 1 (Exercise5)

5 1 1 0 1 1

6 1 1 1 1 0

7 1 1 O 1 O (male leaves it)

8 1 1 1 O O (female leaves lt)

In some cases there is no feasible solution because male labor is constraining

at a level too low to produce sufficient maize for family consumption.

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 original 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 consumption, excess male labor requirements (male

stress level), and end year cash for each of the solutions obtained above with

Exercise 5 (i-1-1-o-i).

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 population of

the community (1-1-o.375-o.375-o.625). 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?

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

I A j__B CI D i E J_F G_1_H I MJ t L M N_LOP_L--_ __

1

2 SELL FAZE M CASH F CASH M CASh F CASH SELL SELL

3 VARIABLES > MAIZE MZE TRN HAY TRN HI TRN TRN I-E TRNII-E GOAT GOAT I GOAT I

~ AMOUIIn > 1.58 12 50 35.00 0.30 28.33 10.00 848.33 172.00 2.00 1.00 1.00 RESOURCE

5 RESICOIIST hi cM cw a $ S S heaa read head RhIS USE CESCOiST

6 LAND ha 1 1 0.1 < 3 2.08 LAND

7 ML8BORI ays 30 5o 05 50 50 00 M LABORI

8 in MLABORq 1 days 20 05 -= 20 7.00 M LABOR

9 F LBORI days 10 0 3 < 25 21 3 F LABOR I

10 F LABOR I days 0 6 3 = 20 7.50 F LABOR I

1 HAYACCTG ions -4 06 = 0 00 iAY ACCTG

12 LI.ZE ACCTG orw .30 1 1 0 00 MZE ACCTG

13 MZE COUS'N wl .1 35 .35.00 MZE CONS71

I4. GOAT ACCTG neba -1 1 1 0.00 GOAT ACCTG

i5 M CSr BEG I S 200 10 1 6 400 400.00 M CSH BEG i

16 F CSH BEGI 0 0 1 20 50 0 F CS BEG I

-11 M CSH BEG II 0 -60 0 .1 1 6 -80 00 M CSH BEG II

18, F CSH BEG II 0 0 -1 1 19 .200 0.00 F CSH BEG H

19; M CSr END YR S 1 130 >= 500 78.33 M CSH END YR

20' F CSH EIDYR S 1 00 ,200 372.00 F CSH EIID YR

21 _hHCSH EIIDYR S -_ 1 1 330 1350.33 iIM CS EID YR

Table 8a. Suggested matrix and the solution (family 1-1-1-o-1) for exercise 8

S.A I B C _.o.._

-8 1 a,, male .. I

1' C

II ble r.ale om

17- I a 12

2 1- 8 0

26 table 3 Labr 'labAI.

27 1I

S:.,saen .. ,

33

3i Fa . -i: Fam l 7- T7-

10 ta' mar -nt.a.T.

36

117

Table 8b. Suggested input tables connected to the matrix

.Household ,ummaa__ ,- t'a'

Houehold composition. 1.1.00-0 1-1-0U0-1 11-0-0-2 1-1-1-0-1 11-0-11 11-1-1-0 1-1-0-1-0 1-1-1-0 Aver-

Snot feasible not feasible not leas ble

Maize (ha) 0 92 0 92 1 18 158 121

Hay Iha) 0 30 0 30 0 j0 0 300 030 0 15

Goas (head) 2 200 200 r 2 00 -. 2 00 200 100

,aze sold (cwt) 5 50 2 50 '-12 50 -" 7 50 15 5 563

Male cash end balance lS) 69167 511 67 9 78 33- -- 678 33 118 33 593 33

Female cash end balance (S) 37200 372 00 372 00 -757.. 372 00 1' 372 00 211 00

Total cash end balance (5) 1063 67 683 67 ..." 1350 33 1050 33 '" 1530 33 804 33

Table 8c. Different solutions for different household compositions

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 over 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. Animal

inventories must be maintained to keep track of births, young stock prior to

breeding age, older animals, death losses, sales, and home consumption.

Exercise ga. 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 considered 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 how many animals it would

include, but consider only female goats. Do not consider purchasing goats. 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

separately 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 coefficients are blanks.

II. Solve the program for objective functions that maximize 1) the sum of year-

end cash balance over 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 integers.

IV. Discuss and interpret the results.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 over 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. Animal

inventories must be maintained to keep track of births, young stock prior to

breeding age, older animals, death losses, sales, and home consumption.

Exercise ga. 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 considered 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 how many animals it would

include, but consider only female goats. Do not consider purchasing goats. 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

separately 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 coefficients are blanks.

II. Solve the program for objective functions that maximize 1) the sum of year-

end cash balance over 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 integers.

IV. Discuss and interpret the results.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 200oo3

&s.asaa&S*!**S^*i i yH

-!!;!;!

t!;i, as

*. NBaL*COf

ml~ iW^Sm

m SiS

,' .tssy

~"' ~i" ti

tsr~:a'a'

jssasi

-mi.. ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ r 11.1111.1- n 11.- j 1111. u*K !!S.'

S9ai. Suggested structure of the three year matrix

L*;.

R2

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

~ SELL TR TRMS TMS TRFS TPF m TR TRa TRN SELLa SL TFNMCS4TINFCSH SELL TR TRMS TRM TmRFI TRFI ADULT "vL

3 VAFABLES. MA1E MAIE MAZE HAY M1 BMEYR BMB BSER GOATS 012 KCONS KR2 GOATI OAT I YR62 YI MAIZE MAIZE MAIZE HAY Ea BIYR BlM WEYR GOATS GOATS

4 AMAOuT LS LBS 25.86 G. 1.75 LS a.B L2 Ls, Lm isM .24 L7 SUMS M2RM LU 12.M M34M LU 225.21 2l12t 31240 274M. LSM L.W

S FIEcGJST Ma c cci ha S 5 6 A Ioca h..d hiad

S LA ORI. I I 0.i'

F MLABORI dap 26 5 05

MLAB0RI dtp 20 6.5 I

S_ FPLAS.i I tap 5 1

Il HAYACCTG tos -4 U5

1 'MZEACCT t -M 0 1 I1

0 WMECONJ ci *4 I .

4 KCOSOOAT ihed -2 1 1 1 1

1 CONSGOAT hied *I

U ADULTGOAT hed *03 1

17 MCSHBEGI $ 200 B 1 S

U FCSHBEOGI 20

U MCSHBEGI $ 4l 1 5 40

20 FCSHBEGI : -1 I 1 -4 I0

21 MCSHENDYR 50 I 00 -1

22 :FCSHENDYR 20

23 TOTCSHEN*YR S I st o0

4 'LAND ha0 I I 1 g

2_ GOATBEG had I

16 GOATYL ha.d

27 MLABORI dags 30 5 S

28 MLABORCI das 20 ,

22 FLABORI tdap .s 3

306 FLABORI tap;

t :HAY ACCTB Q on i4 4 <3

32 M2EACCTG o 1 1

33 IMECONS o *. t4

34 IDOCSlAI A NI d 4

35 CUrL GCAT Med

X ADrLTGOAT .ead I -U0 43

3? MCSHEIEGI 200 5

38 FCriBE5GI 8 1 20

3s MCSLBPE.I S i 1

40 FCSH BEGI I .1 1 I

41 MCSHFI*OYR I I i

42 IFCSHENDYR I .

43 ITOTCSHEM ND a

4 M GOAT YL icath

4 MLABORI t I

4g MLABOI ta Is

4 F'MLABORI dags

50 FLABORI daps

1 HAYACCTM to s

62 MZEACCTGO N

63 MZEWCOJSYJ cvi

6j KMSCaa nGrie

55 CONS'OOAT hed:

W ADULTtOAT h.d |

_7 MCSHBEGI $

8 ;FCSHBEGI

56 MCSHBEGI a

62 FCSHENDYR 4

62 TOTCSHENDYR

Table ga2. Suggested matrix and the solution (three year end cash) for exercise ga (part I)

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

SAD 1 AE IAF A M___ AJ L AK [ AL AM I AN A I A I A I AR AS AT AU I AV AW .' A AY AZ A BB C BO

Tai Tl m I SEULL ILc ICMnOH TmINFCSH SELL TR ; BRMI RM TRF TRFI ADULT Vd TR TI TMi SUBL SUL

01-2 CONS KI-2 OATI GOATII YRA YSZ MAE MAIE MAIE HAY 1-a Bfla Bi I BYWR GOATS GOATS G '2 CONS KH2 GOATI GOATI

Le! 25 .5 L am M M2M 414.60 LU a35 5 n 524 3Lt 3*5 Mu 44250M 4U.L L.M .M L 2L. 9.0 LU. Lu FI80URCEh

I 4 f" USE MSCGT

3 LU LAWO

4 50 ISM MLABORI

Co 20 h75 MLABORI

to 25 a3u s FLABOR

S 20 1.30 FLABORI

4.5 mrHAVYACCTS

S. -I MZEACCT=

3X -s.00 Mz CO Sf

u 4.5IM KIOSGOAT

8 *2 -4N0 COr04GOAT

4W IN MCSPEIiGE

i 0 I>SM FCSHKI EI

I i i: i Ii -1 ; I i *I MCSnfEIl

900 56.0 MCSHENIVR

Sf 4 I438 TOICS.aENDfR

24

25

'71..1 1 4

26 i

27

2o 1

1* I

2 ... . .. .. : -- \.

6030

4211 200

43 330

-4H4

53

_61

Table 2. Suggested matrix and the solution (three year end cash) for exercise a (part II)

Table 9a2. Suggested matrix and the solution (three year end cash) for exercise ga (part II)

<4 3 2W LAND

So 06.00 OOATGO

o I IU GOAT VLG

< 5. I i.M MLABORI

" 20 1."M MLABORI

<* 20 I FLABO-R

20 1 U F LASCAI

<. NI HAYACCTG

L< MZEACCTG

. 4 -5. LMMZECONSW

So I.M K3S GOAT

a .2 -LiZs CONms AT

44 I6.6 ADULTGOAT

L :MCSHBEGI

*a IN FCSHBEGI

a* 'gLM MCESBEGlI

n B.F TOPICS ENDYR

. < 3 L ISLAND

Ia .5 354.5 M IACEC

<

, 2 LU St'MLABI

1* 25 II flMFLABOtI

,* 20 LU-.0 FABORI

0i. HAY AtCTO

- AU HAYACCTG

H 041*: MZE AOCTN

*. -25 -i54, MZtCOFrA

SLI IOS K1S OAT

1 2 ,2 -. 'ICONS GAI

I- 5 .IM ADULT OAT

SIO.,MCSHBrEG

- i50 FCSHEO4I

S* SA MCSHBEQI

L :* SM FCSHBEOI

p. 62 6 1210.21 MCSHENOIR

, 200 IMtS FCSEH E YR

IA. mIL31 TO!ICSHENOI

UMi 5491U.U2

23

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Exercise 9b. Perennial crops

Situation

The farm family decides it would like to consider banana production. They

estimate that the household could consume 1oo kg of bananas and hope surplus

bananas would be sold. The team estimates men's labor for bananas is 5 units in

the first semester and 20 units in the second. The woman would contribute 5

units of labor in the first semester and to in the second. After the first year, the

man and the woman each would contribute 5 units of labor each semester. The

first year the bananas would require $50 of the man's beginning cash. After that

no cash is required for the bananas. Banana yield is estimated at about 6ooo

kg/ha beginning the second year. The price of bananas for sale is $0.30 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.

Exercises

I. Modify the LP matrix from the Exercise ga 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.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

i1 IC _r L H I J K I L j I_ _I 1 Q1 P T I

SK TR i RMt TRMIM TRrI TRF( ni ru TIN TRd stl iiL. INACTMCIBiFCSH

i vuuailS MAI uMA~ E MAE HAY a Mi 894R EB BIWORBAANAWGATS oir K COu K2 OOATI WATI YR5 YR

_4J AMOUNT, LI I I no 1- Ill IN IS I a Ll I. IN I124 L6 II.tS 2U1L

5 FIEICONST ha o- p. I I I 1 h.ad pua Mad

SLA j I I I I 01

_i Muacihl dep a 6 5 o

I MLA 0tI di 20 20 O

I; FLAORI a 0 l 3

1 F LAORI W 5 10U 3

I HtYACCTQ n4 MI

juI MZEACCT M 0 1 I

a CE CONSW Ca 4

N I iaOS AT h 1 1

.5 CONS GOAT h. .

M ADATOCIAT h U

17 MChLEI 200 1 1 50 6

_i FCSa0El I I 20

i' MCSa EOI 4I I 1 4

20 F CSh SE I I I1 1 100

21 MCS.DIO MI s I 00 *I

221 FCS.EN, IYR 2m

3' TQTC3o oF I so I I-

24 I LAlJ hi I

2 1' GO AT Y h .

27 I iANNr A fr ,2

2? MLAH l.l i .

SMLAUEI Uap

1I FLAE tI t ; i ; ,

U MT CClil

54 M E CL a sI ... . .. ... .

31'

36 CCMi -uAT heat

31 ADULT CAT ht *

S ACC i

1 B4 AA WSACO ISI2 I

io M i C SEC.I .

41 FCSmOn -I

I2 MCSiMa.uI ;

3 I FC-4AGI

44 MCSKhOlR 2

tI FCLHOIE I '

I i 1 .C StN y

45I AGA T lji e h

Ml 0~nat&a C.rJ -

_ll 62 MLABORI < .

M f MLA&MRI up

51 F LAEORI -si

M4 FICYJI l I :

56 MEACCTO o *

1 I MZE CSOSN I

U0 I Cf T h"fV S

_i WMCOl =

6- FCSM6[ai.

Ta MCS ShEhal e : '

6?7 MCSHalCDYR I i 8 -

Table 9bi. Suggested matrix and the solution (three year end cast.

ul I V IX L Y I ._ AIQ AC. _AD _4.j AF_ AlS Al -I

Sil B 'I TRMI' TRMI RFI TRIg il ImL iTR ADuT Y. T1N

4M2E MAE MAE HAY I 1Ml l 4R MY BAAIB AYR ABANAhA BANANA WOATS QDATS 012

11 IUI os I 34 n I 22 *il IJAS I 14 IS 12111U6 MIN L L see LAU.

a1 IM

I

I

I 1

4

*I

.1

-2

44

aoo0 1 I

.

*3

.0.3

for exercise 9b (part I)

i

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

I Al AJ AK AL I AM AN A I AR AV AV AY Z BB BC

_ TF~N TFIN SE LL SELL TINMCSI-'TFVFCSH SELL TR TRM s IRM$ TRF$ "TRF$ YR3 SELL T1T AU.LT YRL 1TF 1' TF T10

3 KCONS Ki-2 GOATI GOATI YRI2 YRI-2 MAZE MAiZE MAIZE HAY SBWI 'BSEWF BI-E B sYR BANANA BANANA BANANALGATS3OATS 01-2 COS K12

4 1 O. .660 L.0 2.00 13I1.5W 414.58 1l 10.15 3.e6 0.24 o201l3 21.l0.83 442.5' 412.1 0.5 3271.00 01.86 I.8 L.O0 L2s 2.00 6.

a--,

- i i i I : ..'

S I l I : : !. : *

12

-53

20

26

27

28

II

- 620

34 1

41

38

4o -0 4

46

52 .

54

sa

57

so

604

Table 9b2. Suggested matrix and the solution (three year end cash) for exercise 9b (part II)

_ E IF E BH a BJ

i ci SEL'LA

'GOATI OATI

0 I. 1.2

'I

-S:

RESOURCE

R48 USE RESICONST

* 3 2.6 LAND

* 50 5IM MLABORI

* 20 2060 FMLAORI

* 25 25O0 FLASORI

* 20 15J0 F LAECR I

S o0.00 HAY ACCTr

a 600 M2EACCTO

S.-35 -35.00 MZECONSh0

* A 0.00 KDS GOAT

* -2 -2.00 CONS GOAT

I .0 ADULT IAT

400 40.00 MCSHBe I

50: 5O0 FCSHBEGI

0.0 GMCSHBEGQ

OAO FCSHBEGQ

5* 00 510J0 MCSEICJDYR

* 200 200.J FCSHIDjYR

S 1412 TOTCSHEl1YR

. 3 2.1 LANM

0.0 GOATBEG

* 0.00 GOATYLm

* 000 BANArjAYR2

* 50 500O M.LABORI

* 20 111 MLABS~ I

I 25 23.11 FLABORI

S20 10.31 FLABORI

* 0. HAYACCTO

S 00 MZEACCTQ

S*-35 -J350 MZECONSW

SO8 KIDS GOAT

-2 -2.0 CONS GOAT

0.0 ADULTGOAT

Oj' BANI4ArAACCTI

*' -1O -lOJo BSAM'ACOs N

eAG MCSHBEGI

008 FCGHBEGI

OJO MCSHBE.GI

S O FCSHBEGI

500 WOJA MCSHIEJaYR

S200 20.0J FrCSHENmYR

2564 l l lOT CSD I YR

3 .4? LAND

S OJDO GOATBEG

S 00O GOAT LO

S OO BANANAYR3

I 50 500 MLABORI

20 41 MLABORI

S25 22.61 FLAEIRI

S20 8.81 FLABORI

S 0.80 HAY ACCTQ

o 0 MJEACCTG

S35 -34510 MZECONS1

G 680 KIDSGOAT

-2 -2.0 'CONSGOAT

S 080 BANANAACCTG

S100 -10.00 BANANACONS'N

0.0 'ADULTQOAF

080 MCSHSEGI

0OO FCSHBEGI

: O .,MCSHBEGI

O80 FCSHBEGI

500 T27.0m MCSHENDYR

200 U62.10 FCSHENOYR

342.L8 TOTCSHENDYR

SUM 743.-2

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

III. INTERACTIVE LINEAR PROGRAMMING WITH

VISUAL BASIC

PREFACE

This set of learning exercises constitutes a manual designed for graduate students

and others with linear programming literacy and familiarity with Microsoft

Excel. It is intended to make the linear programming analysis more efficient in

representing diverse households and respecting the diversity of household

strategies. 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 linear programming on farm. Visual Basic could become an essential tool for

updating and testing different situations working directly with the farmers.

Visual Basic makes the simulation process user friendly.

This set of exercises is divided into eight sections and to exercises. The first two

sections deal with the simplest operations of "macros" and with inserting Visual

Basic Objects in a spreadsheet. The third and the seventh sections present

common linear programming tasks as input and output tables, respectively,

linked to Visual Basic codes.

The fourth, fifth, and sixth sections deal with all the needed tools to control the

"solver" function directly from codes in Visual Basic. And, finally the last section

presents and describes advanced "solver" codes.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

III. INTERACTIVE LINEAR PROGRAMMING WITH

VISUAL BASIC

PREFACE

This set of learning exercises constitutes a manual designed for graduate students

and others with linear programming literacy and familiarity with Microsoft

Excel. It is intended to make the linear programming analysis more efficient in

representing diverse households and respecting the diversity of household

strategies. 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 linear programming on farm. Visual Basic could become an essential tool for

updating and testing different situations working directly with the farmers.

Visual Basic makes the simulation process user friendly.

This set of exercises is divided into eight sections and to exercises. The first two

sections deal with the simplest operations of "macros" and with inserting Visual

Basic Objects in a spreadsheet. The third and the seventh sections present

common linear programming tasks as input and output tables, respectively,

linked to Visual Basic codes.

The fourth, fifth, and sixth sections deal with all the needed tools to control the

"solver" function directly from codes in Visual Basic. And, finally the last section

presents and describes advanced "solver" codes.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

A. Macros

Macros are routines and sub-routines of tasks in Excel. Usually we do

many of these routines 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 handling large amounts of data,

saves time, reduces 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 to a tape-recording process; we

can record any routine and recall it at any time by simply using a shortcut

(usually Ctrl + 'letter").

Exercise 1 Copy and Paste Macro

1. In an Excel spreadsheet. Go to Tools/Macro/Record New Macro

A Record Macro window will appear.

Macro name:

Shortcut key: Store macro in:

Ctrl+4 |This Workbook

Description:

Macro recorded 8/12/2003 by VECabrera

aOK Cancel

2. In the Record Macro window. Leave the Macro name as Macrol, assign "z"

as the Shortcut key, Store the macro in: This Workbook, and click OK.

A Stop Rec window will appear.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

A. Macros

Macros are routines and sub-routines of tasks in Excel. Usually we do

many of these routines 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 handling large amounts of data,

saves time, reduces 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 to a tape-recording process; we

can record any routine and recall it at any time by simply using a shortcut

(usually Ctrl + 'letter").

Exercise 1 Copy and Paste Macro

1. In an Excel spreadsheet. Go to Tools/Macro/Record New Macro

A Record Macro window will appear.

Macro name:

Shortcut key: Store macro in:

Ctrl+4 |This Workbook

Description:

Macro recorded 8/12/2003 by VECabrera

aOK Cancel

2. In the Record Macro window. Leave the Macro name as Macrol, assign "z"

as the Shortcut key, Store the macro in: This Workbook, and click OK.

A Stop Rec window will appear.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

3. Now the recording process starts. Select cell C5, go to 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. too), press

Enter and press Ctrl + z.

The number in the cell C5 will be duplicated and assigned to cell Clo. Play

with this.

You will realize that this simple principle could be applied to many useful

routines

Exercise 2 Locate and run the macro in other ways

1. You must be able to find where your macros are located. Let's continue

with the previous exercise. Go to Tools/Macro/Macros...

A Macro window will appear.

Macro name:

|Macrol J

Canel I

Step Into

Edit I

Create }

Mcros in: IThis Workbook Optons...

Description

Macro recorded 10/22/2001by Victor E. Cabrera

2. Make sure This Workbook is selected and Macroi is highlighted. Then

press the Run button. This is another way to call the macro.

3. Let's do it directly 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 properties; and on the right a

window for the CODES (it may contain visual basic codes or could just be

empty).

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

'A Mi car o t ni s na MI B e locl bkHs rdow f reu Bor B x

M JIGGnul) :Ja IMrot '

i- L3_y ..5]....b Macr.ol > Ihe _j

C fuicrea (FUNCE5.XLA)

E VBAProject(FirstButtoanai) acrol Macro

4 ~3 MoooftExo Obcth M Hacro recorded 10/22/201 "y 't coD: L. Cacreza

I Sheetln ClhtI)

Sheet2(et2) Keyboard Shorecut: Ctrl+z

Shed O W)et)

Thiswokbook RAnge "CS").Select

S M Modies Selectcin.Copy

ModJel Rage (C10") Select

ActilveShee.Fatec

End Sub

od model -o-i-r Il

-___ g_______________ LJ

4. You may need to locate your "Macrol" inside the Modulei in the Project

Directory, double click in Modules folder and again double click in Module

Once you have it, you will see the codes o t this simple macro, in the

codes window.

Any routine has a SubName( ) 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 apostrophe) and the real codes.

In this example the name of the routine is Macroi, the comments refer to

the date and author of the macro and the keyboard short cut. The codes

are a set of select, copy, and paste.

5. Return from VBE to the spreadsheet by clicking on the Excel icon in the

bottom tool bar:

and delete whatever is in cell Clo.

Return from the spreadsheet to VBE by clicking on the VBE icon in the

bottom tool bar

f~l^^mu -^S

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Now, first make sure that the cursor is anywhere inside the routine

"Macrol", then 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 Clo in the

spreadsheet.

l Ee E&t rew Isert Fnrmat Debu m Iools &dmns window btep u-to

ISa- H r'l ^Gn I Ca.l "_.1 5 i.

S~.~ ...... a ir= 1 I s.

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 insert 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 appear

2. Let's concentrate for now on the six middle buttons of the window: Check

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 Command Button. Go with the mouse to cell C4 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 C4 and

drag it to the top-right corner of D3. A button will appear

CommandButtonl

4. Double click in the middle of the new CommandButtoni. VBE will open

and the cursor will be right in the middle of a program routine; write

Macroi and close VBE.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Now, first make sure that the cursor is anywhere inside the routine

"Macrol", then 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 Clo in the

spreadsheet.

l Ee E&t rew Isert Fnrmat Debu m Iools &dmns window btep u-to

ISa- H r'l ^Gn I Ca.l "_.1 5 i.

S~.~ ...... a ir= 1 I s.

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 insert 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 appear

2. Let's concentrate for now on the six middle buttons of the window: Check

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 Command Button. Go with the mouse to cell C4 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 C4 and

drag it to the top-right corner of D3. A button will appear

CommandButtonl

4. Double click in the middle of the new CommandButtoni. VBE will open

and the cursor will be right in the middle of a program routine; write

Macroi and close VBE.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

In the spreadsheet you will see that the Design Mode (Ruler, Triangle

Pencil icon) is selected; deselect it by clicking on it.

EF &I WP r~bI -J r AFI 5

5. Go to cell C5. Write a number (e.g. looo), press enter. Now click on the

CommandButtoni.

You will see that the number you put in C5 is copied into cell Clo.

You called Macroi directly from the CommandButtoni.

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 cells H2-

I3.

f--3l

2. Write successive numbers 1 to 10 in cells J1 to Jio. Make sure that the

design mode is selected in the Control Toolbox and then double click on

the Combo Box. The VBE window will appear.

Ic0( n In ..

ornate Sub Co .andButtofl. Gotfo3 ()

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 this code:

ComboBoxl.List = Sheetl.Range("J.:Jio").Value

between Sub and End Sub of the CommandButton2.

You will generate this routine:

You will generate this routine:

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Private Sub ComboBoxi_GotFocusO

ComboBoxi.List = Sheeti.Range("J1:Jio").Value

End Sub

4. Return to the spreadsheet, make sure the design mode is deselected. Click

on the drop menu option of the ComboBox you just created.

You will see that the numbers of cells J1 to Jio are now selection options

in the ComboBox. Imagine 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-Inputting Data

A critical function in individual household LP analysis is being able to

insert each household's information in the matrix efficiently and rapidly

without a chance of error. The Filter function of Excel, macros or routines,

and Visual Basic objects will help in this task.

Exercise 5 Filter and Special Paste

1. Open the file filterl.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 Linear Programming 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; they are

highlighted with yellow. Whenever we change family composition in the

INPUT table, the labor available and the consumption requirement change

in the MATRIX. Let's see how Visual Basic can do that for us.

3. To understand the FILTER function, go to the INPUT sheet, select cell H9

and write in Household then select cell Hio 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

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Private Sub ComboBoxi_GotFocusO

ComboBoxi.List = Sheeti.Range("J1:Jio").Value

End Sub

4. Return to the spreadsheet, make sure the design mode is deselected. Click

on the drop menu option of the ComboBox you just created.

You will see that the numbers of cells J1 to Jio are now selection options

in the ComboBox. Imagine 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-Inputting Data

A critical function in individual household LP analysis is being able to

insert each household's information in the matrix efficiently and rapidly

without a chance of error. The Filter function of Excel, macros or routines,

and Visual Basic objects will help in this task.

Exercise 5 Filter and Special Paste

1. Open the file filterl.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 Linear Programming 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; they are

highlighted with yellow. Whenever we change family composition in the

INPUT table, the labor available and the consumption requirement change

in the MATRIX. Let's see how Visual Basic can do that for us.

3. To understand the FILTER function, go to the INPUT sheet, select cell H9

and write in Household then select cell Hio 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

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

Action

rC ilter the list, in-place

C Cgpy to another location

strange: $A$9:$F$17

Criteria range: I$H$9:$H$10 N

Copy to: I$A$19:SF$19

r- Unique records only

JI Cancel

In the Advanced Filter window, select 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$lo 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 row (family

composition) and Copies it to the selected cells. Change the number of

household in cell Hio and go to the Advanced Filter again. Play with this.

4. Now you are ready to make a macro called filt with a shortcut Ctrl+f. Save

the macro in this workbook and make sure it works properly.

5. The next step will be to put the household composition (selected in cells

B2o to F2o) 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.

Paste

f} r Validation

C Eormulas Al except borders

r vaes r Cokumn wdt s

C Formal Fonmulas and number formats

r Comments C Vales and number formats

Operation

r one r Mltiply

r Add r Divide

C Subtract

rS Sp banks W Transpose

E5Ln E OK Canc

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 Hlo number, do Ctrl+f, the household selected

changes, now do Ctrl+h, the labor available and consumption change not

only in the above tables but also inside the matrix.

Save this file as filter.

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 F26.

3. Double click on the ComboBox and similarly to exercise 4, step 3, write:

Private Sub ComboBoxi_GotFocusO

ComboBoxi.List = Sheets("INPUT").Range("alo:a17").Value

End Sub

This code is assigning the values of the household numbers (1 to8) to the

Combobox.

4. Insert a CommandButton in the MATRIX sheet, between cells K26 and

L27. Double click on this second button and write:

Private Sub CommandButton2_ClickO

Sheets("INPUT").Range("Hio") = ComboBoxl.Value

Sheets("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 Hlo 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 different

households.

Play with it by changing the numbers. Save your file as filter.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 this 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 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 Refernces/Reference to SOLVER.XLA:

Ava"te Refrecs OK Ej |^

W Ms Exice 0.0 Object 'Lary i j funcres (FUNCRES.XLA)

I os tL 2.ob~-ar.y SOLVER (SOLVER.XLA)

S- ofeo.o oe ary + g. VBAProject (filter3.xs)

A, l M-, pit E Microsoft Excel Objects

S .rjsptocono t L 0 T ye L br

LASW D.CIJ I]t 07m Lrr

AcrcIE- LO T e ubrarV

A4t.e Direc'mr TVges

Activ es m Lor.ira erf -,

1 '. C

axE -- -- --- - *Q I

Lonab. c ammesponsose Da uhc'.emlO~rrypo^O ; ""

Langug EnshAJted States

Note that there is a new folder (References)

to the solver. You can put the reference in ai

work, but it is always a good idea to insert it

to make sure it will work in other computer.

3. Close the VBE and save your file as filter.

Sheet2 (INPUT)

ThisWorkbook

Modules

References

0 Reference to SOLVER.XLA

inside which is the reference

another path and it will still

in the file you are working on

S.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 this 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 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 Refernces/Reference to SOLVER.XLA:

Ava"te Refrecs OK Ej |^

W Ms Exice 0.0 Object 'Lary i j funcres (FUNCRES.XLA)

I os tL 2.ob~-ar.y SOLVER (SOLVER.XLA)

S- ofeo.o oe ary + g. VBAProject (filter3.xs)

A, l M-, pit E Microsoft Excel Objects

S .rjsptocono t L 0 T ye L br

LASW D.CIJ I]t 07m Lrr

AcrcIE- LO T e ubrarV

A4t.e Direc'mr TVges

Activ es m Lor.ira erf -,

1 '. C

axE -- -- --- - *Q I

Lonab. c ammesponsose Da uhc'.emlO~rrypo^O ; ""

Langug EnshAJted States

Note that there is a new folder (References)

to the solver. You can put the reference in ai

work, but it is always a good idea to insert it

to make sure it will work in other computer.

3. Close the VBE and save your file as filter.

Sheet2 (INPUT)

ThisWorkbook

Modules

References

0 Reference to SOLVER.XLA

inside which is the reference

another path and it will still

in the file you are working on

S.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

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 this 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 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 Refernces/Reference to SOLVER.XLA:

Ava"te Refrecs OK Ej |^

W Ms Exice 0.0 Object 'Lary i j funcres (FUNCRES.XLA)

I os tL 2.ob~-ar.y SOLVER (SOLVER.XLA)

S- ofeo.o oe ary + g. VBAProject (filter3.xs)

A, l M-, pit E Microsoft Excel Objects

S .rjsptocono t L 0 T ye L br

LASW D.CIJ I]t 07m Lrr

AcrcIE- LO T e ubrarV

A4t.e Direc'mr TVges

Activ es m Lor.ira erf -,

1 '. C

axE -- -- --- - *Q I

Lonab. c ammesponsose Da uhc'.emlO~rrypo^O ; ""

Langug EnshAJted States

Note that there is a new folder (References)

to the solver. You can put the reference in ai

work, but it is always a good idea to insert it

to make sure it will work in other computer.

3. Close the VBE and save your file as filter.

Sheet2 (INPUT)

ThisWorkbook

Modules

References

0 Reference to SOLVER.XLA

inside which is the reference

another path and it will still

in the file you are working on

S.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

F. Direct Solver Call

Exercise 8 Solver Button

1. Open your file filter4.xls

2. Record a new macro called sol with the "Ctrl+s" shortcut to call the solver

and solve the matrix.

3. Record another new macro called del with the "Ctrl+d" shortcut that

deletes the variable 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 CommandButtonl_ClickO

Sheets("INPUT").Range("Hlo") = ComboBoxl.Value

Sheets("INPUT").Select

fit

hhcopy

Sheets("MATRIX").Select

del

sol

End Sub

5. Try it. You must choose one household in the ComboBox, then 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 solvl.

G. Listing Results

Exercise 9 Output Tables

1. Open your file solvi.xls

2. Go to Insert/Worksheet (right click on INPUT and click on insert, then

worksheet). Right click on the new sheet and click on rename and rename

it OUTPUT. In cell B5 in OUTPUT, write household and in the next

columns copy the names of the variables of the matrix. After that, in the

same row copy all the names of the resources and constraints (hint: use

Paste Special/Transpose). Then you will get column headings B5 to AC5.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

F. Direct Solver Call

Exercise 8 Solver Button

1. Open your file filter4.xls

2. Record a new macro called sol with the "Ctrl+s" shortcut to call the solver

and solve the matrix.

3. Record another new macro called del with the "Ctrl+d" shortcut that

deletes the variable 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 CommandButtonl_ClickO

Sheets("INPUT").Range("Hlo") = ComboBoxl.Value

Sheets("INPUT").Select

fit

hhcopy

Sheets("MATRIX").Select

del

sol

End Sub

5. Try it. You must choose one household in the ComboBox, then 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 solvl.

G. Listing Results

Exercise 9 Output Tables

1. Open your file solvi.xls

2. Go to Insert/Worksheet (right click on INPUT and click on insert, then

worksheet). Right click on the new sheet and click on rename and rename

it OUTPUT. In cell B5 in OUTPUT, write household and in the next

columns copy the names of the variables of the matrix. After that, in the

same row copy all the names of the resources and constraints (hint: use

Paste Special/Transpose). Then you will get column headings B5 to AC5.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

F. Direct Solver Call

Exercise 8 Solver Button

1. Open your file filter4.xls

2. Record a new macro called sol with the "Ctrl+s" shortcut to call the solver

and solve the matrix.

3. Record another new macro called del with the "Ctrl+d" shortcut that

deletes the variable 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 CommandButtonl_ClickO

Sheets("INPUT").Range("Hlo") = ComboBoxl.Value

Sheets("INPUT").Select

fit

hhcopy

Sheets("MATRIX").Select

del

sol

End Sub

5. Try it. You must choose one household in the ComboBox, then 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 solvl.

G. Listing Results

Exercise 9 Output Tables

1. Open your file solvi.xls

2. Go to Insert/Worksheet (right click on INPUT and click on insert, then

worksheet). Right click on the new sheet and click on rename and rename

it OUTPUT. In cell B5 in OUTPUT, write household and in the next

columns copy the names of the variables of the matrix. After that, in the

same row copy all the names of the resources and constraints (hint: use

Paste Special/Transpose). Then you will get column headings B5 to AC5.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

F. Direct Solver Call

Exercise 8 Solver Button

1. Open your file filter4.xls

2. Record a new macro called sol with the "Ctrl+s" shortcut to call the solver

and solve the matrix.

3. Record another new macro called del with the "Ctrl+d" shortcut that

deletes the variable 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 CommandButtonl_ClickO

Sheets("INPUT").Range("Hlo") = ComboBoxl.Value

Sheets("INPUT").Select

fit

hhcopy

Sheets("MATRIX").Select

del

sol

End Sub

5. Try it. You must choose one household in the ComboBox, then 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 solvl.

G. Listing Results

Exercise 9 Output Tables

1. Open your file solvi.xls

2. Go to Insert/Worksheet (right click on INPUT and click on insert, then

worksheet). Right click on the new sheet and click on rename and rename

it OUTPUT. In cell B5 in OUTPUT, write household and in the next

columns copy the names of the variables of the matrix. After that, in the

same row copy all the names of the resources and constraints (hint: use

Paste Special/Transpose). Then you will get column headings B5 to AC5.

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

3. In cells C4 to AC4 (yes, above not below) refer or link to the values of the

matrix.

4. Refer or link cell B4 to the INPUT sheet (Hlo).

5. Create a new macro that inserts copied cells from the row 4 to row 6 using

row 3 as a bridge.

Call this macro output and use "Ctrl+o" as a shortcut.

Right click on number "4" (of row 4) and select "Copy"

Right click on number "3" (of row 3) and select "Paste Special..." and select

"Values." Then click OK.

Right click on number "3" (of row 3) and select "Copy"

Right click on number "6" (of row 6) and select "Insert Copied Cells"

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 Excel and try 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 solv2.

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 calling the solver by

commands is that we can control all the parameters 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 manuals. Sometimes tutorial software

handles these topics as well.

Exercise 10 Advanced Solver

1. Open your file solv2.xls

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

3. In cells C4 to AC4 (yes, above not below) refer or link to the values of the

matrix.

4. Refer or link cell B4 to the INPUT sheet (Hlo).

5. Create a new macro that inserts copied cells from the row 4 to row 6 using

row 3 as a bridge.

Call this macro output and use "Ctrl+o" as a shortcut.

Right click on number "4" (of row 4) and select "Copy"

Right click on number "3" (of row 3) and select "Paste Special..." and select

"Values." Then click OK.

Right click on number "3" (of row 3) and select "Copy"

Right click on number "6" (of row 6) and select "Insert Copied Cells"

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 Excel and try 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 solv2.

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 calling the solver by

commands is that we can control all the parameters 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 manuals. Sometimes tutorial software

handles these topics as well.

Exercise 10 Advanced Solver

1. Open your file solv2.xls

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

2. Go to VBE. Find your codes that 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: ="o",

ByChange: ="$C$7:$M$7"

SolverSolve

End Sub Sub solO

3. The first two lines (bold) were set in the solver for exercise 5; Excel does

not include the constraints in this macro, however 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 (not active) and control the parameters directly from the

spreadsheet. Put an apostrophe before the first codes line:

'SolverOk SetCell:="$P$24". MaxMinVal:=1, ValueOf:=o,

ByChange:="$C$7:$M$7"

Letters will turn green indicating they are not active. Now, we are using

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

example delete the ">=" constraints. Now solve directly from the

CommandButton. How do the solutions in the different households

change? Are there more or fewer non-feasible solutions?.

4. Save your file as solv3.

List of Solver VBE Commands

SolverAdd:

Adds constraints to a Solver model. Takes three arguments:

cellRef-Reference to a cell or a range of cells that forms the left side of a

constraint.

relation-1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must

contain integers.)

formulaText-Right side of the constraint.

SolverChange:

Changes an existing constraint. Takes three arguments:

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

* cellRef-Reference to a cell or a range of cells that forms the left side of a

constraint.

* relation-i (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must

contain integers.)

* formulaText-Right side of the constraint.

SolverDelete:

Deletes an existing constraint. Takes three arguments:

* relation-1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must

contain integers.)

* formulaText-Right side of the constraint.

SolverFinish:

After solving a problem, tells Excel to keep the results and to create a

report. Takes two arguments:

* keepFinal-i keep final results; or 2 discard results and return to

original values.

* reportArray-i create an Answer report; 2 create a Sensitivity report;

or 3 create a Limit report.

SolverFinishDialog:

Equivalent to SolverFinish; however, also displays the Solver Results

dialog box after solving a problem. Takes two arguments:

* keepFinal-i keep final results; or 2 discard results and return to

original values.

* reportArray-- create an Answer report; 2 create a Sensitivity report;

or 3 create a Limit report.

SolverGet:

Returns information about the Solver model. Takes two arguments:

typeNum-Takes values from 1 through 18 to return information about

the Solver model. For details, use the

Object Browser to view the SolverGet help topic in the XLM Function

Reference section in Excel 5's online Help.

sheetName-Name of the sheet containing the Solver model.

SolverLoad:

Loads parameters for an existing Solver model that have been saved to the

worksheet. Takes one argument:

loadArea-Range on the active sheet that contains the Solver model.

SolverOk:

Defines a Solver model. Takes four arguments:

setCell-Target cell for the Solver model.

maxMinVal-- maximize target; 2 minimize target, 3 match target to

a specific value.

valueOf-If the maxMinVal argument is set to 3, you must specify the

value to which the target cell is to be

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

matched.

* byChange-Cell or range of cells that will be changed in setting the target

cell.

SolverOkDialog:

Same as SolverOk but also displays the Solver dialog box. Takes four

arguments:

* setCell-Target cell for the Solver model.

* maxMinVal-1 maximize target; 2 minimize target; 3 match target to

a specific value.

* valueOf-If the maxMinVal argument is set to 3, you must specify the

value to which the target cell is to be

matched.

* byChange-Cell or range of cells that will be changed in setting the target

cell.

SolverOptions:

Allows you to specify advanced options for your Solver model. Takes 10

arguments:

* maxTime-Maximum time Excel will spend solving the problem.

* iterations-Maximum iterations Excel will use in solving the problem.

* precision-Number between O and 1 that specifies the degree of

precision to be used in solving the problem.

* assumeLinear-If True, Solver assumes that the underlying model is

linear.

* stepThru-If True, Solver pauses at each trial solution.

* estimates-i for tangent estimates; 2 for quadratic estimates.

derivatives-i for forward, 2 ffir central.

search-i for Quasi Newton search; 2 for Conjugate Gradient search.

intTolerance-Number between 0 and 1 that specifies tolerance.

scaling-If Scaling is True and two or more constraints differ by several

orders of magnitude, Solver 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 values.

SolverSave:

Saves a Solver model definition to a range of cells on the worksheet. Takes

one argument:

saveArea-The range of cells to which the Solver model is to be saved.

SolverSolve:

Starts a Solver solution run. Takes two arguments:

Linear Programming in Small Farm Livelihood Systems. Hildebrand & Cabrera, 2003

userFinish-If True, Solver returns results without displaying anything.

If False, Solver returns results and displays the Solver Results dialog box.

showRef-This argument is used only if True is passed for the step thru

argument of the

SolverOptions function.

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

i. Make the last window of "solver results" 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 them before solving.

3. If you have some literacy in Visual Basic, you could try to make a "loop" in

which you could solve the eight households with only one click.

4. If you have some literacy in Visual Basic, you could try to insert "if-then"

statements that control constraints.

5. Your imagination is the limit.