Linear Programming in Excel or Google Sheets

The spreadsheet is the model; Solver is the engine

Operations leaders live in spreadsheets. Linear programming (LP, optimization with linear objective and linear constraints) is accessible because Excel and Google Sheets can formulate models in plain cells and hand off numerics to a Solver add-in. Lesson 1 taught variables and objectives; Lesson 2 taught feasible regions. This lesson is procedural: build a correct sheet, configure Solver (or OpenSolver / Google alternatives), interpret Optimal vs Infeasible, and avoid setup errors that produce confident nonsense.

The difference between a correct Solver setup and a broken one is often one wrong inequality direction or one cell reference that points at a constant instead of a variable. Because Solver returns a confident "Optimal" label either way, procedural discipline is the safety rail. This lesson is long because the clicks matter: you will repeat them on every mix model you build in your career.

The goal is not memorizing menu clicks alone. The goal is reproducible models an auditor can trace: blue inputs, white formulas, yellow decision cells, documented units, and a screenshot-worthy Solver parameter dialog stored in the workbook notes.

Managers fail when decision cells contain constants, when <= directions are reversed, or when "Assume Linear Model" is unchecked on a linear problem (slower, wrong local minima risk on non-convex setups). They fail when they optimize a range that accidentally includes blank cells treated as zero. This lesson walks FreshPack (three SKUs) end-to-end.

Solver is a black box only if you make it one. Correct setup means every constraint cell is a formula linking decision variables, every coefficient traces to a blue input, and the objective coefficients match finance's contribution margin file. Lesson 5 will read shadow prices from the Sensitivity Report this lesson generates. Unit 6 Lesson 5 will communicate the mix recommendation those prices support.

Sheet architecture and separation of concerns

Use tabs or blocks:

1. Inputs: contributions, hours per unit, RHS capacities, demand caps.
2. Decision variables: production quantities (initial guess 0 or prior week).
3. Calculations: hours used, total contribution, LHS constraint formulas.
4. Solver report area (optional paste of sensitivity output).

Color convention (team standard):

• Blue = hard-coded inputs
• Black formulas = derived
• Yellow = changing cells (variables)

Name ranges (Hours_used, Contrib_total) for readable Solver dialogs.

FreshPack structure:

Hours_used = 0.20*S + 0.50*E + 0.30*D

Contrib_total = 3*S + 5*E + 4*D

Constraints as cell formulas ≤ RHS:

• Hours_used ≤ 160
• S ≤ 400, E ≤ 200, D ≤ 300
• S+E+D ≥ 350 (Surplus form or rearrange: 350 - (S+E+D) ≤ 0)

Non-negativity: check Make Unconstrained Variables Non-Negative or explicit constraints.

Keep a version cell (model v1.3, date) and author in the workbook tab. When the board asks why July mix differed from June, you open the archived file, not memory.

Excel Solver setup (step-by-step)

Enable Solver: File → Options → Add-ins → Manage Excel Add-ins → check Solver Add-in. On Mac, Tools → Excel Add-ins → Solver.

Set Objective: cell Contrib_total (maximize).

By Changing Variable Cells: S:E:D production cells (three single cells or one row).

Constraints Add:

• $Hours_used <= $160
• $S <= 400, $E <= 200, $D <= 300
• $Total_units >= 350 where Total_units = S+E+D

Select a Solving Method: Simplex LP or GRG Nonlinear with Assume Linear Model checked for LP.

Options: tolerance defaults usually fine; increase precision if scaling issues.

Click Solve. Accept solution; optionally keep Sensitivity Report (Lesson 5).

Check solution manually: plug quantities into hours and mins; verify contribution arithmetic.

Common Excel errors:

Solver dialog screenshot checklist for audit: objective address, Max/Min, changing range, each constraint row, Simplex LP selected, non-negative checked. Store screenshot in Appendix_SolverSetup tab.

Sensitivity Report generation: after Solve, choose Sensitivity in Reports box; Excel creates new sheet with shadow prices and reduced costs. Paste values-only to appendix if formulas might break.

Google Sheets and OpenSolver notes

Native Google Sheets lacks built-in LP Solver historically; options include OpenSolver (free, open source, uses CBC/GLPK), third-party add-ons, or export to Excel. Workflow mirrors Excel: same cell layout, OpenSolver menu Model → Solve.

Document engine version in workbook for reproducibility.

Cloud collaboration risk: two editors break named ranges. Lock input blocks.

For large models, Excel desktop often faster and more stable than web add-ins.

