Friday, May 30, 2014

Minimizing Cutting Stock Waste With Excel Solver

Optimizing the Cutting of

Strips of Sheet to

Minimize Waste With

Excel Solver

This is another classic Solver problem with many possible variations. Cutting Stock problems involve cutting large sheets into the optimal number of smaller strips to meet customer orders while minimizing waste. The sheets can represent any type of material that come in a strip that is cut into smaller strips, such as a roll of steel. This problem involves rolls of garment that are cut to smaller sizes to meet individual customer orders.

 

The Cutting Stock Problem

A garment factory produces rolls of garment in the following 2 sizes: rolls that are 75 inches wide and rolls that are 55 inches wide. Customers order garment rolls in the following 3 sizes: rolls that are 36 inches wide, rolls that are 25 inches wide, and rolls that are 13 inches wide.

The factory must cut the 75” rolls and 55” rolls in the correct number of 36”, 25”, and 13” rolls to meet individual customer orders while minimizing waste. Waste represents garment that is cut from the original 75” and 55” rolls which is not included in a customer order and is left-over.

 

Excel Solver Problem Solving Steps

Excel Solver Step 1 – Determine the Objective

In this case, the objective is to minimize the total waste left-over when satisfying each customer order. As seen in Step 3, Cutting Stock problems require listing all of the possible combinations that the original sheets can be cut in order to satisfy the customer’s order.

The amount of waste (leftover material) from each combination is shown. The total amount of waste of all combinations that are cut is also listed. The objective is to determine the correct number of each combination to cut that will meet the customer’s order and minimize the total amount of leftover, wasted garment. The Objective is to minimize the total amount of wasted garment. The cell is which the total waste from each order is calculated is the Objective Cell.

 

Excel Solver Step 2 – Determine the Decision Variables

We are trying to determine how many of each combination to cut that will satisfy the customer’s order requirements and also minimize leftover wasted garment. The Decision Variables are the numbers of each type of cutting combination to cut.

 

Excel Solver Step 3 – Build the Excel Equations That Combine the Objective With All Decision Variables

The yellow Objective Cell, I16, displays the total amount of leftover garment that will be wasted when a specific group of cutting combinations is applied to meet a customer order. This will be minimized.

The green Decision Variable cells, cells H4 to H14, display the number of each type of combination that will be cut to meet an individual customer order. Following is a complete view of the Excel model leading to the Objective.

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

The following 2 images present an expanded view for better clarity of the left and right halves of the preceding complete Excel model. The 1st image is the left side of the model and list all possible cutting combinations and the waste left-over from each. The 2nd image shown is the right side of the model with the green Decision Variables and the yellow Objective Cell:

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

 

Excel Solver Step 4 – List all Constraints

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

The light blue Constraint cells in the model the preceding image link to the light blue user-controlled inputs outside of the model in the following image. The user-controlled inputs represent the customer’s order as follows:

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

 

Excel Solver Step 5 – Test the Excel Spreadsheet

Test the Excel spreadsheet completely before adding information to the Solver dialogue box. Make sure that any changes to Decision Variables produce the correct results in the Objective cell.

 

Excel Solver Step 6 – Insert All Data into the Solver Dialogue Box

Input the Objective cell, Decision Variable cell, and all Constraints into the Solver dialogue box as follows:

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

Note that the Decision Variables are Constrained to be integers because only whole numbers of garment rolls can be cut. The equality Constraints are the links between the number of garment rolls in the customer’s order and the model. Unconstrained variables are made nonnegative to ensure that there can be no negative numbers of rolls cut.

There are still a few more Constraints not shown in the preceding image that are shown as follows by scrolling down the slider bar next to the Constraints:

clip_image014 (Click On Image To See a Larger Version)

All equations on the Excel spreadsheet are linear (1st order) so we can use the Simplex LP (Linear Programming) Solver engine for this optimization problem.

Step 3 shows the completed problem with Decision Variables that have been optimized by the Solver to maximize the Objective while staying within the problem’s Constraints.

 

Excel Solver Answer Report

Part 1

Note:

- The Solver Result

- How long Solver took to solve the problem

- The Solver Engine that was used and the Solver Options settings

- Where the Objective Cell was labeled in the Excel model for its name to appear as it does in Part 1 of the Answer Report

clip_image016 (Click On Image To See a Larger Version)

Part 2

- Note that the Variable Cells contain the Decision Variables

- Note where the labels for each Decision Variable are placed in the Excel model so that the Decision Variable’s name will appear here in Part 2 of the Answer Report as it does

- Note the type of variable - Either Continuous or Integer (Integer, Binary, or Alldifferent)

- Note the Before and After values of each Decision Variable

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

Part 3

- Note how each Constraint is labeled in the Excel model in order for the Constraint’s name to appear here in Part 3 of the Answer Report as it does

- Note which Constraints are binding (had their limits hit) and which aren’t.

- Note how much slack is still available in any Constraint that has not had its limit hit.

- Note any Integer Constraints (Integer, Binary, Alldifferent)

solver,excel solver,cutting stock,optimization,excel, excel 2010,excel 2013, statistics,waste minimization (Click On Image To See a Larger Version)

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at

4 comments:

  1. Laxmi RaiSeptember 2, 2016 at 10:41 PM

    Wonderful article. Thanks for sharing such a useful post.

    non woven fabric bag making machine

    ReplyDelete
  2. Raghavendra ShedbalSeptember 15, 2016 at 1:04 AM

    Hi, can you please share the excel sheet of this solver?

    ReplyDelete
  3. AnonymousFebruary 21, 2019 at 5:05 AM

    How are the values in H4 to H14 (green cells) determined? Is it a formula or an algorithm or just guessing?

    ReplyDelete
  4. john smithJune 12, 2019 at 9:42 AM

    Excellent .. Amazing .. I’ll bookmark your blog and take the feeds also…I’m happy to find so many useful info here in the post, we need work out more techniques in this regard, thanks for sharing.  JUNK HAULING

    ReplyDelete

Subscribe to: Post Comments (Atom)