Supplier Management

ACH Ranking

This report displays an overview of cost and number of transactions for Air, Hotel and Car activity in numeric and graphic formats. The dynamic form of this report allows the user to choose the settings that will best suite his or her needs. Options include the ability to view air, car, or hotel transactions, choose domestic, international or all transactions, and select the number of vendors to display. Rankings are based on cost.

Report Category: Supplier Management

Report Format: Excel Design

Prompts:Date Range: Month to date

Currency code

Selectors:

  • Type (Air, Car, Hotel)
  • International/Domestic
  • Rank

Questions answered with this report include:

  • Overall spend and bookings for air, car, and hotel broken out by vendor, domestic and/or international
  • Average cost for airline ticket, car rental, or hotel broken out by vendor, domestic and/or international
  • Average length of hotel stay or car rental for vendors broken out by domestic and/or international
  • Total number of transactions for air, car, and hotel broken out by vendor, domestic and/or international
  • Savings and loss for air tickets broken out by vendor, domestic and/or international

Data Fields and calculations displayed in this report:

FIELD NAME / DESCRIPTION / CALCULATION
Airline Vendor Fields
(Includes arc, non arc, refund, and exchange transactions,
Excludes voided transactions)
Rank / Sorted by Cost. / N/A
Airline Name / Name of airline ticket is reported against. / N/A
Num Tickets / Total number of tickets using True Ticket Count (see definitions). / Sum (True Ticket Count)
Cost / Total ticket cost. / Sum (Ticket Amt)
Percent to Total / Percentage of cost to the total. / % to Total (Ticket Amt)
Avg Cost / Average cost per ticket. / Sum (Ticket Amt / Num tickets)
Savings / Amount saved by not booking the full fare. / Sum (Full fare – Ticket cost)
Loss / Amount lost in savings. / Sum (Tkt Amt – lowest fare offered)

ACH Ranking continued

FIELD NAME / DESCRIPTION / CALCULATION
Car Vendor Fields
(Excludes voided transaction)
Rank / Sorted by Cost. / N/A
Car Chain / Name of car vendor reporting against. / N/A
Num Cars / Total number of cars rented. / Sum (Number Cars)
Num Days / Total number of days cars were rented. / Sum (Car rental days) x (Number Cars)
Cost / Total cost for car rentals. / Sum (Car rental days) x (Number Cars) x (Car daily rate)
Percent to Total / Percentage of cost to the total. / % to Total (Car Cost)
Avg Rental / Average number of day’s rental. / Sum (Car days / Number cars)
Avg Cost / Average cost per day for car rentals. / Sum (Car cost / Numbercar days)
Hotel Chain Fields
(Excludes voided transaction)
Rank / Sorted by Cost. / N/A
Chain Name / Name of hotel chain reporting against. / N/A
Num Rooms / Number of rooms booked. / Sum (Number rooms)
Num Nights / Number of room nights booked. / Sum (Hotel nights) x (Number rooms)
Cost / Total cost. / Sum (Hotel nights) x (Number rooms) x (Hotel nightly rate)
Percent to Total / Percentage of cost to total. / % to Total (Hotel Cost)
Avg Stay / Average length of stay per room. / Sum (Num Nights / Num Rooms)
Avg Cost / Average cost per night. / Sum (Hotel Cost / Num Nights)

Sample Report – Airline Ranking

Car Ranking

Hotel Ranking

ACH Summary Comparison

This report compares two date periods and displays month over month spend totals for Air, Car and Hotel. This report would be useful when comparing last year’s totals for a specific month or time frame to the current time frame.

Report Category: Program Management

Report Format: PDF

Prompts:Date Range: Prior Year to Date (YTD)

Date Range: Year to Date (YTD)

Currency code

Questions answered with this report include:

  • Total spend, month by month for two date periods for Air, Car and Hotel
  • Overall, spend for each period for Air, Car and Hotel
  • Total spend for X month in previous year compared to total spend for same month in current year for Air, Car and Hotel
  • Change in Air, Car and Hotel spend month by month for two periods in history

Data Fields and calculations displayed in this report:

FIELD NAME / DESCRIPTION / CALCULATION
Air Fields
(Includes ARC, non-ARC, refund, and exchange transactions.
Excludes voided transactions)
Air Per 1 / Total airline ticket cost for current year entry. / Sum (Ticket Amt) from period 1
Air Per 2 / Total airline ticket cost for prior year entry. / Sum (Ticket Amt) from period 2
Car Fields
(Excludes voided transactions)
Car Per 1 / Total car rental cost for current year entry. / Sum (Car days) x (Numbercars) x (Car daily rate) from period 1
Car Per 2 / Total car rental cost for prior year entry. / Sum (Car days) x (Numbercars) x (Car daily rate) from period 2
Hotel Fields
(Excludes voided transactions)
Hotel Per 1 / Total hotel cost for current year entry. / Sum(Hotel nights) x (Number rooms) x (Nightly rate) from period 1
Hotel Per 2 / Total hotel cost for prior year entry. / Sum(Hotel nights) x (Number rooms) x (Nightly rate) from period 2

