Docstoc

Linear Programming Graph Excel

Document Sample
Linear Programming Graph Excel Powered By Docstoc
					                                                                                   1


                           CHAPTER 11
          RESOURCE CONSTRAINTS AND LINEAR PROGRAMMING
                  ANSWERS TO REVIEW QUESTIONS

QUESTIONS

 11.1 Refer to Example 11-1 RCLP Inc LP problem.

 (a) Demonstrate that the optimal values for x1 and x2 in the solution satisfy the
     original constraints by substituting the values into the three constraints.
 (b) Write the raw material A constraint by increasing its RHS value by 1 from 80
     to 81, while keeping the rest of the values of the problem unchanged. Solve the
     resulting new LP problem and demonstrate that the value of the objective
     function will now increase by the amount of the relevant shadow price, which
     is 30, from 3,900 to 3,930.

 11.2 Refer to example 11.2 (capital rationing problem).

 (a) Represent the problem in graphical form and find the optimal solution
     using the graphical method.
 (b) Solve the capital rationing problem using Excel Solver and compare your
     results with the solution details provided in Excel Work Book 11.2.
 (c) Confirm that the objective function will increase by the value of the
     shadow price of the by-product requirement constraint (0.175) by
     appropriately reformulating the capital rationing problem and solving it.

 11.1 Refer to example 11.3 (project portfolio selection problem).

 (a) Formulate the problem as an LP problem by specifying the information in
     terms of a set of linear equations.
 (b) Solve the problem and compare your results with those provided in Work
     Book11.3.


ANSWERS

Answer to Q 11.1

11.1 (a) Optimal values satisfy the constraints:

The original constraint equations in the Example 11.1 - Roclap product-mix problem -
from the chapter are:


availability of raw material            0.05 x1 + 0.1 x2      80 tonnes
availability of ingredient                  1 x 1 + 1 x2      1,000 kg
minimum contractual requirement                         x2     300 units
non-negativity condition                           x1, x2      0
                                                                                         2


The Excel Solver Linear Programming solution shows that 400 units of product x1,
and 600 units of product x2, would maximise the objective function, giving $3,900 of
total contribution.

Substituting the solution values for x1 and , x2 into the four constraint equations we
have:

            0     ( all raw material used up)
             05 0 600
             . 400     .1   80
               600 = 1,000 ( all of the ingredient used up)
            1 400 1
                      600 > 300 ( minimum requirement satisfied)
              400 and 600 > 0   ( positive quantities of both x1, x2).


11.1 (b) Raw material constraint shadow price:

The solution is given in the Excel file titled ‘Q 11.1 (b) Excel Solution.xls’. In this
file, the original Linear Programming model has been repeated from the chapter, but
the constraint value on the right hand side of the equation for raw material has been
increased from 80 to 81 tonnes. The new objective function solution given by Excel
Solver is: $3,930 in cell A 6 of this workbook. (To get this solution, simply run Solver
on the new data, using the same conditions as existed in the original dialog box.)


11.2 (a) Capital Rationing Problem – Graphical Presentation:

The graph is easily drawn by hand on graph paper. To make the graph more intuitive,
the amount of investment in Division 1 is set out on the x (horizontal) axis, and the
amount of investment in Division 2, on the y (vertical) axis. These axes will become
the non-negativity constraints for both Division 1 and Division 2 investment also.

The maximum possible amount of investment in Division 1, $3,000, is represented by
a vertical line from the $3,000 point on the x axis. Similarly, the maximum amount of
possible investment in Division2 is represented by a horizontal line from $1,900 on
the y axis. These are the volume limits, and mean that the feasible region will lie
within the area covered by the two lines.

The by product constraint can be drawn by setting the constraint up as a linear
equation, and then solving for two x,y points. The linear equation will be:

                       1 x 
                        x 22 0
and solving for two points on the x axis : x = 0, and x = 3,000, gives the
corresponding y values of : 0 and 1,500. That is, when x1  0 , x 2 (in this case the y
value) will equal 0, and when x1  3000 x 2 will equal 1,500. After this line is
                                       ,   ,
drawn, the feasible region will be below and to the right of the by product constraint
line.

The corner, or extreme, points of the feasible region will be: 0,0 ; 3000,1500;
3000,0.
                                                                                               3


An objective function line, or iso-contribution line, is drawn by observing that the
scales along both the x and y axes are equal, and then selecting two x,y points at
random, and drawing in the line. The objective function equation is:
                                  . x 0 x
                                Z $25$35
                                     0 1 . 2

For example, select x= 0 and y = 0: Z will equal $0, and one point of the line will be
at the origin. Select x = 3000 and y = 3000, Z will equal $1,800. As the scales on the
axes are equal the Z value of $1,800 can be set along the y axis at x = $3,000, so the
second point on the line is 3000; 1800. An iso-contribution line can then be drawn
running from 0,0; to 3000, 1800.

An iso-contribution line can be made to intersect a corner or boundary point in the
feasible region, by drawing a line parallel to the 0,0: 3000;1800 line. By inspection,
the optimum iso-contribution line is the parallel line which runs through –300,0:
3000,1500. This means that the original iso-contribution line has moved downwards
by 300 units.

The optimum investment levels are then: Division1, $3,000, and Division 2 $1,500,
and the optimum NPV of the overall investment is:

                                   
                                  $ .
                                    25 $ 1500
                                Z0 3000   .
                                          35
                                          0
                                = $1,275.


