linear programming project outline

7/31/2019 Linear Programming Project Outline

1/4

me: _____________________________________________________________tructions: Complete steps 1-4 to fully analyze the problem solving process of the linear programming

oblems. Use http://www.youtube.com/watch?v=ItX80V2Wug4&feature=related as another resource.

p 1: Getting the Solver Program loaded onto Excel A.) Click the Microsoft Office Button , and then click Excel Options .

B.) Click Add-Ins , and then in the Manage box, select Excel Add-ins C.) Click Go .D.) In the Add-Ins available box, select the Solver Add-in check box, and then click OK .E.) Tip If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the

add-in.F.) If you get prompted that the Solver Add-in is not currently installed on your computer,

click Yes to install it.G.) After you load the Solver Add-in, the Solver command is available in the Analysis group

on the Data tab.

p 2: Analyzing a word problems for the Objective Function and the Constraints The problem: A farmer has two crops planted by the acre: wheat (X) and corn (Y). Say it is

calculated that each acre of wheat takes 3 hours of labor and 2 pounds of fertilizer.Corn is calculated that each acre takes 2 hours of labor and 4 pounds of fertilizer.Wheat brings in $200 of profit/acre and corn brings in $300 per/acre. If the farmer has45 acres of land, 100 hours of labor available, and 120 lbs of fertilizer, find the number of acres to plant of each crop in order to maximize the profit.

A.) Identify the Objective Function : _______________________________

B.) Identify the constraints :i.) Land

ii.) Labor

iii.) Fertilizer

p 3: Solve this Optimization Problem manually on a piece of grid paper A.) Graph the inequalitiesB.) Shade in the bounded region clearly.C.) List the Feasible Solutions by finding the verticesD.) Test the Feasible Solutions using your Objective FunctionE.) Determine the Maximum Value

p 4: Solve this Optimization Problem using Excel Solver Program

Recall that each cell is on a grid where each cell is identified by the letter column andnumber row. Refer to Figure A below to view the name of the cells.

Figure A
http://www.youtube.com/watch?v=ItX80V2Wug4&feature=relatedhttp://www.youtube.com/watch?v=ItX80V2Wug4&feature=related


2/4

A: Entering Data into Spreadsheet i.) Identify the two variables of the word problems and type them in cells B1 and C1ii.) Identify your types of constraint factors and type those in cells A2; A3; and A4iii.) You are now going to start building your Constraint Inequalities. Fill in your excelspreadsheet to match the spreadsheet below:

vi.) In row 5, we are going to have the Objective Function. Fill in the cells as followsA5: Profit B5: 200 C5: 300

Q1: Can you determine why we put 1 in B2; 1 in C2; and 45 in D2? What part of theword problem are we addressing?

B: Programming data as constraints and as the objective functionWe need to communicate to Excel this is an optimization problem. Here is how:i.) Insert another row underneath your row 1 data by putting your curser directly between

row 1 and row 2 in your left hand column. Right click and choose insert. Another row

should appear.ii.) Paint cells B2 and C2 gray. These cells are going to represent your variables X and Y.iii.) Insert two more columns after your column C by putting your curser directly between

column C and column D on your topmost row. Right click and choose insertvi.) We need the columns to input our constraint inequalities. Column D will have the

expression involving the variables, Column E will contain an inequality symbol, andColumn F already contains a number expression. For example, the constraint for land isgiven by x + y


3/4

C: Using Excel Solver to solve this linear programming problem i.) You are done with communicating to excel your constraints and objective. YAY!ii.) Click on the Data tab of excel at the top iii.) Under Analytics you should have a Solver command (Refer to Figure B)

Figure B: Menu Tabs and Commands

iv.) A Solver Parameters pop -up window will appear (Refer to Figure C):

Figure C- Solver Parameters

v.) Fill in the following by typing the cell name or clicking on the cell

Set Target Cell (A): D6 This is your objective functions Equal To (B): Max We are finding a solution that will maximize By Changing Cells (C): B2 and C2 Our variables

vi.) Click Add (D). A pop up box will appear. Type in the cells to indicate your constraints.D3


4/4

Figure D: Solver Options

viii.) Check Assume Linear Model (A) and check Assume Non - Negative (B). We areassuming these models are going to be consistently linear and X and Y would not

be able to be negative because we could not plant a negative number of acres.

Q2: Explain why would we assume that our X and Y are non-negative

ix.) Click OK on your Solver Options pop -up (Figure D) and click Solve on your Solver Parameters pop-up (Figure C).

x.) If everything went right, a Solve Results pop-up will appear .

Click OK. If something else came up. Check your parameters window again andyour data entries. Otherwise, consider what may be going on with thelinear programming problem.

Q3: How many acres of wheat and corn should you plant? _________

Q4: What will your maximum profit be?______________Q5: On another paper, write an analysis for the meaning of Column D for which the Solver

has filled in.

mework: Create four more sheets to solve the linear programming problems 34- 37 on page530 &531 of your text. Use this outline to remind you of the steps in using Excel Solver.Email to [email protected] with subject Last name-Period Linear Program

(Goddard-1 Linear Program)

B

A
mailto:[email protected]:[email protected]

linear programming project outline

Documents