Challenge Scenario
In supply chain purchasing and procurement analysis, hardware manufacturing companies request price quotes from multiple competing component suppliers (Supplier A, B, and C) to find the most cost-effective deal.
To optimize product manufacturing margins, purchasing agents compare supplier bids, identify the lowest available price for each component, and calculate the maximum price spread across suppliers.
As a Purchasing Cost Analyst, your tasks are:
- Best Quote (Column E): Identify the lowest price among the three suppliers using the
MIN function across Columns B, C, and D (B2:D2).
- Max Variance (Column F): Calculate the price spread by subtracting the lowest quote from the highest quote (
MAX(B2:D2) - MIN(B2:D2)).
- Total Best Quotes (Cell B8): Calculate the sum of all winning quotes using
SUM.
- Lowest Single Component (Cell C8): Find the absolute lowest component price across the entire catalog using
MIN.
Your Sheet Layout
The vendor quote comparison sheet is organized into clear operational sections:
- Supplier Quotes (Columns A to D): Shows each hardware component name alongside the quoted unit prices from Supplier A, Supplier B, and Supplier C.
- Cost Optimization (Columns E & F): Where you determine the lowest quote (
MIN) and calculate the spread between the cheapest and most expensive supplier (MAX - MIN).
- Procurement Summary (Rows 7 & 8): Summarizes the total bill of materials using optimal quotes ($93) and identifies the cheapest single part ($11).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:D5 |
Component, Suppliers A-C |
Quote Matrix |
Component names and vendor bids ($11 to $48) |
Incoming supplier price quotations. |
E2:E5 |
Best Quote ($) |
Optimization |
=MIN(B2:D2) |
Finds the lowest price among Suppliers A, B, and C for each item. |
F2:F5 |
Max Variance ($) |
Optimization |
=MAX(B2:D2) - MIN(B2:D2) |
Measures the pricing difference between highest and lowest supplier bids. |
B8 |
TOTAL BEST QUOTES |
Summary |
=SUM(E2:E5) |
Sums the best quotes to find minimum assembly build cost ($93). |
C8 |
LOWEST SINGLE COMPONENT |
Summary |
=MIN(E2:E5) |
Finds the lowest price across all components ($11). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column E (E2:E5), find the lowest quote using =MIN(B2:D2).
2
In Column F (F2:F5), calculate quote variance using =MAX(B2:D2)-MIN(B2:D2).
3
In cell B8, calculate total of all best quotes using SUM(E2:E5).
4
In cell C8, find the single lowest component quote using MIN(E2:E5).
Solution & Formula Breakdown
Let's examine how the statistical MIN and MAX functions work in vendor analysis:
1. Finding the Minimum Price with MIN (E2:E5)
=MIN(B2:D2)
The MIN function evaluates all numeric cells in the horizontal range B2:D2 and returns the smallest number:
- Row 2 (OLED Display): Bids are $45, $42, $48 →
MIN selects 42.
- Row 3 (Lithium Battery): Bids are $18, $19, $16 →
MIN selects 16.
- Row 4 (Microcontroller): Bids are $12, $11, $14 →
MIN selects 11.
- Row 5 (Aluminum Casing): Bids are $25, $28, $24 →
MIN selects 24.
2. Calculating Supplier Spread (F2:F5)
=MAX(B2:D2) - MIN(B2:D2)
Subtracting the minimum from the maximum gives the potential savings variance:
- Row 2 (OLED Display):
48 - 42 = 6
- Row 3 (Lithium Battery):
19 - 16 = 3
- Row 4 (Microcontroller):
14 - 11 = 3
- Row 5 (Aluminum Casing):
28 - 24 = 4
3. Summary Rollups (B8 & C8)
- Total Best Quotes (Cell B8):
=SUM(E2:E5) sums 42 + 16 + 11 + 24, giving $93.
- Lowest Single Component (Cell C8):
=MIN(E2:E5) evaluates the best quotes and returns the smallest value, $11.
Notice that in row formulas like =MIN(B2:D2), we keep the cell references relative (no dollar signs) so that when we copy the formula down to row 3, it automatically checks row 3's suppliers (B3:D3).