Excel Optimization - Computer Applications | CEE 3804, Study notes of Civil Engineering

Download Excel Optimization - Computer Applications | CEE 3804 and more Study notes Civil Engineering in PDF only on Docsity!

CEE 3804: Computer Applications

Class

Excel Optimization

Topics to be Covered

• HW

• Reading: Intro to Linear Programming

• Goal Seek Command

• Linear Programming

• Excel Solver

• Activity

2. Reading – Intro to Linear Programming (1/1)

• Linear Programming Problem

• Decision Variables
• Objective Function
• Constraints

• Isoprofit Lines

• Isoprofit Lines Solution Method

• Feasible vs. Non-Feasible Solution

3. Goal Seek Function

• Data  Data Tools  What-If Analysis  Goal Seek
• Difference between Solver and Goal Seek Functions

4. LP Basic Definitions

b. Model Formulation

• An optimization model is a mathematical statement of the problem - Consists of a single objective or merit function - May contain a set of constraint equations - Objective: Min. y = f 0 ( x ) Subject to: f 1 ( x )  b 1 f 2 ( x )  b 2 fm( x )  bm - x = [ x 1 , x 2 , x 3 , …, xn ] T

4. LP Basic Definitions

c. Goal of Optimization Models

• The goal of an optimization model is:
  • To find the vector x , such that y is minimized
    • Subject to satisfying all constraints
• Definition of variables:
  • The variables of vector x are called the control variables
    • Because they are adjusted to minimize the objective function
  • The coefficients in the objective function are called unit costs
  • The constants bi are called the resource constraint parameters (i = 1, 2, …, m)
  • f( x ) is called the performance function
    • Because it describes the relationship between an MOE and the control variables

5. LP Problem Formulation

a. Example Problem – Problem Statement

• Problem Statement:

• A carpenter can make either chairs or tables which sell at $20 and $50 a piece, respectively.
• The carpenter can work up to 220 hours per month.
• The carpenter requires 2 hours to make a chair and 6 hours to make a table.
• Each table and chair set that is sold requires at least 4 chairs per table but no more than 8 per table.

• Objective:

• What is the mixture of chairs and tables that should be made in order to maximize the carpenter’s revenue?

5. LP Problem Formulation

b. Example Problem – Overview of Model Formulation

• The model formulation involves:

• Defining the decision/control variables,
• Establishing the objective function, and
• Establishing the constraints placed on the system.

• Once the model is formulated, then a method

of analysis is selected

5. LP Problem Formulation

d. LP Requirements

• An LP problem is an optimization problem:

• Has a linear objective function that is to be minimized or maximized
• Has constraints that are linear equations that must be satisfied or linear inequalities that cannot be violated, and
• Has variables that are continuous but may have sign restrictions imposed on them. - Integer values are used in integer programming

6. LP Graphical Solution

a. Graphical Representation

• Two variable LP’s can be illustrated

graphically by plotting x

1

and x

2

on

perpendicular axes on graph paper

• All constraints can be added to the plot by

adding a line for each constraint

• The optimal solution is the highest/lowest

valued objective function that touches the

feasible region

7. Example Illustration

a. Problem Definition

• A company manufactures two products A and B, which can be sold for $120 and $80 per unit, respectively
• Management requires at least 1,000 units be manufactured each month
• Product A requires 5 hours of labor per unit and product B requires 3 hours of labor per unit
• The cost of labor is $12/hour and a total of 9, hours are available per month
• Determine a monthly production schedule that will maximize the company’s profit

7. Example Illustration

b. Problem Formulation

• Decision/Control Variables:

• Objective Function:

• Constraints:

• • Constraint on number of …
• • Constraint on number of …
• • Non-negativity constraints

7. Example Illustration

c. Problem Solution

• Optimum:

• A = 0

• B = 2667 units
• Y = $117, A B

7. Example Illustration

d. Extreme Points

• Four extreme points:

• Point 1: (1000,0)
  • Z= $60,
• Point 2: (0,1000)
  • Z= $44,
• Point 3: (0,2667)
  • Z= $117,
• Point 4: (1600,0)
  • Z= $96, A B Point 1 Point 2 Point 3 Point 4