Forum Discussion
Using Solver to solve selling price and quantities to achieve the target amount
First thank you for the reply, it's my debut in this forum. I have to admit this is not as easy as I thought. I accepted the job offer thinking I would find a quick easy solution to accomplish the task. After reading the comments, I've looked at the question more critically, checked out LP and it demands a lot for an amateur... not demeaning myself lol. Yes, formulating an effective LP is the backbone to solving these problems.
From your gist I've learnt a lot... so something like the cost price doesn't necessarily mean it's supposed to be the minimum price which I assumed to be so. From what I've understood now.. I did not include that the first constraint is that: selling price > cost price and the second constraint is: quantity > 0
alvinro wrote: ``I accepted the job offer thinking I would find a quick easy solution to accomplish the task.``
And perhaps there is. See the attached file for some simple models.
In the first model (worksheet "orig"), I allow both selling price and quantity to vary, with a minimum of constraints. I assume that quantity must be integers; but it is unclear if that constraint is valid for all items (e.g. if quantity is a measure of weight). This is the result.
Note that I changed the objective formula in I2. Based on the assumption that the actual amount should not exceed the target amount, the formula in I2 is simply:
=H2-SUMPRODUCT(C2:C14,D2:D14)
Also note that the Solver objective formula in I2 depends on the unrounded selling price (column C), whereas the actual amount in H3 depends on the rounded selling price (column E). We should not reference column E in I2 because often, Solver cannot tolerate rounded amounts.
(It is "dumb luck" when Solver works despite the use of rounded amounts and other functions that cause "discontinuities" -- non-smooth variations as variables are changed.)
-----
However, considering how close the derived selling prices are to "cost", I reiterate that I suspect that "cost" should really be the selling price. So, in the second model (worksheet "quant only"), I allow only quantity to vary. This is the result:
Note the significant difference.
Disclaimer: There might be multiple solutions that are either the same or "good enough". I have not made any effort to explore that.