The manual method of line drawing can be mechanised through an Excel spreadsheet.
The spreadsheet for this solution is in the Excel file titled ‘Q 11.2 Excel
Solution.xls’.This file has a number of sheets, and the answer to part (a) - graphical
solution – is in the sheet titled ‘Q 11.2 (a) Solution Graph’. Several comments are
entered onto this spreadsheet to explain how the graph is drawn. The point of
mechanising the graph is to allow various solutions for changes in the constraints.

11.2 (b) Compare your results with solution provided:

If you have any difficulty to do this, get into Workbook 11.2 and resolve the problem.
The answer to this question is Workbook 11.2 mentioned in the text book, which is
available on the Web.

11.2 (c) Reformulation of the by-product requirement constraint:

This is not as straight forward and simple as the reformulation of other constraints. One way
to reformulate is to replace ' 0 ' by ' 1 '. This means that the investment in Division 1 must be
'at least twice the investment in Division 2 less one dollar', as stated in the text book. This
condition can be stated in algebraic form as follows:

x1  2x2 - 1 ;       1  2x2 - x1       or 2x2 - x1  1

If you solve the problem after inserting ' 1 ' in place of ' 0 ' for the by-product requirement
RHS value ( under the column titled 'supply' in the Excel model), then the new output will
show that the investment in Division 2 is equal to 1500.5 and the value of the objective
function will increase by 0.175 to 1,275.175. That means, if the investment in Division 2 is
increased from $1,500 to 1,501, all other parameters being unchanged, the total NPV will be
$1,275.175 as stated in the text book. You can see in the solution to the reformulated Excel
                                                                                                   4

model, the new investment level in Division 2 is $1,500.5 and in Division 2 $3,000. Thus the
investment in Division 1 is 'twice the investment in Division 2 less one dollar', thus satisfying
the new condition imposed into the reformulation of the LP problem. Note that 1,500.5 x 2 =
3,001 and therefore 'twice the investment in Division 2 less one dollar (3,001 - 1) is 3,000.
This is what exactly you see in the new optimal solution, where the value for investment in
Division 1 appears as $3,000 (and not as 3,001).

Without calculation being undertaken, we generally say that if the RHS value of a constraint is
increased by one unit the objective function value will increase by the value of the shadow
price. Here the 'one unit' is used for simplicity and convenience and it holds true for most
cases. On that basis, the text book, as a general statement, has stated that if the investment
in Division 2 is increased from $1,500 to $1,501, all other things being unchanged, the total
NPV will be $1,275.175. However, that general statement is not valid for this particular case.
In this case, increase by one dollar is too much. The correct amount of increase is by 50
cents (or half a unit). You can test this for your interest, if you solve the problem after inserting
' 2 ' in place of ' 0 ' for the by-product requirement RHS value ( under the column titled 'supply'
in the Excel model),in the original example i.e. workbook 11.2. Then the new solution will
show the investment in Division 2 is equal to $1501, investment in Division 2 is $3,000 and
the value of the objective function is $1,275.35 (an increase of twice the value of the shadow
price 0.175 x 2 = 0.35).

Thus the shadow price in this particular case is valid only within a very restricted limit. Such a
phenomena is not unusual, depending upon the particular situation.

Answer to Q 11.3

11.3 (a) Project portfolio selection – problem formulation:

Linear equations can be structured using the tableau layout from the example 11.3 and
its Workbook 11.3 in the chapter.

There are 9 independent investment projects. Each one of these can be designated as
the unknown value x, with an identifying trailing subscript 1 to 9. The co-efficient of
each of these xs is its respective NPV dollar payoff, so that the objective function can
be defined as:

 14 x 
 1    
    12 x
 2 3 4 5 6 7 8 9
MaximiseZ
 x 17 x 10
  17 x 14
  x 15 x 12
   x 40 x

The period one capital supply constraint is written as:

1 x
 xx7 
x 4 x x 
 54
12 65 6 x8x
    x   9
      648 50
   6 30 36
  23      18

and the period two capital supply constraint is written as:

17  5 6 7 
x x  
22 3 4 x  8 9
1
 x 2 
  x x 6  x x
          x
         4 3
  6 35 3 20

In both these equations, the x coefficients represent the dollar amount of capital outlay
required for each project within each period.

The values for each x can range between zero and 1. The x values are restricted to 0 or
above, because a negative investment does not make economic sense. This is the non-
negativity condition.
                                                                                                5


The definition of x means that x is the proportion of each project being undertaken.
For example, if x = 1.00, then the complete project is undertaken, whilst if x = 0.75
means that only 75% of the project can be undertaken, and if x = 0.00, then that
project is not undertaken at all. In most capital budgeting situations, it would be
expected that either all or nothing of a project is undertaken, so this proportionate
acceptance approach is a little unusual. In this particular example though, the projects
have been economically defined as being proportionately acceptable. This means that
the standard algebraic layout of the constraints on the x values is:

x n  0 and        x n  1 , where n = 1 to 9.

However, in the Excel Solver solution, either meaning of x can be specified. In a
‘standard’ solution, proportional values of x are designated by setting up the
constraint equations in the dialog box as: (cell references) >= 0, and (cell references)
<= 1. In an ‘integer’, ( all or nothing), solution the constraint equations are set up as:
(cell references) = integer. This integer programming is discussed in Chapter 12.

These equations are now sufficient to allow for a computer assisted solution. That solution is
shown in Workbook 11.3.

Q 11.3 (b) The Solution is held in Excel file titled “Q 11.3 Excel Solutions.xls”. These solutions
are the same as those given in the chapter.

				
DOCUMENT INFO
Stats:
views:9839
posted:8/22/2008
language:English
pages:5
Description: This is an example of linear programming graph excel. This document is useful for conducting linear programming graph excel.