General Description |
|---|
The T&E Dashboard has the objective to :
The dashboard is providing different angles of analysis :
There are some accesses restrictions to employee level information in accordance to GDPR regulations. Sources: This dashboard has been build using 3 different data sources:
* check details in "Information on data sources" Concur **check details in "Information on data sources" Amex GBT Dashboard is providing N-1 data, as well as current year YTD M-1 data. Data of M-1 are available on the 15th day of M. Dashboard is only available in EUR. By default, data is always YTD of current year. |
Dashboard details
Executive summary & group benchmark
add screenshot of the dashboard with Numbers of final version in Qlik
| Nb | Name | Source | Calculation | Comments |
|---|---|---|---|---|
| 1 | Spend and Evolution vs N-1 RSB | Controlling | Spend = ∑Spend "Selection" N Evolution = (∑Spend "Selection" N -∑Spend "Selection" N-1 RSB)/∑Spend "Selection" N-1 RSB | "Selection" is YTD by default and can be changed to another period using "Month Year" filters RSB methodology available below "LINK" |
| 2 | Number of active spenders | Concur | Count number of Employee over period (no duplicates) | Any employee that enter an expense in Concur over the analysis period |
| 3 | Number of active travelers | AmexGBT - Spend Analysis | Count number of Employee over period (no duplicates) | Any employee that made a booking either online (concur) or offline (travel agency) over the analysis period |
| 4 | Number of air tickets booked | AmexGBT- Spend Analysis | Filter on Primary product code ="Air" ∑Number of tickets | Including refunds and exchanges |
| 5 | Air average ticket price vs N-1 | AmexGBT- Spend Analysis | ∑Air Net Ticket Amount "Selection" N / ∑ Air Net Tickets "Selection" N vs ∑Air Net Ticket Amount "Selection" N-1 / ∑ Air Net Tickets "Selection" N-1 | Average ticket price regardless of booking class, country point of sales, ... |
| 6 | Book Online | AmexGBT- Spend Analysis | ∑Air Net Tickets "Selection" N with booking channel = "Online" / ∑ Air Net Tickets "Selection" N | Currently for Korea, adjust booking channel to Online if DK description ⊂ "concur" |
| 7 | Book in Advance for Domestic & Regional Flights (min 14 days) | AmexGBT- Spend Analysis | Filter on "Regional or Intercontinental" = "Domestic - Regional" & Filter on Primary product code ="Air" ∑Air Net Tickets "Selection" N with compliance = "Yes" / ∑ Air Net Tickets "Selection" N | |
| 8 | Book in Advance for International Flights (min 21 days) | AmexGBT- Spend Analysis | Filter on "Regional or Intercontinental" = "Intercontinental" & Filter on Primary product code ="Air" ∑Air Net Tickets "Selection" N with compliance = "Yes" / ∑ Air Net Tickets "Selection" N | |
| 9 | Book the lowest logical fare | AmexGBT- Spend Analysis | Filter on Primary product code ="Air" ∑Air Net Tickets "Selection" N with LLA Acceptance = "Yes" / ∑ Air Net Tickets "Selection" N | |
| 10 | % of one day trips vs total of trips (by air) | AmexGBT- Spend Analysis | Filter on Primary product code ="Air" ∑Air Net Tickets "Selection" N with Days traveled = "1 day" / ∑ Air Net Tickets "Selection" N | This metric is including one-way tickets. |
| 11 | % of internal meetings (air & rail) | AmexGBT- Spend Analysis | Filter on Primary product code ="Air" & "Rail" ∑Total number of tickets with Trip Purpose Clean = "Internal meeting" / ∑Total number of tickets | |
| 12 | Solvay Benchmark - Online Booking compliance | AmexGBT- Spend Analysis | Metric number 6 with split per GBU/Function for YTD N. | Cannot be filtered by GBU/Function |
| 13 | Solvay Benchmark - Book in Advance for Domestic & Regional Flights (min 14 days) compliance | AmexGBT- Spend Analysis | Metric number 7 with split per GBU/Function for YTD N. | Cannot be filtered by GBU/Function |
| 14 | Solvay Benchmark - Book in Advance for International Flights (min 21 days) compliance | AmexGBT- Spend Analysis | Metric number 8 with split per GBU/Function for YTD N. | Cannot be filtered by GBU/Function |
| 15 | Solvay Benchmark - Book the lowest logical fare compliance | AmexGBT- Spend Analysis | Metric number 9 with split per GBU/Function for YTD N. | Cannot be filtered by GBU/Function |
| 16 | Monthly Update | N/A | Free Text | Monthly update from Purchasing |
Financial overview - spend evolution (controlling data)
| Nb | Name | Source | Calculation | Comments |
|---|---|---|---|---|
1 | Total Spend | Controlling | ||
| 2 | Spend YTD vs YTD N-1 RSB | |||
| 3 | Spend evolution N vs N-1 | Dimension can be month or quarter | ||
| 4 | Spend per Region | Location of company not expense | ||
| 5 | Details and evolution per GBU and function | Trend = Moving average = |
Expenses overview (Concur data) - incl top spenders
| Nb | Name | Source | Calculation | Comments |
|---|---|---|---|---|
| 1 | Total expenses YTD | Concur | ||
| 2 | Expenses per region | Region is origin region from the traveler, not the expense | ||
| 3 | Expenses by Cluster | Cluster are based on expense type in Concur | ||
| 4 | Monthly evolution | |||
| 5 | ||||
| 6 |
Bookings behaviors (AmexGBT/travel agency data ) - incl top non compliant travelers
Bookings overview (AmexGBT/travel agency data )
My reports
Information on data sources & calculation
Finance/Controlling data: P&L
It has been agreed that controlling data are the reference to measure the spend evolution and will only be adjusted with FX effect and not scope.
Also note that there is an average of 35 days lagging effect between a booking and its impact in BW, with the assumption that travellers are expensing at least once a month (policy recommendation).
RSB - FX effect methodology
TO DO
Expenses :Concur Data
There are two different Concur systems; one from Solvay and the other from Cytec legacy (expected to run until 2020). Concur is a tool were employees are claiming T&E for reimbursement. This tool is still under implementation and therefore not covering 100% of the scope.
INCLUDE LINK TO CONCUR SCOPE AND DEPLOYMENT PLAN
Booking information: Travel Agency data AmexGBT Insight
Detail files with different spend references
Details on sources:
Expenses :Concur Data
There are two different Concur systems; one from Solvay and the other from Cytec legacy (expected to run until 2020). Concur is a tool were employees are claiming T&E for reimbursement. This tool is still under implementation and therefore not covering 100% of the scope.
INCLUDE LINK TO CONCUR SCOPE AND DEPLOYMENT PLAN
Planning deploiement / scope couvert par CONCUR:
Concur Solvay (expense & booking):
- NAM (US CA) > June 2016
- EMEA 1 (BE FR UK NL LX) > June 2017
- EMEA 2 (DE IT PT ES) > Dec 2017
- Technology Solution US > July 2018
- APAC 1 (SG TH / JP KR) > Nov / Dec 2018
- LAM (BR PE CL) > Dec 2018
- MX > Jan 2019
- APAC 2 (IN CN) > Fev 2020
Concur Cytec Expenses
- NAM: US (CM +1 company code of TS)
- APAC: AU (TS), CN (TS CM)
- EMEA: NL (TS), UK (CM), IT FR DE (CM), LV (SBS), CG (TS)
NB. quite many remote locations (employees located in Ukraine, Mongolia, DAR, Kazahstan) are using the NL Concur for expenses
Concur Cytec booking (via Amex GBT reseller agreement)
- NAM: US (CM)
- EMEA: UK DE BE (TS CM),
NB. LV IT (TS CM) are booking in BE Concur
Finance/Controlling data: P&L
It has been agreed that controlling data are the reference to measure the spend evolution and will only be adjusted with FX effect and not scope.
Also note that there is an average of 35 days lagging effect between a booking and its impact in BW, with the assumption that travellers are expensing at least once a month (policy recommendation).
FX effect methodology
TO DO
Booking information: Travel Agency data AmexGBT Insight
Detail files with different spend references
Dimensions Overview:
To adjust for each sources including VIP, SLT,frequent traveller ...
| Qlikview | Comments |
|---|---|
GBU / Function | |
| Company | |
| Region | APAC, EMEA, LAM, NAM |
| Country | Company country |
| Year | Y-1, Y |
| Quarter | |
| Month | |
Cost Center | |
| Fixed Cost Group 2 | |
| Vendor | Name, Number |
Not able to find the solution? Contact SBS Support.