Sample Report – Air Car Hotel Summary Comparison

Air Market Analysis

The Air Market Analysis dashboard provides an overview of key elements about the air program and trends them over a specified period. The dashboard also shows the Top 100 air market pairs with a breakdown of Top 5 airlines by market

Report Category:Supplier Management

Report Format: Xcelsius

Prompts:Date Range:Current MTD (Month to Date)

Current YTD (Year to Date)

Questions answered with this report include:

  • Total Spend, average cost and number of trips per market pair
  • Top market pairs traveled
  • Top airlines used to travel within the top market pairs

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
Rank / Assigned based on the number of flights (where 1=highest). / N/A
Market / Airline market pairs (origination and destination). / N/A
# of Flights (market pairs) / Total number of tickets using True Ticket Count (see definitions). / Sum (True Ticket Count)
% of Flights (market pairs) / Percentage of flights within the specific market pair or on the specific airline. / # of Flights (market pairs)/Total # of Flights
Flight Cost (market pairs) /
  • Pro-rated calculation based on the minute rule (see definitions).
/ (MIN Segment Mileage / MIN Ticket Mileage) * Ticket Amount
% of Flight Cost (market pairs) / Percentage of flight cost within the specific market pair or on the specific airline. / Flight Cost (market pairs)/Total Flight Cost
Average CPM (cost per mile) (market pairs) / Average cost per mile for the market or the airline. / Flight Cost (market pairs)/Number of Miles
Airline / Top 5 Airlines used for the top Markets / N/A
# of Flights (airline carrier) / Total number of tickets using the count allocations (see definitions). / Sum (True Ticket Count)

Air Market Analysis continued

FIELD NAME / DESCRIPTION / CALCULATION
% of Flights (airline carrier) / Percentage of flights within the specific market pair or on the specific airline. / #of Flights (airline carrier)/Total # of Flights
Flight Cost (airline carrier) /
  • Pro-rated calculation based on the minute rule (see definitions).
/ (MIN Segment Mileage / MIN Ticket Mileage) * Ticket Amount
% of Flight Cost (airline carrier) / Percentage of flight cost within the specific market pair or on the specific airline. / Flight Cost (airline carrier)/Total Flight Cost
Average CPM (cost per mile) (airline carrier) / Average cost per mile for the market or the airline. / Flight Cost (airline carrier)/Number of Miles
Total Segment Cost for Period (specific period) /
  • Pro-rated calculation based on the minute rule (see definitions).
/ (MIN Segment Mileage / MIN Ticket Mileage) * Ticket Amount
Total # of Flights (specific period) / Total number of tickets using True Ticket Count (see definitions). / Sum (True Ticket Count)
Total Segment Mileage (specific period) / Total segment mileage for the current month entered. / Sum (Minimum segment mileage)
Average Segment Cost (specific period) / Average cost per segment for the current month entered. / Total segment cost/total number of segments

Report Filters include: arc, non arc, refund, and exchange transactions and excludes voided transactions.

Sample Report – Air Market Analysis

Right side


Airline Trends

This report will show you how much you’re spending on each airline per month and how many tickets were purchased. It also gives the average ticket price per airline as well as the Total amount spent on all airlines.

Report Category: Supplier Management

Report Format: Excel

Prompts:Date Range: Current

Currency code

SelectorsAirlines

Category

Questions answered with this report include:

  • How much did my company spend on tickets each month per vendor?
  • How much did my company spend on all tickets each month?
  • What is the average ticket price per vendor?

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
(Airline Name) Cost / Total amount spent on the chosen airline. User chooses airline in the report. / Sum (Total Amount) when (Airline Name) = Airline chosen by user
(Airline Name) Tickets / Total number of tickets on each chosen airline. / Sum (True Ticket Count)
(Airline Name) ATP* *(Average Ticket Price) / The average price paid for a ticket on the chosen airline. / Weighted (Total Amount) / (True Ticket Count)
Total Cost / Total amount spent on all airlines. / Sum (Total Amount)

Sample Report – Airline Trends


Airline Trip Summary

This report will give you the amount spent and the number of segments booked for each airline. It will also show you what you spent on domestic travel versus international travel.

Report Category: Supplier Management

Report Format: Excel

Prompts:Date Range: Current

Currency code

Questions answered with this report include:

  • How much did my company spend on tickets each month per vendor?
  • How much did my company spend on all tickets each month?
  • What is the average ticket price per vendor?

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
Seg Carrier Name / Name of the airline the segment was booked on. / N/A
No. of Segments / Total number of airline segments per airline. / Sum (True Ticket Count)
% of Segments / Percent of tickets booked on each airline versus the total number of tickets booked. / % to Total (True Ticket Count)
Segment $ Total / Total amount spent on airline segments per airline. / Sum (MIN Mileage /MIN Total Mileage x Total Amount)
% of Cost / Percent spent on each airline versus the total amount spent on all airlines. / % to Total (MIN Mileage/MIN Total Mileage x Total Amount)
Domestic Segments / Total number of domestic (US) segments / Sum (True Ticket Count) when (Intl Indicator) = D
Intl Segments / Total number of international (other than US) segments / Sum (True Ticket Count) when (Intl Indicator) = I
Domestic Spend / Total amount spent on domestic segments (all flights are within the US). / Sum (MIN Mileage/MIN Total Mileage x Total Amount)
when (Intl Indicator) = D
Intl Spend / Total amount spent on international segments. / Sum (MIN Mileage/MIN Total Mileage x Total Amount)
when (Intl Indicator) = I

