Guidance

User guide for automating the academies accounts return and draft financial statements

Updated 16 April 2024

Applies to England

1. General information - automating the academies accounts return

This section includes:

1.1 What this guidance covers

This guidance is intended to support and assist trusts using the automation process to send data from their financial management system (FMS) to the Department for Education (DfE) for completing the 2021/22 accounts return (AR) and draft trust financial statements.

1.2 Introduction to automation

The department has developed automation technology to help reduce the burden of submitting your financial information. This technology enables you to electronically submit your trial balance data directly from your FMS to the department. This data is used to pre-populate the majority of your AR and the trial balance information needed for the trusts financial statements.

There are 3 pre-requisites for using automation:

1. The trust has either adopted the CoA natively or is mapping to it via their FMS software provider – see ‘Guide to adopting the academies chart of accounts’
2. The trusts software provider has developed the automation – see automation availability
3. The trust has assigned and registered a ‘data transfer approver’ role within the trust on IDAMS – see ‘Academy trust IDAMS guidance for financial returns’.

Academies accounts return (AAR) online form: automating your return

1.3 Automation availability

We are working with an increasing number of software suppliers in the sector to make this technology available to as many trusts as possible over the next few years. Please contact your FMS supplier to find out if they can automate your financial data.

FMS software providers who are able to provide automation functionality to trusts’ for the AR 2022/23 return are:

• Access
• Bromcom
• Dayta (Sage 200, Sage Education & Sage Intacct)
• ESS (FMS6 & SIMS)
• Hoge 100
• ION (Sage Intacct)
• IRIS (Version 6 & 7)
• Solutions for Accounting (Sage 200)
• Thorne Widgery (Xero)

Trusts using the academies CoA and automation are able to:

• pre-populate a high percentage of the accounts return (AR) 2022/23
• make use of the draft financial statements for 2022/23
• partially populate the budget forecast return (BFR) 2024 (NEW for AR 2022/23)

If your trust is looking to upgrade their FMS software so you can take advantage of using the academies CoA & automation, please review our webpage Choosing a trust’s financial management system (FMS) - GOV.UK.

1.4 Timelines for the availability of on-line forms

Please see the table below for the timelines of when trusts can automate their financial data to populate the draft trust financial statements and the AR for the 2022/23 collection period.

1.5 Guidance review date

This guidance is applicable to the automation process for completing the 2022/23 AR only. The next review of this guidance will be in September 2024

2. Getting started

2.1 IDAMS registration and roles required

To send data from your financial management system, all academy trust users will need to be set up on the DfE secure identity management service called IDAMS and assigned the relevant responsibility.

2.1.1 IDAMS registration

1. all academy trust users sending data from their financial management system need to be assigned a Data Transfer Approver role for their trust
2. a super-user must assign or approve all requests for the data transfer approver role
3. for new trusts, all users will need to be set up, starting with the super-user (please do this at the earliest opportunity)

For further information on how to get set up and assign roles, please click on the following link: academy trust IDAMS guidance for financial returns.

2.1.2 IDAMS user roles required for Automation

Role	Description	Type
Super-user	Can invite new users to register and allocate roles to already registered users within the IDAMS registration system. This role is administrative and only applicable to the IDAMS registration system.	Mandatory
Data transfer approver	Can electronically submit your trust’s trial balance data directly from your financial management software to the department	Mandatory for automation

ACTION FOR TRUSTS: Ensure you set up a data transfer approver on IDAMS otherwise you will not be able to send data from your FMS to the AR

3. How automation works

This section includes:

3.1 Automation flowchart

The department has developed an application programming interface (API) which enables you to send data to the department via your financial management system (FMS). This flowchart provides a summary of the steps taken during automation.

Summary of the automation process

*draft FS - draft trust financial statements: the workbook will include prior year data if the trust sent their data via an API in the previous financial year, and it will be the final data set loaded so please check for accuracy against your final financial statement.

3.2 Sending your data

Trusts retain control over when their data is automatically shared with the DfE. Data will only be shared with the DfE when:

1. the data transfer approver, assigned by the trust, chooses to send their FMS data and
2. the data is accepted into the online form when a user, designated by the trust, accepts it, as described in section 3.6.

In order to send your data to the DfE, please ensure you have registered in IDAMS and been assigned the data transfer approver role as described in section 2.

You must then log into your FMS to send your data to the DfE. Please contact your supplier for detailed instructions on how to send your data to the DfE from your FMS. The process may vary between suppliers.

The user sending the data will be asked to confirm their username, password and that they are happy for their financial data to be transferred to the department for education. In approving the transfer of data to the department, the user is authorising the department to use that data for the purpose of populating the AR form.

