Return Maximum Gross/Net Proceeds from Auction

[xslim12]

Hi All,

I was asked to prepare an excel file that is able to calculate maximum proceeds from an auction of multiple assets. In short, company is liquidating all assets in a court process and we must track bidders for all assets. Some bidders will bid for entire assets while other bidders will bid for asset groups or individual assets. We need to spit out what returns the most dollars. I know there is a long way of doing this through formulas but am curious if there is an off-shelf macro out there for this. Unfortunately, I am not familiar with VBA and cannot create my own.

Thanks,

-Slim

 

[Logit]

.
Some of the code :

ption Explicit
Function getSheetWithDefault(name As String, Optional wb As Excel.Workbook) As Excel.Worksheet
        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If
        Application.ScreenUpdating = False
        
        If Not sheetExists(name, wb) Then
            wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)).name = name
        End If
        
        Set getSheetWithDefault = wb.Sheets(name)
        
        Application.ScreenUpdating = True
        
End Function
Function sheetExists(name As String, Optional wb As Excel.Workbook) As Boolean
        Dim sheet As Excel.Worksheet


        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If


        sheetExists = False
        For Each sheet In wb.Worksheets
            If sheet.name = name Then
                sheetExists = True
                Exit Function
            End If
        Next sheet
End Function
Sub DeleteSheets1()
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.name <> "Main List" Then
            ws.Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    CreateSheets
End Sub
Sub CreateSheets()
        Dim MyCell As Range
        Dim MyRange As Range
        Dim ws As Worksheet


        Application.ScreenUpdating = False
        
        Set MyRange = Sheets("Main List").Range("A6")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))


        For Each MyCell In MyRange
            If Sheets(Sheets.Count).name <> MyCell.Value Then
                Set ws = getSheetWithDefault(MyCell.Value)
            End If
        
        Next MyCell
        
        Application.ScreenUpdating = True
        Call CopyInfo
    End Sub

You can download the workbook here : https://www.amazon.com/clouddrive/share/LS0oXKxuZNnWdnBi41JhgNCCSRN22FMmmQqYpMo02SL

 

[xslim12]

.
Some of the code :

ption Explicit
Function getSheetWithDefault(name As String, Optional wb As Excel.Workbook) As Excel.Worksheet
        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If
        Application.ScreenUpdating = False
        
        If Not sheetExists(name, wb) Then
            wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count)).name = name
        End If
        
        Set getSheetWithDefault = wb.Sheets(name)
        
        Application.ScreenUpdating = True
        
End Function
Function sheetExists(name As String, Optional wb As Excel.Workbook) As Boolean
        Dim sheet As Excel.Worksheet


        If wb Is Nothing Then
            Set wb = ThisWorkbook
        End If


        sheetExists = False
        For Each sheet In wb.Worksheets
            If sheet.name = name Then
                sheetExists = True
                Exit Function
            End If
        Next sheet
End Function
Sub DeleteSheets1()
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each ws In Application.ActiveWorkbook.Worksheets
        If ws.name <> "Main List" Then
            ws.Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    CreateSheets
End Sub
Sub CreateSheets()
        Dim MyCell As Range
        Dim MyRange As Range
        Dim ws As Worksheet


        Application.ScreenUpdating = False
        
        Set MyRange = Sheets("Main List").Range("A6")
        Set MyRange = Range(MyRange, MyRange.End(xlDown))


        For Each MyCell In MyRange
            If Sheets(Sheets.Count).name <> MyCell.Value Then
                Set ws = getSheetWithDefault(MyCell.Value)
            End If
        
        Next MyCell
        
        Application.ScreenUpdating = True
        Call CopyInfo
    End Sub

You can download the workbook here : https://www.amazon.com/clouddrive/share/LS0oXKxuZNnWdnBi41JhgNCCSRN22FMmmQqYpMo02SL