Regardless of platform, store PDF of Solver dialog and sensitivity report in appendix when decision is material (Unit 6 Lesson 5 communication).

OpenSolver steps parallel Excel: Define Model → objective cell, variables, constraints direction, solve. CBC engine handles standard LP; note solve time on large MIPs.

Scaling, units, and audit trail

Unit consistency: if one constraint uses minutes and others hours, optimum is wrong.

Scaling: if coefficients range 0.0001 to 1,000,000, Solver may warn. Rescale (tons vs pounds) for numerical stability.

Audit trail checklist:

• Objective cell formula visible
• Each constraint cell formula references variables
• RHS cells are inputs, not formulas tied to variables circularly
• Initial feasible point noted (Solver finds optimum; feasible start helps debugging)

Simplex LP returns global optimum for LP. GRG without linear flag on LP is slower.

After solve, run small perturbation: increase hours RHS by 1, re-solve preview shadow price direction (Lesson 5).

Circular reference trap: never let RHS capacity depend on production cells unless modeling endogenous capacity (rare). RHS should be blue inputs.

Troubleshooting workflow

When Solver misbehaves, follow this sequence:

1. Verify a manual feasible point satisfies all constraints.
2. Check objective formula references yellow cells, not hard-coded optimum from last week.
3. Confirm ≤ versus ≥ directions match English meaning of constraint.
4. Toggle Simplex LP; uncheck Assume Linear Model only if model is truly nonlinear.
5. Read Infeasibility report rows; relax one constraint at a time to locate conflict (Lesson 2).

Document resolution in cell comment so next analyst inherits reasoning.

Reproducibility and handoff

Export values and formulas versions separately before board meetings. Values-only PDF prevents accidental edits during presentation.

Handoff note template: "FreshPack v1.3, Simplex LP, optimal Z=$1,920, hours slack 11, sensitivity sheet tab SensitivityReport1, last solve 2026-07-01."

Complete Solver parameter reference (FreshPack)

Mac Excel Solver dialog matches; OpenSolver uses equivalent Model pane fields.

Integer and binary constraints in Solver

Add constraint S int, E int, D int for whole units. Solve time may increase slightly on three variables. Compare integer optimum to continuous rounded solution; if Z differs by >1%, report integer result to floor.

Binary example: Open_line2 ∈ {0,1} with constraint E ≤ 200*Open_line2 models Entree only if line 2 opened (MIP). Use when fixed opening cost exists.

Step-by-step first solve checklist (FreshPack)

1. Enter contributions 3, 5, 4 and hours/unit 0.20, 0.50, 0.30.
2. Set S, E, D to 0 (yellow).
3. Build Hours_used and Contrib_total formulas.
4. Add Total_units = S+E+D.
5. Open Solver; objective Max Contrib_total.
6. Changing cells S, E, D.
7. Add constraints as listed in parameter table.
8. Choose Simplex LP; non-negative on.
9. Solve; keep Sensitivity Report.
10. Fill checklist: hours 149 ≤ 160 ✓, units 550 ≥ 350 ✓, Z=1920 ✓.

Common Solver dialog mistakes (expanded)

Mistake: Reference entire column B:B as variable. Fix: use single cells B10:D10.

Mistake: Constraint Total_units >= 350 entered with wrong cell reference to formula text. Fix: point at cell containing numeric total.

Mistake: Maximize hours instead of contribution. Fix: read objective cell label aloud in team review.

Protecting and sharing workbooks

Lock blue input cells with worksheet protection; leave yellow cells unlocked. Share read-only link for executives; editable copy for planners only. Accidental RHS edit (160 → 16) has caused real production errors.

Automating re-solve with macros (caution)

VBA macro that re-solves on open is powerful and dangerous. If contributions link to external data feed, macro may solve on half-updated inputs. Prefer manual Solve button with dated log entry unless IT governs feed timing.

OpenSolver installation sketch (Google Sheets)

Extensions → OpenSolver → Install. Create model matching Excel layout. Solve using CBC. If infeasible, OpenSolver log window shows conflicting constraints similar to Excel diagnostics.

Building FreshPack from blank workbook (narrative)

Open blank sheet. Row 7 contributions, row 8 hours per unit, row 10 production variables yellow. Cell B12 =SUMPRODUCT(B8:D8,B10:D10). Cell B14 =SUMPRODUCT(B7:D7,B10:D10). Cell E12 type 160 blue. Add =SUM(B10:D10) for total units, constraint ≥350. Snapshot objective and constraints before Solve. After Solve, paste values to Output_archive tab with timestamp. This narrative is the onboarding doc new analysts follow.

