Finding the Best Rate

[saiyamvora13]

I have a table where 4 people bid on a rate and I need to find the best reserve price that I can set to maximize my revenue.
[TABLE="width: 580"]
<tbody>[TR]
[TD]Proposition: At least one bidder (the "demand") must bid above this reserve price for the auction to clear, otherwise the product doesn't sell and we make $0 for that bar. If at least one bidder bids above the reserve price, the product will sell to the bidder who bids the highest price. That bidder will then pay the maximum of (the reserve price and the next highest bid).[/TD]
[/TR]
</tbody>[/TABLE]

Example My reserve price is $0.69

[TABLE="width: 440"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD][TABLE="width: 440"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Auction #[/TD]
[TD]Bobby[/TD]
[TD]Mike[/TD]
[TD]Vanessa[/TD]
[TD]Julie[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.32[/TD]
[TD]0.36[/TD]
[TD]0.77[/TD]
[TD]0.59[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.47[/TD]
[TD]0.71[/TD]
[TD]0.89[/TD]
[TD]0.64[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.25[/TD]
[TD]0.67[/TD]
[TD]0.34[/TD]
[TD]0.42[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 580"]
<colgroup><col></colgroup><tbody>[TR]
[TD]For auction 1, Vanessa wins and pays $0.69, because she has the highest bid, her bid is higher than the reserve price and the reserve price is higher than the next highest bid[/TD]
[/TR]
[TR]
[TD]For auction 2, Vanessa wins and pays $0.71, because she has the highest bid, her bid is higher than the reserve price, and the next highest bid is also higher than the reserve price[/TD]
[/TR]
[TR]
[TD]For Auction 3, nobody wins, because no one has a bid greater than the reserve price[/TD]
[/TR]
[TR]
[TD]THERFORE, total revenue for a $0.69 reserve price is $1.40 ($0.69 + $0.71 + $0.00)[/TD]
[/TR]
</tbody>[/TABLE]

Here is my table. I need to find the best reserve price overall to maximize my revenue

[/IMG]

Here is what I did
I found the best reserve price for each auction. using
=Large((B2,C2,D2,E2),1)-0.01
So the first row best reserve price would be $0.85
Then I took an Average of all of them and got $0.77.

I need help with a formula to find the amount paid if the reserve price is met and if there are two people who have bid higer than the reserve price the amount charged will be the 2nd highest number. Thank you for all your help

Here is the data if you would like to copy and paste in excel.

[TABLE="width: 715"]
<colgroup><col width="143" span="5" style="width:107pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 143"]1[/TD]
[TD="class: xl66, width: 143"] $ 0.36 [/TD]
[TD="class: xl66, width: 143"] $ 0.40 [/TD]
[TD="class: xl66, width: 143"] $ 0.86 [/TD]
[TD="class: xl66, width: 143"] $ 0.66 [/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66"] $ 0.53 [/TD]
[TD="class: xl66"] $ 0.80 [/TD]
[TD="class: xl66"] $ 1.00 [/TD]
[TD="class: xl66"] $ 0.72 [/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl66"] $ 0.38 [/TD]
[TD="class: xl66"] $ 0.59 [/TD]
[TD="class: xl66"] $ 0.23 [/TD]
[TD="class: xl66"] $ 0.36 [/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl66"] $ 0.08 [/TD]
[TD="class: xl66"] $ 0.86 [/TD]
[TD="class: xl66"] $ 0.91 [/TD]
[TD="class: xl66"] $ 0.37 [/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl66"] $ 0.30 [/TD]
[TD="class: xl66"] $ 0.70 [/TD]
[TD="class: xl66"] $ 0.74 [/TD]
[TD="class: xl66"] $ 0.85 [/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl66"] $ 0.59 [/TD]
[TD="class: xl66"] $ 0.14 [/TD]
[TD="class: xl66"] $ 0.07 [/TD]
[TD="class: xl66"] $ 0.70 [/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl66"] $ 0.88 [/TD]
[TD="class: xl66"] $ 0.28 [/TD]
[TD="class: xl66"] $ 0.65 [/TD]
[TD="class: xl66"] $ 0.44 [/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl66"] $ 0.49 [/TD]
[TD="class: xl66"] $ 0.88 [/TD]
[TD="class: xl66"] $ 0.11 [/TD]
[TD="class: xl66"] $ 0.21 [/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl66"] $ 0.14 [/TD]
[TD="class: xl66"] $ 0.04 [/TD]
[TD="class: xl66"] $ 0.68 [/TD]
[TD="class: xl66"] $ 0.94 [/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl66"] $ 0.14 [/TD]
[TD="class: xl66"] $ 0.07 [/TD]
[TD="class: xl66"] $ 0.16 [/TD]
[TD="class: xl66"] $ 0.22 [/TD]
[/TR]
</tbody>[/TABLE]

 

[saiyamvora13]

I would like to change my question a little bit. I just realized that I cannot use the average function here.
I need to find the best reserve price to maximize my revenue. Please let me know what formula to use here.

 

[jamcall]

I copied your data above starting in cell A3, in cell B2 is a reserve price. In F3 enter:
=IF(LARGE(B3:E3,1)>$B$2,IF(LARGE(B3:E3,2)>$B$2,LARGE(B3:E3,2),$B$2),0)

Drag this down as needed.

at the bottom of this new column sum the values above (i.e.: =SUM(F3:F12) ). Then use solver to find a Max for this cell by changing the value in B2.


HTH,
~ Jim

 

[saiyamvora13]

Thanks Jim,
I have never used the solver before, I just added it to excel, but I am not sure how to use it.
I added the column F3:F12
Now how do I find the best reserve price using solver?

 

[jamcall]

Thanks Jim,
I tried adding solver on my excel through ad ins, but I am unable to do so. Any chance you know of a way to solve this by just using a formula?

You could create a table and find the maximum.

Since bids are up to $1, vary your reserve in $0.01 increments up to that amount - in my example, I'd put 0 in F2 and 0.01 in F1 and drag these out to the right (101 columns)

In F3:
=IF(LARGE($B3:$E3,1)>F$2,IF(LARGE($B3:$E3,2)>F$2,LARGE($B3:$E3,2),F$2),0)
Drag this across, then down.
Sum the columns and look for the max...

Cheers,
~ Jim

 

[saiyamvora13]

I was able to add the solver, any chance you could explain on how I would use the solver? I some different combinations but did not get the desired result.
I would love to understand how to use the solver in this scenario.
Thank you.

 

[saiyamvora13]

You could create a table and find the maximum.

Since bids are up to $1, vary your reserve in $0.01 increments up to that amount - in my example, I'd put 0 in F2 and 0.01 in F1 and drag these out to the right (101 columns)

In F3:
=IF(LARGE($B3:$E3,1)>F$2,IF(LARGE($B3:$E3,2)>F$2,LARGE($B3:$E3,2),F$2),0)
Drag this across, then down.
Sum the columns and look for the max...

Cheers,
~ Jim

I used this solution and I got the answer as $0.86 which is incorrect since that does not lead to the maximum revenue.

 

[saiyamvora13]

Thanks alot Jim!! I figured it out using the solver!! Pretty neat tool.
Thank you for your help

 

[saiyamvora13]

Hi Jim,
Considering your expertise in excel , would you please be able to help me out here?

http://www.mrexcel.com/forum/excel-questions/967882-formula-help.html