3.3 Types of FMS data included in submission

Each supplier has different functionality set up, but your submission could include up to four data sets.

Data Type	Area of the AR populated:
Trust trial balance	Fields within the ‘SoFA’, ‘balance sheet assets’, ‘balance sheet liabilities’, ‘balance sheet funds and other disclosures’; and ‘transfers and conversions’.
Academies trial balance	The ‘academies’; area of the benchmarking and land and buildings sections.
MAT central services	The ‘MAT central services’; area of the benchmarking and ‘centrally held’; area of the land and buildings section.
Trust counterparty transactions	The ‘counterparty transactions with the academy sector’; area of the counterparty section.

Please see the chart of accounts webpage for a more detailed breakdown on each of the above mappings.

3.3.1 Trading Subsidiaries

For those trusts whose have a trading subsidiary which is operating as part of the same legal corporate entity as the trust.

• If the accounts for a trading subsidiary are recorded on a separate financial management system (FMS), then it is currently not possible to include your data within the automation processes, unless trusts journaled all the data from the trading subsidiary to the trusts main accounting system at the year end, as the API can only accept a single data feed into the AR. However, if the FMS software provider for the trading subsidiary has adopted the CoA, the trust could also adopt the CoA on that system so that the information across the two systems is consistent and would improve efficiencies, such as mapping data to AR fields and making inter-company journals more transparent.
• If the trading subsidiary is simply set up on a separate cost centre on the same FMS, then there will be no problem in automating this data. For the purposes of automation, this trading cost centre data would need to be tagged as ‘matOverview’ or ‘Academies’ by the FMS provider, see footnote below. Also, if the trading cost centre is specific to one academy only, then it should also be tagged with the LEA Establishment number by the FMS provider for automation.
• If the trading subsidiary is set up as a separate ‘entity’ on the accounting system, even though it is part of the same legal corporate legal entity, the trust will need to check with their FMS provider whether this data can be amalgamated with the trusts main ‘entity’ to automate the data in one upload.

Footnote: if the trust is a MAT, it is up to the trust to decide where to report the trading organisation on the benchmarking, it should be included within the relevant academy if the trading is solely related to that academy; a test of which would be if the academy were to move to another trust, would the trading subsidiary also move. However, if the trading applies to two or move academies within the trust it should be allocated to MAT central services.

3.4 API validations

An application programming interface (API) is a set of programming code that enables data transmission between your FMS and the DfE. When you choose to send data to the DfE in your FMS, your software supplier will call the API. The code in the API will then perform several validations on your FMS data submission and if your data meets the criteria, it will be successfully accepted into the DfE database. It will be rejected if any of the following reasons occur:

1. your trial balance doesn’t balance
2. there are non-numerical values
3. it contains a number with more than 3 decimal places
4. you have sent academy data identifiers that do not match the records the department holds for your trust. More information on this can be found in the validating your trust section.

Your FMS supplier understands the validations that will be carried out on the data submitted and where possible will be sending the data to meet these requirements.

3.4.1   Pre-population of academy data for SAT’s

The AR form now automatically populates much of the academy level data for SAT’s on the land and buildings and benchmarking tables, this will mean that the trust trail balance data overwrites the academy data, and therefore, if different on the trusts FMS, will not display the same information as uploaded. The AR validation will also not be triggered as the overwritten data is now the same in both AR fields.

The FMS mapping report, will however, still show the values imported from a trusts FMS. not the value overwritten from auto-population in the academies data fields, therefore the data in the AR form will be mismatched to the FMS mapping report.

As we do not foresee a situation where SAT’s trial balance and academy data should ever be different, if this issue occurs, the trust should consider making the necessary adjustments on their FMS to match the value shown on the AR’s academy level table. You may also wish to resend your data after this adjustment has been made, but it should be noted that all trail balance, academies and counterparty data will be overwritten if this is done.

ACTION FOR TRUSTS: If your trust is a SAT, please ensure that the values sent in your academy data are the same as the values in your trust trail balance before sending the data to the DfE.

3.5 Validating your trust

Your supplier has set up your submission so that trust level data will include a unique identifier such as your company registration number or trust UPIN. Your academy level data will also be sent with a unique identifier for each of the academies in your trust, their LA Establishment Number (LAESTAB).

When your data is sent, the API will cross check these identifiers with department records to validate your submission. The submission will be rejected if the trust UPIN or company number is not recognised, if the LAESTAB is not recognised as being associated with that trust, or if the person trying to give consent does not have the correct role for that trust.

