Transcript UCL Finance Reporting Project Reports Training Presentation Version 1.0 UCL Finance Reporting Project BOP Reports Training Session Presenters: Ian Davis, Senior Management Accountant Jonathan Thompson, Project.

UCL Finance Reporting Project
Reports Training Presentation
Version 1.0
UCL Finance Reporting Project
BOP Reports Training Session
Presenters:
Ian Davis, Senior Management Accountant
Jonathan Thompson, Project Manager
Session Duration: 2 ¾ Hours
Location:
Training Room, Second Floor
UCL Union
Huntley Street
UCL Finance Reporting Project
BOP Reports Training
Name
School/Department
Ross James
BEAMS
Syed Murshed
BEAMS
Andrew Brown
Co Ly
CSS
Asif Bhatti
CSS
Nadir Chowdery
Robert Ebsworth
Mystery guest
UCL Finance Reporting Project
BOP Rollout Training
Course structure:
• Overview of Financial Reporting Project and Reporting Process.
• Intro to Business Objects Planning Reporting accessing reports in the BOP system.
• Understanding the BOP Data - that is available for use in reports
• How to write BOP reports - hands-on practice and report building exercises in BOP
• Session wrap-up
• Helpdesk, Feedback
UCL Finance Reporting Project
Annual Reporting Calendar
AUG
SEPT
Quarterly
Process
Reporting
NOV
Prepare
End Q4
Adjts
Finalise
Year End
Accounts
JAN
FEB
MAR
Q1
PE
APR
MAY
JUN
Q3
Forecast
5+7
2+10
Prepare YE Accounts
DEC
Q2
Forecast
Q1
Forecast
Forecast
Period End
OCT
Example
8+4
Q2
PE
Q3
PE
Update of
Actuals from FIS
Budget
Process
Prepare
target
setting
Budget
Consolidate
& Finalise
Targets
Update
detailed
Budgets
JUL
UCL Finance Reporting Project
Standard Data Update Processes Impact on reporting data
STANDARD QUARTER PERIOD
Start of New
Quarter
MONTH 3 OF QUARTER PERIOD
QUARTERLY FORECAST UPDATE
Depts
Entry
Depts
In Year
Adjustments
Faculties
Workbooks
Allocations
& Reserves
MTH 1
MTH 2
WEEK 1
WEEK2
WEEK 3
WEEK 4
MTH 1
MTH 2
Departments enter forecasts, allocation, reserve
adjustments
Departments update forecasts,
allocations, reserve
adjustments on individual
accounts as required
Departments update forecasts, allocations,
reserve adjustments on individual accounts
as required
Finance/Faculties enter allocations, transfers of reserves for upload to department accounts
- Consider when the report will be run and which data should be included within it
- Approved or unapproved data?
- Include IYA and AR adjustments?
Current BOP Reporting
What information do I want? - Reports and Print Styles
• Print styles are used to print out parts of a workbook
– They are accessed from the workbook
– They are limited to data within the current workbook
– They can have a limited number of options/parameters to select or limit data
shown
• Reports are available to report on data across the database at various levels
– The data shown can be limited to an appropriate level in the security hierarchy
– A report can consolidate data across departments/divisions etc
– A report can only include data from workbooks that have been saved back to the
database
• Standard Reports already exist
• Additional report may already have been created
– Report catalogue from the homepage
• Refer to Current Report layouts and current print style document
Introduction
Types of reporting requirement
• Process related
– To assist in the process of completion and approval of budget/forecast or
adjustment processes
– E.g. to review information with budget holder or to review information with
HoD for approval
• Periodic
– Management information/departmental performance
– E.g. Report 1 – Income and Expenditure statement
• Analytical/exception
– To help review data based on some criteria
– E.g. material accounts, variance analysis, overspends, range or type of
accounts
Current BOP Reporting
Levels of Summary Reporting
Different types of summary reports will be required for different users:
Report
Description
Report Format 1
Standard top-level I&E reporting format
Report Format 2
Standard I&E reporting format by Analysis Group
Report Format 3
Expenditure breakdown by area
Report Format 4
Income breakdown by area
Report Format 5
Summary Report by Funding Source
Report Format 6
Standard I&E Reporting Format by Account Code
Report Format 7
Target Setting Report
Current BOP Reporting
Levels of Summary Reporting
The report requirements by reference to the report formats and the users requiring them is as follows:
Report
Finance Committee and
SMT
Deans and Vice
Provost (Admin)
Heads of Academic
Divisions/Departments Heads of CSS Divisions
Report Format 1



