LINEAR PROGRAMMING MODULE | Excel Solver Applications

LINEAR PROGRAMMING
with Excel Solver Applications
────────────────────────────────────
A Comprehensive Learning Module
Operations Research | Business Mathematics | Decision Science

Operations Research | Page 1

LINEAR PROGRAMMING MODULE | Excel Solver Applications

MODULE OVERVIEW

Subject Operations Research / Business Mathematics

Module Title Linear Programming with Excel Solver Applications

Duration 6–8 hours (can be split into multiple sessions)

Target Students College students — Business, Engineering, Management, Economics

Prerequisites Basic algebra, familiarity with Microsoft Excel

Learning Objectives
By the end of this module, students will be able to:
• Define linear programming and identify its core components
• Formulate real-world optimization problems as LP models
• Solve LP problems using the graphical method (for two variables)
• Set up and solve LP problems using Excel Solver
• Interpret Solver results and perform sensitivity analysis
• Apply LP to practical business and engineering scenarios

Operations Research | Page 2

LINEAR PROGRAMMING MODULE | Excel Solver Applications

UNIT 1: Introduction to Linear Programming

1.1 What is Linear Programming?

Linear Programming (LP) is a mathematical method used to find the best possible outcome (maximum
profit or minimum cost) in a mathematical model whose requirements are represented by linear
relationships. It is one of the most widely used tools in Operations Research.

📌 Key Definition
Linear Programming is an optimization technique that seeks to maximize or minimize a linear
objective

function subject to a set of linear constraints and non-negativity conditions.

The word 'linear' means all relationships in the model are straight-line (first-degree) equations

or inequalities. The word 'programming' means planning or scheduling.

1.2 Structure of an LP Model

Every LP model has exactly three components:

Component Definition Example

Unknowns to be determined x₁ = units of Product A; x₂ = units

Decision Variables
(what we control) of Product B

A linear expression to be
Objective Function Maximize Z = 5x₁ + 4x₂
maximized or minimized

Linear inequalities/equations 2x₁ + x₂ ≤ 100 (Labor hours

Constraints
limiting decision variables available)

Decision variables cannot be

Non-negativity Conditions x₁ ≥ 0, x₂ ≥ 0
negative

1.3 General LP Formulation

Standard form of a Linear Programming problem:

Operations Research | Page 3

LINEAR PROGRAMMING MODULE | Excel Solver Applications

Maximize or Minimize: Z = c₁x₁ + c₂x₂ + ... + cₙxₙ

Subject to:
a₁₁x₁ + a₁₂x₂ + ... + a₁ₙxₙ ≤ b₁
a₂₁x₁ + a₂₂x₂ + ... + a₂ₙxₙ ≤ b₂
... ...
aₘ₁x₁ + aₘ₂x₂ + ... + aₘₙxₙ ≤ bₘ
x₁, x₂, ..., xₙ ≥ 0

Where: cⱼ = objective function coefficients | aᵢⱼ = constraint coefficients | bᵢ = right-hand side values

Operations Research | Page 4

LINEAR PROGRAMMING MODULE | Excel Solver Applications

UNIT 2: Formulating LP Problems

2.1 Steps in LP Formulation

Identify the Decision Variables

STEP Ask: What quantities do we need to determine?

1 Define each variable clearly (e.g., x₁ = number of units of Product A produced per
day)

Assign symbols: x₁, x₂, ... or descriptive names

Define the Objective Function

STEP Ask: What are we trying to maximize or minimize?
2 Express as a linear combination of the decision variables

Example: Maximize Z = 5x₁ + 4x₂ (where 5 and 4 are profit per unit)

Identify and Write the Constraints

STEP Ask: What limitations or requirements must be satisfied?
3 Convert each restriction into a linear inequality or equation

Common types: resource limits (≤), minimum requirements (≥), exact amounts (=)

Add Non-Negativity Conditions

STEP

4 Unless explicitly stated otherwise, all variables must be ≥ 0

This reflects real-world logic: you cannot produce negative quantities

2.2 Worked Example: Product Mix Problem

📝 Problem Statement
A furniture company makes two products: Chairs (x₁) and Tables (x₂).

Each Chair requires 2 hours of carpentry and 1 hour of finishing.

