Key Topics & Core Concepts

Assignment Overview & Deliverables

In this practice lab, you evaluate rental booking performance by writing nested IF statements, constructing compound Boolean tests, and calculating tiered loyalty discounts for Perfect Vacation Rentals.

Step-by-Step Instructions

Step 1: Open Starter & Review Booking Data

  1. Download and open PerfectRentals-04.xlsx in Excel.
  2. Click Enable Editing if opened in Protected View.
  3. Save the workbook as [your initials] Practice12_NestedIF.xlsx.
  4. Click the Booking Analysis tab and review columns A through F containing Booking ID, Guest Name, Property Type, Nights Booked, and Total Charge.

Step 2: Build a 3-Tier Nested IF Statement

  1. Click cell G4 (Loyalty Category).
  2. Enter a nested IF formula based on Nights Booked (column D):
    • If Nights $\ge$ 14 → "Platinum"
    • If Nights $\ge$ 7 → "Gold"
    • Otherwise → "Standard"
  3. Type: =IF(D4>=14, "Platinum", IF(D4>=7, "Gold", "Standard")) and press Enter.
  4. Double-click the fill handle on cell G4 to copy the formula down through cell G30.

Step 3: Compound Logical Evaluation (AND / OR)

  1. Click cell H4 (Promotional Discount Eligibility).
  2. A booking qualifies for a special promotion if Property Type is "Cabin" OR Total Charge exceeds $2,500: =IF(OR(C4="Cabin", E4>2500), "Eligible", "Standard Rate").
  3. In cell I4 (VIP Upgrade Flag), write a formula that tests if Loyalty Category is "Platinum" AND Total Charge is greater than $3,000: =IF(AND(G4="Platinum", E4>3000), "Complimentary Upgrade", "No").
  4. Copy both formulas down through row 30.

Step 4: Calculate Tiered Discount Percentage with IFS

  1. Click cell J4 (Discount Rate).
  2. Use the modern IFS function to apply tiered rates: =IFS(G4="Platinum", 0.15, G4="Gold", 0.10, G4="Standard", 0.0).
  3. Format column J as Percentage with zero decimal places (0%).
  4. In column K (Final Net Charge), enter: =E4*(1-J4) and copy down through row 30.

Step 5: Number Formatting & Save Workbook

  1. Format columns E and K as Currency ($#,##0.00).
  2. Center-align columns G, H, I, and J.
  3. Auto-fit all column widths to prevent truncation errors.
  4. Save your completed workbook.

Practice exercises are formative and prepare you for homework submissions. Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Skill Area Verification Target
Nested IF Construction Multi-tier conditional classifications correctly structured without syntax errors.
Boolean AND / OR Compound condition wrappers properly integrated into conditional outcome tests.
Modern IFS Syntax Clean IFS expression evaluated without nested parentheses clutter.
Presentation Polish Proper currency formatting and clean layout without column truncation.