Lesson 2: Spreadsheet Implementation and Solver Use

1 Modelling context

Once the LP is formulated, implementing it in a spreadsheet is mechanical. The spreadsheet serves two purposes: it computes the objective and constraint left-hand sides from the decision variables, and it provides the solver with the structure of the problem. A well-organised layout makes the model readable, editable, and auditable.

The layout follows the three-layer discipline from Module 1: inputs (objective and constraint coefficients, bounds) in one region, calculations (objective, constraint left-hand sides) in another, and decision variables in a clearly identified region that the solver will adjust. Documentation records the units and interpretation of each coefficient.

Solver output is not the answer

The solver returns the optimal solution to the LP you specified. It does not check that the LP corresponds to the problem you meant to solve. The checks in Lesson 3 address this gap.

2 Learning objectives

By the end of this lesson you will be able to

Lay out an LP in a spreadsheet with separated input, decision variable, and calculation regions.
Configure the spreadsheet solver to maximize or minimize the objective.
Specify the constraints and the decision-variable cells correctly.
Interpret the solver's output, including status messages and the optimum.
Re-run the solver after changing coefficients and observe the effect.

3 Prerequisite review

Solver: A spreadsheet tool that adjusts a specified set of cells to maximize or minimize a target cell, subject to constraints. Enabled via the spreadsheet software's add-ins.
SUMPRODUCT: A function that computes the dot product of two arrays. Used to evaluate the objective and constraint left-hand sides.
Named range: A human-readable alias for a cell or range, used to make formulas self-documenting. For example, naming the range of decision variables x.

4 Mathematical formulation

Spreadsheet layout

A clean layout has four regions:

Coefficient inputs. The objective coefficients c_j, the constraint coefficients a_{ij}, and the constraint bounds b_i. All in one table.
Decision variables. A single row or column of cells containing x₁, …, xₙ. This is what the solver adjusts.
Calculated outputs. The objective value and the constraint left-hand sides, computed from the decision variables and the coefficients.
Documentation. Units, interpretation, and sources for each coefficient.

The recommended arrangement is:

         [ Decision variables: x₁, x₂, …, xₙ ]

Inputs (c_j)     [ c₁, c₂, …, cₙ ]       Objective: SUMPRODUCT(c, x)

Inputs (a_ij)    [ a₁₁ a₁₂ … a₁ₙ ]      Constraint LHS: SUMPRODUCT(a_i, x)
                 [ a₂₁ a₂₂ … a₂ₙ ]
                 [  …   …   …   …  ]

Inputs (b_i)     [ b₁, b₂, …, b_m ]      Constraint RHS

Solver configuration:
  Set objective: Objective cell
  To: Max / Min
  By changing: Decision variable cells
  Subject to: Constraint LHS ≤ / = / ≥ RHS

Configuring the solver

The solver is configured with four pieces of information:

Objective cell. The single cell containing the objective value.
Direction. Max or Min.
Changing cells. The range containing the decision variables.
Constraints. Each constraint is specified as LHS ≤ RHS, LHS = RHS, or LHS ≥ RHS, where LHS is a cell containing a computed constraint left-hand side.

Additional options include:

Simplex LP. The appropriate solving method for linear programs. Other options (GRG Nonlinear, Evolutionary) are for other problem types.
Make unconstrained variables nonnegative. Often useful when the decision variables represent physical quantities.

Reading the solver output

After running, the solver displays a status message:

"Solver found a solution." An optimal solution was found.
"Solver could not find a feasible solution." No point satisfies all constraints. The LP is infeasible.
"Objective cell values do not converge." The objective is unbounded. The LP is unbounded.
"Solver found a solution. All constraints and optimality conditions are satisfied." The solution is optimal.

Figure 6.3. Recommended spreadsheet layout. Only the coefficient region is edited by hand. Decision variables are adjusted by the solver; outputs are computed by formula.

5 Worked example

Implementing the production planning LP

We implement the LP from Lesson 1:

maximize   40 x_A + 30 x_B
subject to 2 x_A + x_B ≤ 100    (labour)
           x_A + 3 x_B ≤ 90     (material)
           x_A ≤ 40             (demand A)
           x_B ≤ 30             (demand B)
           x_A, x_B ≥ 0

Step 1 — Set up the decision variable row.

A1: "x_A"   B1: "x_B"
A2: 0        B2: 0       (initial values, adjusted by solver)

Step 2 — Set up the coefficients.