Each Table requires 3 hours of carpentry and 2 hours of finishing.

Operations Research | Page 5

LINEAR PROGRAMMING MODULE | Excel Solver Applications

Available per week: 120 hours of carpentry, 80 hours of finishing.

Profit: Php 500 per Chair, Php 800 per Table.

How many of each should be produced to MAXIMIZE total profit?

Solution Formulation:
Step 1 – Decision Variables:
• x₁ = number of Chairs produced per week
• x₂ = number of Tables produced per week

Step 2 – Objective Function:

Maximize Z = 500x₁ + 800x₂

Step 3 – Constraints:
2x₁ + 3x₂ ≤ 120 (Carpentry hours)
x₁ + 2x₂ ≤ 80 (Finishing hours)
x₁, x₂ ≥ 0 (Non-negativity)

Resource Chair (x₁) Table (x₂) Available

Carpentry
2 3 120
(hrs)

Finishing (hrs) 1 2 80

Profit (Php) 500 800 Maximize Z

Operations Research | Page 6

LINEAR PROGRAMMING MODULE | Excel Solver Applications

UNIT 3: Excel Solver — Step-by-Step Guide

3.1 Enabling the Solver Add-in

Excel Solver is a built-in add-in but must be activated before first use:

# Action

1 Click the File tab in the Excel ribbon

2 Select Options from the left-hand menu

3 Click Add-Ins in the Excel Options dialog

4 At the bottom, set Manage: to Excel Add-ins, then click Go...

5 Check the Solver Add-in checkbox and click OK

6 The Solver button now appears on the Data tab, in the Analysis group

3.2 Setting Up the LP Model in Excel

Before using Solver, you must build a spreadsheet model. Use the Furniture Problem as an example:

Spreadsheet Layout (recommended):

Cell Label / Content Excel Entry Value Notes

B2 Decision Variable label Chairs (x₁) Label only

C2 Decision Variable label Tables (x₂) Label only

Solver will change

B3 Initial value of x₁ 0 0
this

Solver will change

C3 Initial value of x₂ 0 0
this

B5 Objective Function label Total Profit (Z) Label only

Objective Function
C5 =500*B3 + 800*C3 0 Maximize this cell
formula

Carpentry constraint
B8 Carpentry Used
label

Carpentry constraint
C8 =2*B3 + 3*C3 0
formula

Operations Research | Page 7

LINEAR PROGRAMMING MODULE | Excel Solver Applications

D8 Constraint sign <=

E8 RHS value 120 120 Available hours

B9 Finishing constraint label Finishing Used

Finishing constraint
C9 =1*B3 + 2*C3 0
formula

D9 Constraint sign <=

E9 RHS value 80 80 Available hours

3.3 Running Excel Solver — Detailed Steps

Open the Solver Dialog

STEP Go to the Data tab in the Excel ribbon
1 Click Solver in the Analysis group (far right)

The Solver Parameters dialog box will open

Set the Objective Cell

STEP In the 'Set Objective' field, click the cell icon and select C5 (your Total Profit formula
cell)
2 Under 'To:', select Max (to maximize profit)

For a minimization problem, select Min instead

Define Changing Variable Cells

STEP Click in the 'By Changing Variable Cells' field
3 Select the range B3:C3 (the cells containing your decision variables x₁ and x₂)

These are the cells Solver will adjust to find the optimal solution

STEP Add Constraints

4 Click the Add button next to 'Subject to the Constraints'

In 'Cell Reference', select C8 (Carpentry Used)

Set the relationship dropdown to <=

In 'Constraint', select or type E8 (value 120)

Operations Research | Page 8

LINEAR PROGRAMMING MODULE | Excel Solver Applications

Click Add to add more constraints (do not click OK yet)

Repeat for C9 <= E9 (Finishing constraint)

Click OK when all constraints are added

Set Non-Negativity and Solving Method

STEP Check the box: 'Make Unconstrained Variables Non-Negative' (this adds x₁, x₂ ≥ 0)
5 In 'Select a Solving Method', choose Simplex LP (for linear problems)

Other options: GRG Nonlinear (for non-linear), Evolutionary (for integer/discrete)

Solve and Interpret Results

