CET CERN Expenditure Tracking

1
CET CERN Expenditure Tracking

• Introduction to CET and basic financial concepts
• June 2007
• IT-AIS-MDS

2
What do you learn today?

• After the presentation, you should be able ...
• to know what is CET and how to access it
• to understand the purpose of the main CET reports
• to distinguish between payments, commitments,
  provisions etc.
• to get a better idea about financial transactions
• to use advanced features like virtual units and
  ranges
• to discover the flexibility of CET Information
  Centre
• to schedule reports for automatic execution

3
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
4
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
5
Where to find CET

• Inside CERN use the following URL
• cet
• From outside CERN
• https//cet.cern.ch/
• Officially supported browser
• Internet Explorer (CERN Nice version)
• And CET normally should work with
• All major browsers in a recent version (e.g. IE
  6/7, Firefox 2.0, Safari 2, Konqueror 3.5 etc.)

6
Some Words about CET

• Gathers its data from the various financial
  applications used at CERN (mainly Qualiac)
• Access is granted based upon official CERN roles,
  EDH signature rights and CFU contract accesses
• Data is extracted daily (extractions run during
  the night)
• CET (among other applications like HRT, SMT, IC,
  CFUtk etc.) is maintained and further extended by
  the AIS-MDS team

Per Gunnar Jönsson (SL)
Chris Dixon (STAF)
Jan Janke (STAF)
Piotr Sliwa (FELL)
7
Main Menu
Contracts
Status of book closing and extractions
Transactions
Summaries
Information Centre
User Preferences
Stored Reports
Virtual Units
8
User Preferences
9
User Preferences
10
Column Selector
Default Columns
11
Ordering Selector
12
Output format
13
Drilldown
14
Custom Query Input
15
Custom Query Input
16
Custom Query Input
17
PDF in CET Reports

• PDF output
• preserves the layout of the report results
  representation,
• allows browser independent printing and
• is convenient for sending report results as email
  attachment.
• Be careful
• The output is always generated on an A4 page.
• I.e. the number of nicely displayable columns is
  limited.

18
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
19
Basic Terminology
Definitions.
Commitment Amount agreed to pay
H/w CHF 3,000
Pay 20 On Order and 80 on Deliv.
Payment on Budget Code Amount charged to
Budget Code
Payment to SupplierAmount paid on invoice
Open CommitmentCommitment Payment (Budget
Code)
9 / 06
8 / 06
20
Basic Terminology
Definitions.
Commitment Amount agreed to pay
H/w CHF 3,000
Pay 20 On Order and 80 on Deliv.
Year 2006 Commitment 3000 Payment to supplier
600 Provision Paid on Budget Code Open Commit.

