Business Travel & Expense Analytics: Policy Compliance, Per Diems & Savings

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Summarise a month of expense lines by category and by employee with SUMIF, calculate each category's share and find the largest category.
• Compute the nightly hotel rate of each trip, look up the city cap with a default for unlisted cities, and flag hotel stays above the cap and business-class flights the policy does not allow.
• Count the travel days of each trip, look up the city's daily rate and calculate the per diem with the first and last day paid at 75 %, then total it per employee.
• Calculate how many days ahead each ticket was booked, compare average fares by lead-time bucket, measure the relation with CORREL and plot it.
• Find claims submitted twice (same employee, date and amount), flag them for review, mark weekend claims and total the value that needs checking before payment.
• Calculate reimbursable miles (round trips doubled, the normal commute deducted, never below zero), the amount per trip and in total, and the distance above which a rental car is cheaper.
• Size two savings levers: what late flight bookings cost compared with early ones, and what stays in non-preferred hotels cost compared with the negotiated rate, then add them up.
• Total six months of T&E spend, compare it with the budget, show the category mix in a stacked column chart and highlight the overspent months with conditional formatting.