Session 4a

1 / 79

# Session 4a - PowerPoint PPT Presentation

Session 4a Overview More Network Models Assignment Model (Contract Bidding) “Big Cost” trick Project Management (House Building) More binary / integer tricks Critical Path / Slack Time Excel trick: Conditional Formatting Cost Crashing Changing an objective to a constraint

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## Session 4a

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Overview

More Network Models

• Assignment Model (Contract Bidding)
• “Big Cost” trick
• Project Management (House Building)
• More binary / integer tricks
• Critical Path / Slack Time
• Excel trick: Conditional Formatting
• Cost Crashing
• Changing an objective to a constraint
• Issues with Integers
• Location Analysis (Hospital Location)

Decision Models -- Prof. Juran

Contract Bidding Example

A company is taking bids on four construction jobs. Three contractors have placed bids on the jobs. Their bids (in thousands of dollars) are given in the table below. (A dash indicates that the contractor did not bid on the given job.)

Contractor 1 can do only one job, but contractors 2 and 3 can each do up to two jobs.

Decision Models -- Prof. Juran

Formulation

Decision Variables

Which contractor gets which job(s).

Objective

Minimize the total cost of the four jobs.

Constraints

Contractor 1 can do no more than one job.

Contractors 2 and 3 can do no more than two jobs each.

Contractor 2 can’t do job 4.

Contractor 3 can’t do job 1.

Every job needs one contractor.

Decision Models -- Prof. Juran

Formulation

Decision Variables

Define Xij to be a binary variable representing the assignment of contractor i to job j. If contractor i ends up doing job j, then Xij = 1. If contractor i does not end up with job j, then Xij = 0.

Define Cij to be the cost; i.e. the amount bid by contractor i for job j.

Objective

Minimize Z =

Decision Models -- Prof. Juran

Formulation

Constraints

for all j.

for i = 1.

for i = 2, 3.

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

Notice the very large values in cells B4 and E3. These specific values (10,000) aren’t important; the main thing is to assign these particular contractor-job combinations costs so large that they will never be in any optimal solution.

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Optimal Solution

Decision Models -- Prof. Juran

Conclusions

The optimal solution is to award Job 4 to Contractor 1, Jobs 1 and 3 to Contractor 2, and Job 2 to Contractor 3. The total cost is \$182,000.

Decision Models -- Prof. Juran

Sensitivity Analysis
• What is the “cost” of restricting Contractor 1 to only one job?
• How much more can Contractor 1 bid for Job 4 and still get the job?

Decision Models -- Prof. Juran

Conclusions
• The sensitivity report indicates a shadow price of –2 (cell E29).
• (Allowing Contractor 1 to perform one additional job would reduce the total cost by 2,000.)
• The allowable increase in the bid for Job 4 by Contractor 1 is 3.
• (He could have bid any amount up to \$43,000 and still have won that job.)

Decision Models -- Prof. Juran

Network Representation

Con. 1

Con. 2

Con. 3

Job 1

Job 2

Job 3

Job 4

Decision Models -- Prof. Juran

Optimal Solution

Con. 1

Con. 2

Con. 3

44

51

40

47

Job 1

Job 2

Job 3

Job 4

Decision Models -- Prof. Juran

House-Building Example

Decision Models -- Prof. Juran

Managerial Problem Definition

Find the critical path and the minimum number of days needed to build the house.

Decision Models -- Prof. Juran

Network Representation

F

6

1

3

4

G

3

A

5

E

4

0

Start

5

End

C

10

B

8

D

5

2

Decision Models -- Prof. Juran

Formulation

Decision Variables

We are trying to decide when to begin and end each of the activities.

Objective

Minimize the total time to complete the project.

Constraints

Each activity has a fixed duration.

There are precedence relationships among the activities.

We cannot go backwards in time.

Decision Models -- Prof. Juran

Formulation

Decision Variables

Define the nodes to be discrete events. In other words, they occur at one exact point in time. Our decision variables will be these points in time.

Define ti to be the time at which node i occurs, and at which time all activities preceding node i have been completed.

Define t0 to be zero.

Objective

Minimize t5.

Decision Models -- Prof. Juran

Formulation

Constraints

There is really one basic type of constraint. For each activity x, let the time of its starting node be represented by tjx and the time of its ending node be represented by tkx. Let the duration of activity x be represented as dx.

For every activity x,

For every node i,

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

The matrix of zeros, ones, and negative ones (B12:G18) is a means for setting up the constraints.

The sumproduct functions in H12:H18 calculate the elapsed time between relevant pairs of nodes, corresponding to the various activities.

The duration times of the activities are in J12:J18.

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Optimal Solution

Decision Models -- Prof. Juran

Conclusions

The project will take 26 days to complete.

The only activity that is not critical is the electrical wiring.

Decision Models -- Prof. Juran

CPM Jargon

Any activity for which

is said to have slack time, the amount of time by which that activity could be delayed without affecting the overall completion time of the whole project. In this example, only activity D has any slack time (13 – 5 = 8 units of slack time).

Decision Models -- Prof. Juran

CPM Jargon

Any activity x for which

is defined to be a “critical” activity, with zero slack time.

Decision Models -- Prof. Juran

F

6

1

3

4

G

3

A

5

E

4

0

Start

5

End

C

10

B

8

D

5

2

CPM Jargon

Every network of this type has at least one critical path, consisting of a set of critical activities. In this example, there are two critical paths: A-B-C-G and A-B-E-F-G.

Decision Models -- Prof. Juran

Excel Tricks: Conditional Formatting

Decision Models -- Prof. Juran

House-Building Example, Continued

Suppose that by hiring additional workers, the duration of each activity can be reduced. Use LP to find the strategy that minimizes the cost of completing the project within 20 days.