You can send academy level data for any number of academies within your trust but the LAESTAB number must match our records for all the academies that you send data for or the whole submission will be rejected.

Once your data has been successfully submitted via the API it will be transferred into the department’s database.

3.6 Applying submitted data to your accounts return

When you next sign into your AR you will be taken straight to a data uploads page where you will be able to decide what to do with your data. Please note, this can only be accessed by users who have been assigned one of the following roles:

• accounts return internal preparer,
• accounts return internal approver or
• external auditor preparer.

External auditor approvers are not able to select what happens to the submitted data.

If you are already logged into the AR when you submit the data via your FMS, the user logged in will see a “Data uploads” link and a “Pending” tag in the right hand corner of the screen.

If you click on this, you will be taken to the data uploads page where you will see a choice of actions of what to do with the data.

The following information is provided to ensure you recognise the submission:

• Initiated by: Identifies who in your organisation sent the data from your financial management system

• Date and time: Identifies when your organisation sent the data from your financial management system

You have the following choice of actions available and you must select one to enable you to leave this screen:

• Apply: the data will be used to pre-populate the trial balance cells in your AR. Please note, that if there is already data in these cells they will be overwritten by this new data upload. Non-trial balance fields will not be impacted. You will receive a warning message to confirm this before the acceptance is performed. Following an import, page statuses will update and validations will clear or trigger according to the new data. Further validations may trigger as you go on to complete each page.
• Reject: the data is deleted and will not pre-populate your AR. If you decide that you should have accepted the upload, you must resubmit the data via your financial management system. You will receive a warning message to confirm this before the rejection is performed.
• Ignore: the data will not pre-populate your AR but it is saved in the background so that you can return to this screen later to accept or reject it. If you submit more than one set of data without accepting or rejecting it, only the latest submitted data will be available, therefore, you will lose your previously ignored uploaded data. You can access this ignored data through the “Data Uploads” link which is located on the top right hand side of every screen.

The data uploads page will keep track of your data acceptance/rejection history. You can see this at any time in the ‘Activity History’ section on the bottom right hand side of the index screen. The data uploads page will keep track of your data acceptance/rejection history. You can see this at any time in the ‘Activity History’ section on the bottom right hand side of the index screen.

If a data upload is pending you will see a tag on the fixed “Data uploads” link at the top right of the screen. Clicking the link will take you to the screen to action the new pending upload

4. Data transformation

This section includes:

4.1 Data transformation - accounts return

The data sent in the FMS submission is formatted to meet the AR requirements. This includes four main activities:

1. sub-totalling of chart of account codes to AR field level following the mapping described in the chart of accounts

2. the sub-totalled values are rounded to the nearest £000

3. due to the rounding occurring in step two, the residual values left after rounding are allocated to certain AR fields throughout the form. These are listed in the table below.

4. the signage of the values is flipped on certain account codes e.g. your income and liability codes are likely to contain negative values in your FMS but the AR may require you to report them as positive values

Type	Rounding approach	AR field allocated to:	AR table
Trial balance	Net all residual values from asset codes	DEB110-A	Other debtors
Trial balance	Net all residual values from liabilities codes	CRD060-A	Other creditors
Trial balance	Net all residual funds codes	RGF080-E	Other gains, losses and transfers
Trial balance	Net all residual values from expenditure codes	OEX010	Other expenditure
Trial balance	Net all residual values from income codes	INC010	Other income
Benchmarking - academies	Net all residual values from academy level expenditure codes	BAE280-A	Other expenditure
Benchmarking - academies	Net all residual values from academy level income codes	BAI080-A	Other income
Benchmarking - central service	Net all residual values from central services expenditure codes	BTE280	Other expenditure
Benchmarking - central service	Net all residual values from central services income codes	BTI080	Other income
Counterparty	Net all residual values from Asset codes relating to counterparty fields	CPD050	Debtors under one year - other debtors
Counterparty	Net all residual values from liabilities codes relating to counterparty fields	CPL050	Liabilities less than one year - other creditors within one year
Counterparty	Net all residual values from expenditure codes relating to counterparty fields	CPE070	Purchase of goods and services
Counterparty	Net all residual values from income codes relating to counterparty fields	CPI100	Other income

Data transformation (accounts return) - worked examples

Here are two worked examples of how data is transformed from what you submit through your FMS to how it is displayed in the AR. There are further examples in the calculated fields section for opening balances and funds.

Example for AR field CAD010

FMS data received:

