Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Solved Problems

Solved Problem 10-1 Consider the 0–1 integer programming problem that follows:

\begin{array}{l} Maximize & 50 X_{1} + 45 X_{2} + 48 X_{3} \\ subject to & 19 X_{1} + 27 X_{2} + 34 X_{3} \leq 80 \\ 22 X_{1} + 13 X_{2} + 12 X_{3} \leq 40 \\ X_{1}, X_{2}, X_{3} must be either 0 or 1 \end{array}

$\begin{array}{l} Maximize & 50 X_{1} + 45 X_{2} + 48 X_{3} \\ subject to & 19 X_{1} + 27 X_{2} + 34 X_{3} \leq 80 \\ 22 X_{1} + 13 X_{2} + 12 X_{3} \leq 40 \\ X_{1}, X_{2}, X_{3} must be either 0 or 1 \end{array}$

Now reformulate this problem with additional constraints so that no more than two of the three variables can take on a value equal to 1 in the solution. Further, make sure that if $X_{1} = 1$ $X_{1} = 1$ , then $X_{2} = 1$ $X_{2} = 1$ also. Then solve the new problem using Excel.

Solution

Excel can handle all-integer, mixed-integer, and 0–1 integer problems. Program 10.12 shows two new constraints to handle the reformulated problem. These constraints are

X_{1} + X_{2} + X_{3} \leq 2

$X_{1} + X_{2} + X_{3} \leq 2$

and

X_{1} - X_{2} \leq 0

$X_{1} - X_{2} \leq 0$

The optimal solution is $X_{1} = 1, X_{2} = 1, X_{3} = 0$ $X_{1} = 1, X_{2} = 1, X_{3} = 0$ , with an objective function value of 95.

Screenshot of an Excel titled solved problem 10-1. — Program 10.12 Excel 2016 Solution for Solved Problem 10.1

Figure 10.12 Full Alternative Text

An image shows the formula equals SUMPRODUCT($B$4 colon $D$4, B5 colon D5).

Solved Problem 10-2 Recall the Harrison Electric Company goal programming problem seen in Section 10.4. Its LP formulation was

$\begin{array}{l} Maximize profit = & $ 7 X_{1} + $ 6 X_{2} \\ subject to & 2 X_{1} + 3 X_{2} \leq 12 (wiring hours) \\ 6 X_{1} + 5 X_{2} \leq 30 (assembly hours) \\ X_{1}, X_{2} \geq 0 \end{array}$ $\begin{array}{l} Maximize profit = & $ 7 X_{1} + $ 6 X_{2} \\ subject to & 2 X_{1} + 3 X_{2} \leq 12 (wiring hours) \\ 6 X_{1} + 5 X_{2} \leq 30 (assembly hours) \\ X_{1}, X_{2} \geq 0 \end{array}$

where

$X_{1} =$ $X_{1} =$ number of chandeliers produced

$X_{2} =$ $X_{2} =$ number of ceiling fans produced

Reformulate Harrison Electrical as a goal programming model with the following goals:
- Priority 1: Produce at least 4 chandeliers and 3 ceiling fans.
- Priority 2: Limit overtime in the assembly department to 10 hours and in the wiring department to 6 hours.
- Priority 3: Maximize profit.
Solution

$\begin{matrix} Minimize P_{1} (d_{1}^{-} + d_{2}^{-}) + P_{2} (d_{3}^{+} + d_{4}^{+}) + P_{3} d_{5}^{-} \\ \begin{array}{r} subject to X_{1} + d_{1}^{-} - d_{1}^{+} = 4 \\ X_{2} + d_{2}^{-} - d_{2}^{+} = 3 \end{array}} P r i o r i t y 1 \\ \begin{array}{r} 2 X_{1} + 3 X_{2} + d_{3}^{-} - d_{3}^{+} = 18 \\ 6 X_{1} + 5 X_{2} + d_{4}^{-} - d_{4}^{+} = 40 \end{array}} P r i o r i t y 2 \\ 7 X_{1} + 6 X_{2} + d_{5}^{-} - d_{5}^{+} = 99, 999} P r i o r i t y 3 \end{matrix}$ $\begin{matrix} Minimize P_{1} (d_{1}^{-} + d_{2}^{-}) + P_{2} (d_{3}^{+} + d_{4}^{+}) + P_{3} d_{5}^{-} \\ \begin{array}{r} subject to X_{1} + d_{1}^{-} - d_{1}^{+} = 4 \\ X_{2} + d_{2}^{-} - d_{2}^{+} = 3 \end{array}} P r i o r i t y 1 \\ \begin{array}{r} 2 X_{1} + 3 X_{2} + d_{3}^{-} - d_{3}^{+} = 18 \\ 6 X_{1} + 5 X_{2} + d_{4}^{-} - d_{4}^{+} = 40 \end{array}} P r i o r i t y 2 \\ 7 X_{1} + 6 X_{2} + d_{5}^{-} - d_{5}^{+} = 99, 999} P r i o r i t y 3 \end{matrix}$

In the priority 3 goal constraint, the 99,999 represents an unrealistically high profit. It is just a mathematical trick to use as a target so that we can get as close as possible to the maximum profit.

Solver Parameter Inputs and Selections	Key Formulas
Set Objective: E5	Copy E5 to E8:E11
By Changing cells: B4:D4
To: Max
Subject to the Constraints:
E8:E11 <= G8:G11
B4:D4 = binary
Solving Method: Simplex LP
☑ Make Variables Non-Negative

Solved Problem 10-3 In Chapter 5, the exponential smoothing method for forecasting time series was presented. Program 10.13A provides an example of this, with the smoothing constant $(α)$ $(α)$ selected to be 0.1. The forecast in time period 1 is assumed to be perfect, so the error and the absolute value of the error are both 0. The mean absolute deviation (MAD) is the measure of accuracy, and the first time period error is not used in computing this, since it was simply assumed to be perfect. The MAD is very dependent on the value of $α .$ $α .$ Use Excel to find the value of $α$ $α$ that will minimize the MAD. Hint: Instead of writing the entire objective function, simply use the cell already developed in Excel for the MAD. Remember, $α$ $α$ must be between 0 and 1.

Excel sheet of the solved problem 10.3. — Program 10.13A Excel 2016 Spreadsheet for Solved Problem 10.3

Figure 10.13A Full Alternative Text

Key Formulas

Copy C6:E6 to C7:E11

Solution

The MAD is a nonlinear function, so Solver in Excel can be used to solve this. There is only one constraint: the smoothing constant, $α,$ $α,$ must be less than or equal to 1. This can be entered directly into the Change Constraint window in Solver, with a 1 entered on the right-hand side of the inequality. Program 10.13B provides the information and the final solution for this. The value for $α$ $α$ that minimizes the MAD is 0.3478, and this yields a MAD of 16.70.

Figure 10.13B Full Alternative Text

Solver Parameter Inputs and Selections	Key Formulas
Set Objective: E12	Copy C6:E6 to C7:E11
By Changing cells: B3
To: Min
Subject to the Constraints:
B3 <= 1
Solving Method: GRG Nonlinear
☑ Make Variables Non-Negative

Note: Solver can be used in a similar manner to find the best weights to use in a weighted moving average forecast.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for
Solved Problems

Solved Problems

Solution

Program 10.12 Excel 2016 Solution for Solved Problem 10.1

Solution

Program 10.13A Excel 2016 Spreadsheet for Solved Problem 10.3

Solution

Program 10.13B Excel 2016 Spreadsheet Solution for Solved Problem 10.3

Table of Contents for Solved Problems

Create new playlist

Sign In

Sign Up

Table of Contents for
Solved Problems