Changed values with Solver have to be the same for 2 out 3 cells
I am currently working on a price model, where I am trying to use solver to find the optimal price. In order to do so, I have to create a solver constraint, if the products are the same (for some reason the costs are different), solver must change the values of these cells with the same values. For instance, producing product A in the UK is more expensive than producing in the US, but the customer pays online the same price, hence solver has to set the pricing as equal.
Prod A, costs 12 dollar, solver has to find a solution price which is the same as flow 3
Prod B, costs 13 dollar
Prod A, costs 15 dollar, solver has to find a solution price which is the same as flow 1
Total costs= (1*12+1*13+1*15)=40
Turnover= (1*X+1*X+1*X)=X
Margin= turnover-costs
To goal is to find a price with a overall margin of 10%, where the price which solver has find is the same for flow 1 and for flow 3. How do I cope with this constraint?
-
Official comment
Hi Jonathan,
Thank you for submitting your request. To be perfectly honest I'm not 100% sure I fully understand, but I will do my best.
My initial interpretation makes me believe this will be fairly straight forward. The Solver allows for Equality Constraints on cells, which can include variables and/or functions.
To create an equality constraint where the right hand side will not be a constant, for example A = B where A and B are functions or variables, we want to enter into a cell =A - B, and set a constraint on that cell such that <cell> = 0. This creates the constraints A-B = 0 in a way Solver can calculate it fairly easily. As you can see from the constraint it is satisfied only when A = B.
Taking your examples, lets assume we know how to calculate Flow3 and Flow1, and we want them to be equal. In an empty cell, let's say A1 for simplicity, in A1 we set the formula =Flow3 - Flow1. In the setup of your model you then set the constraint A1 = 0 and you're all set.
Note if your Right hand side is a constant, for example we know before hand we want Flow3 and Flow1 to be =10 we can simple set a constraint on the cells containing the formula for Flow3 and Flow1 = 10.
Please let us know here if this helps or if your request differs from what my initial interpretation is.
Best,
Brandon T.
-
Hi Brandon,
In the first place, many thanks for your quick response. I have created an excel file, which contains my current issue.
I have created the model, but solver changes all of values with different numbers, incase the flows are the same, solver has to change the values with the same values.
Next, I have to make more contraints that the pricing should be greater than that total costs per product, but that is an easy constaint, sales price >=total costs per product.
Do you know how to cope with this?Kind regards,
Jonathan
0 -
Hi Jonathan,
Thank you for sending this, it does help clarify for me a bit now that I can see what you mean by Flow and what values you're changing.
You mentioned the following: "incase the flows are the same, solver has to change the values with the same values." coupled with your original explanation, I believe my initial statement on how to handle this still stands.
What you will want to do here is, in a new column (or row, if you prefer), create formulas that calculate the difference (A - B) in your "Sales price per piece" values for flows with the same name. Manually is probably easiest with a smaller model like this.
So let's say cell E3 contains the Sales Price Per Piece of BeneluxProd, and E4 is the sales price for that same product/Flow Code. These are the values that are changing that we want Solver to solve for.
In a new cell of your choosing, enter the formula =E3-E4. This is the difference in current sales prices for the same product.
In the solver dialog box, you create a constraint on the cell (let's say we put it in J3) so that it's = 0. Specifically, J3 = 0.
When you then solve the model with that constraint implemented, Solver will try to find a solution where the difference in Sales Price Per Piece for those two product entries is zero, meaning that are equal in value.
You would then have to create the same difference formula and constraint for any other duplicates you may have in your actual model.
Hope this helps! If you have any further followup questions please let us know. Unfortunately we don't have an example of a model trying to do something like this at this time, so do continue to let us know here if you have any follow up questions and we'll do our best to help out.
Regards,
Brandon T.
0
Please sign in to leave a comment.
Comments
3 comments