Linear Programming using Excel – Including penalties which depend on decision variables (avoiding non-linearities)

Question

I am 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. If I multiply a penalty by a new binary decision variable, this becomes a non-linear problem as the penalty depends on the origin decision variables (that is the blend proportions). 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?

BTW: I cross-posted this question to ExcelForum, but am still struggling to solve my problem.

Screen capture of Excel showing example