Manual for Asset Upload System

MANUAL FOR ASSET UPLOAD SYSTEM

Ministry of Education

September 2010

1

TABLE OF CONTENTS

SUMMARY OF CHANGES TO THE 2009-10 REPORTING

INTRODUCTION

FILE FORMAT

UPLOADING THE PIPE DELIMITED FILE INTO the AUS

ERRORS UPLOADING PIPE DELIMITED FILE

APPENDIX 1

APPENDIX 2 – RELATIONSHIP BETWEEN EXCEL/PIPE DELIMITED FILE AND SCHEDULE 3C

APPENDIX 3 – LIST OF ERRORS AND REQUIRED ACTIONS

1

SUMMARY OF CHANGES TO THE 2009-10 REPORTING

Changes to the 2009-10 reporting include:

1).Data Format of the Asset Serial Number changed from 6 digits numerical to a maximum of 12 digits numerical.

2). A new column has been added in schedule 3C for boards to transfer assets between asset classes. Column 7 asset type should reflect the asset class after the transfer.

3). A new column has been added in schedule 3C to cross check the data integrity reported in data upload file and EFIS form. Opening balance in EFIS form will be preloaded from 0809 financial statement ending balance. Transfer between asset classes will be entered by the boards. Other in year activities including adjustments to opening balance, additions/amortization, and write down, disposals will be pre-populated from data upload file. Boards need to ensure that the calculated ending balance in schedule 3C agree with calculated ending balance in data upload file. Boards are required to to clear all error messageson schedule 3C before submitting the financial statements.

INTRODUCTION

Asset Upload System (AUS)allows boards to input capital asset activity from September 1 – August 31 on an asset by asset basis. Boards are required to upload this file during the financial statement cycle of September 1, 2009to August 31, 2010.

The information uploaded in the AUS will automatically populate Schedule 3C. A diagram of the process can be found below:

This manual provides details surrounding the AUS. If you have any unanswered questions after reviewing this guide, please contact Soundari Vigneshwaran at (416) 326-9168 or .

FILE FORMAT

In order to upload a file using the AUS successfully the file must adhere to a specified layout and format. The chart below provides details of the required fields and formats.

The Field Name column represents the information that must be included in the file.

The Data Format column is the format that the information must be presented in. Definitions of the various formats are as follows:

Integer (x, y) means that the data shown in this field must be a number.

The maximum length of the integer is x and can contain a maximum of y decimal places.

Varchar (x) means that the data in this field can be numbers, letters, or

symbols such as dashes. The maximum number of characters is x.

Char (x) means that the data in this field can only be letters. For fields

that are bound by these parameters, the letters that should be provided are

specified by x. For instance, in the cycle field you have a choice of including the following EST for estimates, REV for revised estimates, and FIS for financial statements.

For each of the fields, an example is provided to illustrate the type of information that is acceptable.

Finally, the Attribute Name field represents how this field will be described in the AUS.

Boards are required to upload this information into the system using a, “Pipe Delimited” file format. Essentially, what this file format does is separate each of the required fields using a pipe. An excel document containing this information can easily be changed to a pipe delimited file. The Ministry has provided boards with a tool the will change files from excel to the required pipe delimited format at the following link: Please note that it is the board’s responsibility to convert the file to the required format. This tool has only been provided to help boards and may not be compatible with all versions of excel. Instructions for using the “File Generator” can be found on the tab entitled instructions. A sample excel and pipe delimited file can be found in Appendix 1 of the manual.

UPLOADING THE PIPE DELIMITED FILE INTO theAUS

1. Log into EFIS

1. Click on the link to the Asset Upload System

1. Select the Year and Cycle that you are reporting for.

Click Browse to select appropriate file by double clicking. Finish by clicking Upload Data File

1. Confirm that the values uploaded in the AUS and Schedule 3C agree to the values reported in the financial statements. Appendix 2 has been provided to show the relationship between the Excel/Pipe Delimited File and Schedule 3C. Essentially, the AUS summarizes the values based on asset type and then populates Schedule 3C. If the balances do not agree to the amounts reported in the financial statements, boards should check their file and correct any errors. After the errors have been corrected boards can re-upload information into the AUS. Re-uploading will over write the information that was previously loaded. The re-uploading process can only be done until the board makes their EFIS submission active, at which point, the numbers become final. If you would like to change the information after this point, you will have to contact Mark Bonham at (416) 325-8571 or .

