Excellence Center Demand Management & Purchasing


Claire Boursier - Philippe Vanash


Marie-Line Ardito


 

General Description

The objective is to allow GBU and Functions monitoring their spend evolution, as well as their travelers behaviors over time. 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.
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

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 Finance- Controlling data from BW, but not to try to match figures of the 3 sources.

This dashboard is providing different angles of analysis :
    1. Executive summary & group benchmark
    2. Financial overview - spend evolution (controlling data)
    3. Expenses overview (Concur data)
    4. Bookings behaviors (AmexGBT/travel agency data )
    5. Bookings overview (AmexGBT/travel agency data )  
    6. My reports

ADD LINK TO DETAILS FOR EACH SECTION

There are some accesses restrictions to employee level information in accordance to GDPR regulations.
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. (also display info in dashboard)
By default, data selection is always YTD of current year.
What is online & offline, ATP or O&D? Click here to see the glossary and fully understand the acronyms of T&E.



  • ExCom
  • Leadership team
  • Financial Directors
  • Purchasing Function
  • Controllers


All the accesses must be validated by:
Excellence Center / Purchasing
Click here for the Access form to get access to the T&E Dashboard.
Current authorizations are available on this file.



Data Sources

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 Finance- Controlling data from BW, but not to try to match figures of the 3 sources.



Period of Analysis





1. Finance/Controlling data


  • Objective

This report is a fixed costs report on Travel & Expenses set up by Solvay reporting platform 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 on scope.

Also note that there is an average of about 35 days lagging effect between a booking and its impact in BW, with the assumption that travelers 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 including:

  • employees expenses claimed for reimbursement (all payments means and all booking channels included)
  • lodged card expenses (air, rail and travel agency fees)

which 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.

Data are excluding all PCard expenses, send on a monthly basis to controller for their scope of activity.

Geographical scope is not fully deployed yet and will follow Concur implementation planning.

Countries where Concur is not implemented are out of scope (no data available in this report).


  • 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 that should be replaced by the Solvay Concur system by end of 2020.

  • scope of expenses done in Cytec Concur


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


This information is collected using a different report (provided by Purchasing Service Line T&E - LV).


  • deployment planning



Planning is subject to changes. 


3. Booking information: Travel Agency data AmexGBT Insight


  • Objective

Data from AmexGBT is allowing to get information on booking behaviors of the travelers, as well as details related to location, volumes, class of booking ... for air/rail tickets (actual invoiced) and car rental/hotel (booked amount).

  • Currency

All data is extracted in EUR and exchange rate is based on ???(CHECK with CLAIRE)

  • Source & Scope

Data are extracted using the reporting tool of the travel agency AmexGBT called Insight. 

The scope is all booking information either done online (using Concur Travel) or offline (email or phone with travel agency).

  • additional information

Reporting is including all exchanges and refund information, please consider a delay in seeing full information in the report (reimbursement or full exchange information).



Dimensions Overview:


Dimension Description

Finance -Controlling (BW fixed costs)

Expenses

(Concur)

Booking

(AmexGBT)

Comments
GBU / FunctionXXX
Sub GBU/FunctionXX

Company (name - code)XX

RegionXXXOrigin region of expense

Country

(country of employee)

X (Company Country)X (Country of Employee)X (Country Point of Sale)


Hotel/Car rental Country

X
Hotel/Car rental City

X
Cost CenterXX
No cost center for Cytec Concur
YearXXX
QuarterXXX
MonthXXX
Reference datePosting DateSend for Payment dateBooking date
Vendor NameX (question)
XAirline or Railway company
Expense Type (Cluster)
X

Trip Purpose

X
Trip Duration

X
Employee Name
XX (Traveler Name)
Booking Channel (Online/Offline)

XOnline or Offline
Compliance on advance purchase (Yes/No)

X
Lowest Logical Fare Adoption (Yes/No)

X
Primary Product Code

