KEMBAR78
Chapter 3 - Modeling and Solving LP Problems Rev | PDF | Microsoft Excel | Analysis
Scribd
Upload
5K views20 pages

Chapter 3 - Modeling and Solving LP Problems Rev

This document provides an overview of modeling and solving linear programming problems in Excel spreadsheets using the Solver tool. It discusses setting up the Blue Ridge Hot Tubs problem as an Excel spreadsheet model with decision variables in certain cells, an objective function cell, and constraint cells. It also reviews other LP problems involving make-vs-buy decisions, investment allocation, production and inventory planning, and transportation planning. Comprehensive case examples on facility location and production planning are presented.

Uploaded by

ekbalaji
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5K views20 pages

Chapter 3 - Modeling and Solving LP Problems Rev

This document provides an overview of modeling and solving linear programming problems in Excel spreadsheets using the Solver tool. It discusses setting up the Blue Ridge Hot Tubs problem as an Excel spreadsheet model with decision variables in certain cells, an objective function cell, and constraint cells. It also reviews other LP problems involving make-vs-buy decisions, investment allocation, production and inventory planning, and transportation planning. Comprehensive case examples on facility location and production planning are presented.

Uploaded by

ekbalaji
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 20

Ragsdale, Chapter 3

Modeling and Solving LP Problems

3.4 | A Spreadsheet Model for the Blue Ridge Hot Tubs Problem

Figure 3.1

EXCEL Note: =SUMPRODUCT(B5:C5,B6:C6) is equivalent to B5*B6 + C5*C6 (or, a


sum of products)

1 Management Science QUAN3600


Ragsdale, Chapter 3

EXCEL: Data | Solver

If not activated then:

Office Button | EXCEL Options

Add-Ins | Select from list | Go

2 Management Science QUAN3600


Ragsdale, Chapter 3

Target cell: ________ (which cell is our objective?)


Equal to: ________ (do we want max or min?)
Changing cells: ________ (which cells are our decision variable cells?)

Each constraint has three parts:

Fill in these blanks:

Constraint Cell Reference Relationship Constraint


Pumps
Labor
Tubing

The first one looks like in EXCEL:

3 Management Science QUAN3600


Ragsdale, Chapter 3

So far it looks like this:

Go to Options and Select


• Assume Linear
• Assume Non-Negativity

Back to Solver Parameters Dialog Box and select SOLVE

This is what you hope to see:

Click OK

4 Management Science QUAN3600


Ragsdale, Chapter 3

Now you see the solution:

What does this tell you?

Decision?
Objective?
Constraints?

Solver Sensitivity Report

5 Management Science QUAN3600


Ragsdale, Chapter 3

Your Turn: Problem 3-12

In words, what are the:


Decision variables?
Objective function?
Constraints?

Algebraically, what are the:


Decision variables?

Objective function?

Constraints?

Identify “zones” (dec var, obj function, constraints) on the spreadsheet


for setting up this problem:
(hint: look at the Hot Tubs spreadsheet)

6 Management Science QUAN3600


Ragsdale, Chapter 3

7 Management Science QUAN3600


Ragsdale, Chapter 3

3.8 | Make vs. Buy Decisions

In words, what are the:

Decision variables?

Objective function?

Constraints?

Algebraically, what are the:

Decision variables?

Objective function?

Constraints?

8 Management Science QUAN3600


Ragsdale, Chapter 3

Fig 3.17

9 Management Science QUAN3600


Ragsdale, Chapter 3

Problem 3-181

In words, what are the:

Decision variables?

Objective function?

Constraints?

Algebraically, what are the:

Decision variables?

Objective function?

Constraints?

1
Solution at end of notes

10 Management Science QUAN3600


Ragsdale, Chapter 3

11 Management Science QUAN3600


Ragsdale, Chapter 3

3.9 | An Investment Problem

In words, what are the:

Decision variables?

Objective function?

Constraints?

Algebraically, what are the:

Decision variables?

Objective function?

Constraints?

12 Management Science QUAN3600


Ragsdale, Chapter 3

Fig 3.20

13 Management Science QUAN3600


Ragsdale, Chapter 3

3.12 | A Production and Inventory Planning Problem

Fig 3.31

14 Management Science QUAN3600


Ragsdale, Chapter 3

3.10 | A Transportation Problem

Distance (in miles) Between


Groves and Plants
Grove Ocala Orlando Leesburg
Mt. Dora 21 50 40
Eustis 35 30 22
Clermont 55 20 25

Fig 3.23

15 Management Science QUAN3600


Ragsdale, Chapter 3

Fig 3.24

16 Management Science QUAN3600


Ragsdale, Chapter 3

Your Turn: Problem 3-292

2
Solution at end of notes

17 Management Science QUAN3600


Ragsdale, Chapter 3

Comprehensive Examples

Case 3-3

Identify the problem:

Why would LP help us?

What are we trying to decide?

What is the goal?

Any limiting factors?

18 Management Science QUAN3600


Ragsdale, Chapter 3

Case 3-4

Identify the problem:

Why would LP help us?

What are we trying to decide?

What is the goal?

Any limiting factors?

19 Management Science QUAN3600


Ragsdale, Chapter 3

Your Turn Partial Solutions

Problem 3-18

Problem 3-29

20 Management Science QUAN3600

You might also like