ERRORS UPLOADING PIPE DELIMITED FILE

The Ministry has created some error messages in order to ensure that the data being uploaded is accurate and valid.

A list of the errors and required actions can be found in Appendix 3.

The error message will appear as follows and the file will not be uploaded until corrected.

To avoid errors to the file, ensure that the following rules are followed:

1. Ensure that all of the required fields are completed and are shown in the same order as indicated in the sample excel file in the Appendix 1. If any of the required fields are skipped or the order is not consistent, the file will be rejected. For instance, if the board did not dispose of the asset do not leave the field blank, instead input a zero.

1. Ensure that the serial number for each asset is unique and not

duplicated. If the same serial number is used for two assets, the file will be rejected.

.

1. Ensure the opening balances reported on the Asset Upload file agrees to the ending balances included in the data upload file of 2008-09 financial statement. These balances are pre-populated and carried forward as opening balances in Schedule 3C with the ending balances on the 20008-09 financial statements. Any adjustments to the opening balances should be made in the “Adjustments” column.

1. Please ensure that you do not include commas in your file as using them will cause an error in the file upload.

1. Write downs to assets will be shown as an adjustment to the

accumulated amortization or accelerated amortization in order to maintain the historical cost of the asset.

APPENDIX 1

Sample Excel File

SAMPLE PIPE DELIMITED FILE

67130|2007-08|FIS|16366|E|Briarbrook PS|B40|10997|Klondike Road|Kanata|0|0|0|0|0|0000|0|0|0|0000|0|0|0|0|0|C|0|0|0|0|0|551107|84571|0|0|0|0|0|0|84571

67130|2007-08|FIS|127|E|BlossomPark PS|B20|212|3810 Sixth St|Gloucester|18|-0.5|1610916|0|106192|0000|0|0|0|0000|524521.905465587|0|28097.7892253336|0|0|P|1|0|0|0|0|0|0|0|0|0|0|0|0|0

67130|2007-08|FIS|921|E|Merivale PS|LAN|1531|63 Slack Rd|Nepean|23|-0.5|1044786|-126086|0|0000|0|0|0|0000|400465.253205128|0|8931.47729700855|0|0|N|0|0|0|0|0|0|0|0|0|0|0|0|0|0

67130|2007-08|FIS|16045|E|New Avalon PS|LIM|10701|Anywhere Road|Somewhere|40|0|0|0|0|0000|12631302|0|0|0000|0|0|157891.275|0|0|N|0|0|0|7194026|0|5437276|0|12631302|0|0|0|0|0|0

1

APPENDIX 2 – RELATIONSHIP BETWEEN EXCEL/PIPE DELIMITED FILE AND SCHEDULE 3C

1

1

APPENDIX 3 – LIST OF ERRORS AND REQUIRED ACTIONS

