Versions Compared

Old Version 3

changes.mady.by.user Claire Boursier

Saved on

compared with

New Version Current

changes.mady.by.user Claire Boursier

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.



Info
iconfalse
titleMacro-Package Team
Macro-Package Owner: Claudia Clark
Global Fleet Manager: Sander Ripmeester

Procurement Partner: Frederico Henriques

Finance Partner: Maryse Persehaie


Car Fleet dashboard is owned by Procurement

Info
iconfalse
titleTarget Users
  • ELT members
    Procurement (Car Fleet Buyers)
    Macro-Package core team
    Global controlling
    Fleet Management team
    HR country managers


  • GBU/F Head, GBU/F Finance Director, GBU/F Global Controller
    GBU LT upon request
    GBU MP co-owner
Info
iconfalse
titleAuthorization & Rights
Accesses must be approved by the Macro-Package Team (Procurement)

Click here for the Access request form.

 

General Description

Objectives:

This dashboard is result of a Fuel for Growth initiative and therefore has been built to increase transparency on Car Fleet Operational KPIs.

The objective of this Dashboard is to allow concerned stakeholders (GBU/Functions, Controlling, Finance, Procurement, HR, Fleet Management, Macro Package Team) to find relevant information about the latest state and evolution of their car fleet to:

  • Manage fleet
  • Control cost
  • Monitor policy compliance
  • Benchmark KPIs between Solvay entities & locations
  • Monitor sustainability impact against Solvay One Planet goals
  • Identify and size improvement and savings opportunities


It has been built using 3 different data sources:

  • Costa (cost)
  • Fleet Mgt Companies: ARI and Dragintra for NAM and EMEA (op & compliance KPIs)
  • Manual data inputs for smaller fleet countries (operational & compliance KPIs)
A Quick Start Guide is available here


Structure & Content:

4 pages for users + 1 page (data checking) for dashboard management team



Content of each page: 









Data Sources

Useful information about data:

  • data is refreshed monthly (D10-15)
  • dashboard data is in EUR currency
  • The Macro-Package team is working on improving the quality of the source data displayed in the dashboard will improve month after month in consequence, thank  you for your patience and understanding
3 sources of data are feeding the dashboard:
  1. Vendors data (Fleet Management Companies)
    • ARI for USA and Canada
    • Dragintra for Belgium, France, Italy, Germany, UK, Spain, Portugal
  2. Costa dashboard (query BW xxxx)
  3. Manual imput from different local sources for countries not covered by ARI or Dragintra.

Useful information about data:

  • data is refreshed monthly (D10-15)
  • dashboard data is in EUR currency
  • There are some access restrictions to employee level information in accordance to GDPR regulation: pages view is restricted to your GBU/F except the executive summary page
  • The Macro-Package team is working on improving the quality of the source data displayed in the dashboard will improve month after month in consequence, thank  you for your patience and understanding


Data sources of this dashboard are managed through the following application named “Car Fleet Source” which will transform raw data given by the team in charge of it, to an adapted file for our dashboard. 
Car Fleet dashboard is using different source of data:

  • From query
    • Costa data
  • From flat file
    • ARI: naming is YYYY.MM ARI_Car Fields Data
    • DRAGINTRA: naming is YYYY.MM Dragintra_Car Fields Data
    • Manual data: naming is YYYY.MM XXX_Car Manual Data
    • Mapping (transco)


1. Costa data:

The queries used to extract data from Co$ta are the following:

  • QV_BW_QRY_CPCOCT05_1001
  • QV_BW_QRY_CPCOCT05_1002 (perimeter change only)

Extractions details are described in the Cost Transparency documentation: “Cost Transparency – Detailed Technical Documentation” in section 5.2.
Note that, in the Car Fleet dashboard we will only get one QVD per year contrary to what we have in Costs Transparency i.e one QVD per month.
Here we will use the extraction made on Cost Transparency dashboard, but we will only keep the following fields:

Image Added

Image Added

Image Added


2. ARI data:

ARI data represents all the data at time T, we will have all information by column. 
These data are based on excel files that are given by a specific team. They make excel files available and, on our side, we just must launch the Car Fleet Source application to convert these data from excel to qvd file. 


Dropped source file in production environment, the following path: \\acew1pqlikapp01\shared sense\Solvay Data\Apps\CAR FLEET\SOURCE, then we will have to copy/paste these files into the development environment \\acew1pqlikapp02\shared sense\Solvay Data\Apps\CAR FLEET\SOURCE
These excel files might be named as: YYYY.MM ARI_Car Fields Data


