• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Interim Solver Question

N

Nard Cox

New Member
Hi all,

Long time Chandoo follower, first time forum user.

I have an lovely interim who is currently working on improving our packaging. I've offered to help using some of my Excel knowledge and thought it would be a great idee to use the Excel Solver to determine the new dimensions of our cartons.

We've build the data and formulas but we can't get the Solver to solve the dilemma.

How the Excel sheet works.
Tab Stock = Inventory of our company with dimensions and volumes
Tab Sales = Sales quantity with order id, item nr and sales quantity
Tab Data gathering = Above two tabs combined + % air
Tab Analyse Data = Pivot on the tab before and some additional formulas to determine the correct carton based on the dimensions and volumes and the absolute and relative % of air vs products
Tab Analyse = Some pivots on the tab before where I cross check carton suggestion based on dimensions vs carton suggestion based on volume (not relevant now)
Tab Karton data = The tab where the 'magic' should take place. It contains a table (Berechnung!!!) that is used to calculate the values in tab "Analyse Data". What we are trying to do is

Set Objective: $I$17 - 'Total Luft' (its German) Min
By Changing Variable Cells : $C$11:$E$16 - Carton dimensions
Subject to the Constraints : $H$17 >= 13500 - Minimum number of orders that need to be in the data otherwise it would suggest to do all 0 and there would be no air.

We've tried solving it be using the GRG Nonlinear solving method and have checked Make Unconstrained Variables Non-Negative.
It does something, but it's only very small changes to the data we put in manually.

Please help :)

Thank you in advance!