Appreciate the post but, unless I am interpreting this incorrectly, it is not what I was thinking. Data would be organized like this (https://drive.google.com/open?id=1yjAiz3t_Ars5zMXZO0-J0Q_MQKBLavO4) and then determine which combination of bids returns max proceeds. It is able to work through the choice of two bids on one item. Assume bids are locked and if bidder cannot buy one of the units they will not move forward.

 

[Logit]

.
How many individual asset items will be auctioned ?

How many bidders will there potentially be ?

 

[shg]

For selecting the best pair of bidders, it's pretty easy with formulas:

	A​	B​	C​	D​	E​	F​
1​	Item \ Bidder​	1​	2​	3​	4​	5​
2​	1​
3​	2​
4​	3​
5​	4​	94​
6​	5​
7​	6​		58​	72​
8​	7​	96​		76​
9​	8​
10​	9​		79​	84​
11​	10​			77​
12​	11​	97​			87​
13​	12​		99​
14​	13​
15​	14​					72​
16​	15​	52​
17​	16​	51​	96​
18​	17​
19​	18​				98​	90​
20​	19​			96​
21​	20​			73​		79​
22​		390​	332​	478​	185​	241​
23​
24​	1​
25​	2​
26​	3​
27​	4​		517​	663​
28​	5​	631​	573​

The numbers in the bottom table appear when a pair of bidders have no overlapping items; the max value is highlighted (bids 3 & 4).

The formula in B24 is

=IF((B$1 >= $A24) + (SUMPRODUCT(B$2:B$21, INDEX($B$2:$F$21, 0, $A24)) > 0), "", SUM(B$2:B$21, INDEX($B$2:$F$21, 0, $A24)))

I think cases where three or more bidders have no overlapping items will be few.

 

[xslim12]

For selecting the best pair of bidders, it's pretty easy with formulas:

	A​	B​	C​	D​	E​	F​
1​	Item \ Bidder​	1​	2​	3​	4​	5​
2​	1​
3​	2​
4​	3​
5​	4​	94​
6​	5​
7​	6​		58​	72​
8​	7​	96​		76​
9​	8​
10​	9​		79​	84​
11​	10​			77​
12​	11​	97​			87​
13​	12​		99​
14​	13​
15​	14​					72​
16​	15​	52​
17​	16​	51​	96​
18​	17​
19​	18​				98​	90​
20​	19​			96​
21​	20​			73​		79​
22​		390​	332​	478​	185​	241​
23​
24​	1​
25​	2​
26​	3​
27​	4​		517​	663​
28​	5​	631​	573​

<tbody>
</tbody>

The numbers in the bottom table appear when a pair of bidders have no overlapping items; the max value is highlighted (bids 3 & 4).

The formula in B24 is

=IF((B$1 >= $A24) + (SUMPRODUCT(B$2:B$21, INDEX($B$2:$F$21, 0, $A24)) > 0), "", SUM(B$2:B$21, INDEX($B$2:$F$21, 0, $A24)))

I think cases where three or more bidders have no overlapping items will be few.

This was helpful but there are going to be scenarios where 4+ bidders may have overlapping items. A majority of bidders in these scenarios are corps but not individuals. So you see bids on groups of assets or a majority of a type of asset. We currently have 6 bidders in this but I do expect there to be 10+.

How can this formula be tweaked to adjust for 4+ overlapping bids? Given this runs on a grid, doesn't seem like it may be possible?

-Slim

 

[xslim12]

100+ assets with 10+ bidders. I left a reply above to shg highlighting how the dynamics of this auction will work.

 

[shg]

It would be easy enough to do in code, but if any given bidder either gets everything they bid for or nothing, it seems unlikely to me that any combination of three non-intersecting bidders exists. Your example has no non-intersecting pair.

 

[Logit]

.
I understand this part ("100+ assets with 10+ bidders.").

I do not understand the meaning of your comments in Post #8 to SHG.


My suggestion was similar to SHG's but slightly upended, like this :

	A​	B​	C​	D​	E​	F​
1​	Totals :​	$391.00 ​	$334.00 ​	$481.00 ​	$189.00 ​	$246.00 ​
2​	Item \ Bidder​	1​	2​	3​	4​	5​
3​	1​	​	​	​	​	​
4​	2​	​	​	​	​	​
5​	3​	​	​	​	​	​
6​	4​	94​	​	​	​	​
7​	5​	​	​	​	​	​
8​	6​	​	58​	72​	​	​
9​	7​	96​	​	76​	​	​
10​	8​	​	​	​	​	​
11​	9​	​	79​	84​	​	​
12​	10​	​	​	77​	​	​
13​	11​	97​	​	​	87​	​
14​	12​	​	99​	​	​	​
15​	13​	​	​	​	​	​
16​	14​	​	​	​	​	72​
17​	15​	52​	​	​	​	​
18​	16​	51​	96​	​	​	​
19​	17​	​	​	​	​	​
20​	18​	​	​	​	98​	90​
21​	19​	​	​	96​	​	​
22​	20​	​	​	73​	​	79​
23​	​	​	​	​	​	​

Having the totals at the top makes it easier to view as you don't have to continually scroll down the sheet to view.
But now I am not certain this layout is comparable to what you have envisioned. If neither of these suggestions
meets your needs, please put an example together how you see the sheet laid out. We can then work from there.

 

[xslim12]

It would be easy enough to do in code, but if any given bidder either gets everything they bid for or nothing, it seems unlikely to me that any combination of three non-intersecting bidders exists. Your example has no non-intersecting pair.

The issue is these things can end up multiple ways. The individual asset bids are more likely to come in during the live auction. We have currently received preliminary bids from strategics and liquidators. I think its possible for three to four bidders coming out as winnings just depending on the bids received. Unfortunately, I do not think my data set reflects that.

-Slim