Source used in the dashboard are in the following field: \\acew1pqlikapp02\shared sense\Solvay Data\Apps\CAR FLEET\QVD, all these QVD will be named as: ARI_YYYY_MM
We will have one QVD per month. 


A script part is added to load only the last source file ( the same for all data sources:

Image Added


3. Dragintra data:

In the DRAGINTRA data source file, we will have information by lines. Principle is the same as described in 2.2 for ARI data. 
Source excel file might be named such as: YYYY.MM Dragintra_Car Fields Data / YYYY.MM Dragintra_Financial Data

To get more details on specification of the previous section (2.2, 2.3 and 2.4), please go here and see tab “Template #1 Vendors”.


4. Mapping:

Transco mapping: These mapping are manage in the tab “TRANSCO MAPPING” from this file. It’s loaded and then converted into QVD file. These mapping are updated by the MP team. This mapping is used for main fields of the Car Fleet dashboard. 

GBU Function mapping: We also use a mapping already used in the Costs Transparency dashboard which is coming from this file: here, tab “GBU CBS_Cluster”. This file will help us to build the fields: ORIGIN_GBU_KEY and ORIGIN_GBU of Car Fleet dashboard. 



KPIs description

1. Executive Summary


Image Added


All KPI (except COSTS € YTD) shown on the top of the sheet represents data of the current month. COSTS € (YTD) KPI displays the year-to-date data (meaning from start year to current month). 
On the left, you will find short-cut to filter on field you want. 


The first bar char below display the different measures which can be selected in Image Added  per different analysis axis which can be changed in Image Added.
In the screen shot below, it shows the number of actives car per country.

Image Added

Pie chart below represents the number of EV/Hybrid vs total of cars per Fuel type. Here too, analysis axis can be changed in the drop-down list, analysis can be done at Vendors level

Image Added


2. Fleet MGT & Cost Efficiency


First graphic on this sheet will displays measures over the months.In the same way as previously, perdio analysis can be switched to quarter.

Image Added

As described in their respective title, the left pie chart represents the number of cars at Vendor level. If needed, the user can display the number of cars at the other level that are present in the drop-down list.
The right pie chart shows brands market share.

Image Added


3. Policy compliance & sustainability

Page starts with executive summary on our CO2 impact, in terms of trees offsetting (CO2 23kg for 1 tree), achievement vs Solvay One Planet goal (migrate our full fleet to electric and/or hybrid by 2025) and equivalent cost of CO2 (1 ton = 100 EUR).

Image Added

The other graphics have the same behavior than the ones described previously, except that here we display CO2 emission KPI. 

Image Added


4. My Report

MyReport view enable the user to create tables he wants regarding al the dimension of the dashboard. He can see the raw measures and make whatever analysis he wants.

Image Added


5. Data checking (only to be used by dashboard owner / MP team)

This view allow the user to see if dashboard’s dimensions are well mapped or not. We will find one table by mapped dimensions

Image Added

 Image Addedwill allow to identify which data source need to be adjusted.
Image Added raw data from the extraction, or flat file.
Image Added cleaned data, after treatment done in scripting, using the different mapping file described previously.

Period of Analysis

  • Current year to date
  • Year - 1
  • Current month mapping
  • By month, by quarter
allow to filter above certain ticket amount
Dimensions
Dimension Description
Finance -Controlling (BW)
Expenses
(Concur)
Booking
(AmexGBT)
Comments
GBU / Function
X
X
X
Sub GBU/Function
X
X
Company (name - code)
X
X
Region
X
X
X
Origin 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 Center
X
X
No cost center for Cytec Concur
Year
X
X
X
Quarter
X
X
X
Month
X
X
X
Reference date
Posting Date
Send for Payment date
Booking date
Vendor Name
X
X
Airline or Railway company
Expense Type (Cluster)
X
Trip Purpose
X
Trip Duration
X
Employee Name
X
X (Traveler Name)
Booking Channel (Online/Offline)
X
Online or Offline
Compliance on advance purchase (Yes/No)
X
Lowest Logical Fare Adoption (Yes/No)
X
Primary Product Code
X
Air- 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
X
travelers with an average of more than 2 bookings per month (air and/or rail)
SLT
X
X
travelers part of Solvay Leadership Team (to Do)
O&D Net Amount
X