CoA Account	CoA Description	Value (£)	AR Mapping
612100	SF: Teachers: Wages and salaries	1,754,099.62	CAD010
612200	SF: Teachers: Employers national insurance	189,572.10	CAD010
612300	SF: Teachers: Employers pension contribution	291,189.84	CAD010
615100	SF: Teaching Assistants: Wages and salaries	150,279.51	CAD010
615200	SF: Teaching Assistants: Employers national insurance	9,970.56	CAD010
615300	SF: Teaching Assistants: Employers pension contribution	20,726.34	CAD010
		£2,415,837.97

Transformation steps:

#	Step	Value
1	Sum FMS CoA values to AR field level	£2,415,837.97
2	Round value to nearest 1000	£2,416
3	Calculate residual value
	i. Divide total FMS value by 1000	£2,415.84
	ii. Minus rounded value calculated in step 2	£2,416.00
	iii. Residual rounding to be assigned	(£0.16)
	iv. Assign to allocated rounding account	OEX010
4	No signage flip required	£2,416
	Result: CAD010 AR value	£2,416

Example for AR Field OTA030 (showing signage flip)

FMS data received:

CoA Account	CoA Description	Value (£)	AR Mapping
530400	IN: Trading Income: Sale of uniforms	(170,056.63)	OTA030
530500	IN: Trading Income: Income from clubs	(51,121.00)	OTA030
530600	IN: Trading Income: Income from music	0.00	OTA030
		(£221,177.63)

Transformation steps:

#	Step	Value
1	Sum FMS CoA values to AR field level	(£221,177.63)
2	Round value to nearest 1000	(£221)
3	Calculate residual value
	i. Divide total FMS value by 1000	(£221.18)
	ii. Minus rounded value calculated in step 2	(£221.00)
	iii. Residual rounding to be assigned	(£0.18)
	iv. Assign to allocated rounding account	INC010
4	Apply signage flip	£221
	Result: OTA030 AR value	£221

4.2 Data transformation – draft financial statements

The data transformation of the trusts FMS into the draft financial statements is a much simpler process.

• values are rounded into £’s
• there is no rounding reallocation

Positive and negative balances are displayed as per as appropriate for the trusts published financial statements.

5. After accepting the FMS submission - accounts return

This section includes:

5.1 Overview updates

The ‘overview’ section of the AR has four tables. All four tables must be completed to proceed with the rest of the AR form. You can accept the FMS submission but until you complete all four of the tables in the overview section, you will not be able to see your data.

Many of the AR tables, data entry rows/columns and fields displayed within the AR form are dependent on information provided in the four tables within the four ‘Overview’ tables. The FMS submission trusts send, will populate certain answers within these tables depending on the data sent.

5.2 Academy information – teaching schools

If an academy school within the trust has teaching school status, it is required to report income and expenditure relating to the teaching school separately.

If a trust has data at academy level in teaching school chart of account codes, this will be sent across in their FMS submission, within the academy trial balance dataset. When this data is accepted in the AR, the form will automatically select ‘Yes’ against the teaching school question (ACI050) for the relevant academy, in order to display additional fields relating to teaching schools within the ‘SoFA’ and ‘benchmarking’ sections.

If however, a trust has data against a teaching school chart of account code at trust level and not academy level, i.e. only within the trust trial balance dataset, then the form will not be able to automatically make this selection. A form user will be required to review the list of academies on the academy information page and select yes to the teaching school question (ACI050) for the relevant academies before the page can be marked as complete. If your trust only contains one academy, then ‘Yes’ will automatically be selected against the relevant teaching school question (ACI050).

All three of the above scenarios will lead to the status of the academy information page being re-set to in progress after accepting the FMS submission.

5.3 Academy information – transfers and conversions

The trust is required to confirm the status of each academy school within the trust on the academy information page. This can be done by selecting the correct status from the drop-down list which is situated below the ESFA recorded status.

If a trust sends an FMS submission containing data against in-period transfer-in, in-period transfer-out or newly converted member chart of account codes at academy level, i.e. within the academy trial balance dataset, then the form will automatically select the relevant status in the drop-down list for that academy (ACI040). This will display additional fields relating to this type of transfer or conversion within the AR form, as well as activating the ‘transfers and conversions’ section.

If however, there is data against in-period transfer-in, in-period transfer-out or newly converted member chart of account codes at trust level and not academy level, i.e. within the trust trial balance dataset, the form will not be able to automatically make this selection. A form user will be required to review the list of academies on the academy information page and update the transfer status in line with the data which has been submitted. For example, if the FMS submission contained data for in-period transfer-in and newly converted member, the form user would need to ensure both these statuses are selected in the drop down list (ACI040) for the relevant academies before the page can be marked as complete.

The above scenarios will lead to the status of the academy information page being re-set to in progress after accepting the FMS submission.