Solver engine comparison

Do not use Evolutionary for FreshPack LP; unnecessary and non-optimal risk.

Error message playbook

"Solver could not find feasible solution" → Lesson 2 infeasibility repair.

"Objective Cell values must converge" → check formulas circular.

"Too many changing cells" → reduce range to actual variables.

Sensitivity report generation walkthrough

After Solve, dialog offers Reports. Select Sensitivity. New sheet appears with:

• Adjustable Cells: reduced costs, allowable increases/decreases on objective coefficients.
• Constraints: shadow prices, RHS allowable ranges.

Rename sheet Sens_2026-07-01. Paste values to PDF for board. Lesson 5 interprets numbers.

Template workbook structure

Tabs: README, Inputs, Model, Solver_Output, Sensitivity, Changelog. README tab documents owners, refresh cadence, and macro policy.

Training exercise for teams

New analyst rebuilds FreshPack from blank sheet in 30 minutes without looking at answer key, then diff-check against gold copy. Certification complete when Z, mix, and slack match within tolerance.

Deep dive: every Solver dialog field

Set Objective must be a formula tied to variables. Constants-only objectives do not move when Solver changes cells.

By Changing Variable Cells lists yellow production cells only. Never include blue RHS or contribution rows.

Subject to the Constraints uses Excel comparisons. Minimum total S+E+D >= 350 is a common reversal error; verify arrow direction matches English.

Select a Solving Method: choose Simplex LP for FreshPack. GRG without linearity is slower and unnecessary.

Make Unconstrained Variables Non-Negative enforces non-negativity globally.

Assume Linear Model applies when using GRG on LP; prefer Simplex LP instead.

Options precision tolerances rarely need tuning if units are consistent (hours, units, dollars).

Deep dive: constraint row construction in words

Hours: "0.20 Salad + 0.50 Entree + 0.30 Dessert hours per unit, summed, ≤ 160."

Spreadsheet: =SUMPRODUCT(B8:D8,B10:D10) <= E12.

Salad cap: B10 <= 400. Repeat per SKU.

Minimum: =SUM(B10:D10) >= 350.

Test with absurd S=1000: hours explode, caps violated. Solver should pull back.

Deep dive: collaboration and version control

Gold workbook lives in SharePoint with version history. Analysts comment changelog tab when contributions update. PDF mix memo references workbook version hash.

Google Sheets OpenSolver users reconcile monthly to Excel gold copy; CBC versus Simplex LP may differ on MIP tolerances.

Solver outcomes reference

Sensitivity report archival policy

Keep last 12 weekly sensitivity PDFs. Compare shadow price trends on hours; rising shadow signals tightening bottleneck approaching.

Solver setup is a professional skill like building a pivot table: learn once per template, reuse weekly. The FreshPack template in this lesson should be copied, not reinvented, at every site that runs a line.

Supplemental narrative: first-week Solver failures

New analyst Maria builds FreshPack. Solver returns Infeasible. She checks manual point: contract minimum 800 units, hours 160. Lesson 2 interval: max feasible units 600. Maria emails sales: reduce minimum to 500 or approve overtime variable. Sales chooses overtime 40 hours added to RHS via OT variable. Model feasible, Solve Optimal, Maria documents repair in changelog. Failure became diagnostic, not embarrassment, because Lesson 3 troubleshooting workflow was followed.

Supplemental narrative: sensitivity report handoff

After Solve, Maria generates Sensitivity Report, pastes to Sens tab, emails PDF to ops with shadow price on hours ($0, slack 11) and binding Salad cap. Ops does not schedule Saturday overtime because shadow zero. Lesson 5 interpretation starts from Maria's archived PDF, not from memory of last week's dialog.

Supplemental narrative: Google Sheets versus Excel

Field office uses Google OpenSolver for visibility. Corporate maintains Excel gold. Monthly reconciliation compares optimum Z and mix; differences trigger coefficient audit. Collaboration without reconciliation breeds two production plans.

Supplemental Solver dialog audit (prose)

Auditor asks Maria to read each FreshPack constraint aloud while pointing at Excel dialog. Hours Hours_used <= 160 ✓. Caps ✓. Minimum Total_units >= 350 ✓. Non-negative ✓. Simplex LP ✓. Changing cells B10:D10 only ✓. Audit completes in four minutes because architecture matches Lesson 3 standard.

Supplemental infeasibility repair log

Changelog tab prevents repeating mistakes.

Closing standards

