Business Travel & Expense Analytics: Policy Compliance, Per Diems & Savings
Analyse a consulting firm's travel and expense data hands-on: totals by category and employee, hotel caps by city and class-of-travel checks, per diems with 75 % travel days, booking lead time vs airfare, duplicate and weekend claims, mileage with a rental break-even, savings from advance booking and preferred hotels, and a T&E dashboard with a chart and red flags. Fictional company, illustrative rates.
The first lesson is free — no account needed.
About this course
A business travel and expense analytics course that audits the T&E data of one fictional consulting firm, Brackenridge Consulting, in a spreadsheet, for finance and accounts payable staff, travel managers, controllers and internal auditors. You total expense lines by category and by employee with SUMIF, check hotel rates against city caps with a default for unlisted cities and flag business-class flights the policy does not allow, calculate per diems with the first and last travel day paid at 75%, compare airfares by booking lead time with CORREL and a scatter chart, find duplicate claims with COUNTIFS keys and flag weekend claims, work out mileage with the commute deducted and a rental car break-even, size the savings from advance booking and preferred hotels, and build a T&E dashboard with budget variance and a stacked chart. Fictional company, illustrative rates and policies. The first lab is free.
Lessons
- Expense Report Totals by Category & EmployeeFree
- Policy Compliance: Hotel Caps by City and Class of Travel Pro
- Per Diems: Travel Days, City Rates and 75 % Partial Days Pro
- Booking Lead Time vs Airfare: Buckets, CORREL and a Scatter Pro
- Duplicate & Suspicious Claims: COUNTIFS Keys and Weekend Flags Pro
- Mileage Reimbursement: Round Trips, Commute Deduction and a Rental Break-Even Pro
- Savings Opportunities: Advance Booking and Preferred Hotels Pro
- T&E Dashboard: Monthly Totals, Budget Variance, a Stacked Chart and Red Flags Pro