EIN 4905/ESI 6912 Decision Support Systems Excel

1
Spreadsheet-Based Decision Support Systems
Chapter 8 The Solver and Mathematical Programming
Prof. Name
name_at_email.com Position
[phone] University Name
2
Overview

• Introduction
• Formulating Mathematical Programs
• The Excel Solver
• Applications of the Solver
• Summary

3
Introduction

• Formulating a mathematical program by determining
  its decision variables, constraints, and
  objective function
• The difference between linear, integer, and
  nonlinear programming problems
• Using the Excel Solver to solve a mathematical
  program
• Preparing the spreadsheet with the model parts
  and then enter the corresponding cells into the
  Solver window
• Reading the Solver reports
• Example linear, integer, and nonlinear
  programming problems

4
Formulating Mathematical Programs

• Parts of the Mathematical Program
• Linear, Integer, and Nonlinear Programming

5
Parts of the Mathematical Program

• Decision Variables variables assigned to a
  quantity or response that must be determined in
  the problem
• Objective Function equation which states the
  goal of the model
• Maximize
• Minimize
• Constraints equations which state limitations
  of the problem
• To solve the model, each constraint must be
  considered simultaneously in conjunction with the
  objective function

6
Linear Integer and Nonlinear Programming

• Linear Programming problem there is a linear
  relationship among all constraints and the
  objective function
• Integer Programming problem decision variables
  can only take integer values in a given range
  (these integer values can also be boolean 0 or
  1 only)
• Nonlinear Programming problem do not have a
  linear objective function and/or constraints. NLP
  problems must use more challenging methods to
  solve these complex equations.

7
The Excel Solver

• The Solver Steps
• Understanding Solver Reports

8
The Solver Steps

• Step 1 Read and Interpret the Problem
• Step 1.1 determine the decision variables
• Step 1.2 state the objective function
• Step 1.3 state any constraints
• Step 2 Prepare the Spreadsheet
• Step 2.1 Enter the decision variables
• Step 2.2 Enter the constraints
• Step 2. 3 Enter the objective function
• Step 3 Solve the model with the Solver
• Step 3.1 Set the Target Cell and choose Min or
  Max
• Step 3.2 Select Changing Cells
• Step 3.3 Add Constraints
• Step 3.4 Set Solver Options
• Step 3.5 Solve and review Results

9
Product Mix Problem

• A company produces six different types of
  products. They want to schedule their production
  to determine how much of each product type should
  be produced in order to maximize their profits.
  This is known as the Product Mix problem.
• Production of each product type requires labor
  and raw materials but the company is limited by
  the amount of resources available.
• There is also a limited demand for each product,
  and no more than this demand per product type can
  be produced. Input tables for the necessary
  resources and the demand are given.

10
Step 1

• Decision Variables The amount produced of each
  product type
• x1, x2, x3, x4, x5, x6
• Objective Function Maximize Profit
• z p1x1 p2x2 p3x3 p4x4 p5x5 p6x6
• Constraints
• Labor Constraint
• l1x1 l 2x2 l 3x3 l4x4 l 5x5 l 6x6
  lt available labor 4500
• Raw Material Constraint
• r1x1 r 2x2 r 3x3 r 4x4 r 5x5 r
  6x6 lt available raw material 1600
• Demand Constraint
• xi lt Di for i 1 to 6

11
Step 2

• The spreadsheet should have each part of the
  model clearly entered

12
Step 3

• The Solver parameters can now be set according to
  the cell references with the appropriate model
  parts

13
Step 3 (contd)

• Solver Options should also now be set

14
Figure 8.11

• The results of the Solver are shown
• All constraints are met

15
Solver Reports

• Answer Report lists the target cell and the
  changing cells with their original and final
  values, constraints, and information about the
  constraints.
• Sensitivity Report shows how sensitive the
  solution is to small changes in the objective
  function formula or the constraints.
• Limits Report lists the target cell and the
  changing cells with their respective values,
  lower and upper limits, and target values.

16
Applications of the Solver