Copy FreshPack template; do not reinvent. Simplex LP for linear models. Archive sensitivity PDF weekly. Reconcile Google OpenSolver to Excel gold monthly. Read constraint directions aloud before Solve.

Extended review: Solver setup end-to-end

Install Solver add-in. Build FreshPack sheet with yellow S,E,D and formulas Hours_used, Contrib_total, Total_units. Open Solver: Max Contrib_total, change S:E:D, constraints hours, caps, minimum, non-negative, Simplex LP. Solve. Generate Sensitivity Report. Manual check hours 149 ≤ 160, units 550 ≥ 350, Z=3(400)+5(120)+4(30)=1920. Archive PDF. Email ops memo with mix and binding Salad cap.

Repeat weekly with updated contributions only. Time drops to minutes when template stable.

OpenSolver on Google: same steps, verify against Excel monthly.

Troubleshoot Infeasible using Lesson 2 feasible interval before changing Solver engine.

This review is the job aid taped inside analyst cubicle.

Teams that skip sensitivity generation lose shadow price history and cannot explain overtime decisions six weeks later during audit.

Integration narrative: corporate analytics standard

Corporate publishes LP_Standard_v2.pdf: colors, tab names, Simplex LP default, sensitivity archive naming Sens_{site}_{date}.pdf, changelog required, Google Sheets reconciliation monthly, troubleshooting flowchart for Infeasible. Sites adopt FreshPack clone; only coefficients and RHS differ. Internal audit samples three sites quarterly: verify constraint algebra, compare shadow trends, confirm memos reference version IDs. Standard turns Lesson 3 from individual craft into organizational capability.

Analysts should be able to rebuild FreshPack Solver dialog from memory after one week of practice.

Additional examples: Solver outcomes explained

Optimal: constraints satisfied, objective at global best for LP. Infeasible: repair per Lesson 2 before changing engine. Unbounded: add missing cap. Not solved: check circular references. After Optimal, always generate Sensitivity Report; without it Lesson 5 interpretation lacks dual values. Store dialog screenshot with date in Appendix_SolverSetup tab for auditors who do not have Solver installed on their laptops.

Solver procedural fluency is the bottleneck for most teams, not linear algebra. Repeat FreshPack setup until muscle memory.

Document every constraint in English beside the Excel formula so new hires never reverse a ≥ minimum by accident.

Lesson 3 mastery check: rebuild FreshPack Solver dialog, solve Simplex LP, archive sensitivity PDF, pass manual Z and hours check without looking at answer key.

Treat the Solver parameter dialog as part of the model deliverable, not a disposable step. Screenshots age better than memory when turnover hits the analytics team.

Unit 7 bridge paragraph

Solver is how MBA-scale models reach decisions before close of business. The competitive alternative is gut feel or politics. Procedural rigor (colors, checks, Simplex LP, sensitivity archive) is what makes optimization adoptable outside the analytics department.

Run Solver only after manual feasible point passes; skipping the check wastes more time fixing Infeasible than the check takes.

Keep a printed Solver dialog screenshot in the plant binder next to the lockout tagout sheet: serious operations treat optimization like safety procedure.

Simplex LP, sensitivity PDF, manual check: three outputs every solve, no exceptions.

When IT disables macros, Solver still works; do not let macro fear block basic LP adoption.

Lesson 3 completes when any certified analyst reproduces FreshPack optimum and sensitivity PDF from a blank workbook in under forty-five minutes.

Solver certification should be as common as pivot table certification in corporate analytics hiring.

FreshPack template, Solver dialog, sensitivity PDF, and manual check form a four-piece deliverable every certified analyst ships weekly.

Solver quick reference card (text)

Max or Min objective cell. Change yellow variables only. Constraints reference formula cells. Simplex LP. Non-negative on. Solve. Sensitivity Report. Manual Z and hours check. Archive PDF. Email ops memo with mix and binding caps. Version ID in footer.

OpenSolver on Google Sheets follows the same quick reference; only the menu path differs.

Copy the FreshPack template before inventing a new layout for every plant. Standard workbook layout beats clever one-off architecture every time for audit and turnover. Post-solve checklist belongs in the README tab, not in tribal chat history.

Document version IDs on every workbook (FreshPack_v2026-07-13) so sensitivity PDFs archived in email match the coefficients finance approved. Mismatch between PDF and live sheet is a common audit finding when commodity costs update midweek without re-solve.

Train backup analysts on the same template: if only one person knows where the Solver dialog lives, vacation becomes a production planning risk. README tab should list certified users and last successful solve timestamp.