Error Message / Required Action
GROSS_BOOK_TRANSF_CIP did not equal CIP_TRANSF_CIP for asset #[number]! / Check that your transfer out of construction in progress to the asset is equal for the asset with the serial number specified.
GROSS_BOOK_TRANSF_PRE did not equal PREAC_AMT_TRANSF_TO_ASSET for asset #[number]! / Check that your transfer out of pre-acquisition/construction to the asset is equal for the asset with the serial number specified.
CIP_TRANSF_PRECON did not equal PREAC_AMT_TRANSF_CIP for asset #[number]! / Check that your transfer out of pre-acquisition/construction to construction in progress is equal for the asset with the serial number specified.
GROSS_BOOK_DISPOSAL was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the disposal of gross book value. This amount must be positive as the formula will be subtracting this amount.
ACC_AMOR_DISPOSAL was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the disposal of accumulated amortization. This amount must be positive as the formula will be subtracting this amount.
GROSS_BOOK_ADD was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for additions to the gross book value. This amount must be positive.
ACC_AMOR_AMORT was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the amortization expense. This amount must be positive.
ACC_AMOR_WRITEDW was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the writedowns. This amount must be positive as amortization is being accelerated.
CIP_ADD was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the addition of construction in progress costs. This amount must be positive.
GROSS_BOOK_TRANSF_CIP was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the transfer from construction in progress costs to the asset. This amount must be positive.
GROSS_BOOK_TRANSF_PRE was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the transfer from preconstruction/acquisition costs to the asset. This amount must be positive.
CIP_TRANSF_PRECON was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the transfer from preconstruction/acquisition to construction in progress. This amount must be positive.
CIP_TRANSF_CIP was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the transfer of construction in progress to the asset. This amount must be positive as the formula will be subtracting this amount.
PREAC_AMT_TRANSF_TO_ASSET was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the transfer of preconstruction/acquisition to the asset. This amount must be positive as the formula will be subtracting this amount.
PREAC_AMT_TRANSF_CIP was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the transfer of preconstruction/acquisition to construction in progress. This amount must be positive as the formula will be subtracting this amount.
PREAC_ADD was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the addition of pre-acquisition costs. This amount must be positive.
PREAC_WRITEOFF was a negative amount for asset #[number]; this amount must be reported as an absolute (read: positive) amount. / Check whether you have included a negative amount for the writeoff of pre-acquisition costs. This amount must be positive as the formula will be subtracting this amount.
Your login’s Board ID ( [board ID] ) doesn’t match the Board ID in the file for asset #[number]. / Check that you have indicated the correct 5 digit Board ID number for the asset serial number specified.
Selected school year ( [school year] ) doesn’t match school year in file for asset #[number] / Checked that you have indicated the correct school year for the asset serial number specified.
Selected cycle ( [cycle] ) doesn’t match cycle in file for asset #[number] / Check that you have indicated the correct cycle for the asset serial number specified.
No asset serial number was supplied for one of the assets in the file. / Check that all assets have serial numbers. If the ministry has assigned the asset a serial number, it can not be changed. Asset serial numbers must be unique and can not be duplicated.
Invalid asset serial number ( [number] ) – must be less than 12 digits and be a number greater than 0. / Check the serial number of the asset specified. Remember that all assets must have serial numbers and that they can not be greater than 6 digits. Also, if the ministry has assigned the asset a serial number, it can not be changed. Asset serial numbers must be unique and can not be duplicated.
Invalid asset status for asset #[number]; asset status must be ‘N’ or ‘E’ (no quotes) / Check the status of the asset specified
and ensure that it is one of the ones specified. Remember you can not leave any cells blank. One of these options must be specified.
Asset #[number] has no name! All assets must have a name. / Check that you have included a name for the asset with the serial number specified. Remember you can not leave any cells blank.
The name for asset #[number] is too long. Asset names may be up to 255 characters long. / Check the length of the asset name for the asset with the serial number specified. It should not be more than 255 characters long.
Invalid asset type for asset #[number]; must be one of ‘B40’, ‘B20’, ‘LAN’, ‘LIM’, or ‘40R’ / Check the type of the asset specified
and ensure that it is one of the ones required. Remember you can not leave any cells blank. One of these options must be specified.
Asset #[number] doesn’t have an SFIS number. All assets require an SFIS number. / Check that you have included an SFIS number for the asset with the serial number specified. If an SFIS number has not been assigned, assign the asset a temporary one. Remember you can not leave any cells blank.
Asset #[number] doesn’t have an address. All assets require an address. / Check that you have included an address for the asset with the serial number specified. Remember you can not leave any cells blank.
Asset #[number] doesn’t have a city. All assets require a city. / Check that you have included a city for the asset with the serial number specified. Remember you can not leave any cells blank.
Invalid disposal type for asset #[number]; must be one of ‘C’, ‘P’ or ‘N’ (no quotes). / Check the disposal of the asset specified
and ensure that it is one of the ones specified. Remember you can not leave any cells blank. One of these options must be specified.
The opening gross book value for asset # [number] does not match the closing value from the last collection cycle. / Check that the opening gross book value balance reported agrees to the prior year closing for the asset indicated to ensure continuity.
The opening service life for asset # [number] does not match the closing service life from the last collection cycle. / Check that the opening service of the asset indicated agrees to the prior year closing to ensure continuity.
The opening accumulated amortization for asset # [number] does not match the closing value from the last collection cycle. / Check that the opening accumulated amortization of the asset indicated agrees to the prior year closing to ensure continuity.

1