+ 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,967

    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

  3. #3
    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,758

    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 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,758

    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; 12-05-2019 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)

Similar Threads

  1. Solver Production Planning Issue
    By preszsbuild in forum Excel General
    Replies: 4
    Last Post: 07-19-2019, 08:12 AM
  2. Consolidated Production chart (Production vs wastage vs Changeovers)
    By Abrarpkbev in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-10-2019, 03:15 AM
  3. Solver for Production Planning
    By snap101 in forum Excel General
    Replies: 2
    Last Post: 02-23-2016, 02:48 PM
  4. How to get maximum production by using solver
    By amolryeolekar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-08-2013, 11:53 AM
  5. [SOLVED] Solver modeling problem with Frodo and hobbits
    By Alf in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-02-2011, 04:08 AM
  6. Excel Solver background in forecasting modeling
    By RL in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2005, 06:06 PM
  7. Replies: 0
    Last Post: 03-16-2005, 03:13 AM

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