+ Reply to Thread
Results 1 to 3 of 3

Excel Solver - Converting a non-linear model to a linear one

  1. #1
    Registered User
    Join Date
    05-18-2021
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 365
    Posts
    1

    Excel Solver - Converting a non-linear model to a linear one

    Hello,

    First time poster looking for some help with an Excel Solver Model I am trying to develop. I would prefer if the model was linear, or a Mixed Integer Linear Program (MILP) will be OK too as these models are more easily solved than say non-linear models. I have created an example problem which is a smaller version of the actual problem I have. If I can solve the example problem, I can solve my real problem, and any help will be greatly appreciated!

    I am trying to maximise profit from blending multiple components (A, B, C ..). Each component has different properties (Quality 1, Quality 2, and Quality 3), available tonnes and cost price.

    The produced blend has a specified tonnage, which is 180 tonnes for the example. The blend also has limits placed on the quality and a penalty applies if the quality exceeds the Benchmark spec limit. I have created an example workbook which I have attached below.

    The model is linear until I start adding pricing adjustments for the different qualities. The pricing adjustments are calculated from the calculated blend quality and require if statements to account for cases when a price adjustment is not required:
    • If Quality 1 is higher than the Benchmark Spec (70) then there is no pricing adjustment from the Benchmark spec price ($100).
    • If Quality 2 is lower than the Benchmark Spec (12) then there is no penalty, otherwise calculate a penalty when values are between the Benchmark Spec and Rejection Max (12-15).
    • If Quality 3 is lower than the Benchmark Spec (15) then there is no penalty, otherwise calculate a penalty when values are between the Benchmark Spec and Rejection Max (15-20).

    Adding these pricing adjustments causes to the model to become non-linear. I believe I can add a binary variable and use a Modified M method to change these adjustments, but I cannot work out how to do this such that the model can be solved using the Simplex LP. Other examples assume a decision variable is constrained, but in this case a calculated value is constrained.

    Can this model be converted into either a linear of MILP model and how can I do this?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Hydraulics's Avatar
    Join Date
    07-15-2018
    Location
    Udine - Italy
    MS-Off Ver
    Office 365
    Posts
    369

    Re: Excel Solver - Converting a non-linear model to a linear one

    I think your problem can be converted to a MILP form.

    Since your question is more about mathematical programming than Solver in itself, you may have more luck in a math forum. Anyhow, here are a couple of hints.

    The penalty/adjustment price can be written using a binary (indicator) variable, so that when the condition is satisfied (for instance, C23 - C7 >= 0.0001), it will switch to 1. You can easily find examples online, and even in this forum.
    Please note that we use a threshold, since strict inequalities don't make sense from a mathematical (and physical) point of view.

    The next step is somewhat harder, because the new price is now expressed in terms of a sum of products between the binary variable just introduced, and the continuous ones in H13:H17. Luckily, we can always replace this kind of non-linear product with a set of constraints and auxiliary variables, but you have to repeat it for n components. You may also need to revise the setup of your worksheet.

    HTH,

    Francesco
    Aim high or don't even try.
    ---------------------------------
    If your question has been answered, don't forget to mark the thread as SOLVED.
    If you find an answer helpful, click on the star icon at the bottom of the post.

  3. #3
    Registered User
    Join Date
    09-14-2018
    Location
    China
    MS-Off Ver
    Office 2010
    Posts
    41

    Re: Excel Solver - Converting a non-linear model to a linear one

    No need to use Big M method here.

    You just need to add 3 decision variables(Y1,Y2,Y3) for Q1, Q2 and Q3 adjustment.
    Y1,Y2,Y3>=0;
    Y1>= (the formula of the second part of in IF formula you used)
    similarly for Y2, Y3.
    Meanwhile, note your revenue/cost cells(C33,C34) are using some cells violating the "linear requirement". You should just multiply 180(the D3 cell) by the price/cost.

    It is difficult to explain why we need to add 3 variables here.
    Let's do it like this way:
    Your objective to max your profit, right?
    Your obj is: max z=(price - cost - adjustedQ1 - adjustedQ2 - adjustedQ3)*180
    So Solver is trying to make adjustedQ1, adjustQ2, adjustedQ3 as small as possible in order to maximize the objective function.
    But we set up a constraint: Y1,Y2,Y3>=0 and Y1>=abc (Y1 is adjustedQ1, abc is the the formula of the second part of in IF formula you used)
    So Solver have to choose the larger one of (0, abc).
    if abc<0, it will use 0
    if abc>0, it will use abc.
    Then the problem is solved.

    I have corrected your model and the new model is a linear one. See attached file.
    Attached Files Attached Files
    Last edited by astupig; 06-01-2021 at 04:59 AM.

+ 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. Replies: 11
    Last Post: 03-05-2020, 02:53 PM
  2. Solver: How to maximize a mark average (non-linear problem) in a linear way?
    By Sunday4th in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-07-2017, 07:42 AM
  3. Linear program using Excel Solver
    By natygrosso in forum Excel General
    Replies: 6
    Last Post: 07-02-2014, 09:00 PM
  4. Transportation model, linear programming and Solver
    By GregDP in forum Excel General
    Replies: 6
    Last Post: 12-07-2013, 04:56 AM
  5. [SOLVED] Excel Solver linear program
    By Johnford in forum Excel General
    Replies: 5
    Last Post: 10-25-2012, 02:40 AM
  6. [SOLVED] linear model with solver
    By vert in forum Excel General
    Replies: 1
    Last Post: 04-04-2006, 11:35 AM
  7. Replies: 2
    Last Post: 02-08-2006, 08:10 PM

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