If the form user has already completed the academy information section and the data imported from the FMS submission does not contradict the information input, there will be no change to the page status.

ACTION FOR TRUSTS: Check academies have the correct status

5.4 Questionnaire

Most trusts will not have disclosures for every table in the return and completing this questionnaire tailors the return to the tables, assets and investment types that are relevant to them.

When an FMS submission is accepted, the system will check if there is any data related to the optional tables which can be made available through a questionnaire selection. If there is, this selection will be automatically chosen on the questionnaire, according to the data sent from the FMS, so that the table is made available and the user can see all values populated in the form. This will override any previous questionnaire responses, and the status of the questionnaire page will be changed from either not started or completed to in progress.

If the user has already completed the questionnaire and the data loaded through the FMS submission does not contradict the information input, then the status of this page will remain as completed.

If you have selected to make a table available in the questionnaire, and there is no data imported for this category, your answer on the questionnaire will not be changed.

Trusts can change the answers to questions at any time before the form is approved. Note that doing so would also remove any associated data in the form, including both data that has been imported through the FMS submission or manually entered.

Trusts may find that certain responses are pre-populated and in a read-only (greyed out) format, this is as a result of the closing balances disclosed in the trust’s 2018/19 AR. You will not be able to change the response to these questions.

ACTION FOR TRUSTS: Review and confirm questionnaire - updating where necessary

5.5 Calculated fields – opening balances

Opening balances are pre-populated fields using the data submitted as the closing balance in the previous year’s AR. The opening balance values are read-only and cannot be modified.

When an FMS submission is accepted, the pre-populated opening balances figures are deducted from the brought forward figure sent through the FMS and any remaining posted to the relevant adjustments made to opening balance line. As these fields are showing the result of a calculation rather than the imported FMS value, the field level tracking label will default to ‘Modified’ rather than ‘Automated’.

Below is a worked example showing the data transformation and opening balance calculation.

Example for AR field TFC020-A

FMS data received:

CoA Account	CoA Description	Value (£)
110100	FA: Freehold Land: Cost brought forward	400,000.00
115100	FA: Freehold Buildings: Cost brought forward	3,625,277.95
		£4,025,277.95

Transformation steps:

#	Step	Value
1	Sum CoA values to AR field level	£4,025,277.95
2	Round value to nearest 1000	£4,025
3	Calculate residual value
	i. Divide total FMS value by 1000	£4,025.28
	ii. Minus rounded value calculated in step 2	£4,025.00
	iii. Residual rounding to be assigned	£0.28
	iv. Assign to allocated rounding account	DEB110-A
4	No signage flip required	£4,025
	AR pre-populated opening balance in TFC010-A	£4,500
5	Calculate difference between AR and FMS opening balance	(£475)
	Result: TFC020 AR value	(£475)

5.6 Calculated fields – funds

Within the chart of accounts, the funds trial balance codes represent the total funds balance for the year. When this information is passed from the FMS to the DfE to be populated in the AR, a calculation is required. The AR already pre-populates the opening balance with the closing balance submitted in the previous year’s AR. As the funds balance contains the opening balance within its figure, the AR form deducts this opening balance from the funds balance and the remainder is added into the ‘Other gains, losses and transfers’ line in the funds table.

As the trial balance codes do not provide further detail, you will need to go into the form and manually modify the funds tables to provide the breakdown of the fund balance.

In addition, you will may also need to amend the following fields in the benchmarking section, as BOB030 (Benchmarking totals > Closing balance) is auto-populated with the value mapped from CoA account codes 420100 and 460100:

• BAB030 - Benchmarking academies > Closing balance (Restricted and Unrestricted Funds) for each applicable academy, and
• BTB030 - Benchmarking MAT central services > Closing balance (Restricted and Unrestricted Funds)

Using the benchmarking field guidance in the AR form, overwrite the automated value in line with the adjustments made to the ‘Funds’ tables for restricted and unrestricted funds,

Worked example for AR field RFF080-A

If the following data was submitted at chart of account level through an automated FMS submission:

CoA Account	CoA Description	Value (£)
430100	FD: Restricted Fixed Asset Fund: Fund balance	70,000.00
470100	FD: Revaluation Fund: Fund balance	30,000.00
		100,000.00

Transformation steps:

#	Step	Value
1	Sum CoA values to AR field level	£100,000.00
2	Round value to nearest 1000	£100
3	Calculate residual value
	i. Divide total FMS value by 1000	£100
	ii. Minus rounded value calculated in step 2	£100
	iii. Residual rounding to be assigned	£0
