Using The Excel Solver To Solve Mathematical Programs

Using The Excel Solver To Solve Mathematical Programs

summarize the file “Chapter 8: Using The Excel Solver To Solve Mathematical Programs.” Give pictures,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