• Transportation Problem
• Workforce Scheduling
• Capital Budgeting
• Warehouse Location

17
Transportation Problem

• A company ships their products from three
  different plants (one in LA, one in Atlanta, and
  one in New York City) to four regions of the
  United States (East, Midwest, South, West).
• Each plant has a capacity on how many products
  can be sent out, and each region has a demand of
  products they must receive.
• There is a different transportation cost between
  each plant, or each city, and each region.
• The company wants to determine how many products
  each plant should ship to each region in order to
  minimize the total transportation cost.

18
Transportation Problem (contd)

• Decision variables
• The amount to ship from each plant to each region
• Constraints
• Demand the total number of products received by
  a region (from each plant) is greater than or
  equal to its demand
• Capacity the total number of products shipped
  from a plant (to each region) is less than or
  equal to its capacity
• Objective function
• Minimize the total transportation costs

19
Figure 8.20

• Prepare the spreadsheet

20
Figure 8.21

• The Solver window

21
Figure 8.22

• The solution

22
Workforce Scheduling

• A company wants to schedule its employees for
  every day of the week.
• Employees work 5 days consecutively, so the
  company wants to schedule on which day each
  employee starts working that is how many
  employees start working each day.
• There is a certain number of employees needed
  each day of the week.
• The objective function is to find the schedule
  which minimizes the total number of employees
  working for the week.

23
Workforce Scheduling (contd)

• Decision variables
• The number of employees that will begin working
  (for 5 consecutive days) on each day of the week
• Constraints
• The total number of employees working on a given
  day (regardless of which day they started
  working) is greater than or equal to the number
  of employees needed on that particular day
• objective function
• Minimize the total number of employees needed

24
Figure 8.23

• Prepare the spreadsheet

25
Figure 8.24

• The Solver window

26
Figure 8.25

• The solution

27
Figures 8.26 and 8.27

• Integer constraint must be added

28
Figure 8.28

• Updated solution

29
Capital Budgeting

• There are 20 projects that a company, or
  individual, can invest in.
• Each project has a net present value (NPV) and
  cost per year given.
• The company, or investor, wants to determine how
  much to invest in each project, given a limited
  amount of yearly funds available, in order to
  maximize the total NPV of the investment.

30
Capital Budgeting (contd)

• Decision variables
• Which projects we do and do not invest in
• Constraints
• No more than the yearly available funds can be
  spent each year
• Objective Function
• Maximize the total NPV

31
Figure 8.30

• Prepare the spreadsheet

32
Figures 8.31 and 8.32

• A binary constraint
• The Solver window

33
Figure 8.33

• The solution

34
Warehouse Location

• A company stores all of its products in one
  warehouse.
• It has customers in cities around the United
  States and is trying to determine the best
  location of their warehouse in order to minimize
  the total transportations costs.
• Each citys location is given by its latitude and
  longitude. The number of shipments made to each
  city is also given. We are to determine the
  warehouse location based on its latitude and
  longitude values.

35
Warehouse Location (contd)

• Decision variable
• The latitude and longitude values of the location
  of the warehouse
• Constraints
• The latitude and longitude for the warehouse
  location must be between the values of 0 and 120
• Objective function
• Minimize the total distance traveled from the
  warehouse to each city

36
Figures 8.34 and 8.36

• Prepare the spreadsheet

37
Figure 8.37

• The Solver window

38
Figure 8.41

• The solution

39
Summary

• The three parts of a mathematical model are
  decision variables, objective function, and
  constraints.
• The three primary types of mathematical models
  are linear, integer, and nonlinear programming
  problems.
• Using Solver involves three main steps reading
  and interpreting the problem to determine the
  three parts of the model preparing the
  spreadsheet so that Solver can read the data and
  running the Solver.
• LP examples are Transportation and Workforce
  Scheduling. An IP example is Capital Budgeting,
  and an NLP example is the Warehouse Location
  problem.
• We use the Premium Solver to solve NLP problems
  since the Genetic Algorithm is more accurate.

40
Additional Links

• (place links here)