Repetition Blindness

Order Description

lab report research study about repetition Blindness.

use the data sheet for the write up

Using The Excel Solver To Solve Mathematical Programs

summarize “Chapter 8: Using The Excel Solver To Solve Mathematical Programs.” I want pictures. I want animations in each slide for written language, pictures and slid.

Chapter Overview

8.1

Introduction

8.2

Formulating Mathematical Programs

8.2.1

Parts of the Mathematical Program

8.2.2

Linear, Integer, and Nonlinear Programming

8.3

The Excel Solver

8.3.1

The Solver Steps

8.3.1.1

Standard Solver

8.3.1.2

Premium Solver

8.3.2

A Solver Example

8.3.2.1

Product Mix

8.3.2.2

Infeasibility

8.3.2.3

Unboundedness

8.3.3

Understanding Solver Reports

8.4

Applications of the Solver

8.4.1

Transportation Problem

8.4.2

Workforce Scheduling

8.4.3

Capital Budgeting

8.4.4

Warehouse Location

8.5

Limitations and Manipulations of the Solver

8.6

Summary

8.7

Exercises

Chapter 8

Using The Excel Solver

To Solve Mathematical Programs

Chapter 8: Using The Excel Solver

To Solve Mathematical Programs

2

8.1

Introduction

This chapter illustrates how to use the Excel Solver as a tool to solve mathematical

programs. We review the basic parts of formulating a mathematical program and present

several examples of how the Solver interprets these parts of the program from the

spreadsheet. We give examples of linear, integer, and non-linear programming problems

to show how the Solver can be used to solve a variety of mathematical programs. We

also give an overview of the Premium Solver and its benefits. This chapter is important

for the reader to understand as many DSS applications involve solving optimization

problems, which are mathematical programs. The reader should be comfortable with

preparing the spreadsheet for use with the Solver. In Chapter 19, we revisit the Solver

using VBA commands. We have several examples of DSS applications which use the

Solver to solve optimization problems, su

ch as Portfolio Management and Optimization.

8.2

Formulating Mathematical Programs

The Excel spreadsheet is unique because it is capable of working with complex

mathematical models. Mathematical models

transform a word problem into a set of

equations that clearly define the values that we are seeking, given the limitations of the

problem. Mathematical models are employed in

many fields, including all disciplines of

engineering. In order to solve a mathemat

ical model, we develop a mathematical

program which can numerically be solved and re

translated into a qualitative solution to

the mathematical model.

8.2.1

Parts of the Mathematical Program

A mathematical program consists of three main parts. The first is the

decision

variables

.

Decision variables

are assigned to a quantity or response that we must

determine in a problem. For example, if a toy manufacturer wants to determine how

many toy boats and toy cars to produce, we assign a variable to represent the quantity

of toy boats produced,

x

1

, and the quantity of toy cars produced,

x

2

. Decision

variables

are defined as

negative

,

non-negative,

or

unrestricted

. An

unrestricted

variable can be

either

negative

or

non-negative.

These variables represent all other relationships in a

mathematical program, including the objective, the limitations, and the requirements.

The second part of the math program, called the

objective function

, is an equation that

states the goal, or objective, of the model. In the same example of the toy manufacturer,

we want to know the quantities of toy boats and toy cars to produce. However, the goal

of the manufacturing plant’s production may be to increase profit. If we know that we can

profit $5 for every toy boat and $4 for every toy car, then our objective function is:

Maximize 5x

1

+ 4x

2

In other words, we want profit to drive us in determining the quantity of boats and cars to

produce. Objective functions are either

maximized

or

minimized

; most applications

involve maximizing profit or minimizing cost.

The third part of the math progam, the

constraints

, are the limitations of the problem.

That is, if we want to maximize our profit, as in the toy manufacturer example, we could

produce as many toys as possible if we di

d not have any limits. However, in most

Chapter 8: Using The Excel Solver

To Solve Mathematical Programs

5

8.3

The Excel Solver

We will now discuss how to operate these two versions of the Solver. In general, the

