3 Software - Excel

Contents | ‹ Previous | Next › | Download EPUB | PDF

Chapter 3
Software - Excel

Learning Outcomes

Learn relevant Excel functions
Set up linear programs in Excel
Solve linear programs in Excel

Excel Solver is a powerful tool for solving optimization problems with linear and nonlinear constraints. Before diving into using Solver, it is essential to understand some basic Excel functions and techniques that are commonly used when setting up optimization problems. These functions help organize data, compute intermediate values, and define objective functions and constraints.

3.1.1 Useful Excel Functions and Techniques

Using Formulas in Cells In Excel, formulas begin with an equals sign (=). For example, typing =A1+B1 in a cell will calculate the sum of the values in cells A1 and B1.

SUM() The SUM() function adds a range of numbers. For example, =SUM(A1:A10) calculates the sum of all values in the range A1 to A10.

SUMPRODUCT() The SUMPRODUCT() function multiplies corresponding elements in two or more arrays and returns the sum of those products. This is particularly useful for computing dot products or weighted sums. For example, =SUMPRODUCT(A1:A10, B1:B10) computes $\sum_{i = 1}^{10} A_{i} B_{i}$ .

COUNT() The COUNT() function counts the number of numeric entries in a range. For example, =COUNT(A1:A10) returns the number of numeric cells in the range A1 to A10.

COUNTIF() The COUNTIF() function counts the number of cells in a range that meet a specific condition. For example, =COUNTIF(A1:A10, ">5") counts the number of cells in A1:A10 with values greater than 5.

Duplicating Formulas Across Rows or Columns To apply a formula to multiple cells, you can drag the fill handle (a small square at the bottom-right corner of the selected cell) across the desired range. Excel automatically adjusts cell references based on the relative position (relative referencing). To keep a reference constant, use the dollar sign ($) in the cell reference (absolute referencing). For example, $A$1 always refers to cell A1.

Conditional Formatting Conditional formatting allows you to highlight cells that meet specific criteria. For example, you can format cells with values above a threshold in bold or a particular color to identify important data visually.

Data Validation Data validation helps restrict the type of data entered in a cell. For example, you can set a rule that only allows numbers within a specific range or text from a predefined list.

Naming Ranges Naming ranges makes formulas easier to read and maintain. Instead of =SUM(A1:A10), you can name the range "Sales" and write =SUM(Sales).

Using Logical Functions Logical functions like IF(), AND(), and OR() are helpful for decision-making within formulas. For example, =IF(A1>10, "High", "Low") returns “High” if A1 is greater than 10, and “Low” otherwise.

With these foundational tools, setting up optimization problems becomes much more manageable. In the next section, we will explore how to use these functions in combination with Excel Solver to define and solve optimization models.

3.1.2 How to Use Excel Solver

Excel Solver is a powerful add-in that allows users to define and solve optimization problems. This section provides a step-by-step guide on how to effectively layout data and use Solver for optimization.

Step 1: Layout the Data Organizing your data clearly is crucial for using Solver effectively. Use color coding to distinguish between different types of cells:

Data: Cells containing fixed input values, such as coefficients or constants, should be highlighted in 50!white!//light blue.
Variables: Cells representing decision variables should be highlighted in yellow.
Formulas: Cells with formulas, such as the objective function or summarizing constraints values should be highlighted in 50!white!//light green.

Other color combinations are also acceptable, as long as they consistently distinguish between these components.