Click the Solve button
STEP
Solver will display: 'Solver found a solution. All constraints and optimality conditions
6 are satisfied.'

Select 'Keep Solver Solution' and click OK

Your spreadsheet now shows the optimal values in B3 and C3, and optimal Z in C5

Generate Sensitivity Report (Optional but Important)

STEP Before clicking OK in the results dialog, select 'Sensitivity' in the Reports section

7 Click OK — a new sheet named 'Sensitivity Report' will be created

This report shows Allowable Increase/Decrease for objective coefficients and RHS
values

✅ Expected Result for Furniture Problem

Optimal solution: x₁ (Chairs) = 40 units, x₂ (Tables) = 13.33 units

Maximum Profit: Z = Php 500(40) + Php 800(13.33) = Php 30,667

Note: If integer solutions are required, add a constraint that x₁, x₂ are integers

and change the solving method to 'Evolutionary' or use integer programming (IP).

Operations Research | Page 9

LINEAR PROGRAMMING MODULE | Excel Solver Applications

UNIT 4: Sensitivity Analysis

4.1 What is Sensitivity Analysis?

Sensitivity analysis examines how the optimal solution changes when the model's parameters change.
It answers: How robust is our optimal solution? Which resources are truly limiting?

Term Meaning Practical Implication

A constraint where all available This resource is the bottleneck;

Binding Constraint
resource is used (slack = 0) more of it increases profit

A constraint where some resource Getting more of this resource does

Non-Binding Constraint
is unused (slack > 0) NOT improve the solution

Shadow Price (Dual Increase in Z per unit increase in a Tells management how much to
Value) binding constraint's RHS pay for additional resources

Range of RHS or coefficient

Quantifies how much parameters
Allowable Range values where the current basis
can change without re-solving
stays optimal

4.2 Reading the Excel Sensitivity Report

After running Solver with the Sensitivity Report option, Excel creates a report with two tables:
• Variable Cells Table: Shows final values, reduced costs, and allowable ranges for objective
function coefficients
• Constraints Table: Shows shadow prices, slack/surplus values, and allowable ranges for RHS
values

📊 Interpreting Shadow Prices — Furniture Example

If the Sensitivity Report shows Shadow Price = 160 for the Carpentry constraint:

→ For every additional hour of carpentry available, profit increases by Php 160

→ It is worth paying up to Php 160 per overtime hour for carpentry workers

If Shadow Price = 0 for a constraint:

→ That resource has a surplus; acquiring more of it will NOT help

Operations Research | Page 10

LINEAR PROGRAMMING MODULE | Excel Solver Applications

SEATWORK 1: LP Formulation Practice

📋 Instructions
Read each problem carefully. Identify the decision variables, write the objective function,

and list all constraints including non-negativity conditions.

Do NOT solve — only formulate the LP model. Time allowed: 25 minutes.

Problem 1 — Bakery Production (12 points)

A bakery produces two items: Pandesal (x₁) and Ensaymada (x₂). Each Pandesal requires 0.5 kg of
flour and 0.1 kg of butter. Each Ensaymada requires 0.3 kg of flour and 0.2 kg of butter. The bakery has
60 kg of flour and 20 kg of butter available daily. Profit per piece: Php 8 for Pandesal, Php 15 for
Ensaymada. Formulate the LP model.

Answer Guide:
Decision Variables:
x₁ = x₂ =
___________________________________________ ___________________________________
____ ___

Objective Function:
Maximize / Minimize Z = _________________________________

Constraints:
Constraint 1 (Flour): _____________________________________

Constraint 2 (Butter): _____________________________________

Non-negativity: _____________________________________

Problem 2 — Manufacturing Mix (12 points)

A factory makes two models of electric fans: Standard (x₁) and Deluxe (x₂). Each Standard fan needs
2 hours of assembly and 1 hour of testing. Each Deluxe fan needs 3 hours of assembly and 2 hours of
testing. Available: 180 hours assembly, 100 hours testing per week. At least 20 Standard fans must be
produced to meet a contract. Selling price: Php 1,200 (Standard) and Php 1,800 (Deluxe). Formulate
the LP to maximize revenue.

Operations Research | Page 11

LINEAR PROGRAMMING MODULE | Excel Solver Applications

