+ Reply to Thread
Results 1 to 12 of 12

[SOLVED] How to optimize cost and material with formula, Solver or any method

  1. #1
    Forum Contributor
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    477

    [SOLVED] How to optimize cost and material with formula, Solver or any method

    Hello all,

    I had gotten help on this thread.

    http://www.excelforum.com/excel-form...-material.html

    Thank you so much for the solution. But I have 2 additional new requests. It may need new methods, I do not know.

    We have 2 different products:Apple and Orange. They use same order of boxes and the box holds different qtr for each products.

    Question 1) How can I optimize the packing material with combination of different items?
    Question 2) How can I optimize the material cost? As you see it on column D3 and D6. If we send out order of 48 pcs using 2 "14 x 12 x8", we can save cost of 22 cents per order.

    I search the solution on line, and I think a method called solver can accomplish this but I have not sort it out how to do it.

    Any help will be greatly appreciate. Thanks.
    Attached Files Attached Files
    Last edited by jackson_hollon; 11-29-2014 at 11:51 AM.

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: How to optimize cost and material with formula, Solver or any method

    Just checking I understand you problem properly. In your uploaded file order 1001 has a solution where you use 1 box 14X14X8 and 1 box 13X9X9 but if you used a 14X14X14 box that would be a cheaper solution for packing costs but this box would have 3 "empty" spaces as this is a 36 unit and you only need 33.

    So is your problem really that all boxes should be filled to maximum capacity and the number of boxes selected and their size should be the cheapest possible combination?

    Alf

  3. #3
    Forum Contributor
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    477

    Re: How to optimize cost and material with formula, Solver or any method

    ALF,

    Thanks for the help.

    Yes. I need the cheapest packing material with combination of different items. The sample data was only for demonstration purpose. The true data will be different. I just need to know how to do it and I will replace with the true data.

    Thank in advance for the help.

    Jackson

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: How to optimize cost and material with formula, Solver or any method

    Ok I've set up a solver model based on your uploaded file. To test you need to run the macro "Solver_loop". Before you can run this macro you must set a reference to solver (I assune solver is installed on your PC).

    To set a reference to Solver click on "Developers" tap, then click on the "Visual Basic" icon and select "Tools" -> "References" and tick the box marked "Solver"

    I've split you apple and orange to separate sheet one for apple and one for oranges.

    Macro starts by clearin all data on these two sheets. Then it aplies autofilter to sheet1 in order to separate apples and oranges and result are copied to the appropriate sheet.

    At the moment solver is set to match the number of boxes with the number of cans this is set by the line for apples

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    for oranges.

    Below these lines are a green line i.e. not active at the moment. Difference is that the "Relation:=3" this part canges the setting from "equal to" to "equal or grater than".

    I've tested the effect of this change and you will find the result on sheet2.

    Alf
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    477

    Re: How to optimize cost and material with formula, Solver or any method

    ALF,

    First of all, I want to tank you for the great help.

    I did all you said to set a reference to solver. However, after I set the reference, how do I run the Macro? Or do I go to Data/Hit the solver button? Sorry for asking as I am very new in this.


    Thanks again for the help.

    Jackson
    Last edited by jackson_hollon; 11-27-2014 at 11:17 PM.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: How to optimize cost and material with formula, Solver or any method

    Sorry for asking
    No problem. How should we learn if we did not ask.

    First of all is Solver installed on your PC? Click on the "Data" tab and see if you have the "Solver Icon" , probably the last icon going left to right.

    If you don't see it you need to set up solver and this link should tell you how to do it.

    http://www.excel-easy.com/data-analysis/solver.html

    You now need to set a reference to solver. In the Visual Basic Editor, with a module active, click References on the Tools menu, and then select Solver under Available References. If Solver does not appear under Available References, click Browse, and then open "Solver.xlam" in the \Program Files\Microsoft Office\Office14\Library\SOLVER subfolder. Click ok and then close the "Microsoft Visual Basic for Application" window.

    When browsing to find the "Solver.xlam" file you need to change the file settings in the window that pops up when clicking the "Browse" button. In the "drop-down" for "Files of type:" change it to "All Files (*.*)" or you will not find the "Solver.xlam" file.


    You should no be in the "Developers" tab. Click on the "Macro" icon and the macro windows pops up. You will then see the macro "Solver_loop", the blue line in the scroll window shows which macro is "selected" and then just click the "Run" button.

    As this setup runs solver in a loop with a macro you use the "Developers" tab -> "Macro" icon to find / select the appropriate macro and the you click the "Run" button.

    Hope this works for you, if not don't hesitate to ask for more information.

    Alf
    Last edited by Alf; 11-28-2014 at 04:02 AM.

  7. #7
    Forum Contributor
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    477

    Re: How to optimize cost and material with formula, Solver or any method

    Alf,

    First of all, I need to thank you for all of your help. I had learned a lot in this thread.

    I majored in political science but end up getting a job in a different field. I need to use a lot of Excel at work. It seems everyone at work knows a lot of Excel, I am trying to keep up with them.

    I just did all as you stated in the thread. I am using my wife's computer, now I see the effect. Thanks again for the explanation.

    One thing to ask is if I have new data, which tab should I enter the data to? What I mean is which sheet content the source data?

    Thank you very much again for the help.

    Jackson

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: How to optimize cost and material with formula, Solver or any method

    Hi Jackson
    It works now, macro and all?

    For new data you enter it in sheet1 in the same place as the old data was. If new data is more or less row than the old data this is no problem as macroc checks the number of rows.

    When I get a bit more time i.e. tomorrow I can upload a world document explaning in detail how this macro works as this will give you a better understanding of a macros strength and weknes.

    Alf

  9. #9
    Forum Contributor
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    477

    Re: How to optimize cost and material with formula, Solver or any method

    Alf,

    My goodness. I just tested by adding items in the sheet 1. It all works out. Thank you very very much.

    I cannot say enough thank you for all you did.

    Looking forward for the word. I will study back and forth to learn it. Thanks AGAIN.

    Have a nice weekend.

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: How to optimize cost and material with formula, Solver or any method

    Hi Jackson
    Thanks for rep an wishes for nice weekend. Have put together a walk-through for the macro. Will not be a bestseller but hopefully you will find it useful.

    To see what happens you could when you run the macro in "slow motion" i.e. go to the developers tap. Click on the macro icon and when the the "Macro" windows opends click on "Step Into" you will be go to thr macro and by pressing the "F8" button on the key board the macro will be executed one line at the time.
    By jumping between the Visual Basic window and the "excel file" view you can see what happens, remember what should be done will only happens after you move on to the next line.

    More possible uses for a setup like this, perhaps running previous months on this file and see what solver says is the best solution then compare this with what really happened?

    Or do a forecast on the future based on expected sales and average customer demands, or change size om some boxes and see if this may bring down costs?
    The Excel solver is a quite powerful tool for these kinds of studies.

    A nice weekend to you as well.

    Alf

    Ps if this solves you problem don't forget to mark thread "Solved". Go to first post, click "Thread Tools" and select "Solved" from dropp-dowe.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    06-07-2013
    Location
    US
    MS-Off Ver
    Excel 365
    Posts
    477

    Re: How to optimize cost and material with formula, Solver or any method

    Alf,

    This is Thanksgiving weekend in the U.S. As of today, you have giving me one more thing to be thankful. Thanks for the kind action. I will study the word doc, and will do some testing with really data when I go back to work on Monday. I will post the result here.

    Thanks again for the great help.

    Jackson

  12. #12
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,657

    Re: [SOLVED] How to optimize cost and material with formula, Solver or any method

    You are welcome.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Formula to optimize packing material
    By jackson_hollon in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2014, 04:13 AM
  2. Optimize Cutting Material list
    By Suraj3825 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2013, 02:14 PM
  3. [SOLVED] Cost Estimator Material Consumption
    By chriswiec in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-14-2013, 06:12 AM
  4. Cost Estimator Material Consumption
    By chriswiec in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-14-2013, 01:13 AM
  5. Need optimization program to optimize material/minimize waste
    By mellowbiscuit in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2012, 07:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1