Manufacturing problem: using a maximum $ amount of current inventory

  • In a manufactuing company, we have X products and their parts that are becoming obsolete. Before they become obsolete, we need to use as much of the current inventory as possible. We are allowed to buy new parts to use up current inventory as long as it doesn't exceed the $ amount that it will allow us to use up (for example you could buy wheels even if they are becoming obsolete because it will allow you to use up some motors).


    I think the best way to solve this would be to use Excel's solver but I'm not sure how to do it.. Anyone have a pointer?


    please find attached an example (the real data will have about 50 possible products that can be built out of about 300 parts and they don't necessarily use all parts). forum.ozgrid.com/index.php?attachment/33839/

  • Re: Manufacturing problem: using a maximum $ amount of current inventory


    Hello ALB,


    Welcome to Ozgrid.


    Just thinking about this project, my guess is that the amount of work involved to determine the various correlations of quantities is going to be beyond the amount of time the unpaid volunteers on this forum may be willing give.


    For instance, and as you likely are aware, most manufacturing companies use a Bill of Materials that specify the exact quantities of parts relative to a given model. Your example workbook does not contain the necessary info to even begin to understanding the quantities required for "Manufacturing Product 1" or "2".


    My recommendation is post this request in the Hired Help forum with much more detail. At the least you will need a table matrix correlating part quantities to product types in conjunction with another table listing parts and costs.

  • Re: Manufacturing problem: using a maximum $ amount of current inventory


    in the attached XLS file, you can find the bill of material of each product. If you look at "Manufactured Product 2" for example: the first row says that you need to build 3x "Manufactured Product 2". And row 3-5 says that you are going to need 18 wheels, 3 motors and 6 frames (divide by 3 to get the BOM for a single "Manufactured Product 2".


    I hope it is clearer


    I don't ask anyone to solve it completely but a link to a related example would be nice..


    thanks

  • Re: Manufacturing problem: using a maximum $ amount of current inventory


    I solved it, when I get a chance I will "anonymize" the data and post it (hopefully by the weekend)

  • Re: Manufacturing problem: using a maximum $ amount of current inventory


    You can find the spreadsheet attached.


    Usage: You either need to change the product quantities manually at the top or use the solver (Tools - solver).


    note: If you were to use the solver, replacing the "OVERALL PARTS IN STOCK >= OVERALL PARTS TO BUY" (D2 >= G2) constraint with a constraint for each product instead might give better results (G440 >= G439 for example).


    The solver in excel 2002 doesn't seem very efficient so I solved it manually (might not the very best solution).


    Please let me know if you have any comments or suggestions

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!