+ Reply to Thread
Results 1 to 4 of 4

Solver modeling for production mix

  1. #1
    Registered User
    Join Date
    12-04-2019
    Location
    USA
    MS-Off Ver
    Office 365 for Mac
    Posts
    1

    Solver modeling for production mix

    I'm trying to set up a solver model and can't figure out how to add a certain constraint:

    Given are 4 different products (Product A, B, C and D) with its given profit per unit. Let's assume the profit is $1, $2, $3, $4 respectively. Additionally the time to produce each unit is given, let's assume 1 minute, 2 minutes, 3 minutes and 4 minutes respectively. I'm trying to maximize the profit. the variable cells are the quantities for each product.
    Constraints are:
    Total amount for all products can't exceed 1260 units
    total amount of production time available can't exceed 21600 minutes
    Product D >20 units
    For every 10 units of Product A and B produced, one unit of Product C must be produced - This is the constraint I can't figure out how to incorporate into the model.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,929

    Re: Solver modeling for production mix

    You could try defining the quantity of C with a formula based on A & B

    =MIN(ROUND(B2,-1),ROUND(B3,-1))/10

    When I had a go at solving, it suggests just make product D with these profits and times.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,359

    Re: Solver modeling for production mix

    Hi treemaster

    As Martin already pointed out your present profit setup will only make solver produce 1260 units of product D so in order to get a more sensible answer I reversed the product proffits.

    for the constraint of the C production I used the mod function and as you specified D production > 20 and as solver only has ">=" I specified D as ">=21"

    solv_mod.jpg

    Alf

  4. #4
    Forum Expert
    Join Date
    03-13-2004
    Location
    Gothenburg/Stockholm, Sweden
    MS-Off Ver
    Excel 2003, Excel 2007 & reluctant Excel 2010 user
    Posts
    4,359

    Re: Solver modeling for production mix

    Something strange with the setup, modifying the formula for C production to "=((E3-MOD(E3,10))+(E4-MOD(E4,10)))*0.1 and setting target cell ">=4700" I got a much better profit. A thing one would expect as maximising production of A, minimizing production of D and balancing the C production according to the ratio is the optimal solution.

    Don't know why the model did not see that at once? Something to do with the MOD function?

    solv_mod2.jpg

    Havin tested a bit more I replaced the C constraint with the "INT" function instead of using the "MOD" function so formula for C production is now

    Please Login or Register  to view this content.
    and this seems to work better as I got the target cell value to 4753 without specifying that this value should be ">=4700" and production was A =1127, B =0, C =112 and D =21.

    Alf
    Last edited by Alf; Yesterday at 04:11 AM. Reason: Better formula for constraint

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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