4	No signage flip required	£100
	AR pre-populated opening balance in RFF010-A:	£10
5	Calculate difference between AR opening balance and FMS closing balance	£90
	Result: RFF080-A AR value	£90

When it is first imported the table would look as illustrated in table 1, you would then manually allocate these as appropriate so the form may look more like table 2:

Worked example for AR field RGF080-A

FMS data received:

CoA Account	CoA Description	Value (£)
420100	FD: Restricted General Fund: Fund Balance	967,724.08

Transformation steps:

#	Step	Value
1	Sum CoA values to AR field level	£967,724.08
2	Round value to nearest 1000	£968
3	Calculate residual value
	i. Divide total FMS value by 1000	£967.72
	ii. Minus rounded value calculated in step 2	£968.00
	iii. Residual rounding to be assigned	(£0.28)
	iv. Assign to allocated rounding account	RGF080-E
4	No signage flip required	£968
	AR pre-populated opening balance in RGF010-A:	£1,000
5	Calculate difference between AR opening balance and FMS closing balance	(£32)
	Result: RGF080-A AR value	(£32)

When it is first imported the table would look as illustrated in table 3, you would then manually allocate these as appropriate so the form may look more like table 4:

Please also note that chart of account codes affecting gross transfer between funds will map into the gross transfer between funds line and affect the closing balance.

ACTION FOR TRUSTS: Update funds table pages

5.7 Manual allocation of COVID grant funding

There are no CoA codes available to post COVID-19 funding directly to, and as noted in the ‘Guide to using the academies chart of accounts’ the trust should allocate COVID related income to the following account codes (columns B,C & D), then manually reallocate the balances relating to COVID funding (columns A,E & F), as follows:

AR field description (A)	Initial FMS coding of COVID income in CoA – (B)	AR field mapped to via automation API – (C)	Bench-marking field mapped to via automation API – (D)	AR field to manually reallocate grant to – (E)	Benchmarking field to manually reallocate grant to (F)
Other DfE/ESFA COVID-19 funding	510950	RGR150	BAI010-A	RGR151	BAI061
Catch-up premium	510950	RGR150	BAI010-A	RGR152	BAI061
Recovery Premium	510950	RGR150	BAI010-A	RGR153	BAI061
Coronavirus Job Retention Scheme Grant	520300	ORG040	BTI060 / BAI060-A	ORG041	BTI061/BAI061
Other COVID-19 funding (Non DfE/ESFA)	520300	ORG040	BTI060 / BAI060-A	ORG042	BTI061/BAI061

Table configuration:

• columns B to D - shows CoA income allocation and mapping
• columns E & F - shows action for trusts to manually amend field value

ACTION FOR TRUSTS: - Manually re-allocate grant received for COVID-19 related expenditure as per table above.

5.8 Non-automated data

Data can only be automated if it is held within your FMS and therefore part of the trial balance. There are several non-trial balance chart of account codes, these all starting with 9. These will not be sent across from your FMS and so fields within the AR, which relate to non-trial balance data, will have to be completely manually.

There are several tables within the AR, which compromise of only non-trial balance information. These tables are listed below and where applicable, will need to be completed manually:

• Academy transfer In
• Academy transfer Out
• Agency arrangements
• Amortisation periods
• Average staff numbers (FTE)
• Capital commitments - contracted
• Capitalisation thresholds
• Contingent liabilities
• COVID revenue grants (see section ***)
• Depreciation periods
• Exit schemes
• Finance lease commitments
• Fixed asset financing
• Fund analysis
• Intangible fixed assets - asset financing
• Loan analysis
• Loss of office payments
• Losses & special payments
• Newly converted member
• Non-cancellable contracts
• Off payroll arrangements
• Operating lease commitments
• Pension breakdown
• Pension - employer contribution
• Pension life expectancy
• Pension major assumptions
• Pension plan assets
• Pension sensitivity
• Permanent employees - gender analysis
• PFI commitments
• Provisions (ageing)
• Related party transactions
• Staff costs
• Staff emoluments
• Staff sickness - days lost
• Trustee remuneration

Cells which must be completed manually are called non-automated cells. Sending data from your FMS will have no impact on these non-automated cells – i.e. where you have manually entered data into the non-automated cells and then need to import or re-import data from your FMS, these non-automated (manual) cells will not be overwritten.

ACTION FOR TRUSTS: Manually update non-trial balance cells

5.9 Checking and tracking your data