Row 4:  objective       A4: 40       B4: 30
Row 5:  labour          A5: 2        B5: 1       C5: 100
Row 6:  material        A6: 1        B6: 3       C6: 90
Row 7:  demand A        A7: 1        B7: 0       C7: 40
Row 8:  demand B        A8: 0        B8: 1       C8: 30

Step 3 — Compute the objective.

D4: =SUMPRODUCT(A4:B4, A2:B2)   → objective value

Step 4 — Compute the constraint left-hand sides.

D5: =SUMPRODUCT(A5:B5, A2:B2)   → labour LHS
D6: =SUMPRODUCT(A6:B6, A2:B2)   → material LHS
D7: =SUMPRODUCT(A7:B7, A2:B2)   → demand A LHS
D8: =SUMPRODUCT(A8:B8, A2:B2)   → demand B LHS

Step 5 — Configure the solver.

Set Objective:          D4
To:                     Max
By Changing:            A2:B2
Subject to the constraints:
  D5 ≤ C5              (labour ≤ 100)
  D6 ≤ C6              (material ≤ 90)
  D7 ≤ C7              (demand A ≤ 40)
  D8 ≤ C8              (demand B ≤ 30)
  A2:B2 ≥ 0            (nonnegativity)
Select method:          Simplex LP
Check:                  Make Unconstrained Variables Non-Negative

Step 6 — Run and read the output.

After running, the solver reports "Solver found a solution. All constraints and optimality conditions are satisfied." The values are:

x_A = 42.0
x_B = 16.0
objective = 40 · 42.0 + 30 · 16.0 = 1680 + 480 = 2160

Step 7 — Check the constraints.

Labour:   2·42.0 + 1·16.0 = 100  ≤ 100  (binding)
Material: 1·42.0 + 3·16.0 = 90   ≤ 90   (binding)
Demand A: 42.0                   ≤ 40   (violated!)

The demand A constraint is violated: x_A = 42.0 exceeds the bound of 40. This indicates a solver configuration error, since the solver reported the solution as feasible. Investigation reveals that the constraint was entered with the inequality direction reversed. Correcting it and re-running gives:

x_A = 40.0
x_B = 16.67
objective = 40·40 + 30·16.67 ≈ 2100

Both constraints and the optimum should always be checked after running the solver. The solver's report of feasibility is a check against the constraints it was given, not against the constraints you intended.

6 Verification check

Checks to perform

Verify the objective formula. The objective cell should compute SUMPRODUCT(c, x). Confirm the coefficient row matches the LP statement.
Verify each constraint LHS. Each constraint cell should compute SUMPRODUCT(a_i, x). Confirm each coefficient row matches the LP statement.
Check the constraint directions. Each inequality must have the same direction as in the LP statement. A reversed direction is a common error.
Check the constraint bounds. The RHS of each constraint must match the LP statement, including units.
Test a known feasible solution. Set the decision variables to a known feasible point and confirm the constraint LHS values are within bounds.
Test a known infeasible solution. Set the decision variables to a point that violates a constraint and confirm the violation appears.
Verify the solver output against the constraints. After the solver runs, compute each constraint LHS and compare against its bound. All should be satisfied.

7 Interpretation

The solver's output is a recommendation: the values of the decision variables that maximize or minimize the objective subject to the constraints. Three interpretive points:

The recommendation is conditional on the LP. If the coefficients or constraints are wrong, the recommendation may be wrong.
All constraints should be checked. If a constraint is violated, the solver was misconfigured or the LP is infeasible. A solution that satisfies all the constraints the solver was given but violates a constraint you intended is a configuration error.
The optimum is at a corner. In a typical LP, the optimal solution will have some constraints binding (LHS = RHS) and others slack (LHS < RHS). Which constraints are binding is decision-relevant information, developed further in Unit 6.2.

Reporting the LP solution

A complete report states: the LP formulation, the solver configuration, the optimal values of the decision variables, the optimal objective value, which constraints are binding, and a documentation of the units and interpretation. The reporting template from Module 1 applies.

8 Uncertainty and limitations

The spreadsheet implementation introduces its own sources of uncertainty, in addition to the model uncertainty inherited from the coefficients.

Sources of error in a spreadsheet LP
Source	Effect
Coefficient transcription errors	The spreadsheet LP differs from the intended LP
Solver configuration errors	The solver solves a different LP than the one laid out
Scaling issues	Coefficients of widely different magnitudes reduce numerical accuracy
Nonlinear solving methods	Using GRG Nonlinear when Simplex LP is appropriate can fail to find the true optimum