Answer Guide:
Decision Variables:
x₁ = x₂ =
_______________________________________ _______________________________________
________ ________

Objective Function:
Maximize Z = _________________________________

Constraints (list all 4 including non-negativity):

1. ______________________________________________________

2. ______________________________________________________

3. ______________________________________________________

4. ______________________________________________________

Problem 3 — Diet / Blending Problem (6 points)

A nutritionist is designing a diet using two food sources: Rice (x₁, per cup) and Beans (x₂, per cup).
The diet must provide at least 2,000 calories and at least 50 g of protein per day. Rice provides 200 cal
and 4 g protein per cup. Beans provide 150 cal and 10 g protein per cup. Cost: Php 12 (rice) and Php
20 (beans) per cup. Formulate the LP to minimize daily food cost.

Decision Variables: x₁ = ___________ x₂ = ___________

Objective: __________ Z = _______________________

Calorie constraint: ________________________________________

Protein constraint: ________________________________________

Non-negativity: ________________________________________

Operations Research | Page 12

LINEAR PROGRAMMING MODULE | Excel Solver Applications

SEATWORK 2: Excel Solver Application

📋 Instructions
Use Microsoft Excel and the Solver Add-in to solve the following problems.

Submit your Excel file AND a written interpretation of the results.

Take a screenshot of your Solver setup (parameters dialog) and paste it in your answer sheet.

Time allowed: 40 minutes.

Problem A — Production Planning (20 points)

A garment factory produces two types of shirts: Basic (B) and Premium (P). The table below shows the
resource requirements per shirt and the weekly resource availability:

Resource Basic Shirt Premium Shirt Weekly Limit

Cutting (hours) 1.5 2.5 300 hrs

Sewing (hours) 2.0 3.0 360 hrs

Finishing (hours) 0.5 1.0 100 hrs

Profit per unit Php 250 Php 450 MAXIMIZE

Tasks:
1. Formulate the LP model (decision variables, objective function, all constraints)
2. Set up the Excel spreadsheet model as taught (label all cells clearly)
3. Run Solver using Simplex LP method
4. Record the optimal solution: optimal x₁ = _____ , x₂ = _____ , Z = Php _____
5. Generate the Sensitivity Report. Identify which constraints are binding.
6. Interpret the shadow price of any ONE binding constraint in 2–3 sentences

Answer Sheet:
LP Formulation:

Objective Function: Z = ___________________________________________

Constraint 1: _____________________________________________________

Operations Research | Page 13

LINEAR PROGRAMMING MODULE | Excel Solver Applications

Constraint 2: _____________________________________________________

Constraint 3: _____________________________________________________

Non-negativity: ____________________________________________________

Optimal Solution from Solver: B = _______ P = _______ Z = Php _______

Binding Constraints (from Sensitivity Report): ___________________________

Shadow Price Interpretation:

________________________________________________________________________________
________________________________________________________________________________
________________________________________________________________________________

Problem B — Transportation/Cost Minimization (10 points)

A logistics manager wants to minimize the weekly operating cost of delivery routes. Two types of
vehicles are used: Vans (V) and Trucks (T). Each Van can deliver to 5 locations and consumes 8 liters
of fuel per trip. Each Truck can deliver to 12 locations and consumes 15 liters of fuel per trip. At least
120 delivery locations must be covered weekly. Fuel budget limit: 200 liters/week. Cost per vehicle per
trip: Php 2,500 (Van) and Php 4,000 (Truck). Formulate and solve using Excel Solver.

LP Formulation (write here):

Decision Variables: V = _____________ T = _____________

Objective Function (Minimize): Z = ___________________________

Constraint 1 (Coverage): _____________________________________

Constraint 2 (Fuel): ________________________________________

Solver Result: V = _______ T = _______ Min Cost = Php _______

Operations Research | Page 14

LINEAR PROGRAMMING MODULE | Excel Solver Applications

UNIT 5: Practice Problems with Excel Applications

5.1 Practice Set A — Formulation Only

Formulate (do not solve) each of the following LP problems:

Practice Problem 1 — Investment Portfolio

An investor has Php 500,000 to invest in two assets: Stocks (x₁) and Bonds (x₂). Expected annual
return: 12% for Stocks, 7% for Bonds. At most 60% of the investment may go to Stocks. At least 20%
must go to Bonds. Formulate an LP to maximize total expected return.

