Forum Discussion
Using Solver to solve selling price and quantities to achieve the target amount
alvinro wrote: ``what is the best selling price for each unit and how much quantity``
First, you need to understand that this is not a simple problem. It falls into the category called "linear programming". And it often difficult to set up the constraints of the problem so that it can be solved.
Second, to that end, you need to define what you mean by "best". For example, do you require a minimum of 1 of each quantity? What is the maximum of each quantity? Do you have a minimum and maximum for the selling price of each item sold?
(Cost is not always the minimum selling price, unless of course you want to make that a constraint.)
(It seems odd that you can vary both. As a consumer, usually the selling price is out of our control. As a seller, usually the quantity is out of our control.)
For use with Solver, you might set up a target cell with a formula of the form
=(H2 - SUMPRODUCT(C2:C14, D2:D14))^2 , then specify Set ... To: Min in Solver.
Essentially, we want to minimize the absolute value of the difference -- unless you do not want to exceed the target amount. But Solver does not always work well with the ABS function. Squaring the difference is a way to accomplish the same thing without using ABS.
-----
PS.... I might add that in my experience, Solver does not do well with min-max problems with more than two variables. First, I can usually demonstrate with a counter-example that the result is __not__ a min or max. Second, often I just get an error from Solver -- either a complaint about the set-up, or a failure to find a solution. But both issues might simply be due to my ineffectiveness at setting up LP problems, in the first place.
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.