Payment on Budget Code Amount charged to
Budget Code
- 600
1400
1400
2000
2006 2007
2,000.00 1,000.00
Payment to SupplierAmount paid on invoice
1000
2000
1000
Open CommitmentCommitment Payment(Budget
Code)
Provisioned next yearMoney from this years
budget set aside for next year expenses
12 / 06
9 / 05
8 / 05
21
Basic Terminology
Definitions.
Commitment Amount agreed to pay
H/w CHF 3,000
Pay 20 On Order and 80 on Deliv.
Year 2007 Commitment 3000 Carry Over Bill
from supplier Paid from Provision Paid on
Budget Code
Payment on Budget Code Amount charged to
Budget Code
1000
2400
2006 2007
2,000.00 1,000.00
Payment to SupplierAmount paid on invoice
1400
1000
Open CommitmentCommitment - Payment
01 / 07
Provisioned next yearMoney from this years
budget set aside for next year expenses
12 / 05
Carry OverOpen Commitments at the end of last
year
9 / 05
1000
1400
8 / 05
Paid on ProvisionAmount paid on invoices using
money provisioned last year
22
Annual vs. Pluri-Annual Commitment
H/w CHF 3,000
Special Offer Pay 20 On Order and 80 on Deliv.
Annual commitment
2006
2007
Pluri-annual commitment
2006 ?
2007 ?
CHF 3000
CHF 2400
The chosen time period must not exceed one
(financial) year.
23
Annual vs. Pluri-Annual Commitment
H/w CHF 3,000
Special Offer Pay 20 On Order and 80 on Deliv.
Annual commitment
2006
2007
Pluri-Annual ?
Pluri-annual commitment
2006 ?
2007 ?
CHF 3000
CHF 2400
CHF 5400 ?
24
Annual vs. Pluri-Annual Commitment
H/w CHF 3,000
Special Offer Pay 20 On Order and 80 on Deliv.
Pluri-annual commitment payment
Annual commitment
2006 CHF 3000
2007 CHF 2400
Pluri-annual commitment
2006
2007
CHF 600
CET automatically switches to the pluri-annual
versions of the columns, if the time period
refers to a time period not matching a single
(financial) year.
CHF 2400
More than one year is selected.
25
Terminology in a CET Example
Summaries
26
Terminology in a CET Example
CET Pivot
27
Terminology in a CET Example
28
Terminology in a CET Example
Paid to Supplier
Open Commit.
Payments
Commitments
Budget
29
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
30
Paid on Budget Code vs. Paid to Supplier
According to our system we have paid you the full
amount of 50K
But we have only received 20K
31
Paid on Budget Code vs. Paid to Supplier
32
Provisioning
Skip provisioning excursus

• Allocate money now for future expenses
• Used to balance accounts towards end of year
• DPOs are the experts on provisioning

33
Provision Columns in CET
Excursus
Last year
This year
34
How to explain the difference?
Excursus
Paid on Budget Code 257,313
Paid to Supplier 248,633
Paid on Budget Code gt Paid to Supplier
35
How to explain the difference?
Excursus
Paid on Budget Code ( 257,313 )- Provisioned
Next Year ( 15,000 ) Provision Paid This Year (
6,320 ) ------------------------------------------
-------Paid to Supplier ( 248, 633 )
Paid on Budget Code 257,313
Paid to Supplier 248,633
Paid on Budget Code gt Paid to Supplier
36
Main Building Blocks of a Financial Transaction
37
Main Building Blocks of a Financial Transaction
38
Main Building Blocks of a Financial Transaction
39
Main Building Blocks of a Financial Transaction
40
Lifecycle of an Order
Order EDH Pipe Paid Commit Open
Status CA123 111235 0 0 3000 3000 73
Order EDH Pipe Paid Commit Open
Status CA123 111235 0 2000 3000 1000 91
CET...
Order EDH Pipe Paid Commit Open
Status CA123 111235 3000 0 0 0 -
Order EDH Pipe Paid Commit Open
Status CA123 111235 0 0 3000 3000 30
41
Pipeline Order Status

• Pipeline
• Shows "committed" expenses not yet in Qualiac
• from EDH, CFU
• Amounts are estimates
• Once an order is committed in Qualiac, it
  disappears from the pipeline
• Qualiac users When deleting DAIs from Qualiac
• In order to make sure that deleted DAIs do not
  appear in the CET pipeline, the Qualiac function
  SUPDAI must be used for deletion
• Order Status
• Shows the financial status of a transaction

42
Most Important Order Statuses
Code Description
30 Transfert en comptabilité, facturation
49 Réception
52 Edition bon de réception
72 Facture détaillée
91 Mise au paiement
96 Poubelle (DAI, codes supprimés)
97 Archivage
43
Flashback Terminology in a CET Example
Paid to Supplier
Open Commit.
Payments
Commitments
Budget
Pipeline
44
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
45
CERN Specialities in CET

• Contracts
• Contract data from CFU (Contract Follow-Up) and
  Qualiac
• Draft contract expenditure is integrated into the
  "pipeline"
• Contract life cycle
• A divisional request (DR) is made to request
  money