Once you have accepted your data upload into your AR, you will need to check it is correct. To easily identify which cells have been pre-populated from your finance system (rather than completed manually) these cells have been tagged with ‘Automated’ underneath them. If you manually change the data by overtyping in the field the tag will change to ‘Modified’ and the cell outline colour will change from grey to blue. If you decide the original pre-populated value was correct and you change it back, the tag will revert to ‘Automated’ and the cell outline colour will be grey once again.

Where fields are calculated using data provided from your FMS, such as opening balances or funds, if the calculation results in a different number to the one sent from your FMS, the field will be tagged with the ‘modified’ label.

This field level tracking functionality will be turned on as a default when you accept data into your form. You can turn it off/on using the display tracking button on the right-hand side of the index screen. Please note this button, as well as the field level tracking, is only visible after you have accepted an FMS submission, and can then only be seen by those with the accounts return internal preparer, external auditor preparer or accounts return internal approver roles.

5.10 Report showing mapping between FMS and AR

The FMS mapping report can be found on the right-hand side of the index screen once you have accepted a data upload. Please note, this report will not be visible before this point. This report shows which chart of accounts codes have been fed from your FMS into which AR fields. You will also be able to see the original values submitted from your FMS and how they have been transformed to meet the AR requirements per the data transformation steps.

5.11 Amending your data

If you need to make amendments to the pre-populated cells you can either:

• change the data manually by deleting the automated figure and typing in your own. Note that this will change the field level tracking tag for those cells from ‘Automated’ to ‘Modified’.
• make the adjustments in your finance systems and upload a new set of trial balance data. Note this will overwrite all automated cells including those you may have previously changed manually - n.b. Manual cells (those that can’t be automated) will be unaffected.

Please note that for single academy trusts, where possible, the form will use data submitted in the SoFA to auto-populate cells in the benchmarking section. This will be read-only data and you will not be able to amend these fields.

5.12 Cut off for data uploads

Uploads from your finance system will only be possible up to the point the form is sent through the approval cycle. After this no more uploads will be accepted, even if the form is sent back to preparer stage.

All changes will thereafter need to be made manually.

5.13 Accounts return review, approval, and submission

Once you have completed your return, checked your data and confirmed this by clicking mark as complete on each page and cleared any validations, please follow the normal approval process, submitting the accounts return to your trust and auditor approvers, more information on this can be found here.

ACTION FOR TRUSTS: Check your data and confirm using the mark as complete button on each page and send for approval.

6. After accepting the FMS submission - draft financial statements

This section includes:

6.1 Data upload and acceptance processes

Trusts use the same process of uploading and accepting data into the AR form and should refer to guidance in section 3 ‘How automation works’.

Please note: the differences between this guidance, for draft financial statements, and the AR guidance are:

• the AR form for will be open from 12th September 2023 to upload and accept data into the for the financial statements report and the AR form until 30th January 2024.

• the overview section of the AR form does not need to be completed to use the financial statements report

6.2 Data transformation

Trusts can generate their draft financial statements by clicking on the ‘Draft Trust Financial Statements’ link, which is located in the right hand menu of the index page of the AR and is highlighted with a red box in the image below. When clicked, this link displays options for the user to download the data into an Excel or PDF file format.

6.3 Draft trust financial statements workbook

