Excel Solver to minimize price while maximizing revenue

**tkdlax** · 05-23-2012, 06:18 PM

I have a problem where I am trying to use linear programming (and the solver in excel) to help me create a bid form.

In this bid, we have a service code that is performed various times in a year. I am looking for excel to help me find the lowest cost for that service code (subject to constraints) that in combination will produce the maximum revenue.

To further demonstrate, I have a set of the following:

McodePrice1 (service code) * frequency1 = revenue1
McodePrice2 * frequency2 = revenue2
mcodePriceX * frequencyX - revenueX

McodePrice 1 cannot exceed $25 and cannot be lower than $1
McodePrice 2 cannot exceed $24 and cannot be lower than $1
McodePrice X cannot exceed $a and cannot be lower than $b

I am looking to maximize the aggregate revenue of revenues 1-X while minimizing each McodePrice 1-X.

Any help is greatly appreciated!

**tigeravatar** · 05-23-2012, 07:04 PM

tkdlax,

Welcome to the forum!
In your post, you state you are using linear programming. If everything is linear, then we can use some basic algebra to find what you're looking for.

A linear equation is y=m*x+b

To accurately demonstrate model this scenario:
b would be the number of items sold if the product was free
m would be the estimated decline in sales for every 1$ increase in price
x would be the price in 1$ increments
y would be the amount of units sold (the frequency as you called it)

We need to introduce a new variable, r (for revenue). r=y*x (revenue = units sold * price)
So if r=y*x and y=m*x+b then we can substitute y for it's equation counterpart:

r=(m*x+b)*x
r=m*x^2+b*x

Now we can use the peak location of a quadratic to extract the best revenue:
max r = -b/(2*m)

**tkdlax** · 05-23-2012, 07:23 PM

thanks for the welcome!

OK, my problem here becomes the introduction of variables I don't know. I don't the number of services that would be performed if the price were zero, and I don't know my slope (estimated decline in services performed for every $1 increase).

That being said, what method should I be using to maximize one result while minimizing the other? I won't have access to any other exogenous variables other than those listed here, but you have got me thinking about creating endogenous description variables...