Optimization with Excel Solver

Accessing Solver: Go to the 'Data' tab in Excel and click on 'Solver'.
Objective Function: Specify the cell containing the total profit (e.g., orange cell or type in 'E6').
- No need to include dollar signs; Solver adds them automatically.
Optimization Goal: Choose 'Maximize' to maximize profit or 'Minimize' to minimize cost.
Variable Cells: Click and drag to select the decision cells (yellow cells).

Batch Adding Constraints: Add multiple constraints at once if they have the same direction (e.g., all less than or equal to).
Left-Hand Side (LHS): Click and drag the cells containing the totals from the left-hand side of the constraints (e.g., totals for large and small bricks).
Operator: Select the appropriate operator (=,
Right-Hand Side (RHS): Click and drag the cells containing the right-hand side constraint values (e.g., available large bricks, small bricks).
Click 'Okay' after adding constraints.
Click 'Solve' to find the solution.

Adding Integer Constraint: To ensure whole number solutions, add another constraint.
Click and drag the decision cells, and select 'int' (integer) from the dropdown menu.
Solver will cut off decimal points to provide only whole number outputs.
I will specify in a word problem if there are constraints that must be in whole numbers.

The "Sum Product" formula is a useful tool for automation of formulas.
It is written as follows: $SUMPRODUCT(array1, array2, …)$
Locking Cells: To lock in your decision cells use the F4 command, or Command + T for Apple products. To make sure cells are locked, there should be a dollar sign in front of each letter and number associated with the cell you are locking: $A$1.

Decision Variables How much to make of benches ( $x<em>1$ ) and tables ( $x</em>2$ ).
Objective Function Maximize profit: $Z = 9x<em>1 + 20x</em>2$
- Put 9 under benches and 20 under tables.
Constraints
- 4x1 + 7x2
- 10x1 + 35x2
Excel Setup
- Use the SUMPRODUCT formula to calculate total profit
- Copy and paste formulas for totals, ensuring correct cell references
Solver Setup
- Set objective function to the total profit cell.
- Maximize profit by changing the decision variable cells.
- Add constraints for both equations.
Solution: 100 benches and 14.3 tables for a total profit of $3,185.
- Solver allows for decimal points, but integer constraints can be added for whole numbers.

This example is used to demonstrate minimizing costs while achieving minimum nutritional requirements.
Decision Variables: Amount of milk (m), beans (b), and oranges (o) for lunch.
Objective Function: Minimize cost: $2m + 0.2b + 0.25o$
Constraints: Minimum nutritional requirements for niacin, thiamine, and vitamin C.
- Niacin: $3.2m + 4.9b + 0.8o$
- Thiamine: $1.12m + 1.3b + 0.19o$
- Vitamin C: $32m + 0b + 93o$
Additional Constraint
- Total cost of milk and beans should be less than or equal to 60% of total cost: 2m + 0.2b <= 0.6 * (2m + 0.2b + 0.25o)
Excel Setup
- Use SUMPRODUCT to calculate total cost and nutritional totals.
- Copy and paste formulas for totals.
- Set up the additional constraint for milk and beans cost.
Solver Setup
- Set objective to minimize the total cost cell.
- Change variable cells to the decision variable cells (milk, beans, oranges).
- Add constraints for minimum nutritional requirements and the milk/beans cost constraint.
Solution: No milk, 2.44 beans, and 1.3 oranges for a total cost of $0.81.
Potential Typing Error: Be careful not to mistake “4.9” for "4.8" when inputting the beans coefficient for niacin.

Two factories shipping items to three warehouses to optimize shipping costs and meet supply/demand constraints.
Decision Variables How much to ship from each factory to each warehouse (a 2x3 matrix).
Objective Function: Maximize profit, considering production costs, shipping rates, and selling prices.
Constraints
- Supply Constraints: Each factory has a maximum production capacity (e.g., 65 units).
- Demand Constraints: Each warehouse requires at least a minimum number of units (e.g., 40 units).
Excel Setup
- Set up a 2x3 matrix for decision variables.
- Calculate profit coefficients for each route (Factory A to Warehouse 1, Factory A to Warehouse 2, etc.).
- Profit = Selling Price - Production Cost - Shipping Cost
- Use SUMPRODUCT to calculate total profit, multiplying decision variables by profit coefficients.
Solver Setup
- Set objective to maximize total profit.
- Change variable cells to the 2x3 decision variable matrix.
- Add supply constraints for each factory.
- Add demand constraints for each warehouse.
Different Demand Constraints: The demand constraints might indicate that the warehouse requires are at least a certain amount. In some situations, the retailer only wants up to a certain amount. Meaning the form of the equation might be switched to reflect the constraints accordingly. 11, 8, 2, 8, 6, -1