Report Format 2



Report Format 3


Report Format 4


Report Format 5


Report Format 7
Drill levels relating to the
account hierarchy


Report Format 6
Individual
budget
holders



0-3
2-5
4-7

6-7
UCL Finance Reporting Project
Introducing Business Objects Planning Reports:
• Logging on
• Homepage
• Reports
- Accessing
- Menu Structure
- Data Security/Access
- Running
-Drilldown
- Speed Drill
- Report drill
- Detail drill
-Saving
-Printing
-Emailing
Report Writing Introduction
•What’s the requirement?
•Layout
•Data
•Does it already exist? – Report catalogue
•Process for creating a report
•Define the requirement
•Produce a design - template
•Build using the report wizard
•Test
•Submit to report catalogue
•Directory structure/naming convention
Defining a report
•
Designing a report - template
–
–
–
–
–
•
Style
Layout – Rows, Columns, Totalling
Sorting
Drilling
Filters
Report types
– Fixed
– Variable
•
BOP report Help
New Requirements
Requirements template
Description
Fixed / variable
Time series
Layout - Columns
(dimension, hierarchy)
Rows
(dimension, hierarchy)
Totals
Filters (dimension,
hierarchy, data)
Options /
parameters
Frequency /
audience
Reporting data
•Data
•Dimensions - the independent data tracking items of a system. Each row
(record) in a data source is defined by a unique combination of dimension
elements.
– Account
– Analysis Code
– Version
•Versions
•Time series
•Hierarchies
•Calculations/formulas
Current BOP Reporting
Data Available - Versions
• The main financial data is held in the system in “versions”
• Each version contains a number of time series –
• Current and Previous Year Monthly balance (August – July, period 13),
• Current and Previous Year Q1-4 Totals,
• Current and Previous Year Year To date,
• Current and Previous Year Yearly Total
• Future year total for 4 years
• Per account and analysis code combination
Versions
WAR
CFC
FQ1/2/3/4 ACT
BUD
Working Unapproved
Position
Latest Approved position
Previously Approved
Positions
Latest Approved position
(Working Versions)
Workbook Data
(Primary Version)
Current Forecast
(Archive Versions)
Quarter 1
Quarter 2
Quarter 3
Actuals
(Primary Version)
Budget
Current BOP Reporting
Data Available - Versions
Version
What’s included in it?
WAR
Current working version in progress work book updates including AR
updates
CFC
The latest forecast including AR, IYA adjustments and Financial Adj
FQ1/2/3/4
Archive of previously approved forecasts – don’t get updated by
subsequent IYA or AR adjustments
BUD
The Approved budget
Current BOP Reporting
Hierarchies – report drill down and data access
In view of UCL’s existing account code structure, four separate hierarchies will be required:
Main Hierarchy :
Linking account codes into the management reporting structure so that
reports at the various levels can be produced
Analysis Hierarchy :
Linking analysis codes to the I & E Headings to produce the totals and sub
analysis for types of income and expenditure
Source Hierarchy :
Linking source codes to Funding Source to produce the ‘Operating Budget’
position within any level of report. Also required to produce RAM reports.
Budget Manager Hierarchy : Linking account codes to Budget Manager will be available when the
budget manager data is populated
The Main Hierarchy is set out in diagrammatic on the following page.
There is no Unit code hierarchy within BOP
Data is held at the account balance level – there no transaction data held within BOP
Current BOP Reporting
Main Reporting Hierarchy
Level
CONSOLIDATED
UCL
0
UCL
1
SUBSIDIARIES
ACADEMIC
CORPORATE
SUPPORT SERVICES
SCHOOLS
CSS GROUP – e.g. ESTATES &
FACILITIES
FACULTY
CSS GROUP – e.g. ESTATES &
FACILITIES
4
DIVISION
(includes Faculty Offices)
AREA - Premises Estates
Administration
5
DEPARTMENT
DEPARTMENT - Cleaning Services
Postal Services
6
BUDGET
MANAGER (once populated)
BUDGET
MANAGER (once populated)
7
ACCOUNT
CODE
ACCOUNT
CODE
1a
2
3
Current BOP Reporting
ANALYSIS HIERARCHY
CONSOLIDATED
UCL
INCOME
EXPENDITURE
I&E HEADING
I&E HEADING
ANALYSIS GROUP
ANALYSIS GROUP
BUDGET CODE
BUDGET CODE
BUDGET ANALYSIS
CODE
GL ANALYSIS
CODE
BUDGET ANALYSIS
CODE
BALANCE SHEET
GL ANALYSIS
CODE
OTHER
ADJUSTMENTS
Writing Reports
• Report writing Wizard
– Menu driven wizard for writing report
– Possible to amend afterwards - by support team
• Report example exercises 1-4
Standard reporting
• Updating default format
–
–
–
–
Hiding of system rows and columns
Amendment of prompts, total descriptions etc
Insert columns
Fonts/colours - standardise
Advanced reporting
• Exception
• Scheduling
• Report packs
– Sequential
– Assembling
– Batch
• Manual report writing/updating
• Planning Functions
Index - Appendices
Report Format 1 - Standard Top Level I & E Report
Report Format 2 - Standard I & E Report by Analysis Group
Report Format 3 - Standard Expenditure breakdown by area
Report Format 4 – Standard Income breakdown by area
Report Format 5 - Summary Report by Funding Source
Report Format 6 - Standard I & E Report by Account code
Report Format 7 - RAM Report
Report Design Template
Report Format 1 – Standard Top Level I & E Reporting Format
Report Format 2 - Standard I & E Reporting Format by Analysis Group
Report Format 2 - Standard I & E Reporting Format by Analysis Group
Report Format 3 – Expenditure breakdown by Area
Report Format 4 – Income breakdown by Area
Report Format 4 – Income breakdown by Area - continued
Report Format 5 – Summary Report by Funding Source
Report Format 5 – Summary Report by Funding Source – continued
Report Format 6 - Standard I & E Reporting Format by Account code
Year
£k
Budget
£k
Actual
£k
Variance
£k
Var.
%
Year
£k
Budget
£k
Actual
£k
Variance
£k
Var.
%
Year
£k
Budget
£k
Forecast
£k
Variance
£k
Var.
%
DA53- Account name
Income
Staff costs
Other Operating Expenses
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
DA54 - Account name
Income
Staff costs
Other Operating Expenses
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
DA55 - Account name
Income
Staff costs
Other Operating Expenses
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
DA56 - Account name
Income
Staff costs
Other Operating Expenses
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
TOTAL
Income
Staff costs
Other Operating Expenses
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0%
0.0%
0.0%
Report Format 7 – RAM Report
Key terms used
Budget Forecast/
Revised Forecast -
the initial position for the year as approved by Finance Committee and Council.
Adjustments may be made to reflect internal transfers.
the latest projected position for the year consisting of actuals to date plus forecast activity
for the remainder of the year.
Out-turn -
the final position at the year end as per the financial statements.
Operating Budget -
income and expenditure associated with UCL’s core routine business funded from general
income (ie not given to cover specific costs) e.g. HEFCE Block Grant, tuition fees and
contributions to indirect costs. This activity is recorded on accounts with source codes 0
and 1.
RAM -
Resource Allocation Model is used to set faculty-based budgets and to monitor the
financial position of the academic departments / faculties. The RAM is based upon the
Operating Budget position but brings in other activities on a self financing basis together
with the reflection of transfers to and from reserves. The RAM also reflects the distribution
of central costs to academic departments/faculties using a number of financial and nonfinancial cost drivers. This distribution will be calculated outside of the solution when the
budget is set and the amounts will remain largely fixed throughout the year.