Figure 3.1: Excel spreadsheet showing the recommended Solver layout: data cells (coefficients, right-hand...

Step 2: Define the Objective Function Identify the cell that represents the objective function. This cell should contain a formula that calculates the value to be maximized or minimized, such as total profit or cost.

Step 3: Set Up Constraints Clearly specify the constraints of the problem. Constraints can be represented using formulas that involve the variables, coefficients, and right-hand side values. For example, a constraint ensuring that total production does not exceed capacity can be written as =SUMPRODUCT(Production, Coefficients) <= Capacity.

Step 4: Open Solver Ensure Solver is enabled in Excel. If not, you can activate it by navigating to File > Options > Add-Ins > Manage Excel Add-ins and checking the box for Solver.

Once Solver is enabled, go to Data > Solver to open the Solver Parameters dialog box.

Figure 3.2: Screenshot of Excel’s Data ribbon with the Solver button highlighted on the right side of the...

Step 5: Define Solver Parameters In the Solver Parameters dialog box:

Set the Objective to the cell containing the objective function.
Choose Max or Min to indicate whether the objective is to be maximized or minimized.
Specify the Variable Cells by selecting the range of cells representing decision variables.
Add Constraints by specifying the relationship (e.g., <=, >=, =) and the cells involved.
When the variables are non-negative, you can select Make Unconstrained Variables Non-Negative.

Figure 3.3: Solver Parameters dialog with fields filled in: Set Objective cell, Max/Min selector, By Changing...

Step 6: Choose a Solving Method Solver provides three solving methods:

GRG Nonlinear: For smooth nonlinear problems.
Simplex LP: For linear programming problems.
Evolutionary: For non-smooth problems or those with integer constraints.

Select the method that matches the problem type.

Figure 3.4: Close-up of the Select a Solving Method dropdown in the Solver Parameters dialog, showing the three...

Step 7: Solve the Problem Click Solve to run Solver. If Solver finds a solution, it will display the results and allow you to keep or discard them. Analyze the solution to ensure it meets the problem’s requirements.

Figure 3.5: Solver Results dialog that appears after solving, offering Keep Solver Solution or Restore Original...

Step 8: Interpret the Results Review the values of the decision variables and the objective function. Check that all constraints are satisfied and assess the solution’s feasibility and quality.

Answer report

Figure 3.6: Excel Answer Report worksheet generated by Solver, listing the objective cell’s original and final...

Sensitivity report

Figure 3.7: Excel Sensitivity Report worksheet with a Variable Cells table (final value, reduced cost...

Solution on spreadsheet

Figure 3.8: Excel spreadsheet after Solver has run, with the yellow variable cells now populated with optimal...

Resources

Examples and guides

Some videos

Some other links

Try it out visually!

Excel Solver Walkthrough: cells, formulas, and the Solver dialog on a small LP.

3.2 Exercises

Warm-ups

Exercise 3.1: Solver Rerun: Minimum-Cost Network Flow

$⋆$ This exercise mirrors Example 4.10 (Minimum-Cost Network Flow). Download the Excel workbook linked in that example and change only the cost data: shipping now costs $3$ per unit from Warehouse 1 to Store 1, $7$ from Warehouse 1 to Store 2, $4$ from Warehouse 2 to Store 1, and $2$ from Warehouse 2 to Store 2. The supplies ( $20$ and $30$ ), demands ( $25$ and $25$ ), and route capacities ( $15$ , $10$ , $20$ , $20$ ) are unchanged.

1.: Re-solve with the Simplex LP method and report the optimal flow on each of the four routes.
2.: As a check, the minimum cost is $160$ . Which routes are at capacity?

[§3.1, Example 4.10]

Exercise 3.2: Solver Rerun: Production Planning over 10 Periods

$⋆$ This exercise mirrors Example 4.2 (Production Planning with 10 Periods). Download the Excel workbook linked in that example. Keep the production costs $(10, 12, \dots, 28)$ , the holding cost of $5$ , and the initial inventory $s_{0} = 5$ , but replace the demands with $(6, 9, 7, 8, 5, 10, 7, 6, 8, 4)$ .

1.: Update the demand cells and re-solve with the Simplex LP method. Report the production plan and the total cost. As a check, the minimum cost is $1252$ .
2.: The optimal plan carries no inventory between periods. Explain why, by comparing the holding cost with the period-to-period increase in production cost.

[§3.1, Example 4.2]

Core problems

Exercise 3.3: Diet Problem in Excel

$⋆ ⋆$ A student on a tight budget is planning one day of meals from the five pantry staples below. The table gives the cost and nutrition data per serving.


	Price $($)$ per serving	Calories per serving	Fat $(g)$ per serving	Protein $(g)$ per serving	Carbohydrate $(g)$ per serving

Oatmeal	$0.25$	150	$3.0$	$5.0$	27
Whole milk	$0.30$	149	$8.0$	$8.0$	12
Brown rice	$0.18$	216	$1.8$	$5.0$	45
Black beans	$0.40$	227	$0.9$	15	41
Almond butter	$0.45$	196	18	$7.0$	6

Table 3.1: Cost and nutrition data per serving for the five pantry staples.

Choose (possibly fractional) numbers of servings of each food so that the day’s totals satisfy all of the following requirements at the lowest possible grocery cost:

calories must be at least 2200,
fat must be at least $60 g$ ,
protein must be at least $90 g$ ,
carbohydrates must be at least $300 g$ .

Formulate the LP that finds the cheapest plan meeting every requirement.

Solve this problem using Excel Solver. Provide a screenshot of your spreadsheet with the optimal solution.

[§3.1]

Exercise 3.4: Production Planning in Excel

$⋆ ⋆$ A small factory produces two products: widgets and gadgets. Each widget earns $8 in profit and each gadget earns $6. Production is limited by two resources:

	Widget	Gadget	Available

Machine time (hrs)	2	1	40
Raw material (kg)	1	2	30

Table 3.2: Resource usage per unit and availability for widgets and gadgets.

Both products require nonnegative production quantities.

1.: Set up this problem in Excel, clearly color-coding data cells (blue), variable cells (yellow), and formula cells (green).
2.: Solve using Excel Solver with the Simplex LP method. Report the optimal production quantities and maximum profit.
3.: Generate a Sensitivity Report. Using the report, answer: if the profit per widget increases to $9, does the optimal production plan change?

[§3.1]

Exercise 3.5: Transportation Problem in Excel

$⋆ ⋆$ A company has two warehouses (W1 and W2) that supply three retail stores (S1, S2, S3). The shipping cost per unit, supply at each warehouse, and demand at each store are given below:

	S1	S2	S3	Supply

W1	$4	$8	$1	60
W2	$6	$3	$5	50

Demand	30	40	40

Table 3.3: Shipping cost per unit, warehouse supplies, and store demands.

1.: Set up a transportation model in Excel with decision variables representing the number of units shipped from each warehouse to each store.
2.: Solve using Excel Solver to minimize total shipping cost. Report the optimal shipping plan and minimum cost.

[§3.1, §4.5]

Exercise 3.6: Product Mix with a Sensitivity Report

$⋆ ⋆$ A furniture shop makes tables, chairs, and desks with profits of $70, $50, and $90 per item. Weekly resources are 240 hours of carpentry, 100 hours of finishing, and 2000 board-feet of wood:

	Table	Chair	Desk	Available

Carpentry (hrs)	4	3	6	240
Finishing (hrs)	2	1	3	100
Wood (bd-ft)	30	20	40	2000

Table 3.4: Resource requirements per item and weekly availability for tables, chairs, and desks.

Production quantities may be fractional.

1.: Set up the LP in Excel with color-coded data, variable, and formula cells, and solve with the Simplex LP method. Report the production plan and the maximum profit. As a check, the maximum profit is $4100.
2.: Generate a Sensitivity Report. Report the shadow price of each resource and state which constraints are binding.
3.: Using only the report, decide: is it worth paying $10 per hour for additional finishing time?
4.: The report lists an allowable increase for the desk profit coefficient. If the profit per desk rises to $100, does the production plan change? What if it rises to $110?

[§3.1]

Exercise 3.7: Unbalanced Transportation with a Sensitivity Report

$⋆ ⋆$ Three plants supply three cities. Plant capacities are 70, 50, and 40 units; city demands are 50, 60, and 40 units, so total supply exceeds total demand. Shipping costs per unit are:

	C1	C2	C3	Supply

P1	$4	$5	$7	70
P2	$6	$3	$2	50
P3	$5	$8	$4	40

Demand	50	60	40

Table 3.5: Shipping costs per unit, plant capacities, and city demands.

1.: Set up the model in Excel with supply constraints as $\leq$ and demand constraints as $=$ , and solve with the Simplex LP method. Report the shipping plan and the minimum cost. As a check, the minimum cost is $560.
2.: Which plant does not ship its full capacity, and how many units stay there?
3.: Generate a Sensitivity Report. Report the shadow price of each supply constraint and interpret the sign of the nonzero one: what would one extra unit of capacity at that plant be worth?

[§3.1, §4.5]

Concepts and connections

Exercise 3.8: Simplex LP versus GRG Nonlinear

$⋆ ⋆$ Solver’s dialog offers three solving methods: Simplex LP, GRG Nonlinear, and Evolutionary.

1.: Describe what class of problems each of Simplex LP and GRG Nonlinear is designed for.
2.: If your model is a linear program, why should you choose Simplex LP rather than GRG Nonlinear, even though both may return the same answer? Consider the guarantee on the solution (global versus possibly local optimum) and the contents of the Sensitivity Report.
3.: Suppose you select Simplex LP but one of your constraint cells contains the formula =B2*B3, where both B2 and B3 are variable cells. What will Solver do, and why?
4.: Give an example of an objective function for which GRG Nonlinear is appropriate and Simplex LP is not.

[§3.1]

Challenge problems

Exercise 3.9: When a Variable Must Go Negative

$⋆ ⋆ ⋆$ An investor has $100 (in thousands) to split between fund A, which returns 5%, and fund B, which returns 12%. The broker allows short selling fund A: the investor may hold a negative amount of A and use the proceeds to buy more of B. Margin rules cap the holding in B at $140. The model is

\begin{array}{l} \max & 0.05 x_{A} + 0.12 x_{B} \\ s.t. & x_{A} + x_{B} = 100 \\ x_{B} \leq 140, x_{B} \geq 0, x_{A} free . \end{array}

1.: Set up the model in Excel and solve with the Simplex LP method, leaving Make Unconstrained Variables Non-Negative checked. Report the solution.
2.: Now uncheck Make Unconstrained Variables Non-Negative, add the constraint $x_{B} \geq 0$ explicitly, and re-solve. Report the new solution.
3.: Explain exactly what the checkbox does and why it must be unchecked for this model. What risk do you take on for other variables when you uncheck it?

[§3.1]

Selected Solutions

Solution

(Exercise 3.4) Let $x_{1}$ and $x_{2}$ be the number of widgets and gadgets produced. The LP behind the spreadsheet is

\begin{array}{l} \max & z = 8 x_{1} + 6 x_{2} \\ s.t. & 2 x_{1} + x_{2} \leq 40 & (machine time) \\ x_{1} + 2 x_{2} \leq 30 & (raw material) \\ x_{1}, x_{2} \geq 0 . \end{array}

In Excel, put the two variables in yellow cells, compute the objective and the two constraint left-hand sides with SUMPRODUCT in green cells, and add the constraints in Solver with the Simplex LP method. Solver returns $x_{1} = 50 ∕ 3 \approx 16.67$ widgets and $x_{2} = 20 ∕ 3 \approx 6.67$ gadgets with maximum profit $520 ∕ 3 \approx $ 173.33$ ; both resource constraints are binding. For part 3, the Sensitivity Report shows the allowable increase for the widget profit coefficient. Raising it from $8 to $9 stays within the allowable range, so the optimal production plan does not change — only the profit does, rising to $190. (Geometrically, the objective slope stays between the slopes of the two binding constraints, so the same corner point remains optimal.)

Solution

(Exercise 3.5) Let $x_{i j} \geq 0$ be the number of units shipped from warehouse $i$ to store $j$ . A natural Excel layout is a $2 \times 3$ grid of yellow variable cells mirroring the cost table, with row sums compared to supply and column sums compared to demand:

\begin{array}{l} \min & z = 4 x_{11} + 8 x_{12} + x_{13} + 6 x_{21} + 3 x_{22} + 5 x_{23} \\ s.t. & x_{11} + x_{12} + x_{13} \leq 60 & (supply W1) \\ x_{21} + x_{22} + x_{23} \leq 50 & (supply W2) \\ x_{11} + x_{21} = 30, x_{12} + x_{22} = 40, x_{13} + x_{23} = 40 & (demands) \\ x_{i j} \geq 0 . \end{array}

Total supply equals total demand (110 units), so both supplies are used fully. Solver finds the optimal plan $x_{11} = 20$ , $x_{13} = 40$ , $x_{21} = 10$ , $x_{22} = 40$ (all other routes unused), with minimum cost $300: each warehouse ships everything it can along its cheapest routes (W1 to S3, W2 to S2), and store S1’s demand is split to make the supplies balance.

Solution

(Exercise 3.9) With the checkbox checked, Solver silently adds $x_{A} \geq 0$ and $x_{B} \geq 0$ , so the best it can do is $x_{A} = 0$ , $x_{B} = 100$ , for a return of $0.05 \cdot 0 + 0.12 \cdot 100 = 12$ (that is, $12,000). With the checkbox unchecked and $x_{B} \geq 0$ added as an explicit constraint, Solver finds $x_{A} = - 40$ , $x_{B} = 140$ : the investor shorts $40 of fund A to push fund B to its margin cap, earning $0.05 (- 40) + 0.12 (140) = 14.8$ , or $14,800. The checkbox applies a lower bound of zero to every decision variable that has no explicit lower bound in the constraint list. Short selling is exactly a negative holding, so that hidden bound cuts off the optimal solution. The risk when unchecking is that all other variables also lose the automatic bound: any variable that should be non-negative (here $x_{B}$ ) must now get its own $\geq 0$ constraint, or Solver may return meaningless negative values for it.

Chapter 3Software - Excel

3.1 Using Excel Solver

3.1.1 Useful Excel Functions and Techniques

3.1.2 How to Use Excel Solver

3.2 Exercises

Selected Solutions

Chapter 3
Software - Excel