{#draft-trust-financial-statements-workbook}

The workbook uses the trusts automated data to produce draft financial statements in the Coketown format. An ‘Instructions’ tab has been included at the beginning of the workbook to aid with understanding the workbook.

6.3.1 Workbook data population

The workbook can be updated with new data by uploading in a new version of the trusts FMS upload file to the AR form. It will populate this template and be ready to download. There is no need to accept the data into the AR form first.

Each new automated data submission generates a new ‘Draft trust financial statements’ workbook. Older versions will not be retained. If you wish to keep older versions these will need to be downloaded and stored locally.

6.3.2 Workbook navigation

The ‘Note names’ tab enables easier navigation and the user can move around the workbook in the following ways:

• each note is hyperlinked, and the user can navigate to them easily from the ‘Notes name’ tab

• alternatively, the user can right click on the arrows found in the bottom left hand corner of the workbook and select the appropriate tab from the list

• links are also available to the notes from the relevant references on the primary statements

• the user can also navigate back to the ‘Notes name’ tab from any of the notes by clicking on the note name in the top left cell (cell A1)

• the user can navigate to the relevant tabs directly from the B/S, SoFA, and CF tabs by clicking on the note number, found in Column C ‘B/S’ tab, or column B (CF and SoFA tab)

The image below highlights the note numbers that can be clicked on to navigate to other tabs within the document.

6.3.3 The ‘Note names’ tab

The ‘Note names’ tab can be used to alter the note numbering dynamically by deleting any number. An example is shown below where you may wish to delete Note 7 (Expenditure).

6.3.4 The ‘Mapping’ tab

The ‘Mapping’ tab shows the downloaded CoA data from the trusts FMS data upload. This shows how the account codes map to the draft trust financial statements workbook and the AR.

Selecting the ‘+’ above the stated columns unhides the relevant sections for you to view.

All cells can be overwritten, but many are formula driven. If you change them, the formulas may no longer work. Where data is not available from the automated feed, you can manually type information in. We suggest adding/subtracting from existing formulas rather than overwriting where this is necessary. If you find there are errors in the mapping, please feed this back via API.Finance@education.gov.uk so we can improve the workbook.

Please note the following with regards to account codes:

• if your trust has added its own account codes, these will be rolled up to the DfE’s account code

• if an account code is being used which is currently not in our chart of accounts, for example, the trust has used a code added to the following version of the CoA, then it must be added manually

• if there are any cases where an account code is missing from the current collection periods CoA, please feed this back to us via API.Finance@education.gov.uk

6.3.5 Prior year data

The prior year tab is currently unavailable, but the data can be added manually.

6.4 6.3.6 Workbook data types

The workbook will need to be reviewed and updated with any changes needed for pre-populated data and also for non-trial balance data so that the trust has a full data set for the draft financial statements.

The table below describes the action required in different tabs of the workbook to provide the trust with a full set of financial statements data.

Tab colour	Trial balance type	Action
Green	Trial balance driven	All fields in the tab will be completed by automated feed (data dependant) and manual input is only required if the user wishes to update the pre-populated data
Amber	Partially trial balance driven	Some fields in the tab will require manual input
Red	Non-trial balance information	All fields in the tab will require manual input

7. Further help and information

This section includes:

7.1 Checklist for trusts using automation

(#checklist-for-trusts-using-automation)

This checklist is provided as a pointer of things you may want to check if you are submitting your financial information via automation.

Before you start:

• ensure you set up a ‘Data Transfer Approver’ on IDAMS otherwise you will not be able to send data from your FMS to the AR

Once data has been accepted into the AR form:

• Overview - check each academy has the correct status for teaching schools and transfers on the academy information page

• Overview - review and confirm questionnaire responses

• SoFA > Revenue grants – manually re-allocate funds from RGR150 and ORG040 as directed in section 4.7 ‘Manual allocation of COVID grant funding’ [LINK]

• Balance sheet funds and other disclosures - update funds table pages

• Non trial balance fields - manually update non-trial balance fields throughout the form

Check all your data and confirm using the mark as complete button on each page before sending for approval.

7.2 Additional sources of help and information

This guidance is complementary to other information available to help trusts complete the AR. Additional sources of help and information are:

Resources and guidance available	Description
AR online form guidance	This is the primary source of guidance with many tables displaying guidance text at the top of the page and most cells providing a ‘Help’ link below the row title to provide specific guidance. The in-form guidance for the 2019/20 return has been substantially improved to help trusts navigate the form.
AR web page	A collection of help tools and guidance including on-line tutorial videos, excel workbook, academy accounts return 2019/20: updates and additional information requirements, additional pensions information and help text and validation summary, are available on the the AR web page.
Chart of accounts	In May 2021, the DfE published an update to the standardised chart of accounts (CoA). Information can be found on the ‘Academies chart of accounts and automating the accounts return’ web page which provides a spreadsheet showing detailed mapping of income and expenditure into the AR form categories. Within the document, the tab named ‘CoA structure and mappings’ may provide support to complete the AR as the descriptions broadly align to fit the summarised ‘code’ descriptions in column F, which map into tables of the AR in columns G to T.
Academies Account Direction (AAD)	Published by the DfE to aid in the completion of the trust’s financial statements.
Help with specific queries	Any questions not addressed in the above tools and guidance should be addressed to the enquiry service please add a reference ‘automation’ and provide an explanation of the issue and an accompanying screen shot where possible.
Help with incorrect trust pre-populated data	Please e-mail academies.SD@education.gov.uk including the trust UPIN and details of what should be changed.
“Having trouble with this service?”	If you are experiencing technical difficulties with the AR on-line form, this link is available on the right hand menu of the index page for trusts to report the problem. If the DfE are already aware of an issue, it will be posted under “Known issues”, along with advice on how to proceed. If your issue is not listed here please click “Send an enquiry” to report your problem.
Help forums	Once the AR form has launched, dial in (by phone or MS Teams) and web chat sessions will be available for users of the AR form to ask questions or raise issues directly with a member of the AR team. A full schedule is published on the AR webpage.

*[DfE] Department for Education