Transcript Financial Modeling Intro - Loyola Marymount University

Solver & Optimization Problems




An optimization problem is a problem in which we
wish to determine the best values for decision
variables that will maximize or minimize a
performance measure subject to a set of constraints
A feasible solution is set of values for the decision
variables which satisfy all of the constraints
An optimal solution is a feasible solution which
achieves the maximization/minimization objective for
the performance measure
Solver is an Excel Add-in which can identify the
optimal solutions for a correctly defined spreadsheet
model
Premium Solver for Excel (pg.54-55)
Components of an Optimization
Problem

Decision Variables: Changing cells, the
exogenous cells users experiment with to try
to improve the situation and which are under
the user’s control

Constraint Cells: The endogenous cells
that users watch to make sure that cell values
remain in an appropriate range

Objective: Target cell, the endogenous
performance measure cell that the user wants
to maximize or minimize
Influence Chart Notation



Changing Cells: No arrows are directed into these
points. They are parameters that are under the
manager’s control. (Denoted with squares)
Constraint Cells: Arrows must point into the cell.
Changing cells must directly or indirectly influence
constraint cells, so an attempt to attain feasibility can
be made. (Denote with circles)
Target Cell: Cell that started the influence chart.
Arrows must point into the target cell and changing
cells must directly or indirectly influence it, so an
attempt to optimize the target can be made. (Denote
with polygon)
Overview of Mathematical Programming
Optimization Techniques

Linear Programming:
• Continuous values for decision variables
• Linear constraints
• Single linear objective

Nonlinear Programming:
• Continuous values for decision variables
• Linear or nonlinear constraints
• Single linear or nonlinear objective
Overview of Mathematical Programming
Optimization Techniques (continued)

Integer Programming:
• Integer values for decision variables
• Linear constraints
• Single linear objective

Goal Programming:
• Continuous values for decision variables
• Linear or nonlinear constraints
• Several linear objectives
Linearity



A linear function is where each variable appears in a
separate term together with its constant coefficient.
The graph of a linear function of two variables is a
straight line
An optimization problem is linear if:
• the objective is a linear function of the decision
variables
• Each constraint cell is a linear function of the
decision variables
Integrality Considerations



In linear programming, the decision variables are not
required to assume only integer values. Therefore
often fractional solutions are identified as the optimal
solution.
If one or more decision variables need to consider
only integer values, the model becomes an integer
programming problem.
If possible, fractional solutions can be rounded,
interpreted as the average number or work-inprogress or ignored if the model is for planning
purposes only
Graphical Solutions (for 2 decision
variable problems)

Plot all constraints including nonnegativity
ones

Determine the feasible region. (The feasible
region is the set of feasible solution points)

Identify the optimal solution using either
•
•
the isoprofit or isocost line method
the extreme point method which is based on the
property that an optimal solution will always exist
on at least one of the corner points of the feasible
region
Types of LP Solutions

Any linear program falls in one of three categories:
•
•
•
is infeasible (the problem is overconstrained so
that no solution satisfies all the constraints)
has a unique optimal solution or alternate optimal
solutions
has an objective function that can be increased
without bound
Example: Feasible Problem with
Unique Solution

Solve graphically for the optimal
solution:
Max
s.t.
z = x1 + x2
4x1 + 3x2 > 12
2x1 + x2 < 8
x1, x2 > 0
Example: Unique Optimal
Solution

There is one point that satisfies all four constraints,
x2
and maximizes
the objective. (0,8) is the optimal
solution.
2x1 + x2 < 8
8
4x1 + 3x2 > 12
4
3
4
x1
Solver Result Messages


Solver found a solution. All constraints and optimality
conditions are satisfied: Solver has correctly
identified an optimal solution for the problem you
have formulated. Note that there may be alternative
optimal solutions possible however.
Solver has converged to the current solution. All
constraints are satisfied: You have not selected the
linear programming option in the Solver options.
Thus nonlinear programming is being performed and
this is the best solution Solver has found so far. It is
not guaranteed to be the optimal one however.
Example: Infeasible Problem

Solve graphically for the optimal
solution:
Max
s.t.
z = x1 + x2
4x1 + 3x2 < 12
2x1 + x2 > 8
x1, x2 > 0
Example: Infeasible Problem

There are no points that satisfy both constraints,
x2 problem has no feasible region, and no
hence this
optimal solution.
2x1 + x2 > 8
8
4x1 + 3x2 < 12
4
3
4
x1
Solver Result Messages

Solver could not find a feasible solution: You may
have too many constraints, one of the constraints
may be entered wrong (e.g. the inequality sign might
be going the wrong way) or you may not have
enough changing cells.

Set Cell values do not converge: Your model as
formulated is unbounded. One or more constraint is
missing from the problem or entered wrong. Often
times the modeler has forgotten to check the Assume
Nonnegativity option in Solver.
Example: Unbounded Problem

Solve graphically for the optimal
solution:
Max
s.t.
z = 3x1 + 4x2
x1 + x2 > 5
3x1 + x2 > 8
x 1, x 2 > 0
Example: Unbounded Problem

The feasible region is unbounded and the objective
function line can be moved parallel to itself without
bound so that
x z can be increased infinitely.
2
3x1 + x2 > 8
8
5
x1 + x2 > 5
Max 3x1 + 4x2
2.67
5
x1
Solver Result Messages

The Linearity Conditions required by this Solver
Engine are not satisfied: Solver’s preliminary tests
indicate that your model is not linear. This may be
the case. However sometimes the test fails not due to
nonlinearity, but due to poor scaling (e.g. some #s
are in % and others in millions). If you think your
model is linear, try resolving the model again. Some
times Solver can find the solution the second time. If
not, use the option in Solver called Use Automatic
Scaling. Solver will attempt to rescale your data. If
that doesn’t solve the problem, you will need to
rescale the data yourself.
Solver Result Messages

Solver encountered an error value in a target or
constraint cell: Using the optimization technique
selected, a cell formula resulted in an error message
and the algorithm cannot continue solving the
problem.

This can occur if you have a nonlinear formula in a target or
constraint cell and you try to solve the problem using the
Standard simplex LP technique. Make the formula linear or
switch to the Nonlinear solution technique.

This also happens when your formula results in a number that is
not real (for instance, when you divide by zero). You will need
to fix the logic and then close down and reopen Excel to clear
the registry of this error message.
Solver Modeling Requirements



All components of the optimization problem must be
on the same worksheet. Solver’s settings are saved
with the sheet.
To speed up computation time, keep reports, data
sets used to calculate parameter values, and other
intermediate calculations on a different worksheet.
Solver’s constraint dialog box will not let you enter
formulas. All formulas and calculations must be done
on the worksheet. The constraint dialog box just
compares cells to determine feasibility.
Ragsdale’s Chpt 3.9 Investment
Problem

Problem: how to design a retirement income portfolio
for retirees using corporate bonds?

Client has $750,000 in liquid assets to invest

A list of upcoming bond issues from 6 companies has
been identified and is shown in the Investment
Problem worksheet of the LPFInancialPlanning.xls
spreadsheet.

Restrictions exist regarding how money should be
allocated across the 6 bonds in order to control the
risk of the portfolio.
Retirement Planning Example




You are planning for retirement. At the
beginning of each year for the next 40 years,
you will contribute some money to your
retirement fund. You expect your investment
to earn 8% on average per year.
When you retire in 40 years, you plan to
withdraw $100,000 per year for 20 years.
How much money should you plan to
contribute to your retirement each year?
Refer to the Retirement Planning model in the
LPFinancialPlanning.xls spreadsheet