XAir- Rail - Car - Hotel
Domestic or Regional/International

X
Class of Service

X

Booking class (Explain FIRST vs FIRST US & others) for Air/Rail

Car type for Rental

Frequent traveler

Xtravelers with an average of more than 2 bookings per month (air and/or rail)
VIP

Xtravelers accessing AMEX VIP service
SLT
XXtravelers part of Solvay Leadership Team (to Do)
O&D Net Amount

Xallow to filter above certain ticket amount


A conserver???

QlikviewComments

GBU / Function

 
Company 
RegionAPAC, EMEA, LAM, NAM
CountryCompany country
YearY-1, Y
Quarter
Month

Cost Center


Fixed Cost Group 2
VendorName, Number


 

 


Dashboard details

always consider N as selected period "Selection"

  1. 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 but no dimension on Employee level

NbNameSourceCalculationComments
1Spend and Evolution vs N-1 RSBBW- Fixed Costs

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 (FX effect) methodology available below "LINK"

2Number of active spendersConcur - ExpensesCount number of Employee over period (no duplicates)Any employee that enter an expense in Concur over the analysis period
3Number of active travelersAmexGBT - Spend AnalysisCount number of Employee over period (no duplicates)Any employee that made a booking either online (concur) or offline (travel agency) over the analysis period
4Number of air tickets bookedAmexGBT- Spend Analysis

Filter on Primary product code ="Air"

∑Number of tickets

Including refunds and exchanges


5Air average ticket price vs N-1AmexGBT- 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, ...
6Book OnlineAmexGBT- 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" → REMOVE WHEN DASHBOARD READY

Online = in Concur

Offline = via travel agency

7Book 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


8Book 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


