Linear Programming Project Outline

Name: _____________________________________________________________ Instructions: Complete steps 1-4 to fully analyze the problem solving process of the linear programming
problems. Use http://www.youtube.com/watch?v=ItX80V2Wug4&feature=related as another resource. Step 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. Step 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 has 45 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 Step 3: Solve this Optimization Problem manually on a piece of grid paper A.) Graph the inequalities B.) Shade in the bounded region clearly. C.) List the Feasible Solutions by finding the vertices D.) Test the Feasible Solutions using your Objective Function E.) Determine the Maximum Value
Step 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 and number row. Refer to Figure A below to view the name of the cells. Figure A
A: Entering Data into Spreadsheet i.) Identify the two variables of the word problems and type them in cells B1 and C1 ii.) Identify your types of constraint factors and type those in cells A2; A3; and A4 iii.) You are now going to start building your Constraint Inequalities. Fill in your excel spreadsheet to match the spreadsheet below:
vi.) In row 5, we are going to have the Objective Function. Fill in the cells as follows A5: 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 the word problem are we addressing?
B: Programming data as constraints and as the objective function We 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 insert vi.) 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, and Column F already contains a number expression. For example, the constraint for land is given by x + y <= 45. We will by typing in the following: Column E3- E5: Type in <= to represent in each row Column D3: Type =B3*B2+C3*C2 or type = and click on the appropriate boxes to form your constraint inequality. (Note: when you hit enter it will change to 0.)
Column D4-D6: Mirror what you did for D3 in this cells. Remember you are typing in the variable expressions portraying the constraints of the problem.
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
A
B
C E D F
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 <= F3. Click OK. You will need to repeat two more times to program your two other constraints. They will appear in (E) of Figure C above
vii.) Lastly, click on options (F) of Figure C above. A pop up will appear. (Refer to Figure D on the next page)
Figure D: Solver Options
A B
viii.) Check Assume Linear Model (A) and check Assume Non-Negative (B). We are assuming 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 and your data entries. Otherwise, consider what may be going on with the linear 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. Homework: Create four more sheets to solve the linear programming problems 34- 37 on page 530 &531 of your text. Use this outline to remind you of the steps in using Excel Solver. ## Email to egoddard@eastsac.k12.ia.us with subject Last name-Period Linear Program (Goddard-1 Linear Program)

Linear Programming Project Outline

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Linear Programming Project Outline

Uploaded by

Copyright:

Available Formats

Name: _____________________________________________________________ Instructions: Complete steps 1-4 to fully analyze the problem solving process of the linear programming

Step 4: Solve this Optimization Problem using Excel Solver Program

Figure D: Solver Options

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

You might also like