Solver must understand the problem’s mathematical program parts, which we take care

of by preparing our spreadsheet to contain distinct cells for the decision variables,

constraints, and objective function. We must then tell the Solver if we want to minimize

or maximize the problem, or if we want to solve it for a particular value of the objective

function. There are also several options that we can apply to give more specific

instructions to the Solver for solving the problem.

(Note: To find the Solver, go to

Tools > Solver

from the menu options. If you do not see

Solver

in the

Tools

menu, you must first choose the

Solver Add-In

. To do so, select the

Add-In

option from the

Tools

menu. A small dialog box will appear; from there, select

Solver

Add-In

from the list. If you do not see

Solver Add-In

in the

Add-In

list, click

Browse

and look for the

Solver.xla

file from the following directory:

C Drive

>

Program

Files

>

Microsoft Office

>

Office (or Office10

) >

Library

>

Solver

. Double-click this file.

Now you should find

Solver

Add-In

in the list; check the box next to it. Restart Excel. If

you do not find the

Solver.xla

file, go to the

Add-Ins

window as explained above; select

Solver Add-in

and press

OK

. Insert the MS Office CD in CD-ROM drive when asked.)

8.3.1

The Solver Steps

To operate the

Solver

, we must follow a short sequence of steps: 1) read and interpret

the problem; 2) prepare the spreadsheet; and 3) solve the model and review the results.

We will now describe these steps in detail for both the Standard Solver and the Premium

Solver.

The Standard Solver

STEP 1: Read and Interpret the Problem

We must first determine the type of problem that we are dealing with (linear

programming, Integer Programming, or nonlinear programming) and outline the model

parts. Whether the problem is an LP, IP, or NLP model does not affect the model parts

but does affect the Options that we specify for the Solver. They may also require some

additional constraint specifications. In each case, we still need to determine the decision

variables, the objective function, and the constraints. We need to write these

mathematically, with the objective function and constraints in terms of the decision

variables.

STEP 2: Prepare the spreadsheet

Next, we transfer these parts of the model into our Excel spreadsheet, clearly defining

each part of our model in the spreadsheet. The

Solver

interprets our model according to

the location of these model parts on the spreadsheet.

Chapter 8: Using The Excel Solver

To Solve Mathematical Programs

6

STEP 2.1

: Place the Input Table

Usually the input for the problem is provided for us. We just need to place it on the

spreadsheet in the form of a table. We reference this input when forming our constraint

and objective function formulas.

STEP 2.2

: Set the Decision Variables Cells

Next, we list the decision variables in individual cells with an empty cell next to each one.

The

Solver

places values in these cells for each

decision variable as it solves the model.

We recommend naming the range of decision variables for easier reference in constraint

and objective function formulas.

STEP 2.3

: Enter the Constraint Formulas

Now we place the constraint equations in the spreadsheet; we enter them separately

using formulas, with an optional description

next to each constraint. As each constraint

is in terms of the decision variables, all of these formulas must be in terms of the

decision variable cells already defined.

Another important consideration when laying out the constraints in preparation for the

Solver

is that there must be individual cells for the right-hand side (RHS) values as well.

We should also place all inequality signs in their own cells. This organization will become

clear once we explain how the

Solver

interprets our model.

Another advantageous way to keep our constraints organized as we use the

Solver

is to

name cells. We can also group constraints that have the same inequality signs. The

benefit of this habit will become apparent once we input the model parts for the

Solver

.

STEP 2.4

: Enter the Objective Function Formula

We can now place our objective function in a cell by transforming this equation into a

formula in terms of the decision variables. The spreadsheet is now prepared for the

Solver

with all three parts of the model clearly displayed.

STEP 3: Solve the Model with the

Solver

The

Solver

can now interpret this information and use algorithms to solve the model. The

Solver

receives the decision variables, constraint equations, and objective function

equation as input into a hidden programming code that applies the algorithm to the data.

We will explain in more detail how this programming works when we discuss VBA. To

use

Solver

, we choose

Tools > Solver

from the menu; the window in Figure 8.3 then