Sample Report – Airline Trip Summary

Specific airline


Car Market Analysis

The Air Market Analysis dashboard provides an overview of key elements about the air program and trends them over a specified period. The dashboard also shows the Top 100 Air Markets with a breakdown of Top 5 Airlines by Market.

Report Category: Supplier Management

Report Format: Xcelsius

Prompts:Date Range:Current MTD

Current YTD

Questions answered with this report include:

  • What are the top 100 cities where cars are rented?
  • How much have we spent on car rentals?
  • Are we spending too much on car rentals in certain markets?
  • What car company are we using more?

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
Rank / Top 100 cities where cars are rented based on the number of cars rented. / N/A
City / Car rental city. / N/A
Bookings (City) / Number of cars booked. / N/A
% of Bookings (City) / Percentage of cars booked within a specific market with a specific car rental company. / #of bookings/Total # bookings
Total Cost (City) / Total amount of car rental. / N/A
Percentage of Total Cost (City) / Percentage of car rental cost within a specific market for a specific car rental company. / Total Cost (City) / total cost
Average Car Cost (City) / Average cost for a car in specific city / Rental Cost/Total Rental Cost
Car Chain / Name of top car chains based on top car cities. / N/A

Car Market Analysis continued

FIELD NAME / DESCRIPTION / CALCULATION
Bookings (Car Chain) / Number of cars booked. / N/A
% of Bookings (Car Chain) / Percentage of cars booked within a specific market with a specific car rental company. / #of Bookings (per car chain)/Total # bookings
Total Cost (Car Chain) / Total car amount. / Sum (Total car amount)
Percentage of Total Cost (Car Chain) / Percentage of car rental cost for a specific car rental company. / Car Cost (per car chain)/Total Car Cost
Average Car Cost (Car Chain) / Average cost paid for a car for specific car chain. / Car Cost (per car chain)/Total Car Cost
Total Cost (Specific Period) / Total car amount for a specific time period. / N/A
Total Number of Car Rentals (Specific Period) / Total number of cars rented for a specific time period. / N/A
Total Number of Rental Days (Specific Period) / Total number of days cars were rented for a specific time period. / N/A
Average Rental Rate (Specific Period) / Average rate for a rental car over a specific period. / Car Cost (specific period)/Total Car Cost

Report Filters include: arc, non arc, refund, and exchange transactions and excludes voided transactions.

Report Sample – Car Market Analysis

Right side

Car Summary

This report will give an overview of what was spent on car rentals and how many days cars were rented. It will break it down domestically, internationally, and by car company for two different date periods to show the difference between the date periods.

Report Category: Supplier Management

Report Format: PDF

Prompts:MTD

Prior MTD

Currency Code

Questions answered with this report include:

  • Do we book more cars domestically or internationally?
  • Which car rental company did we use the most?
  • Which car rental company did we spend the most money with?

Data Fields:

FIELD NAME / DESCRIPTION / CALCULATION
Total Car Rental Days / Total number of days cars were rented domestically (within the US), internationally, and total for both. / Sum (Num days) x (Num cars)
Total Car Rental Expense / Total amount spent on car rentals domestically, internationally, and total for both. / Sum (Num days) x (Num cars) x (Daily Rate)
Average Rate per Day / The average rate spent on car rental per day. / Sum (Car Rental Expense) / (Car Rental Days)
Number of Days per Chain / Total number of days that cars were rented per car rental company. / Sum (Num days) x (Num cars)
Cost per Chain / Total amount spent on car rentals per car rental company. / Sum (Num days) x (Num cars) x (Daily Rate)

Sample Report – Car Summary

Hotel Market Analysis

The Hotel Market Analysis dashboard report provides reporting period total costs, total number of nights, total bookings, average length of stay, and average room rate totals on the left hand side of the report. On the right side of the report is a breakdown of those totals, by destination city. The bottom of the report provides the user with the option to drill-down for detail on each destination city, providing a breakdown of bookings by property.

The center part of the report graphs the comparative variance of total costs, total nights, total booking, average length of stay, and average room rate on a month-by-month basis.

Report Category: Supplier Management

Report Format: Xcelsius

Prompts:Date Range – MTD

Date Range - YTD

Questions answered with this report include:

  • How much did we spend for hotel stays for the reporting range? How did this vary by month?
  • How many nights did we stay in hotels, and how did this vary by month?
  • What was the total number of bookings made during the reporting period, and how did this vary by month?
  • What was the average duration of each hotel stay, and how did that vary by month?
  • What was the average room rate, and how did that vary by month?
  • What were my bookings and costs, including per-night averages, by destination city? How did this break down by each property in the destination city?

Data Fields: