How Excel Helps You Land a Business Analyst Job Faster

Orders table: A OrderID | B CustomerID | C Region | D Amount | E Segment
Customers table: CustID | Segment

E2  Segment       =XLOOKUP(B2, Customers[CustID], Customers[Segment], "Not found")
F2  Large order?  =IF(D2>=10000, "Large", "Standard")

Summary cells:
  North revenue   =SUMIFS(Orders[Amount], Orders[Region], "North")
  Large orders    =COUNTIFS(Orders[Amount], ">=10000")
  Unmatched rows  =COUNTIF(Orders[Segment], "Not found")

import pandas as pd

orders = pd.DataFrame({"order_id": [1, 2, 3], "customer_id": ["C1", "C2", "C9"],
                       "region": ["North", "South", "North"], "amount": [12000, 4000, 7000]})
customers = pd.DataFrame({"customer_id": ["C1", "C2"], "segment": ["Retail", "Corporate"]})

merged = orders.merge(customers, on="customer_id", how="left")
merged["segment"] = merged["segment"].fillna("Not found")
print(merged.pivot_table(index="region", columns="segment", values="amount", aggfunc="sum", fill_value=0))
print("unmatched:", (merged["segment"] == "Not found").sum())

Assumptions (named cells, example values to change):
  Orders       = 4000      (orders per month)
  ManualMin    = 6         (minutes per order today)
  AutoMin      = 2         (minutes per order with automation)
  HourlyCost   = 300       (cost per staff hour)
  SetupCost    = 250000    (one-time automation cost)

Calculations:
  ManualCost   =Orders*ManualMin/60*HourlyCost
  AutoCost     =Orders*AutoMin/60*HourlyCost
  Saving       =ManualCost-AutoCost
  PaybackMonths =IFERROR(SetupCost/Saving, "No saving")

Scenario: build a Data Table varying Orders (2000-6000) against PaybackMonths.

def payback_months(orders, manual_min=6, auto_min=2, hourly_cost=300, setup_cost=250_000):
    saving = orders * (manual_min - auto_min) / 60 * hourly_cost
    return round(setup_cost / saving, 1) if saving > 0 else None

# Example inputs only; replace with real assumptions from stakeholders
for orders in (2000, 4000, 6000):
    print(f"orders {orders:5}: payback {payback_months(orders)} months")

Requirement tracker (Format as Table: Reqs)
Columns: ID | Title | Description | Stakeholder | Priority | Status | Owner | Due | Notes

Data validation:
  Priority  List: Must,Should,Could,Won't
  Status    List: New,Agreed,In build,Testing,Done

Conditional formatting:
  Status = "Done"            -> green fill
  Due < TODAY() and Status <> "Done" -> red text

Summary:
  Must not done  =COUNTIFS(Reqs[Priority], "Must", Reqs[Status], "<>Done")

import pandas as pd

reqs = pd.DataFrame({
    "id": ["REQ-1", "REQ-2", "REQ-3", "REQ-4"],
    "priority": ["Must", "Should", "Must", "Could"],
    "status": ["Done", "In build", "Agreed", "New"],
    "due": pd.to_datetime(["2026-03-01", "2026-03-20", "2026-03-05", "2026-04-01"]),
})
today = pd.Timestamp("2026-03-10")
print(pd.crosstab(reqs["priority"], reqs["status"]))
overdue = reqs[(reqs["due"] < today) & (reqs["status"] != "Done")]
print("overdue:", overdue["id"].tolist())

Case (practice): A retailer wants to know why repeat orders fell.

You receive: orders.xlsx and customers.xlsx
Tasks:
1. Combine the files with XLOOKUP; count unmatched rows
2. Pivot: repeat orders by region and month
3. Identify the segment with the biggest drop
4. Build a what-if: effect of a loyalty discount on repeat orders
5. One-page summary: question, 3 findings, 2 recommendations

Question: Why did repeat orders fall last quarter?

Findings:
1. The drop is concentrated in one region and segment
2. Delivery times rose in the same period (if data shows it)
3. Customers with one late delivery repeat less often

Recommendations:
1. Investigate delivery partner performance in that region
2. Test a small loyalty offer and measure repeat rate

Caveats: data covers one quarter; correlation is not causation

Week 1  Tables, XLOOKUP, SUMIFS, COUNTIFS
Week 2  Pivot tables, slicers, charts
Week 3  What-if models, data tables, goal seek
Week 4  Requirement tracker with validation and formatting
Week 5  Full case study with one-page summary