EDUCATION FINANCIAL SERVICES
REPORTS PACK

Finance reports user guide for Oxfordshire County Council

maintained schools

Revised September 2017

1

Contents

Budget vs forecast

Weekly/monthly transaction details8

Staffing actual costs3

Glossary of terminology...... 31

Further help

Budget vs forecast

This report presents a detailed analysis of expenditure/income for a costcentre or cost centre group(s), which enables budget managers to review expenditure and income against a budget and help highlight areas where corrective action may be required. For help with some of the terminology used in reports, please see the Glossary section at the end of this guide.

1.Navigate

All reports (revenue and capital, g/l) can be obtained through the following route:

IBC Portal > Finance >Reports Revenue monitoringBudget vs forecast

The report will automatically run for the cost centre group that you have budget management responsibility for. In the absence of a cost centre group being assigned to your OM position, the report will run at the top level of your organisation.

For information: If your cost centre group default is incorrect or missing, your line manager needs to submit an IBC enquiry, detailing the change required to your default cost centre group. When completing the form please select the “Position changes and restructures” option from the “type” field and the “missing a cost centre group” option from the “enquiry” field to ensure the form is routed to the correct team.

2.Variable entry

If you wish to alter the selection criteria, you can change the variables by clicking the “variable screen” button on the top left.

The variable entry screen will open and will default to your school’s cost centre or cost centre group.

Upate the variable entry fields:

  • Controlling Area: this field will default to your organisation's controlling area – OCC schools 9020.
  • Fiscal year/period: this field will default to the open reporting period and current fiscal year. Leave as default, or enter the periodand year you would like to run the report against. Please note that the open period changes mid-month.
  • Cost centre hierarchy: this field will default to your organisation's cost centre hierarchy. It should show ‘OCC Management Reporting Hierarchy’.
  • Cost centres or CC group: Your school cost centre(s) or cost centre group(s) will be the default for this field.For all cost centres, type in9020EDXXXXALL, or for one individual cost centre, type in9020/EDXXXX_, the last two digits being the cost center code e.g.10, 75 etc.
  • Cost element hierarchy: OCC schools should use the drop down and select ‘OCC Consistent Financial Reporting’.
  • Cost element hierarchy node: a default cost element group will be displayed. Click on the drop-down and remove ‘9020 OCCCFR OCC Consistent Financial Reporting’ from the right hand box (click to highlight and select ‘remove’). In the left hand box select ‘revenue’ or ‘capital’ and click on ‘add’ to move it to the right hand box.

Click OK.

Tip: You can save the criteria you have selected on the variable entry screen as a variant by clicking on ‘save as’, and giving the variant a name in the description box. When saving the variant, leave the fiscal year/period box blank. To select this variant when running the report, you need to select it from the ‘available variants’ box.

Click OK to run the report.

3. Budget vs forecast for period

The ‘Budget vs forecast for period’ screen will open.

Schools should then select ‘alternate layout’.

It is recommended that schools use this layout as it displays a set of columns which are specific to school requirements.

You can expand or collapse sections of your report by clicking the black arrows until the required level of information is displayed.

Alternatively, you can expand the whole report.

Right click on ‘cost element’ and select ‘hierarchy’ > ‘expand hierarchy’,then select the required level, i.e. level 04to expand the report to CFR level, or level 05to see it at GL level (the required level will depend on your own preference and the level of information required).

Once expanded, you can navigate through the report using the slide bar at the bottom of the report to scroll to the right, and the arrows to page up and down.

Explanation of the columns:

Original budget: Governor approved budget plan.

Budget adjustments: Variance between the originalbudget plan and revised budget plan (annual budget plan).

Annual budget: Governor approved revised budget plan.

Period actuals: Actuals for the period for which the report has been run.

Actuals YTD: Actuals for the year to date.

Commitments: Commitments in the period for which the report has been run.

YTD committed spend: Total expenditure/income to date.

YTD variance: Expenditure – balance to spend. Income –balance to receive.

YTD variance (%):Expenditure: Balance to spend (%). Income: Balance to receive (%).

Outturn forecast: Revised budget plan (not governor approved)

Outturn forecast variance: Variance between the ‘annual budget’ column and the ‘outturn forecast’ column.

4. Actual internal charges

For this report only, please change the variable entry fields as follows:

Please use the Cost Element Hierarchy ‘BW OCC Rev’ for this report ONLY.

  • Click on the drop down icon for the Cost Element Hierarchy Node.
  • In the search box enter 9020OX-RCHGEXP.
  • Press the enter key.
  • Double click on the result and add to the right hand column.
  • Remove the first line 9020ox-current occ Net Current Expenditure
  • Press OK

To see any internal recharge income, enter 9020OX-INCOME in the Hierarchy Node.

5. To run cost centre reports for your monthly reports

Procurement card default code - cost centre 05

Direct credit account -cost centre 02

Check 00 cost centre for other than HR

6. Add fields to your report

You can bring additional fields into your report. Available fields are shown under the ‘free characteristics’ heading on the left of the screen. Click and drag the item you would like included in your report, and drop the field onto the ‘rows’ or ‘columns’ field.

7. Drill down to actual line items

Using the mouse, right click on the amount you would like to drill down on. A menu will be returned, select ‘goto’, followed by ‘actual transactions’.

The ‘actual transactions’ screen will be returned, detailing the actual transactions. If there is more than one page, use the arrows at the bottom of the page to move to the next page.

You can add additional fields (free characteristics) to this report. To do this, please see step 6 of these work instructions.

Drill through to Accounting Document

From the actual transactions report, you can drill through to view the details of an individual accounting document. To do this, right click on the ‘document ref number’of the line you would like to drill through on, and click the right mouse button. A menu will be returned. Select ‘goto’, followed by ‘accounting document’.

Note: You cannot drill through to the accounting document for payroll or purchase order related postings, i.e. document types WE (goods receipt), RN (supplier invoice prior IBC), ZN (supplier invoice IBC), AB (accounting doc).

Drill through to Purchase Order Details

You can also drill through to view details of a purchase order. To do this, right click on the ‘PO number’ of the line you would like to drill through on. A menu will be returned. Select ‘goto’ followed by ‘PO details’.

Drill through to Purchasing History

From the Purchase Order Line Items report (above) you can drill through further to view invoices against individual purchase lines. To do this, hover your cursor over the ‘PO doc number’, and click the right mouse button. Select ‘goto’ followed by ‘purchasing history’.

8. Drill down to commitment line items

Drill down from the report summary to commitment line items. Hover the cursor over the value in the ‘commitments’column that you would like to drill down on and click the right mouse button. A menu will be returned. Select ‘goto’, followed by ‘commitments’.

You can add additional fields (free characteristics) to this report. To do this, please see step 6 of these work instructions.

Drill through to Purchase Order Details

You can also drill through to view details of a purchase order. To do this, hover your cursor over the ‘Ref doc no/Reference document no’ of the line you would like to drill through on and right mouse click. A menu will be returned. Select ‘goto’, followed by ‘PO details’.

9. Go back (one screen)

When you have carried out more than one action, you can go back one navigation step at a time. Do not use the back button on your internet browser. Right mouse click anywhere on your report to display a drop down menu, select ‘back’, then either:

  • Back one navigation step - to go back one step, or
  • Back to start - to return to the original (default) view of your report.

Alternatively you can close the tabs at the top of the screen to go back.

10. Send report

At the top of your screen, click Send.

The Broadcasting Wizard window will open.

Step 1: Determine basic settings

In the ‘output format’ field, select ‘PDF’ from the drop down list. Click ‘continue’.

Step 2: Determine page layout

In the ‘orientation’ field, select ‘landscape’ from the drop down list. Click ‘continue’.

Step 3: Enter E-Mail message

Enter the email address of the recipient and a subject. Enter any additional text you would like the recipient to see in the contents box. Click ‘execute’ to send the email.

11. Print report

At the top of your screen, click ‘print version’.

The ‘export dialogue’ window will open.

Make the following changes:

  • Theme: select ‘black and white’from the drop down list.
  • Alignment: select ‘landscape format’ from the drop down list.

Click ‘OK’ to continue.

A PDF file will be produced. Click the printericon, then click ‘print’.

Example printout

Page 1

Page 2

12. Export report

At the top of your screen, click ‘export to Microsoft Excel’.

A message will be displayed asking if you would like to open or save the file. Click ‘open’. The following dialog box will appear:

Click ‘Yes’. An Excel workbook will open.

Click ‘enable editing’ if you would like to make changes to the worksheet.

Weekly/monthly transaction details

The instructions below explain how to change the date range for your Budget vs forecast report to show transactions for a required period.

Run the Budget vs forecast report.

Complete the Variable Entry screen for the current period and click OK.

When the report opens, click on Alternate Layout – to show schools preferred layout.

