Ranking Tender Offers

Many companies periodically run tenders to gather offers from several prospective B2B partners and choose the best ones. The problem is that the results of a tender come as a set of files, one per prospective partner. How can you easily build a single file that shows the best offers in every tender category?

Let’s take the following example for concreteness. Your company finished running a tender for shipping products to stations located in different regions of a country and got offers from several hauliers. To make things simple, assume that tender specifications stated that each haulier had to provide a single price for every region. To make further analysis easier, your company distributed the following Excel table and requested hauliers to fill it in:

ranking-tender-offers-tendertableempty

In reality, the table would be substantially longer for many reasons I’ll not state here (one of those is a more detailed division of a country into regions), so that doing the task manually would require several hours of monotonous work and a large possibility to make a mistake somewhere. For the purposes of this example, let’s keep things simple and just illustrate the approach.

Your company got offers from five hauliers: A, B, C, D and E. Not all hauliers gave quotes for every region, so the first task is to understand how many offers there are and whether there are regions with no offers. The latter is a particularly important topic, as it would require running a new region-specific tender, which will take additional time.

Let’s create an Excel table called TenderResults, which has the same columns as the table attached to the tender and an additional column storing the name of an haulier. Then just open every Excel file received from hauliers, set the filter to show only those rows where price is defined and copy-paste the information from there to our tender results table, addingthe name of the haulier manually:

Haulier Region Price
A R1 63
A R2 66
A R3 63
A R4 77
A R5 76
B R6 76
B R7 88
B R8 90
B R10 91
C R4 61
C R6 81
D R1 54
D R2 53
D R3 70
D R4 61
D R7 83
E R1 47
E R2 69
E R6 73
E R8 95

Then let’s create a summary table called TenderSummary simply listing all ten regions. Recall that we’re first interested in how many offers we’ve got for each region, which is a standard case for a COUNTIF formula, so I’ll not even write the formula out. Then set conditional formatting to highlight the cells with zero offers:

Ranking Tender Offers - Results-1.PNG

We see that there are no more than 3 offers per region and region R9 has zero offers.

Then the core task is to rank the offers. From the first sight, the task is easy: just take the TenderResults table, add a Rank column and use a simple on-the-fly filter to get region-specific rank of a price quote:

{=RANK.AVG([Price], IF([Region] = [@Region], [Price], “”))}

Well, the problem is that you’ll get #VALUE! in the whole column, which means guess what? That the RANK function doesn’t work with arrays.

That’s why this post actually exists. Let’s see what’s an alternative.

As it frequently is, the technical realization is relatively simple when you understand the idea. The idea is that we still want to have a column showing the rank of an offer in the TenderResults table, but we’re not going to use the RANK function. If you think about a rank then what is it? What does it mean that an offer ranks first or second?

If an offer ranks nth, it means that it has (n – 1) offers that are better than it.

In other words, the first best offer has zero offers that are better than it, the second best has one offer that’s better and so on.

As soon as you understand it, the formula for the Rank column is straightforward:

=COUNTIFS([Region], [@Region], [Price], “<” & [@Price]) + 1

But that’s not the whole story. The problem is that there might be ties — cases when several hauliers submit the same price in the same region. In this case, the above formula will give the same rank to all tied bids, which will make it problematic to look them up correctly afterwards (the look-up will get the first of these entries only).

This is the case here: note that A bid 77 in R4, but C and D both bid 61 in R4, which will give them rank 1. Thus, with the above formula, C and D will have rank 1 and A will have rank 3.

The solution here is to rank C as 1, rank D as 2 and keep A ranked third.

The implementation of this solution requires to create a technical table named Hauliers defining haulier IDs:

Ranking Tender Offers - Hauliers.PNG

Then add column Haulier ID to TenderResults that looks up values in the Hauliers table (preferably, using the INDEX–MATCH approach).

Then the Rank column will have the following formula:

=COUNTIFS([Region], [@Region], [Price], “<” & [@Price]) + COUNTIFS([Region], [@Region], [Price], [@Price], [Haulier ID], “<” & [@[Haulier ID]]) + 1

The second component adds the number of cases when the price and region are the same as the current price and region but haulier ID is smaller than the current haulier ID.

Here’s what we have as a result:

ranking-tender-offers-tenderranks

Note that C is ranked first in R4 and D ranked second, as expected.

Now let’s first finish creating the structure of the summary table by adding columns for haulier names and prices for the three best offers — O1, O2 and O3, where O1 is the best offer for a given region:

Ranking Tender Offers - Results-2.PNG

The formulae are straightforward. I’ll just show the approach on the example of the best haulier. There are a few things to keep in mind:

  • Remember to check how many offers were received so that the formula outputs an empty cell instead of an error when there are no offers of rank n
  • You can use SUMIFS to get the result in the On Price columns, but can’t use it to get the respective haulier name — you’re thus forced to look up data by several columns (region and rank), so that you start by creating a UID column in the TenderResults table with a formula =[@Region] & “-” & [@Rank]

Taking all that into account, here’s the formula that determines the haulier with the best offer:

=IF(VALUE(MID(TenderSummary[[#Headers],[O1 Haulier]:[O3 Price]], 2, 1)) <= [@[Offers Received]], INDEX(TenderResults[Haulier], MATCH([@Region] & “-” & MID(TenderSummary[[#Headers],[O1 Haulier]:[O3 Price]], 2, 1), TenderResults[UID], 0)), “”)

The fragment TenderSummary[[#Headers],[O1 Haulier]:[O3 Price]] may seem a bit cumbersome for the first few seconds, but it’s simply a reference to a region of headers of the columns we’ll be filling in. I’m using implicit intersection to skip referring to the address of the header cell and also skip changing the reference to the name of the header cell in every column. Now, if cell C1 contains text O1 Haulier then MID(C1, 2, 1) will return 1, which is the rank of hauliers listed in this column. Thus, the MID() function in the above formula returns the rank whose results are displayed in the current column.

So the first part of the IF-statement checks whether an offer with this rank actually exists, and if it does, it looks up an offer in a given region and with a given rank from the TenderResults table.

It remains to copy the formulae in the O1 Haulier column to the O1 Price column and substitute TenderResults[Haulier] by TenderResults[Price]. Then copy the contents of both O1* columns to the remaining columns. The result will be:

Ranking Tender Offers - Results-3.PNG

Note that the ties in region R4 are displayed correctly.

Advertisements

One thought on “Ranking Tender Offers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s