Decision Models -- Prof. Juran

Crashing Parameters

Decision Models -- Prof. Juran

Managerial Problem Definition

Find a way to build the house in 20 days.

Decision Models -- Prof. Juran

Formulation

Decision Variables

Now the problem is not only when to schedule the activities, but also which activities to accelerate. (In CPM jargon, accelerating an activity at an additional cost is called “crashing”.)

Objective

Minimize the total cost of crashing.

Decision Models -- Prof. Juran

Formulation

Constraints

The project must be finished in 20 days.

Each activity has a maximum amount of crash time.

Each activity has a “basic” duration. (These durations were considered to have been fixed in Part a; now they can be reduced.)

There are precedence relationships among the activities.

We cannot go backwards in time.

Decision Models -- Prof. Juran

Formulation

Decision Variables

Define the number of days that activity x is crashed to be Rx.

For each activity there is a maximum number of crash days Rmax, x

Define the crash cost per day for activity x to be Cx

Objective

Minimize Z =

Decision Models -- Prof. Juran

Formulation

Constraints

For every activity x,

For every activity x,

For every node i,

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

G3 now contains a formula to calculate the total crash cost.

The new decision variables (how long to crash each activity x, represented by Rx) are in M12:M18.

G8 contains the required completion time, and we will constrain the value in G6 to be less than or equal to G8.

The range J12:J18 calculates the revised duration of each activity, taking into account how much time is saved by crashing.

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Optimal Solution

Decision Models -- Prof. Juran

F

6

1

3

4

G

3

A

3

E

3

0

Start

5

End

C

9

B

5

D

5

2

Conclusions

It is feasible to complete the project in 20 days, at a cost of \$145.

Decision Models -- Prof. Juran

Conclusions

Decision Models -- Prof. Juran

An Alternative Solution

Decision Models -- Prof. Juran

Excel Tricks: VLOOKUP

Looks for a specific value in the left column of a table and finds the row where that value appears, then returns the value corresponding to another specified column in that row.

Decision Models -- Prof. Juran

Integer Programs
• We have seen models where the decision variables had to be integers, but we didn’t have to impose a Solver constraint to make that happen (a special attribute of some transportation models)
• In general, you do need to impose a Solver constraint to force an integer solution
• No more Simplex algorithm!
• Binary = Special case of integer

Decision Models -- Prof. Juran

Difficulties with Integer Programs
• Linear approximations are not necessarily feasible or optimal
• Integer optimal solutions require much more complicated algorithms
• Integer algorithms do not yield a sensitivity report

Decision Models -- Prof. Juran

Optimal linear-programming solution: X = 1.857, Y = 0

Rounded to X = 2, Y = 0 is infeasible

Rounded to X = 1, Y = 0 is not optimal

Optimal integer-programming solution: X = 0, Y = 3

Decision Models -- Prof. Juran

Hospital Location Example

A county is going to build two hospitals.

There are nine cities in which the hospitals can be built.

The number of hospital visits per year made by people in each city and the x-y coordinates of each city are listed in the table below.

The county's goal is to minimize the total distance that patients must travel to hospitals. Where should it locate the hospitals?

Decision Models -- Prof. Juran

Hospital Location Example

Decision Models -- Prof. Juran

Hospital Location Example

Decision Models -- Prof. Juran

Managerial Problem Definition

Decision Variables

We need to decide whether or not to build a hospital in each of nine cities.

We also need to decide how many visits there will be from each city to each hospital.

Objective

We want to minimize the total distance traveled to the hospital by all county residents.

Decision Models -- Prof. Juran

Managerial Problem Definition

Constraints

The cities can’t be moved.

Exactly two hospitals will be built.

All of the planned hospital visits must be accounted for and included in the total distance calculation.

No hospital visits are allowed to a city that has no hospital.

Decision Models -- Prof. Juran

3

4

7

8

5

9

6

2

1

Network Representation

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

One Hospital

1

Decision Models -- Prof. Juran

Formulation

Decision Models -- Prof. Juran

Formulation

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

The 9 Xj decision variables are in the range C3:K3.

The 81 Vij decision variables are in the range C7:K15.

The objective function is in cell P3.

The matrix in the range N18:V26 calculates the distance between each pair of cities using a long and ugly Excel function based on the famous Pythagorean theorem.

Cell N3 is used to keep track of constraint (1).

Cells L7:L15 and N7:N15 are used to keep track of constraint (2).

Cells C16:K16 and C18:K18 are used to keep track of constraint (3).

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Solution Methodology

Decision Models -- Prof. Juran

Optimal Solution

Decision Models -- Prof. Juran

Optimal Solution

If the county wants to build two hospitals, then the optimal locations are in City 4 (Lowthersburg) and City 8 (Rothsboro). The total miles traveled would be 132,500.

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

Two Hospitals

1

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

Three Hospitals

1

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

Four Hospitals

1

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

Five Hospitals

1

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

Six Hospitals

1

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

Seven Hospitals

1

Decision Models -- Prof. Juran

Network Representation

3

4

7

8

5

9

6

2

Eight Hospitals

1

Decision Models -- Prof. Juran

3

4

7

8

5

9

6

2

1

Network Representation

Nine Hospitals

Decision Models -- Prof. Juran

Summary

More Network Models

• Assignment Model (Contract Bidding)
• “Big Cost” trick
• Project Management (House Building)
• More binary / integer tricks
• Critical Path / Slack Time
• Excel trick: Conditional Formatting
• Cost Crashing
• Changing an objective to a constraint
• Issues with Integers
• Location Analysis (Hospital Location)

Decision Models -- Prof. Juran