Once the alternate layout has opened follow the steps below.

The ‘actual transactions’ screen will open. You now need to bring the ‘created on’ date into the report.Click on ‘created on’ under free characteristics on the left had side of the report and drag upwards into ‘rows’ section, and release.

(The ‘created on’ date is preferable to the posting date as this date cannot be changed. For example, if a transaction is created on a date within the period you wish to run the report for, but is posted back into the previous week or period, it may be missed. However if ‘created on’ date is selected, then it will show in the report.)

Select date range required

Click on the word Filter which is in blue to the far right of the black menu bar.

The next screen will allow you to choose from a number of variables to suit your search preferences.

The next screen is the ‘Select values for created on’

In the ‘show tool’ drop down, select ‘value ranges.

Enter the date range you wish to view in the ‘from’ and ‘to’ boxes.

Click ‘add’.

The date range moves to the ‘selections’ box:

The report will now show the transactions for the period required and can be sorted by date, GL (cost element), value etc. by clicking on the small grey arrow next to the column title.

Staffing actual costs

This report provides a detailed breakdown of staffing actual costs paid through payroll. It details all payments made to each member of staff, including car allowances, staff travel expenses and other employee expenses (for example: professional subscriptions, training expenses).

Please note:

  • Only managers and finance staff have access to run this report.
  • For this report to be accurate, your Organisational Management (OM) structure must be up to date, and cost centre assignments against positions must be correct.

For help with some of the terminology used in reports, see the Glossary section at the bottom of this guide.

1. Navigate

Open the IBC Portal and click on the following;

Finance folder > Reports > Staffing costs > Staffing actual costs

The report will automatically run for the cost centre group that you have budget management responsibility for (the cost centre group assigned to your OM position).

For information: If your cost centre group default is incorrect or missing your line manager needs to submit an IBC Enquiry, detailing the change required to your default cost centre group. When completing the form please select the “Position changes and restructures” option from the “Type” field and the “Missing a cost centre group” option from the “Enquiry” field to ensure the form is routed to the correct team

2. Variable entry

The ‘variable entry’ screen will open and will default to your school’s cost centre or cost centre group.

Update the variable entry fields:

  • In Period Range: This field defaults to the current period and current fiscal year. Leave as the default, or enter the period and year range you want to run the report against.
  • Cost Centre Hierarchy: This field will default to a cost centre hierarchy, it should read ‘OCC Management Reporting Hierarchy’.
  • Cost Centres or CC Group (mandatory): Your school’s cost centre(s) or cost centre group(s) will be the default for this field - 9020EDXXXXALL. You can type in an individual cost centrein the format9020/EDXXXX_ _, the last two digits being the individual cost centre code.
  • Cost Element Hierarchy – select from the drop-down pick list

Click ‘OK’to run the report.

3. Report summary

The fields you see in the report are:

  • Employee Start Date - the start date for an employee if they started after the beginning of the current financial year
  • Employee End Date – the leaving date for an employee if they left after the beginning of the current financial year
  • Basic salary - the amounts paid as basic salary for the current period
  • Overtime - the amounts paid as overtime for the current period
  • Additional payments - the amounts paid as bonuses and allowances for the current period (includes staff expenses)
  • National Insurance - the National Insurance contributions for the current period
  • Pension - the superannuation (pension) contributions for the current period
  • Total Paid - the total actual payments made for the current period
  • FTE annual basic salary - the basic salary for a full-time equivalent member of staff (i.e. based on a full-time employee who works 37 hours per week, 52 weeks per year)
  • Actual annual salary (basic) - the basic salary for a member of staff based on their current FTE (based on their actual working hours and paid weeks)
  • Weekly hours - the number of hours per week a member of staff works
  • Paid weeks - the number of weeks per year a member of staff is contracted to work. Variances to the standard 52 weeks per year may occur where staff work term time only for example
  • Current FTE - the actual FTE (out of 1) of the member of staff in post
  • Position FTE – the position FTE (out of 1) of the member of staff in post.

To view all the above columns you will need to move the scroll bar at the bottom of the page to the right. This will then display the left to right arrows to move to the next page.

If there is more than one page, use the arrows at the bottom left hand side of the window to scroll through the subsequent pages.

4. Add fields to your report

Available fields are shown under the ‘free characteristics’ heading on the left of the screen. Click and drag the item you would like included in your report (e.g. pay scale group) up to the ‘rows’ field and drop when ‘rows’ is highlighted in blue.