• TABLE OF CONTENTS
HIDE
 Front Cover
 Title Page
 Introduction
 Preface
 Table of Contents
 Chapter I: Introduction to matrix...
 Chapter II: Introduction to...
 Chapter III: Interactive linear...
 Preface






Group Title: Department of Food and Resource Economics Staff Paper Series SP 03-3
Title: Modeling and analyzing small farm livelihood systems with linear programming
CITATION PAGE IMAGE ZOOMABLE PAGE TEXT
Full Citation
STANDARD VIEW MARC VIEW
Permanent Link: http://ufdc.ufl.edu/UF00053833/00001
 Material Information
Title: Modeling and analyzing small farm livelihood systems with linear programming
Series Title: Staff paper
Physical Description: 53 p. : ill. ; 28 cm.
Language: English
Creator: Hildebrand, Peter E
Cabrera, Victor E
University of Florida -- Food and Resource Economics Dept
Publisher: University of Florida, Institute of Food and Agricultural Sciences, Food and Resource Economics Department
Place of Publication: Gainesville Fla
Publication Date: 2003
 Subjects
Subject: Farm management -- Linear programming -- Florida   ( lcsh )
Farm management -- Mathematical models   ( lcsh )
Genre: government publication (state, provincial, terriorial, dependent)   ( marcgt )
 Notes
Statement of Responsibility: by Peter E. Hildebrand and Victor E. Cabrera.
General Note: Cover title.
General Note: "August 2003."
Funding: Florida Historical Agriculture and Rural Life
 Record Information
Bibliographic ID: UF00053833
Volume ID: VID00001
Source Institution: Marston Science Library, George A. Smathers Libraries, University of Florida
Holding Location: Florida Agricultural Experiment Station, Florida Cooperative Extension Service, Florida Department of Agriculture and Consumer Services, and the Engineering and Industrial Experiment Station; Institute for Food and Agricultural Services (IFAS), University of Florida
Rights Management: All rights reserved, Board of Trustees of the University of Florida
Resource Identifier: aleph - 002983035
oclc - 53275219
notis - APM4846

Table of Contents
    Front Cover
        Front Cover
    Title Page
        Page i
    Introduction
        Introduction
    Preface
        Page 1
        Page 2
    Table of Contents
        Page 3
        Page 4
    Chapter I: Introduction to matrix construction and solution with excel ®
        Page 5
        Page 6
        Page 7
        Page 8
        Page 9
    Chapter II: Introduction to exercises
        Page 10
        Model creation: Describing the livelihood system
            Page 11
            Excercise 1: Basic LP matrix
                Page 11
                Page 12
            Exercise 2: Intermediate products and accounting rows
                Page 13
            Exercise 3: Family consumption constraints and transfer activities
                Page 14
                Page 15
                Exercise 4a: Gender analysis
                    Page 16
                    Page 17
                Exercise 4b: Creating input and output tables
                    Page 18
                    Page 19
            Exercise 5: Resource flow and integer solutions
                Page 20
                Page 21
        Model calibration and validation: Is it ready to use?
            Page 22
        Hypothesis testing: Assessing livelihood strategies
            Page 22
            Page 23
            Exercise 6: Assessing alternative technology
                Page 24
                Page 25
        Prediction
            Page 26
            Excercise 7: Policy analysis
                Page 26
                Page 27
        Production labor stress
            Page 28
            Exercise 8a: Household composition, food security and production labor stress
                Page 28
        Aggregating to a higher scale
            Page 29
            Exercise 8b: Aggregating to a higher scale
                Page 29
                Page 30
        Dynamic programming
            Page 31
            Exercise 9a: Animals
                Page 31
                Page 32
                Page 33
                Page 34
            Exercise 9b: Perennial crops
                Page 35
                Page 36
                Page 37
    Chapter III: Interactive linear programming with visual basic
        Page 38
    Preface
        Page 38
        A. Macros
            Page 39
            Exercise 1: Copy and paste macro
                Page 39
            Exercise 2: Locate and run the macro in other ways
                Page 40
                Page 41
        B. Visual basic objects and buttons
            Page 42
            Exercise 3: Locate and familiarize yourself with VB objects
                Page 42
            Exercise 4: Set up a combo box
                Page 43
        C. Filters (inputting data)
            Page 44
            Exercise 5: Filter and special paste
                Page 44
                Page 45
            Exercise 6: Select households from the matrix sheet
                Page 46
        D. Visual basic solver function
            Page 47
        E. Visual basic reference to solver
            Page 47
            Exercise 7: Including the solver reference
                Page 47
        F. Direct solver call
            Page 48
            Exercise 8: Solver button
                Page 48
        G. Listing results (output table)
            Page 48
            Exercise 9: Output tables
                Page 48
        H. Solver with commands
            Page 49
            Exercise 10: Advanced solver
                Page 49
        List of solver commands
            Page 50
            Page 51
            Page 52
        Other proposed exercises
            Page 53
Full Text


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.




University of Florida Home Page
© 2004 - 2010 University of Florida George A. Smathers Libraries.
All rights reserved.

Acceptable Use, Copyright, and Disclaimer Statement
Last updated October 10, 2010 - - mvs