BM101.PUR

⏰Estimated Practice Time: 30 Minutes

Objective

  • Learn how to create a Supplier Price Comparison Table to evaluate total costs from multiple suppliers and identify the most cost-effective option using Excel formulas and formatting.

Scenario: Supplier Price Comparison

  • Set Up Your Comparison Table:

    • Open Excel and create the following headers in Row 1:

      • A1: "Supplier" | B1: "Product Cost" | C1: "Shipping Fee" | D1: "Total Cost"

    • Format the headers:

      • Select cells A1:D1, click Bold, increase the font size to 12, and apply a light fill color for better clarity.

  • Input Sample Supplier Data:

    • Starting in Row 2, enter the following sample data:

  • Calculate the "Total Cost" for Each Supplier:

    • In cell D2 (under "Total Cost"), enter the formula to sum Product Cost and Shipping Fee:

      =B2+C2

    • Press Enter.

    • Copy the formula down by dragging the fill handle from D2 to D5.

  • Format the Costs as Currency:

    • Highlight cells B2:D5.

    • Go to Home → Number Group → Currency or use the shortcut Ctrl + Shift + $.

  • Identify the Lowest Total Cost:

    • Select cells D2:D5 (the "Total Cost" column).

    • Go to Home → Conditional Formatting → Highlight Cell Rules → Equal To.

    • Enter the formula to highlight the minimum value:

      =MIN($D$2:$D$5)

    • Choose a light green fill to make the lowest cost stand out.

  • AutoFit Columns for Readability:

    • Double-click the column separators between A, B, C, and D to resize the columns for a clean look.