• A market survey (MS) follows
• Invitation to tender (IT)
• Contract is concluded
• Contract is put into Qualiac

Data of thedraft contractavailable in CFU, but
notin Qualiac!
46
CERN Specialities in CET

• Contracts
• Contract data from CFU (Contract Follow-Up) and
  Qualiac
• Draft contract expenditure is integrated into the
  "pipeline"
• Contract life cycle
• A divisional request (DR) is made to request
  money
• A market survey (MS) follows
• Invitation to tender (IT)
• Contract is concluded
• Contract is put into Qualiac

Data for allphases of acontract isavailable
inCET.
47
Contracts in CET
48
Contracts in CET
49
Contracts in CET
Base Data
FC Authorized Amount
Paid to Supplier
Breakdown by Budget Code
Forecasted Expenditure
50
Contracts in CET
PDF Available now
51
CERN Specialities in CET

• Team Accounting
• Separation of CERN and non-CERN money
• Accounting is very close to bank statements
• Cash flow, budget and balances
• Two main categories of team accounts
• T1T4 Institutes, Teams etc.
• T5T9 Collaborations, Experiments
• There is a big difference in how team codes
  ofthe different categories are handled
• Sign in balance columns is inversed compared
  tothe rest of CET
• Negative amounts Money owed to CERN
• Positive amounts Balance in the team's favour

52
Team Accounting

• Teams T1T4 with expenditure ceiling
• Followup of this account is a matter of ensuring
  that the payments and foreseen expenditure do not
  exceed the ceiling
• No details by activity code and nature available
• Teams T1T4 without expenditure ceiling
• No agreed ceiling
• Spending is possible up to the limit of the
  'cash' that is available in the 'cashflow'
  account
• No details by activity code and nature available
• Teams T5T9
• Details by activity code and nature are available
• No cashflow transactions available, instead cash
  flow balance is provided

53
Team Accounting in CET
Skip team account excursus

• Four reports are dedicated to teams
• Team Transactions
• Request for Funds (Team RFF Transactions)
• Totals for Teams
• Team Graphs

54
Team Accounting in CET

• Four reports are dedicated to teams
• Team Transactions
• Request for Funds (Team RFF Transactions)
• Totals for Teams
• Team Graphs

Excursus
Cashflow Transactions
Actual Transactions
55
Team Accounting in CET

• Four reports are dedicated to teams
• Team Transactions
• Request for Funds (Team RFF Transactions)
• Totals for Teams
• Team Graphs

Excursus
56
Team Accounting in CET

• Four reports are dedicated to teams
• Team Transactions
• Request for Funds (Team RFF Transactions)
• Totals for Teams
• Team Graphs

Excursus
57
Team Accounting in CET

• Four reports are dedicated to teams
• Team Transactions
• Request for Funds (Team RFF Transactions)
• Totals for Teams
• Team Graphs

Excursus
58
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
59
CET Advanced Features

• Virtual units
• Create your own units for specific monitoring
  purposes.
• Ranges
• Query data for a range of values.
• Stored reports
• Store criterias for a particular report for easy
  reuse.

60
CET Advanced Features
Virtual Units
61
Virtual Units

• Available in CET and HRT
• Can be used in all reports
• Container for different items of various types
• Persons
• Cost centres
• Organic and PPA units
• Experiments and institutes
• Projects
• Contracts
• Items of different types can be mixed up
• Are processed at the time a report is executed
  (runtime)
• Changing a virtual unit affects all reports it is
  used in

62
Editing Virtual Units
63
Editing Virtual Units
An additional budget code has been added.
64
Using Virtual Units
Instead of only queryingthe expenses for
IT-AIS-MDS, the reportwill also search
forexpenses charged to thebudget code 47115
(IT-AIS-HR).
IT-AIS-MDS
47115
65
Ranges

• Use of ranges is possible in fields like
• Organic Unit, PPA Unit, Budget Code, Project,
  Experiment