Practice Problem 2 — Staffing Problem

A BPO company operates two shifts: Day (x₁) and Night (x₂). Each day-shift agent handles 50 calls at
a cost of Php 400/day. Each night-shift agent handles 40 calls at a cost of Php 500/day. At least 4,000
calls must be handled daily. Night shift agents cannot exceed 60% of total agents. Formulate an LP to
minimize total daily staffing cost.

Practice Problem 3 — Agricultural Planning

A farmer has 200 hectares to plant with Rice (x₁) and Corn (x₂). Rice requires 4 work-days and Php
8,000 per hectare. Corn requires 3 work-days and Php 6,000 per hectare. Available: 700 work-days
and Php 1,400,000 budget. Expected revenue: Php 25,000/ha Rice, Php 18,000/ha Corn. Formulate to
maximize total revenue.

5.2 Practice Set B — Solve with Excel Solver

Excel Instructions
For each problem: (1) Set up the spreadsheet model with labeled cells,

(2) Run Solver with Simplex LP, (3) Record the optimal solution,

(4) Generate the Sensitivity Report and identify binding constraints.

Excel Practice 1 — Advertising Budget Allocation

A company has Php 600,000 for advertising across TV (T) and Social Media (S). TV reaches 50,000
viewers per Php 1,000 spent; Social Media reaches 80,000 viewers per Php 1,000. At least Php
200,000 must go to TV. At most Php 400,000 may go to Social Media. Maximize total viewer reach.

Operations Research | Page 15

LINEAR PROGRAMMING MODULE | Excel Solver Applications

Optimal T (Php): ________________________

Optimal S (Php): ________________________

Max Viewer Reach: ________________________

Binding Constraint(s): ________________________

Excel Practice 2 — Supply Chain / Shipping Cost

A warehouse ships products to two retail branches. Branch A requires at least 200 units/week; Branch
B requires at least 150 units/week. Each unit shipped to A costs Php 35 and to B costs Php 50. The
warehouse can ship at most 500 units total per week. Minimize weekly shipping cost. Let x₁ = units to
A, x₂ = units to B.

Optimal units to A: ________________________

Optimal units to B: ________________________

Minimum Shipping Cost: Php _______________

Shadow Price of Branch A req.: ________________________

Excel Practice 3 — Multi-Resource Blending

A chemical plant blends two raw materials (R₁ and R₂) to produce a compound. The compound must
contain at least 30% of Component A and at most 50% of Component B. R₁ has 40% A and 30% B per
liter. R₂ has 20% A and 60% B per liter. Cost: Php 180/liter (R₁), Php 120/liter (R₂). At least 100 liters
of compound must be produced. Minimize cost.

Optimal R₁ (liters): ________________________

Optimal R₂ (liters): ________________________

Minimum Cost: Php _______________

Is Blending constraint binding?: Yes / No

Operations Research | Page 16

LINEAR PROGRAMMING MODULE | Excel Solver Applications

UNIT 6: Answer Key & Model Solutions

⚠️ Instructor Note
This section is for instructor/facilitator reference.

Solutions for Seatwork and Practice problems are provided to guide grading and discussion.

Encourage students to verify their Excel Solver output against these answers.

Seatwork 1 — Answer Key

Problem 1 (Bakery):
Decision variables: x₁ = pieces of Pandesal, x₂ = pieces of Ensaymada
Maximize Z = 8x₁ + 15x₂
Subject to: 0.5x₁ + 0.3x₂ ≤ 60 (flour), 0.1x₁ + 0.2x₂ ≤ 20 (butter), x₁, x₂ ≥ 0

Problem 2 (Electric Fans):

Decision variables: x₁ = Standard fans, x₂ = Deluxe fans
Maximize Z = 1200x₁ + 1800x₂
Subject to: 2x₁ + 3x₂ ≤ 180 (assembly), x₁ + 2x₂ ≤ 100 (testing), x₁ ≥ 20 (contract), x₁, x₂ ≥ 0

