The Solver | The Excel Solver

The Solver | The Excel Solver.

Order Description
lab report research study about repetition Blindness.
use the data sheet for the write up

Using The_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 Th_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 Excel  Solver. In general, the Excel  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 Excel 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 Excel  Solver for solving the problem.
(Note: To find the Excel 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 Excel  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 Excel  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 Excel 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 th_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 Excel\_ 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

The Solver

Is this question part of your Assignment?

We can help

Our aim is to help you get A+ grades on your Coursework.

We handle assignments in a multiplicity of subject areas including Admission Essays, General Essays, Case Studies, Coursework, Dissertations, Editing, Research Papers, and Research proposals

Header Button Label: Get Started NowGet Started Header Button Label: View writing samplesView writing samples