• Specify range limits by using a colon ''
• Ranges only having a left or a right bound are
  accepted as well
• Two bounds - 4711547118
• Right bound - 47118
• Left bound - 47115
• Comma separated value lists are also possible
• E.g. for Organic Unit, PPA, Budget Code, Project
  etc.

66
CET Advanced Features
Stored Reports
67
Stored Reports

• All CET reports can be "stored"
• Very useful if a particular report is executed
  regularly and uses a non-trivial set of input
  criteria
• Storing a report means
• Saving the criteria that have been defined for it
• Stored reports can be executed via the Report
  Folder
• Accessible from the main menu
• It is now possible to schedule stored reports for
  automated and unattended execution!

68
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
69
CET Information Centre

• MDS is constantly asked
• to enhance existing report
• Add extra information
• to implement new reports
• Combination of reports A and B
• in both cases the enhancements are often of
  interest only for a few users
• Information Centre allows users to create reports
  themselves because they know best what they need!

70
CET Information Centre

• Allows
• creating personal reports targeted at specific
  needs
• sending created reports to other users
• scheduling reports for automatic unattended
  execution
• Supports
• access rights identical to standard CET
• accessing the same data as is used for standard
  CET reports
• standard and pivot style reports
• simple conditions
• creation of search criteria input fields
• different output types (HTML, Excel, PDF and XML)

71
CET IC Main Page
Toolbar
Folders
Reports
72
CET IC Report Editor 1/2
Toolbar
Tables
Joins
Selected columns
73
CET IC Report Editor 2/2
Add dynamic search criteria
Static conditions
Order results
Group results
74
CET IC Report Execution

• An IC report looks like a standard CET report
• More information is available at
• http//ais.web.cern.ch/ais/apps/cet/InformationCen
  ter/IC Main.html

75
Agenda
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
76
Report Scheduling

• Pushing data vs. pulling data
• Instead of manually executing a report each time
  certain data is required, a report is executed
  automatically in regular intervals.
• The results of the report's execution are sent by
  email.
• More than one recipient for the report results
  can be defined.
• Use cases
• Overview of my section's or group's budget.
• What's in the pipeline and coming next ?
• What's the situation of the team accounts I am
  responsible for ?

77
Report Scheduling

• What can be scheduled ?
• Almost everything !
• Information Centre reports
• Standard CET (stored) reports
• What kind of execution intervals are available ?
• Daily, weekly, monthly
• For weekly and monthly execution, the concrete
  day can be chosen (i.e. day of the week or
  month).
• Where do I get more information ?
• On the web
• http//ais.web.cern.ch/ais/apps/hrt/ReportScheduli
  ng/welcome.html

78
The Data comes to you
2) Click here to configurethe report to be
scheduledfor automated execution.
Information Center Report
1) Select the report you would like to
beexecuted regularly.
79
The Data comes to you
Standard Report
2) Click here to configurethe report to be
scheduledfor automated execution.
1) Select the report you would like to
beexecuted regularly.
80
The Data comes to you
Configure how thescheduling should work
81
The Data comes to you
Wait for the daily email,coming each
morning,once per week or once per month.
82
Agenda
The End
1 CET Overview
2 Basic Terminology
3 Financial Concepts
4 CERN Specialities
5 Advanced Features
6 CET Information Centre
7 Report Scheduling (Pushing Data)
83
Additional Resources

• Slides of this presentation
• http//ais.web.cern.ch/ais/presentations/welcome.h
  tml
• Online help pages
• http//ais.web.cern.ch/ais/apps/cet/
• Guided tour and team transaction tutorial
• http//ais.web.cern.ch/ais/apps/cet/tutorial/tutor
  ials.html

84
The End.

• Questions answers
• Thank you for your attention

L'argent .. est un bon serviteur et un mauvais
maître.-- Alexandre Dumas Fils ("La dame aux
camélias")