Understanding Linear Optimization: A Step-by-Step Guide

Dr. Ernesto Lee

5 min read

·

Sep 23, 2024

--

Listen

Share

Press enter or click to view image in full size

Introduction

Linear Optimization, often referred to as Linear Programming, is a powerful technique used to solve problems where you want to optimize an objective (like maximizing profit or minimizing costs) while respecting certain constraints (such as resource limits). It’s like trying to fit the maximum number of pieces in a puzzle, but every piece must follow specific rules and limitations. Deck.

In the real world, many decisions revolve around the best use of limited resources. Imagine running a factory where you can produce two products, but your production capacity, raw materials, and labor are finite. How do you decide how many units of each product to manufacture to maximize your profit while not exceeding your available resources?

This problem is precisely what Linear Optimization helps solve. The beauty of this method is that it breaks complex decision-making into manageable mathematical equations — simple lines that represent your choices and constraints. The result is a clear roadmap to the optimal decision.

At its core, Linear Optimization involves:

1. An Objective Function: The goal you want to achieve (e.g., maximizing profit or minimizing cost).
2. Decision Variables: The quantities you control (e.g., how many units of a product to produce).
3. Constraints: The limitations you must respect (e.g., the amount of raw materials or time available).

This structure can be applied to problems in logistics, manufacturing, finance, and many other fields. Whether you are planning a production schedule or balancing a portfolio of investments, Linear Optimization provides a rigorous framework to make optimal decisions.

Real-World Example

Let’s take a deeper dive into a practical example. You run a business that manufactures two products: Product A and Product B. The goal is to maximize your total profit while accounting for the available machine hours and raw materials.

Data:

• Profit per Unit:
• Product A: $30
• Product B: $50

Machine Time Required per Unit:

• Product A: 2 hours
• Product B: 1 hour

Raw Material Required per Unit:

• Product A: 1 unit
• Product B: 2 units

Available Resources:

• 400 machine hours
• 300 units of raw material

Objective:

Maximize profit by deciding how many units of Product A and Product B to produce, given the machine and material constraints.

This type of problem is commonly encountered in manufacturing, where businesses must decide how to allocate limited resources (like time and materials) to maximize their financial returns.

Formulating the Problem:

Objective Function: Profit = 30 * A + 50 * B

• You want to maximize this equation.

Constraints:

• Machine Time: 2A + 1B ≤ 400 (You can’t use more than 400 hours of machine time).
• Material Availability: 1A + 2B ≤ 300 (You have only 300 units of material).
• Non-negativity: A, B ≥ 0 (You can’t produce negative units of a product).

Using Excel Solver for Bakery Cost Optimization

Problem:

You are the owner of a bakery, and you want to introduce a new pastry. To produce it, you need to buy a new piece of baking equipment that costs $40,000. The cost will be paid off in installments over 12 months. Your goal is to calculate the minimum cost per pastry that will allow you to cover the equipment cost within this timeframe.

Goal:

Find the minimum price per pastry that allows you to pay for the new equipment within 12 months, based on the number of pastries you expect to sell each month.

Step 1: Set Up the Model in Excel

1. Open a new Excel workbook.
2. Enter the following data into your spreadsheet:

Step 2: Set Up the Objective Function

1. Now, the objective is to find the minimum cost per pastry that ensures enough monthly sales to cover the payment. The formula to calculate this is:

• In Cell B7, enter: = B3 / (B4 * B5)
• This formula divides the monthly payment by the cost of the the new equipment (B3) and the total cost per pastry * client (B4 * B5).

Step 3: Open Excel Solver

1. Go to the Data tab and select Solver from the Analysis group.
2. In the Solver Parameters dialog, do the following:

• Set Objective: Select B7 (this is the monthly payment formula).
• To: Minimize (since you want to find the minimum price per pastry).
• By Changing Variable Cells: Select B3 (Total Pastries per Month) and B5 (Cost per Pastry).

Step 4: Add Constraints

1. You need to ensure that the number of pastries sold per month is less than or equal to the maximum capacity.

• Click Add and add the constraint:
• B3 =40,000 (Total cost of the equipment).

The number of pastries must also be a positive integer:

• Add another constraint: B3 ≥ 0 (pastries cannot be negative).
• Ensure that B3 is set as an integer: Click on Add, and in the drop-down menu, select int to ensure that the number of pastries is a whole number.
• Also make sure the number of projected customers is less than 50/month

Press enter or click to view image in full size

Step 5: Solve the Problem

1. After setting the parameters and constraints, click Solve.
2. Excel Solver will run and find the minimum cost per pastry to cover the equipment cost over the specified timeframe.
3. Once Solver finds a solution, it will show a dialog box. Select Keep Solver Solution and click OK.

Step 6: Review the Results

• The cost per pastry (B5) will now show the minimum price you need to charge for each pastry to ensure you can pay off the equipment within 12 months.
• The total pastries sold per month (B3) will show how many pastries need to be sold per month at this price point.

Conclusion:

By using Excel Solver, you’ve found the minimum price per pastry your bakery needs to charge in order to cover the equipment cost over 12 months. This approach allows you to optimize the financial viability of introducing new products or services while managing your resources efficiently.