Always verify the solver's solution against the constraints

The solver returns a solution it believes satisfies the constraints it was given. If the configuration differs from the intent — a reversed inequality, a mistyped bound, a missing constraint — the solver will not detect the error. The verification checks in this lesson catch most such errors.

9 Common implementation errors

Watch for these

Wrong inequality direction. Entering D5 ≥ C5 when the LP says D5 ≤ C5 produces a different LP and a different solution. Always double-check the direction.
Absolute reference errors. When the constraint rows are filled down, the reference to the decision variable row must remain absolute. If it shifts, the SUMPRODUCT computes the wrong value.
Forgetting the nonnegativity constraint. Without it, the solver may propose negative values for physical quantities.
Using the wrong solving method. Using GRG Nonlinear for an LP may find a local optimum that is not the global optimum. Use Simplex LP for linear problems.
Not checking the constraints after solving. The solver's reported status should always be verified against the actual constraint values.
Leaving previous solver settings. When opening a spreadsheet that has been used before, the solver retains the previous configuration. Clear and reconfigure.
Mixing linear and nonlinear terms. A PRODUCT of two decision variable cells is nonlinear and cannot be solved by Simplex LP, even if it looks like a simple formula.

10 Independent modelling task

Your turn

Use the LP from Lesson 1's task.
Lay out the coefficients, decision variables, objective, and constraints in a spreadsheet following the recommended layout.
Add a documentation region: units for each coefficient, source for each value, and interpretation for the objective.
Configure the solver and solve the LP.
Verify the solution against the constraints. Report which constraints are binding and which are slack.
Change one coefficient by 10 percent and re-run. Observe the effect on the optimal solution and the objective value.
Write a one-page report describing the model, the solution, and the change observed in step 6.

This exercise forms part of Laboratory 11 and the implementation component of Assignment 5.

11 Reflection prompt

Reflect before continuing

The solver reports "Solver found a solution" without further qualification. Why is this report insufficient by itself?

Two or three sentences. Consider the difference between satisfying the constraints the solver was given and satisfying the constraints you intended.

12 Lesson summary

Key takeaways

Lay out the LP with separated input, decision variable, and calculation regions. The three-layer discipline of Module 1 applies.
The solver is configured with the objective cell, the direction, the changing cells, and the constraints. Use Simplex LP for linear problems.
Always verify the solution against the constraints. The solver's report is against the constraints it was given.
A binding constraint is one where LHS = RHS. Which constraints bind is decision-relevant information.
Small coefficient changes can change the optimum. Sensitivity analysis quantifies this, and is developed in Unit 6.2.

Coming next

Lesson 3 develops the feasibility, optimality, and model-checking procedures that confirm the solver's answer is the answer to your problem.

§ Self-quiz, practice, and homework

Practice exercises

Set up the production planning LP in a spreadsheet and solve it. Verify that both labour and material constraints are binding at the optimum.
Change the profit on product A from $40 to $50 and re-solve. How does the optimal solution change?
Add a constraint that the total units produced cannot exceed 60. Solve the new LP. Which constraints are binding?
Introduce a deliberate configuration error (reverse an inequality direction) and observe the effect. Correct it and confirm the solution returns to the intended one.

Homework problems

Full spreadsheet implementation. Implement the LP from Lesson 1's homework problem (three products, two resources). Solve it in a spreadsheet. Report the optimal decision variables, the objective value, and which constraints are binding. Include the spreadsheet with documentation. Write a 500-word report.
Configuration experiment. Deliberately introduce three configuration errors (reversed inequality, missing nonnegativity, wrong bound) into your LP and observe the effect of each on the reported solution. Document each error, the effect it had, and how you detected it.
Coefficient sensitivity preview. Change each objective coefficient by 10 percent in turn, re-solving after each change. Record the effect on the optimal solution and objective value. Tabulate the results. This is a preview of the systematic sensitivity analysis developed in Unit 6.2.

Mini project

Build a spreadsheet LP model of a decision problem of your choice. Include: the LP statement, the spreadsheet layout, the solver configuration, the solution, and a documentation sheet. Verify the solution against the constraints. Run a small sensitivity experiment by changing each coefficient by 10 percent. Submit the spreadsheet and a two-page report. This completes Laboratory 11 and feeds Assignment 5.