General Description |
|---|
The T&E Dashboard, is a result of a Demand Management initiative and therefore has been built to increase transparency on T&E Spend, Expenses reports and travelers behaviors at time of booking. The objective is to allow GBU and functions to monitor its spend evolution, as well as its travelers behaviors over time. It has been built using 3 different data sources :
* check details in "Information on data sources" Concur **check details in "Information on data sources" Amex GBT This dashboard is providing different angles of analysis :
You will find N-1 data, as well as current year YTD M-1 data, all figures are in EUR. Data of M-1 are available on the 15th day of M. By default, data selection is always YTD of current year. |
Dashboard details
- Executive summary & group benchmark
add screenshot of the dashboard with Numbers of final version in Qlik or add screenshot for each line below
No restrictions on scope
| 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 | Filter on Primary product code ="Air" ∑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 | Solvay Benchmark - Average Spend per Employee | Concur | ∑Approved Amount without reclaimable tax (EUR) / Metric number 2 with split per GBU/Function for YTD N. | Cannot be filtered by GBU/Function |
| 17 | Monthly Update | N/A | Free Text | Monthly update from Purchasing |
2. Financial overview - spend evolution (controlling data)
add screenshot of the dashboard with Numbers of final version in Qlik
No restrictions on scope
| Nb | Name | Source | Calculation | Comments |
|---|---|---|---|---|
1 | Total Spend | Controlling | 2018 Spend = ∑Spend N-1 YTD 2019 Spend = ∑Spend Ytd N | No filter on time dimension |
| 2 | Total Spend YTD vs YTD N-1 RSB | Evolution = (∑Spend "Selection" N -∑Spend "Selection" N-1 RSB)/∑Spend "Selection" N-1 RSB | Clear indication on spend variation in EUR & in % vs previous year | |
| 3 | Spend evolution (N vs N-1) | Monthly/Quarter Spend for N data Monthly/Quarter RSB Spend for N-1 data | Dimension can be month or quarter | |
| 4 | Spend per Region (%) | % of each region | Location of company not expense | |
| 5 | Details and evolution per GBU and function | Trend = (∑Spend "Selection" N -∑Spend "Selection" N-1 RSB)/∑Spend "Selection" N-1 RSB Moving average = (∑Spend last quarter - Average Spend last 4 quarters)/Average Spend last 4 quarters |
3. Expenses overview (Concur data) - incl top spenders
add screenshot of the dashboard with Numbers of final version in Qlik
Restricted on Scope
This page has no N-1 comparison as scope is not stable yet (see planning of deployment for Concur)
| Nb | Name | Source | Calculation | Comments |
|---|---|---|---|---|
| 1 | Total expenses | Concur | ∑Approved Amount without reclaimable tax (EUR) | Total is excluding Pcard |
| 2 | Number of active spenders | Count number of Employee over period (no duplicates) | Any employee that enter an expense in Concur over the analysis period | |
| 3 | Expenses per region/Country (%) | ∑Approved Amount without reclaimable tax (EUR) in % of each Region or country | This metric has 2 dimensions: Region or country Region/country is origin region/country from the traveler, not the location of the expense | |
| 4 | Expenses by Cluster | ∑Approved Amount without reclaimable tax (EUR) in % of each expense cluster | This metric has 2 dimensions: % or EUR A cluster is a grouping of similar expense types in Concur | |
| 5 | Spend evolution | ∑Approved Amount without reclaimable tax (EUR) per month/quarter | This metric has 2 dimensions: Month / Quarter | |
| 6 | Spend per Employee | ∑Approved Amount without reclaimable tax (EUR) per month per employee | ||
| 7 | Spend per Cost Center | ∑Approved Amount without reclaimable tax (EUR) per month per cost center | Not available in Concur Cytec |
4. Bookings behaviors (AmexGBT/travel agency data ) - incl top non compliant travelers
add screenshot of the dashboard with Numbers of final version in Qlik
Restricted on Scope
| Nb | Name | Source | Calculation | Comments |
|---|---|---|---|---|
1.1 1.2 1.3 1.4 | Book Online KPI vs N-1 Details Quarterly/Monthly evolution Non Compliant Travelers | AmexGBT- Spend Analysis | always filter on Primary product code ="Air" ∑Air Net Tickets YTD N with booking channel = "Online" / ∑ Air Net Tickets YTD N vs ∑Air Net Tickets N-1 with booking channel = "Online" / ∑ Air Net Tickets N-1 Eligible Online = Yes if "Country (Point of Sale)" ≠ "India" ≠ "China" ≠"Taiwan" ≠" Latvia" and Primary Product Code ≠ "C" ≠"J" Tickets Booked = ∑Air Net Tickets YTD N Main non-compliant countries = Top 3 countries with lowest non-compliance to KPI (based on absolute value ∑Air Net Tickets "Selection" with booking channel = "Online" / ∑ Air Net Tickets "Selection" per period (quarter or month) ∑Air Net Tickets YTD N with booking channel = "Offline" sorted per traveler (descending) | Compliance evolution over time based on selection Month/Year This metric has 2 dimensions: Month / Quarter |
2.1 2.2 2.3 2.4 | Book in Advance KPI vs N-1 Domestic & Regional Flights (min 14 days) Details Quarterly/Monthly evolution Non Compliant Travelers | always filter on Primary product code ="Air" & Filter on "Regional or Intercontinental" = "Domestic - Regional" ∑Air Net Tickets YTD N with compliance = "Yes" / ∑ Air Net Tickets YTD N vs ∑Air Net Tickets N-1 with compliance = "Yes" / ∑ Air Net Tickets N-1 ∑Air Net Tickets "Selection" with compliance = "no" Count number of travelers (no duplicate) with compliance = "No" (XX% represents 80%) ∑Air Net Tickets "Selection" with compliance = "Yes" / ∑ Air Net Tickets "Selection" per period (quarter or month) ∑Air Net Tickets YTD N with compliance = "No" sorted per traveler (descending) | Compliance evolution over time based on selection Month/Year This metric has 2 dimensions: Month / Quarter | |
3.1 3.2 3.3 3.4 | Book in Advance KPI vs N-1 Intercontinental Flights (min 21 days) Details Quarterly/Monthly evolution Non Compliant Travelers | always filter on Primary product code ="Air" & Filter on "Regional or Intercontinental" = "Intercontinental" ∑Air Net Tickets YTD N with compliance = "Yes" / ∑ Air Net Tickets YTD N vs ∑Air Net Tickets N-1 with compliance = "Yes" / ∑ Air Net Tickets N-1 ∑Air Net Tickets "Selection" with compliance = "No" Count number of travelers (no duplicate) with compliance = "No" (XX% represents 80%) ∑Air Net Tickets "Selection" with compliance = "Yes" / ∑ Air Net Tickets "Selection" per period (quarter or month) ∑Air Net Tickets YTD N with compliance = "No" sorted per traveler (descending) | Compliance evolution over time based on selection Month/Year This metric has 2 dimensions: Month / Quarter | |
4.1 4.2 4.3 4.4 | Book Lowest Logical Fare KPI vs N-1 Details Quarterly/Monthly evolution Non Compliant Travelers | Filter on Primary product code ="Air" ∑Air Net Tickets YTD N with LLA Acceptance = "Yes" / ∑ Air Net Tickets YTD N vs ∑Air Net Tickets N-1 with LLA Acceptance = "Yes" / ∑ Air Net Tickets N-1 ∑Air Net Tickets "Selection" with LLA Acceptance = "No" Top 10 of ∑Air Net Tickets "Selection" with LLA Acceptance = "No" per traveler ∑ Lost Savings "Selection" % of Spend= ∑ Lost Savings "Selection"/∑Air Net Tickets amount "Selection" ∑Air Net Tickets "Selection" with LLA Acceptance= "Yes" / ∑ Air Net Tickets "Selection" per period (quarter or month) ∑Air Net Tickets YTD N with LLA Acceptance = "No" sorted per traveler (descending) | Lowest logical fare means best financial option with same number of connections and flight schedule. | |
| 5 | Trip Duration (based on number of air tickets booked) | Filter on Primary product code ="Air" % of ∑Air Net Tickets "Selection" per "Days traveled" (excluding "Exchange refund code" = "Exchange" & "days travelled" = "Refund") | This metric has 2 dimensions: Month / Quarter | |
6 | Trip Purpose (based on number of air & rail tickets booked) | % of ∑Air Net Tickets "Selection" per "Trip Purpose Clean" (excluding "Exchange refund code" = "Exchange") | This metric has 2 dimensions: % or absolute value | |
| 7 | Number of trip by air per traveler | Filter on Primary product code ="Air" ∑Air Net Tickets "Selection" per "Days traveled" sorted per traveler (descending) | Ability to filter on trip duration |
5. Bookings overview (AmexGBT/travel agency data )
add screenshot of the dashboard with Numbers of final version in Qlik
Restricted on Scope
| Nb | Name | Source | Calculation | Comments |
|---|---|---|---|---|
| 1 | Air Overview
| AmexGBT- Spend Analysis | Filter on Primary product code ="Air" Spend = ∑Air Net Tickets Amount ATP= ∑Air Net Ticket Amount / ∑ Air Net Tickets | |
| 2 | Volume per airlines alliances | AmexGBT- O&D | Filter on Primary product code ="Air" ∑O&D Net Count in % of airline alliances (or number of tickets per airline alliances) | This metric has 2 dimensions: % or number of tickets |
| 3 | Air tickets volumes : Domestic/Regional vs Intercontinental | AmexGBT- Spend Analysis | Filter on Primary product code ="Air" ∑Air Net Tickets in % of "Regional or Intercontinental" | |
| 4 | Air Routes Overview | AmexGBT- O&D | Filter on Primary product code ="Air" Column 1 : Origin -Destination (non directional) Column 2: ∑Air Net Tickets amount "Selection" Column 3: ∑Air Net Tickets "Selection" Column 4: % of ∑Air Net Tickets amount "Selection" for each Origin - Destination Column 5: Average cost per Origin - Destination "Selection" Column 6: Average cost per Origin - Destination "Solvay | NB: 2 Origin - Destination = 1 roundtrip |
| 5 | Air Tickets volume per booking class | AmexGBT- O&D | Filter on Primary product code ="Air" ∑O&D Net Count in % of "Class of Service - Invoice (Predominant) ∑O&D Net Amount in % of "Class of Service - Invoice (Predominant) | |
| 6 | Rail Overview
| AmexGBT- Spend Analysis | Filter on Primary product code ="Rail" Spend = ∑Air Net Tickets Amount ATP= ∑Air Net Ticket Amount / ∑ Air Net Tickets | |
| 7 | Rail Routes Overview | AmexGBT- O&D | Filter on Primary product code ="Rail" Column 1 : Origin -Destination (non directional) Column 2: ∑Rail Net Tickets amount "Selection" Column 3: ∑Rail Net Tickets "Selection" Column 4: % of ∑Rail Net Tickets amount "Selection" for each Origin - Destination Column 5: Average cost per Origin - Destination "Selection" Column 6: Average cost per Origin - Destination "Solvay | NB: 2 Origin - Destination = 1 roundtrip |
| 8 | Hotel Overview*
| AmexGBT - Hotels | Booked amount = ∑Period 1 Hotel Booked Amount Nights = ∑ Period 1 Hotel Room Nights | |
| 9 | Volumes per Hotel Chains (based on number of nights)* | AmexGBT - Hotels | ∑ Period 1 Hotel Room Nights in % of Hotel Chain | |
| 10 | Hotel Bookings Overview* | AmexGBT - Hotels | Column 1 : Hotel City Column 2: ∑Period 1 Hotel Booked Amount "Selection" Column 3: ∑ Period 1 Hotel Room Nights "Selection" Column 4: % of ∑Period 1 Hotel Booked Amount "Selection" for each Hotel City Column 5: Average cost per Hotel City "Selection" Column 6: Average cost per Hotel City "Solvay | |
| 11 | Car Overview*
| AmexGBT - Car rental by city | Booked amount = ∑Period 1 Car Booked Amount Days = ∑ Period 1 Car Days | |
| 12 | Volumes per Car Rental Companies (based on number of days)* | AmexGBT - Car rental by city | ∑ Period 1 Car Days in % of Car Rental Vendor | |
| 13 | Car rental Overview* | AmexGBT - Car rental by city | Column 1 : Rental City Column 2: ∑Period 1 Car Booked Amount "Selection" Column 3: ∑ Period 1 Car Days "Selection" Column 4: % of ∑Period 1 Car Booked Amount "Selection" for each Rental City Column 5: Average cost per Rental City "Selection" Column 6: Average cost per Rental City "Solvay | |
| 14 | Detailed Overview per Employee | TBC | Check with Claire |
* booking costs might be different from final invoiced costs
6. My reports
Restricted on Scope
Reporting can be done using one of the 3 sources of the dashboard:
- Finance/Controlling : Based on Fixed Cost Report from BW
- Expenses Reports: Based on Concur reporting without Pcard spend
- Booking Reports: Based on AmexGBT/travel agency data
To find more details about information available in this details, please report to the section Dimensions. (ADD LINK)
Additional information on data sources
Dashboard using 3 different data sources, those sources are coming from different tools, using different scope and reference dates.
It is recommended to use Expenses and Booking information as additional information to Controlling data, but not to try to match figures of the 3 sources.
1. Finance/Controlling data
- Objective
This report is a fixed costs report on Travel & Expenses set up by Solvay reporting platform (ownership: Finance Service Line) and is the reference for P&L calculation.
- Currency
Data are extracted in EUR each month for the current year with a RSB at M.
Data from Y-1 are all RSB at average rate of December Y-1.
- Source & scope
Data are extracted from SAP (PF1 + WP1) using Business Warehouse (BW) for all Solvay Group
- Additional information
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).
- Exception
BAAN & PE1 spend cannot be extracted from BW.
In the meantime of Composites Materials integration on WP1, data are extracted from Cognos (owner: James Blandford) and are RSB with the same methodology as spend extracted from BW.
- RSB - FX effect methodology
In order to neutralize the FX effect, spend is always extracted in local currency and calculated in EUR using the following methodology.
Exchanges rates are extracted from AO Docs
All spend of current year is adjusted from local currency to EUR using the exchange rate (average value) of M.
All spend of previous year is fixed and adjusted using average value of December of this same year.
2. Expenses :Concur Data
- Objective
Concur is a tool were employees are claiming T&E for reimbursement. It allow user to get details at employee level, with some information on the expense type.
- Currency
Concur data are extracted in local currency (available in "MyReport" but not as such in the dashboard) and in EUR based on:
- if it is a card transaction then the exchange rate is provided by the bank at the day the transaction
- If it is an out of pocket transaction, then the exchange rates in Concur reflect the OANDA exchange rates of the transaction date
- Source & Scope
Data are extracting from Concur using Cognos in a format agreed with controlling and created by Purchasing Service Line (process owner).
Data are excluding all PCard expenses, send on a monthly basis to controller for their scope of activity.
Geographical scope is not yet stable and subject to Concur implementation planning. In countries where Concur (or equivalent solution) is not yet implemented, there is not possibility to have this level of details on transactions at employee level.
- additional information
Concur details reports are received each month by controllers,, see list of reports and receivers.
Each manager can also get details on its team using the reporting section of the tool:
- exception
A part of the Cytec legacy is still using a different Concur system to record expenses that should be replaced by the Solvay Concur system by end of 2020.
This information is collected using a different report (owner: Guna Bambale).
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
- NAM: US (CM)
- EMEA: UK DE BE (TS CM)
NB. LV IT (TS CM) are booking in BE Concur
- deployment planning
Planning is owned by the Purchasing Service Line and might be subject to changes.
3. Booking information: Travel Agency data AmexGBT Insight
- Objective
- Currency
- Source & Scope
- additional information
- exception
- deployment planning
Detail files with different spend references
Dimensions Overview:
To adjust for each sources including VIP, SLT,frequent traveler ...
| Dimension Description | Controlling Finance | Expenses (Concur) | Booking (AmexGBT) | Comments |
|---|---|---|---|---|
| GBU / Function | ||||
| Company | ||||
| Region | ||||
| Country | ||||
| Cost Center | X | X | No cost center for Cytec Concur | |
| Year | X | X | X | |
| Quarter | ||||
| Month | ||||
| Reference date | Posting Date | Send for Payment date | Booking date | |
| Supplier? | ||||
| Employee Name | ||||
| VIP | ||||
| SLT | ||||
| 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.