Finding the Best Rate

saiyamvora13

New Member
Joined
Apr 3, 2015
Messages
32
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.
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).

<tbody>
</tbody>

Example My reserve price is $0.69

Auction #BobbyMikeVanessaJulie
10.320.360.770.59
20.470.710.890.64
30.250.670.340.42

<colgroup><col span="5"></colgroup><tbody>
</tbody>

<colgroup><col span="5"></colgroup><tbody>
</tbody>
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
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
For Auction 3, nobody wins, because no one has a bid greater than the reserve price
THERFORE, total revenue for a $0.69 reserve price is $1.40 ($0.69 + $0.71 + $0.00)

<colgroup><col></colgroup><tbody>
</tbody>

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

wsv1pf.png
[/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.

1 $ 0.36 $ 0.40 $ 0.86 $ 0.66
2 $ 0.53 $ 0.80 $ 1.00 $ 0.72
3 $ 0.38 $ 0.59 $ 0.23 $ 0.36
4 $ 0.08 $ 0.86 $ 0.91 $ 0.37
5 $ 0.30 $ 0.70 $ 0.74 $ 0.85
6 $ 0.59 $ 0.14 $ 0.07 $ 0.70
7 $ 0.88 $ 0.28 $ 0.65 $ 0.44
8 $ 0.49 $ 0.88 $ 0.11 $ 0.21
9 $ 0.14 $ 0.04 $ 0.68 $ 0.94
10 $ 0.14 $ 0.07 $ 0.16 $ 0.22

<colgroup><col width="143" span="5" style="width:107pt"> </colgroup><tbody>
</tbody>
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top