Problem 3 (Diet):
Decision variables: x₁ = cups of Rice, x₂ = cups of Beans
Minimize Z = 12x₁ + 20x₂
Subject to: 200x₁ + 150x₂ ≥ 2000 (calories), 4x₁ + 10x₂ ≥ 50 (protein), x₁, x₂ ≥ 0

Seatwork 2 — Model Solver Results

Problem A (Shirts):
Optimal: Basic (B) ≈ 80 shirts, Premium (P) ≈ 60 shirts, Max Profit Z = Php 47,000
Binding constraints: Sewing and Finishing hours (check with Sensitivity Report)

Problem B (Logistics):
Minimize Z = 2500V + 4000T
Constraints: 5V + 12T ≥ 120 (coverage), 8V + 15T ≤ 200 (fuel), V, T ≥ 0

Operations Research | Page 17

LINEAR PROGRAMMING MODULE | Excel Solver Applications

Solver result may vary; encourage students to check feasibility and interpret the trade-off

Practice Set B — Sample Solver Results

Problem Optimal x₁ Optimal x₂ Optimal Z

Excel Practice 1 (Advertising) T = 200,000 S = 400,000 42,000,000 viewers

Excel Practice 2 (Shipping) A = 200 units B = 150 units Php 14,500

Excel Practice 3 (Blending) R₁ ≈ varies R₂ ≈ varies ~Php 15,000

Quick Reference: Excel Solver Checklist

✓ Solver Add-in is enabled (Data tab → Analysis group)

☐ Decision variable cells are initialized to 0

☐ Objective function cell contains a formula referencing variable cells

☐ Constraint LHS cells contain formulas (not hardcoded numbers)

☐ RHS values are in separate cells (not typed directly in Solver dialog)

☐ 'Make Unconstrained Variables Non-Negative' is checked

☐ Solving Method is set to 'Simplex LP' for linear problems

☐ After solving: verify all constraints are satisfied (no violations)

☐ For sensitivity analysis: check the Sensitivity Report box before clicking OK

Operations Research | Page 18

LINEAR PROGRAMMING MODULE | Excel Solver Applications

GLOSSARY OF KEY TERMS

Term Definition

A constraint that holds exactly at equality at the optimal solution; its

Binding Constraint
slack/surplus is zero.

A vertex of the feasible region; the optimal solution of an LP always

Corner Point
occurs at a corner point.

An unknown quantity that the decision-maker controls; the LP

Decision Variable
determines its optimal value.

The set of all points (solutions) that satisfy all constraints

Feasible Region
simultaneously.

Feasible Solution Any solution that satisfies all constraints; not necessarily optimal.

Infeasible Problem An LP with no feasible region — the constraints are contradictory.

Objective Function The linear expression to be maximized or minimized.

The feasible solution that achieves the best value of the objective
Optimal Solution
function.

The rate of change in the optimal objective value per unit increase in a
Shadow Price
RHS constant.

An algorithm that moves along the corner points of the feasible region
Simplex Method
toward the optimal solution.

A variable added to a ≤ constraint to convert it to equality; represents

Slack Variable
unused resource.

An Excel add-in that solves optimization problems using LP, nonlinear,

Solver (Excel)
or evolutionary methods.

A variable subtracted from a ≥ constraint to convert it to equality;

Surplus Variable
represents excess above minimum.

An LP where the objective can be improved indefinitely — usually a

Unbounded Problem
formulation error.

Module Summary
This module covered the complete workflow for solving Linear Programming problems using Excel
Solver:
• Understanding the structure of LP models: decision variables, objective function, and
constraints
• Formulating real-world problems as LP models using a systematic 4-step process

Operations Research | Page 19

LINEAR PROGRAMMING MODULE | Excel Solver Applications

• Setting up Excel spreadsheet models with proper cell references and formulas
• Running Excel Solver with the Simplex LP method for linear problems
• Interpreting optimal solutions and sensitivity analysis reports
• Applying LP to diverse contexts: production, logistics, finance, diet, and advertising

🎯 Key Takeaway
Linear Programming is a powerful decision-making tool used in business, engineering, and
government.

Excel Solver makes it accessible — you do not need to know the Simplex algorithm manually.

The real skill is in FORMULATION: correctly identifying variables, the objective, and constraints.

Sensitivity analysis transforms a single answer into a rich managerial insight.

Operations Research | Page 20