1617 Local Govt Training - Walkthrough (Text Heavy).ppt
Whole of Government Accounts: 2016/17 DCT Training for Local Government Structure of the training materials Section 1: Introduction to WGA Section 2: Timeframes for WGA Section 3: Data Collection Tool Walkthrough Section 4: Additional Tools Walkthrough Section 5: Whats New for 2016/17 Section 6: Help, Guidance and Troubleshooting . Section 1 Introduction to WGA .
What is WGA? Set of accounts for the whole UK public sector (over 6,000 bodies) Largest of any such consolidation in the world Commercial style accounts based on IFRS and presented in line with central governments financial reporting manual (the FReM) Model set of accounts for the simplifying accounts project Subject to audit, based on actuals (not models/estimates) Provides trend data . Stages of WGA Preparation Stage 1 Bodies produce accounts data
Stage 2 Data Collection Tools (DCTs) are Completed By Stage 3 HMT perform WGA Consolidation Final Product 22 Departments (consolidating 300 bodies in total) Public Sector Bodies within WGA boundary 600 Local Authorities 1 Academies Pack
(consolidating 5000 bodies in total) 60 Public Corporations . Consolidation & Elimination Of Intra Government Balances Whole of Government Accounts
Local Government in WGA 2014/15 Figures . Section 2 Timeframes for WGA . 2016/17 WGA Deadlines Cycle 1 Unaudited Local Government Return 30th June 2017 Cycle 2 Audited Local Government Return 29th September 2017 Please flag up any anticipated delays to the WGA team as soon as possible .
2016/17 WGA Timeline Prior to April After Sept Local Authorities: Submit designation order & CPID changes to DCLG WGA Team: may be in touch to resolve queries Apr
WGA Team contact preparers to resolve any issues WGA Team publish DCT Tool & Guidance May Local Authorities: Agree CPID transactions with each other Jun
Jul Local Authorities: Complete and submit unaudited DCT Return Aug Sep Local Authorities: Complete and submit
audited DCT Return . Section 3 DCT Walkthrough A tab by tab walkthrough of the Data Collection Tool (DCT) spreadsheet, addressing key points Further guidance notes are published alongside the DCT on our gov.uk pages (link in Section 6) . Structure of the DCT Walkthrough
Glossary Cycle 1 Unaudited Return a) Getting ready to use the DCT spreadsheet b) Inputting accounts data: tab by tab walkthrough c) Validating your data d) Inputting details of your transactions with other WGA entities (CPID Transactions) e) Essential checks before you lock your pack f) CPID Validation & locking and submitting your DCT
Cycle 2 Audited Return . Glossary of Key Terms OSCAR: Online System for Central Accounting and Reporting The financial system we use to upload your DCTs DCT: Data Collection Tool The spreadsheet you use to report and submit your statement of accounts SCOA: Standard Chart of Accounts List of 8 digit account codes used to classify transactions CPID: Counterparty Identifiers
A unique ID code for every WGA body MR: Match Relationships Used in WGA to identify and group types of transactions. For example: MR 10 groups general payables and receivables together . Cycle 1 Unaudited Return . a) Getting ready to use the DCT Spreadsheet You must enable certain add ins and macros before downloading and opening the file. Start by opening Excel File - Options - Customise Ribbon tick Developer. .
a) Getting ready to use the DCT Spreadsheet Excel 2003 The developer ribbon is divided between two toolbars: Control Toolbox and Forms. To display the toolbars: Press View on the main toolbar Select Toolbars Check the toolbars Control Toolbox & Forms Excel 2007 Click Office Button Click Excel Options at the bottom Ensure Popular tab on left menu is selected Check the option Show developer tab in the ribbon .
a) Getting ready to use the DCT Spreadsheet Click on the Developer tab in the top Excel ribbon and click Add Ins Excel 2003 Click on Tools Click on Options Click Other Click Advanced Options Select Add Ins
Manager Excel 2007 Click Office Button Click Excel Options at the bottom Click Add Ins on the left Select Excel Add ins in the bottom drop down menu (shown below) and click Go to reach the Add Ins menu .
Tick these settings a) Getting ready to use the DCT Spreadsheet For 2007 and later Under References, tick all these libraries options Go back to the Developer ribbon: then click Visual Basic Tools References Use the newest options available on your system. If any are marked as missing, deselect them and select the equivalent in your version of Excel. Excel 2003 To access Visual Basic, select Tools
> Macro > Visual Basic Editor . a) Getting ready to use the DCT Spreadsheet Exit the VBA window to the main Excel screen. Go to File Options Trust Center Trust Center Settings Macro Settings/Macro Security Settings. Excel 2003 Click Tools then Options Click Security Click Macro Security Excel 2007
You must tick Trust access to the VBA project object model. Click the Office button Click Excel Options Select Trust Centre Click Trust Centre settings Click Macro Settings . a) Getting ready to use the DCT Spreadsheet
Now download, save and open the DCT spreadsheet Please visit our pages on gov.uk to download the DCT tool, additional tools and further guidance. If the DCT tool is updated, the new version will be uploaded here. https://www.gov.uk/government/collections/whole-of-government-accounts Save the file to your local directory as an xls file (do not use xlsm). We recommend you save versions of the file as you go along using save as. . a) Getting ready to use the DCT Spreadsheet If one of the below troubleshooting messages appears on opening the file... .please re-check your macro and VBA settings . a) Getting ready to use the DCT Spreadsheet
Once you have the file open Enable editing and enable contents Click on the Instructions tab Click into the CPID selection box cell, then type your CPID code or select the CPID code for your authority using the dropdown arrow (the CPID is a unique identifier code for each WGA entity) Select Proforma Method Save as to desired location Close the file down Open it and it is ready for use. The file will be populated with local government relevant sheets only. . a) Getting ready to use the DCT Spreadsheet The below sheets will now be visible in the file = only becomes visible once the
other tabs have been completed = not visible in all UK regions. CollFund & Transferred Debt England only. Hways Infr not NI. . a) Getting ready to use the DCT Spreadsheet Check Updates: Please read the DCT Updates tab for important information on changes since last years tool . a) Getting ready to use the DCT Spreadsheet Check Opening Balances: Access the LP-Balance Sheet tab and check that your opening balances have been brought
through correctly (section marked 1). If you have restated your prior year figures, enter restatements here (columns in section marked 2). Restated figures show in section marked 3. 3 . 2 1 a) Getting ready to use the DCT Spreadsheet Check Opening Balances (continued) If the figures do not balance, ensure you only have one DCT copy open. Contact us if you are still getting a Does Not Balance warning at the bottom of the sheet. Examples of does not balance warnings at foot of tab
. a) Getting ready to use the DCT Spreadsheet Prior Year Comprehensive Income & Expenditure Restatements : Access the LP-Restatement CI&E tab. Restatements for prior year CI&E should be recorded here only if you have prior agreement with the WGA team to do so please contact us if unsure. Restatements must be in line with your final accounts. . b) Inputting accounts data Getting Started The DCT has been designed to, where possible, resemble your statement of accounts Most sheets will be self explanatory and you can pull many figures straight from your accounts. If you are unsure where to put figures, our guidance
notes and your CIPFA code of practice should help. Some figures may need a bit of work The best approach to completing the DCT is to work through the tabs from left to right starting from LP - Liabilities & Provs. This is because some of the sheets feed into the others. . b) Inputting accounts data Adjustments for restatements must be entered again and do not pull through automatically, as data requirement here is more granular. Sheet: LP Liabilities & Provs Note: some rows are only applicable for certain UK regions (green bracketed text)
. b) Inputting accounts data Sheet: LP - Fin Inst . b) Inputting accounts data Sheet: LP Non Curr Assets Add Info This sheet is used to identify material assets not consolidated into WGA for instance, some voluntary aided schools. .
b) Inputting accounts data Sheet: LP PP&E & Invest Prop Sheet headers and first section We require the balances for Land and Buildings to be separately reported New section for ELG Schools converting to academy trusts . Please ensure financing section at foot of tab is completed
b) Inputting accounts data Sheet: LP - Intangibles Heritage assets have been designed so that they cannot be depreciated. If you need to depreciate an intangible heritage asset, this will need to be entered in the other movement line. Sheet headers and first section . b) Inputting accounts data Sheet: LP Invs, JVs & Assoc Sheet headers and first section . b) Inputting accounts data Sheet: LP Current Assets & AHFS
. b) Inputting accounts data Sheet: LP CollFund English Local Government only How you complete the information depends on whether you are the preceptor or the billing authority Sheet headers and start of council tax section You would need to split your collection fund account as per the different organisations that form part of your collection fund statement. The sheet has sections for council tax and business rates.
. b) Inputting accounts data The sum of the first 6 columns of data must equal zero Sheet: LP CollFund (continued) Business rates section Final sections . b) Inputting accounts data Sheet: LP IAS 19 Pensions
Sheet headers and start of scheme liabilities section New analysis table per scheme. The relevant data should be in your actuarial report. If not, please note in comment box and arrange provision of the data from 17/18. . b) Inputting accounts data Collected as one whole (difference to your
statement of accounts presentation) Sheet: LP I&E NCS Subjective Analysis Employees section Other/Miscellanous Expenditure section Please only use as a last resort! . b) Inputting accounts data Sheet: LP I&E NCS Subjective Analysis (continued) Part of Income section .
b) Inputting accounts data Sheet: LP CI&E Taxation and Non Specific Grant Income section If a specific grant stream is not listed, data can be provided in the other grant cells . b) Inputting accounts data Sheet: LP Reserves Now also used as Welsh Schools Reserve Excerpt from reserves columns Available to use if you cannot locate a column for a particular usable reserve .
b) Inputting accounts data Sheet: LP Additional Data This tab looks at finance and operational leases, and PFI contracts. A mix of financial and non financial data. First section Staff numbers section . b) Inputting accounts data Sheet: LP Add Info Hways Infr Will not be visible to Northern Irish Local Government
Part of DRC disclosure section This tab captures the progress being made on revaluing Highways Infrastructure Assets and data will be shared with CIPFA. . b) Inputting accounts data Sheet: LP Add Info Transferred Debt English Local Government only Sample section for one of the affected groups of authorities Few local authorities will have this type of debt, but if you are holding transferred debt as a liability on your balance sheet, please complete this tab.
. c) Validating your data Sheet: LP Validations Hard validation fail Needs correction Soft validation fail Needs correction/explanation Validation pass .
c) Validating your data Box will change colour to reflect outcome once macro has been run Sheet: Instructions Insert explanation for any soft validation fails here (copy from LP Validations sheet) Sheet: Validation Errors . d) Inputting details of your transactions with other WGA entities (CPID Transactions)
Box will change colour to reflect outcome once macro has been run Sheet: Instructions Sheet: CPID Transactions example of entered data Transaction Account Code Trial Balance Account Balance Instigating Organisation (You) Recipient Organisation
Amount of Transaction with each Recipient Organisation . d) Inputting details of your transactions with other WGA entities (CPID Transactions) and click this button 2 to add more rows Sheet: CPID Transactions Here, 5 rows were needed to split up the trial balance amount
1 Place cursor into relevant account code cell . d) Inputting details of your transactions with other WGA entities (CPID Transactions) Select Delete in the drop down box in the Update Action column Sheet: CPID Transactions 2
then press Delete Selected Rows to delete extra rows added in error . 1 d) Inputting details of your transactions with other WGA entities (CPID Transactions) Sheet: CPID List Sheet: CPID Transactions Transaction Account Code
Trial Balance Account Balance Instigating Organisation (You) Recipient Organisation Amount of Transaction with each Recipient Organisation . d) Inputting details of your transactions with other WGA entities (CPID Transactions) Sheet: CPID Transactions Example: Trial balance allocation of an internal SCOA
Full allocation of trial balance External SCOAs: These are for transactions with non WGA entities, so there should not be any counterparty transactions for these Partial allocation of trial balance Example: Trial balance allocation of a mixed SCOA . d) Inputting details of your transactions with other WGA entities (CPID Transactions) Sheet: CPID Transactions
Double click into the amount cell to be taken to the place data is entered in the DCT . e) Essential checks before you lock your pack Do not use decimal points Enter figures in 000 (with some exceptions)
Input in white/blue cells only Make use of the comments fields if needed Save as xls file type (Excel 97-2003) Complete your own and your auditor contact details on the Instructions tab
Complete the management review checklist and have this signed by the responsible senior officer who has authorised you to proceed (more information in Section 4) . f) CPID Validation & locking and submitting your DCT Sheet: Instructions Remember to tick the relevant audit threshold box Box will change colour to reflect outcome once macro has been run Option box which will appear if all CPID validation checks have been passed .
f) CPID Validation & locking and submitting your DCT Sheet: CPID Transactions Example of CPID transactions passing the validation rules Example 1 of CPID transaction failing the validation rules Example 1 of CPID transaction validation failure being resolved Negative signage was in error and has been corrected . Status remains red will change to green when macro re-run
No explanation given in this case as reason for fail has been corrected f) CPID Validation & locking and submitting your DCT Sheet: CPID Transactions Example 2 of CPID transaction failing the validation rules Signage error and over allocation of the trial balance total Example 2 of CPID transaction validation failure being resolved Figure has been amended to 400 but validation rules will still detect a
signage error . A full explanation has been provided for why the signage is not as expected f) CPID Validation & locking and submitting your DCT Sheet: CPID Transactions Trial balance amount has been under allocated. Internal SCOAs must be fully allocated. Example 3 of CPID transaction failing the validation rules This SCOA must be 100% pointed to
a particular entity Example 3 of CPID transaction validation failure being resolved Omission of instigating and counterparty data was in error and has been corrected . Status remains red will change to green when macro re-run No explanation given in this case as reason for fail has been
corrected f) CPID Validation & locking and submitting your DCT Option box which will appear if all CPID validation checks have been passed Successfully locked pack The box has turned green and the message on the right hand side shows the data and time of locking . f) CPID Validation & locking and submitting your DCT
File name format should be: Cycle1_[CPID no.]_[Name of LA]_[date]_Locked . Cycle 2 Audited Return Entities above audit threshold Auditors audit Cycle 1 Pack Your Cycle 2 Pack will be Cycle 1 data with audit adjustments and corrected mismatches. It must match your final audited accounts Cycle 2 Pack must be locked by both yourselves and your auditors Entities below audit threshold Your Cycle 2 Pack will be Cycle 1 data updated to correct mismatches Cycle 2 Pack must be locked by
yourselves and then submitted (auditor locking left blank) . Section 4 Additional Tools Walkthrough . Management Review Checklist This checklist must be completed by the DCT preparer before locking and submission of Cycles 1 and 2 DCT packs
It must be signed by the responsible senior officer who gives authorisation to proceed It provides assurance to your managers, HMT and auditors that proper procedures are in place for providing WGA data . Mismatches Report A spreadsheet sent to you by the WGA Team after cycle 1
It lists transactions you have with other WGA entities, where your data is not in agreement with theirs Mismatches between WGA entities can occur for a number of reasons, including transactions being reported as different amounts, or account codes in different match relationships being used These mismatches then need to be resolved . Mismatches Report Match Relationship
INC - CURRENT GRANTS FROM CENTRAL GOVERNMENT E5104X INC - CURRENT GRANTS RECEIPTS IN ADVANCE E5104X INC - CAPITAL GRANTS FROM CENTRAL GOVERNMENT E5104X INC - SALES OF OTHER GOODS AND SERVICES E5104X EXP - GOODS/SERVICES - OTHER DFT004 EXP - CAPITAL GRANTS TO CENTRAL GOVERNMENT DFT004 EXP - CAPITAL GRANTS TO LOCAL GOVERNMENT DFT004 EXP - LEVIES & LOCAL PRECEPTS (NOT IN SERV LINES) E5100X INC - GENERAL GLA (CURRENT) GRANT (LG) E7060X NCA - OTHER LOANS
PWL888 CA - LOANS PWL888 NCL - BANK AND OTHER BORROWINGS E5045X NCA - OTHER RECEIVABLES E5100X CA - OTHER RECEIVABLES (CA) E5100X NCL - BANK AND OTHER BORROWINGS E5106X INC - Curr GRANTS FROM CG - REVENUE SUPPORTE2221X GRANT INC- CURRENT GRANTS FROM CG- NDR TOP UP PAYMENTS E2221X EXP - CURRENT GRANTS TO LG - REVENUE SUPPORT COM085
GRANT Recipient Organisation Instigating Organisation Org name Transport for London Transport for London Transport for London Transport for London Transport for London Department for Transport Department for Transport 1 Department for Transport Greater London Authority
Mayor's Office for Policing and Crime and Met P Co Public Works Loans Board Public Works Loans Board Newham London Borough Council Greater London Authority Greater London Authority London Legacy Development Corporation Kent County Council 2 Kent County Council CLG Communities Mismatch Example 1 -Account codes in different MRs -Not pointing to each other CPID
COM085 E2221X Mismatch CPID name Value in DCT Mismatch (variance) Department for Transport (24,000) (3,030,002) Department for Transport (1,034,948) (3,030,002) Department for Transport 0 (3,030,002) Department for Transport
(2,015,388) (3,030,002) Department for Transport (1,887) (3,030,002) Transport for London 40,845 (3,030,002) Transport for London 31 (3,030,002) Transport for London 5,345 (3,030,002) Mayor's Office for Policing and Crime and Met P Co 564,166 564,166 CLG Communities
(564,166) (564,166) Newham London Borough Council 0 208,655 Newham London Borough Council 0 208,655 Public Works Loans Board 208,655 208,655 London Legacy Development Corporation 170,626 171,032 London Legacy Development Corporation 406 171,032
Greater London Authority (170,626) (170,626) CLG Communities (213,092) (120,633) CLG Communities (120,633) (120,633) Kent County Council 213,092 (120,633) Mismatch Example 2 -Difference in aggregate amount of reported transaction .
Bridging Tool An optional supplementary spreadsheet It allows you to start work on listing out transactions with other WGA entities before you have reached the stage where the CPID Transactions tab becomes visible in your main DCT spreadsheet . Bridging Tool
Open the Bridging Tool spreadsheet, click on Data and then Edit Links Click on Change Source, find the location where you have saved your DCT and select the file. Then click on Update Values to bring through the data entered into your DCT. . Bridging Tool The SCOAs tab on the bridging tool lists all the WGA account codes in use for 16-17 Excerpt from SCOAs tab . Bridging Tool
Identifies if SCOA is mixed (amber), external (red) or internal (green) Excerpt from Summary tab Once linked with your DCT, this tab will list everything done so far within it . Bridging Tool Excerpt from Your Data tab The tab where you can begin to list out counterparty transactions for any account codes identified on the Summary tab as internal or mixed
. Bridging Tool Excerpt from CPID tab . Pro forma Tool A non macro version of the Data Collection Tool This can be helpful in dividing work amongst your team
It can also be used as a working paper Its use is optional This must not be submitted instead of the macro DCT . Section 5 Whats New for 2016-17 1 simplified pack for all UK local authorities
Some new sections Counterparty ID changes Improved usability Error correction . Section 5 Whats New for 2016-17 Example of rows only applicable to local authorities in some regions of the UK . Section 5 Whats New for 2016-17 1 simplified pack for all UK local authorities New checkboxes on Instructions tab Audit threshold at Cycle 1
Documents being returned at Cycle 2 Some new sections Counterparty ID changes New section on schools converting to academy trusts (English Local Government only) Improved usability New requirements for pensions and purchases of goods and services (next slide) Error correction .
Section 5 Whats New for 2016-17 Pensions: New analysis table on IAS 19 Pensions tab Purchase of goods and services: new analysis table on I&E NCS Subjective Analysis tab . Video Section 5 Whats New for 2016-17 1 simplified pack for all UK local authorities Some new sections Key changes
Counterparty ID changes Use CAD022 for any transactions with academies Scottish Further Education institutions now have individual CPIDs, rather than being grouped together Improved usability Error correction . Video Section 5 Whats New for 2016-17 1 simplified pack for all UK local authorities
Some new sections Streamlining of background coding & mapping Counterparty ID changes Removal of redundant rows and columns Improved usability Instructions on using delete rows functionality added to CPID transactions tab Error correction Collection Fund tab updated to automatically pick up the entity at the top
. Video Section 5 Whats New for 2016-17 1 simplified pack for all UK local authorities Some new sections Correction of existing errors Formula issues dealt with Mapping for Scottish JVs corrected Counterparty ID changes Avoiding errors being made All cells now locked when the pack is locked
Certain account codes restricted to certain CPIDs to ensure accurate allocation Improved usability Error correction . Section 6 Help, Guidance and Troubleshooting . Important Reminders Part 1 Read WGA DCT Guidance
Follow the recommended order of completion Enable macros Input in 000s in white cells only - grey cells are auto-populated or not applicable Whole numbers only (except for % for pensions & financial instruments)
Debits are entered as positive and credits are entered as negative Additional Information tab: All data is input as positive i.e. leases, other financial commitments, capital commitment . Important Reminders Part 2 Lease information: Lessee not Lessor
Identify and report counter party transactions Agree balances and transactions where possible Obtain copies of grant determination letters to support counterparty breakdown for audit Include WGA deadlines in your work plans
Agree deadlines with auditors Management review checklist Remember government grant SCOAs should be fully allocated . Web Link HM Treasury WGA website: https://www.gov.uk/government/collections/whole-of-governme
nt-accounts Visit the website to download the DCT, guidance, and all supplementary documents . Webinar Registration Register for Webinar to be held at 11am on June 6th https ://attendee.gotowebinar.com/register/15561534823728499 21 . Email Contacts
General Queries [email protected] Local Government Queries about the DCT returns [email protected] For other contact details, please check the newsletter (e.g. for local authorities and central departments you may need to agree transaction balances with) .
HEAT WAVE HITS EAST CHINA CITIES China Daily - 2004-07-27 The city of Shanghai is expected to remain on red alert as the temperatures are expected to remain above 35 C until the end of this month. ... Xcel Energy,...
Reasons: Children Belief that divorce would be greater misery Moral commitment to marriage Chapter 7 Diversity in Marriage Relationship Motivations for and Functions of Marriage Marriage as a Commitment Marriage as a Rite of Passage Changes after marriage Racial/Cultural Marital...
Sexual exploitation and abuse in humanitarian crisis is not confined to West Africa, but is a global problem. The task force has produced an action plan to prevent and respond to sexual abuse and has developed 6 core principles governing...
Four Elements of Style: Diction, Syntax, Tone, Point of View Tone: Details Facts that are included or those that are omitted. Consider: What do the details the author chooses to include imply? What does the author choose to exclude? What...
Migration et expositions aux violences. Les différentesétapes du processus migratoire exposent les migrants, et en particulier ceux qui migrent de façonillégale ou dans des conditions précaires, à de multiples incidents, dangers et risques pouvant leur provoquer des traumatismes psychologiques et...
The more parallelism, the faster speed. As long as the FPGA chip space is adequate, we can parallel more pipelines to speedup the design. In current FPGA chip, it is possible to use 6 or 12 pipelines to double or...
Ready to download the document? Go ahead and hit continue!