Operations Research - A Dive into Non-Linear Programming Modeling

Introduction

Non-linear Programming (NLP) adds a layer of complexity to optimization problems by incorporating non-linear functions. This article explores the modeling of Non-linear Programming using Microsoft Excel Solver. We will embark on this journey by presenting an example problem statement, demonstrating the data setup in Excel, and showcasing the step-by-step process of leveraging Solver to optimize non-linear models.

Example Problem Statement

Let's consider a classic non-linear optimization problem. You want to maximize the profit (P) of a product based on the quantity produced (Q) and the production cost (C). The profit function is given by:

P=20Q?2Q2?0.1CQ

However, there is a constraint on the production cost, C, which should not exceed $5000.

Solving the Problem

Step 1: Formulate the Objective Function:

The objective is to maximize the profit P based on the quantity produced Q and the production cost C. The profit function P is given by:

P = 20*Q ?2*Q^2 ? 0.1*C*Q

Step 2: Set Up the Constraint:

There is a constraint on the production cost C, which should not exceed $5000:

C ≤ 5000

Step 3: Find the Critical Points:

To find the critical points where the profit is maximized, we need to find the first-order partial derivatives of the profit function with respect to Q and C, and set them equal to zero:

?P / ?Q = 20 ? 4*Q ? 0.1*C = 0

?P / ?C = ?0.1*Q = 0

Solving these equations simultaneously will give us the critical points.

Step 4: Check the Second Derivatives:

To determine whether each critical point is a maximum or minimum, we need to compute the second-order partial derivatives of the profit function with respect to Q and C, and evaluate them at the critical points:

?^2P / ?Q^2 = ?4

?^P / ?C^2 = 0

?^2P / ?Q?C = ?0.1

Step 5: Verify Constraints:

Verify that the critical points satisfy the constraint C ≤ 5000.

Step 6: Determine the Maximum Profit:

Once you have identified the critical points that satisfy the constraint and determined their nature (maximum, minimum, or saddle point), evaluate the profit function at these points to find the maximum profit.

By following these steps, you can systematically solve the non-linear optimization problem and determine the quantity produced Q and the production cost C that maximize the profit for the given scenario.

Setting Up the Excel Worksheet

Define Decision Variables: Open a new Excel worksheet. In cell B2, label it "Quantity (Q)". This cell will be our decision variable.

Objective Function: In a cell, let's say C2, label it "Profit (P)". Enter the formula =20*B2 - 2*B2^2 - 0.1*5000*B2 to represent the profit function.

Constraint: Introduce a constraint to ensure the production cost does not exceed $5000. In cell D2, label it "Production Cost (C)". Enter the formula =5000.

Non-negativity Constraint: Set a non-negativity constraint for the quantity produced: =B2 >= 0.

Solver Parameters Dialog Box

Click on "Solver" in the "Data" tab. This opens the Solver Parameters dialog box.

Set Objective Function and Decision Variables: In the Solver Parameters dialog box, set the objective function cell to C2 and the decision variable cell (By Changing Variable Cells) to B2.

Add Constraints: Click on "Add" to enter the constraint for the production cost. Additionally, set the non-negativity constraint.

Choose Solving Method: Choose the GRG Nonlinear solving method for non-linear programming problems.

Solver Options: Optionally, set additional options based on your requirements.

Solve: Click "Solve" in the Solver Parameters dialog box. Solver will analyze the non-linear model and provide the optimal quantity to maximize profit while satisfying the constraints.

Interpreting Results

Once Solver completes its analysis, it will display the optimal quantity in the worksheet. This value represents the quantity to produce for maximizing profit within the given constraints.

Conclusion

This example illustrates the application of Excel Solver for Non-linear Programming. By skillfully setting up the problem, incorporating constraints, and utilizing the Solver function, organizations can optimize non-linear models efficiently. Excel's Solver provides a versatile and user-friendly platform for tackling complex non-linear programming scenarios, enabling businesses to make informed decisions and maximize outcomes in various fields, from production planning to financial optimization.