Resource Allocation Linear Programming Model: How It Can Be Solved in Excel and Python

📌 Full technical details and complete code examples are available on Medium: https://medium.com/@natacherchum/resource-allocation-linear-programming-model-01337b658653

Resource allocation problems arise whenever limited resources must be distributed in the most efficient way. Typical objectives include maximizing profit or minimizing cost, subject to technological, financial, or capacity constraints.

When both the objective function and all constraints are linear, such problems can be formulated as a linear programming (LP) problem.

Problem Description

Consider a manufacturing enterprise that produces three products using three limited resources.

For each product:

• the required amount of each resource is known,
• the unit profit is known.

The objective is to determine an optimal production plan that maximizes total profit without exceeding the available amounts of resources.

Mathematical Model

Let x₁, x₂, x₃ denote the production levels of Products 1, 2, and 3, respectively.

Objective function

Maximize

F = 5x₁ + 4x₂ + 3x₃

Constraints

6x₁ + 4x₂ + 2x₃ ≤ 24 (Resource 1)

x₁ + 2x₂ + x₃ ≤ 6  (Resource 2)

x₁ + x₂ + x₃ ≤ 10 (Resource 3)

x₁, x₂, x₃ ≥ 0

This formulation represents a standard LP structure with decision variables, an objective function, and resource constraints.

Beyond the Optimal Solution: Sensitivity Analysis

Obtaining an optimal solution is only the first step. In practice, decision-makers are often more interested in how robust this solution is with respect to parameter changes.

Sensitivity (post-optimality) analysis allows us to examine:

• Dual values (shadow prices) of constraints, showing how the objective value changes when a resource is increased by one unit.
• Slack or surplus values, indicating whether a constraint is binding.
• Reduced costs of decision variables, reflecting how much an objective coefficient must improve for a variable to become positive.
• Ranges of feasibility for the right-hand sides of constraints.
• Ranges of optimality for objective function coefficients.

Since the choice of software directly affects the depth of analysis, the table below summarizes how Excel and Python-based tools differ in their support for sensitivity analysis in linear programming.

Solving the Model in Excel

Microsoft Excel Solver provides a user-friendly environment for solving LP problems.

After defining:

• decision variables,
• the objective function,
• and constraints,

Solver finds the optimal solution and generates:

• an Answer Report, containing optimal variable values, the objective value, and slack values;
• a Sensitivity Report, which includes shadow prices, slack values, reduced costs, and allowable ranges.

Excel Solver is particularly useful when automatic and complete sensitivity analysis is required without additional programming.

Solving the Model in Python

SciPy (scipy.optimize.linprog)

The LP model can be solved using the linprog function from scipy.optimize.

• The objective function coefficients are negated to convert maximization into minimization.
• Constraints are defined in matrix form.
• Non-negativity bounds are specified explicitly.
• The HiGHS solver is used due to its robustness and efficiency.

The result object provides:

• optimal values of decision variables,
• the objective function value,
• constraint slacks,
• dual variables and reduced costs.

However, automatic sensitivity ranges are not available and must be analyzed manually.

PuLP

PuLP is a high-level Python library for modeling LP problems in a readable, algebraic form.

Using PuLP:

• decision variables are explicitly defined,
• the objective function and constraints are added to the model,
• the model is solved using a default solver (typically CBC).

PuLP allows access to:

• optimal variable values,
• constraint slacks,
• dual values and reduced costs (depending on the solver).

As with SciPy, ranges of optimality and feasibility are not computed automatically.

Pyomo

Pyomo is a powerful modeling framework that separates model formulation from solution.

• Models are defined using ConcreteModel.
• External solvers (e.g., GLPK, CBC, CPLEX, Gurobi) perform the optimization.
• Dual values and reduced costs can be imported using special suffixes.

Pyomo provides great flexibility and is widely used in research and industry. Nevertheless, automatic sensitivity analysis is not available, and post-optimality analysis must be carried out manually or via solver-specific reports.

Key Remarks

• Excel Solver provides automatic and complete sensitivity analysis, including feasibility ranges.
• SciPy, PuLP, and Pyomo provide access to dual values, slacks, and reduced costs, but do not compute allowable ranges automatically.
• In PuLP and Pyomo, sensitivity information depends on the capabilities of the underlying solver.
• Python-based tools require manual interpretation for post-optimality insights.

📌 Full technical details and complete code examples are available on Medium: https://medium.com/@natacherchum/resource-allocation-linear-programming-model-01337b658653

#Optimization #LinearProgramming #DataAnalytics #BusinessIntelligence #OperationsResearch #ExcelTips #PythonForDataScience #DecisionSupport #ProcessImprovement #ResourceManagement