File download GDrive
(it's to big to be used as attachment on the forum)
 
Hi:

For solver to work yo will have to clearly define your

  • Objective Function
  • Decision Variables
  • Constrains
  • Non negativity Constraints

Once you have clear understanding of the above you will be able to translate your business problem into a solver based solution. Solver is not very technical since the excel will do all the hard work for you. The challenge is how well someone can understand the business problem and translate it to a language that solver understands. There are lot of sample solutions a write ups available over the net which will help you.

Thanks
 
Hi Nebu,

We do have some solver example working that's in the 'Solver' tab. That wasn't the problem. The problem we are having is translating what we know have into Solver language. As far as we see we are doing everything correct but still it's not working properly. We've checked a lot of example and topics but still couldn't figure it out, hence we made this topic.

We've done a lot of manual work by manually changing the cells to try and reduce the air and that has been fairly successful so far. But I know Excel Solver can do a better job than we can.

If we use Solver is does change the values but for example from 150 to 149.91 etc. For us that's not relevant since we are talking about carton dimensions in milimeters so we don't need comma accuracy. Also tried changing that in Options by changing 'Constraint precision' to 0,1 and 'Convergence' to 0,1 but no luck.
 
Hi ,

Leaving aside what you have tried , can you clearly and comprehensively explain :

1. What should the Solver do ? Which cell should it minimize , maximize or bring to some preset value ?

2. Which cells should it vary ?

3. What are the constraints that need to be applied , with specific reference to cells and their values ?

Narayan
 
Hi NARAYANK991,

1. It should reduce the amount of air inside the karton. It should minimize the cell that is SUMming the amount of air.

2. It should vary the karton dimensions. These will influence the karton volume and hence the amount of air inside the cartons.

3. There is only 1 real constraint and that it that it needs to suggest dimensions for cartons in a way that can we pack all orders.
 
Hi ,

You mentioned just a few references , but with the kind of formulae that are being used , and a pivot table also involved , I doubt that Solver can do anything.

Either we reduce the amount of data and see whether it makes a difference , or we eliminate the pivot table , and use the data directly ; is either of these possible ?

Narayan
 
The pivot tables are not involved for the data that the solver needs to work with.

The solver only needs to work with Tabs "Analyse DATA" and "Karton data" in which only plain old simple formulae are used.
 
Hi ,

Your aim is to minimize Total Luft ; this is an addition of values from this formula :

=(F11*H11)-SUMIFS('Analyse DATA'!$E$2:$E$?????,'Analyse DATA'!$H$2:$H$?????,B11)

where the question marks can stand for any kind of end row number. Here , column E is a part of the pivot table PivotTable4.

The pivot table is a consolidation of more than 44000 rows of data.

We need to start with a more reasonable set of data , say 1000 or even 100 rows.

I do not know why we need to structure the data order-wise ; probably you can explain.

The entire application text being in German is making it all the more difficult to come to grips with it.

What exactly are Neue 1er , Neue 3er , Neue 5er and so on ? Why are we trying to minimize the total luft of all these ? Why not of each one at a time ?

Narayan
 
The cause for this exercise is that we've seen at our packing stations that we ship a lot of air inside the cartons. This basically means that our carton dimensions/volume don't fit out order profile.

The goal of this exercise is to get a better fit between the products we ship and the cartons that we use. In order to express this we said; we want to reduce the amount of air we ship inside cartons. This also reduces cost, carbon footprint and damages during transport.



Perhaps it's better if I explain the file as shortly but completely as possible.



Tab Stock
Contains our product dimensions (column B-D) and volume (column E). Columns F to H are to create numbers from the text strings.

Tab Sales
Contains the sales data for a single day. It starts with the order id (column A), every order has several items (column B) and the quantity per item (column C).

Tab Data gathering
The first 3 columns are a copy / paste from the Sales tab. Behind that I search for the product dimensions (column D-F), then I want to know the min, median and max dimension of each product (column G-I). Then I search for the volume per item nr (column J) and multiply that with the sales quantity (column C) and add x% of air (cell O1) to get a more realistic view of the volume we need.

Tab Analyse DATA
The first 5 columns are a pivot based on the table in tab 'Data gathering'. Here I want to know the max dimensions of the combined products in 3 dimensions x, y, z. Hence I select the max of the min dimensions, the max of the median dimensions and the max of the max dimensions (columns B-D) per order. After that I take the SUM of the Volume of the items.

Then I want to select the smallest possible carton to fit the max dimensions in (column F) and the smallest possible carton to fit the volume in (column G). Then I want to know which of these is the biggest carton (column H) since this is the carton that will be used to pack the order in.

In column I I simple add the volume of the suggest carton (column H). Then I calculate the amount of air in the carton (column J). In column K I calculated the % of air inside the carton. Column L is irrelevant and not used (so far).

Tab Analyse
Not relevant / not used.

Tab Karton data
This tab only contains I table that i actively being used. Table 13 (rows 10-17).
The first part is the name of the carton. Than the dimensions in x, y, z in milimeters. Than the cm3 per carton.
% Luft is than the average % of luft inside that type of carton.
# karton is the number of kartons I need off that type.
Total luft is the total amount of air inside all the cartons of a single type.
AVG Luft pro Karton is the average amount of air inside a carton (not relevant)
Kosten pro karton are the cost per carton in Euros
Total kosten are the total cost, so the number of cartons multiplied with the price per carton.

Tab Solver
Just a small example we made where the Solver works like a charm.



Now with regards to your questions.



E is indeed part of the pivot table, but that table doesn't get refreshed (for now) during the calculations. I could have also copy / pasted that data into a new table and went from there. I've didn't do that because later I want to use a week worth of data (500.000 rows) and that would save me the hassle of copy/paste.


We need to calculate the data per order since I need a carton per order.


Neue 1er , Neue 3er , Neue 5er are just names for the cartons, new 1, new 3 and new 5. 1, 3 and 5 are the 'type' or 'names' we call our cartons. 1 being the smallest and 5 the biggest (not taken the Vol. kartons into account since these are bigger).


Hope the above makes sense. Thank you for taking the effort to help us, much appreciated.
 
Hi ,

Thanks for the explanation ; it is somewhat clearer , but with more clarity come more doubts.

My first doubt is why can we not run the Solver on the different carton types Neue 1er , Neue 3er , Neue 5er individually , independently ?

I tried removing all the constraints not relating to Neue 1er , and tried minimizing I11 ; is this meaningful ?

Narayan
 
You could do that, I cut off the solver at 60 seconds to prevent me waiting to long.

However, I would think it would make it all 0 because that would reduced the air to 0 because this carton will not be suggested for any order.

We've also been discussing and made 2 more contraints.
1. The total number of kartons need to be 13500 minimum since otherwise I could make the cartons so small that we can't pack a lot of orders.

2. The other thing is that the "Vol." cartons combined can't be more than 400 (sum of cells H14-H16). Reason for this is that the first three types can be handled automatically and the bigger ones can't meaning more handling, time, cost, etc.
 
You must log in or register to reply here.
Back
Top