9Book the lowest logical fareAmexGBT- 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 meeting trips (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

Based on number of tickets booked for trip by air or rail
12Solvay Benchmark - Online Booking complianceAmexGBT- Spend AnalysisMetric number 6 with split per GBU/Function for YTD N.Cannot be filtered by GBU/Function
13Solvay Benchmark - Book in Advance for Domestic & Regional Flights (min 14 days) complianceAmexGBT- Spend AnalysisMetric number 7 with split per GBU/Function for YTD N.Cannot be filtered by GBU/Function
14Solvay Benchmark - Book in Advance for International Flights (min 21 days) complianceAmexGBT- Spend AnalysisMetric number 8 with split per GBU/Function for YTD N.Cannot be filtered by GBU/Function
15Solvay Benchmark - Book the lowest logical fare complianceAmexGBT- Spend AnalysisMetric number 9 with split per GBU/Function for YTD N.Cannot be filtered by GBU/Function
16Solvay Benchmark - Average Spend per EmployeeConcur - Expenses

∑Approved Amount without reclaimable tax (EUR) / Metric number 2 with split per GBU/Function for YTD N.

Cannot be filtered by GBU/Function
17Monthly UpdateN/AFree TextMonthly 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

NbNameSourceCalculationComments

1

Total SpendBW- Fixed Costs

2018 Spend = ∑Spend N-1

YTD 2019 Spend = ∑Spend Ytd N

No filter on time dimension
2Total Spend YTD vs YTD N-1 RSBEvolution = (∑Spend "Selection" N -∑Spend "Selection" N-1 RSB)/∑Spend "Selection" N-1 RSBClear indication on spend variation in EUR & in % vs previous year
3Spend 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
4Spend per Region (%)% of each regionLocation of company not expense
5Details and evolution per GBU and function

Trend = (∑Spend "Selection" N -∑Spend "Selection" N-1 RSB)/∑Spend "Selection" N-1 RSB

Moving average = (∑Spend last 3 months - Average Spend last 12 quarters)/Average Spend last 12 quarters in %





3. Expenses overview (Concur data) - incl top spenders

add screenshot of the dashboard with Numbers of final version in Qlik

Restricted on Scope

Scope of Concur is not finalize yet (see planning of deployment for Concur) - therefore it is not always relevant to compare N vs N-1.


NbNameSourceCalculationComments
1Total expensesConcur - Expenses




∑Approved Amount without reclaimable tax (EUR)Total is excluding Pcard
2Number of active spenders

Count number of Employee over period (no duplicates)

Any employee that enter an expense in Concur over the analysis period

3Expenses 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

4Expenses 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

5Spend evolution∑Approved Amount without reclaimable tax (EUR) per month/quarter This metric has 2 dimensions: Month / Quarter
6Spend per Employee∑Approved Amount without reclaimable tax (EUR) per month per employee
7Spend per Cost Center (name + code)∑Approved Amount without reclaimable tax (EUR) per month per cost centerNot 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


NbNameSourceCalculationComments

1.1




1.2






1.3


1.4

Book Online KPI vs N-1




Details






Quarterly/Monthly evolution


Non Compliant Travelers (Online eligible)

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 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" & "Eligible" = Yes; 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.
5Trip 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

ADD OPTION TO FILTER ON RAIL IN THE FUTURE

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

ADD OPTION TO FILTER AIR OR RAIL IN THE FUTURE

7Number 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
8Air Ticket above 5000 EURTBD


5. Bookings overview (AmexGBT/travel agency data )

 

add screenshot of the dashboard with Numbers of final version in Qlik

This page will diplay data based on O&D (origin - Destination) basis, meaning a Oneway ticket. 

Restricted on Scope


NbNameSourceCalculationComments
1

Air Overview

  • Total Spend
  • Number of O&D
  • Average O&D cost
AmexGBT- O&D

Filter on Primary product code ="Air"

Spend = O&D Net Amount

Number of O&D = O&D Net Count

Average O&D cost= O&D Net Amount / O&D Net Count


2Volume per airlines alliancesAmexGBT- 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
3Air tickets volumes : Domestic/Regional vs IntercontinentalAmexGBT- Spend Analysis

Filter on Primary product code ="Air"

∑Air Net Tickets in % of "Regional or Intercontinental"


4Air Routes OverviewAmexGBT- O&D

Filter on Primary product code ="Air"

Column 1 : Origin -Destination (non directional)

Column 2: O&D Net Amount "Selection"

Column 3: O&D Net Count"Selection"

Column 4: % of O&D Net 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
5Air Tickets volume per booking classAmexGBT- 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

    • Total Spend
    • Number of O&D
    • Average O&D cost
AmexGBT- O&D

Filter on Primary product code ="Rail"

Spend = O&D Net Amount

Number of O&D = O&D Net Count

ATP= ∑O&D Net Amount / ∑ O&D Net Count


7Rail Routes OverviewAmexGBT- O&D

Filter on Primary product code ="Rail"

Column 1 : Origin -Destination (non directional)

Column 2: ∑O&D Net Amount "Selection"

Column 3: ∑O&D Net Count"Selection"

Column 4: % of O&D Net 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*

  • Booked amount
  • Number of nights
  • Average daily rate
AmexGBT - Hotels

Booked amount = ∑Period 1 Hotel Booked Amount

Nights = ∑ Period 1 Hotel Room Nights

ADR = ∑Period 1 Hotel Booked Amount/∑ Period 1 Hotel Room Nights


9Volumes per Hotel Chains (based on number of nights)*AmexGBT - Hotels ∑ Period 1 Hotel Room Nights in % of Hotel Chain
10Hotel 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*

  • Booked Amount
  • Number of days
  • Average daily rate
AmexGBT - Car rental by city

Booked amount = ∑Period 1 Car Booked Amount

Days = ∑ Period 1 Car Days

ADR= ∑Period 1 Car Booked Amount/∑ Period 1 Car Days


12Volumes per Car Rental Companies (based on number of days)*AmexGBT - Car rental by city

∑ Period 1 Car Days in % of Car Rental Vendor


13Car 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


* 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 details per data source.