When IT pushes Microsoft 365 web Excel, confirm whether Solver add-in is supported on your tenant before migrating gold workbooks; desktop Excel often remains the solve environment while web is view-only.

Quarterly refresh: re-read constraint directions aloud as a team; reversed inequalities survive copy-paste errors longer than anyone admits. Pair the refresh with a five-minute manual feasible-point drill on every FreshPack clone.

Part A: Optimal solution (Solver output)

Optimal production (typical LP solution):

Check hours: 0.20(400)+0.50(120)+0.30(30) = 80+60+9 = 149 ≤ 160, slack 11 hours.

Total units: 400+120+30 = 550 ≥ 350 ✓

Contribution: 3(400)+5(120)+4(30) = 1200+600+120 = $1,920

(Different corner than two-variable slice because Dessert and contract minimum included.)

Part B: Binding status

Hours not binding (slack 11). Which binds? S at cap 400 likely; verify E and D caps slack.

Part C: Solver dialog capture (documentation)

Objective: Max $Contrib_total

Variables: $S:$D

Constraints listed as above; Non-negative; Simplex LP.

Part D: Managerial read

Optimal mix pushes Salad to demand cap; Entree and Dessert fill contract and margin given remaining hours. Marketing should not assume Entree unlimited; capacity and mins shape mix.

Minimize 40*Search + 25*Social + 55*Events

Variables: leads per channel.

Constraints:

• Search <= 6000
• Social <= 8000
• Events <= 3000
• Search+Social+Events >= 10000

Solver: Min objective, changing lead cells, Simplex LP.

Solution: Social 8000, Search 2000, Events 0, cost $280,000 ✓

Sensitivity report preview: binding sum ≥ constraint and Social cap; Events slack.

Part D: Managerial read and sensitivity preview

CFO approves $280k spend. If Search cap drops to 1,500 leads, model becomes infeasible (Social max 8000 + Search 1500 = 9500 < 10000). Shadow price on Search cap becomes positive when cap binds. Lesson 5: compare shadow to cost per lead from Search channel before paying premium for extra inventory leads.

Check: 8000+2000=10000 ✓; cost 280k ✓

Build Solver setup list (not full sheet) for: Max 10A + 12B s.t. A+B<=100, 2A+B<=160, A,B>=0.

1. Objective cell formula?
2. Changing cells?
3. Three constraint rows?

Solution

1. =10*A_cell + 12*B_cell Max

2. A_cell, B_cell

3. A+B <= 100; 2A+B <= 160; non-neg (option or explicit)

Optimum by Lesson 2 corners: (60,40) Z=1080 check 60+40=100 bind; 120+40=160 bind ✓

Solver returns Infeasible on FreshPack when contract minimum is 800 units. What are two repair options?

Solution

(1) Reduce contract minimum toward feasible total (policy negotiation). (2) Add overtime hours variable with extra cost column expanding hours RHS. (3) Extend to two-week horizon doubling hours RHS. Pick based on managerial feasibility.

Adopt a gold standard FreshPack workbook as template. All plant mix models copy tab structure, color rules, and Solver dialog pattern. New sites only change input rows, not architecture.

Onboarding checklist: enable Solver, rebuild FreshPack in 30 minutes, explain each constraint in English, generate sensitivity report, interpret one shadow price (forward to Lesson 5).

Mac versus Windows menu paths differ; document both in README tab.

Google Sheets teams maintain parallel Excel gold copy for large solves.

Never email Solver results without attachment version ID; email threads lose context.

Quarterly audit: random plant resubmit workbook; central analytics verifies constraint algebra.

Complete constraint translation table (FreshPack)

Translate each English sentence before touching Solver. Reversed ≥ on minimum is the most common FreshPack bug.

Mac Excel Solver differences

Solver on Mac may show Plot options; ignore for LP. Add-in location differs; IT install script should be documented for new laptops.

Batch re-solve with scenarios

Use Scenario Manager or Python script to re-solve for Downside caps; paste results table. Lesson 5 interprets mix shifts across scenarios.

Key takeaways

• Separate inputs, decision variables, and constraint formulas on the sheet.
• Excel Solver: set objective, changing cells, constraints, Simplex LP, non-negative.
• Google Sheets typically uses OpenSolver with the same layout discipline.
• Always manually verify binding constraints and arithmetic after Solve.
• Document Solver settings and sensitivity output for audit and communication.

1. Build a three-variable LP for a simple mix at your firm; solve and paste sensitivity report.
2. Which constraint has slack in your optimum?
3. Continue to Lesson 4: Resource Allocation and Product-Mix Models.