Excel Solver Trouble

[mvmoore]

Hello everyone! I am hoping to find some help. I am a student running solver to try and find the optimal price for a fictional product.
I somehow need to incorporate a price of demand elasticity formula into a fictional invoice before running solver to find the optimal price.

I am at my wits end, stuck totally in the mud. I don't know how to incorporate this formula into the invoice so that solver returns useful values.

Here is the problem:

Julie assumes that for the product, the price elasticity of demand is -2.0 (for every 10 percent increase in in sales price, she expects sales to drop by 20 percent). Assume that the company must manufacture at least 60,000 units but no more than 150,000. Using the values from the Status Quo scenario as a basis, run Solver to calculate the optimal price for the company's product to maximize net income. Save the solver model on the worksheet.

(New Quantity – Old Quantity)/(Old Quantity)= (New Price – Old Price)/(Old Price)* PEoD

Any suggestions?

[MrShorty]

This looks a lot like a homework exercise, so I don't want to just give the answer. IN the spirit of coaching you through this problem, I would start by noting that Solver asks for 4 basic things:
1) A set target cell -- the main cell you want to find
2) What do you want for the target cell: minimum, maximum, or a specific value.
3) By changing cell(s) -- what cell(s) should Solver change to find the desired value of the target cell.
4) Subject to any constraints on any cells/values in the spreadsheet.

I often start these kinds of problems by clearly identifying each of those cells/values in my problem statement and/or spreadsheet.

Once I have identified the main Solver elements for my problem statement, the next big step is to correctly connect the by changing cells to the target cell. Some of this is math outside of Excel -- correctly identifying and understanding the math relationships between the variables of the problem, often including some algebra as needed. Some of this is spreadsheet programming -- how to program the math relationships into the spreadsheet. I suspect that you are kind of stuck on this step, as I do not see the quantity "price elasticity of demand" anywhere in the spreadsheet or in any of the formulas in the spreadsheet.

As a coach, I have two questions for you at this point:
Can you identify the 4 parts of the Solver model in your spreadsheet and math relationship?
Can you identify the calculation steps in going from your by changing cells to get to the target cell? How does the equation cited in your OP fit into this calculation sequence?

[mvmoore]

I'll take it! I am definitely not looking for an answer, simply some tips and guidance.

So the objective is net income max.
The changing variable cell is unit price.
The constraints are units produced >= 150000 and <=6000

I know the equation must be inserted into the units sold cell. However when I run the equation it gives me a negative number since the elasticity of demand is -2.

The equation I am using is Units Sold =((New Price – Old Price)/(Old Price))*-2* Old Quantity+Old Quantity

[MrShorty]

However when I run the equation it gives me a negative number

Do you have an example set of values that make that function return a value less than 0? If new price/unit is the same as old price/unit, it should give old quantity. When I test it, I have to use a new price >~277 in order for units sold to be less than 0.