Using Solver to solve selling price and quantities to achieve the target amount

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.